引言

在學(xué)習(xí)mysql時(shí)，我們經(jīng)常會(huì)使用explain來(lái)查看sql查詢(xún)的索引等優(yōu)化手段的使用情況。在使用explain時(shí)，我們可以觀察到，explain的輸出有一個(gè)很關(guān)鍵的列，它就是type屬性，type表示的是掃描方式，代表 MySQL 使用了哪種索引類(lèi)型，不同的索引類(lèi)型的查詢(xún)效率是不一樣的。

在type這一列，有如下一些可能的選項(xiàng)：

system：系統(tǒng)表，少量數(shù)據(jù)，往往不需要進(jìn)行磁盤(pán)IOconst：常量連接eq_ref：主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描ref：非主鍵非唯一索引等值掃描range：范圍掃描index：索引樹(shù)掃描ALL：全表掃描(full table scan)

在上面列出的7種選項(xiàng)中，前面五種我就不詳細(xì)講了，可以參考Mysql Explain之type詳解這篇文章。我當(dāng)時(shí)對(duì)于前五種屬性是比較容易就理解了的，但是對(duì)于后面兩種即索引樹(shù)掃描和全表掃描我還是存在一些疑問(wèn)。

索引樹(shù)掃描我們是比較熟悉的，它就是會(huì)遍歷聚簇索引樹(shù)，底層是一顆B+樹(shù)，葉子節(jié)點(diǎn)存儲(chǔ)了所有的實(shí)際行數(shù)據(jù)。其實(shí)，全表掃描也是掃描的聚簇索引樹(shù)，因?yàn)榫鄞厮饕龢?shù)的葉子節(jié)點(diǎn)中存儲(chǔ)的就是實(shí)際數(shù)據(jù)，只要掃描遍歷聚簇索引樹(shù)就可以得到全表的數(shù)據(jù)了。

那索引樹(shù)掃描和全表掃描究竟有什么區(qū)別呢？

以下將以一個(gè)實(shí)例來(lái)詳細(xì)分析這兩種掃描方式的區(qū)別。

實(shí)例

我們建立一張t_article表：

create table t_article(
    t_article_id int primary key auto_increment,
    t_title varchar(40),
);

在我們創(chuàng)建的t_article表中，只有兩個(gè)字段，一個(gè)是主鍵t_article_id，另一個(gè)是普通字段t_title。

我們知道，InnoDB會(huì)將聚簇索引默認(rèn)建立在主鍵上，而聚簇索引樹(shù)中的葉子節(jié)點(diǎn)就存儲(chǔ)了整張表的行數(shù)據(jù)。

接著，我們分別設(shè)計(jì)兩個(gè)sql查詢(xún)case：

走主鍵索引

explain SELECT t_article_id FROM t_article;

走全表掃描：

explain SELECT t_title FROM t_article;

以上兩個(gè)查詢(xún)都沒(méi)有where查詢(xún)，按理來(lái)說(shuō)底層的sql執(zhí)行情況應(yīng)該是差不多的。

結(jié)果分析

我們可以來(lái)看看上面兩種查詢(xún)的結(jié)果，在查詢(xún)時(shí)使用explain語(yǔ)句輸出sql執(zhí)行的詳細(xì)信息。

1.走索引掃描

2.走全表掃描

從以上兩個(gè)查詢(xún)結(jié)果中我們可以發(fā)現(xiàn)，走主鍵索引的查詢(xún)和走全表的查詢(xún)是不一樣的。我們前面也提到了，InnoDB的索引是使用B+樹(shù)來(lái)實(shí)現(xiàn)的，而主鍵索引中存儲(chǔ)了整張表的數(shù)據(jù)，那全表掃描時(shí)其實(shí)也是掃描的主鍵索引。那為什么這兩種查詢(xún)會(huì)不一樣呢？按理來(lái)說(shuō)都是查詢(xún)的主鍵索引，它們應(yīng)該是一樣的。

其實(shí)，它們兩者是有一些細(xì)節(jié)區(qū)別的。

比如，第一個(gè)查詢(xún)，它的優(yōu)化手段是使用索引樹(shù)掃描，也就是type中顯示的index屬性，而且它還使用了覆蓋索引，即Extra列中的Using index屬性。之所以第一個(gè)查詢(xún)能夠使用這兩種優(yōu)化手段，其實(shí)是因?yàn)閟elect查詢(xún)的結(jié)果列只包含主鍵，而主鍵的值是可以直接在遍歷聚簇索引樹(shù)時(shí)確定，也不需要回表查詢(xún)了。

對(duì)于第二個(gè)查詢(xún)，它也沒(méi)有使用where進(jìn)行過(guò)濾，而且它的select結(jié)果列包含的是普通列，并不是主鍵或者其他索引列，所以它會(huì)走全表掃描。而全表掃描其實(shí)底層也是掃描的聚簇索引樹(shù)，也就是底層的B+樹(shù)。這種全表掃描與索引樹(shù)掃描有一個(gè)明顯區(qū)別，那就是，全表掃描不僅僅需要掃描索引列，還需要掃描每個(gè)索引列中指向的實(shí)際數(shù)據(jù)，這里包含了所有的非索引列數(shù)據(jù)。

前面的分析可能還是有點(diǎn)生硬和難以理解，具體地，我們通過(guò)下面一張圖來(lái)更直觀地看一下：

圖片源自:從數(shù)據(jù)頁(yè)的角度看 B+ 樹(shù)

從上面的圖我們可以看到，對(duì)于索引掃描來(lái)講，它只需要讀取葉子節(jié)點(diǎn)的所有key，也就是索引的鍵，而不需要讀取具體的data行數(shù)據(jù)；而對(duì)于全表掃描來(lái)說(shuō)，它無(wú)法僅僅通過(guò)讀取索引列獲得需要的數(shù)據(jù)，還需要讀取具體的data數(shù)據(jù)才能獲取select中指定的非索引列的具體值。所以，全表掃描的效率相比于索引樹(shù)掃描相對(duì)較低一點(diǎn)，但是差距不是很大。

參考

【mysql】全表掃描過(guò)程 & 聚簇索引 區(qū)別和聯(lián)系
從數(shù)據(jù)頁(yè)的角度看 B+ 樹(shù)

到此這篇關(guān)于MySQL中的全表掃描和索引樹(shù)掃描 的文章就介紹到這了,更多相關(guān)mysql全表掃描和索引樹(shù)掃描 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論