之前寫過幾篇關(guān)于mysql相關(guān)的文章，今天探討一下索引底層原理。

慢sql優(yōu)化思路及使用規(guī)范

Mysql引擎

MySQL優(yōu)化策略

mysql多線程update死鎖問題

Mysql表空間碎片釋放

1 InnoDB 存儲數(shù)據(jù)原理

MySQL 把數(shù)據(jù)存儲和查詢操作抽象成了存儲引擎角寸，不同的存儲引擎朝聋，對數(shù)據(jù)的存儲和讀取方式各不相同虐沥。MySQL 支持多種存儲引擎腿宰，并且可以以表為粒度設(shè)置存儲引擎。因?yàn)橹С质聞?wù)覆劈，我們最常使用的是 InnoDB保礼。

雖然數(shù)據(jù)保存在磁盤中，但其處理是在內(nèi)存中進(jìn)行的责语。為了減少磁盤隨機(jī)讀取次數(shù)炮障，InnoDB 采用頁而不是行的粒度來保存數(shù)據(jù)，即數(shù)據(jù)被分成若干頁坤候，以頁為單位保存在磁盤中胁赢。InnoDB 的頁大小，一般是 16KB白筹。

各個(gè)數(shù)據(jù)頁組成一個(gè)雙向鏈表智末，每個(gè)數(shù)據(jù)頁中的記錄按照主鍵順序組成單向鏈表；每一個(gè)數(shù)據(jù)頁中有一個(gè)頁目錄徒河，方便按照主鍵查詢記錄系馆。數(shù)據(jù)頁的結(jié)構(gòu)如下：

image.png

頁目錄通過槽把記錄分成不同的小組，每個(gè)小組有若干條記錄虚青。如圖所示它呀，記錄中最前面的小方塊中的數(shù)字螺男，代表的是當(dāng)前分組的記錄條數(shù)棒厘，最小和最大的槽指向 2 個(gè)特殊的偽記錄。有了槽之后下隧，我們按照主鍵搜索頁中記錄時(shí)奢人，就可以采用二分法快速搜索，無需從最小記錄開始遍歷整個(gè)頁中的記錄鏈表淆院。

舉一個(gè)例子何乎，如果要搜索主鍵（PK）=15 的記錄：

• 先二分得出槽中間位是 (0+6)/2=3，看到其指向的記錄是 12＜15，所以需要從 #3 槽后繼續(xù)搜索記錄支救；
• 再使用二分搜索出 #3 槽和 #6 槽的中間位是 (3+6)/2=4.5 取整 4抢野，#4 槽對應(yīng)的記錄是 16＞15，所以記錄一定在 #4 槽中各墨；
• 再從 #3 槽指向的 12 號記錄開始向下搜索 3 次指孤，定位到 15 號記錄。

2 聚簇索引和二級索引

說到索引贬堵，頁目錄就是最簡單的索引，是通過對記錄進(jìn)行一級分組來降低搜索的時(shí)間復(fù)雜度。但泌辫，這樣能夠降低的時(shí)間復(fù)雜度數(shù)量級阳堕，非常有限。當(dāng)有無數(shù)個(gè)數(shù)據(jù)頁來存儲表數(shù)據(jù)的時(shí)候蒸殿，我們就需要考慮如何建立合適的索引筷厘，才能方便定位記錄所在的頁。

2.1 B+ 樹

為了解決這個(gè)問題伟桅，InnoDB 引入了 B+ 樹敞掘。如下圖所示，B+ 樹是一棵倒過來的樹：

image.png

B+ 樹的特點(diǎn)包括：

• 最底層的節(jié)點(diǎn)叫作葉子節(jié)點(diǎn)楣铁，用來存放數(shù)據(jù)玖雁；

• 其他上層節(jié)點(diǎn)叫作非葉子節(jié)點(diǎn)，僅用來存放目錄項(xiàng)盖腕，作為索引赫冬；

• 非葉子節(jié)點(diǎn)分為不同層次，通過分層來降低每一層的搜索量溃列；

• 所有節(jié)點(diǎn)按照索引鍵大小排序劲厌，構(gòu)成一個(gè)雙向鏈表，加速范圍查找听隐。

關(guān)于B+樹的介紹补鼻，見之前的文章Java 二叉樹、紅黑樹雅任、B+樹

2.2 B+樹索引的插入過程 和 快速查找主鍵

• B+ 樹索引的插入過程
  B+Tree索引的數(shù)據(jù)存儲在葉子節(jié)點(diǎn)上风范，每個(gè)葉子節(jié)點(diǎn)默認(rèn)的大小是16KB。

  當(dāng)新記錄插入到InnoDB聚簇索引中時(shí)沪么，如果按順序插入索引記錄（升序或降序）硼婿，當(dāng)達(dá)到葉子節(jié)點(diǎn)最大的容量時(shí)，下一條記錄就會寫到新的的頁中禽车。葉子節(jié)點(diǎn)可使用的容量為總?cè)萘康?5/16寇漫，InnoDB會留1/16的空間刊殉，以備將來插入和更新索引記錄時(shí)使用， 如果以隨機(jī)順序插入記錄州胳，則頁面的容量為1/2到15/16之間记焊。

  可以設(shè)置 innodb_page_size 來調(diào)整頁的大小，支持 64KB, 32KB, 16KB (默認(rèn)), 8KB, 和4KB栓撞。

• B+ 樹如何實(shí)現(xiàn)快速查找主鍵
  比如亚亲，我們要搜索 PK=4 的數(shù)據(jù)，通過根節(jié)點(diǎn)中的索引可以知道數(shù)據(jù)在第一個(gè)記錄指向的 2 號頁中腐缤，通過 2 號頁的索引又可以知道數(shù)據(jù)在 5 號頁捌归，5 號頁就是實(shí)際的數(shù)據(jù)頁，然后再通過二分法查找頁目錄馬上可以找到記錄的指針岭粤。

2.3 聚簇索引

因此惜索，InnoDB 使用 B+ 樹，既可以保存實(shí)際數(shù)據(jù)剃浇，也可以加速數(shù)據(jù)搜索巾兆，這就是聚簇索引。如果把上圖葉子節(jié)點(diǎn)下面方塊中的省略號看作實(shí)際數(shù)據(jù)的話虎囚，那么它就是聚簇索引的示意圖角塑。由于數(shù)據(jù)在物理上只會保存一份，所以包含實(shí)際數(shù)據(jù)的聚簇索引只能有一個(gè)淘讥。

InnoDB 會自動使用主鍵（唯一定義一條記錄的單個(gè)或多個(gè)字段）作為聚簇索引的索引鍵（如果沒有主鍵圃伶，就選擇第一個(gè)不包含 NULL 值的唯一列）。上圖方框中的數(shù)字代表了索引鍵的值蒲列，對聚簇索引而言一般就是主鍵窒朋。

2.4 二級索引

為了實(shí)現(xiàn)非主鍵字段的快速搜索，就引出了二級索引蝗岖，也叫作非聚簇索引侥猩、輔助索引。二級索引抵赢，也是利用的 B+ 樹的數(shù)據(jù)結(jié)構(gòu)欺劳，如下圖所示：

image.png

這次二級索引的葉子節(jié)點(diǎn)中保存的不是實(shí)際數(shù)據(jù)，而是主鍵和對應(yīng)的索引列铅鲤，獲得主鍵值后去聚簇索引中獲得數(shù)據(jù)行划提。這個(gè)過程就叫作回表。

比如彩匕，有個(gè)索引是針對用戶名字段創(chuàng)建的腔剂，索引記錄上面方塊中的字母是用戶名媒区，按照順序形成鏈表驼仪。如果我們要搜索用戶名為 b 的數(shù)據(jù)掸犬，經(jīng)過兩次定位可以得出在 #5 數(shù)據(jù)頁中，查出所有的主鍵為 7 和 6绪爸，再拿著這兩個(gè)主鍵繼續(xù)使用聚簇索引進(jìn)行兩次回表得到完整數(shù)據(jù)湾碎。

2.5 額外創(chuàng)建二級索引的代價(jià)

創(chuàng)建二級索引的代價(jià)，主要表現(xiàn)在維護(hù)代價(jià)奠货、空間代價(jià)和回表代價(jià)三個(gè)方面介褥。

首先是維護(hù)代價(jià)。創(chuàng)建 N 個(gè)二級索引递惋，就需要再創(chuàng)建 N 棵 B+ 樹柔滔，新增數(shù)據(jù)時(shí)不僅要修改聚簇索引，還需要修改這 N 個(gè)二級索引萍虽。

頁中的記錄都是按照索引值從小到大的順序存放的睛廊，新增記錄就需要往頁中插入數(shù)據(jù)，現(xiàn)有的頁滿了就需要新創(chuàng)建一個(gè)頁杉编，把現(xiàn)有頁的部分?jǐn)?shù)據(jù)移過去超全，這就是頁分裂；如果刪除了許多數(shù)據(jù)使得頁比較空閑邓馒，還需要進(jìn)行頁合并嘶朱。頁分裂和合并，都會有 IO 代價(jià)光酣，并且可能在操作過程中產(chǎn)生死鎖疏遏。

其次是空間代價(jià)。雖然二級索引不保存原始數(shù)據(jù)救军，但要保存索引列的數(shù)據(jù)改览，所以會占用更多的空間＄脱裕可以使用下面的 SQL 查看數(shù)據(jù)和索引占用的磁盤：

SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_NAME='person'

最后是回表的代價(jià)宝当。二級索引不保存原始數(shù)據(jù)，通過索引找到主鍵后需要再查詢聚簇索引胆萧，才能得到我們要的數(shù)據(jù)庆揩。

2.6 聯(lián)合索引數(shù)據(jù)結(jié)構(gòu)

聯(lián)合索引中其實(shí)保存了多個(gè)索引列的值，對于頁中的記錄先按照字段 1 排序跌穗，如果相同再按照字段 2 排序订晌，如圖所示：

image.png

圖中，葉子節(jié)點(diǎn)每一條記錄的第一和第二個(gè)方塊是索引列的數(shù)據(jù)蚌吸，第三個(gè)方塊是記錄的主鍵锈拨。如果我們需要查詢的是索引列索引或聯(lián)合索引能覆蓋的數(shù)據(jù)，那么查詢索引本身已經(jīng)“覆蓋”了需要的數(shù)據(jù)羹唠，不再需要回表查詢奕枢。因此娄昆，這種情況也叫作索引覆蓋。

所以盡量不要在 SQL 語句中 SELECT *缝彬，而是 SELECT 必要的字段萌焰，甚至可以考慮使用聯(lián)合索引來包含我們要搜索的字段，既能實(shí)現(xiàn)索引加速谷浅，又可以避免回表的開銷扒俯。

一句話總結(jié)：考慮到索引的維護(hù)代價(jià)、空間占用和查詢時(shí)回表的代價(jià)一疯，不能認(rèn)為索引越多越好撼玄。索引一定是按需創(chuàng)建的，并且要盡可能確保足夠輕量墩邀。一旦創(chuàng)建了多字段的聯(lián)合索引互纯，我們要考慮盡可能利用索引本身完成數(shù)據(jù)查詢，減少回表的成本磕蒲。

2.7 為什么主鍵通常建議使用自增id呢留潦？

聚簇索引的數(shù)據(jù)的物理存放順序與索引順序是一致的，即：只要索引是相鄰的辣往，那么對應(yīng)的數(shù)據(jù)一定也是相鄰地存放在磁盤上的兔院，當(dāng)前的頁寫滿的時(shí)候，下一條記錄就寫在新的頁中站削，當(dāng)不斷的插入數(shù)據(jù)是坊萝，它只需要順序的一頁一頁地寫，索引結(jié)構(gòu)相對緊湊许起，磁盤碎片少十偶，寫入效率高；當(dāng)讀取數(shù)據(jù)的時(shí)候园细，也可以按順序讀取惦积，充分利用了局部性的優(yōu)勢，大大提高了讀取效率猛频。
而如果主鍵不是自增id狮崩，那么可以想象，它將會不斷地調(diào)整數(shù)據(jù)的物理地址鹿寻、分頁睦柴，當(dāng)然也有其他一些措施來減少這些操作，但卻無法徹底避免毡熏。但坦敌，如果是自增的，那就簡單了，它只需要一頁一頁地寫狱窘，索引結(jié)構(gòu)相對緊湊杜顺，磁盤碎片少，效率也高训柴。

3 索引開銷的最佳實(shí)踐

3.1 不是所有針對索引列的查詢都能用上索引

第一，索引只能匹配列前綴妇拯。

//比如下面的 LIKE 語句幻馁，搜索 name 后綴為 name123 的用戶無法走索引，執(zhí)行計(jì)劃的 type=ALL 代表了全表掃描
EXPLAIN SELECT * FROM person WHERE NAME LIKE '%name123' LIMIT 100

image.png

//把百分號放到后面走前綴匹配越锈，type=range 表示走索引掃描仗嗦，key=name_score 看到實(shí)際走了 name_score 索引
EXPLAIN SELECT * FROM person WHERE NAME LIKE 'name123%' LIMIT 100

image.png

原因很簡單，索引 B+ 樹中行數(shù)據(jù)按照索引值排序甘凭，只能根據(jù)前綴進(jìn)行比較稀拐。如果要按照后綴搜索也希望走索引的話，并且永遠(yuǎn)只是按照后綴搜索的話丹弱，可以把數(shù)據(jù)反過來存德撬，用的時(shí)候再倒過來。

第二躲胳，條件涉及函數(shù)操作無法走索引蜓洪。

//比如搜索條件用到了 LENGTH 函數(shù)，肯定無法走索引：
EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7

image.png

同樣的原因坯苹，索引保存的是索引列的原始值隆檀，而不是經(jīng)過函數(shù)計(jì)算后的值。如果需要針對函數(shù)調(diào)用走數(shù)據(jù)庫索引的話粹湃，只能保存一份函數(shù)變換后的值恐仑，然后重新針對這個(gè)計(jì)算列做索引。

第三为鳄，聯(lián)合索引只能匹配左邊的列裳仆。

//雖然對 name 和 score 建了聯(lián)合索引，但是僅按照 score 列搜索無法走索引：
EXPLAIN SELECT * FROM person WHERE SCORE>45678

image.png

原因也很簡單孤钦，在聯(lián)合索引的情況下鉴逞，數(shù)據(jù)是按照索引第一列排序，第一列數(shù)據(jù)相同時(shí)才會按照第二列排序司训。也就是說构捡，如果我們想使用聯(lián)合索引中盡可能多的列，查詢條件中的各個(gè)列必須是聯(lián)合索引中從最左邊開始連續(xù)的列壳猜。如果我們僅僅按照第二列搜索勾徽，肯定無法走索引。

//把搜索條件加入 name 列统扳，可以看到走了 name_score 索引：

EXPLAIN SELECT * FROM person WHERE SCORE>45678 AND NAME LIKE 'NAME45%'

image.png

需要注意的是喘帚，因?yàn)橛胁樵儍?yōu)化器畅姊，所以 name 作為 WHERE 子句的第幾個(gè)條件并不是很重要。

第四吹由，排序使用到索引

排序使用到索引若未，在執(zhí)行計(jì)劃中的體現(xiàn)就是 key 這一列。如果沒有用到索引倾鲫，會在 Extra 中看到 Using filesort粗合，代表使用了內(nèi)存或磁盤進(jìn)行排序。而具體走內(nèi)存還是磁盤乌昔，是由 sort_buffer_size 和排序數(shù)據(jù)大小決定的隙疚。

排序無法使用到索引的情況有：

• 對于使用聯(lián)合索引進(jìn)行排序的場景，多個(gè)字段排序 ASC 和 DESC 混用磕道；
• a+b 作為聯(lián)合索引供屉，按照 a 范圍查詢后按照 b 排序；
• 排序列涉及到的多個(gè)字段不屬于同一個(gè)聯(lián)合索引溺蕉；

排序列使用了表達(dá)式伶丐。其實(shí)，這些原因都和索引的結(jié)構(gòu)有關(guān)疯特。

總結(jié)：

是不是建了索引一定可以用上撵割？并不是，只有當(dāng)查詢能符合索引存儲的實(shí)際結(jié)構(gòu)時(shí)辙芍，才能用上啡彬。這里，只給出了三個(gè)肯定用不上索引的反例故硅。其實(shí)庶灿，有的時(shí)候即使可以走索引，MySQL 也不一定會選擇使用索引吃衅。

怎么選擇建聯(lián)合索引還是多個(gè)獨(dú)立索引往踢？如果你的搜索條件經(jīng)常會使用多個(gè)字段進(jìn)行搜索，那么可以考慮針對這幾個(gè)字段建聯(lián)合索引徘层；同時(shí)峻呕，針對多字段建立聯(lián)合索引，使用索引覆蓋的可能更大趣效。如果只會查詢單個(gè)字段瘦癌，可以考慮建單獨(dú)的索引，畢竟聯(lián)合索引保存了不必要字段也有成本跷敬。

不能認(rèn)為建了索引就一定有效讯私，對于后綴的匹配查詢、查詢中不包含聯(lián)合索引的第一列、查詢條件涉及函數(shù)計(jì)算等情況無法使用索引斤寇。此外桶癣，即使 SQL 本身符合索引的使用條件，MySQL 也會通過評估各種查詢方式的代價(jià)娘锁，來決定是否走索引牙寞，以及走哪個(gè)索引。

3.2 數(shù)據(jù)庫基于成本決定是否走索引

通過前面的案例莫秆，我們可以看到间雀，查詢數(shù)據(jù)可以直接在聚簇索引上進(jìn)行全表掃描，也可以走二級索引掃描后到聚簇索引回表馏锡±柞澹看到這里伟端，你不禁要問了杯道，MySQL 到底是怎么確定走哪種方案的呢。

其實(shí)责蝠，MySQL 在查詢數(shù)據(jù)之前党巾，會先對可能的方案做執(zhí)行計(jì)劃，然后依據(jù)成本決定走哪個(gè)執(zhí)行計(jì)劃霜医。

這里的成本齿拂，包括 IO 成本和 CPU 成本：

• IO 成本，是從磁盤把數(shù)據(jù)加載到內(nèi)存的成本肴敛。默認(rèn)情況下署海，讀取數(shù)據(jù)頁的 IO 成本常數(shù)是 1（也就是讀取 1 個(gè)頁成本是 1）。
• CPU 成本医男，是檢測數(shù)據(jù)是否滿足條件和排序等 CPU 操作的成本砸狞。默認(rèn)情況下，檢測記錄的成本是 0.2镀梭。

全表掃描的成本

全表掃描刀森，就是把聚簇索引中的記錄依次和給定的搜索條件做比較，把符合搜索條件的記錄加入結(jié)果集的過程报账。那么研底，要計(jì)算全表掃描的代價(jià)需要兩個(gè)信息：

• 聚簇索引占用的頁面數(shù)，用來計(jì)算讀取數(shù)據(jù)的 IO 成本透罢；
• 表中的記錄數(shù)榜晦，用來計(jì)算搜索的 CPU 成本。

那么羽圃，MySQL 是實(shí)時(shí)統(tǒng)計(jì)這些信息的嗎芽隆？其實(shí)并不是，MySQL 維護(hù)了表的統(tǒng)計(jì)信息，可以使用下面的命令查看：

SHOW TABLE STATUS LIKE 'person'

image.png

可以看到：

• 總行數(shù)是 100086 行（之前 EXPLAIN 時(shí)胚吁，也看到 rows 為 100086）牙躺。你可能說，person 表不是有 10 萬行記錄嗎腕扶，為什么這里多了 86 行孽拷？其實(shí)，MySQL 的統(tǒng)計(jì)信息是一個(gè)估算半抱，其統(tǒng)計(jì)方式比較復(fù)雜我就不再展開了脓恕。但不妨礙我們根據(jù)這個(gè)值估算 CPU 成本，是 100086*0.2=20017 左右窿侈。
• 數(shù)據(jù)長度是 4734976 字節(jié)炼幔。對于 InnoDB 來說，這就是聚簇索引占用的空間史简，等于聚簇索引的頁面數(shù)量 * 每個(gè)頁面的大小乃秀。InnoDB 每個(gè)頁面的大小是 16KB，大概計(jì)算出頁面數(shù)量是 289圆兵，因此 IO 成本是 289 左右跺讯。

所以，全表掃描的總成本是 20306 左右

接下來殉农，我還是用 person 表這個(gè)例子刀脏，和你分析下 MySQL 如何基于成本來制定執(zhí)行計(jì)劃。現(xiàn)在超凳，我要用下面的 SQL 查詢 name>‘name84059’ AND create_time>‘2020-01-24 05:00:00’

EXPLAIN SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00'

image.png

只要把 create_time 條件中的 5 點(diǎn)改為 6 點(diǎn)就變?yōu)樽咚饕擞郏⑶易叩氖?create_time 索引而不是 name_score 聯(lián)合索引：

image.png

可以得到兩個(gè)結(jié)論：

• MySQL 選擇索引，并不是按照 WHERE 條件中列的順序進(jìn)行的轮傍；

• 即便列有索引暂雹，甚至有多個(gè)可能的索引方案，MySQL 也可能不走索引金麸。

原因就是擎析，MySQL 并不是猜拳決定是否走索引的，而是根據(jù)成本來判斷的挥下。雖然表的統(tǒng)計(jì)信息不完全準(zhǔn)確揍魂，但足夠用于策略的判斷了。

不過棚瘟，有時(shí)會因?yàn)榻y(tǒng)計(jì)信息的不準(zhǔn)確或成本估算的問題现斋，實(shí)際開銷會和 MySQL 統(tǒng)計(jì)出來的差距較大，導(dǎo)致 MySQL 選擇錯(cuò)誤的索引或是直接選擇走全表掃描偎蘸，這個(gè)時(shí)候就需要人工干預(yù)瞬内，使用強(qiáng)制索引了。比如限书，像這樣強(qiáng)制走 name_score 索引：

EXPLAIN SELECT * FROM person FORCE INDEX(name_score) WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00' 

查看優(yōu)化器生成執(zhí)行計(jì)劃的整個(gè)過程

MySQL 會根據(jù)成本選擇執(zhí)行計(jì)劃粉铐，通過 EXPLAIN 知道優(yōu)化器最終會選擇怎樣的執(zhí)行計(jì)劃劝枣，但 MySQL 如何制定執(zhí)行計(jì)劃始終是一個(gè)黑盒哨免。那么丘损，有沒有什么辦法可以了解各種執(zhí)行計(jì)劃的成本而钞，以及 MySQL 做出選擇的依據(jù)呢而晒？

在 MySQL 5.6 及之后的版本中倡怎，我們可以使用 optimizer trace 功能查看優(yōu)化器生成執(zhí)行計(jì)劃的整個(gè)過程迅耘。有了這個(gè)功能，我們不僅可以了解優(yōu)化器的選擇過程监署，更可以了解每一個(gè)執(zhí)行環(huán)節(jié)的成本颤专，然后依靠這些信息進(jìn)一步優(yōu)化查詢。

如下代碼所示钠乏，打開 optimizer_trace 后栖秕，再執(zhí)行 SQL 就可以查詢 information_schema.OPTIMIZER_TRACE 表查看執(zhí)行計(jì)劃了，最后可以關(guān)閉 optimizer_trace 功能：

SET optimizer_trace="enabled=on";
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

對于按照 create_time>'2020-01-24 05:00:00’條件走全表掃描的 SQL晓避，我從 OPTIMIZER_TRACE 的執(zhí)行結(jié)果中簇捍，摘出了幾個(gè)重要片段來重點(diǎn)分析：

• 使用 name_score 對 name84059<name 條件進(jìn)行索引掃描需要掃描 25362 行，成本是 30435俏拱，因此最終沒有選擇這個(gè)方案暑塑。這里的 30435 是查詢二級索引的 IO 成本和 CPU 成本之和，再加上回表查詢聚簇索引的 IO 成本和 CPU 成本之和锅必，我就不再具體分析了：

  {
    "index": "name_score",
    "ranges": [
      "name84059 < name"
    ],
    "rows": 25362,
    "cost": 30435,
    "chosen": false,
    "cause": "cost"
  },
  

• 使用 create_time 進(jìn)行索引掃描需要掃描 23758 行事格，成本是 28511，同樣因?yàn)槌杀驹驔]有選擇這個(gè)方案：

  {
    "index": "create_time",
    "ranges": [
      "0x5e2a79d0 < create_time"
    ],
    "rows": 23758,
    "cost": 28511,
    "chosen": false,
    "cause": "cost"
  }
  

• 最終選擇了全表掃描方式作為執(zhí)行計(jì)劃搞隐【杂蓿可以看到，全表掃描 100086 條記錄的成本是 20306尔许，和我們之前計(jì)算的一致么鹤，顯然是小于其他兩個(gè)方案的 28511 和 30435：

  {
    "considered_execution_plans": [{
      "table": "`person`",
      "best_access_path": {
        "considered_access_paths": [{
          "rows_to_scan": 100086,
          "access_type": "scan",
          "resulting_rows": 100086,
          "cost": 20306,
          "chosen": true
        }]
      },
      "rows_for_plan": 100086,
      "cost_for_plan": 20306,
      "chosen": true
    }]
  }
  

  把 SQL 中的 create_time 條件從 05:00 改為 06:00，再次分析 OPTIMIZER_TRACE 可以看到味廊，這次執(zhí)行計(jì)劃選擇的是走 create_time 索引蒸甜。因?yàn)槭遣樵兏頃r(shí)間的數(shù)據(jù)棠耕，走 create_time 索引需要掃描的行數(shù)從 23758 減少到了 16588。這次走這個(gè)索引的成本 19907 小于全表掃描的 20306柠新，更小于走 name_score 索引的 30435：

  {
    "index": "create_time",
    "ranges": [
      "0x5e2a87e0 < create_time"
    ],
    "rows": 16588,
    "cost": 19907,
    "chosen": true
  }
  

在嘗試通過索引進(jìn)行 SQL 性能優(yōu)化的時(shí)候窍荧，務(wù)必通過執(zhí)行計(jì)劃或?qū)嶋H的效果來確認(rèn)索引是否能有效改善性能問題，否則增加了索引不但沒解決性能問題恨憎，還增加了數(shù)據(jù)庫增刪改的負(fù)擔(dān)蕊退。如果對 EXPLAIN 給出的執(zhí)行計(jì)劃有疑問的話，你還可以利用 optimizer_trace 查看詳細(xì)的執(zhí)行計(jì)劃做進(jìn)一步分析憔恳。