[[236054]]

除了基礎(chǔ)題部分，本文還收集整理的MySQL面試題還包括如下知識(shí)點(diǎn)或題型：

• MySQL高性能索引
• SQL語(yǔ)句
• MySQL查詢(xún)優(yōu)化
• MySQL高擴(kuò)展高可用
• MySQL安全性

問(wèn)題1：char、varchar的區(qū)別是什么？

varchar是變長(zhǎng)而char的長(zhǎng)度是固定的。如果你的內(nèi)容是固定大小的，你會(huì)得到更好的性能。

問(wèn)題2: TRUNCATE和DELETE的區(qū)別是什么？

DELETE命令從一個(gè)表中刪除某一行，或多行，TRUNCATE命令***地從表中刪除每一行。

問(wèn)題3：什么是觸發(fā)器，MySQL中都有哪些觸發(fā)器？

觸發(fā)器是指一段代碼，當(dāng)觸發(fā)某個(gè)事件時(shí)，自動(dòng)執(zhí)行這些代碼。在MySQL數(shù)據(jù)庫(kù)中有如下六種觸發(fā)器：

1. Before Insert
2. After Insert
3. Before Update
4. After Update
5. Before Delete
6. After Delete

問(wèn)題4：FLOAT和DOUBLE的區(qū)別是什么？

1. FLOAT類(lèi)型數(shù)據(jù)可以存儲(chǔ)至多8位十進(jìn)制數(shù)，并在內(nèi)存中占4字節(jié)。
2. DOUBLE類(lèi)型數(shù)據(jù)可以存儲(chǔ)至多18位十進(jìn)制數(shù)，并在內(nèi)存中占8字節(jié)。

問(wèn)題5：如何在MySQL種獲取當(dāng)前日期？ 

SELECT CURRENT_DATE(); 

問(wèn)題6：如何查詢(xún)第n高的工資？ 

SELECT DISTINCT(salary) from employee ORDER BY salary DESC LIMIT n-1,1 

問(wèn)題7：請(qǐng)寫(xiě)出下面MySQL數(shù)據(jù)類(lèi)型表達(dá)的意義（int(0)、char(16)、varchar(16)、datetime、text）

知識(shí)點(diǎn)分析

此題考察的是MySQL數(shù)據(jù)類(lèi)型。MySQL數(shù)據(jù)類(lèi)型屬于MySQL數(shù)據(jù)庫(kù)基礎(chǔ)，由此延伸出的知識(shí)點(diǎn)還包括如下內(nèi)容：

• MySQL基礎(chǔ)操作
• MySQL存儲(chǔ)引擎
• MySQL鎖機(jī)制
• MySQL事務(wù)處理、存儲(chǔ)過(guò)程、觸發(fā)器

數(shù)據(jù)類(lèi)型考點(diǎn)：

• 1、整數(shù)類(lèi)型，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別表示1字節(jié)、2字節(jié)、3字節(jié)、4字節(jié)、8字節(jié)整數(shù)。任何整數(shù)類(lèi)型都可以加上UNSIGNED屬性，表示數(shù)據(jù)是無(wú)符號(hào)的，即非負(fù)整數(shù)。

長(zhǎng)度：整數(shù)類(lèi)型可以被指定長(zhǎng)度，例如：INT(11)表示長(zhǎng)度為11的INT類(lèi)型。長(zhǎng)度在大多數(shù)場(chǎng)景是沒(méi)有意義的，它不會(huì)限制值的合法范圍，只會(huì)影響顯示字符的個(gè)數(shù)，而且需要和UNSIGNED ZEROFILL屬性配合使用才有意義。

例子，假定類(lèi)型設(shè)定為INT(5)，屬性為UNSIGNED ZEROFILL，如果用戶(hù)插入的數(shù)據(jù)為12的話(huà)，那么數(shù)據(jù)庫(kù)實(shí)際存儲(chǔ)數(shù)據(jù)為00012。

• 2、實(shí)數(shù)類(lèi)型，包括FLOAT、DOUBLE、DECIMAL。

DECIMAL可以用于存儲(chǔ)比BIGINT還大的整型，能存儲(chǔ)精確的小數(shù)。

而FLOAT和DOUBLE是有取值范圍的，并支持使用標(biāo)準(zhǔn)的浮點(diǎn)進(jìn)行近似計(jì)算。

計(jì)算時(shí)FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串進(jìn)行處理。

• 3、字符串類(lèi)型，包括VARCHAR、CHAR、TEXT、BLOB

VARCHAR用于存儲(chǔ)可變長(zhǎng)字符串，它比定長(zhǎng)類(lèi)型更節(jié)省空間。

VARCHAR使用額外1或2個(gè)字節(jié)存儲(chǔ)字符串長(zhǎng)度。列長(zhǎng)度小于255字節(jié)時(shí)，使用1字節(jié)表示，否則使用2字節(jié)表示。

VARCHAR存儲(chǔ)的內(nèi)容超出設(shè)置的長(zhǎng)度時(shí)，內(nèi)容會(huì)被截?cái)唷?/p>

CHAR是定長(zhǎng)的，根據(jù)定義的字符串長(zhǎng)度分配足夠的空間。

CHAR會(huì)根據(jù)需要使用空格進(jìn)行填充方便比較。

CHAR適合存儲(chǔ)很短的字符串，或者所有值都接近同一個(gè)長(zhǎng)度。

CHAR存儲(chǔ)的內(nèi)容超出設(shè)置的長(zhǎng)度時(shí)，內(nèi)容同樣會(huì)被截?cái)唷?/p>

使用策略：

對(duì)于經(jīng)常變更的數(shù)據(jù)來(lái)說(shuō)，CHAR比VARCHAR更好，因?yàn)镃HAR不容易產(chǎn)生碎片。

對(duì)于非常短的列，CHAR比VARCHAR在存儲(chǔ)空間上更有效率。

使用時(shí)要注意只分配需要的空間，更長(zhǎng)的列排序時(shí)會(huì)消耗更多內(nèi)存。

盡量避免使用TEXT/BLOB類(lèi)型，查詢(xún)時(shí)會(huì)使用臨時(shí)表，導(dǎo)致嚴(yán)重的性能開(kāi)銷(xiāo)。

• 4、枚舉類(lèi)型（ENUM），把不重復(fù)的數(shù)據(jù)存儲(chǔ)為一個(gè)預(yù)定義的集合。

有時(shí)可以使用ENUM代替常用的字符串類(lèi)型。

ENUM存儲(chǔ)非常緊湊，會(huì)把列表值壓縮到一個(gè)或兩個(gè)字節(jié)。

ENUM在內(nèi)部存儲(chǔ)時(shí)，其實(shí)存的是整數(shù)。

盡量避免使用數(shù)字作為ENUM枚舉的常量，因?yàn)槿菀谆靵y。

排序是按照內(nèi)部存儲(chǔ)的整數(shù)

• 5、日期和時(shí)間類(lèi)型，盡量使用timestamp，空間效率高于datetime，

用整數(shù)保存時(shí)間戳通常不方便處理。

如果需要存儲(chǔ)微妙，可以使用bigint存儲(chǔ)。

看到這里，這道真題是不是就比較容易回答了。

答：int(0)表示數(shù)據(jù)是INT類(lèi)型，長(zhǎng)度是0、char(16)表示固定長(zhǎng)度字符串，長(zhǎng)度為16、varchar(16)表示可變長(zhǎng)度字符串，長(zhǎng)度為16、datetime表示時(shí)間類(lèi)型、text表示字符串類(lèi)型，能存儲(chǔ)大字符串，最多存儲(chǔ)65535字節(jié)數(shù)據(jù)）

MySQL基礎(chǔ)操作：

常見(jiàn)操作

MySQL的連接和關(guān)閉：mysql -u -p -h -P

-u：指定用戶(hù)名

-p：指定密碼

-h：主機(jī)

-P：端口

進(jìn)入MySQL命令行后：G、c、q、s、h、d

G：打印結(jié)果垂直顯示

c：取消當(dāng)前MySQL命令

q：退出MySQL連接

s：顯示服務(wù)器狀態(tài)

h：幫助信息

d：改變執(zhí)行符

MySQL存儲(chǔ)引擎：

1、InnoDB存儲(chǔ)引擎，

• 默認(rèn)事務(wù)型引擎，最重要最廣泛的存儲(chǔ)引擎，性能非常優(yōu)秀。
• 數(shù)據(jù)存儲(chǔ)在共享表空間，可以通過(guò)配置分開(kāi)。也就是多個(gè)表和索引都存儲(chǔ)在一個(gè)表空間中，可以通過(guò)配置文件改變此配置。
• 對(duì)主鍵查詢(xún)的性能高于其他類(lèi)型的存儲(chǔ)引擎。
• 內(nèi)部做了很多優(yōu)化，從磁盤(pán)讀取數(shù)據(jù)時(shí)會(huì)自動(dòng)構(gòu)建hash索引，插入數(shù)據(jù)時(shí)自動(dòng)構(gòu)建插入緩沖區(qū)。
• 通過(guò)一些機(jī)制和工具支持真正的熱備份。
• 支持崩潰后的安全恢復(fù)。
• 支持行級(jí)鎖。
• 支持外鍵。

2、MyISAM存儲(chǔ)引擎，

• 擁有全文索引、壓縮、空間函數(shù)。
• 不支持事務(wù)和行級(jí)鎖、不支持崩潰后的安全恢復(fù)。
• 表存儲(chǔ)在兩個(gè)文件，MYD和MYI。
• 設(shè)計(jì)簡(jiǎn)單，某些場(chǎng)景下性能很好，例如獲取整個(gè)表有多少條數(shù)據(jù)，性能很高。
• 全文索引不是很常用，不如使用外部的ElasticSearch或Lucene。

3、其他表引擎，

Archive、Blackhole、CSV、Memory

使用策略

在大多數(shù)場(chǎng)景下建議使用InnoDB存儲(chǔ)引擎。

MySQL鎖機(jī)制

表鎖是日常開(kāi)發(fā)中的常見(jiàn)問(wèn)題，因此也是面試當(dāng)中最常見(jiàn)的考察點(diǎn)，當(dāng)多個(gè)查詢(xún)同一時(shí)刻進(jìn)行數(shù)據(jù)修改時(shí)，就會(huì)產(chǎn)生并發(fā)控制的問(wèn)題。共享鎖和排他鎖，就是讀鎖和寫(xiě)鎖。

• 共享鎖，不堵塞，多個(gè)用戶(hù)可以同時(shí)讀一個(gè)資源，互不干擾。
• 排他鎖，一個(gè)寫(xiě)鎖會(huì)阻塞其他的讀鎖和寫(xiě)鎖，這樣可以只允許一個(gè)用戶(hù)進(jìn)行寫(xiě)入，防止其他用戶(hù)讀取正在寫(xiě)入的資源。

鎖的粒度

• 表鎖，系統(tǒng)開(kāi)銷(xiāo)最小，會(huì)鎖定整張表，MyIsam使用表鎖。
• 行鎖，***程度的支持并發(fā)處理，但是也帶來(lái)了***的鎖開(kāi)銷(xiāo)，InnoDB使用行鎖。

MySQL事務(wù)處理

• MySQL提供事務(wù)處理的表引擎，也就是InnoDB。
• 服務(wù)器層不管理事務(wù)，由下層的引擎實(shí)現(xiàn)，所以同一個(gè)事務(wù)中，使用多種引擎是不靠譜的。
• 需要注意，在非事務(wù)表上執(zhí)行事務(wù)操作，MySQL不會(huì)發(fā)出提醒，也不會(huì)報(bào)錯(cuò)。

存儲(chǔ)過(guò)程

• 為以后的使用保存的一條或多條MySQL語(yǔ)句的集合，因此也可以在存儲(chǔ)過(guò)程中加入業(yè)務(wù)邏輯和流程。
• 可以在存儲(chǔ)過(guò)程中創(chuàng)建表，更新數(shù)據(jù)，刪除數(shù)據(jù)等等。

使用策略

• 可以通過(guò)把SQL語(yǔ)句封裝在容易使用的單元中，簡(jiǎn)化復(fù)雜的操作
• 可以保證數(shù)據(jù)的一致性
• 可以簡(jiǎn)化對(duì)變動(dòng)的管理

觸發(fā)器

提供給程序員和數(shù)據(jù)分析員來(lái)保證數(shù)據(jù)完整性的一種方法，它是與表事件相關(guān)的特殊的存儲(chǔ)過(guò)程。

使用場(chǎng)景

• 可以通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。
• 實(shí)時(shí)監(jiān)控某張表中的某個(gè)字段的更改而需要做出相應(yīng)的處理。
• 例如可以生成某些業(yè)務(wù)的編號(hào)。
• 注意不要濫用，否則會(huì)造成數(shù)據(jù)庫(kù)及應(yīng)用程序的維護(hù)困難。
• 大家需要牢記以上基礎(chǔ)知識(shí)點(diǎn)，重點(diǎn)是理解數(shù)據(jù)類(lèi)型CHAR和VARCHAR的差異，表存儲(chǔ)引擎InnoDB和MyISAM的區(qū)別。

問(wèn)題8：請(qǐng)說(shuō)明InnoDB和MyISAM的區(qū)別

• InnoDB支持事務(wù)，MyISAM不支持；
• InnoDB數(shù)據(jù)存儲(chǔ)在共享表空間，MyISAM數(shù)據(jù)存儲(chǔ)在文件中；
• InnoDB支持行級(jí)鎖，MyISAM只支持表鎖；
• InnoDB支持崩潰后的恢復(fù)，MyISAM不支持；
• InnoDB支持外鍵，MyISAM不支持；
• InnoDB不支持全文索引，MyISAM支持全文索引；

問(wèn)題9：innodb引擎的特性

• 插入緩沖（insert buffer)
• 二次寫(xiě)(double write)
• 自適應(yīng)哈希索引(ahi)
• 預(yù)讀(read ahead)

問(wèn)題10：請(qǐng)列舉3個(gè)以上表引擎

InnoDB、MyISAM、Memory

問(wèn)題11：請(qǐng)說(shuō)明varchar和text的區(qū)別

• varchar可指定字符數(shù)，text不能指定，內(nèi)部存儲(chǔ)varchar是存入的實(shí)際字符數(shù)+1個(gè)字節(jié)（n<=255）或2個(gè)字節(jié)(n>255)，text是實(shí)際字符數(shù)+2個(gè)字節(jié)。
• text類(lèi)型不能有默認(rèn)值。
• varchar可直接創(chuàng)建索引，text創(chuàng)建索引要指定前多少個(gè)字符。varchar查詢(xún)速度快于text,在都創(chuàng)建索引的情況下，text的索引幾乎不起作用。
• 查詢(xún)text需要?jiǎng)?chuàng)建臨時(shí)表。

問(wèn)題11：varchar(50)中50的含義

最多存放50個(gè)字符，varchar(50)和(200)存儲(chǔ)hello所占空間一樣，但后者在排序時(shí)會(huì)消耗更多內(nèi)存，因?yàn)閛rder by col采用fixed_length計(jì)算col長(zhǎng)度(memory引擎也一樣)。

問(wèn)題12：int(20)中20的含義

是指顯示字符的長(zhǎng)度，不影響內(nèi)部存儲(chǔ)，只是當(dāng)定義了ZEROFILL時(shí)，前面補(bǔ)多少個(gè) 0

問(wèn)題13：簡(jiǎn)單描述MySQL中，索引，主鍵，唯一索引，聯(lián)合索引的區(qū)別，對(duì)數(shù)據(jù)庫(kù)的性能有什么影響？

知識(shí)點(diǎn)分析

此真題主要考察的是MySQL索引的基礎(chǔ)和類(lèi)型，由此延伸出的知識(shí)點(diǎn)還包括如下內(nèi)容：

• MySQL索引的創(chuàng)建原則
• MySQL索引的注意事項(xiàng)
• MySQL索引的原理

下面我們就來(lái)將這些知識(shí)一網(wǎng)打盡

索引的基礎(chǔ)

• 索引類(lèi)似于書(shū)籍的目錄，要想找到一本數(shù)的某個(gè)特定主題，需要先查找書(shū)的目錄，定位對(duì)應(yīng)的頁(yè)碼
• 存儲(chǔ)引擎使用類(lèi)似的方式進(jìn)行數(shù)據(jù)查詢(xún)，先去索引當(dāng)中找到對(duì)應(yīng)的值，然后根據(jù)匹配的索引找到對(duì)應(yīng)的數(shù)據(jù)行。

創(chuàng)建索引的語(yǔ)法：

• 首先創(chuàng)建一個(gè)表：create table t1 (id int primary key,username varchar(20),password varchar(20));
• 創(chuàng)建單個(gè)索引的語(yǔ)法：CREATE INDEX 索引名 on 表名（字段名）
• 索引名一般是：表名_字段名
• 給id創(chuàng)建索引：CREATE INDEX t1_id on t1(id);
• 創(chuàng)建聯(lián)合索引的語(yǔ)法：CREATE INDEX 索引名 on 表名（字段名1，字段名2）
• 給username和password創(chuàng)建聯(lián)合索引：CREATE index t1_username_password ON t1(username,password)
• 其中index還可以替換成unique，primary key，分別代表唯一索引和主鍵索引
• 刪除索引：DROP INDEX t1_username_password ON t1

索引對(duì)性能的影響：

• 大大減少服務(wù)器需要掃描的數(shù)據(jù)量。
• 幫助服務(wù)器避免排序和臨時(shí)表。
• 將隨機(jī)I/O變順序I/O。
• 大大提高查詢(xún)速度。
• 降低寫(xiě)的速度（不良影響）。
• 磁盤(pán)占用（不良影響）。

索引的使用場(chǎng)景：

• 對(duì)于非常小的表，大部分情況下全表掃描效率更高。
• 中到大型表，索引非常有效。
• 特大型的表，建立和使用索引的代價(jià)會(huì)隨之增大，可以使用分區(qū)技術(shù)來(lái)解決。

索引的類(lèi)型：

索引很多種類(lèi)型，是在MySQL的存儲(chǔ)引擎實(shí)現(xiàn)的。

• 普通索引：最基本的索引，沒(méi)有任何約束限制。
• 唯一索引：和普通索引類(lèi)似，但是具有唯一性約束。
• 主鍵索引：特殊的唯一索引，不允許有空值。

索引的區(qū)別：

-一個(gè)表只能有一個(gè)主鍵索引，但是可以有多個(gè)唯一索引。

• 主鍵索引一定是唯一索引，唯一索引不是主鍵索引。
• 主鍵可以與外鍵構(gòu)成參照完整性約束，防止數(shù)據(jù)不一致。
• 聯(lián)合索引：將多個(gè)列組合在一起創(chuàng)建索引，可以覆蓋多個(gè)列。（也叫復(fù)合索引，組合索引）
• 外鍵索引：只有InnoDB類(lèi)型的表才可以使用外鍵索引，保證數(shù)據(jù)的一致性、完整性、和實(shí)現(xiàn)級(jí)聯(lián)操作（基本不用）。
• 全文索引：MySQL自帶的全文索引只能用于MyISAM，并且只能對(duì)英文進(jìn)行全文檢索 （基本不用）

MySQL索引的創(chuàng)建原則

• 最適合創(chuàng)建索引的列是出現(xiàn)在WHERE或ON子句中的列，或連接子句中的列而不是出現(xiàn)在SELECT關(guān)鍵字后的列。
• 索引列的基數(shù)越大，數(shù)據(jù)區(qū)分度越高，索引的效果越好。
• 對(duì)于字符串進(jìn)行索引，應(yīng)該制定一個(gè)前綴長(zhǎng)度，可以節(jié)省大量的索引空間。
• 根據(jù)情況創(chuàng)建聯(lián)合索引，聯(lián)合索引可以提高查詢(xún)效率。
• 避免創(chuàng)建過(guò)多的索引，索引會(huì)額外占用磁盤(pán)空間，降低寫(xiě)操作效率。
• 主鍵盡可能選擇較短的數(shù)據(jù)類(lèi)型，可以有效減少索引的磁盤(pán)占用提高查詢(xún)效率。

MySQL索引的注意事項(xiàng)

1、聯(lián)合索引遵循前綴原則 

KEY(a,b,c)  
WHERE a = 1 AND b = 2 AND c = 3  
WHERE a = 1 AND b = 2  
WHERE a = 1  
#以上SQL語(yǔ)句可以用到索引  
WHERE b = 2 AND c = 3  
WHERE a = 1 AND c = 3  
#以上SQL語(yǔ)句用不到索引  

2、LIKE查詢(xún)，%不能在前 

WHERE name LIKE "%wang%"  
#以上語(yǔ)句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。  

3、列值為空（NULL）時(shí)是可以使用索引的，但MySQL難以?xún)?yōu)化引用了可空列的查詢(xún),它會(huì)使索引、索引統(tǒng)計(jì)和值更加復(fù)雜?？煽樟行枰嗟膬?chǔ)存空間，還需要在MySQL內(nèi)部進(jìn)行特殊處理。

4、如果MySQL估計(jì)使用索引比全表掃描更慢，會(huì)放棄使用索引，例如：

表中只有100條數(shù)據(jù)左右。對(duì)于SQL語(yǔ)句WHERE id > 1 AND id < 100，MySQL會(huì)優(yōu)先考慮全表掃描。

5、如果關(guān)鍵詞or前面的條件中的列有索引，后面的沒(méi)有，所有列的索引都不會(huì)被用到。

6、列類(lèi)型是字符串，查詢(xún)時(shí)一定要給值加引號(hào)，否則索引失效，例如：

列name varchar(16)，存儲(chǔ)了字符串"100"

WHERE name = 100;

以上SQL語(yǔ)句能搜到，但無(wú)法用到索引。

MySQL索引的原理

• MySQL索引是用一種叫做聚簇索引的數(shù)據(jù)結(jié)構(gòu)實(shí)現(xiàn)的，下面我們就來(lái)看一下什么是聚簇索引。
• 聚簇索引是一種數(shù)據(jù)存儲(chǔ)方式，它實(shí)際上是在同一個(gè)結(jié)構(gòu)中保存了B+樹(shù)索引和數(shù)據(jù)行，InnoDB表是按照聚簇索引組織的（類(lèi)似于Oracle的索引組織表）。

注：

B+ 樹(shù)是一種樹(shù)數(shù)據(jù)結(jié)構(gòu)，是一個(gè)n叉排序樹(shù)，每個(gè)節(jié)點(diǎn)通常有多個(gè)孩子，一棵B+樹(shù)包含根節(jié)點(diǎn)、內(nèi)部節(jié)點(diǎn)和葉子節(jié)點(diǎn)。根節(jié)點(diǎn)可能是一個(gè)葉子節(jié)點(diǎn)，也可能是一個(gè)包含兩個(gè)或兩個(gè)以上孩子節(jié)點(diǎn)的節(jié)點(diǎn)。

B+ 樹(shù)通常用于數(shù)據(jù)庫(kù)和操作系統(tǒng)的文件系統(tǒng)中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等文件系統(tǒng)都在使用B+樹(shù)作為元數(shù)據(jù)索引。B+ 樹(shù)的特點(diǎn)是能夠保持?jǐn)?shù)據(jù)穩(wěn)定有序，其插入與修改擁有較穩(wěn)定的對(duì)數(shù)時(shí)間復(fù)雜度。B+ 樹(shù)元素自底向上插入。

InnoDB通過(guò)主鍵聚簇?cái)?shù)據(jù)，如果沒(méi)有定義主鍵，會(huì)選擇一個(gè)唯一的非空索引代替，如果沒(méi)有這樣的索引，會(huì)隱式定義個(gè)主鍵作為聚簇索引。

下圖形象說(shuō)明了聚簇索引表(InnoDB)和普通的堆組織表(MyISAM)的區(qū)別：

最常問(wèn)的MySQL面試題三——每個(gè)開(kāi)發(fā)人員都應(yīng)該知道

對(duì)于普通的堆組織表來(lái)說(shuō)（右圖），表數(shù)據(jù)和索引是分別存儲(chǔ)的，主鍵索引和二級(jí)索引存儲(chǔ)上沒(méi)有任何區(qū)別。

而對(duì)于聚簇索引表來(lái)說(shuō)（左圖），表數(shù)據(jù)是和主鍵一起存儲(chǔ)的，主鍵索引的葉結(jié)點(diǎn)存儲(chǔ)行數(shù)據(jù)，二級(jí)索引的葉結(jié)點(diǎn)存儲(chǔ)行的主鍵值。

聚簇索引表***限度地提高了I/O密集型應(yīng)用的性能，但它也有以下幾個(gè)限制：

• 1）插入速度嚴(yán)重依賴(lài)于插入順序，按照主鍵的順序插入是最快的方式，否則將會(huì)出現(xiàn)頁(yè)分裂，嚴(yán)重影響性能。因此，對(duì)于InnoDB表，我們一般都會(huì)定義一個(gè)自增的ID列為主鍵。
• 2）更新主鍵的代價(jià)很高，因?yàn)閷?huì)導(dǎo)致被更新的行移動(dòng)。因此，對(duì)于InnoDB表，我們一般定義主鍵為不可更新。
• 3）二級(jí)索引訪(fǎng)問(wèn)需要兩次索引查找，***次找到主鍵值，第二次根據(jù)主鍵值找到行數(shù)據(jù)。

二級(jí)索引的葉節(jié)點(diǎn)存儲(chǔ)的是主鍵值，而不是行指針，這是為了減少當(dāng)出現(xiàn)行移動(dòng)或數(shù)據(jù)頁(yè)分裂時(shí)二級(jí)索引的維護(hù)工作，但會(huì)讓二級(jí)索引占用更多的空間。

解題方法

在一些MySQL索引基礎(chǔ)考題中，我們可以輕松的通過(guò)索引基礎(chǔ)和類(lèi)型來(lái)解決此類(lèi)問(wèn)題，對(duì)于一些索引創(chuàng)建注意事項(xiàng)方面的考點(diǎn)，我們可以通過(guò)索引創(chuàng)建原則和注意事項(xiàng)來(lái)解決。

問(wèn)題14：創(chuàng)建MySQL聯(lián)合索引應(yīng)該注意什么？

需遵循前綴原則

問(wèn)題15：列值為NULL時(shí)，查詢(xún)是否會(huì)用到索引？

在MySQL里NULL值的列也是走索引的。當(dāng)然，如果計(jì)劃對(duì)列進(jìn)行索引，就要盡量避免把它設(shè)置為可空，MySQL難以?xún)?yōu)化引用了可空列的查詢(xún),它會(huì)使索引、索引統(tǒng)計(jì)和值更加復(fù)雜。

問(wèn)題16：以下語(yǔ)句是否會(huì)應(yīng)用索引：SELECT FROM users WHERE YEAR(adddate) < 2007;*

不會(huì)，因?yàn)橹灰猩婕暗竭\(yùn)算，MySQL就不會(huì)使用索引。

問(wèn)題17：MyISAM索引實(shí)現(xiàn)？

MyISAM存儲(chǔ)引擎使用B+Tree作為索引結(jié)構(gòu)，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。MyISAM的索引方式也叫做非聚簇索引的，之所以這么稱(chēng)呼是為了與InnoDB的聚簇索引區(qū)分。

問(wèn)題17：MyISAM索引與InnoDB索引的區(qū)別？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)著行數(shù)據(jù)，因此主鍵索引非常高效。
• MyISAM索引的葉子節(jié)點(diǎn)存儲(chǔ)的是行數(shù)據(jù)地址，需要再尋址一次才能得到數(shù)據(jù)。
• InnoDB非主鍵索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和其他帶索引的列數(shù)據(jù)，因此查詢(xún)時(shí)做到覆蓋索引會(huì)非常高效。

問(wèn)題18：以下三條sql 如何建索引，只建一條怎么建？ 

WHERE a=1 AND b=1  
WHERE b=1  
WHERE b=1 ORDER BY time DESC  

以順序b,a,time建立聯(lián)合索引，CREATE INDEX table1_b_a_time ON index_test01(b,a,time)。因?yàn)?**MySQL版本會(huì)優(yōu)化WHERE子句后面的列順序，以匹配聯(lián)合索引順序。

問(wèn)題19：有A(id,sex,par,c1,c2),B(id,age,c1,c2)兩張表，其中A.id與B.id關(guān)聯(lián)，現(xiàn)在要求寫(xiě)出一條SQL語(yǔ)句，將B中age>50的記錄的c1,c2更新到A表中同一記錄中的c1,c2字段中

考點(diǎn)分析

這道題主要考察的是MySQL的關(guān)聯(lián)UPDATE語(yǔ)句

延伸考點(diǎn)：

• MySQL的關(guān)聯(lián)查詢(xún)語(yǔ)句
• MySQL的關(guān)聯(lián)UPDATE語(yǔ)句

針對(duì)剛才這道題，答案可以是如下兩種形式的寫(xiě)法： 

UPDATE A,B SET A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id  
UPDATE A INNER JOIN B ON A.id=B.id SET A.c1 = B.c1,A.c2=B.c2  

再加上B中age>50的條件： 

UPDATE A,B set A.c1 = B.c1, A.c2 = B.c2 WHERE A.id = B.id and B.age > 50;  
UPDATE A INNER JOIN B ON A.id = B.id set A.c1 = B.c1,A.c2 = B.c2 WHERE B.age > 50 

MySQL的關(guān)聯(lián)查詢(xún)語(yǔ)句

六種關(guān)聯(lián)查詢(xún)

• 交叉連接（CROSS JOIN）
• 內(nèi)連接（INNER JOIN）
• 外連接（LEFT JOIN/RIGHT JOIN）
• 聯(lián)合查詢(xún)（UNION與UNION ALL）
• 全連接（FULL JOIN）
• 交叉連接（CROSS JOIN） 

SELECT * FROM A,B(,C)或者  
SELECT * FROM A CROSS JOIN B (CROSS JOIN C)  
#沒(méi)有任何關(guān)聯(lián)條件，結(jié)果是笛卡爾積，結(jié)果集會(huì)很大，沒(méi)有意義，很少使用  

內(nèi)連接（INNER JOIN） 

SELECT * FROM A,B WHERE A.id=B.id或者  
SELECT * FROM A INNER JOIN B ON A.id=B.id  

多表中同時(shí)符合某種條件的數(shù)據(jù)記錄的集合，INNER JOIN可以縮寫(xiě)為JOIN

內(nèi)連接分為三類(lèi)

• 等值連接：ON A.id=B.id
• 不等值連接：ON A.id > B.id
• 自連接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外連接（LEFT JOIN/RIGHT JOIN）

• 左外連接：LEFT OUTER JOIN, 以左表為主，先查詢(xún)出左表，按照ON后的關(guān)聯(lián)條件匹配右表，沒(méi)有匹配到的用NULL填充，可以簡(jiǎn)寫(xiě)成LEFT JOIN
• 右外連接：RIGHT OUTER JOIN, 以右表為主，先查詢(xún)出右表，按照ON后的關(guān)聯(lián)條件匹配左表，沒(méi)有匹配到的用NULL填充，可以簡(jiǎn)寫(xiě)成RIGHT JOIN

聯(lián)合查詢(xún)（UNION與UNION ALL） 

SELECT * FROM A UNION SELECT * FROM B UNION ... 

就是把多個(gè)結(jié)果集集中在一起，UNION前的結(jié)果為基準(zhǔn)，需要注意的是聯(lián)合查詢(xún)的列數(shù)要相等，相同的記錄行會(huì)合并

• 如果使用UNION ALL，不會(huì)合并重復(fù)的記錄行
• 效率 UNION 高于 UNION ALL
• 全連接（FULL JOIN）

MySQL不支持全連接

可以使用LEFT JOIN 和UNION和RIGHT JOIN聯(lián)合使用 

SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION  
SELECT * FROM A RIGHT JOIN B ON A.id=B.id  

嵌套查詢(xún)

用一條SQL語(yǔ)句得結(jié)果作為另外一條SQL語(yǔ)句得條件，效率不好把握 

SELECT * FROM A WHERE id IN (SELECT id FROM B) 

解題方法

根據(jù)考題要搞清楚表的結(jié)果和多表之間的關(guān)系，根據(jù)想要的結(jié)果思考使用那種關(guān)聯(lián)方式，通常把要查詢(xún)的列先寫(xiě)出來(lái)，然后分析這些列都屬于哪些表，才考慮使用關(guān)聯(lián)查詢(xún)

問(wèn)題20：

為了記錄足球比賽的結(jié)果，設(shè)計(jì)表如下：

team：參賽隊(duì)伍表

match：賽程表

其中，match賽程表中的hostTeamID與guestTeamID都和team表中的teamID關(guān)聯(lián)，查詢(xún)2006-6-1到2006-7-1之間舉行的所有比賽，并且用以下形式列出：拜仁 2:0 不萊梅 2006-6-21

首先列出需要查詢(xún)的列：

• 表team
• teamID teamName
• 表match
• match ID
• hostTeamID
• guestTeamID
• matchTime matchResult

其次列出結(jié)果列：

• 主隊(duì) 結(jié)果 客對(duì) 時(shí)間

初步寫(xiě)一個(gè)基礎(chǔ)的SQL： 

SELECT hostTeamID,matchResult,matchTime guestTeamID from match where matchTime between "2006-6-1" and "2006-7-1"; 

通過(guò)外鍵聯(lián)表，完成最終SQL： 

select t1.teamName,m.matchResult,t2.teamName,m.matchTime from match as m left join team as t1 on m.hostTeamID = t1.teamID, left join team t2 on m.guestTeamID=t2.guestTeamID where m.matchTime between "2006-6-1" and "2006-7-1" 

問(wèn)題21：UNION與UNION ALL的區(qū)別？

如果使用UNION ALL，不會(huì)合并重復(fù)的記錄行

效率 UNION 高于 UNION ALL

問(wèn)題22：一個(gè)6億的表a，一個(gè)3億的表b，通過(guò)外鍵tid關(guān)聯(lián)，你如何最快的查詢(xún)出滿(mǎn)足條件的第50000到第50200中的這200條數(shù)據(jù)記錄。

1、如果A表TID是自增長(zhǎng),并且是連續(xù)的,B表的ID為索引 

select * from a,b where a.tid = b.id and a.tid>50000 limit 200; 

2、如果A表的TID不是連續(xù)的,那么就需要使用覆蓋索引.TID要么是主鍵,要么是輔助索引,B表ID也需要有索引。 

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid; 

問(wèn)題23：拷貝表( 拷貝數(shù)據(jù), 源表名：a 目標(biāo)表名：b) 

insert into b(a, b, c) select d,e,f from a; 

問(wèn)題24： Student(S#,Sname,Sage,Ssex) 學(xué)生表 Course(C#,Cname,T#) 課程表 SC(S#,C#,score) 成績(jī)表 Teacher(T#,Tname) 教師表 查詢(xún)沒(méi)學(xué)過(guò)“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名 

select Student.S#,Student.Sname  
from Student  
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’葉平’);  

問(wèn)題25：隨機(jī)取出10條數(shù)據(jù) 

SELECT * FROM users WHERE id >= ((SELECT MAX(id) FROM users)-(SELECT MIN(id) FROM users)) * RAND() + (SELECT MIN(id) FROM users) LIMIT 10  
#此方法效率比直接用SELECT * FROM users order by rand() LIMIT 10高很多  

問(wèn)題26：請(qǐng)簡(jiǎn)述項(xiàng)目中優(yōu)化SQL語(yǔ)句執(zhí)行效率的方法，從哪些方面，SQL語(yǔ)句性能如何分析？

考點(diǎn)分析：

這道題主要考察的是查找分析SQL語(yǔ)句查詢(xún)速度慢的方法

延伸考點(diǎn)：

• 優(yōu)化查詢(xún)過(guò)程中的數(shù)據(jù)訪(fǎng)問(wèn)
• 優(yōu)化長(zhǎng)難的查詢(xún)語(yǔ)句
• 優(yōu)化特定類(lèi)型的查詢(xún)語(yǔ)句

如何查找查詢(xún)速度慢的原因

記錄慢查詢(xún)?nèi)罩荆治霾樵?xún)?nèi)罩?，不要直接打開(kāi)慢查詢(xún)?nèi)罩具M(jìn)行分析，這樣比較浪費(fèi)時(shí)間和精力，可以使用pt-query-digest工具進(jìn)行分析

使用show profile 

set profiling=1;開(kāi)啟，服務(wù)器上所有執(zhí)行語(yǔ)句會(huì)記錄執(zhí)行時(shí)間，存到臨時(shí)表中  
show profiles  
show profile for query 臨時(shí)表ID  

使用show status

show status會(huì)返回一些計(jì)數(shù)器，show global status會(huì)查看所有服務(wù)器級(jí)別的所有計(jì)數(shù)

有時(shí)根據(jù)這些計(jì)數(shù)，可以推測(cè)出哪些操作代價(jià)較高或者消耗時(shí)間多

show processlist

觀(guān)察是否有大量線(xiàn)程處于不正常的狀態(tài)或特征

最常問(wèn)的MySQL面試題五——每個(gè)開(kāi)發(fā)人員都應(yīng)該知道

使用explain

分析單條SQL語(yǔ)句

優(yōu)化查詢(xún)過(guò)程中的數(shù)據(jù)訪(fǎng)問(wèn)

• 訪(fǎng)問(wèn)數(shù)據(jù)太多導(dǎo)致查詢(xún)性能下降
• 確定應(yīng)用程序是否在檢索大量超過(guò)需要的數(shù)據(jù)，可能是太多行或列
• 確認(rèn)MySQL服務(wù)器是否在分析大量不必要的數(shù)據(jù)行
• 避免犯如下SQL語(yǔ)句錯(cuò)誤
• 查詢(xún)不需要的數(shù)據(jù)。解決辦法：使用limit解決
• 多表關(guān)聯(lián)返回全部列。解決辦法：指定列名
• 總是返回全部列。解決辦法：避免使用SELECT *
• 重復(fù)查詢(xún)相同的數(shù)據(jù)。解決辦法：可以緩存數(shù)據(jù)，下次直接讀取緩存
• 是否在掃描額外的記錄。解決辦法：
• 使用explain進(jìn)行分析，如果發(fā)現(xiàn)查詢(xún)需要掃描大量的數(shù)據(jù)，但只返回少數(shù)的行，可以通過(guò)如下技巧去優(yōu)化：
• 使用索引覆蓋掃描，把所有的列都放到索引中，這樣存儲(chǔ)引擎不需要回表獲取對(duì)應(yīng)行就可以返回結(jié)果。
• 改變數(shù)據(jù)庫(kù)和表的結(jié)構(gòu)，修改數(shù)據(jù)表范式
• 重寫(xiě)SQL語(yǔ)句，讓優(yōu)化器可以以更優(yōu)的方式執(zhí)行查詢(xún)。

優(yōu)化長(zhǎng)難的查詢(xún)語(yǔ)句

• 一個(gè)復(fù)雜查詢(xún)還是多個(gè)簡(jiǎn)單查詢(xún)
• MySQL內(nèi)部每秒能掃描內(nèi)存中上百萬(wàn)行數(shù)據(jù)，相比之下，響應(yīng)數(shù)據(jù)給客戶(hù)端就要慢得多
• 使用盡可能小的查詢(xún)是好的，但是有時(shí)將一個(gè)大的查詢(xún)分解為多個(gè)小的查詢(xún)是很有必要的。
• 切分查詢(xún)
• 將一個(gè)大的查詢(xún)分為多個(gè)小的相同的查詢(xún)
• 一次性刪除1000萬(wàn)的數(shù)據(jù)要比一次刪除1萬(wàn)，暫停一會(huì)的方案更加損耗服務(wù)器開(kāi)銷(xiāo)。
• 分解關(guān)聯(lián)查詢(xún)，讓緩存的效率更高。
• 執(zhí)行單個(gè)查詢(xún)可以減少鎖的競(jìng)爭(zhēng)。
• 在應(yīng)用層做關(guān)聯(lián)更容易對(duì)數(shù)據(jù)庫(kù)進(jìn)行拆分。
• 查詢(xún)效率會(huì)有大幅提升。
• 較少冗余記錄的查詢(xún)。

優(yōu)化特定類(lèi)型的查詢(xún)語(yǔ)句

• count(*)會(huì)忽略所有的列，直接統(tǒng)計(jì)所有列數(shù)，不要使用count(列名)
• MyISAM中，沒(méi)有任何where條件的count(*)非常快。
• 當(dāng)有where條件時(shí)，MyISAM的count統(tǒng)計(jì)不一定比其它引擎快。
• 可以使用explain查詢(xún)近似值，用近似值替代count(*)
• 增加匯總表
• 使用緩存

優(yōu)化關(guān)聯(lián)查詢(xún)

• 確定ON或者USING子句中是否有索引。
• 確保GROUP BY和ORDER BY只有一個(gè)表中的列，這樣MySQL才有可能使用索引。

優(yōu)化子查詢(xún)

• 用關(guān)聯(lián)查詢(xún)替代
• 優(yōu)化GROUP BY和DISTINCT
• 這兩種查詢(xún)據(jù)可以使用索引來(lái)優(yōu)化，是最有效的優(yōu)化方法
• 關(guān)聯(lián)查詢(xún)中，使用標(biāo)識(shí)列分組的效率更高
• 如果不需要ORDER BY，進(jìn)行GROUP BY時(shí)加ORDER BY NULL，MySQL不會(huì)再進(jìn)行文件排序。
• WITH ROLLUP超級(jí)聚合，可以挪到應(yīng)用程序處理

優(yōu)化LIMIT分頁(yè)

• LIMIT偏移量大的時(shí)候，查詢(xún)效率較低
• 可以記錄上次查詢(xún)的***ID，下次查詢(xún)時(shí)直接根據(jù)該ID來(lái)查詢(xún)

優(yōu)化UNION查詢(xún)

• UNION ALL的效率高于UNION

優(yōu)化WHERE子句

解題方法

對(duì)于此類(lèi)考題，先說(shuō)明如何定位低效SQL語(yǔ)句，然后根據(jù)SQL語(yǔ)句可能低效的原因做排查，先從索引著手，如果索引沒(méi)有問(wèn)題，考慮以上幾個(gè)方面，數(shù)據(jù)訪(fǎng)問(wèn)的問(wèn)題，長(zhǎng)難查詢(xún)句的問(wèn)題還是一些特定類(lèi)型優(yōu)化的問(wèn)題，逐一回答。

SQL語(yǔ)句優(yōu)化的一些方法？

1.對(duì)查詢(xún)進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

2.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num is null可以在num上設(shè)置默認(rèn)值0，確保表中num列沒(méi)有null值，然后這樣查詢(xún)：select id from t where num=

3.應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描。

4.應(yīng)盡量避免在 where 子句中使用or 來(lái)連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num=10 or num=20可以這樣查詢(xún)：select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描，如：

select id from t where num in(1,2,3) 對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

6.下面的查詢(xún)也將導(dǎo)致全表掃描：select id from t where name like ‘%李%’若要提高效率，可以考慮全文檢索。

7. 如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪(fǎng)問(wèn)計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然 而，如果在編譯時(shí)建立訪(fǎng)問(wèn)計(jì)劃，變量的值還是未知的，因而無(wú)法作為索引選擇的輸入項(xiàng)。如下面語(yǔ)句將進(jìn)行全表掃描：

select id from t where num=@num可以改為強(qiáng)制查詢(xún)使用索引：select id from t with(index(索引名)) where num=@num

8.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where num/2=100應(yīng)改為:select id from t where num=100*2

9.應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where substring(name,1,3)=’abc’ ，name以abc開(kāi)頭的id應(yīng)改為:

select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無(wú)法正確使用索引。