一次对group by时间导致的慢查询的优化

最新推荐文章于 2024-04-30 17:28:01 发布

Micrle_007

最新推荐文章于 2024-04-30 17:28:01 发布

阅读量2k

点赞数 1

原文链接：https://zhuanlan.zhihu.com/p/341653200

版权

MySQL 同时被 2 个专栏收录

63 篇文章 3 订阅

订阅专栏

Oracle

13 篇文章 0 订阅

订阅专栏

前言：

最近在测试环境中点击一个图表展示页面时，半天才得到后台响应的数据进行页面渲染展示，后台的响应很慢，这样极大的降低了用户的体验；
发现这个问题后马上进行了排查，通过排查发现是由一个查询很慢的 group by 语句导致的；

本文主线：

①、简单描述下排查步骤；

②、对 group by 查询慢进行优化；

简单描述下排查步骤：

排查主要分为了两个步骤：

后台接口的监控，看看哪个方法调用时耗时多
数据库开启慢查询日志，记录执行很慢的SQL

推荐使用阿里开源的Java线上诊断工具 Arthas ，使用其 trace 命令统计方法调用链路上各个方法节点的耗时；

Arthas 工具的具体使用方法可参考：线上服务响应时间太长的排查心路；

通过使用Arthas工具统计到一个进行数据库的 group by查询 方法耗时很严重；

为了进一步确定是这个查询SQL 很耗时，将MySql 的慢查询日志开启了，然后再次调用后台这个接口，发现慢查询日志中确实存在了这个SQL语句；

SQL语句如下：

SELECT
    date_format(createts, '%Y') AS YEAR
FROM
    t_test_log
GROUP BY
    date_format(createts, '%Y')
ORDER BY
    createts DESC

这个SQL语句是用来统计表中所有数据被创建时的年份；
下面就来聊聊这个SQL为什么会比较慢，然后进行了怎样的优化；

对 group by 查询慢进行优化：

在优化group by查询的时候，一般会想到下面这两个名词，通过下面这两种索引扫描可以高效快速的完成group by操作：

松散索引扫描（Loose Index Scan）
紧凑索引扫描（Tight Index Scan）

group by操作在没有合适的索引可用时，通常先扫描整个表提取数据并创建一个临时表，然后按照group by指定的列进行排序；在这个临时表里面，对于每一个group 分组的数据行来说是连续在一起的。

完成排序之后，就可以得到所有的groups 分组，并可以执行聚集函数（aggregate function）。

可以看到，在没有使用索引的时候，需要创建临时表和排序；那在执行计划的 Extra 额外信息中通常就会看到这些信息 Using temporary; Using filesort 出现。

1、首先查看下SQL的执行计划：

得到这个慢查询的SQL后，马上使用 explain 关键字分析其执行计划：

通过查看执行计划发现，这个SQL语句走的是 全表扫描 ，并且通过扫描了大概 99974 行记录后才得到最终的结果集，并且执行过程中使用到了临时表和文件辅助排序；

2、SQL执行计划内容简述：

查看执行计划时，主要看上图中花圈的那三项数据即可：

type：访问类型，这是sql查询优化中一个很重要的指标，结果值从好到坏依次是：

Rows：数据行，根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数；
Extra：额外信息，SQL执行时十分重要的额外信息，简单说几个常会出现的值：
Using filesort ：未利用到索引的默认排序，需要使用文件辅助进行排序，出现其说明SQL性能不好；
Using temporary：使用临时表保存中间结果，常见于 group by ，出现其说明SQL性能不好；
Using index：说明可以直接在索引树上就能得到最终的值，避免了回表，出现其说明SQL性能很好；
Using index for group-by：表示使用了 松散索引扫描 ，出现其说明SQL性能很好；因为松散索引扫描只需要读取很少量的数据就可以完成group by操作，所以执行效率非常高；
select tables optimized away：在没有group by子句的情况下，基于索引优化 MIN/MAX 聚合函数操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化，出现其说明SQL性能达到最优，往往配合 type访问类型的system 出现；

3、建立索引后再查看执行计划：

上面通过查看执行计划得知，因为没有创建相应的索引，所以走的是全表扫描，性能最差；然后对 createts 字段创建索引；再查看其执行计划：

通过查看创建索引后的执行计划发现，此次查询走的 索引全扫描 ，此次虽然从全表扫描优化到了索引全扫描，但是还是需要通过扫描了大概 99974 行记录后才得到最终的结果集，性能并没有提升太多；

并且发现 Extra 信息中还是存在 Using temporary; Using filesort ，说明没有使用到 松散索引扫描或紧凑索引扫描 ；

然后再次分析下SQL语句：

SELECT
    date_format(createts, '%Y') AS YEAR
FROM
    t_test_log
GROUP BY
    date_format(createts, '%Y')
ORDER BY
    createts DESC

发现SQL中对索引字段 createts 做了 date_format 函数运算，所以才导致没使用上松散索引扫描或紧凑索引扫描；然后需要重写下SQL 。

4、通过改写SQL进行优化：

改写后的SQL如下：

SELECT
    date_format(createts, '%Y') AS years
FROM
    (
        SELECT
            createts
        FROM
            t_test_log
        GROUP BY
            createts
    ) t_test_log_1
GROUP BY
    date_format(createts, '%Y')
ORDER BY
    createts DESC

改写完SQL后重新执行，发现查询速度快了非常多，性能上有了质的飞跃；

然后又查看了下它的执行计划如下：

查看上面那个嵌套查询SQL语句的执行计划，子查询部分的通过扫描大概52行记录就能得到结果集，相比于一开始需要扫描 99974 行记录才能得到结果集，这个性能快了太多了；并且子查询的 Extra 信息中出现了 Using index for group-by ，说明使用到了松散索引扫描，效率才提升了这么多；

外查询对子查询（52行记录）的结果集再次进行分组排序，此时采用的是全表（全结果集）的查询， 如果结果集很大的话，效率不会很高 ；

所以，在使用此优化方案的SQL语句时，需要统计下子查询的结果集的大小，如果子查询结果集很大的话，就不建议使用此方案了，可以尝试使用下面的这种优化方案；

5、通过改写SQL + 改写代码进行优化：

上面优化方案，只需改写SQL即可，无需对代码进行修改；本优化方案既要改写SQL，还要进行代码的修改；

改写后的SQL如下：这个SQL是查询出表中最小年份和最大年份

(
    SELECT
        date_format(createts, '%Y') AS years
    FROM
        t_test_log
    ORDER BY
        createts
    LIMIT 1
)
UNION ALL
    (
        SELECT
            date_format(createts, '%Y') AS years
        FROM
            t_test_log
        ORDER BY
            createts DESC
        LIMIT 1
    )

查看下上面这个SQL语句的执行计划：

上面这个SQL是利用索引的默认排序，直接获取排序后的第一条记录，只需要扫描一行记录（rows ：1）就能获取到最终的结果集；所以此SQL的性能是非常好的。

但是需要记住，这个SQL查询出的结果集不是最终需要的数据，需要 写代码 计算出最终的结果集：

得到的最大最小年份这两个值一样：说明表中的数据都是属于一个年份的
得到的最大最小年份这两个值不一样：
两个值相减得一：说明年份是挨着的两个年份，可以直接将结果集返回；
两个值相减大于一：说明最小年份和最大年份之间还存在年份，通过计算得出中间年份

但是注意，通过写代码计算出最终的年份，这种方式还是存在一个问题的，那就是确实表中根本没有中间年份的数据，但是通过计算却得出了；

举例说明：假如通过SQL查询出了最小年份和最大年份是2018和2021，那么再通过代码计算出中间年份2019和2020，但是表中数据根本就不存在2019年份的数据，这是就会出现问题了；

所以这种方案也需要根据自己具体的业务场景和实际的数据情况等分析是否需要采用。

扩展：

在通过 改写SQL + 改写代码 进行优化时，改写的SQL不止上面那一种，还有一种查询效率也比较高的改写SQL；

就是使用 min、max 聚合函数进行改写SQL，但是在使用聚合函数时，可以写出下面两种样式的SQL，到底哪种改写SQL效率是比较高呢，留个悬念，大家可以自行去分析尝试下哟！ 可以在评论区留下你的答案呀！

第一种改写SQL方式：

(
    SELECT
        min(date_format(createts, '%Y')) AS years
    FROM
        t_test_log
)
UNION ALL
  (
        SELECT
            max(date_format(createts, '%Y')) AS years
        FROM
            t_test_log
   )

第二种改写SQL方式：

(
    SELECT
        date_format(minyear, '%Y') AS years
    FROM
        (
            SELECT
                min(createts) AS minyear
            FROM
                t_test_log
        ) t_test_log_1
)
UNION ALL
   (
        SELECT
            date_format(maxyear, '%Y') AS years
        FROM
            (
                SELECT
                    max(createts) AS maxyear
                FROM
                    t_test_log
            ) t_test_log_2
   )