前言

說(shuō)到覆蓋索引之前，先要了解它的數(shù)據(jù)結(jié)構(gòu)：B+樹。

先建個(gè)表演示(為了簡(jiǎn)單,id按順序建)：

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

非聚集索引與聚集索引的區(qū)別在于非聚集索引的葉子節(jié)點(diǎn)不存儲(chǔ)表中的數(shù)據(jù)，而是存儲(chǔ)該列對(duì)應(yīng)的主鍵，想要查找數(shù)據(jù)我們還需要根據(jù)主鍵再去聚集索引中進(jìn)行查找，這個(gè)再根據(jù)聚集索引查找數(shù)據(jù)的過(guò)程，我們稱為回表。

B+樹

B+樹和B樹是mysql索引的常用數(shù)據(jù)結(jié)構(gòu)，B+樹是B樹的進(jìn)一步優(yōu)化，將上面的表轉(zhuǎn)成圖分析一下：

B+樹的特點(diǎn)：

1.B+ 樹非葉子節(jié)點(diǎn)上是不存儲(chǔ)數(shù)據(jù)的，僅存儲(chǔ)鍵值

2.葉子節(jié)點(diǎn)的數(shù)據(jù)是按照順序排列的

3. B+ 樹中各個(gè)頁(yè)之間是通過(guò)雙向鏈表連接

聚簇索引和非聚簇索引

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

聚簇索引：

以 InnoDB 作為存儲(chǔ)引擎的表，表中的數(shù)據(jù)都會(huì)有一個(gè)主鍵，即使你不創(chuàng)建主鍵，系統(tǒng)也會(huì)幫你創(chuàng)建一個(gè)隱式的主鍵。

這是因?yàn)?InnoDB 是把數(shù)據(jù)存放在 B+ 樹中的，而 B+ 樹的鍵值就是主鍵，在 B+ 樹的葉子節(jié)點(diǎn)中，存儲(chǔ)了表中所有的數(shù)據(jù)。

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

非聚簇索引：

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

非聚集索引與聚集索引的區(qū)別在于非聚集索引的葉子節(jié)點(diǎn)不存儲(chǔ)表中的數(shù)據(jù)，而是存儲(chǔ)該列對(duì)應(yīng)的主鍵，想要查找數(shù)據(jù)我們還需要根據(jù)主鍵再去聚集索引中進(jìn)行查找，這個(gè)再根據(jù)聚集索引查找數(shù)據(jù)的過(guò)程，我們稱為回表。

如何用覆蓋索引避免回表

為什么明明用了非主鍵索引還會(huì)回表，簡(jiǎn)單說(shuō)就是非主鍵索引是非聚簇索引，在B+樹葉子節(jié)點(diǎn)中只保存主鍵和該非主鍵索引，一次查詢只能查到這兩個(gè)字段，如果想查三個(gè)字段，就必須再查一次聚簇索引，這就是回表。

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

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

能夠命中name索引，索引葉子節(jié)點(diǎn)存儲(chǔ)了主鍵id，通過(guò)name的索引樹即可獲取id和name，無(wú)需回表，符合索引覆蓋，效率較高。

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

能夠命中name索引，索引葉子節(jié)點(diǎn)存儲(chǔ)了主鍵id，但age字段必須回表查詢才能獲取到，不符合索引覆蓋，需要再次通過(guò)id值掃碼聚集索引獲取age字段，效率會(huì)降低?！　?/p>

結(jié)論:那怎么做才能避免回表呢?很簡(jiǎn)單,將單列索引(name)升級(jí)為聯(lián)合索引(name,age).

總結(jié)

到此這篇關(guān)于mysql利用覆蓋索引避免回表優(yōu)化查詢的文章就介紹到這了,更多相關(guān)mysql覆蓋索引避免回表優(yōu)化查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論