前言：
最近在测试环境下点击图表展示页面时，半天后得到后台响应的数据进行页面呈现展示，后台响应缓慢，大大降低了用户体验；
发现此问题后立即进行故障诊断，并发现故障诊断是由查询较慢的group by语句引起的
正文主线：
、简要说明故障诊断步骤；
优化group by查询的延迟
简要说明故障诊断的步骤。
故障诊断主要分为两个步骤。
监视后台接口，查看哪些方法调用需要时间
数据库打开速度较慢的查询日志，并记录运行速度较慢的SQL
建议使用蚂蚁开源的Java在线诊断工具Arthas，使用其trace命令统计方法，然后调用链路上的每个方法节点需要很长时间；
Arthas工具的具体用法是在线服务响应时间过长的故障诊断
使用Arthas工具统计数据库group by查询方法需要的时间
为了进一步验证此查询SQL是否需要时间，打开MySql的抛出查询日志，然后再次调用名为后台的接口，发现此SQL语句存在于抛出查询日志中。
SQL语句如下：
选择
date_format(createts，' %Y ' ) AS YEAR
来自
t_test_log
GROUP BY
date _ format (创建ts，' %Y ' ) )。
订单依据
创建台
此SQL语句是统计表中所有数据的创建年份。
谈谈这个SQL为什么会变慢，以及进行了什么样的优化；
要优化group by查询速度：
优化group by查询时，通常会想到以下两个名词： 可以通过以下两个索引扫描高效快速地执行group by操作：
松散索引扫描(Loose Index Scan ) ) )。
精简索引扫描(Tight Index Scan ) ) ) ) ) )。
如果没有适当的索引，group by操作通常会扫描整个表、提取数据、创建临时表，然后按group by指定的列进行排序。 在此临时表中，组组中的每个数据行都是连续的。
排序完成后，可以检索所有组并运行合并函数。
如果没有使用索引，您会发现需要创建临时表和排序； 这些信息Using temporary通常显示在执行计划的Extra的其他信息中； 用户文件出现。
1、首先查看下一个SQL执行计划：
获得此慢速查询的SQL后，立即使用explain关键字分析执行计划。
从执行计划中可以看到，此SQL语句正在进行全表扫描，扫描了大约99974行的记录，然后得到了最终的结果集，在执行过程中使用了临时表和文件辅助排序。
2、SQL执行计划内容简介：
看计划的时候，主要看上图花环的三个数据就可以了。
类型：访问类型。 这是优化sql查询的重要指标之一，结果的值从好到低依次为：
Rows )根据数据行、表统计信息和索引选择情况，估算查找所需记录所需读取的行数；
Extra :简要说明其他信息、对运行SQL非常重要的其他信息以及一些常用的值。
Using filesort :未用于索引的默认排序，需要使用文件辅助进行排序，表明SQL性能较差；
Using temporary :用临时表保存中间结果。 group by常见，说明SQL性能差；
Using index :说明索引树可以直接得到最终值，避免返回表，表明SQL性能良好
Using index for group-by :表示正在使用松散索引扫描，表示SQL性能良好； 稀疏索引扫描只需读取少量数据就可以完成group by操作，因此执行效率非常高；
如果没有selecttablesoptimizedaway:group by子句，则根据索引优化MIN/MAX聚合函数的操作。 不需要等到计算出执行阶段。 在生成查询执行计划的阶段，优化已完成，SQL性能得到了优化，这表明它们通常与类型访问类型的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学习干货文章，您可以来瞧一瞧哟！