MySQL查詢優化利刃-EXPLAIN

稻草堆上打著滾兒發表於2020-06-08
MySql優化AI

有一個 ?

遇到這樣一個疑問：當where查詢中In一個索引欄位作為條件，那麼在查詢中還會使用到索引嗎？

SELECT * FROM table_name WHERE column_index in (expr)

上面的sql語句檢索會使用到索引嗎？帶著這個問題，在網上查詢了很多文章，但是有的說 in 會導致放棄索引，全表掃描；有的說Mysql5.5之前的版本不會走，之後的innodb版本會走索引...

越看越迷糊，那答案到底是怎樣的呢？

唯有實踐是檢驗真理的唯一方式！

拿出我們的利刃——EXPLAIN，去剖析 SELECT 語句，一探究竟！

EXPLAIN 的用法

在 SELECT 語句前加上 EXPLAIN 就可以了 ，例如：

EXPLAIN SELECT * FROM table_name [WHERE Clause]

EXPLAIN 的輸出

EXPLAIN 命令的輸出內容為一個表格形式，表的每一個欄位含義如下：

列名 解釋
id SELECT 查詢的識別符號. 每個 SELECT 都會自動分配一個唯一的識別符號
select_type SELECT 查詢的型別
table 查詢的是哪個表
partitions 匹配的分割槽
type join 型別
possible_keys 此次查詢中可能選用的索引
key 此次查詢中確切使用到的索引
ref 哪個欄位或常數與 key 一起被使用;與索引比較的列
rows 顯示此查詢一共掃描了多少行, 這個是一個估計值
filtered 表示此查詢條件所過濾的資料的百分比
extra 額外的資訊
select_type
查詢型別 解釋
SIMPLE 表示此查詢不包含 UNION 查詢或子查詢
PRIMARY 表示此查詢是最外層的查詢
UNION 表示此查詢是 UNION 的第二或隨後的查詢
DEPENDENT UNION UNION 中的第二個或後面的查詢語句, 取決於外面的查詢
UNION RESULT UNION 的結果
SUBQUERY 子查詢中的第一個 SELECT
DEPENDENT SUBQUERY 子查詢中的第一個 SELECT，取決於外面的查詢。子查詢依賴於外層查詢的結果
MATERIALIZED Materialized subquery
table

表示查詢涉及的表或衍生表 。 這也可以是以下值之一：

  • <unionM,N>：該行指的是具有和id值的行 的 M並集 N。
  • ：該行是指用於與該行的派生表結果id的值 N。派生表可能來自FROM子句中的子查詢 。
  • ：該行是指該行的物化子查詢的結果，其id 值為N。
partitions

查詢將匹配記錄的分割槽。該值適用NULL於未分割槽的表。

type

聯接型別。 提供了判斷查詢是否高效的重要依據依據。通過 type 欄位，我們判斷此次查詢是全表掃描還是索引掃描等。 從最佳型別到最差型別：

  • system： 該表只有一行（=系統表）。這是const聯接型別的特例 。

  • const： 針對主鍵或唯一索引的等值查詢掃描，最多隻返回一行資料。const 查詢速度非常快，因為它僅僅讀取一次即可 。

    SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

  • eq_ref： 此型別通常出現在多表的 join 查詢，表示對於前表的每一個結果，都只能匹配到後表的一行結果。並且查詢的比較操作通常是 =，查詢效率較高

    SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

  • ref ： 此型別通常出現在多表的 join 查詢，針對於非唯一或非主鍵索引，或者是使用了最左字首規則索引的查詢。ref可以用於使用=或<=> 運算子進行比較的索引列。

    SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

  • ref_or_null： 這種連線型別類似於 ref，但是除了MySQL會額外搜尋包含NULL值的行。此聯接型別優化最常用於解析子查詢。

    SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

  • unique_subquery： 只是一個索引查詢函式，它完全替代了子查詢以提高效率。

    value IN (SELECT primary_key FROM single_table WHERE some_expr)

  • index_subquery：此連線型別類似於 unique_subquery。它代替IN子查詢，但適用於以下形式的子查詢中的非唯一索引。

  • range： 表示使用索引範圍查詢, 通過索引欄位範圍獲取表中部分資料記錄。這個型別通常出現在 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 操作中。

    當 type 是 range 時，那麼 EXPLAIN 輸出的 ref 欄位為 NULL，並且 key_len 欄位是此次查詢中使用到的索引的最長的那個 。

    SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

  • index： 表示全索引掃描(full index scan)和 ALL 型別類似，只不過 ALL 型別是全表掃描，而 index 型別則僅僅掃描所有的索引，而不掃描資料。

    index 型別通常出現在: 所要查詢的資料直接在索引樹中就可以獲取到，而不需要掃描資料。當是這種情況時，Extra 欄位 會顯示 Using index

  • ALL： 表示全表掃描，這個型別的查詢是效能最差的查詢之一。

    我們的查詢不應該出現 ALL 型別的查詢，因為這樣的查詢在資料量大的情況下，對資料庫的效能是巨大的災難。如一個查詢是 ALL 型別查詢，那麼一般來說可以對相應的欄位新增索引來避免 。

possible_keys

表示 MySQL 在查詢時，能夠使用到的索引。

即使有些索引在 possible_keys 中出現，但是並不表示此索引會真正地被 MySQL 使用到。MySQL 在查詢時具體使用了哪些索引，由 key 欄位決定。

key

是 MySQL 在當前查詢時所真正使用到的索引。

key_len

表示查詢優化器使用了索引的位元組數。

這個欄位可以評估組合索引是否完全被使用，或只有最左部分欄位被使用到。key_len 的計算規則如下：

  • 字串
    • char(n): n 位元組長度
    • varchar(n): 如果是 utf8 編碼, 則是 3n + 2位元組; 如果是 utf8mb4 編碼, 則是 4n + 2 位元組
  • 數值型別
  • TINYINT: 1位元組
  • SMALLINT: 2位元組
  • MEDIUMINT: 3位元組
  • INT: 4位元組
  • BIGINT: 8位元組
  • 時間型別
  • DATE: 3位元組
  • TIMESTAMP: 4位元組
  • DATETIME: 8位元組
  • 欄位屬性： NULL 屬性 佔用一個位元組。如果一個欄位是 NOT NULL 的, 則沒有此屬性
rows

查詢優化器根據統計資訊，估算 SQL 要查詢到結果集需要掃描讀取的資料行數。這個值非常直觀顯示 SQL 的效率好壞，原則上 rows 越少越好。

這個 rows 就是 mysql 認為必須要逐行去檢查和判斷的記錄的條數。舉個例子來說，假如有一個語句 select * from t where column_a = 1 and column_b = 2; 全表假設有 100 條記錄，column_a 欄位有索引（非聯合索引），column_b沒有索引。column_a = 1 的記錄有 20 條， column_a = 1 and column_b = 2 的記錄有 5 條。

Extra

EXplain 中的很多額外的資訊會在 Extra 欄位顯示，常見的有以下幾種內容：

  • Using filesort：當 Extra 中有 Using filesort 時，表示 MySQL 需額外的排序操作，不能通過索引順序達到排序效果。一般有 Using filesort，都建議優化去掉，因為這樣的查詢 CPU 資源消耗大。
  • Using index："覆蓋索引掃描"，表示查詢在索引樹中就可查詢所需資料，不用掃描表資料檔案，往往說明效能不錯
  • Using temporary：查詢有使用臨時表，一般出現於排序，分組和多表 join 的情況，查詢效率不高，建議優化
  • Using where： WHERE子句用於限制哪些行與下一個表匹配或傳送給客戶端 。

得出結論

說到最後，那 WHERE column_index in (expr) 到底走不走索引呢？ 答案是不確定的。

走不走索引是由 expr 來決定的，不是一概而論走還是不走。

SELECT * FROM a WHERE id in (1,23,456,7,8)
-- id 是主鍵，查詢是走索引的。type = range,key = PRIMARY
SELECT * FROM a WHERE id in (SELECT b.a_id FROM b WHERE some_expr)
-- id 是主鍵，如果 some_expr 是一個索引查詢，那麼 select a 將走索引；
-- some_expr 不是索引查詢，那麼 select a 將全表掃描；

上面是兩個通用案例，但到底對不對了，還是自己去實踐最好了，拿起EXPLAIN去剖析吧~

參考文章： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain

相關文章