幾乎所有的業(yè)務(wù)項(xiàng)目都會(huì)涉及數(shù)據(jù)存儲(chǔ)，雖然當(dāng)前各種NoSQL和文件系統(tǒng)大行其道，但MySQL等關(guān)系型數(shù)據(jù)庫(kù)因?yàn)闈M足ACID、可靠性高、對(duì)開(kāi)發(fā)友好等特點(diǎn)，仍然最常被用于存儲(chǔ)重要數(shù)據(jù)。在關(guān)系型數(shù)據(jù)庫(kù)中，索引是優(yōu)化查詢性能的重要手段。

為此，我經(jīng)常看到一些同學(xué)一遇到查詢性能問(wèn)題，就盲目要求運(yùn)維或DBA給數(shù)據(jù)表相關(guān)字段創(chuàng)建大量索引。顯然，這種想法是錯(cuò)誤的。今天，我們就以MySQL為例來(lái)深入理解下索引的原理，以及相關(guān)誤區(qū)。

InnoDB是如何存儲(chǔ)數(shù)據(jù)的？

MySQL把數(shù)據(jù)存儲(chǔ)和查詢操作抽象成了存儲(chǔ)引擎，不同的存儲(chǔ)引擎，對(duì)數(shù)據(jù)的存儲(chǔ)和讀取方式各不相同。MySQL支持多種存儲(chǔ)引擎，并且可以以表為粒度設(shè)置存儲(chǔ)引擎。因?yàn)橹С质聞?wù)，我們最常使用的是InnoDB。為方便理解下面的內(nèi)容，我先和你簡(jiǎn)單說(shuō)說(shuō)InnoDB是如何存儲(chǔ)數(shù)據(jù)的。

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

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

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

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

• 先二分得出槽中間位是(0+6)/2=3，看到其指向的記錄是12＜15，所以需要從#3槽后繼續(xù)搜索記錄；
• 再使用二分搜索出#3槽和#6槽的中間位是(3+6)/2=4.5取整4，#4槽對(duì)應(yīng)的記錄是16＞15，所以記錄一定在#4槽中；
• 再?gòu)?3槽指向的12號(hào)記錄開(kāi)始向下搜索3次，定位到15號(hào)記錄。
  

理解了InnoDB存儲(chǔ)數(shù)據(jù)的原理后，我們就可以繼續(xù)學(xué)習(xí)MySQL索引相關(guān)的原理和坑了。

聚簇索引和二級(jí)索引

說(shuō)到索引，頁(yè)目錄就是最簡(jiǎn)單的索引，是通過(guò)對(duì)記錄進(jìn)行一級(jí)分組來(lái)降低搜索的時(shí)間復(fù)雜度。但，這樣能夠降低的時(shí)間復(fù)雜度數(shù)量級(jí)，非常有限。當(dāng)有無(wú)數(shù)個(gè)數(shù)據(jù)頁(yè)來(lái)存儲(chǔ)表數(shù)據(jù)的時(shí)候，我們就需要考慮如何建立合適的索引，才能方便定位記錄所在的頁(yè)。

為了解決這個(gè)問(wèn)題，InnoDB引入了B+樹(shù)。如下圖所示，B+樹(shù)是一棵倒過(guò)來(lái)的樹(shù)：

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

• 最底層的節(jié)點(diǎn)叫作葉子節(jié)點(diǎn)，用來(lái)存放數(shù)據(jù)；
• 其他上層節(jié)點(diǎn)叫作非葉子節(jié)點(diǎn)，僅用來(lái)存放目錄項(xiàng)，作為索引；
• 非葉子節(jié)點(diǎn)分為不同層次，通過(guò)分層來(lái)降低每一層的搜索量；
• 所有節(jié)點(diǎn)按照索引鍵大小排序，構(gòu)成一個(gè)雙向鏈表，加速范圍查找。

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

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

我們?cè)倏纯碆+樹(shù)如何實(shí)現(xiàn)快速查找主鍵。比如，我們要搜索PK=4的數(shù)據(jù)，通過(guò)根節(jié)點(diǎn)中的索引可以知道數(shù)據(jù)在第一個(gè)記錄指向的2號(hào)頁(yè)中，通過(guò)2號(hào)頁(yè)的索引又可以知道數(shù)據(jù)在5號(hào)頁(yè)，5號(hào)頁(yè)就是實(shí)際的數(shù)據(jù)頁(yè)，然后再通過(guò)二分法查找頁(yè)目錄馬上可以找到記錄的指針。

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

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

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

考慮額外創(chuàng)建二級(jí)索引的代價(jià)

創(chuàng)建二級(jí)索引的代價(jià)，主要表現(xiàn)在維護(hù)代價(jià)、空間代價(jià)和回表代價(jià)三個(gè)方面。接下來(lái)，我就與你仔細(xì)分析下吧。

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

我們通過(guò)實(shí)驗(yàn)測(cè)試一下創(chuàng)建索引的代價(jià)。假設(shè)有一個(gè)person表，有主鍵ID，以及name、score、create_time三個(gè)字段：

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  `create_time` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通過(guò)下面的存儲(chǔ)過(guò)程循環(huán)創(chuàng)建10萬(wàn)條測(cè)試數(shù)據(jù)，我的機(jī)器的耗時(shí)是140秒（本文的例子均在MySQL 5.7.26中執(zhí)行）：

CREATE DEFINER=`root`@`%` PROCEDURE `insert_person`()
begin
    declare c_id integer default 1;
    while c_id<=100000 do
    insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
    set c_id=c_id+1;
    end while;
end

如果再創(chuàng)建兩個(gè)索引，一個(gè)是name和score構(gòu)成的聯(lián)合索引，另一個(gè)是單一列create_time的索引，那么創(chuàng)建10萬(wàn)條記錄的耗時(shí)提高到154秒：

KEY `name_score` (`name`,`score`) USING BTREE,
KEY `create_time` (`create_time`) USING BTREE

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

你可以查看這個(gè)文檔，以進(jìn)一步了解如何設(shè)置合理的合并閾值，來(lái)平衡頁(yè)的空閑率和因?yàn)樵俅雾?yè)分裂產(chǎn)生的代價(jià)。

其次是空間代價(jià)。雖然二級(jí)索引不保存原始數(shù)據(jù)，但要保存索引列的數(shù)據(jù)，所以會(huì)占用更多的空間。比如，person表創(chuàng)建了兩個(gè)索引后，使用下面的SQL查看數(shù)據(jù)和索引占用的磁盤(pán)：

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

結(jié)果顯示，數(shù)據(jù)本身只占用了4.7M，而索引占用了8.4M。

最后是回表的代價(jià)。二級(jí)索引不保存原始數(shù)據(jù)，通過(guò)索引找到主鍵后需要再查詢聚簇索引，才能得到我們要的數(shù)據(jù)。比如，使用SELECT * 按照name字段查詢用戶，使用EXPLAIN查看執(zhí)行計(jì)劃：

EXPLAIN SELECT * FROM person WHERE NAME='name1'

執(zhí)行計(jì)劃如下，可以發(fā)現(xiàn)：

• key字段代表實(shí)際走的是哪個(gè)索引，其值是name_score，說(shuō)明走的是name_score這個(gè)索引。
• type字段代表了訪問(wèn)表的方式，其值ref說(shuō)明是二級(jí)索引等值匹配，符合我們的查詢。

把SQL中的*修改為NAME和SCORE，也就是SELECT name_score聯(lián)合索引包含的兩列：

EXPLAIN SELECT NAME,SCORE FROM person WHERE NAME='name1'

再來(lái)看看執(zhí)行計(jì)劃：

可以看到，Extra列多了一行Using index的提示，證明這次查詢直接查的是二級(jí)索引，免去了回表。

原因很簡(jiǎn)單，聯(lián)合索引中其實(shí)保存了多個(gè)索引列的值，對(duì)于頁(yè)中的記錄先按照字段1排序，如果相同再按照字段2排序，如圖所示：

圖中，葉子節(jié)點(diǎn)每一條記錄的第一和第二個(gè)方塊是索引列的數(shù)據(jù)，第三個(gè)方塊是記錄的主鍵。如果我們需要查詢的是索引列索引或聯(lián)合索引能覆蓋的數(shù)據(jù)，那么查詢索引本身已經(jīng)“覆蓋”了需要的數(shù)據(jù)，不再需要回表查詢。因此，這種情況也叫作索引覆蓋。我會(huì)在最后一小節(jié)介紹如何查看不同查詢的成本，和你一起看看索引覆蓋和索引查詢后回表的代價(jià)差異。

最后，我和你總結(jié)下關(guān)于索引開(kāi)銷(xiāo)的最佳實(shí)踐吧。

第一，無(wú)需一開(kāi)始就建立索引，可以等到業(yè)務(wù)場(chǎng)景明確后，或者是數(shù)據(jù)量超過(guò)1萬(wàn)、查詢變慢后，再針對(duì)需要查詢、排序或分組的字段創(chuàng)建索引。創(chuàng)建索引后可以使用EXPLAIN命令，確認(rèn)查詢是否可以使用索引。我會(huì)在下一小節(jié)展開(kāi)說(shuō)明。

第二，盡量索引輕量級(jí)的字段，比如能索引int字段就不要索引varchar字段。索引字段也可以是部分前綴，在創(chuàng)建的時(shí)候指定字段索引長(zhǎng)度。針對(duì)長(zhǎng)文本的搜索，可以考慮使用Elasticsearch等專(zhuān)門(mén)用于文本搜索的索引數(shù)據(jù)庫(kù)。

第三，盡量不要在SQL語(yǔ)句中SELECT *，而是SELECT必要的字段，甚至可以考慮使用聯(lián)合索引來(lái)包含我們要搜索的字段，既能實(shí)現(xiàn)索引加速，又可以避免回表的開(kāi)銷(xiāo)。

不是所有針對(duì)索引列的查詢都能用上索引

在上一個(gè)案例中，我創(chuàng)建了一個(gè)name+score的聯(lián)合索引，僅搜索name時(shí)就能夠用上這個(gè)聯(lián)合索引。這就引出兩個(gè)問(wèn)題：

• 是不是建了索引一定可以用上？
• 怎么選擇創(chuàng)建聯(lián)合索引還是多個(gè)獨(dú)立索引？

首先，我們通過(guò)幾個(gè)案例來(lái)分析一下索引失效的情況。

第一，索引只能匹配列前綴。比如下面的LIKE語(yǔ)句，搜索name后綴為name123的用戶無(wú)法走索引，執(zhí)行計(jì)劃的type=ALL代表了全表掃描：

EXPLAIN SELECT * FROM person WHERE NAME LIKE '%name123' LIMIT 100

把百分號(hào)放到后面走前綴匹配，type=range表示走索引掃描，key=name_score看到實(shí)際走了name_score索引：

EXPLAIN SELECT * FROM person WHERE NAME LIKE 'name123%' LIMIT 100

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

第二，條件涉及函數(shù)操作無(wú)法走索引。比如搜索條件用到了LENGTH函數(shù)，肯定無(wú)法走索引：

EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7

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

第三，聯(lián)合索引只能匹配左邊的列。也就是說(shuō)，雖然對(duì)name和score建了聯(lián)合索引，但是僅按照score列搜索無(wú)法走索引：

EXPLAIN SELECT * FROM person WHERE SCORE>45678

原因也很簡(jiǎn)單，在聯(lián)合索引的情況下，數(shù)據(jù)是按照索引第一列排序，第一列數(shù)據(jù)相同時(shí)才會(huì)按照第二列排序。也就是說(shuō)，如果我們想使用聯(lián)合索引中盡可能多的列，查詢條件中的各個(gè)列必須是聯(lián)合索引中從最左邊開(kāi)始連續(xù)的列。如果我們僅僅按照第二列搜索，肯定無(wú)法走索引。嘗試把搜索條件加入name列，可以看到走了name_score索引：

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

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

現(xiàn)在回到最開(kāi)始的兩個(gè)問(wèn)題。

• 是不是建了索引一定可以用上？并不是，只有當(dāng)查詢能符合索引存儲(chǔ)的實(shí)際結(jié)構(gòu)時(shí)，才能用上。這里，我只給出了三個(gè)肯定用不上索引的反例。其實(shí)，有的時(shí)候即使可以走索引，MySQL也不一定會(huì)選擇使用索引。我會(huì)在下一小節(jié)展開(kāi)這一點(diǎn)。
• 怎么選擇建聯(lián)合索引還是多個(gè)獨(dú)立索引？如果你的搜索條件經(jīng)常會(huì)使用多個(gè)字段進(jìn)行搜索，那么可以考慮針對(duì)這幾個(gè)字段建聯(lián)合索引；同時(shí)，針對(duì)多字段建立聯(lián)合索引，使用索引覆蓋的可能更大。如果只會(huì)查詢單個(gè)字段，可以考慮建單獨(dú)的索引，畢竟聯(lián)合索引保存了不必要字段也有成本。

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

通過(guò)前面的案例，我們可以看到，查詢數(shù)據(jù)可以直接在聚簇索引上進(jìn)行全表掃描，也可以走二級(jí)索引掃描后到聚簇索引回表?？吹竭@里，你不禁要問(wèn)了，MySQL到底是怎么確定走哪種方案的呢。

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

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

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

基于此，我們分析下全表掃描的成本。

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

• 聚簇索引占用的頁(yè)面數(shù)，用來(lái)計(jì)算讀取數(shù)據(jù)的IO成本；
• 表中的記錄數(shù)，用來(lái)計(jì)算搜索的CPU成本。

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

SHOW TABLE STATUS LIKE 'person'

輸出如下：

可以看到：

• 總行數(shù)是100086行（之前EXPLAIN時(shí)，也看到rows為100086）。你可能說(shuō)，person表不是有10萬(wàn)行記錄嗎，為什么這里多了86行？其實(shí)，MySQL的統(tǒng)計(jì)信息是一個(gè)估算，其統(tǒng)計(jì)方式比較復(fù)雜我就不再展開(kāi)了。但不妨礙我們根據(jù)這個(gè)值估算CPU成本，是100086*0.2=20017左右。
• 數(shù)據(jù)長(zhǎng)度是4734976字節(jié)。對(duì)于InnoDB來(lái)說(shuō)，這就是聚簇索引占用的空間，等于聚簇索引的頁(yè)面數(shù)量*每個(gè)頁(yè)面的大小。InnoDB每個(gè)頁(yè)面的大小是16KB，大概計(jì)算出頁(yè)面數(shù)量是289，因此IO成本是289左右。

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

接下來(lái)，我還是用person表這個(gè)例子，和你分析下MySQL如何基于成本來(lái)制定執(zhí)行計(jì)劃?，F(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'

其執(zhí)行計(jì)劃是全表掃描：

只要把create_time條件中的5點(diǎn)改為6點(diǎn)就變?yōu)樽咚饕?，并且走的是create_time索引而不是name_score聯(lián)合索引：

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

• MySQL選擇索引，并不是按照WHERE條件中列的順序進(jìn)行的；
• 即便列有索引，甚至有多個(gè)可能的索引方案，MySQL也可能不走索引。

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

不過(guò)，有時(shí)會(huì)因?yàn)榻y(tǒng)計(jì)信息的不準(zhǔn)確或成本估算的問(wèn)題，實(shí)際開(kāi)銷(xiāo)會(huì)和MySQL統(tǒng)計(jì)出來(lái)的差距較大，導(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' 

我們介紹了MySQL會(huì)根據(jù)成本選擇執(zhí)行計(jì)劃，也通過(guò)EXPLAIN知道了優(yōu)化器最終會(huì)選擇怎樣的執(zhí)行計(jì)劃，但MySQL如何制定執(zhí)行計(jì)劃始終是一個(gè)黑盒。那么，有沒(méi)有什么辦法可以了解各種執(zhí)行計(jì)劃的成本，以及MySQL做出選擇的依據(jù)呢？

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

如下代碼所示，打開(kāi)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";

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

使用name_score對(duì)name84059<name條件進(jìn)行索引掃描需要掃描25362行，成本是30435，因此最終沒(méi)有選擇這個(gè)方案。這里的30435是查詢二級(jí)索引的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)槌杀驹驔](méi)有選擇這個(gè)方案：

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

最終選擇了全表掃描方式作為執(zhí)行計(jì)劃?？梢钥吹剑頀呙?00086條記錄的成本是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
}

有關(guān)optimizer trace的更多信息，你可以參考MySQL的文檔。

重點(diǎn)回顧

今天，我先和你分析了MySQL InnoDB存儲(chǔ)引擎頁(yè)、聚簇索引和二級(jí)索引的結(jié)構(gòu)，然后分析了關(guān)于索引的兩個(gè)誤區(qū)。

第一個(gè)誤區(qū)是，考慮到索引的維護(hù)代價(jià)、空間占用和查詢時(shí)回表的代價(jià)，不能認(rèn)為索引越多越好。索引一定是按需創(chuàng)建的，并且要盡可能確保足夠輕量。一旦創(chuàng)建了多字段的聯(lián)合索引，我們要考慮盡可能利用索引本身完成數(shù)據(jù)查詢，減少回表的成本。

第二個(gè)誤區(qū)是，不能認(rèn)為建了索引就一定有效，對(duì)于后綴的匹配查詢、查詢中不包含聯(lián)合索引的第一列、查詢條件涉及函數(shù)計(jì)算等情況無(wú)法使用索引。此外，即使SQL本身符合索引的使用條件，MySQL也會(huì)通過(guò)評(píng)估各種查詢方式的代價(jià)，來(lái)決定是否走索引，以及走哪個(gè)索引。

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

到此這篇關(guān)于數(shù)據(jù)庫(kù)索引并不是萬(wàn)能藥的文章就介紹到這了,更多相關(guān)數(shù)據(jù)庫(kù)索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！