MySQL性能優化之explain分析

MySQL 提供了explain 指令， 它可以對 SELECT 語句進行分析，并輸出 SELECT 執行的詳細資訊， 以供開發人員針對性優化。深入了解MySQL基于開銷的優化器，可以獲得很多可能被優化器考慮到的通路政策的細節，以及當運作SQL語句時哪種政策預計會被優化器采用。explain指令用法十分簡單, 在 SELECT 語句前加上explain就可以了。

mysql> explain select * from table1;

+----+-------------+--------+------+---------------+------+---------+------+------+-------+

| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |

+----+-------------+--------+------+---------------+------+---------+------+------+-------+

|  1 | SIMPLE      | table1 | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |

+----+-------------+--------+------+---------------+------+---------+------+------+-------+

expain出來的資訊有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。

一、 id：SELECT 查詢的辨別符. 每個 SELECT 都會自動配置設定一個唯一的辨別符。

1）、 id相同時，執行順序由上至下；

2）、 如果是子查詢，id的序号會遞增，id值越大優先級越高，越先被執行；

3）、id如果相同，可以認為是一組，從上往下順序執行；在所有組中，id值越大，優先級越高，越先執行。 

注意：根據笛卡爾積，資料量小的表優先查詢

二、select_type：表示查詢中每個select子句的類型

1) 、SIMPLE(簡單SELECT，不使用UNION或子查詢等)

2)、 PRIMARY(查詢中若包含任何複雜的子部分，最外層的select被标記為PRIMARY)

3) 、UNION(UNION中的第二個或後面的SELECT語句)

4) 、DEPENDENT UNION(UNION中的第二個或後面的SELECT語句，取決于外面的查詢)

5) 、UNION RESULT(UNION的結果)

6)、 SUBQUERY(子查詢中的第一個SELECT)

7)、 DEPENDENT SUBQUERY(子查詢中的第一個SELECT，取決于外面的查詢)

8) 、DERIVED(派生表的SELECT， FROM子句的子查詢)

9)、 UNCACHEABLE SUBQUERY(一個子查詢的結果不能被緩存，必須重新評估外連結的第一行) 

三、table：查詢涉及的表或衍生表，有時不是真實的表名字,看到的是derivedx(x是個數字，第幾步執行的結果)

四、type：表示MySQL在表中找到所需行的方式，又稱“索引類型”。type字段比較重要, 它提供了判斷查詢是否高效的重要依據。 通過 type 字段，我們判斷此次查詢是全表掃描還是索引掃描等。要對type優化的前提是有索引，常用的類型有： ALL, index,  range, ref, eq_ref, const, system（從左到右，性能從差到好），其中system、const隻是理想情況，實際能達到ref>range。

1）、ALL：表示全表掃描, 這個類型的查詢是性能最差的查詢之一。 通常來說, 我們的查詢不應該出現 ALL 類型的查詢, 因為這樣的查詢在資料量大的情況下, 對資料庫的性能是巨大的災難。 如一個查詢是 ALL 類型查詢, 那麼一般來說可以對相應的字段添加索引來避免。

2）、index: 表示全索引掃描(full index scan), 和 ALL 類型類似，隻不過 ALL 類型是全表掃描， 而 index 類型則僅僅掃描所有的索引，而不掃描資料。index 類型通常出現在 所要查詢的資料直接在索引樹中就可以擷取到, 而不需要掃描資料. 當是這種情況時, Extra 字段 會顯示 Using index。

3）、range:表示使用索引範圍查詢, 通過索引字段範圍擷取表中部分資料記錄。這個類型通常出現在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中，其中in偶爾會失效，進而變成all。當 type 是 range 時, 那麼 explain 輸出的 ref 字段為 NULL, 并且 key_len 字段是此次查詢中使用到的索引的最長的那個。

4）、ref：非唯一性索引，對于每個索引鍵的查詢，傳回比對的行（0，多行）。

5）、eq_ref：唯一性索引，對于每個索引鍵的查詢，傳回唯一行資料（有且隻有一行資料，不能多，也不能為0）。 select * from ...where name =...常見于主鍵或唯一索引。

6）、const：僅僅能查到一條資料的SQL，用于主鍵或唯一索引（類型與索引有關）。 const 查詢速度非常快, 因為它僅僅讀取一次即可。

7）、system: 隻有一條資料的系統表，或衍生表隻有一條資料的主查詢。 這個類型是特殊的 const 類型。 

五、possible_keys：possible_keys 表示 MySQL 在查詢時, 能夠使用到的索引。 注意, 即使有些索引在 possible_keys中出現, 但是并不表示此索引會真正地被MySQL使用到。 MySQL 在查詢時具體使用了哪些索引, 由key字段決定。 

六、Key：顯示MySQL實際決定使用的鍵（索引）

如果沒有選擇索引，鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。 

七、key_len：表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表内檢索出的），用于判斷複合索引是否被完全使用。不損失精确性的情況下，長度越短越好。在utf8中，1個字元占3個位元組，gbk1個字元2個位元組，latin1個字元1個位元組。如果索引字段可以為NULL，在會使用1個位元組用于辨別。

key_len 的計算規則如下：

字元串

char(n): n 位元組長度

varchar(n): 如果是 utf8 編碼, 則是 3 n + 2位元組; 如果是 utf8mb4 編碼, 則是 4 n + 2 位元組。

數值類型

TINYINT: 1位元組

SMALLINT: 2位元組

MEDIUMINT: 3位元組

INT: 4位元組

BIGINT: 8位元組

時間類型

DATE: 3位元組

TIMESTAMP: 4位元組

DATETIME: 8位元組

字段屬性: NULL 屬性 占用一個位元組. 如果一個字段是 NOT NULL 的, 則沒有此屬性。

八、ref：表示上述表的連接配接比對條件，即哪些列或常量被用于查找索引列上的值 。

九、rows： 表示MySQL根據表統計資訊及索引選用情況，估算的找到所需的記錄所需要讀取的行數 ，原則上 rows 越少越好。

十、Extra：該列包含MySQL查詢的詳細資訊，有以下幾種情況：

1）、Using where:需要回原表中查詢。

2）、Using temporary：表示查詢有使用臨時表, 一般出現于排序, 分組group by和多表 join 的情況, 查詢效率不高, 建議優化。 排序盡量對第一個表的索引字段進行，可以避免mysql建立臨時表，這是非常耗資源的。查詢哪些列，盡量group by那些列。

3）、Using filesort：MySQL中無法利用索引完成的排序操作稱為“檔案排序”， 表示 MySQL 需額外的排序操作, 不能通過索引順序達到排序效果。 一般有 Using filesort, 都建議優化去掉, 因為這樣的查詢 CPU 資源消耗大。對于單索引，如果查找和排序是同一個索引，則不會出現，如果查找和排序不是同一個索引，則會出現。對于複合索引，不能跨列，符合最佳左字首。避免where和order by按照複合索引的順序使用，不能跨列或無序使用。

4）、Using join buffer：該值強調了在擷取連接配接條件時沒有使用索引，并且需要連接配接緩沖區來存儲中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要添加索引來改進能。

5）、Impossible where：這個值強調了where語句會導緻沒有符合條件的行。

6）、Select tables optimized away：這個值意味着僅通過使用索引，優化器可能僅從聚合函數結果中傳回一行

7）、Using index："覆寫索引掃描", 表示查詢在索引樹中就可查找所需資料, 不用掃描表資料檔案, 往往說明性能不錯。 會對possible_keys有影響，如果有where則索引出現在possible_keys和key中，如果沒有where則隻在key中顯示。

總結

1）、EXPLAIN不會告訴你關于觸發器、存儲過程的資訊或使用者自定義函數對查詢的影響情況

2）、EXPLAIN不考慮各種Cache

3）、EXPLAIN不能顯示MySQL在執行查詢時所作的優化工作

4）、部分統計資訊是估算的，并非精确值

5）、EXPALIN隻能解釋SELECT操作，其他操作要重寫為SELECT後檢視執行計劃。

參考資料

https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

https://segmentfault.com/a/1190000008131735