3s->30ms!MySQL 生产环境 GROUP BY 优化实践
案例介绍
首先,我们看一个生产环境上 GROUP BY 语句 的优化案例。
SQL 优化前:执行时间 3s
SQL 优化后:执行时间 30ms!
注意:id
和 reporttime
字段值具有相关性的情况才可以这样修改。
两条 SQL 的 GROUP BY 使用了同一个索引,但是效率却相差很多,这到底是为什么呢?
环境准备
对于 GROUP BY 在使用索引上的优化,分为两种情况讨论:
**表上无索引。**执行时,会生成临时表进行分组。可以通过索引来优化,来避免使用临时表。
表上有索引。 GROUP BY 语句有几种扫描算法:
松散索引扫描(Loose Index Scan)
紧凑索引扫描(Tight Index Scan)
两种算法结合
准备测试数据
无索引的情况
不使用索引的 GROUP BY
Extra: Using temporary
可以看到这里使用到了临时表。
使用索引的 GROUP BY
Extra: Using index & type: index
表示全索引扫描。这种情况下,如果表数据量很大,还是会比较耗时的。
有索引的情况
有索引并正常使用的情况,索引的访问有两种算法:
松散索引扫描(Loose Index Scan)
不需要扫描所有的索引,根据分组前缀(GROUY BY 的字段)跳跃扫描部分
Extra: Using index for group-by
紧凑索引扫描(Tight Index Scan)
需要扫描范围或全部的索引
Extra: Using index
另外还有一种将两种算法结合使用的方式我们后文说明。
下面是两条 SQL 分别使用 Loose Index Scan 和 Tight Index Scan:
第一条 SQL 扫描示意图
第二条 SQL 扫描示意图
下面,我们详细说明一下两种扫描方式。
Loose Index Scan
跳跃扫描部分索引,而不需要扫描全部。
举例:
Extra: Using index for group-by
表示使用松散索引扫描。
使用场景
当需要获取每个分组的某条记录,而非对全部记录做聚合运算时可能会用到,比如:
最小值或最大值:
MIN()
、MAX()
统计类:
COUNT(distinct)
、SUM(distinct)
、AVG(distinct)
注意:如果 SQL 语句中既有 1-2 个 min\max
,也有 1-3 个 count(distinct)\sum(distinct)\avg(distinct)
时,无法用到 Loose index;两组分别出现的时候才可能会用到。
distinct
可以转换为 GROUP BY 进行处理。
使用到 Loose Index Scan 其他必要条件:
查询基于一个表。
GROUP BY 的字段满足索引的最左匹配原则。
聚合函数使用的列,必须包含在索引上;且使用多个聚合函数时,必须使用相同的字段,且 GROUP BY 字段+聚合函数字段也必须满足最左匹配原则。
索引中字段必须是全字段索引,而不能是前缀索引,例如
INDEX(c1(10))
以上条件结合索引的结构就很好理解了。
另外,在选择是否使用 Loose Index Scan 时,也会受到 SQL、统计信息、成本等因素的影响。
举例:
Tight Index Scan
对于无法使用到 Loose Index Scan 的一些 GROUP BY,在满足索引最左匹配原则情况下可能会用到 Tight Index Scan。
该种方式实际上是范围索引扫描或全部索引扫描,数据量大的情况下性能仍然可能会比较差,但是相比无索引还是可以避免使用临时表和全表扫描,在某些情况下有一定的优化作用。
两种算法结合
对于统计类 AGG(DISTINCT) 即 SUM|COUNT|AVG(distinct),可能会出现使用松散索引扫描(Loose Index Scan)成本大于紧凑索引扫描(Tight Index Scan)的情况。
两种方式在引擎层主要包含的成本:
Loose Index Scan
读取分组的第一条记录,得到分组前缀
根据分组前缀读取分组的第一条或最后一条记录返回给 SERVER 层
Tight Index Scan
从 ENGINE 层读取数据,返回给 SERVER 层
SERVER 层判断是否符合
WHERE
条件的记录,并根据聚合函数进行处理
可以看到,对于 ENGINE 层的访问,Loose Index Scan 的成本有可能会高于 Tight Index Scan,且在 MySQL 中,引擎层读取数据页的成本常数是 1,SERVER 层判断一条记录的成本常数是 0.2。
至于 MIN/MAX 为什么不会出现 Loose Index Scan 成本 > Tight Index Scan 成本,我理解只有到组内值都是唯一的情况下才会出现吧?那这样也没有必要去分组求最值了。欢迎在留言处讨论。
在某些情况下,Loose Index Scan 的成本会高于 Tight Index Scan,比如:
当分组较多,但组内的记录数并不多或唯一值较高的情况,对于每一个分组,都需要扫描两次,能跳过的记录数很少的情况。即 Loose Index Scan 在分组字段的选择性相对不太高,组内的数据量相对较多的情况更适用。
举例:
该 SQL 在当前的测试数据中,松散扫描的成本还是要低于紧凑扫描。
新建一个相同表结构的表,插入下面的测试数据。
Extra: Using index for group-by (scanning)
该方式可以理解为 Loose Index Scan 的扩展或两种方式的结合(索引顺序扫描的同时进行去重)。
示意图
最后,再回到文章开头的案例,其执行计划如下:
优化前
优化后
其核心就是将紧凑索引扫描转化为了松散索引扫描。
总结
对于 GROUP BY 可以使用索引进行优化,Loose Index Scan 相对于 Tight Index Scan 在一些情况下可以大大减少扫描的行数,使用 Loose Index Scan 时,Extra: Using index for group-by。
在 Loose Index Scan 的成本大于 Tight Index Scan 的一些情况下,可以尝试用到两者的结合的方式,Extra: Using index for group-by (scanning)
Loose Index Scan 更适用于分组内重复值相对较多,分组个数相对较少的情况。
评论