分析MySQL EXPLAIN結果集

151次閱讀

共計 6025 個字符，預計需要花費 16 分鐘才能閱讀完成。

這篇文章主要介紹“分析 MySQL EXPLAIN 結果集”，在日常操作中，相信很多人在分析 MySQL EXPLAIN 結果集問題上存在疑惑，丸趣 TV 小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”分析 MySQL EXPLAIN 結果集”的疑惑有所幫助！接下來，請跟著丸趣 TV 小編一起來學習吧！

EXPLAIN：查看 SQL 語句的執行計劃

EXPLAIN 命令可以幫助我們深入了解 MySQL 基于開銷的優化器，還可以獲得很多可能被優化器考慮到的訪問策略的細節，以及當運行 SQL 語句時哪種策略預計會被優化器采用，在優化慢查詢時非常有用

執行 explain 之后結果集包含如下信息

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+

下面將對每一個值進行解釋

1、id

id 用來標識整個查詢中 SELELCT 語句的順序，在嵌套查詢中 id 越大的語句越先執行，該值可能為 NULL

id 如果相同，從上往下依次執行。id 不同，id 值越大，執行優先級越高，如果行引用其他行的并集結果，則該值可以為 NULL

2、select_type

select_type 表示查詢使用的類型，有下面幾種：

simple：

列類型長度備注 id int4+1int 為 4bytes, 允許為 NULL, 加 1byteid bigint not null8bigint 為 8bytesuser char(30) utf830*3+1utf8 每個字符為 3bytes, 允許為 NULL, 加 1byteuser varchar(30) not null utf830*3+2utf8 每個字符為 3bytes, 變長數據類型, 加 2bytesuser varchar(30) utf830*3+2+1utf8 每個字符為 3bytes, 允許為 NULL, 加 1byte, 變長數據類型, 加 2bytesdetail text(10) utf830*3+2+1TEXT 截取部分, 被視為動態列類型。

key_len 只指示了 where 中用于條件過濾時被選中的索引列，是不包含 order by 或 group by 這一部分被選中的索引列

8、ref

ref 列用來顯示使用哪個列或常數與 key 一起從表中選擇相應的行。它顯示的列的名字（或 const），此列多數時候為 null

9、rows

rows 列顯示的是 mysql 解析器認為執行此 SQL 時必須掃描的行數。此數值為一個預估值，不是具體值，通常比實際值小

10、filtered

此參數為 mysql 5.7 新加參數，指的是返回結果的行數所占需要讀到的行（rows 的值）的比例
對于使用 join 時，前一個表的結果集大小直接影響了循環的行數

11、extra（重要）

extra 表示不在其他列并且也很重要的額外信息

using index：該值表示這個 SQL 語句使用了覆蓋索引（覆蓋索引是指可以直接在索引列中得到想要的結果，而不用去回表），此時效率最高

mysql  explain select id from test;
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
| 1 | SIMPLE | test | index | NULL | idx_bnet | 9 | NULL | 68505 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+

這個例子中 id 字段為主鍵，但是 key 那里顯示走的并不是主鍵索引，這個是因為 mysql 的所有二級索引中都會包含所有的主鍵信息，而 mysql 沒有單獨的存儲主鍵索引，所以掃描二級索引的開銷比全表掃描更快

using where：表示存儲引擎搜到記錄后進行了后過濾(POST-FILTER)，如果查詢未能使用索引，using where 的作用只是提醒我們 mysql 要用 where 條件過濾結果集

mysql  explain select * from test where id   1;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | test | range | PRIMARY | PRIMARY | 8 | NULL | 34252 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+-------------+

using temporary 表示 mysql 需要使用臨時表來存儲結果集，常見于排序和分組查詢

mysql  explain select * from test where id in (1,2) group by bnet_id;
+----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | test | range | PRIMARY,IDX(event_key-bnet_Id),idx_bnet | PRIMARY | 8 | NULL | 2 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+-----------------------------------------+---------+---------+------+------+----------------------------------------------+

using filesort：是指 mysql 無法利用索引直接完成排序（排序的字段不是索引字段），此時會用到緩沖空間來進行排序

mysql  explain select * from test order by bnet_id;
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 68505 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------+

using join buffer：強調在獲取連接條件時沒有用到索引，并且需要連接緩沖區來存儲中間結果。（性能可以通過添加索引或者修改連接字段改進）

mysql  explain select * from test left join test2 on test.create_time = test2.create_time;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 959692 | 100.00 | NULL |
| 1 | SIMPLE | test2 | NULL | ALL | NULL | NULL | NULL | NULL | 958353 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Block Nested Loop 是指 Block Nested-Loop Join 算法：將外層循環的行 / 結果集存入 join buffer, 內層循環的每一行與整個 buffer 中的記錄做比較，從而減少內層循環的次數.

impossible where：表示 where 條件導致沒有返回的行

mysql  explain select * from test where id is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+

using index condition：是 mysql 5.6 之后新加的特性，結合 mysql 的 ICP（Index Condition Pushdown）特性使用。主要是優化了可以在索引（僅限二級索引）上進行 like 查找

如果 extra 中出現多個上面結果，則表示順序使用上面的方法進行解析查詢

到此，關于“分析 MySQL EXPLAIN 結果集”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！

正文完