有时候我们会遇到这样的事情:项目上线了,一切顺利,就等产品验收完毕回家睡觉了,但是产品突然来找你了:
产品:首页为什么加载这么慢
开发:数据量太大了,没办法
产品:不行,必须优化
开发:....
这种情况,很大概率是sql出现慢查询了,此时我们就需要把查询sql拉出来优化优化了,那我们就需要使用到本文要说的explain命令了。
![26d991ca3c581aa44865f2cb9a7a3829.png](https://i-blog.csdnimg.cn/blog_migrate/721d9649a2a269056b3098ce88cac878.png)
本文目的
1. 帮助大家认识explain,遇到上述问题的时候可以到此来查阅执行计划中每个字段的意思 2. 能根据慢查询的执行计划快速找到问题所在 3. 提供常见的问题原因以及解决方案![26d991ca3c581aa44865f2cb9a7a3829.png](https://i-blog.csdnimg.cn/blog_migrate/721d9649a2a269056b3098ce88cac878.png)
explain能干嘛
在了解explain之前,不妨先看下mysql服务大致的逻辑架构图,以对其有一个整体的认识:
而我们使用explain即是去查询优化器查询执行计划。
![26d991ca3c581aa44865f2cb9a7a3829.png](https://i-blog.csdnimg.cn/blog_migrate/721d9649a2a269056b3098ce88cac878.png)
explain字段解释
看一条简单的执行计划:explain select * from t_user where id = 1;
常见的值以及描述如下:
SIMPLE | 简单的SELECT语句(不包括UNION操作或子查询操作) |
PRIMARY | 查询中最外层的SELECT(如两表做UNION或者存在子查询的外层的表操作为PRIMARY,内层的操作为UNION) |
UNION | UNION操作中,查询中处于内层的SELECT,即被union的SELECT |
SUBQUERY | 子查询中的SELECT |
DERIVED | 表示包含在 From 子句中的 Select 查询 |
UNION RESULT | union的结果,此时id为NULL |
涉及到的表
4 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 | 全表扫描 |
显示索引的哪一列被使用了,有时候会是一个常量:表示哪些列或常量被用于查找索引列上的值
8 rows估算出结果集行数,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数, 原则上 rows 越少越好。
9 filtered 查询结果的行数占上面rows的百分比10 Extra(重要)这一列也很重要,主要展示额外的信息说明,能够给出让我们深入理解执行计划进一步的细节信息
常见的值及描述如下:
Using filesort | 当order by 无法利用索引完成排序时,优化器不得不选择合适的算法从内存或者磁盘进行排序 |
Using temporary | 使用了临时表 |
Using index | select后面的查询字段在索引中就可以取到,无需再回表了,即所谓的覆盖索引,这种查询性能很好 |
Using index condition | mysql5.6之后引入了ICP(索引条件下推) |
Using where | Mysql 服务器在存储引擎检索行后再进行过滤 |
![26d991ca3c581aa44865f2cb9a7a3829.png](https://i-blog.csdnimg.cn/blog_migrate/721d9649a2a269056b3098ce88cac878.png)
优化原则
通常有以下几种优化原则:
1. 让主要查询语句使用到合适的索引,type出现ALL(全表扫描)需格外注意,同时建立合适的索引以减少possible_keys的数量
2. type最好能达到ref级别
3. Extra列出现Using temporary、Using filesort(文件排序)务必去除
![26d991ca3c581aa44865f2cb9a7a3829.png](https://i-blog.csdnimg.cn/blog_migrate/721d9649a2a269056b3098ce88cac878.png)
基于第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;
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;
explain select * from t_user t1 left join t_group t2 on t1.group_id = t2.id order by t2.id;
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;
·END·