sql优化 explain关键字进行性能分析

• 一：背景介绍
• 二：思路&方案
• • mysql的执行顺序
  • EXPLAIN关键字
  • • EXPLAIN的输出列
  • distinct和group by效率对比
• 三：过程
• • 优化案例
  • 进行优化
• 总结

一：背景介绍

在实际项目开发过程中，由于SQL语句查询11s导致接口请求超时，现在对SQL语句进行优化。

二：思路&方案

一般情况下对SQL语句进行优化分为两步：1.根据mysql的执行顺序，查看有不合理的位置。2.通过expain关键字进行分析sql性能这里主要看的是索引是否生效。

mysql的执行顺序

1. from
2. on
3. join
4. where
5. group by
6. having + 聚合函数
7. select
8. distinct
9. order by
10. limit

我们都知道数据在在越早的阶段过滤掉越好，sql的性能也就越高。

总结：
能够写在on里的条件，就不要卸载where里，能够写在where里的条件就不写在having里。

EXPLAIN关键字

MySQL EXPLAIN官网介绍
EXPLAIN语句提供了MySQL如何执行语句的信息。EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。

EXPLAIN的输出列

MySQL官网的图

其中最重要的字段为：id、type、key、rows、Extra

id select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，id越大，优先级越高，Id相等，从上往下顺序执行。

select_type
查询的类型，用于区分普通查询、联合查询、子查询。
SIMPLE类型
PRIMARY类型
UNION类型
DEPENDENT UNION类型

type
描述了表是如何连接的
从好到坏的次序
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
阿里巴巴规约要求最差也要是range级别。

distinct和group by效率对比

在语义相同，有索引的情况下：group by和distinct都能使用索引，效率相同。

在语义相同，无索引的情况下：distinct效率高于group by。原因是distinct 和 group by都会进行分组操作，但group by可能会进行排序，触发filesort，导致sql执行效率低下。

三：过程

优化案例

1.两张表的左连接只走了一个表的索引，并没有走arpro_user_info表的索引。
2.使用到了group by

进行优化

1. 将group by 修改为distinct
2. 解决索引失效的问题，aui这张表id是它的主键，按理来说会走主键索引，而不是最后进行的全表查询。
   
   我们可以看到arpro_user_info表的主键为varchar类型，但是arpro_course_info表的外键为bigint类型，由于join的字段类型不一致导致索引失效。

阿里规约中也给出了建议：

现在我们将arpro_user_info表的字段修改bigint，进行测试。

我们可以看到，两张表都走了对应的索引。

总结

清楚mysql执行顺序，以及explain关键字的使用之后，对于我们之后写出高性能sql有很大的帮助。