EXPLAIN语句能够被用于获取一些关于SQL执行时的相关信息,比如表的连接顺序,对表的方式方式等等。通过对该相关信息进行进一步的分析,我们 可以通过对表添加适当的索引,以及优化连接顺序,使用提示等等手段来达到使SQL高效运行的目的。本文描述了EXPLAIN的用法并给出了相关示例。
一、EXPLAIN概述
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: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 590 Extra: Using where*************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 14 Extra:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
1、各列表示的意义
Column Meaningid The SELECT identifier select_type The SELECT typetable The table for the output rowpartitions The matching partitionstype The join typepossible_keys The possible indexes to choosekey index actually chosenkey_len The length of the chosen keyref The columns compared to the indexrows Estimate of rows to be examinedfiltered Percentage of rows filtered by table conditionExtra Additional information
2、各列上的具体描述
id: 包含一组数字,表示查询中执行select子句或操作表的顺序 id相同,执行顺序由上至下,否则id值越大(通常子查询会产生)优先级越高,越先被执行 id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行select_type: 表示查询中每个select子句的类型(简单 OR复杂) select_type Value Meaning ------------- ----------------------------------------------- SIMPLE Simple SELECT (not using UNION or subqueries) PRIMARY Outermost SELECT 最外层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) MATERIALIZED Materialized subquery UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be reevaluated 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) table: 从哪个表(表名)上输出行记录,也可能是下列值: • <unionM,N>: The row refers to the union of the rows with id values of M and N. • <derivedN>: The row refers to the derived table result for the row with an id value of 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 row with an id value of 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子查询中,就是value in (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与Using Where同时出现的话,则是利用索引查找键值的意思; d.如单独出现,则是用读索引来代替读行,但不用于查找 all 全表扫描possible_keys: 指出MySQL能使用哪个索引在表中找到行。 查询涉及到的字段上若存在索引则该索引将被列出,但不一定被查询使用。 如果改列为NULL,说明该查询不会使用到当前表上的相关索引,考虑是否有必要添加索引key 显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL 也可能存在key不等于possible_keys的情形,即possible_keys不适合提取所需的行 而查询所选择的列在使用其他索引时更高效 TIPS:查询中若使用了覆盖索引,则该索引仅出现在key列表中key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度ref 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值rows 表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数 对于InnoDB,该值为预估,不一定精确Extra 包含不适合在其他列中显示但十分重要的额外信息
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
三、使用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: ALLpossible_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: refpossible_keys: idx_fk_country_id key: idx_fk_country_id key_len: 2 ref: sakila.country.country_id rows: 1 filtered: 100.00 Extra: NULL2 rows in set, 1 warning (0.00 sec)(root@localhost) [sakila]> show warnings\G*************************** 1. row *************************** Level: Note Code: 1003Message: /* 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语句
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
四、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时+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)(root@localhost) [sakila]> explain partitions select * from actor_part where actor_id=10; -- 使用partitions时+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)-- 多出了partitions列
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
五、参考:
MySQL reference manual 5.6其他个人博客
转载自:http://blog.csdn.net/leshami/article/details/49069199