接觸MySQL數(shù)據(jù)庫的小伙伴一定避不開索引，索引的出現(xiàn)是為了提高數(shù)據(jù)查詢的效率，就像書的目錄一樣。

某一個SQL查詢比較慢，你第一時間想到的就是“給某個字段加個索引吧”，那么索引是什么？是如何工作的呢？一起靜下心來，耐心看完這篇文章吧，干貨不啰嗦，相信你一定會有所收獲。

一、索引模型

模型也就是數(shù)據(jù)結(jié)構(gòu)，常見的三種模型分別是哈希表、有序數(shù)組和搜索樹。

了解MySQL的朋友已經(jīng)知道，現(xiàn)在MySQL默認使用的是InnoDB存儲引擎，使用的是B+樹索引數(shù)據(jù)結(jié)構(gòu)。所以這個話題我們簡單介紹，不作過多篇幅解釋，只需了解為什么InnoDB選擇B+樹作為索引的數(shù)據(jù)結(jié)構(gòu)。

1.1 哈希表

key-value鍵值對存儲結(jié)構(gòu)，哈希思路非常easy，給定key，通過哈希函數(shù)命中value。了解HashMap的都知道，哈希表不可避免的會發(fā)生同值沖突，所以需要通過鏈表跟在數(shù)組后面。

哈希表的特點是插入速度很快，只需要通過hash找到對應(yīng)數(shù)組，發(fā)生沖突就在鏈表往后追加。但缺點也正是因為無序，導(dǎo)致查詢速度很慢，幾乎全部掃描一遍。

1.2 有序數(shù)組

有序數(shù)組非常簡單，遞增順序保存。查詢時可以使用二分法，時間復(fù)雜度O(log(N))，但是更新數(shù)據(jù)就很麻煩，往中間插入一個數(shù)據(jù)就必須挪動后面所有的記錄，成本很高。

1.3 B+樹

B+樹衍生于二叉搜索樹，沒錯，大學(xué)數(shù)據(jù)結(jié)構(gòu)中的二叉搜索樹，課堂的熟悉感是不是都回來了～

二叉搜索樹的特點是：每個節(jié)點的左兒子小于父節(jié)點，右兒子大于父節(jié)點。所以查詢搜索的時間復(fù)雜度是O(log(N))，為了維持O(log(N))查詢復(fù)雜度，需要保證這棵樹是平衡二叉樹，所以更新的時間復(fù)雜度也是O(log(N))。

但是平衡二叉樹的缺點在于隨著數(shù)據(jù)的增加，整棵樹的層級越來越高，考慮葉子節(jié)點的數(shù)據(jù)總是在內(nèi)存中，那么樹層級越多意味著需要多次的磁盤尋址。一棵100萬數(shù)據(jù)量節(jié)點的平衡二叉樹，樹高H=log2(N+1) - 1=log2(1000000+1)-1=20，一次查詢可能需要訪問20個數(shù)據(jù)節(jié)點，磁盤隨機讀一個數(shù)據(jù)塊大約10ms，總計需要20個10ms時間找到一行數(shù)據(jù)，難以接受！

為了減少盡量少的讀磁盤，二叉樹就演變?yōu)榱薔叉樹，N是多少，取決于數(shù)據(jù)塊的大小。如果MySQL數(shù)據(jù)表中你創(chuàng)建了一個整數(shù)類型的索引，這個N差不多是1200，樹高是4時，整棵樹可以存儲1200的3次方，也就是17億數(shù)據(jù)，查找一個數(shù)據(jù)只需要訪問3次磁盤，很nice~

二、索引維護

每個索引在InnoDB中都對應(yīng)一棵B+樹。

根據(jù)葉子節(jié)點的內(nèi)容，索引類型分為主鍵索引和非主鍵索引。主鍵索引又稱為聚簇索引，葉子節(jié)點中存儲的是整行數(shù)據(jù)。

非主鍵索引的葉子節(jié)點中存儲的是主鍵的值。所以，如果你的SQL語句查詢時用到的索引不是主鍵，那么就會有一次普通索引找到葉子節(jié)點中的主鍵ID，再進行回表獲取數(shù)據(jù)。因此，我們在查詢時應(yīng)盡量使用主鍵查詢。

B+樹為了維護索引有序性，是有代價的，如果新插入的數(shù)據(jù)在數(shù)據(jù)頁中有空位置且后面沒有數(shù)據(jù)，可以直接插入；如果在500和700中間插入600，則需要挪動700及后面的數(shù)據(jù)，空出位置；更糟糕的是，如果數(shù)據(jù)頁已經(jīng)滿了，則需要新申請一個新的數(shù)據(jù)頁，然后挪動數(shù)據(jù)，這就是頁分裂。有分裂就有合并，對應(yīng)的就是刪除數(shù)據(jù)場景。性能很受影響！

從性能角度看，如果采用主鍵自增剛好符合索引有序的特點，每次插入數(shù)據(jù)都是追加，不會挪動數(shù)據(jù)也不會觸發(fā)頁分裂。

從存儲空間角度看，主鍵長度越小，索引的葉子節(jié)點就越小，占用空間越小。如整型只要4個字節(jié)，長整型（bigint）就是8字節(jié)。這也是為什么不建議大家用隨機數(shù)作為主鍵的原因。

三、索引利用

我們以一條SQL語句執(zhí)行流程來看索引是怎么提高查詢效率的。

selcect * from T where k between 1 and 3;

假定主鍵索引和k索引樹分別是：

這條SQL查詢的執(zhí)行流程：

1、在k索引樹上找到k=1的記錄，取得主鍵ID=1

2、到主鍵索引樹上查到ID=1對應(yīng)的V1

3、在k索引樹繼續(xù)取下一個值k=3，取得主鍵ID=4

4、到主鍵索引樹上查到ID=4對應(yīng)的V4

5、在k索引樹上取下一個值k=5，不滿足查詢條件，循環(huán)結(jié)束。

可以看到，這條SQL查詢了k索引樹3次，回表2次。這里你一定會想，能不能優(yōu)化索引，避免回表呢？

如果這條SQL改為selcect id from T where k between 1 and 3;那么就不需要回表了，這時k索引也稱為覆蓋索引，也就是查詢的字段已經(jīng)在k索引中，無需回表了。

你可能會立即反問，業(yè)務(wù)中怎么可能剛剛好只查主鍵呢，那就需要根據(jù)實際情況考慮建立聯(lián)合索引。

3.1 最左前綴原則

每一種查詢都新加一個索引，無疑是對索引的浪費。

我們來看（name，age）這個聯(lián)合索引：

當你查詢姓名為“李五”時，可以快速定位到id3，然后向后遍歷得到所要的結(jié)果。

如果你查詢的姓名第一個字是“李”時，可以定位到id2，然后向后遍歷得到所要的結(jié)果。

所以最左前綴，不僅是聯(lián)合索引的最左N個字段，也可以是字符串的最左N個字符。

這時你需要根據(jù)具體的業(yè)務(wù)場景，考慮聯(lián)合索引的順序問題，當有(name,age)這個聯(lián)合索引，就無需再單獨建立（name）索引了，但是如果查詢條件里面只有age，是無法使用(name,age)索引了。

3.2 索引下推

在MySQL5.6之后，引入了索引下推優(yōu)化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾不滿足條件的記錄，減少回表次數(shù)。

SQL語句：

select * from T where name like "李%" and age = 25;

無索引下推機制時，根據(jù)最左前綴匹配原則，找到“李%”的name之后，就回表查詢主鍵索引，然后過濾age字段。索引下推機制下，在尋找“李%”name時，會直接判斷age=25符合條件的數(shù)據(jù)，然后回表查詢主鍵索引，減少了2次回表次數(shù)。

3.3 唯一索引

唯一索引的屬性列不能出現(xiàn)重復(fù)的數(shù)據(jù)，但是允許數(shù)據(jù)為 NULL，一張表允許創(chuàng)建多個唯一索引。建立唯一索引的目的大部分時候都是為了該屬性列的數(shù)據(jù)的唯一性，而不是為了查詢效率。

知道了唯一索引的概念之后，你也許已經(jīng)猜到唯一索引的性能可能比不上普通索引，一起來看背后的原因是什么？

我們從查詢和更新2個角度來分析，唯一索引和普通索引的區(qū)別。

查詢過程

SQL語句：

select id from T where k = 3;

普通索引：查找到滿足第一個k=3記錄后，向后繼續(xù)尋找，直到第一個不滿足k=3的記錄。

唯一索引：由于索引數(shù)據(jù)的唯一性，查找到第一個滿足k=3條件的記錄后，停止檢索。

對于內(nèi)存操作來說，兩者的性能差別幾乎可以忽略。

更新過程

當更新一個數(shù)據(jù)頁時，如果數(shù)據(jù)頁在內(nèi)存中就直接更新，否則InnoDB會將這些更新操作緩存在change buffer中，這樣就不需要從磁盤中讀入這個數(shù)據(jù)頁了。在下次查詢訪問這個數(shù)據(jù)頁時，會觸發(fā)change buffer的merge操作，持久化到磁盤中。除了訪問數(shù)據(jù)頁觸發(fā)merge時，系統(tǒng)有后臺線程也會定期merge，在數(shù)據(jù)庫正常關(guān)閉時，也會執(zhí)行merge操作。將更新操作先記錄在change buffer，就是為了減少讀磁盤，提升SQL語句執(zhí)行速度。

唯一索引更新不能使用change buffer，而普通索引可以使用。

此時，再執(zhí)行一條更新語句時，第一種情況時更新的數(shù)據(jù)頁在內(nèi)存中，這時：

唯一索引：找到需要插入的位置，判斷有沒有沖突，執(zhí)行插入，結(jié)束；

普通索引：找到需要插入的位置，插入這個值，結(jié)束。

可以看出，數(shù)據(jù)頁在內(nèi)存中的兩者操作幾乎沒有性能差別。

重點來了，如果更新的數(shù)據(jù)頁不在內(nèi)存中，這時：

唯一索引：將數(shù)據(jù)頁讀入內(nèi)存，判斷有沒有沖突，執(zhí)行插入，結(jié)束；

普通索引：將更新記錄在change buffer，結(jié)束。

將數(shù)據(jù)從磁盤讀入內(nèi)存涉及隨機IO的訪問，是數(shù)據(jù)庫里面成本最高的操作之一。change buffer因為減少了隨機磁盤訪問，所以對更新性能的提升是會很明顯的。

舉個例子，大家感受更深，如果你的業(yè)務(wù)庫有大量插入數(shù)據(jù)的操作，內(nèi)存命中率下降明顯，系統(tǒng)就會經(jīng)常處于阻塞狀態(tài)，更新語句都堵住了。有可能就是因為普通索引改為了唯一索引。

那么怎么判斷是不適合用唯一索引呢？

對于寫多讀少的業(yè)務(wù)來說，寫完之后立即讀的概率比較小，此時change buffer效果很好，推薦使用普通索引。

對于一個業(yè)務(wù)寫完之后立即就要訪問，將更新操作記錄在change buffer之后，由于馬上訪問這個數(shù)據(jù)頁，會立即出觸發(fā)merge操作，增加隨機訪問IO次數(shù)，此時，change buffer反而起到了反作用，增加了維護代價，可以使用唯一索引。

四、索引實踐

4.1 order by語句

給定一個SQL語句：

select city,name,age from T where city = "杭州" order by name limit 1000;

此時你已經(jīng)很清楚，為了避免全表掃描，需要在city字段上加索引，我們用explain命令看一下這條SQL執(zhí)行情況：

Extra中的“Using filesort”表示就是需要排序，MySQL會分配一塊內(nèi)存用于排序，稱為sort_buffer。這條SQL的執(zhí)行流程是：

1、初始化sort_buffer，放入name、city、age三個字段

2、從索引city找到第一個滿足city="杭州"的主鍵ID

3、從主鍵索引中取出行數(shù)據(jù)，選取select條件中的city、name、age三個字段，放入sort_buffer中

4、從索引city中取下一個記錄的主鍵id

5、重復(fù)3、4直到不滿足city的查詢條件

6、對sort_buffer中的數(shù)據(jù)按照name進行快速排序

7、按照排序結(jié)果取前1000行返回給用戶

通過一張圖來看流程：

但是sort_buffer不是無限大的，如果排序數(shù)據(jù)量太大，內(nèi)存就會放不下，就會用到磁盤臨時文件進行排序。

合并多個臨時文件一般使用歸并排序算法。MySQL的設(shè)計思想是：如果內(nèi)存夠用，盡量多利用內(nèi)存，減少磁盤訪問。

4.2 索引選擇

其實對于4.1中的SQL語句是可以優(yōu)化的，不知道你猜到了沒有，SQL中過濾條件是city和name，我們可以創(chuàng)建一個(city，name)的聯(lián)合索引，這樣能夠保證從city索引中取出來的數(shù)據(jù)，天然就是按照name遞增排序的，此時的流程變?yōu)榱耍?/p>

是不是效果好多了，而且查詢過程不需要臨時表，也不需要排序，我們用explain來驗證下：

可以看到，Extra中沒有Using filesort了，也就是不需要排序了。而且(city,name)聯(lián)合索引本身有序，掃描行數(shù)由之前的4000行減少到1000行。

你是不是覺得到此為止了？還可以再優(yōu)化下哦，如果創(chuàng)建一個(city,name,age)的聯(lián)合索引，那么這個聯(lián)合索引對于這個SQL來說就成了覆蓋索引，流程簡化成了這樣：

來看一下explain效果：

可以看到，Extra中多了“Using index”，表示使用了覆蓋索引，性能上快了很多。

以上是索引選擇的一個例子，在大家日常業(yè)務(wù)開發(fā)中會有很多遇到索引的情況，一般可考慮：

?被頻繁查詢的字段：我們創(chuàng)建索引的字段應(yīng)該是查詢操作非常頻繁的字段。

?被作為條件查詢的字段：被作為 WHERE 條件查詢的字段，應(yīng)該被考慮建立索引。

?頻繁需要排序的字段：索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間。

?被經(jīng)常頻繁用于連接的字段：經(jīng)常用于連接的字段可能是一些外鍵列，對于外鍵列并不一定要建立外鍵，只是說該列涉及到表與表的關(guān)系。對于頻繁被連接查詢的字段，可以考慮建立索引，提高多表連接查詢的效率。

?如果一個字段不被經(jīng)常查詢，反而被經(jīng)常修改，那么就更不應(yīng)該在這種字段上建立索引了。

?盡可能的考慮建立聯(lián)合索引而不是單列索引。每個索引都需要維護一棵B+樹，索引占用的空間也是很多的，且修改索引時，耗費的時間也是較多的。如果是聯(lián)合索引，多個字段在一個索引上，那么將會節(jié)約很大磁盤空間，且修改數(shù)據(jù)的操作效率也會提升。

4.3 索引失效

索引失效也是慢查詢的主要原因之一，常見的導(dǎo)致索引失效的情況有下面這些：

?使用 SELECT * 進行查詢，優(yōu)化器可能會認為全表掃描比索引更高效。

?創(chuàng)建了聯(lián)合索引，但查詢條件未遵守最左匹配原則;

?在索引列上進行計算、函數(shù)、類型轉(zhuǎn)換等操作;

?以 % 開頭的 LIKE 查詢比如 like '%abc';

?查詢條件中使用 or，且 or 的前后條件中有一個列沒有索引，涉及的索引都不會被使用到;

這里可以展開說明函數(shù)和類型轉(zhuǎn)換為什么會導(dǎo)致索引失效。

案例一：函數(shù)操作

SQL語句：

select count(*) from T where month(modified) = 6

這條SQL想要檢索modified修改時間在6月份的數(shù)據(jù)量有多少。

即使modified字段上有索引，你仍會發(fā)現(xiàn)SQL語句執(zhí)行了很久。因為MySQL規(guī)定：對字段做了函數(shù)計算，就不能使用索引。為什么條件是where modified='2024-06-18’的時候可以用上索引，而改成where month(modified)=6的時候就不行了？

其實也很簡單，modified索引樹的每個節(jié)點存儲的是“2024-06-18”這樣的數(shù)據(jù)，month(modified)之后的結(jié)果是7，無法在索引樹中進行檢索。最終MySQL選擇了全表掃描。

案例二：類型轉(zhuǎn)換

給定SQL語句：

select * from T where age = 20;

設(shè)定age這個字段上有索引，創(chuàng)建表語句中age是varchar(10)，通過explain可以發(fā)現(xiàn)，這條SQL走了全表掃描，因為輸入的age參數(shù)是整數(shù)，需要做類型轉(zhuǎn)換。

對于MySQL優(yōu)化器來說，這條SQL等同于：

select * from T where CAST(a ge AS signed int) = 20;

這樣就又回到了對索引字段做函數(shù)操作，優(yōu)化器會放棄走樹搜索功能。

案例三：編碼轉(zhuǎn)換

如果你需要做兩張表的聯(lián)表查詢，但是一張表的編碼是utf8，另一張表的編碼格式是utf8mb4，那么在通過join字段連接時用不上關(guān)聯(lián)字段的索引。utf8中的一個英文字符占用一個字節(jié)的存儲空間，一個中文占用三個字節(jié)的存儲空間，不支持4個字節(jié)的存儲，而utf8mb4支持4個字節(jié)的存儲，可以更好的支持emoji表情。

所以在連表查詢時，MySQL會先將utf8字符串轉(zhuǎn)換為utf8mb4字符集，再做比較。SQL語句變成了：

select * from T1 where CONVERT(T1.age USING utf8mb4)=T2.age.value;

這樣又回到了對索引字段做函數(shù)操作，走不到索引。

五、QA

（1）B樹與B+樹的區(qū)別

數(shù)據(jù)結(jié)構(gòu)上：B樹所有節(jié)點都可包含記錄，而B+樹只有葉子節(jié)點存儲數(shù)據(jù)，非葉子節(jié)點只用于索引，不存儲實際數(shù)據(jù)。

更新操作上：B+樹執(zhí)行更新需要維護索引的變化以保持有序。

查詢性能上：B樹通過二分查找，而且是跨層查找，理論上，需要命中的數(shù)據(jù)離根節(jié)點越近性能越高（最好的性能是O(1)），否則需要多次磁盤訪問，性能較低。B+樹是數(shù)據(jù)存儲在葉子節(jié)點，通過鏈表定位數(shù)據(jù)的時間復(fù)雜度是O(log(N))。

使用場景上：B樹適合隨機訪問的場景，比如文件系統(tǒng)索引；B+樹適合范圍查詢和順序查詢，比如數(shù)據(jù)庫索引。

（2）explain語句

explain語句也稱為獲取MySQL執(zhí)行計劃信息，展示一條SQL的執(zhí)行方案。

explain語句執(zhí)行結(jié)構(gòu)一共有12列，每一列什么含義和如何分析，百度很多，這里不展開解釋了。大家也不需要刻意記每個字段什么含義，需要explain時百度下，次數(shù)多了自然也就熟悉了。

（3）為什么要限制每張表上的索引數(shù)量？

索引可以提高查詢效率，是不是一張表上索引越多越好呢，其實不然。索引可以提高效率也可以降低效率，因為MySQL優(yōu)化器在選擇如何優(yōu)化SQL查詢時，會對每一個索引進行評估，以生成一個最好的執(zhí)行計劃，如果同時有很多索引都可以使用，就會增加MySQL優(yōu)化器生成執(zhí)行計劃的時間，同樣會降低查詢性能。所以一般建議單表索引不超過5個，根據(jù)實際頻繁查詢的字段設(shè)置索引。

（4）索引失效的進一步擴展

我們已經(jīng)知道MySQL遇到函數(shù)轉(zhuǎn)換會使索引失效，那么假定主鍵id是int類型，如果執(zhí)行下面SQL語句，會導(dǎo)致全表掃描嗎？

select * from T where id = "65535";

你可以去數(shù)據(jù)庫嘗試下這條SQL，然后通過explain驗證下。其實結(jié)論很簡單，這里進行的是查詢條件的value值函數(shù)轉(zhuǎn)換（將varchar轉(zhuǎn)換為int），并不是在索引字段id上，自然是命中索引的。

大家在日常中還有遇到什么坑或者經(jīng)驗，歡迎評論區(qū)分享

審核編輯 黃宇