MySQL——通过EXPLAIN分析SQL的执行计划

226次阅读

原链接：https://www.cnblogs.com/songwenjie/p/9409852.html

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

下面分别对 EXPLAIN 命令结果的每一列进行说明：

select_type: 表示 SELECT 的类型，常见的取值有：

类型	说明
SIMPLE	简单表，不使用表连接或子查询
PRIMARY	主查询，即外层的查询
UNION	UNION 中的第二个或者后面的查询语句
SUBQUERY	子查询中的第一个

table: 输出结果集的表（表别名）

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

ALL	全表扫描
index	索引全扫描
range	索引范围扫描
ref	非唯一索引扫描
eq_ref	唯一索引扫描
const,system	单表最多有一个匹配行
NULL	不用扫描表或索引

type=ALL，全表扫描，MySQL 遍历全表来找到匹配行 一般是没有 where 条件或者 where 条件没有使用索引的查询语句
EXPLAIN SELECT * FROM customer WHERE active=0;
type=index，索引全扫描，MySQL 遍历整个索引来查询匹配行，并不会扫描表 一般是查询的字段都有索引的查询语句
EXPLAIN SELECT store_id FROM customer;
type=range，索引范围扫描，常用于 <、<=、>、>=、between 等操作EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;

注意这种情况下比较的字段是需要加索引的，如果没有索引，则 MySQL 会进行全表扫描，如下面这种情况，create_date 字段没有加索引：

EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ;
type=ref，使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行 store_id 字段存在普通索引（非唯一索引）
EXPLAIN SELECT * FROM customer WHERE store_id=10;

ref类型还经常会出现在 join 操作中：

customer、payment表关联查询，关联字段customer.customer_id（主键），payment.customer_id（非唯一索引）。表关联查询时必定会有一张表进行全表扫描，此表一定是几张表中记录行数最少的表，然后再通过非唯一索引寻找其他关联表中的匹配行，以此达到表关联时扫描行数最少。

因为 customer、payment 两表中 customer 表的记录行数最少，所以 customer 表进行全表扫描，payment表通过非唯一索引寻找匹配行。

EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id;
type=eq_ref，类似 ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配 eq_ref 一般出现在多表连接时使用 primary key 或者 unique index 作为关联条件。
film、film_text表关联查询和上一条所说的基本一致，只不过关联条件由非唯一索引变成了主键。

EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id;
type=const/system，单表中最多有一条匹配行，查询起来非常迅速，所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理 const/system 出现在根据主键 primary key 或者唯一索引 unique index 进行的查询
根据主键 primary key 进行的查询：

EXPLAIN SELECT * FROM customer WHERE customer_id =10;

根据唯一索引 unique index 进行的查询：

EXPLAIN SELECT * FROM customer WHERE email ='MARY.SMITH@sakilacustomer.org';
type=NULL，MySQL 不用访问表或者索引，直接就能够得到结果

possible_keys: 表示查询可能使用的索引
key: 实际使用的索引
key_len: 使用索引字段的长度
ref: 使用哪个列或常数与 key 一起从表中选择行。
rows: 扫描行的数量
filtered: 存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例(百分比)

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

Using Index	表示索引覆盖，不会回表查询
Using Where	表示进行了回表查询
Using Index Condition	表示进行了 ICP 优化
Using Flesort	表示 MySQL 需额外排序操作, 不能通过索引顺序达到排序效果

什么是 ICP？

MySQL5.6 引入了 Index Condition Pushdown（ICP） 的特性，进一步优化了查询。Pushdown 表示操作下放，某些情况下的条件过滤操作下放到存储引擎。

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id>=300 AND customer_id<=400;

在 5.6 版本之前：

优化器首先使用复合索引 idx_rental_date 过滤出符合条件 rental_date='2005-05-25' 的记录，然后根据复合索引 idx_rental_date 回表获取记录，最终根据条件 customer_id>=300 AND customer_id<=400 过滤出最后的查询结果（在服务层完成）。

在 5.6 版本之后：

MySQL 使用了 ICP 来进一步优化查询，在检索的时候，把条件 customer_id>=300 AND customer_id<=400 也推到存储引擎层完成过滤，这样能够降低不必要的 IO 访问。Extra 为 Using index condition 就表示使用了 ICP 优化。