資料庫系列：覆蓋索引和規避回表

Brand發表於2023-04-04
資料庫索引

1 介紹

在MySQL資料庫查詢過程中，索引覆蓋和避免不必要的回表，是減少檢索步驟，提高執行效率的有效手段。下面從這兩個角度分析如何進行MySQL檢索提效。

2 資料準備

模擬一個500w資料容量的部門表 emp，表結構如下，並透過工具模擬500w的資料：

CREATE TABLE `emp` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `empno` int unsigned DEFAULT NULL,
  `empname` varchar(50) DEFAULT NULL,
  `job` varchar(50) DEFAULT NULL,
  `mgr` int DEFAULT 1,
  `hiredate` datetime DEFAULT NULL ,
  `sal` int DEFAULT 0,
  `comn` int DEFAULT 0,
  `depno` int DEFAULT 100,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2345 DEFAULT CHARSET=utf8;

3 分析一下回表

3.1 回表的概念

先來了解兩個基本概念，一級索引和二級索引：

  • 一級索引：索引和資料儲存在一起，在同一個B+tree中的葉子節點中。一般主鍵索引都是一級索引。
  • 二級索引：二級索引樹的葉節點僅儲存主鍵而沒有資料。當找到索引後，拿到對應的主鍵，再回到一級索引中找主鍵對應的資料記錄。

回表的本質就是：透過二級索引找到B+樹中的葉子結點，但二級索引的葉子節點的內容並不完全，只有索引列的值和主鍵key值。
我們需要拿主鍵值再去主鍵（聚集）索引的葉子節點中去獲取完整的資料，這樣的查詢等同於需要多掃描一棵索引樹，這就是回表。
image
上圖中我們以empname二級索引為例，先透過二級索引找到葉結點中的索引的主鍵Id，在透過回表檢索以及索引樹，透過該Id獲得完整的記錄資訊。
圖中『主鍵索引檢索過程』，表示的就是回表的操作。

2.2 回表的效能代價

從上面那種圖中可以看出，我們透過empname欄位查詢二級索引的葉子節點，再透過回表，最後拿到了我們的需要的資料。
我們來分析下這個的效能問題：

  • 我們在empname欄位上建立了索引，會透過索引定位資料，避免了全表掃描。
  • 根據B+Tree的特性，葉子節點所在的Page，都是透過雙向連結串列進行關的聯，遍歷檢索的效率比較高；
  • 同一個索引的葉子節點資料會在多Page磁碟空間中儘量相鄰，避免隨便IO或多次IO，帶來效能損耗。

雖然MySQL做了最佳化，但是我們的二級索引檢索完成之後還是需要拿著主鍵Id再去主鍵索引樹中再檢索一次。在進行回表的時候，也極有可能出現主鍵id所在的記錄在聚簇索引葉子節點不斷變化的情況，這樣就會導致隨機IO。而且如果資料內容不在記憶體中，還要從磁碟中載入。一個16kb的page，對效能的損耗還是比較大的。
所以，想報保證MySQL執行的效率，我們只能儘量地減少回表操作帶來的效能消耗：

  • 儘量避免回表
  • 如果查詢的欄位比較多，必須回表，則應該儘量減少回表的次數

既然回表對效能有損，如何避免回表呢？就是查詢的欄位，透過索引可以直接全部拿到，不需要透過主鍵索引再次去取。
則該索引稱之為索引覆蓋，索引覆蓋可以提高查詢的效率，下面會詳細說到。

3 關於索引覆蓋

3.1 索引覆蓋

什麼是索引覆蓋麼，可以看一下官方的定義：

What is a covering index?
A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional lookup to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation.

大意就是：只需在一棵索引樹上就能獲取SQL所需的所有資料元素，無需回表無需額外操作，單次輪詢即可，速度更快。
結合我們的emp表來說，如果二級索引上的葉子節點上有我們想要的所有資料，那就不需要回表了。
比如我為empname和job 兩個欄位建立了一個組合索引，而我們檢索的也恰好是這兩個欄位，這時候單次查詢就可以達到目的，不需要回表。
如下圖：
image

SELECT id, empname, job FROM emp WHERE empname = "Deny";

我們把索引中已經包含了所有需要獲取的所有欄位的查詢方式稱為覆蓋索引（或索引覆蓋）。

3.2 索引覆蓋實踐

  • 建立索引
create index idx_emp_empname_job on emp(empname(5),job);
  • Explain 執行計劃分析
explain  SELECT id, empname, job FROM emp WHERE empname = "Deny";

explain的輸出結果Extra欄位為Using index時，能夠觸發索引覆蓋。如下圖:

image

  • 查詢最佳化建議

在上面建立的索引前提下，如果透過empname進行資料檢索：

select * from emp where empname = ?

需要需要在name索引中找到name對應的Id，然後透過獲取的Id在主鍵索引中查到對應的行。整個過程需要掃描兩次索引，一次empname，一次id。

如果我們查詢只想查詢id的值，就可以改寫SQL為：

select id from emp where empname = ?

因為只需要id的值，透過name查詢的時候，掃描完name索引，我們就能夠獲得id的值了，所以就不需要再去掃面id索引，就會直接返回，避免了回表。

當然，如果你同時需要獲取hiredate的值：

select id,empname,hiredate from emp where empname = ?

這樣就無法使用到覆蓋索引了。

知道了覆蓋索引，就知道了為什麼sql中要求儘量不要使用select *，要寫明具體要查詢的欄位。其中一個原因就是在使用到覆蓋索引的情況下，不需要進入到資料區，資料就能直接返回，提升了查詢效率。

相關文章