MySQL EXPLAIN 命令詳解
MySQL的EXPLAIN命令用於SQL語句的查詢執行計劃(QEP)。這條命令的輸出結果能夠讓我們瞭解MySQL 最佳化器是如何執行
1 語法
UPDATE table1
SET col1 = X, col2 = Y
WHERE id1 = 9
AND dt >= '2010-01-01' ;
這個UPDATE語句可以被重寫成為下面這樣的SELECT語句：
SELECT col1, col2
FROM table1
WHERE id1 = 9
AND dt >= '2010-01-01' ;
在5.6.10版本里面,是可以直接對dml語句進行explain分析操作的.
MySQL 最佳化器是基於開銷來工作的，它並不提供任何的QEP的位置。這意味著QEP 是在每條SQL 語句執行的時候動態地計
2 各列詳解
mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G;
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table : inventory
type: ALL
possible_keys: NULL
key : NULL
key_len: NULL
ref: NULL
rows : 787338
Extra: Using where
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table : inventory
type: ref
possible_keys: item_id
key : item_id
key_len: 4
ref: const
rows : 1
Extra:
在這個QEP 中，我們看到使用了一個索引，且估計只有一行資料將被獲取。
QEP 中每個行的所有列表如下所示： ? id
這些列展示了SELECT 語句對每一個表的QEP。一個表可能和一個物理模式表或者在SQL 執行時生成的內部臨時表(例如從子查詢或者合併操作會產生內部臨時表)相關聯。http://dev.mysql.com/doc/refman/5.5/en/explain-output.html 。
2.1 key
命令是最簡單的檢視錶和索引列細節的方式。和key 列相關的列還包括possible_keys、rows 以及key_len。
2.2 ROWS
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table : p
type: const
possible_keys: PRIMARY
key : PRIMARY
key_len: 4
ref: const
rows : 1
Extra:
********************* 2. row ***********************
id: 1
select_type: SIMPLE
table : c
type: ref
possible_keys: parent_id
key : parent_id
key_len: 4
ref: const
rows : 10
Extra:
可以使用SHOW STATUS 命令來檢視實際的行操作。這個命令可以提供最佳的確認物理行操作的方式。請看下面的示例：
mysql> SHOW SESSION STATUS LIKE 'Handler_read%' ;
+
| Variable_name | Value |
+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 11 |
+
7 rows in set (0.00 sec)
在下一個QEP 中，透過id=1 找到的外層巢狀迴圈估計有160行。第二個迴圈估計有1 行。
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table : p
type: ALL
possible_keys: NULL
key : NULL
key_len: NULL
ref: NULL
rows : 160
Extra:
********************* 2. row ***********************
id: 1
select type: SIMPLE
table : c
type: ref
possible_keys: PRIMARY ,parent_id
key : parent_id
key_len: 4
ref: test.p.parent_id
rows : 1
Extra: Using where
透過SHOW STATUS 命令可以檢視實際的行操作，該命令表明物理讀運算元量大幅增加。請看下面的示例：
mysql> SHOW SESSION STATUS LIKE 'Handler_read%' ;
+
| Variable_name | Value |
+
| Handler_read_first | 1 |
| Handler_read_key | 164 |
| Handler_read_last | 0 |
| Handler_read_next | 107 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 161 |
+
相關的QEP 列還包括key 列。
2.3 possible_keys possible_keys 列指出最佳化器為查詢選定的索引。 一個會列出大量可能的索引(例如多於3 個)的QEP 意味著備選索引數量太多了，同時也可能提示存在一個無效的單列索引。 可以用第2 章詳細介紹過的SHOW INDEXES 命令來檢查索引是否有效且是否具有合適的基數。 為查詢確定QEP 的速度也會影響到查詢的效能。如果發現有大量的可能的索引，則意味著這些索引沒有被使用到。 相關的QEP 列還包括key 列。 2.4 key_len key_len 列定義了用於SQL 語句的連線條件的鍵的長度。此列值對於確認索引的有效性以及多列索引中用到的列的數目很重要。 此列的一些示例值如下所示： 此列的一些示例值如下所示： key_len: 4 // INT NOT NULL key_len: 5 // INT NULL key_len: 30 // CHAR(30) NOT NULL key_len: 32 // VARCHAR(30) NOT NULL key_len: 92 // VARCHAR(30) NULL CHARSET=utf8 從這些示例中可以看出，是否可以為空、可變長度的列以及key_len 列的值只和用在連線和WHERE 條件中的索引的列 有關。索引中的其他列會在ORDER BY 或者GROUP BY 語句中被用到。下面這個來自於著名的開源部落格軟體WordPress 的表展示了 如何以最佳方式使用帶有定義好的表索引的SQL 語句：
CREATE TABLE `wp_posts` (
`ID` bigint (20) unsigned NOT NULL AUTO_INCREMENT,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
`post_status` varchar (20) NOT NULL DEFAULT 'publish' ,
`post_type` varchar (20) NOT NULL DEFAULT 'post' ,
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
) DEFAULT CHARSET=utf8
CREATE TABLE `wp_posts` (
`ID` bigint (20) unsigned NOT NULL AUTO_INCREMENT,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
`post_status` varchar (20) NOT NULL DEFAULT 'publish' ,
`post_type` varchar (20) NOT NULL DEFAULT 'post' ,
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
) DEFAULT CHARSET=utf8
這個表的索引包括post_type、post_status、post_date 以及ID列。下面是一個演示索引列用法的SQL 查詢： EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01'; 這個查詢的QEP 返回的key_len 是62。這說明只有post_type列上的索引用到了(因為(20×3)+2=62)。儘管查詢在WHERE 語句 中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引沒有被使用的原因是MySQL 只能使用定義索引的 最左邊部分。為了更好地利用這個索引，可以修改這個查詢來調整索引的列。請看下面的示例：
mysql> EXPLAIN SELECT ID, post_title
-> FROM wp_posts
-> WHERE post_type= 'post'
-> AND post_status= 'publish'
-> AND post_date > '2010-06-01' ;
在SELECT查詢的新增一個post_status 列的限制條件後，QEP顯示key_len 的值為132，這意味著post_type、post_status、post_date 三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，這個索引的主碼列ID 的定義是使用MyISAM 儲存索 引的遺留痕跡。當使用InnoDB 儲存引擎時，在非主碼索引中包含主碼列是多餘的，這可以從key_len 的用法看出來。 相關的QEP 列還包括帶有Using index 值的Extra 列。 2.5 table table 列是EXPLAIN 命令輸出結果中的一個單獨行的唯一識別符號。這個值可能是表名、表的別名或者一個為查詢產生臨時表 的識別符號，如派生表、子查詢或集合。下面是QEP 中table 列的一些示例： table: item table: table: 表中N 和M 的值參考了另一個符合id 列值的table 行。相關的QEP 列還有select_type
2.6 select_type
1. SIMPLE
2. PRIMARY 3. DERIVED
4. DEPENDENT SUBQUERY 5. UNION ，
2.7 partitions
2.8 Extra http://dev.mysql.com/doc/refman/5.5/en/explain-output.html 。
1. Using where
2. Using temporary
3. Using filesort
4. Using index
5. Using join buffer
6. Impossible where
7. Select tables optimized away
8. Distinct
9. Index merges
2.9 id
2.10 ref
2.11 filtered
2.12 type http://dev.mysql.com/doc/refman/5.5/en/explain-output.html 。
3 解釋EXPLAIN 輸出結果
mysql> EXPLAIN SELECT p.*
-> FROM parent p
-> WHERE p.id NOT IN ( SELECT c.parent_id FROM child
c)\G
********************* 1. row ***********************
id: 1
select type: PRIMARY
table : p
type: ALL
possible_keys: NULL
key : NULL
key_len: NULL
ref: NULL
rows : 160
Extra: Using where
********************* 2. row ***********************
id: 2
select_type: DEPENDENT SUBQUERY
table : c
type: index_subquery
possible_keys: parent_id
key : parent_id
key_len: 4
ref: func
rows : 1
Extra: Using index
2 rows in set (0.00 sec)
EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULL \G
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table : p
type: ALL
possible_keys: NULL
key : NULL
key_len: NULL
ref: NULL
rows : 160
Extra:
********************* 2. row ***********************
id: 1
select_type: SIMPLE
table : c
type: ref
possible_keys: parent_id
key : parent_id
key_len: 4
ref: test.p.id
rows : 1
Extra: Using where ; Using index ; Not exists
2 rows in set (0.00 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23490154/viewspace-1062493/，如需轉載，請註明出處，否則將追究法律責任。
