mysql利用覆蓋索引避免回表優化查詢

Posted on by

前言

說到覆蓋索引之前，先要瞭解它的數據結構：B+樹。

先建個表演示(為瞭簡單,id按順序建)：

id name
1 aa
3 kl
5 op
8　 aa
10 kk
11 kl
14 jk
16 ml
17 mn
18 kl
19 kl
22 hj
24 io
25 vg
29 jk
31 jk
33 rt
34 ty
35 yu
37 rt
39 rt
41 ty
45 qt
47 ty
53 qi
57 gh
61 dh

 以主鍵以外的列值作為鍵值構建的 B+ 樹索引，我們稱之為非聚集索引。

非聚集索引與聚集索引的區別在於非聚集索引的葉子節點不存儲表中的數據，而是存儲該列對應的主鍵，想要查找數據我們還需要根據主鍵再去聚集索引中進行查找，這個再根據聚集索引查找數據的過程，我們稱為回表。

B+樹

B+樹和B樹是mysql索引的常用數據結構，B+樹是B樹的進一步優化，將上面的表轉成圖分析一下：

B+樹的特點：

1.B+ 樹非葉子節點上是不存儲數據的，僅存儲鍵值

2.葉子節點的數據是按照順序排列的

3. B+ 樹中各個頁之間是通過雙向鏈表連接

聚簇索引和非聚簇索引

B+ 樹索引按照存儲方式的不同分為聚集索引和非聚集索引。

聚簇索引：

以 InnoDB 作為存儲引擎的表，表中的數據都會有一個主鍵，即使你不創建主鍵，系統也會幫你創建一個隱式的主鍵。

這是因為 InnoDB 是把數據存放在 B+ 樹中的，而 B+ 樹的鍵值就是主鍵，在 B+ 樹的葉子節點中，存儲瞭表中所有的數據。

這種以主鍵作為 B+ 樹索引的鍵值而構建的 B+ 樹索引，我們稱之為聚集索引。

非聚簇索引：

以主鍵以外的列值作為鍵值構建的 B+ 樹索引，我們稱之為非聚集索引。

非聚集索引與聚集索引的區別在於非聚集索引的葉子節點不存儲表中的數據，而是存儲該列對應的主鍵，想要查找數據我們還需要根據主鍵再去聚集索引中進行查找，這個再根據聚集索引查找數據的過程，我們稱為回表。

如何用覆蓋索引避免回表

為什麼明明用瞭非主鍵索引還會回表，簡單說就是非主鍵索引是非聚簇索引，在B+樹葉子節點中隻保存主鍵和該非主鍵索引，一次查詢隻能查到這兩個字段，如果想查三個字段，就必須再查一次聚簇索引，這就是回表。

舉個例子，表中新增一個字段age，我們用name建一個索引（非聚簇索引）

id name age
10　 zs 23
7 ls 54
13 ww 12
5 zl 76
8 xw 23
12 xm 43
17 dy 21 
select id,name from user where name = 'zs';

能夠命中name索引，索引葉子節點存儲瞭主鍵id，通過name的索引樹即可獲取id和name，無需回表，符合索引覆蓋，效率較高。

select id,name,age from user where name = 'zs';

能夠命中name索引，索引葉子節點存儲瞭主鍵id，但age字段必須回表查詢才能獲取到，不符合索引覆蓋，需要再次通過id值掃碼聚集索引獲取age字段，效率會降低。　　

結論:那怎麼做才能避免回表呢?很簡單,將單列索引(name)升級為聯合索引(name,age).

總結

到此這篇關於mysql利用覆蓋索引避免回表優化查詢的文章就介紹到這瞭,更多相關mysql覆蓋索引避免回表優化查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：