MySQL EXPLAIN SQL 输出信息描述

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: 1select_type: SIMPLEtable: a type: ALLpossible_keys: PRIMARYkey: NULLkey_len: NULLref: NULL rows: 590Extra: Using where*************************** 2. row *************************** id: 1select_type: SIMPLEtable: b type: refpossible_keys: idx_fk_customer_idkey: idx_fk_customer_idkey_len: 2ref: sakila.a.customer_id rows: 14Extra:

1、各列表示的意义

ColumnMeaning------------------------------------------idThe SELECT identifier select_type The SELECT typetable The table for the output rowpartitionsThe matching partitionstypeThe join typepossible_keys The possible indexes to choosekey index actually chosenkey_len The length of the chosen keyref The columns compared to the indexrowsEstimate of rows to be examinedfilteredPercentage of rows filtered by table conditionExtra Additional information

2、各列上的具体描述

id：包含一组数字，表示查询中执行select子句或操作表的顺序id相同，执行顺序由上至下，否则id值越大（通常子查询会产生）优先级越高，越先被执行id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行select_type:表示查询中每个select子句的类型（简单 OR复杂）select_typeValue Meaning------------------------------------------------------------SIMPLE Simple SELECT （not using UNION or subqueries）PRIMARYOutermost SELECT 最外层selectUNIONSecond or later SELECT statement in a UNIONDEPENDENT UNIONSecond or later SELECT statement in a UNION, dependent on outer queryUNIONRESULT Result of a UNION.SUBQUERY First SELECT in subqueryDEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query（通常为相关子查询）DERIVEDDerived table SELECT （subquery in FROM clause）MATERIALIZED Materialized subqueryUNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be reevaluated for each row of the outer queryUNCACHEABLE UNIONThe 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类似，但包括NULLindex_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包含不适合在其他列中显示但十分重要的额外信息

三、使用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: 1select_type: SIMPLEtable: country type: ALLpossible_keys: PRIMARYkey: NULLkey_len: NULLref: NULL rows: 109 filtered: 100.00Extra: Using where*************************** 2. row *************************** id: 1select_type: SIMPLEtable: city type: refpossible_keys: idx_fk_country_idkey: idx_fk_country_idkey_len: 2ref: sakila.country.country_id rows: 1 filtered: 100.00Extra: NULL2 rows in set, 1 warning （0.00 sec）（root@localhost） [sakila]> show warningsG*************************** 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语句

四、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: 200Duplicates: 0Warnings: 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列

本文永久更新链接地址