MySQL EXPLAIN

通过 EXPLAIN 或者 DESC 命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序

 

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'

 

 EXPLAIN 结果列含义

select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。

table：输出结果集的表。

type：表示MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如图所示。从左至右，性能由最差到最好。

 （1）type=ALL，全表扫描，MySQL遍历全表来找到匹配的行：

EXPLAIN SELECT *
FROM film
WHERE rating > 9

（2）type=index，索引全扫描，MySQL遍历整个索引来查询匹配的行：

EXPLAIN SELECT title
FROM film

 （3）type=range，索引范围扫描，常见于<、<=、>、>=、between等操作符：

EXPLAIN SELECT *
FROM payment
WHERE customer_id >= 300
    AND customer_id <= 350

 （4）type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行，例如：

EXPLAIN SELECT *
FROM payment
WHERE customer_id = 350

 索引idx_fk_customer_id是非唯一索引，查询条件为等值查询条件customer_id=350，所以扫描索引的类型为ref。ref还经常出现在join操作中：

EXPLAIN SELECT b.*, a.*
FROM payment a, customer b
WHERE a.customer_id = b.customer_id

 （5）type=eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用 primary key或者 unique index作为关联条件。

EXPLAIN SELECT *
FROM film a, film_text b
WHERE a.film_id = b.film_id

 （6）type=const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key或者唯一索引unique index进行的查询。

EXPLAIN SELECT *
FROM customer
WHERE email = 'AARON.SELBY@sakilacustomer.org'

 （7）type=NULL，MySQL不用访问表或者索引，直接就能够得到结果

EXPLAIN SELECT 1
FROM dual
WHERE 1

 类型type还有其他值，如ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）、index_merge（索引合并优化）、unique_subquery（in 的后面是一个查询主键字段的子查询）、index_subquery（与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）等。

possible_keys：表示查询时可能使用的索引。

key：表示实际使用的索引。

key_len：使用到索引字段的长度。

rows：扫描行的数量。

Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

 

explain extended

MySQL 4.1开始引入了 explain extended命令，通过 explain extended加上 show warnings，我们能够看到在SQL真正被执行之前优化器做了哪些SQL改写：

EXPLAIN extended SELECT SUM(amount)
FROM customer a, payment b
WHERE 1 = 1
    AND a.customer_id = b.customer_id
    AND email = 'JANE.BENNETT@sakilacustomer.org';

SHOW WARNINGS;

SELECT SUM(`sakila`.`b`.`amount`) AS `sum(amount)`
FROM `sakila`.`customer` `a`
    JOIN `sakila`.`payment` `b`
WHERE `sakila`.`b`.`customer_id` = '77'
    AND 'JANE.BENNETT@sakilacustomer.org' = 'JANE.BENNETT@sakilacustomer.org'

explain extended输出结果中多了 filtered字段，同时从warning的message字段能够看到优化器自动去除了1=1恒成立的条件，也就是说优化器在改写SQL时会自动去掉恒成立的条件。在遇到复杂的SQL时，我们可以利用 explain extended的结果来迅速地获取一个更清晰易读的SQL。

 

explain partitions

MySQL 5.1开始支持分区功能，同时 explain命令也增加了对分区的支持。可以通过 explainpartitions命令查看SQL所访问的分区。例如，创建一个Hash分区的customer_part表，根据分区键查询的时候，能够看到 explain partitions的输出结果中有一列 partitions，其中显示了SQL所需要访问的分区名字p2：

DROP TABLE IF EXISTS customer_part;

CREATE TABLE customer_part (
    `customer_id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_id` tinyint(3) UNSIGNED NOT NULL,
    `first_name` varchar(45) NOT NULL,
    `last_name` varchar(45) NOT NULL,
    `email` varchar(50) DEFAULT NULL,
    `address_id` smallint(5) UNSIGNED NOT NULL,
    `active` tinyint(1) NOT NULL DEFAULT '1',
    `create_date` datetime NOT NULL,
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`customer_id`)
)
PARTITION BY HASH (customer_id) PARTITIONS 8;

INSERT INTO customer_part SELECT * FROM customer;

EXPLAIN partitions SELECT * FROM customer_part WHERE customer_id = 130;