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

4,595 阅读7分钟

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

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

开发:数据量太大了,没办法

产品:不行,必须优化

开发:....

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

本文目的

  1. 帮助大家认识explain,遇到上述问题的时候可以到此来查阅执行计划中每个字段的意思
  2. 能根据慢查询的执行计划快速找到问题所在
  3. 提供常见的问题原因以及解决方案

explain能干嘛

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

explain

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

  1. 与mysql建立连接
  2. 查询缓存是否存在,如果有则直接返回结果
  3. 解析器,主要是对sql进行解析
  4. 查询优化器,主要对sql进行各种优化,包括重写查询、决定表的读取顺序以及选择合适的索引等等。。并产生执行计划
  5. 去存储引擎查询结果

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

explain字段解释

看一条简单的执行计划

explain select * from t_user where id = 1;

执行计划1

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

id

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

规则:

  1. id不相同的,id值越大越先执行
  2. id相同的,从上到下顺序执行

select_type

常见的值以及描述如下

描述
SIMPLE 简单的SELECT语句(不包括UNION操作或子查询操作)
PRIMARY 查询中最外层的SELECT(如两表做UNION或者存在子查询的外层的表操作为PRIMARY,内层的操作为UNION)
UNION UNION操作中,查询中处于内层的SELECT,即被union的SELECT
SUBQUERY 子查询中的SELECT
DERIVED 表示包含在 From 子句中的 Select 查询
UNION RESULT union的结果,此时id为NULL

table

涉及的表

type(重要)

这列很重要,显示了连接使用哪种类型,有无使用索引, 常见的值从最好到最差如下 system > const > eq_ref > ref > range > index > all

各值的描述如下

描述
system 表只有一行,MyISAM引擎所有
const 常量连接,表最多只有一行匹配,通常用于主键或者唯一索引比较时,如:
select * from t_user where id = 1;
eq_ref 表关联查询时,对于前表的每一行,后表只有一行与之匹配。
(1) join查询
(2) 命中主键或者非空唯一索引
ref 只使用了索引的最左前缀或者使用的索引是非唯一索引、非主键索引
range between,in,>等都是典型的范围(range)查询
index 需要扫描索引上的全部数据,如:
select count(*) from t_user;
all 全表扫描

possible_keys

表示可能用到的索引

key

表示最终用到的key

ref

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

rows

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

filtered

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

Extra(重要)

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

常见的值及描述如下

描述
Using filesort 当order by 无法利用索引完成排序时,优化器不得不选择合适的算法从内存或者磁盘进行排序
Using temporary 使用了临时表
Using index select后面的查询字段在索引中就可以取到,无需再回表了,即所谓的覆盖索引,这种查询性能很好
Using index condition mysql5.6之后引入了ICP(索引条件下推)
Using where Mysql 服务器在存储引擎检索行后再进行过滤

优化原则

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

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

  2. type最好能达到ref级别

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

优化思路

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

针对优化原则1,2

上述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

  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的子句不一样时

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

    执行计划

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

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

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

    执行计划

    这种情况有时候无法避免,只能尽量将distinct的字段和order by的字段使用相同的索引。还有会出现临时表的情况有: from 中的子查询、union,这里就不一一举例了。

总结

sql优化已经是我们后端开发的内化技能之一了,在学习框架,设计思想的同时,不要忘记打牢基础,希望各位能够有所收获。