MySQL 優化---Explain關鍵字詳解（二）

MySQL執行計劃Explain關鍵字詳解

環境說明

Centos版本: CentOS Linux release 7.7.1908 (Core)

Linux連接配接工具：SecureCRT

MySQL Version: 5.7.28

MySQL連接配接工具：Navicate

參考位址：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

Explain是幹什麼的

EXPLAIN語句提供有關MySQL如何執行語句的資訊。 EXPLAIN可以作用于 SELECT， DELETE， INSERT， REPLACE，和 UPDATE

語句。EXPLAIN為SELECT語句中使用的每個表傳回一行資訊。它按照MySQL在處理語句時讀取它們的順序列出了輸出中的表。MySQL

用嵌套循環連接配接方法解析所有連接配接。這意味着MySQL從第一個表中讀取一行，然後在第二個表，第三個表中找到比對的行，依此類推。

處理完所有表後，MySQL将通過表清單輸出標明的列和回溯，直到找到一個表，其中存在更多比對的行。從該表中讀取下一行，然後繼續

下一個表。

舉例說明個字段的含義

id字段

SELECT的辨別符。這是SELECT查詢中的序号，值越高執行的優先級越高，值相同，依次執行

select_type字段

查詢類型，用于差別普通查詢，複合查詢、子查詢等複雜查詢

SIMPLE：簡單SELECT查詢（不使用 UNION或子查詢）

PRIMARY：如果有複雜的子查詢，最外層标記為primary；

UNION：若第二個select出現在union之後，标記為union，若union包含在from字句的子查詢中，外層的select将被标記為derived

UNION RESULT：從union表擷取結果的select

SUBQUERY：First SELECT in subquery

Derived：衍生查詢

table字段

查詢的表

table字段

查詢的表

partitions字段

待定

type字段

通路類型，是一個重要的名額，常見的名額排序：system > const > eq_ref > ref >range > all

system：表隻有一條資料

const：通過索引隻查一次就能找到

eq_ref：唯一性索引掃描，表中隻有一條記錄比對

ref：非唯一性索引掃描，表中有多條記錄比對

range：檢索給定範圍

all：全表掃描

possible_keys字段

SELECT可能用到的索引

key字段

實際用到的索引

key_len字段

官方解釋：The key_len column indicates the length of the key that MySQL decided to use. The value of key_len

enables you to determine how many parts of a multiple-part key MySQL actually uses. If the key column says

NULL, the len_len column also says NULL.Due to the key storage format, the key length is one greater for a column

that can be NULL than for a NOT NULL column.

了解：就是索引所占列的位元組數總和，根據這個值可以判斷索引的使用情況,特别是在組合索引的時候,判斷該索引有多少部分被使用到

非常重要。

并且：不損失精确性的情況下，長度越短越好

ref字段

官方解釋：The ref column shows which columns or constants are compared to the index named in the key column

to select rows from the table.

了解：對前面的tpye的一個詳細顯示，很有可能是一個常量，比如用到的索引是什麼type，用到主鍵具體是那個資料庫哪張表的主鍵

rows字段

官方解釋：The ref column shows which columns or constants are compared to the index named in the key column

to select rows from the table.For InnoDB tables, this number is an estimate, and may not always be exact.

了解：MySQL認為執行該查詢需要掃描的行數，但是如果是才用的InnoDB引擎，這個資料并不總是準确的

filtered字段

官方解釋：The filtered column indicates an estimated percentage of table rows that will be filtered by the table

condition. The maximum value is 100, which means no filtering of rows occurred. Values decreasing from 100

indicate increasing amounts of filtering. rows shows the estimated number of rows examined and rows × filtered

shows the number of rows that will be joined with the following table. For example, if rows is 1000 and filtered

is 50.00 (50%), the number of rows to be joined with the following table is 1000 × 50% = 500.

解釋：~~~~

Extra字段

性能按Extra排序

Using index：用了覆寫索引

Using index condition：用了條件索引（索引下推）

Using where：從索引查出來資料後繼續用where條件過濾

Using join buffer (Block Nested Loop)：join的時候利用了join bu?er（優化政策：去除外連接配接、增大join buffer大小）

Using filesort：用了檔案排序，排序的時候沒有用到索引

Using temporary：用了臨時表（優化政策：增加條件以減少結果集、增加索引，思路就是要麼減少待排序的數量，要麼就提前排好序）

Start temporary, End temporary：子查詢的時候，可以優化成半連接配接，但是使用的是通過臨時表來去重

FirstMatch(tbl_name)：子查詢的時候，可以優化成半連接配接，但是使用的是直接進行資料比較來去

• 有以下幾種常見情況：

Using index 代表索引覆寫，就是查詢的列正好在索引中，不用回實體行查詢資料

Using where:列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表傳回的，這發生在對表的全部的請求列都是同一個索引的

部分的時候，表示mysql伺服器将在存儲引擎檢索行後再進行過濾

Using temporary：在許多查詢的執行過程中，MySQL可能會借助臨時表來完成一些功能，比如去重、排序之類的，比如我們在執

行許多包含DISTINCT、GROUP BY、UNION等子句的查詢過程中，如果不能有效利用索引來完成查詢，MySQL很有可能尋求通過建立

内部的臨時表來執行查詢。如果查詢中使用到了内部的臨時表，在執行計劃的Extra列将會顯示Using temporary提示。

Using filesort：很多情況下排序操作無法使用到索引，隻能在記憶體中（記錄較少的時候）或者磁盤中（記錄較多的時候）進行

排序，這種在記憶體中或者磁盤上進行排序的方式統稱為檔案排序（英文名：?lesort）。如果某個查詢需要使用檔案排序的方式執行查

詢，就會在執行計劃的Extra列中顯示Using ?lesort提示。

• 可能會有其他情況值：

no table used：當查詢語句的沒有FROM子句時将會提示該額外資訊。

Impossible where：查詢語句的WHERE子句永遠為FALSE時将會提示該額外資訊。

No Matching min/Max row當查詢清單處有MIN或者MAX聚集函數，但是并沒有符合WHERE子句中的搜尋條件的記錄時，将會提示該額外資訊。

Using index condition：有些搜尋條件中雖然出現了索引列，但卻不能使用到索引（在MySQL 5.6版本後加入的新特性）