EXPLAIN 语句主要是用于解析SQL执行计划,通过分析执行计划采取适当的优化方式提高SQL运行的效率。
EXPLAIN 语句输出通常包括id列,select_type,table,type,possible_keys,key等等列信息
MySQL 5.6.3后支持SELECT, DELETE, INSERT,REPLACE, and UPDATE.
EXPLAIN EXTENDED支持一些额外的执行计划相关的信息
EXPLAIN PARTITIONS支持基于分区表查询执行计划的相关信息
二、EXPLAIN输出列描述
-- 下面通过示例来展示EXPLAIN输出列
(root@localhost) [sakila]> explain select sum(amount) from customer a,
-> payment b where 1=1 and a.customer_id=b.customer_id and -> email='JANE.BENNETT@sakilacustomer.org'\G*************************** 1. row *************************** id: 1
select_type: SIMPLE
table: a type: ALL
possible_keys: PRIMARY
key: NULL key_len: NULL ref: NULL rows: 590 Extra: Using where*************************** 2. row *************************** id: 1
select_type: SIMPLE
table: b type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 14 Extra:
1、各列表示的意义
Column Meaning
------ ------------------------------------
id The SELECT identifier
select_type The SELECT type
table The tablefor the outputrow
partitions The matching partitions
type The join type
possible_keys The possible indexes to choose
key index actually chosen
key_len The length of the chosen key
ref The columns compared to the index
rows Estimate ofrowsto be examined
filtered Percentage ofrows filtered bytable condition
Extra Additional information
2、各列上的具体描述
id:
包含一组数字,表示查询中执行select子句或操作表的顺序
id相同,执行顺序由上至下,否则id值越大(通常子查询会产生)优先级越高,越先被执行
id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行
select_type:
表示查询中每个select子句的类型(简单 OR复杂)
select_type Value Meaning
------------- -----------------------------------------------
SIMPLE Simple SELECT (notusingUNIONor subqueries)
PRIMARY Outermost SELECT 最外层selectUNIONSecondor later SELECT statement in a UNION
DEPENDENT UNIONSecondor later SELECT statement in a UNION, dependent onouter query
UNION RESULT Result of a UNION.
SUBQUERY FirstSELECTin subquery
DEPENDENT SUBQUERY FirstSELECTin subquery, dependent onouter query(通常为相关子查询)
DERIVED Derived tableSELECT (subquery inFROM clause)
MATERIALIZED Materialized subquery
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be reevaluated
foreachrowof the outer query
UNCACHEABLE UNION The secondor later selectin a UNION that belongs to an uncacheable
subquery (see UNCACHEABLE SUBQUERY)
table:
从哪个表(表名)上输出行记录,也可能是下列值:
• <unionM,N>: The row refers to the unionof the rowswith id valuesof M and N.
• <derivedN>: The row refers to the derived table result for the rowwith an id valueof N.
A derived table may result, for example, from a subquery in the FROM clause.
• <subqueryN>: The row refers to the result of a materialized subquery for the rowwith an id valueof N.
partitions:
查询匹配的记录来自哪一个分区,当使用EXPLAIN,分区PARTITIONS关键字被指定时
type:
连接类型
system 表只有一行
const 表最多只有一行匹配,通用用于主键或者唯一索引比较时
eq_ref 每次与之前的表合并行都只在该表读取一行,这是除了system,const之外最好的一种,
特点是使用=,而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref 如果每次只匹配少数行,那就是比较好的一种,使用=或<=>,可以是左覆盖索引或非主键或非唯一键
fulltext 全文搜索
ref_or_null 与ref类似,但包括NULL
index_merge 表示出现了索引合并优化(包括交集,并集以及交集之间的并集),但不包括跨表和全文索引。
这个比较复杂,目前的理解是合并单表的范围索引扫描(如果成本估算比普通的range要更优的话)
unique_subquery 在in子查询中,就是valuein (select...)把形如“select unique_key_column”的子查询替换。
PS:所以不一定in子句中使用子查询就是低效的!
index_subquery 同上,但把形如”select non_unique_key_column“的子查询替换
range 常数值的范围
index a.当查询是索引覆盖的,即所有数据均可从索引树获取的时候(Extra中有Using Index);
b.以索引顺序从索引中查找数据行的全表扫描(无 Using Index);
c.如果Extra中Using Index与UsingWhere同时出现的话,则是利用索引查找键值的意思;
d.如单独出现,则是用读索引来代替读行,但不用于查找
all 全表扫描
possible_keys:
指出MySQL能使用哪个索引在表中找到行。
查询涉及到的字段上若存在索引则该索引将被列出,但不一定被查询使用。
如果改列为NULL,说明该查询不会使用到当前表上的相关索引,考虑是否有必要添加索引
key
显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
也可能存在key不等于possible_keys的情形,即possible_keys不适合提取所需的行
而查询所选择的列在使用其他索引时更高效
TIPS:查询中若使用了覆盖索引,则该索引仅出现在key列表中
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
ref
表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows
表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
对于InnoDB,该值为预估,不一定精确
Extra
包含不适合在其他列中显示但十分重要的额外信息
三、使用EXPLAIN EXTENDED 示例
(root@localhost) [sakila]> explain extended select * from city where country_id in
-> ( select country_id from country where country='China') and 1=1 \G*************************** 1. row *************************** id: 1
select_type: SIMPLE
table: country type: ALL
possible_keys: PRIMARY
key: NULL key_len: NULL ref: NULL rows: 109 filtered: 100.00 Extra: Using where*************************** 2. row *************************** id: 1
select_type: SIMPLE
table: city type: ref
possible_keys: idx_fk_country_id
key: idx_fk_country_id key_len: 2 ref: sakila.country.country_id rows: 1 filtered: 100.00 Extra: NULL
2 rows in set, 1 warning (0.00 sec)
(root@localhost) [sakila]> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `city`.`city_id` AS `city_id`,`city`.`city` AS `city`,`city`.`country_id`
AS `country_id`,`city`.`last_update` AS `last_update` from `sakila`.`country` join `sakila`.`city` where
((`city`.`country_id` = `country`.`country_id`) and (`country`.`country` = 'China'))
1 row in set (0.00 sec)
-- 从上面的extended使用可以看出,查询中多出了filtered列
-- 其次原来的SQL语句真正在执行的时候被改写,即原来的1=1的条件被去掉
-- 对于复杂的SQL语句我们可以通过该方式获得一个比较清晰的真正被执行的SQL语句
四、EXPLAIN PARTITIONS示例
(root@localhost) [sakila]> CREATE TABLE `actor_part` (
-> `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, -> `first_name` varchar(45) NOT NULL, -> `last_name` varchar(45) NOT NULL, -> `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (`actor_id`), -> KEY `idx_actor_last_name` (`last_name`) -> ) partition by hash(actor_id) partitions 4;
Query OK, 0 rows affected (0.11 sec)
(root@localhost) [sakila]> insert into actor_part select * from actor;
Query OK, 200 rows affected (0.02 sec)
Records: 200 Duplicates: 0 Warnings: 0
(root@localhost) [sakila]> explain select * from actor_part where actor_id=10; -- 未使用partitions时
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+| 1 | SIMPLE | actor_part | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
(root@localhost) [sakila]> explain partitions select * from actor_part where actor_id=10; -- 使用partitions时
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+| 1 | SIMPLE | actor_part | p2 | const | PRIMARY | PRIMARY | 2 | const | 1 | NULL |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
-- 多出了partitions列