我們都知道MySQL的輔助索引可以提升檢索效率，但是為什么有的時候，走輔助索引反而不如走主鍵索引的效率高呢？這里我覺得需要先弄懂輔助索引的底層原理以及回表查詢的概念。

ps：下邊我們討論的場景主要是針對innodb存儲引擎為前提。

索引的分類

在我們給MySQL表建立索引的時候，一共有兩種，分別是聚簇索引，非聚簇索引。

聚簇索引

聚簇索引會將索引和對應(yīng)的行記錄數(shù)據(jù)內(nèi)容都統(tǒng)一存放在同一個葉子節(jié)點中。例如下圖所示：

從上圖中我們可以看到，最上頭是非葉子結(jié)點，這種非葉子結(jié)點里面存儲的是主鍵id的值，而非葉子結(jié)點的內(nèi)部會有個數(shù)據(jù)頁的指針，這些指針會指向下層的B+樹節(jié)點，一般B+樹的最底層我們稱之為葉子結(jié)點。在聚簇索引的葉子結(jié)點里面，會存儲主鍵id和對應(yīng)的行記錄內(nèi)容。

非聚簇索引

非聚簇索引的結(jié)構(gòu)如下所示：

可以看到，在非聚簇索引中，所有的葉子結(jié)點都包含了輔助索引的值和主鍵的值。而當(dāng)我們要根據(jù)輔助索引查詢的時候，最終就會通過使用輔助索引定位到具體的葉子結(jié)點，最后根據(jù)葉子節(jié)點里面的主鍵id去聚簇索引的b+樹中檢索具體的行記錄。

下邊我們通過一組代碼案例來深入了解下回表的知識點。

實戰(zhàn)理解

首先需要創(chuàng)建一張表用于做測試：

CREATE TABLE `t_common` (
  `a` int unsigned NOT NULL AUTO_INCREMENT,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  `d` int DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `ud_b_c` (`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;

然后我們插入一些測試數(shù)據(jù)：

INSERT INTO `t_common` (`a`, `b`, `c`, `d`)
VALUES
	(1, 1, 1, 1),
	(2, 2, 2, 2);

接著我們來看看下邊的幾個sql案例：

1.全表掃描

select a,b,c,d from t_common;

explain結(jié)果如下：

可以看到這條sql需要從mysql中檢索出a,b,c,d四個字段，走的是全表掃描，并沒有走索引。

2.按照c關(guān)鍵字查詢

select a,b,c,d from t_common where c=1;

explain結(jié)果如下：

可以看到，這里也是走了全表掃描。

3.按照b關(guān)鍵字查詢

select a,b,c,d from t_common where c=1;

explain結(jié)果如下：

可以看到，結(jié)果是走了b,c聯(lián)合索引。這里的結(jié)果也應(yīng)證了最左匹配原則的說法。但是這里因為查詢出來的d字段不在bc索引樹上，因此需要回表。

4.按照c關(guān)鍵字查詢，只返回b,c字段

select b,c from t_common where c=1;

explain結(jié)果如下：

這種情況有點特殊，按理說他是不滿足最左匹配原則的，但是由于檢索的內(nèi)容正好是輔助索引的字段，同時掃描輔助索引的IO開銷要比掃描主鍵索引的IO開銷小，所以這里的查詢對輔助索引樹進行了全表掃描。

（開銷更小的原因是：因為主鍵索引存儲的是行記錄,加載的數(shù)據(jù)更多。走普通索引的時候,葉子節(jié)點存儲的是主鍵id值,這樣一次加載的數(shù)據(jù)會更多,走普通索引效率比主鍵索引要高。）

5.按照c關(guān)鍵字查詢，返回a,b,c字段

select a,b,c from t_common where c=1;

explain結(jié)果如下：

這種情況和上邊的情況相同，由于c的查詢不滿足最左匹配原則，原先是不不應(yīng)該走b,c索引的，但是后期優(yōu)化器發(fā)現(xiàn)，需要查詢的字段正好是輔助索引的字段內(nèi)容，而掃描輔助索引的IO開銷要比掃描主鍵索引的IO開銷小，所以這里的查詢對輔助索引樹進行了全表掃描。

（開銷更小的原因是：因為主鍵索引存儲的是行記錄,加載的數(shù)據(jù)更多。走普通索引的時候,葉子節(jié)點存儲的是主鍵id值,這樣一次加載的數(shù)據(jù)會更多,走普通索引效率比主鍵索引要高。）

6.按照b關(guān)鍵字進行查詢，查詢a,b,c,是否有回表

select a,b,c from t_common where b=1;

explain結(jié)果如下：

這種情況下，要注意，由于我們的bc索引的葉子結(jié)點包含了主鍵的值，所以其實減少了回表查詢的情況。但是如果我們看回上邊所說的第三種情況，第三種查詢其實還需要通過一次回表的操作，去查詢d的值。

7.如果查詢的字段包含了主鍵索引和輔助索引，優(yōu)先走輔助索引

select a,b,c from t_common;

explain結(jié)果：

因為主鍵索引存儲的是行記錄,加載的數(shù)據(jù)更多。走普通索引的時候,葉子節(jié)點存儲的是主鍵id值,這樣一次加載的數(shù)據(jù)會更多,走普通索引效率比主鍵索引要高。所以這條sql直接掃描了整個b,c聯(lián)合索引樹。

到此這篇關(guān)于MySQL索引查詢的具體使用的文章就介紹到這了,更多相關(guān)MySQL索引查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論