快捷導(dǎo)航

MySQL索引查詢的具體使用

更新時(shí)間：2023年05月08日 09:45:24 作者：DannyIdea

本文主要介紹了MySQL索引查詢的具體使用，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

索引的分類

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

聚簇索引

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

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

非聚簇索引

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

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

下邊我們通過一組代碼案例來深入了解下回表的知識(shí)點(diǎn)。

實(shí)戰(zhàn)理解

首先需要?jiǎng)?chuàng)建一張表用于做測(cè)試：

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;

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

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

接著我們來看看下邊的幾個(gè)sql案例：

1.全表掃描

select a,b,c,d from t_common;

explain結(jié)果如下：

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

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)證了最左匹配原則的說法。但是這里因?yàn)椴樵兂鰜淼膁字段不在bc索引樹上，因此需要回表。

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

select b,c from t_common where c=1;

explain結(jié)果如下：

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

（開銷更小的原因是：因?yàn)橹麈I索引存儲(chǔ)的是行記錄,加載的數(shù)據(jù)更多。走普通索引的時(shí)候,葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵id值,這樣一次加載的數(shù)據(jù)會(huì)更多,走普通索引效率比主鍵索引要高。）

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開銷小，所以這里的查詢對(duì)輔助索引樹進(jìn)行了全表掃描。

（開銷更小的原因是：因?yàn)橹麈I索引存儲(chǔ)的是行記錄,加載的數(shù)據(jù)更多。走普通索引的時(shí)候,葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵id值,這樣一次加載的數(shù)據(jù)會(huì)更多,走普通索引效率比主鍵索引要高。）

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

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

explain結(jié)果如下：

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

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

select a,b,c from t_common;

explain結(jié)果：

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

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

您可能感興趣的文章: