(保留出處并非什麼原創作品權利,本人拙作還遠遠達不到,僅僅是為了連結到原文,因為後續對可能存在的一些錯誤進行修正或補充,無他)
mysql執行計劃中的extra列中表明了執行計劃的每一步中的實作細節,其中包含了與索引相關的一些細節資訊
其中跟索引有關的using index 在不同的情況下會出現Using index, Using where Using index ,Using index condition等
那麼Using index 和 Using where;Using index 有什麼差別?網上搜了一大把文章,說實在話也沒怎麼弄懂,于是就自己動手試試。
本文僅從最簡單的單表去測試using index 和 using where using index以及簡單測試using index condition的情況的出現時機 。
執行計劃的生成與表結構,表資料量,索引結構,統計資訊等等上下文等多種環境有關,無法一概而論,複雜情況另論。
測試環境搭建
測試表以及測試資料搭建,類似于訂單表和訂單明細表,暫時先用訂單表做測試
測試表結構
測試資料(50W)
Using index VS Using where Using index
首先,在"訂單表"上,這裡是一個多列複合索引
create index idx_userid_order_id_createdate on test_order(user_id,order_id,create_date);
Using index
1,查詢的列被索引覆寫,并且where篩選條件是索引的是前導列,Extra中為Using index
Using where Using index
1,查詢的列被索引覆寫,并且where篩選條件是索引列之一但是不是索引的不是前導列,Extra中為Using where; Using index,
意味着無法直接通過索引查找來查詢到符合條件的資料
2,查詢的列被索引覆寫,并且where篩選條件是索引列前導列的一個範圍,同樣意味着無法直接通過索引查找查詢到符合條件的資料
NULL(既沒有Using index,也沒有Using where Using index,也沒有using where)
1,查詢的列未被索引覆寫,并且where篩選條件是索引的前導列,
意味着用到了索引,但是部分字段未被索引覆寫,必須通過“回表”來實作,不是純粹地用到了索引,也不是完全沒用到索引,Extra中為NULL(沒有資訊)
Using where
1,查詢的列未被索引覆寫,where篩選條件非索引的前導列,Extra中為Using where
2,查詢的列未被索引覆寫,where篩選條件非索引列,Extra中為Using where
using where 意味着通過索引或者表掃描的方式程序where條件的過濾,
反過來說,也就是沒有可用的索引查找,當然這裡也要考慮索引掃描+回表與表掃描的代價。
這裡的type都是all,說明MySQL認為全表掃描是一種比較低的代價。
Using index condition
1,查詢的列不全在索引中,where條件中是一個前導列的範圍
2,查詢列不完全被索引覆寫,查詢條件完全可以使用到索引(進行索引查找)
參考:MySQL · 特性分析 · Index Condition Pushdown (ICP)
using index conditoin 意味着查詢列的某一部分無法直接使用索引
上述case1中,
如果禁用ICP(set optimizer_switch='index_condition_pushdown=off'),
執行計劃是using where,意味着全表掃描,如果啟用ICP,執行計劃為using index Condition,意味着在篩選的過程中實作過濾
上述case1中
第二個查詢條件無法直接使用索引,隐含了一個查找+篩選的過程。
兩個case的共同點就是無法直接使用索引。
結論:
1,Extra中的為Using index的情況
where篩選列是索引的前導列 &&查詢列被索引覆寫 && where篩選條件是一個基于索引前導列的查詢,意味着通過索引超找就能直接找到符合條件的資料,并且無須回表
2,Extra中的為空的情況
查詢列存在未被索引覆寫&&where篩選列是索引的前導列,意味着通過索引超找并且通過“回表”來找到未被索引覆寫的字段,
3,Extra中的為Using where Using index:
出現Using where Using index意味着是通過索引掃描(或者表掃描)來實作sql語句執行的,即便是索引前導列的索引範圍查找也有一點範圍掃描的動作,不管是前非索引前導列引起的,還是非索引列查詢引起的。
尚未解決的問題:
查詢1
查詢2
查詢3(邏輯上等價于查詢1+查詢2),執行計劃發生了很大的變化。
總結:
MySQL執行計劃中的Extra中資訊非常多,不僅僅包括Using index,Using where Using index,Using index condition,Using where,尤其是在多表連接配接的時候,這一點在相對MSSQL來說,不夠直覺或者結構化。
MSSQL中是通過區分索引查找(index seek),索引掃描(index scan),表掃描(table scan)來實作具體的查詢的,這圖形化的執行計劃在不同的場景下是非常直覺的,要想完全弄懂MySQL的這個執行計劃,可能要更多地在實踐中摸索。