MySQL EXPLAIN結果集分析 - 附帶大量案例
轉載自公眾號：運維咖啡吧
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： 簡單的select查詢，沒有union或者子查詢
mysql> explain select * from test where id =
1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type
| table | partitions
| type | possible_keys
| key | key_len
| ref | rows
| filtered | Extra
|
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|
1
| SIMPLE | test
| NULL | const
| PRIMARY | PRIMARY
| 4 | const
| 1 |
100.00
| NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
primary： 最外層的select查詢
mysql> explain select * from (select * from test where id =
1000) a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|
1
| PRIMARY | <derived2>
| system | NULL
| NULL | NULL
| NULL |
1
| NULL |
| 2 | DERIVED
| test | const
| PRIMARY | PRIMARY
| 8 | const
| 1 | NULL
|
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
union： union中的第二個或隨後的select查詢，不依賴於外部查詢的結果集
mysql> explain select * from test where id =
1000 union all select * from test2 ;
+----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+
|
1
| PRIMARY | test
| const | PRIMARY
| PRIMARY |
8
| const |
1
| NULL |
| 2 | UNION
| test2 | ALL
| NULL | NULL
| NULL | NULL
| 67993 | NULL
|
| NULL
| UNION RESULT | <union1,
2>
| ALL | NULL
| NULL | NULL
| NULL | NULL
| Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+-------+-----------------+
dependent union： union中的第二個或隨後的select查詢，依賴於外部查詢的結果集
mysql> explain select * from test where id
in (select id from test where id =
1000 union all select id from test2) ;
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
|
1
| PRIMARY | test
| ALL | NULL
| NULL | NULL
| NULL |
68505
| Using where |
| 2 | DEPENDENT SUBQUERY
| test | const
| PRIMARY | PRIMARY
| 8 | const
| 1 | Using index
|
|
3
| DEPENDENT UNION | test2
| eq_ref | PRIMARY
| PRIMARY |
8
| func |
1
| Using index |
| NULL | UNION RESULT
| <union2,3> | ALL
| NULL | NULL
| NULL | NULL
| NULL | Using temporary
|
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
subquery： 子查詢中的第一個select查詢，不依賴與外部查詢的結果集
mysql> explain select * from test where id = (select id from test where id =
1000);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|
1
| PRIMARY | test
| const | PRIMARY
| PRIMARY |
8
| const |
1
| NULL |
| 2 | SUBQUERY
| test | const
| PRIMARY | PRIMARY
| 8 | const
| 1 | Using index
|
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
dependent subquery： 子查詢中的第一個select查詢，依賴於外部查詢的結果集
mysql> explain select * from test where id
in (select id from test where id =
1000 union all select id from test2) ;
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
|
1
| PRIMARY | test
| ALL | NULL
| NULL | NULL
| NULL |
68505
| Using where |
| 2 | DEPENDENT SUBQUERY
| test | const
| PRIMARY | PRIMARY
| 8 | const
| 1 | Using index
|
|
3
| DEPENDENT UNION | test2
| eq_ref | PRIMARY
| PRIMARY |
8
| func |
1
| Using index |
| NULL | UNION RESULT
| <union2,3> | ALL
| NULL | NULL
| NULL | NULL
| NULL | Using temporary
|
+----+--------------------+------------+--------+---------------+---------+---------+-------+-------+-----------------+
derived： 用於from子句中有子查詢的情況，mysql會遞迴執行這些子查詢，此結果集放在臨時表中
mysql> explain select * from (select * from test2 where id =
1000)a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|
1
| PRIMARY | <derived2>
| system | NULL
| NULL | NULL
| NULL |
1
| NULL |
| 2 | DERIVED
| test2 | const
| PRIMARY | PRIMARY
| 8 | const
| 1 | NULL
|
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
3、table
table用來表示輸出行所引用的表名
4、type（重要）
type表示訪問型別，下面依次解釋各種型別，型別順序從 最好到最差排列
system： 表僅有一行，是const型別的一個特例
mysql> explain select * from (select * from test2 where id =
1000)a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|
1
| PRIMARY | <derived2>
| system | NULL
| NULL | NULL
| NULL |
1
| NULL |
| 2 | DERIVED
| test2 | const
| PRIMARY | PRIMARY
| 8 | const
| 1 | NULL
|
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
因為子查詢只有一行資料，模擬了單表只有一行資料，此時type為system
const： 確定只有一行匹配的時候，mysql最佳化器會在查詢前讀取它並且只讀取一次，速度非常快
mysql> explain select * from test where id =
1 ;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|
1
| SIMPLE | test
| const | PRIMARY
| PRIMARY |
8
| const |
1
| NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row
in set (
0.
00 sec)
eq_ref： 對於每個來自於前面的表的行組合，從該表中讀取一行，常用在一個索引是unique key或者primary key
mysql> explain select * from test,test2 where test.com_key=test2.com_key;
+----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
|
1
| SIMPLE | test2
| ALL | IDX(com_key)
| NULL | NULL
| NULL |
67993
| NULL |
| 1 | SIMPLE
| test | eq_ref
| IDX(com_key) | IDX(com_key)
| 194 | test.test2.com_key
| 1 | NULL
|
+----+-------------+-------+--------+---------------+--------------+---------+--------------------+-------+-------+
ref： 對於來自前面的表的行組合，所有有匹配索引值的行都從這張表中讀取,如果聯接只使用鍵的最左邊的字首，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基於關鍵字選擇單個行的話），則使用ref
ref可以用於使用=或<=>運算子的帶索引的列
mysql> explain select * from test ,test2 where test.bnet_id=test2.aid;
+----+-------------+-------+------+---------------+---------+---------+-------------------+-------+-----------------------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+------+---------------+---------+---------+-------------------+-------+-----------------------+
|
1
| SIMPLE | test
| ALL | NULL
| NULL | NULL
| NULL |
68505
| Using where |
| 1 | SIMPLE
| test2 | ref
| idx_aid | idx_aid
| 5 | test.test.bnet_id
| 34266 | Using index condition
|
+----+-------------+-------+------+---------------+---------+---------+-------------------+-------+-----------------------+
test表
bnet_id不是索引，test2表
aid為索引列
ref_or_null： 類似ref，但是新增了可以專門搜尋null值的行
mysql> explain select * from test where bnet_id=
1
or bnet_id is null;
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+
|
1
| SIMPLE | test
| ref_or_null | idx_bnet
| idx_bnet |
9
| const |
2
| Using index condition |
+----+-------------+-------+-------------+---------------+----------+---------+-------+------+-----------------------+
前提為
bnet_id列為索引，且
bnet_id列有null值
index_merge： 該訪問型別使用了索引合併最佳化方法，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素
mysql> explain select * from test where id =
1
or bnet_id =
1;
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
|
1
| SIMPLE | test
| index_merge | PRIMARY,idx_bnet
| PRIMARY,idx_bnet |
8,
9
| NULL |
2
| Using union(PRIMARY,idx_bnet); Using where |
+----+-------------+-------+-------------+------------------+------------------+---------+------+------+--------------------------------------------+
前提條件為
id列和
bnet_id列都有單列索引。如果出現index_merge，並且這類SQL後期使用較頻繁，可以考慮把單列索引換為組合索引，這樣效率更高
range： 只檢索給定範圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。key_len包含所使用索引的最長關鍵元素。在該型別中ref列為NULL
當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN運算子，用常量比較關鍵字列時，可以使用range
mysql> explain select * from test where bnet_id >
1000
and bnet_id <
10000;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|
1
| SIMPLE | test
| range | idx_bnet
| idx_bnet |
9
| NULL |
1
| Using index condition |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
前提條件為
bnet_id列有索引
index： 在進行統計時非常常見，此聯接型別實際上會掃描索引樹
mysql> explain select count(*) 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 |
+----+-------------+-------+-------+---------------+----------+---------+------+-------+-------------+
all： 對於每個來自於先前的表的行組合，進行完整的表掃描，通常可以增加更多的索引而不要使用ALL，使得行能基於前面的表中的常數值或列值被檢索出
mysql> explain select * from test where create_time =
'0000-00-00 00:00:00';
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type
| table | type
| possible_keys | key
| key_len | ref
| rows | Extra
|
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|
1
| SIMPLE | test
| ALL | NULL
| NULL | NULL
| NULL |
68505
| Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
5、possible_keys
possible_keys是指在這個SQL中，mysql可以使用這個索引去輔助查詢記錄，當查詢涉及到的欄位，都會被列出，但不一定被查詢使用.若為空則表示沒有可以使用的索引，此時可以透過檢查where語句看是否可以引用某些列或者新建索引來提高效能。
6、key（重要）
key列顯示的是當前表實際使用的索引，如果沒有選擇索引，則此列為null，要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
7、key_len
key_len列顯示MySQL決定使用的鍵長度。如果KEY鍵是NULL，則長度為NULL。 在不損失精確性的情況下，長度越短越好
key len的長度還和字符集有關,latin1一個字元佔用1個位元組,gbk一個字元佔用2個位元組,utf8一個字元佔用3個位元組。key_len的計演算法方法：
| 列型別 | 長度 | 備註 |
|---|---|---|
| id int | 4+1 | int為4bytes,允許為NULL,加1byte |
| id bigint not null | 8 | bigint為8bytes |
| user char(30) utf8 | 30*3+1 | utf8每個字元為3bytes,允許為NULL,加1byte |
| user varchar(30) not null utf8 | 30*3+2 | utf8每個字元為3bytes,變長資料型別,加2bytes |
| user varchar(30) utf8 | 30*3+2+1 | utf8每個字元為3bytes,允許為NULL,加1byte,變長資料型別,加2bytes |
| detail text(10) utf8 | 30*3+2+1 | TEXT擷取部分,被視為動態列型別。 |
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中出現多個上面結果，則表示順序使用上面的方法進行解析查詢
想了解更多關於資料庫、雲技術的內容嗎？
快來關注“資料和雲"、"雲和恩墨，"公眾號及"雲和恩墨"官方網站，我們期待大家一同學習與進步！
資料和雲小程式”DBASK“線上問答，隨時解惑，歡迎瞭解和關注！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31556440/viewspace-2656093/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- EXPLAIN結果含義(轉)AI
- MySQL的Explain總結MySqlAI
- Mysql效能最佳化(三)--explain返回的結果說明MySqlAI
- MySQL高階(3)-效能分析ExplainMySqlAI
- MySQL 查詢效能分析之 ExplainMySqlAI
- mysql效能分析之explain的用法MySqlAI
- 【mysql】explain命令分析慢查詢MySqlAI
- MySQL explain結果Extra中"Using Index"與"Using where; Using index"區別MySqlAIIndex
- MySQL（十四）分析查詢語句Explain 七千字總結MySqlAI
- 使用navicat匯出查詢大量資料結果集並匯入到其他資料庫（mysql）資料庫MySql
- MySQL explainMySqlAI
- [Mysql]ExplainMySqlAI
- PB帶引數帶結果集的動態SQL查詢SQL
- (4) MySQL中EXPLAIN執行計劃分析MySqlAI
- 用 Explain 命令分析 MySQL 的 SQL 執行AIMySql
- MySQL 索引 +explainMySql索引AI
- iOS FMDB有返回結果集和無返回結果集iOS
- Mysql使用like全模糊和半模糊， Explain分析後的結果如何選擇？MySqlAI
- MySQL——通過EXPLAIN分析SQL的執行計劃MySqlAI
- MySQL經典案例分析MySql
- Mysql之案例分析（一）MySql
- 故障分析 | MySQL死鎖案例分析MySql
- 簡單的mysql儲存過程，輸出結果集MySql儲存過程
- MySQL學習之explainMySqlAI
- MySQL explain命令詳解MySqlAI
- MySQL 中的 EXPLAIN 命令MySqlAI
- mysql返回一個結果集的儲存過程小例子MySql儲存過程
- 結果集 (ResultSet)全面解析
- 批次對比結果集
- 壓測結果分析
- MySQL案例08:MySQL Scheduler Events帶來的風險MySql
- MySQL bit型別增加索引後查詢結果不正確案例淺析MySql型別索引
- mysql優化之explain 指令MySql優化AI
- mysql explain 執行計劃MySqlAI
- mysql執行計劃explainMySqlAI
- [MySql]explain用法及實踐MySqlAI
- MySQL explain 和 profiling 詳解MySqlAI
- MySQL批量更新死鎖案例分析MySql