【渣渣程式設計師我不是DBA】MySQL的Explain記錄一下

少年吖少年發表於2019-01-23
程式設計師MySqlAI

寫一下Explain記錄一下

簡單粗暴的記錄一下，日常使用Explain是為了檢視目標SQL是不是用到了索引，也就是索引的使用情況，但是，沒寫這篇文章之前我使用Explain全靠猜，寫這個的目的也是自己整理學習一下，ok，步入正題

使用場景一般就是這種：

在查詢語句之前加上explain，來分析SQL語句

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
row in set (0.03 sec)

分析出來的結果有10列分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra，挨個解釋一遍，就不墨跡了

  • ID

mysql執行計劃中的ID，ID如果相同，可以認為是一組，從上往下順序執行，在每組ID中，其中ID越大，優先順序越高，越早執行

  • select_type

(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

查詢的哪一張表的資訊

  • type

查詢到特定行，用到的型別，分為以下幾種：
常用的型別有： ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，效能從差到好）

具體的解釋：
ALL：Full Table Scan， MySQL進行了全表掃描以找到匹配的行

index: Full Index Scan，index與ALL區別為index型別只遍歷索引樹

range:只檢索給定範圍的行，使用一個索引來選擇行

ref: 表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值

eq_ref: 類似ref，區別就在使用的索引是唯一索引，對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連線中使用primary key或者 unique key作為關聯條件

const、system: 當MySQL對查詢某部分進行優化，並轉換為一個常量時，使用這些型別訪問。如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量,system是const型別的特例，當查詢的表只有一行的情況下，使用system

NULL: MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列裡選取最小值可以通過單獨索引查詢完成。

  • possible_keys

指出MySQL能使用哪個索引在表中找到記錄，查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢使用

該列完全獨立於EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL，則沒有相關的索引。在這種情況下，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢效能。如果是這樣，創造一個適當的索引並且再次用EXPLAIN檢查查詢

  • key

key列顯示MySQL實際決定使用的鍵（索引）！！此項強烈建議重點關注

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

  • key_len

表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度（key_len顯示的值為索引欄位的最大可能長度，並非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的）

不損失精確性的情況下，長度越短越好

  • ref

表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值

  • rows

表示MySQL根據表統計資訊及索引選用情況，估算的找到所需的記錄所需要讀取的行數

  • Extra

該列包含MySQL解決查詢的詳細資訊,有以下幾種情況：

Using where:列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表返回的，這發生在對錶的全部的請求列都是同一個索引的部分的時候，表示mysql伺服器將在儲存引擎檢索行後再進行過濾

Using temporary：表示MySQL需要使用臨時表來儲存結果集，常見於排序和分組查詢

Using filesort：MySQL中無法利用索引完成的排序操作稱為“檔案排序”

Using join buffer：改值強調了在獲取連線條件時沒有使用索引，並且需要連線緩衝區來儲存中間結果。如果出現了這個值，那應該注意，根據查詢的具體情況可能需要新增索引來改進能。

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

Select tables optimized away：這個值意味著僅通過使用索引，優化器可能僅從聚合函式結果中返回一行

總結：
• EXPLAIN不會告訴你關於觸發器、儲存過程的資訊或使用者自定義函式對查詢的影響情況
• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執行查詢時所作的優化工作
• 部分統計資訊是估算的，並非精確值
• EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT後檢視執行計劃。

相關文章