sql在进行查询的时候首先，与MySQL建立连接，然后查询缓存，缓存中存在，则直接返回，缓存中没有，通过解析器对sql进行解析，然后通过查询优化器对sql进行优化（包括重写查询、选择合适的索引等等..），然后去存储引擎中查询结果。
而对sql性能影响非常关键的一步，就是查询优化器对sql的优化，而"explain"就是去查询优化器中查询sql的执行计划。
explain(翻译：执行)
- 执行计划格式
explain + 具体sql
我们先来一个简单地执行计划：
explain select * from dept where id = 5
explain select * from dept where id in (select id from employee)
image.png
image.png
我们可以看到，一条执行计划会展示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
各值的描述如下：
1.system:表只有一行，MyISAM引擎所有。
2.const:常量连接，表最多只有一行匹配，通常用于主键或者唯一索引比较时,如:
explain select * from dept where id=5
3.eq_ref:表关联查询时，对于前表的每一行,后表只有一行与之匹配。
(1) join查询
(2) 命中主键或者非空唯一索引
4.ref:只使用了索引的最左前缀或者使用的索引是非唯一索引、非主键索引
5.range:between，in，>等都是典型的范围(range)查询 如:explain select * from dept where id BETWEEN 5 and 8;
6.index:需要扫描索引列上的全部数据， 如：explain select id from dept
7.All:全表扫描 如：explain select * from dept
- 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点其实都可以通过优化索引来达到目的,而要想让我们建的索引达到最优,则需要依据一个原则: 三星索引原则,简单描述就是:
☆: 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常见的有以下几种情况:
- 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 ;
- 组合索引跨列: 举例: 给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);
- 由于group by第一步默认进行了排序,所以当group by 的字段满足上述条件是,也会出现
Using filesort,可以在group by后面加上order by null取消排序。
最后，我们来看下Using temporary(使用了临时表):
临时表的出现对性能影响是很大的,主要会出现在以下情况中:
- 分组字段不在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 哪些字段.
- 表连接中，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的字段排序就不会出现临时表了,这里就不举例了。
结论: 连接查询的时候，排序字段使用驱动表的字段
- order by和group by的子句不一样时
如下Sql:
explain select * from t_user group by group_id order by `name`;
这种情况只能尽量使用同一个字段来分组和排序了，否则无法避免
- distinct查询并且加上order by时
如下sql:
explain select DISTINCT(`name`) from t_user order by age;
这种情况有时候无法避免，只能尽量将distinct的字段和order by的字段使用相同的索引。
还有会出现临时表的情况有: from 中的子查询、union，这里就不一一举例了。
此文章参考：https://kuy8.com/sIFEp
