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
，这里就不一一举例了。