SQL 优化：使用 explain 分析 SQL 执行计划

有时候我们会遇到这样的事情：项目上线了，一切顺利，就等产品验收完毕回家睡觉了，但是产品突然来找你了 :

产品：首页为什么加载这么慢

开发：数据量太大了，没办法

产品：不行，必须优化

开发： ....

这种情况，很大概率是 sql 出现慢查询了，此时我们就需要把查询sql拉出来优化优化了,那我们就需要使用到本文要说的explain命令了。

本文目的

1. 帮助大家认识 explain, 遇到上述问题的时候可以到此来查阅执行计划中每个字段的意思

2.  能根据慢查询的执行计划快速找到问题所在

3. 提供常见的问题原因以及解决方案

explain能干嘛

在了解 explain 之前，不妨先看下 mysql 服务大致的逻辑架构图，以对其有一个整体的认识 :

从图中可以看出 , 我们的 sql 在查询的时候主要需要经历以下步骤 :

1. 与 mysql 建立连接

2. 查询缓存是否存在 , 如果有则直接返回结果

3. 解析器 , 主要是对 sql 进行解析

4. 查询优化器 , 主要对 sql 进行各种优化 , 包括重写查询、决定表的读取顺序以及选择合适的索引等等。。并产生执行计划

5. 去存储引擎查询结果

而我们使用 explain 即是去查询优化器查询执行计划。

explain字段解释

看一条简单的执行计划 :

explain select * from t_user where id = 1;

我们可以看到，一个执行计划会展示 12 个相关的字段 , 下面我们对主要字段以及这些字段常见的值进行解释 :

含义：是一组数字，表示的是查询中执行 select 子句或者是操作表的顺序

规则：

1. id 不相同的， id 值越大越先执行

2. id 相同的 , 从上到下顺序执行

常见的值以及描述如下 :

涉及到的表

这列很重要 , 显示了连接使用哪种类型 , 有无使用索引，

常见的值从最好到最差如下 :

system > const > eq_ref > ref > range > index > all 

各值的描述如下：

表示可能用到的索引

表示最终用到的 key

7

显示索引的哪一列被使用了，有时候会是一个常量：表示哪些列或常量被用于查找索引列上的值

估算出结果集行数，表示 MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数,   原则上 rows 越少越好。

查询结果的行数占上面 rows的百分比

这一列也很重要 ,主要展示 额外的信息说明 , 能够给出让我们深入理解执行计划进一步的细节信息

常见的值及描述如下 :

优化原则

通常有以下几种优化原则 :

1.  让主要查询语句使用到合适的索引 ,type 出现 ALL( 全表扫描 ) 需格外注意 , 同时建立合适的索引以减少 possible_keys 的数量

2. type 最好能达到 ref 级别                                                           

3. Extra 列出现 Using temporary、Using filesort（文件排序）务必去除

优化思路

针对上面提到的几点优化原则 , 提供如下的优化思路 :

上述 1,2 点其实都可以通过优化索引 来达到目的 , 而要想让我们建的索引达到最优 , 则需要依据一个原则 : 三星索引原则 , 简单描述就是 :

☆ : where 后条件匹配的索引列越多扫描的数据将越少 ,

比如组合索引 (a,b,c), 最好在 where 后面能同时用到索引上的 a,b,c 这三列

☆ : 避免再次排序

简单来说 , 就是排序字段尽量使用索引字段 , 因为索引默认是排好序的 , 使用索引字段排序可以避免再次排序

☆ : 索引行包含查询语句中所有的列 , 即覆盖索引

基于这一点，我们应该少用 select * 来查询，以增加覆盖索引的可能性

如果你的索引能集齐上述三颗星 , 则说明你的索引是最优的索引！

基于 第 3 点 ,

我们创建如下用户表 :

CREATE TABLE `t_user`  (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `group_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1240277101395107842 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

分组表 :

CREATE TABLE `t_group`  (
  `id` bigint(20) NOT NULL,
  `group_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

并插入一些数据。

我们首先来看下 Using filesort, 出现 Using filesort 常见的有以下几种情况 :

1. order by 的字段不在 where 条件中，比如 :

下面这条 sql 会出现 Using filesort:

select * from t_user where group_id = 2 and age = 32 order by name;

但是下面这条 sql 不会 :

select * from t_user where group_id = 2 and age = 32 order by group_id ;

2. 组合索引跨列 : 举例 : 给 t_user 表创建索引 (name,age,group_id),

则下面这条 sql 排序会出现 Using filesort:

select * from t_user where name= '李A' order by group_id;

但是下面这条就不会 :

select * from t_user where name = '李A' order by age;

因为第一条查询 order by 跳过了 age, 直接使用了 group_id;

删除索引 (name,age,group_id);

3. 由于 group by 第一步默认进行了排序 , 所以当 group by 的字段满足上述条件是 , 也会出现

Using filesort, 可以在 group by 后面加上 order by null 取消排序。

最后，我们来看下 Using temporary(使用了临时表):

临时表的出现对性能影响是很大的 , 主要会出现在以下情况中 :

1. 分组字段不在where条件后面,并且group by字段不是最终使用到的索引,原因有点类似于上面的Using filesort:

比如 :

下面这条 sql 会出现 Using temporary :

select * from t_user where group_id = 2 and name= '李A' group by age;

但是下面这条 sql 不会 :

select * from t_user where name = '李A' and age = 21 group by age;

结论 : where哪些字段,就group by 哪些字段.

2. 表连接中， order by 的列不是驱动表中的

如下 sql 是会创建临时表的 :

explain select * from t_user t1 left join t_group t2 on t1.group_id = t2.id order by t2.id;

因为 t1 和 t2 连接的时候 ,t1 是驱动表 , 但是排序使用了被驱动表 t2 中的字段。改为 t1 的字段排序就不会出现临时表了 , 这里就不举例了。

结论 : 连接查询的时候，排序字段使用驱动表的字段

3. order by 和 group by 的子句不一样时

如下 Sql:

explain select * from t_user group by group_id order by `name`;

这种情况只能尽量使用同一个字段来分组和排序了，否则无法避免

4. distinct 查询并且加上 order by 时

如下 sql:

explain select DISTINCT(`name`) from t_user order by age;

这种情况有时候无法避免，只能尽量将 distinct 的字段和 order by 的字段使用相同的索引。

还有会出现临时表的情况有 : from 中的子查询、 union ，这里就不一一举例了。