數(shù)據(jù)庫索引并不是萬能藥

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

為此，我經(jīng)?？吹揭恍┩瑢W一遇到查詢性能問題，就盲目要求運維或DBA給數(shù)據(jù)表相關字段創(chuàng)建大量索引。顯然，這種想法是錯誤的。今天，我們就以MySQL為例來深入理解下索引的原理，以及相關誤區(qū)。

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

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

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

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

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

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

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

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

聚簇索引和二級索引

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

為了解決這個問題，InnoDB引入了B+樹。如下圖所示，B+樹是一棵倒過來的樹：

B+樹的特點包括：

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

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

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

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

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

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

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

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

創(chuàng)建二級索引的代價，主要表現(xiàn)在維護代價、空間代價和回表代價三個方面。接下來，我就與你仔細分析下吧。

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

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

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;

通過下面的存儲過程循環(huán)創(chuàng)建10萬條測試數(shù)據(jù)，我的機器的耗時是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)建兩個索引，一個是name和score構成的聯(lián)合索引，另一個是單一列create_time的索引，那么創(chuàng)建10萬條記錄的耗時提高到154秒：

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

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

你可以查看這個文檔，以進一步了解如何設置合理的合并閾值，來平衡頁的空閑率和因為再次頁分裂產(chǎn)生的代價。

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

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

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

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

EXPLAIN SELECT * FROM person WHERE NAME='name1'

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

• key字段代表實際走的是哪個索引，其值是name_score，說明走的是name_score這個索引。
• type字段代表了訪問表的方式，其值ref說明是二級索引等值匹配，符合我們的查詢。

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

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

再來看看執(zhí)行計劃：

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

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

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

最后，我和你總結下關于索引開銷的最佳實踐吧。

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

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

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

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

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

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

首先，我們通過幾個案例來分析一下索引失效的情況。

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

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

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

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

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

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

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

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

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

EXPLAIN SELECT * FROM person WHERE SCORE>45678

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

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

需要注意的是，因為有查詢優(yōu)化器，所以name作為WHERE子句的第幾個條件并不是很重要。

現(xiàn)在回到最開始的兩個問題。

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

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

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

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

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

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

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

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

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

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

SHOW TABLE STATUS LIKE 'person'

輸出如下：

可以看到：

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

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

接下來，我還是用person表這個例子，和你分析下MySQL如何基于成本來制定執(zhí)行計劃?，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í)行計劃是全表掃描：

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

我們可以得到兩個結論：

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

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

不過，有時會因為統(tǒng)計信息的不準確或成本估算的問題，實際開銷會和MySQL統(tǒng)計出來的差距較大，導致MySQL選擇錯誤的索引或是直接選擇走全表掃描，這個時候就需要人工干預，使用強制索引了。比如，像這樣強制走name_score索引：

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

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

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

如下代碼所示，打開optimizer_trace后，再執(zhí)行SQL就可以查詢
information_schema.OPTIMIZER_TRACE表查看執(zhí)行計劃了，最后可以關閉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í)行結果中，摘出了幾個重要片段來重點分析：

使用name_score對name84059name條件進行索引掃描需要掃描25362行，成本是30435，因此最終沒有選擇這個方案。這里的30435是查詢二級索引的IO成本和CPU成本之和，再加上回表查詢聚簇索引的IO成本和CPU成本之和，我就不再具體分析了：

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

使用create_time進行索引掃描需要掃描23758行，成本是28511，同樣因為成本原因沒有選擇這個方案：

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

最終選擇了全表掃描方式作為執(zhí)行計劃?？梢钥吹剑頀呙?00086條記錄的成本是20306，和我們之前計算的一致，顯然是小于其他兩個方案的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í)行計劃選擇的是走create_time索引。因為是查詢更晚時間的數(shù)據(jù)，走create_time索引需要掃描的行數(shù)從23758減少到了16588。這次走這個索引的成本19907小于全表掃描的20306，更小于走name_score索引的30435：

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

有關optimizer trace的更多信息，你可以參考MySQL的文檔。

重點回顧

今天，我先和你分析了MySQL InnoDB存儲引擎頁、聚簇索引和二級索引的結構，然后分析了關于索引的兩個誤區(qū)。

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

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

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

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