MySQL-explain
来自技术开发小组内部wiki
一、语法
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type] SELECT select_options
explain_type: {EXTENDED | PARTITIONS}
示例：
explain employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
explain select * from employees where emp_no=10001;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employees | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
explain extended select * from employees where emp_no=10001;show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select '10001' AS `emp_no`,'1953-09-02' AS `birth_date`,'Georgi' AS `first_name`,'Facello' AS `last_name`,'M' AS `gender`,'1986-06-26' AS `hire_date` from `employees`.`employees` where 1 |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
explain extended select * from employees where first_name='Georgi' and first_name like 'Geo%';show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `employees`.`employees`.`emp_no` AS `emp_no`,`employees`.`employees`.`birth_date` AS `birth_date`,`employees`.`employees`.`first_name` AS `first_name`,`employees`.`employees`.`last_name` AS `last_name`,`employees`.`employees`.`gender` AS `gender`,`employees`.`employees`.`hire_date` AS `hire_date` from `employees`.`employees` where (`employees`.`employees`.`first_name` = 'Georgi') |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
二、输出列
+----+-------------+----------+------+----------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+----------------+---------+---------+-------+------+-------+
1)id:
示例：
explain select 'Georgi','Facello',dept_name
from departments
where dept_no in
(select dept_no
from dept_emp
where emp_no in
(select emp_no from employees where first_name = 'Georgi' and last_name = 'Facello'));
+----+--------------------+-------------+-----------------+---------------+-----------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+-----------------+---------------+-----------+---------+------+-------+--------------------------+
| 1 | PRIMARY | departments | index | NULL | dept_name | 122 | NULL | 9 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | dept_emp | index_subquery | dept_no | dept_no | 12 | func | 41485 | Using index; Using where |
| 3 | DEPENDENT SUBQUERY | employees | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+-------------+-----------------+---------------+-----------+---------+------+-------+--------------------------+
explain select a.first_name, a.last_name, c.dept_name
from employees a, dept_emp b, departments c
where a.emp_no = b.emp_no
and b.dept_no = c.dept_no
and a.first_name = 'Georgi'
and a.last_name = 'Facello';
+----+-------------+-------+--------+------------------------+-----------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------+-----------+---------+---------------------+-------+-------------+
| 1 | SIMPLE | c | index | PRIMARY | dept_name | 122 | NULL | 9 | Using index |
| 1 | SIMPLE | b | ref | PRIMARY,emp_no,dept_no | dept_no | 12 | employees.c.dept_no | 41485 | Using index |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | employees.b.emp_no | 1 | Using where |
+----+-------------+-------+--------+------------------------+-----------+---------+---------------------+-------+-------------+
2)select_type:
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT Result of a UNION.
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DERIVED Derived table SELECT (subquery in FROM clause)
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
示例：
explain select * from employees where emp_no=10001 or emp_no=10002;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | employees | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
explain select * from employees a where emp_no=10001 union all select * from employees b where emp_no=10002;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | a | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | UNION | b | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
explain select * from employees b
where emp_no in
(select emp_no from dept_emp c where dept_no = 'd001'
union all
select emp_no from dept_emp d where dept_no = 'd002');
+----+--------------------+------------+------+------------------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+------+------------------------+---------+---------+------+--------+--------------------------+
| 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 299920 | Using where |
| 2 | DEPENDENT SUBQUERY | c | ref | PRIMARY,emp_no,dept_no | PRIMARY | 4 | func | 1 | Using where; Using index |
| 3 | DEPENDENT UNION | d | ref | PRIMARY,emp_no,dept_no | PRIMARY | 4 | func | 1 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+------------+------+------------------------+---------+---------+------+--------+--------------------------+
explain select * from dept_emp where dept_no = ( select dept_no from dept_emp where dept_no=@@sql_log_bin);
+----+----------------------+----------+-------+---------------+--------+---------+------+--------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+----------------------+----------+-------+---------------+--------+---------+------+--------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | UNCACHEABLE SUBQUERY | dept_emp | index | dept_no | emp_no | 4 | NULL | 331883 | Using where; Using index |
+----+----------------------+----------+-------+---------------+--------+---------+------+--------+-----------------------------------------------------+
explain select * from (select * from employees b where emp_no=10001) a;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | b | const | PRIMARY | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
3)table:
tablename
<derivedN>
<unionM,N>
示例：
explain select * from (select * from employees b where emp_no=10001) a;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | b | const | PRIMARY | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
explain select * from employees a where emp_no=10001 union all select * from employees b where emp_no=10002;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | a | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | UNION | b | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
4)type:
system The table has only one row (= system table).This is a special case of the const join type.
const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values.
eq_ref One row is read from this table for each combination of rows from the previous tables.
ref All rows with matching index values are read from this table for each combination of rows from the previous tables.
ref_or_null This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values.
fulltext The join is performed using a FULLTEXT index.
index_merge This join type indicates that the Index Merge optimization is used.
unique_subquery This type replaces ref for some IN subqueries of the following form:value IN (SELECT primary_key FROM single_table WHERE some_expr),unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.
index_subquery This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:value IN (SELECT key_column FROM single_table WHERE some_expr)
range Only rows that are in a given range are retrieved, using an index to select the rows.range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN() operators:
index The index join type is the same as ALL, except that the index tree is scanned.
ALL A full table scan is done for each combination of rows from the previous tables.
示例：
explain select * from (select * from employees b where emp_no=10001 limit 1) a;
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | b | const | PRIMARY | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+
explain select a.first_name, a.last_name, c.dept_name
from employees a, dept_emp b, departments c
where a.emp_no = b.emp_no
and b.dept_no = c.dept_no
and a.first_name = 'Georgi'
and a.last_name = 'Facello';
+----+-------------+-------+--------+------------------------+-----------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------+-----------+---------+---------------------+-------+-------------+
| 1 | SIMPLE | c | index | PRIMARY | dept_name | 122 | NULL | 9 | Using index |
| 1 | SIMPLE | b | ref | PRIMARY,emp_no,dept_no | dept_no | 12 | employees.c.dept_no | 41485 | Using index |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | employees.b.emp_no | 1 | Using where |
+----+-------------+-------+--------+------------------------+-----------+---------+---------------------+-------+-------------+
explain select dept_no, dept_name from departments1 where dept_name = 'Sales' or dept_name is null;
+----+-------------+--------------+-------------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------------+---------------+-----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | departments1 | ref_or_null | dept_name | dept_name | 123 | const | 4 | Using where; Using index |
+----+-------------+--------------+-------------+---------------+-----------+---------+-------+------+--------------------------+
create fulltext index idx_dept2_name on departments2(dept_name);
explain select dept_no, dept_name from departments2 where match(dept_name) against('Sal' in boolean mode);
+----+-------------+--------------+----------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+----------+----------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | departments2 | fulltext | idx_dept2_name | idx_dept2_name | 0 | | 1 | Using where |
+----+-------------+--------------+----------+----------------+----------------+---------+------+------+-------------+
explain select * from employees1 where first_name='Georgi' or last_name='Peac'
+----+-------------+------------+-------------+----------------------+----------------------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------------+----------------------+----------------------+---------+------+------+------------------------------------------------+
| 1 | SIMPLE | employees1 | index_merge | first_name,last_name | first_name,last_name | 44,50 | NULL | 435 | Using union(first_name,last_name); Using where |
+----+-------------+------------+-------------+----------------------+----------------------+---------+------+------+------------------------------------------------+
explain select * from dept_emp where emp_no in(select emp_no from employees where first_name='Georgi' or last_name='Peac');
+----+--------------------+-----------+-----------------+---------------+---------+---------+------+--------+-------------+
| 1 | PRIMARY | dept_emp | ALL | NULL | NULL | NULL | NULL | 331883 | Using where |
| 2 | DEPENDENT SUBQUERY | employees | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+-----------+-----------------+---------------+---------+---------+------+--------+-------------+
explain select * from employees where first_name in(select first_name from employees1 where first_name='Georgi' or last_name='Peac');
+----+--------------------+------------+----------------+----------------------+------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+----------------+----------------------+------------+---------+------+--------+-------------+
| 1 | PRIMARY | employees | ALL | NULL | NULL | NULL | NULL | 299920 | Using where |
| 2 | DEPENDENT SUBQUERY | employees1 | index_subquery | first_name,last_name | first_name | 44 | func | 1 | Using where |
+----+--------------------+------------+----------------+----------------------+------------+---------+------+--------+-------------+
explain select * from employees where emp_no<10002;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | employees | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
explain select emp_no from employees;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | employees | index | NULL | PRIMARY | 4 | NULL | 299920 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
explain select * from employees;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299920 | |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
5)possible_keys
The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.
6)key
The key column indicates the key (index) that MySQL actually decided to use.
7)key_len
The key_len column indicates the length of the key that MySQL decided to use.
8)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.
9)rows
The rows column indicates the number of rows MySQL believes it must examine to execute the query.
10)Extra
This column contains additional information about how MySQL resolves the query.
Child of 'table' pushed join@1
const row not found
Distinct
Full scan on NULL key
Impossible HAVING
Impossible WHERE
Impossible WHERE noticed after reading const tables
No matching min/max row
no matching row in const table
No tables used
Not exists
Range checked for each record (index map: N)
Scanned N databases
Select tables optimized away
Skip_open_table, Open_frm_only, Open_trigger_only, Open_full_table
unique row not found
Using filesort
Using index
Using index for group-by
Using join buffer
Using sort_union(...), Using union(...), Using intersect(...)
Using temporary
Using where
Using where with pushed condition
Using filesort、Using temporary
示例：
参考：http://dev.mysql.com/doc/refman/5.5/en/using-explain.html
以下补充：
select @@profiling;
set profiling=1;
执行 select 语句
show profiles;
show profile for query N;
示例：
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000113 |
| checking permissions | 0.000006 |
| checking permissions | 0.000002 |
| checking permissions | 0.000027 |
| Opening tables | 0.000027 |
| System lock | 0.000012 |
| init | 0.000030 |
| optimizing | 0.000020 |
| statistics | 0.000075 |
| preparing | 0.000025 |
| executing | 0.000004 |
| Sending data | 0.362112 |
| end | 0.000011 |
| query end | 0.000004 |
| closing tables | 0.000009 |
| freeing items | 0.000517 |
| logging slow query | 0.000004 |
| cleaning up | 0.000004 |
+----------------------+----------+
