前言：

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

发现这个问题后马上进行了排查 ，通过排查发现是由一个查问很慢的 group by 语句导致的；

本文主线：

①、简略形容下排查步骤；

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

简略形容下排查步骤：

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

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

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

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

通过应用Arthas工具统计到一个进行数据库的 group by查问 办法耗时很重大；

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

SQL语句如下：

SELECT    date_format(createts, '%Y') AS YEARFROM    t_test_logGROUP 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 YEARFROM    t_test_logGROUP BY    date_format(createts, '%Y')ORDER BY    createts DESC

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

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

改写后的SQL如下：

SELECT    date_format(createts, '%Y') AS yearsFROM    (        SELECT            createts        FROM            t_test_log        GROUP BY            createts    ) t_test_log_1GROUP 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学习干货文章，您能够来瞧一瞧哟！