MySQL查詢優化利刃-EXPLAIN

有一個 ?

遇到這樣一個疑問：當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 命令的輸出內容為一個表格形式，表的每一個欄位含義如下：

select_type

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

標籤：MySQL

上一篇：docker下MySQL修改配置

下一篇：MySQL使用order by field()自定義排序