explain 让你的 sql 写的更踏实

概念介绍

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

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

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

全数据表扫描

全索引表扫描

对索引列进行范围查找

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

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

搜索时使用primary key 或 unique类型

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

系统，表仅有一行(=系统表)。这是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;

其中 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;

我们发现 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;

再看这次 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;

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

排序查询

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

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

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

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

复合索引

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

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

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

这条 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