3. 通过 EXPLAIN 分析低效 SQL 的执行计划

可以通过 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'\G;
  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL

select_type：表示 SELECT 的类型，常见的取值有：
- SIMPLE（简单表，即不使用表连接或者子查询）
- PRIMARY（主查询，即外层的查询）
- UNION（UNION 中的第二个或者后面的查询语句）
- SUBQUERY（子查询中的第一个 SELECT）
table：输出结果集的表。
type：表示 MySQL 在表中找到所需行的方式，或者叫访问类型。
possible_keys：表示查询时可能使用的索引。
key：表示实际使用的索引。
key_len：使用到索引字段的长度。
rows：扫描行的数量。
Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

type

表示 MySQL 在表中找到所需行的方式，或者叫访问类型，常见的类型有：

ALL

全表扫描，MySQL 遍历全表来找到所有的匹配行。

index

索引全扫描，MySQL 遍历整个索引来查询匹配的行。

EXPLAIN
SELECT film_id
FROM film \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_fk_language_id
      key_len: 1
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using index

range

索引范围扫描，常见于 <、<=、>、>=、between 等操作符。

EXPLAIN
SELECT *
FROM payment
WHERE customer_id >= 300
  AND customer_id <= 350;
  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 1350
     filtered: 100.00
        Extra: Using index condition

ref

使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行。

EXPLAIN
SELECT *
FROM payment
WHERE customer_id = 350 \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: const
         rows: 23
     filtered: 100.00
        Extra: NULL

eq_ref

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

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

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.b.film_id
         rows: 1
     filtered: 100.00
        Extra: NULL

const/system

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

EXPLAIN
SELECT *
FROM (
    SELECT *
    FROM customer
    WHERE email = 'AARON.SELBY@sakilacustomer.org'
) a \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: const
possible_keys: uk_email
          key: uk_email
      key_len: 203
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

NULL

MySQL 不用访问表或者索引，直接就能够得到结果。

EXPLAIN
SELECT 1
FROM DUAL \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used

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

Extra

Using index：表示 MySQL 将使用覆盖索引，以避免回表。
Using where：表示会在存储引擎检索之后再进行过滤。
Using temporary：表示会使用一个临时表。
Using filesort：表示对查询结果进行了排序。

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' \G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: const
possible_keys: PRIMARY,uk_email
          key: uk_email
      key_len: 203
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: const
         rows: 28
     filtered: 100.00
        Extra: NULL
2 rows in set, 2 warnings (0.00 sec)

SHOW WARNINGS\G;

*************************** 1. row ***************************
  Level: Warning
   Code: 1681
Message: 'EXTENDED' is deprecated and will be removed in a future release.
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ 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'))

WARNING信息中的SQL语句美化后，如下所示：

/* select#1 */
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'))

在遇到复杂的 SQL 时，我们可以利用 explain extended 的结果来迅速地获取一个更清晰易读的 SQL。

可以通过 explain partitions 命令查看 SQL 所访问的分区。

上一页2. 定位执行效率较低的 SQL 语句下一页4. 通过 SHOW PROFILE 分析 SQL

最后更新于8个月前

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 partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 599 filtered: 10.00 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b partitions: NULL type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 26 filtered: 100.00 Extra: NULL

EXPLAIN SELECT film_id FROM film \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: index possible_keys: NULL key: idx_fk_language_id key_len: 1 ref: NULL rows: 1000 filtered: 100.00 Extra: Using index

EXPLAIN SELECT * FROM payment WHERE customer_id >= 300 AND customer_id <= 350; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment partitions: NULL type: range possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 1350 filtered: 100.00 Extra: Using index condition

EXPLAIN SELECT * FROM payment WHERE customer_id = 350 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment partitions: NULL type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: const rows: 23 filtered: 100.00 Extra: NULL

EXPLAIN SELECT * FROM film a, film_text b WHERE a.film_id = b.film_id \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: b partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 1000 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.b.film_id rows: 1 filtered: 100.00 Extra: NULL

EXPLAIN SELECT * FROM ( SELECT * FROM customer WHERE email = 'AARON.SELBY@sakilacustomer.org' ) a \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: customer partitions: NULL type: const possible_keys: uk_email key: uk_email key_len: 203 ref: const rows: 1 filtered: 100.00 Extra: NULL

EXPLAIN SELECT 1 FROM DUAL \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used

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' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a partitions: NULL type: const possible_keys: PRIMARY,uk_email key: uk_email key_len: 203 ref: const rows: 1 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b partitions: NULL type: ref possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: const rows: 28 filtered: 100.00 Extra: NULL 2 rows in set, 2 warnings (0.00 sec)

SHOW WARNINGS\G; *************************** 1. row *************************** Level: Warning Code: 1681 Message: 'EXTENDED' is deprecated and will be removed in a future release. *************************** 2. row *************************** Level: Note Code: 1003 Message: /* select#1 */ 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'))

/* select#1 */ 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'))