关于sql优化:一次对group-by时间导致的慢查询的优化

42次阅读

共计 3847 个字符，预计需要花费 10 分钟才能阅读完成。

前言：

最近在测试环境中点击一个图表展现页面时，半天才失去后盾响应的数据进行页面渲染展现，后盾的响应很慢，这样极大的升高了用户的体验；

发现这个问题后马上进行了排查，通过排查发现是由一个查问很慢的 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
   )

❤ 点赞 + 评论 + 转发 哟

如果本文对您有帮忙的话，请挥动下您爱发财的小手点下赞呀，您的反对就是我一直创作的能源，谢谢啦！

您能够微信搜寻【木子雷】公众号，大量 Java 学习干货文章，您能够来瞧一瞧哟！

正文完
sql优化
发表至： sql优化
2021-01-04
 0
关于sql优化:数据量很大分页查询很慢有什么优化方案
关于sql优化:第22期索引设计组合索引适用场景
关于sql优化:第39期MySQL-时间类分区写-SQL-注意事项
关于sql优化:第23期索引设计组合索引不适用场景改造