投稿
  1. 簡單一點首頁
  2. 投稿

索引功能介紹及應用大全「數據庫中的索引有什麼用」

投稿專員 • • 投稿

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

為此，我經常看到一些同學一遇到查詢性能問題，就盲目要求運維或DBA給數據表相關字段創建大量索引。顯然，這種想法是錯誤的。今天，我們就以MySQL為例來深入理解下索引的原理，以及相關誤區。

InnoDB是如何存儲數據的？

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

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

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

數據庫索引：索引並不是萬能葯

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

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

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

理解了InnoDB存儲數據的原理後，我們就可以繼續學習MySQL索引相關的原理和坑了。

聚簇索引和二級索引

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

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

數據庫索引：索引並不是萬能葯

B+樹的特點包括：

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

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

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

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

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

數據庫索引：索引並不是萬能葯

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

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

考慮額外創建二級索引的代價

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

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

我們通過實驗測試一下創建索引的代價。假設有一個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;

通過下面的存儲過程循環創建10萬條測試數據，我的機器的耗時是140秒（本文的例子均在MySQL 5.7.26中執行）：

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

如果再創建兩個索引，一個是name和score構成的聯合索引，另一個是單一列create_time的索引，那麼創建10萬條記錄的耗時提高到154秒：

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

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

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

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

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

結果顯示，數據本身只佔用了4.7M，而索引佔用了8.4M。

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

EXPLAIN SELECT * FROM person WHERE NAME='name1'

執行計劃如下，可以發現：

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

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

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

再來看看執行計劃：

數據庫索引：索引並不是萬能葯

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

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

數據庫索引：索引並不是萬能葯

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

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

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

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

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

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

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

  • 是不是建了索引一定可以用上？
  • 怎麼選擇創建聯合索引還是多個獨立索引？

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

第一，索引只能匹配列前綴。比如下面的LIKE語句，搜索name後綴為name123的用戶無法走索引，執行計劃的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+樹中行數據按照索引值排序，只能根據前綴進行比較。如果要按照後綴搜索也希望走索引的話，並且永遠只是按照後綴搜索的話，可以把數據反過來存，用的時候再倒過來。

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

EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7
數據庫索引：索引並不是萬能葯

同樣的原因，索引保存的是索引列的原始值，而不是經過函數計算後的值。如果需要針對函數調用走數據庫索引的話，只能保存一份函數變換後的值，然後重新針對這個計算列做索引。

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

EXPLAIN SELECT * FROM person WHERE SCORE>45678
數據庫索引：索引並不是萬能葯

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

EXPLAIN SELECT * FROM person WHERE SCORE>45678 AND NAME LIKE 'NAME45%'
數據庫索引：索引並不是萬能葯

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

現在回到最開始的兩個問題。

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

數據庫基於成本決定是否走索引

通過前面的案例，我們可以看到，查詢數據可以直接在聚簇索引上進行全表掃描，也可以走二級索引掃描後到聚簇索引回表。看到這裡，你不禁要問了，MySQL到底是怎麼確定走哪種方案的呢。

其實，MySQL在查詢數據之前，會先對可能的方案做執行計劃，然後依據成本決定走哪個執行計劃。

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

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

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

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

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

那麼，MySQL是實時統計這些信息的嗎？其實並不是，MySQL維護了表的統計信息，可以使用下面的命令查看：

SHOW TABLE STATUS LIKE 'person'

輸出如下：

數據庫索引：索引並不是萬能葯

可以看到：

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

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

接下來，我還是用person表這個例子，和你分析下MySQL如何基於成本來制定執行計劃。現在，我要用下面的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'

其執行計劃是全表掃描：

數據庫索引：索引並不是萬能葯

只要把create_time條件中的5點改為6點就變為走索引了，並且走的是create_time索引而不是name_score聯合索引：

數據庫索引：索引並不是萬能葯

我們可以得到兩個結論：

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

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

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

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

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

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

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

  • 使用name_score對name84059<name條件進行索引掃描需要掃描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"
}
  • 最終選擇了全表掃描方式作為執行計劃。可以看到，全表掃描100086條記錄的成本是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可以看到，這次執行計劃選擇的是走create_time索引。因為是查詢更晚時間的數據，走create_time索引需要掃描的行數從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存儲引擎頁、聚簇索引和二級索引的結構，然後分析了關於索引的兩個誤區。

第一個誤區是，考慮到索引的維護代價、空間佔用和查詢時回表的代價，不能認為索引越多越好。索引一定是按需創建的，並且要儘可能確保足夠輕量。一旦創建了多字段的聯合索引，我們要考慮儘可能利用索引本身完成數據查詢，減少回表的成本。

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

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

原創文章，作者：投稿專員，如若轉載，請註明出處：https://www.506064.com/zh-hant/n/287635.html

贊 (0)
打賞 微信掃一掃 微信掃一掃 支付寶掃一掃 支付寶掃一掃
投稿專員的頭像投稿專員
0 0
上一篇 2024-12-23 13:40
下一篇 2024-12-23 13:40

相關推薦

發表回復

登錄後才能評論