MySQL中的全表掃描和索引樹掃描

Garrett_Wale發表於2022-05-14

引言

在學習mysql時,我們經常會使用explain來檢視sql查詢的索引等優化手段的使用情況。在使用explain時,我們可以觀察到,explain的輸出有一個很關鍵的列,它就是type屬性,type表示的是掃描方式,代表 MySQL 使用了哪種索引型別,不同的索引型別的查詢效率是不一樣的。

在type這一列,有如下一些可能的選項:

  • system:系統表,少量資料,往往不需要進行磁碟IO
  • const:常量連線
  • eq_ref:主鍵索引(primary key)或者非空唯一索引(unique not null)等值掃描
  • ref:非主鍵非唯一索引等值掃描
  • range:範圍掃描
  • index:索引樹掃描
  • ALL:全表掃描(full table scan)

在上面列出的7種選項中,前面五種我就不詳細講了,可以參考Mysql Explain之type詳解這篇文章。我當時對於前五種屬性是比較容易就理解了的,但是對於後面兩種即索引樹掃描和全表掃描我還是存在一些疑問。

索引樹掃描我們是比較熟悉的,它就是會遍歷聚簇索引樹,底層是一顆B+樹,葉子節點儲存了所有的實際行資料。其實,全表掃描也是掃描的聚簇索引樹,因為聚簇索引樹的葉子節點中儲存的就是實際資料,只要掃描遍歷聚簇索引樹就可以得到全表的資料了。

那索引樹掃描和全表掃描究竟有什麼區別呢?

以下將以一個例項來詳細分析這兩種掃描方式的區別。

例項

我們建立一張t_article表:

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

在我們建立的t_article表中,只有兩個欄位,一個是主鍵t_article_id,另一個是普通欄位t_title

我們知道,InnoDB會將聚簇索引預設建立在主鍵上,而聚簇索引樹中的葉子節點就儲存了整張表的行資料。

接著,我們分別設計兩個sql查詢case:

  1. 走主鍵索引
    explain SELECT t_article_id FROM t_article;
    
  2. 走全表掃描:
    explain SELECT t_title FROM t_article;
    

以上兩個查詢都沒有where查詢,按理來說底層的sql執行情況應該是差不多的。

結果分析

我們可以來看看上面兩種查詢的結果,在查詢時使用explain語句輸出sql執行的詳細資訊。

  1. 走索引掃描
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_article index PRIMARY 4 2 100 Using index
  1. 走全表掃描
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t_article ALL 2 100

從以上兩個查詢結果中我們可以發現,走主鍵索引的查詢和走全表的查詢是不一樣的。我們前面也提到了,InnoDB的索引是使用B+樹來實現的,而主鍵索引中儲存了整張表的資料,那全表掃描時其實也是掃描的主鍵索引。那為什麼這兩種查詢會不一樣呢?按理來說都是查詢的主鍵索引,它們應該是一樣的。

其實,它們兩者是有一些細節區別的。

比如,第一個查詢,它的優化手段是使用索引樹掃描,也就是type中顯示的index屬性,而且它還使用了覆蓋索引,即Extra列中的Using index屬性。之所以第一個查詢能夠使用這兩種優化手段,其實是因為select查詢的結果列只包含主鍵,而主鍵的值是可以直接在遍歷聚簇索引樹時確定,也不需要回表查詢了。

對於第二個查詢,它也沒有使用where進行過濾,而且它的select結果列包含的是普通列,並不是主鍵或者其他索引列,所以它會走全表掃描。而全表掃描其實底層也是掃描的聚簇索引樹,也就是底層的B+樹。這種全表掃描與索引樹掃描有一個明顯區別,那就是,全表掃描不僅僅需要掃描索引列,還需要掃描每個索引列中指向的實際資料,這裡包含了所有的非索引列資料。

前面的分析可能還是有點生硬和難以理解,具體地,我們通過下面一張圖來更直觀地看一下:

image
圖片源自:從資料頁的角度看 B+ 樹

從上面的圖我們可以看到,對於索引掃描來講,它只需要讀取葉子節點的所有key,也就是索引的鍵,而不需要讀取具體的data行資料;而對於全表掃描來說,它無法僅僅通過讀取索引列獲得需要的資料,還需要讀取具體的data資料才能獲取select中指定的非索引列的具體值。所以,全表掃描的效率相比於索引樹掃描相對較低一點,但是差距不是很大。

參考

【mysql】全表掃描過程 & 聚簇索引 區別和聯絡
從資料頁的角度看 B+ 樹

相關文章