explain 让你的 sql 写的更踏实

本文主要通过一些实例介绍如何使用 mysql 中的 explain 关键字分析查询语句，好让我们的查询语句写的更踏实，也让我们养成用 explain 分析的习惯，养成查询语句的写法习惯。

概念介绍

在 mysql 里， explain 是执行计划的意思，即可以通过该命令看出 mysql 是如何执行该条 sql 的，可以通过分析索引，表结构等方面来优化你的慢查询语句。

mysql 使用 explain + sql 语句 来查看执行计划，执行结果有十个字段，具体描述如下：

字段 描述

id

id相同，执行顺序由上至下；id不同，id的序号会递增，id值越大优先级越高，越先被执行

select_type

主要是用于区别普通查询、联合查询、子查询等的复杂查询

table

当前执行的表

type

访问类型

possible_keys

可能使用的索引

key

实际使用的索引

key_len

使用的索引的长度

ref

显示索引的哪一列被使用了

rows

查询过程中可能扫描的行数

Extra

解析查询的额外信息，通常会显示是否使用了索引，是否需要排序，是否会用到临时表等

其中 type 字段可选值如下，性能从低到高排列：

type 说明

ALL

全数据表扫描

index

全索引表扫描

RANGE

对索引列进行范围查找

INDEX_MERGE

合并索引，使用多个单列索引搜索

REF

根据索引查找一个或多个值

EQ_REF

搜索时使用primary key 或 unique类型

CONST

常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次

SYSTEM

系统，表仅有一行(=系统表)。这是const联接类型的一个特例

OK，概念匆匆介绍之后，结合自己的分析习惯，下面会通过实例聚焦 type 、 key 、 rows 、 Extra 这几个字段，来介绍如何分析我们的查询语句。

实例分析

数据初始化

新建测试表，插入 10 w 数据：

CREATE TABLE `test` (  
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 批量插入 10w 数据
-- DROP PROCEDURE IF EXISTS batchInsert
DELIMITER $  
CREATE PROCEDURE batchInsert () BEGIN DECLARE i INT DEFAULT 1;  
START TRANSACTION; WHILE i<=100000  
DO  
INSERT INTO test (a,b) VALUES (i,i);  
SET i=i+1; END WHILE;  
COMMIT; END $  
CALL batchInsert ();

全表查询

目前默认只有一个主键索引，我们分析下全表查询：

mysql> explain select * from test;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

ALL

NULL

NULL

NULL

NULL

100333

NULL

其中 type 值为 ALL，表示全表扫描了，我们看到 rows 这个字段显示有 100332 条，实际上我们一共才 10w 条数据，说明这个字段只是 mysql 的一个预估，不总是准确的。这个 test 表一次真实的查询时间为：2.708000s，可见这种全表扫描的效率非常低，是需要被优化的。

索引查询

接下来我们分别给字段 a 和 b 添加普通索引。

mysql> alter table test add index idx_a(a);  
mysql> alter table test add index idx_b(b);

看下下面这条 sql：

mysql> explain select * from test where a > 10000;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

ALL

idx_a

NULL

NULL

NULL

100333

Using where

我们发现 type 竟然不是 index, 刚刚不是给字段 a 添加索引了么？还有 possible_keys 也显示了有 a_index，但是 key 显示 null，表示实际上不会使用任何索引，这是为什么呢？

这是因为 select * 的话还需要回到主键索引上查找 b 字段，这个过程叫 回表 。

这条语句会从索引中查出 9w 条数据，也就是说这 9w 条数据都需要 回表 操作，全表扫描都才 10w 条数据，所以在 mysql 最后的决策是还不如直接全表扫描得了，至少还免去了回表过程了。

当然，最后决策是否用索引不是固定的，mysql 会比较各种查询的代价，我们把上面的 sql 中 where 条件再稍微改造一下。

mysql> explain select * from test where a > 90000;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using index condition

再看这次 type 为 range 了， key 为 a_index，表示使用了 a 索引，如我们所愿了。这是因为满足这次索引中查出只有 10000 条数据，mysql 认为 10000 条数据就算回表也要比全表扫描的代价低，因而决定查索引。

上面两条查询说明 mysql 会比较 索引 + 回表 和 直接全表扫描 的查询性能，选择其中更好的作为最后的查询方式，这就是 mysql 优化器的作用了。

还有一点就是这次 Extra 字段中值为 Using index condition，这是指条件过滤的时候用到了索引，但因为是 select * ，所以还是需要回表，再看下面这个语句。

mysql> explain select a from test where a > 90000;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using where; Using index

注意这次 Extra 的值为 Using where; Using index，表示查询用到了索引，且要查询的字段在索引中就能拿到，所以不需要回表，显然这种效率比上面的要高，这也是日常开发中不建议写 select * 的原因，尽量只查询业务所需的字段。

排序查询

再来看一个带排序的查询。

mysql> explain select a from test where a > 90000 order by b;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using index condition; Using filesort

我们知道索引本来就是有序带，但这个 Extra 中返回了一个 Using filesort，说明无法利用索引完成排序，需要从内存或磁盘进行排序，具体哪种排序 explain 是没有体现的。 总之，这种情况也是需要优化的，尽量能利用索引的有序性，比如下面：

mysql> explain select a from test where a > 90000 order by a;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using where; Using index

这次 Extra 值有 Using index 了，表示使用上了索引。

复合索引

我们再创建一个复合索引看看。

mysql> alter table test add index idx_a_b(a,b);

看下之前的查询 mysql> explain select * from test where a > 10000;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

t

range

idx a,idx a_b

idx a b

4

NULL

50166

Using where; Using index

这条 sql 刚刚在没有创建复合索引的时候，是走的全表扫描，现在看 Extra 有 Using index，说明利用了覆盖索引，同样也免去了回表过程，即在 idx a b 索引上就能找出要查询的字段。

总结

本文通过几个实例介绍了如何使用 explain 来分析一条 sql 的查询计划，例子都很简单，旨在能通俗易懂的说明白一些常见的查询问题，也让我们能养成良好的查询习惯。

参考

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_rows https://blog.csdn.net/poxiaonie/article/details/77757471 https://www.cnblogs.com/tufujie/p/9413852.html https://www.xttblog.com/?p=4225