MySQL性能優(yōu)化主要包含3個方面，從微觀到宏觀依次為：

SQL優(yōu)化：當(dāng)然其中必然包含索引的優(yōu)化,，表設(shè)計優(yōu)化,，SQL拆分，查詢緩存優(yōu)化等問題,；

服務(wù)器參數(shù)優(yōu)化：比如各種全局緩存的微調(diào)（連接數(shù)配置, InnoDB緩沖池相關(guān)配置等）,，每個連接需要的各種緩存微調(diào)，IO參數(shù)調(diào)整（比如日志緩沖刷盤方式）,，并發(fā)配置等,；

服務(wù)器架構(gòu)優(yōu)化：比如主從復(fù)制，垂直拆分,，水平拆分,，甚至業(yè)務(wù)上增加緩存層等都能直接減輕數(shù)據(jù)庫壓力，從而降低響應(yīng)時間,。

當(dāng)然還有服務(wù)器硬件方面地調(diào)整,，也會有顯著效果。本文主要闡述SQL優(yōu)化中的索引部分,。

在談到索引的時候,，我們需要明確的是，適當(dāng)?shù)乃饕诙嘧x場景中能夠顯著的提升數(shù)據(jù)庫的查詢效率,，如果業(yè)務(wù)場景是寫多讀少,，就要謹(jǐn)慎地使用索引了，雖然他能夠在更新的時候（比如你的索引字段只出現(xiàn)在WHERE子句中）提升少許性能,，但是帶來的負(fù)面影響可能更大,，比如寫入數(shù)據(jù)時需要額外的索引維護(hù)，以及索引更新時導(dǎo)致的鎖問題,。

MySQL架構(gòu)圖大家都很熟悉，就不貼了，此處和索引相關(guān)的主要是服務(wù)器端和存儲引擎之間的交互部分,。

服務(wù)器端會根據(jù)成本模型生成最優(yōu)的查詢執(zhí)行計劃,，這份查詢執(zhí)行計劃會對SQL語句進(jìn)行優(yōu)化，例如關(guān)聯(lián)表順序的調(diào)整,，使用等價變化規(guī)則來簡化表達(dá)式,，IN子句優(yōu)化，提前終止查詢等,。

查詢執(zhí)行計劃決定了服務(wù)器端以哪種方式從存儲引擎獲取數(shù)據(jù),，比如通過主鍵獲取(rnd_*接口[1])或者通過二級索引獲取(index_*接口[2])。查詢計劃被發(fā)送給存儲引擎,，存儲引擎通過主鍵或者二級索引檢索數(shù)據(jù),，然后把結(jié)果返回到服務(wù)器端，服務(wù)器端根據(jù)WHERE條件中的其他子句再進(jìn)行過濾,，最后才返回給客戶端,。

需要注意的是存儲引擎端只會根據(jù)主鍵或者二級索引的值來過濾數(shù)據(jù)行（當(dāng)出現(xiàn)覆蓋索引的時候，則不需要再回表查詢數(shù)據(jù)行）,，不會做其他非索引字段的過濾,，如果有其他非索引字段的過濾，則需要返回到服務(wù)器端再做過濾（查詢執(zhí)行計劃中,，extra列出現(xiàn)using where）,。和客戶端從服務(wù)器端獲取數(shù)據(jù)的方式類似，服務(wù)器端從存儲引擎端獲取數(shù)據(jù)也是通過游標(biāo)的方式逐行獲取的,。

索引的定義是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu),。所以索引一定是針對存儲引擎而言的，不同的存儲引擎各自的索引實(shí)現(xiàn)各有不同,。

InnoDB存儲引擎采用B+Tree的數(shù)據(jù)結(jié)構(gòu)來存儲索引和數(shù)據(jù),，所以對順序查找，最左前綴匹配,，前綴列匹配,，范圍匹配的查詢支持非常好。InnoDB存儲引擎不支持用戶自定義的哈希索引,，自身實(shí)現(xiàn)的時候會依賴自適應(yīng)性哈希索引來提高根據(jù)二級索引查找主索引的效率,。

InnoDB存儲引擎采用聚簇索引（clustered index）的數(shù)據(jù)存儲方式來做物理存儲。聚簇索引同時保存了索引和數(shù)據(jù)頁,，而且二者相鄰存儲,，即，主索引值相鄰的數(shù)據(jù)頁相鄰存儲,。聚簇索引就是按照每張表的主鍵引構(gòu)成一個B+樹,，并且葉子節(jié)點(diǎn)中存放著整張表的行記錄數(shù)據(jù)，因此也讓聚簇索引的葉節(jié)點(diǎn)成為數(shù)據(jù)頁，每個數(shù)據(jù)頁都通過一個雙向鏈表來進(jìn)行鏈接,。而對于其他非聚簇索引而言,，葉子節(jié)點(diǎn)則指的是主索引，所以在通過非聚簇索引來作查詢的時候,，需要兩次索引查找,，但是InnoDB存儲引擎會利用自適應(yīng)性哈希索引來優(yōu)化這部分重復(fù)工作。

根據(jù)聚簇索引的原理不難發(fā)現(xiàn),，如果用戶的查詢是使用二級索引進(jìn)行范圍檢索的時候（實(shí)際情況中很大部分查詢是這種情況）,，如果結(jié)果沒有命中查詢緩存的話，勢必會造成大量的磁盤隨機(jī)IO訪問,。因?yàn)閷Χ壦饕M(jìn)行范圍檢索得出的一級索引本身是無序的,，再根據(jù)一級索引去檢索數(shù)據(jù)行就會出現(xiàn)對磁盤的大量隨機(jī)IO操作。

InnoDB會使用一種叫Multi-Range Read的優(yōu)化方式,，來降低隨機(jī)IO的讀取,。其原理就是先將二級索引范圍檢索得到的一級索引放入緩存進(jìn)行排序，在回表的時候就用有序的一級索引進(jìn)行查找,，從而將大量的隨機(jī)IO轉(zhuǎn)換成順序IO,。這個緩存的大小受限于系統(tǒng)變量read_rnd_buffer_size的大小，這個系統(tǒng)變量是客戶端級別的,，對其調(diào)整需要謹(jǐn)慎,。

在創(chuàng)建索引的時候，我們需要遵循一定的原則：

主鍵的選擇：因?yàn)镮nnoDB存儲引擎底層是通過聚簇索引來組織數(shù)據(jù)存儲的,，其中的一級索引就是當(dāng)前表的主鍵,，如果沒有定義主鍵，則會選擇表的非空的唯一性索引作為一級索引,，如果連這都沒有,，那系統(tǒng)會自動生成一個隱藏的一級索引。大多數(shù)情況下,，這個一級索引就是指的主鍵,。

定義主鍵的時候需要遵循兩個原則：選擇占用空間小的類型,，有序（單調(diào)遞增）,。

為主鍵選擇占用空間小的類型是因?yàn)槎壦饕娜~子節(jié)點(diǎn)存放的是一級索引，那一級索引如果占用很大空間就意味著所有的二級索引需要更大的空間來存儲,。主鍵有序是因?yàn)榫鄞厮饕龥Q定了在物理存儲的時候需要按照順序存儲一級索引和數(shù)據(jù)行,，如果主鍵無序，就會出現(xiàn)大量的頁分裂操作,，產(chǎn)生大量的內(nèi)存碎片,，降低寫入效率,。

列的選擇性：當(dāng)為某列添加索引的時候，需要考慮這個列是不是頻繁出現(xiàn)在查詢條件中,，這個列的選擇性是否足夠高,。選擇性不高的列，不但不會提高查詢性能,，反而會導(dǎo)致額外的存儲和索引維護(hù)工作。諸如性別,，狀態(tài)這樣的字段,，需要謹(jǐn)慎評估是否需要添加索引。

創(chuàng)建組合索引：在選擇索引的時候,，一個常見的錯誤是給每個需要添加索引的列都加上獨(dú)立的索引,，但是一般情況下，我們的查詢條件由表中的多列組成,，我們完全可以利用多列索引,，來覆蓋單列或者多列的查詢。需要使用多列索引的典型信號就是當(dāng)我們在分析SQL執(zhí)行計劃的時候,，extra列中出現(xiàn)using union,，using sort_union, using intersection提示信息。在添加索引的時候,，根據(jù)業(yè)務(wù)場景,，結(jié)合最左前綴原則，適當(dāng)?shù)奶砑佣嗔兴饕軌蝻@著提升查詢效率,。

需要注意的是多列索引的順序問題,，一個是各個列之間的順序問題，這個需要結(jié)合業(yè)務(wù)場景,，根據(jù)最左前綴原則,，來定義列的順序，如果不考慮排序和分組,，把選擇性最高的列放在列首通常是好的,，如果出現(xiàn)了ORDER BY子句，那索引列的順序應(yīng)該參考WHERE子句中索引列和ORDER BY子句索引列的順序,；

然后是各個列數(shù)據(jù)的升序倒序問題,，查詢的結(jié)果需要和索引定義的各個列的數(shù)據(jù)的升降序方向保持一致（這里的保持一致指多列多列索引所定義的數(shù)據(jù)的升降序和查詢結(jié)果中的數(shù)據(jù)升降序完全一致或者完全相反），才能使用到該索引的有序的優(yōu)勢,，不然還是需要額外的內(nèi)存排序,。舉個例子，針對某一查詢有ORDRE BY C1 DESC, C2 ASC, 創(chuàng)建索引INDEX(C1 DESC, C2 ASC) 和INDEX(C1 ASC, C2 DESC)都可以使用到索引的有序性而不用額外的內(nèi)存排序,。這里是調(diào)整多列索引自身的順序來滿足查詢數(shù)據(jù)所需順序,，從而使查詢的時候利用索引排序,。

這里還需要注意的一點(diǎn)是如果有ORDER BY子句，那只有ORDER BY子句中的所有列都來自同一張表才能保證查詢的時候使用索引來排序,，否則還是需要額外的內(nèi)存排序,。

最左前綴原則：索引中經(jīng)常提及的最左前綴原則，是指在多列索引中,，查詢語句的條件子句和分組排序子句會根據(jù)多列索引最左邊的列開始匹配,，如果查詢條件中沒有出現(xiàn)最左邊的列，則匹配失敗,，如果出現(xiàn)了,，而且是等值匹配，則依次往右進(jìn)行匹配,。如果查詢語句中不包含分組排序子句,，則此處等值的定義為等于子句或者IN子句或者OR+等于子句；如果包含分組排序子句,，那此處等值只能是等于子句,。當(dāng)然最左前綴原則也適用于針對字符串的LIKE查詢，如果是右匹配,，還是能使用到索引的,，但是做匹配或者全匹配就沒有辦法使用列上的索引了。

覆蓋索引：覆蓋索引是指某個查詢中WHERE子句和SELECT子句中涉及到的列都來自一個索引,。這樣在查詢的時候,，存儲引擎可以在檢索二級索引的時候獲取到所有需要查詢的列，從而避免再去檢索主鍵索引和數(shù)據(jù)行,。典型的應(yīng)用場景就是優(yōu)化分頁實(shí)現(xiàn),。可以先在子查詢中利用覆蓋索引查詢出主鍵的區(qū)間值,，再和外層查詢級聯(lián),，這樣就只有當(dāng)前分頁大小的數(shù)據(jù)需要回表查詢。

針對分頁還有一種優(yōu)化方式是利用已知的區(qū)間值（比如主鍵）來過濾,，然后每次查詢都通過該區(qū)間值來進(jìn)行過濾,，從而減少不必要的數(shù)據(jù)檢索，比如WHERE id > 100 and a=? limit 20這種方式,，但是這和傳統(tǒng)的分頁設(shè)計不符,，需要改造分頁查詢的設(shè)計，這也是需要權(quán)衡的地方,。

索引擴(kuò)展：索引擴(kuò)展（Index Extension）[3]是InnoDB存儲引擎針對二級索引的一個優(yōu)化,，存儲引擎內(nèi)部會對這個索引進(jìn)行擴(kuò)展，將一級索引對應(yīng)的列自動添加到二級索引后面,，從而生成更高效的查詢執(zhí)行計劃,，提高性能,。

舉個例子，表t的主鍵是KEY(C1,C2),，其中定義了一個二級索引是INDEX（C3）,，通過索引擴(kuò)展過后，該二級索引就被優(yōu)化擴(kuò)展為 INDEX（C3,C1,C2）的效果,。比如有查詢SELECT C4, C5 FROM t WHERE C3=1 and C1=2 就能利用到C3和主鍵兩個索引,。索引擴(kuò)展只適用于ref, range, index_merge方式的數(shù)據(jù)掃描查詢。

索引下推：索引下推（Index Condition Pushdown）[4]是針對二級索引的另一個優(yōu)化,，能夠減少存儲引擎回表的次數(shù),，降低服務(wù)器端和存儲引擎端傳輸?shù)臄?shù)據(jù)量，默認(rèn)開啟,。其原理就是在存儲引擎根據(jù)某個索引回表查詢數(shù)據(jù)行之前，先根據(jù)where子句中該索引的其他非命中的索引列進(jìn)行過濾,，最后再回表查詢數(shù)據(jù)行,，最終返回數(shù)據(jù)到服務(wù)器端。而未使用索引下推的情況下,，對該索引的非命中索引列的過濾是在服務(wù)器端進(jìn)行的,，無法在存儲引擎端過濾，更不用說在存儲引擎回表之前過濾,。該優(yōu)化適用于range, ref, eq_ref, ref_or_null方式的數(shù)據(jù)掃描查詢,。

為了驗(yàn)證我們的索引是否能和預(yù)期一致，需要分析查詢執(zhí)行計劃,。EXPLAIN輸出的每一列的意思不用多說,，這里主要針對部分需要注意的值進(jìn)行特殊說明。

1. type列值中的index指的是MySQL掃描表時按照索引的次序進(jìn)行,，而不是行,，本質(zhì)上還是全表掃描，只是避免了額外的內(nèi)存排序,，但是如果同時extra列的值是using index,，那說明正在使用覆蓋索引掃描，不會回表查詢具體數(shù)據(jù)行,，效率很高,。

2. key列 + key_len列能夠定位組合索引中哪些列沒有被使用到。key列指MySQL決定使用哪個索引來優(yōu)化對該表的訪問,，ken_len列指使用到該索引的字節(jié)數(shù),。

可以通過ken_len的長度來反推使用到該索引的哪些列，這里需要注意的是索引每項(xiàng)的長度計算方式,，若列是非NULL定長類型,，則該列索引長度為該定長類型的長度,，例入INT類型為4 byte；如果該列可為NULL,，則還需要額外的1byte來保存該信息,；變長類型除開該變長本身類型的長度以外，需要額外的2 byte來保存變長相關(guān)信息,；如果是字符類型,，則需要考慮字符集因素，例如CHAR（10）NOT NULL UTF8則key_len是10 × 3 + 2 = 32 byte,，因?yàn)閁TF8字符集下每個字符需要3 byte來存儲,。

3. extra列有很多的提示信息，充分理解其含義可以引導(dǎo)優(yōu)化SQL語句,。using index指使用了覆蓋索引方式掃描數(shù)據(jù),，using index condition 使用了索引下推優(yōu)化，using sort_union,，using union,，using intersect 使用了索引合并優(yōu)化，需要考慮是否應(yīng)該合并多個單列索引為組合索引,；using where指服務(wù)器端從存儲引擎獲取了數(shù)據(jù)過后還進(jìn)行了額外的過濾操作,，需要考慮是否可以將非索引字段添加為索引字段（根據(jù)實(shí)際情況而定）；

根據(jù)EXPLAIN的輸出,，我們很快可以定位到SQL語句中的問題所在,，以下羅列了部分容易被忽視的索引失敗案例：

1. 查詢優(yōu)化器的成本模型認(rèn)為全表掃描會比使用索引進(jìn)行檢索更快，這個和很多因素有關(guān),，比如表數(shù)據(jù)量大小,，索引列的選擇性是否足夠高，以及查詢優(yōu)化器成本模型本身的一些缺陷,。我們可以使用FORCE INDEX或者USE INDEX去模擬其他因素導(dǎo)致的某些索引無法命中情況,。

2. 索引列不獨(dú)立，即索引列不能是表達(dá)式的一部分,，比如使用了函數(shù),，運(yùn)算符等。比如CONCAT(“Kobe”,，C1) = “Kobe Bryant”,，即使C1列上有索引，由于列不完整,，也會導(dǎo)致索引無法使用,。

3. 索引列類型不匹配。這是在做SQL優(yōu)化中經(jīng)常會遇到的情況,，要么是傳入的參數(shù)和索引列類型不匹配,，要么是在做連接操作的時候兩個連接的列類型不同,，導(dǎo)致索引無法命中。

4. ORDER BY或者GROUP BY子句中的列來自不同的表,，會導(dǎo)致ORDER BY 或者GROUP BY子句的列無法使用索引,，從而導(dǎo)致需要額外的內(nèi)存排序。

5. 前面提到的組合索引的順序問題,，如果不滿足的話,，也是無法命中索引的。需要注意不同情況下最左前綴原則的等值處理