MySQL索引查詢的具體使用

目錄

• 索引的分類
• 聚簇索引
• 非聚簇索引
• 實戰理解

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

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

索引的分類

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

聚簇索引

聚簇索引會將索引和對應的行記錄數據內容都統一存放在同一個葉子節點中。例如下圖所示：

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

非聚簇索引

非聚簇索引的結構如下所示：

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

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

實戰理解

首先需要創建一張表用于做測試：

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;

然后我們插入一些測試數據：

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結果如下：

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

2.按照c關鍵字查詢

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

explain結果如下：

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

3.按照b關鍵字查詢

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

explain結果如下：

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

4.按照c關鍵字查詢，只返回b,c字段

select b,c from t_common where c=1;

explain結果如下：

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

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

5.按照c關鍵字查詢，返回a,b,c字段

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

explain結果如下：

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

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

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

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

explain結果如下：

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

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

select a,b,c from t_common;

explain結果：

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

到此這篇關于MySQL索引查詢的具體使用的文章就介紹到這了,更多相關MySQL索引查詢內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！