MySQL调优笔记——慢SQL优化记录(2)

        今天调优的原因是，有一个统计报表业务，查询的时间太慢；同时由于数据库的压力是随机性的，这个业务的执行下限和上限相差近20倍；快的时候可以达到600ms，慢的时候有9秒之多；

        接下来详细介绍：这是一个报表业务，按照设备ID统计设备在一段时间内的异常（业务逻辑判断）订单总数；他的数据生成是定时任务每小时查询订单表，并按照一定的业务逻辑判断为异常订单统计单个设备在这段时间范围内异常订单总数的；

        SQL大致是这样的：

    SELECT t1.gun_id,sum(total_count) total_countFROM order_exception t1 where is_deleted = 0 and start_date > '2023-04-01 00:00:00' and start_date < '2023-04-07 23:59:59' group by gun_id order by concat(pile_sn,gun_id) asc

        我们的前端报表是每页10行，分页查询；

        这里就出现了第一个优化点，由于我们使用的是mybatis-plus框架，在使用自动分页功能查询数据的时候，他会自动在外面套上查询总数的语句，以计算出一共有多少页；

        像这样：

SELECT COUNT(1) NUM from (你的业务SQL) 

        这样做的话，会导致这个统计语句被执行两次，在数据量大的情况下就很慢；

        我们初步优化一下，由于每页仅可查看10条数据，但是这个聚合的汇总统计语句计算出的是该段时间内所有设备的总异常订单数量，因此我们需要缩小查询的范围：

        1. 使用DISTINCT语句，计算出该统计表中符合查询条件的设备ID，因为在这个业务中每个设备就是一行数据，所以这里查询到的设备数就是该业务报表的总行数；和原SQL的分页逻辑一致；

        2. 在经过一个查询设备ID的分页SQL之后，可以得到需要在该页展示的设备ID，因此使用IN语句去查询这些设备的异常订单数，查询的范围就大大降低了，效率提升明显，不过这里一定别忘了加上设备id的这个索引；修改之后像下面这样子：

SELECT t1.gun_id,sum(total_count) total_count
FROM order_exception t1 where is_deleted = 0 and start_date > '2023-04-01 00:00:00' and start_date < '2023-04-07 23:59:59' and gun_id in 
(
1367069347104690178,
1367069418131034114,
1367069451580608514,
1368462285101600770,
1368462416299429889,
1368462497148833794
)

        这样修改后，查询时间从5秒降低到了0.5秒，这其中还包括查询结果传输到客户端的时间；

修改前后的EXPLAIN执行计划对比：

 很明显扫描的行数rows字段变少了很多；

        最后，由于该表的数据量总是会随着业务量的增长和时间的推移积累越来越多，于是我找产品经理商量了一下，确认了不需要小时频率更新的需求，于是就就将定时任务的执行频率改为每日执行一次；

        经过上面两个步骤的操作之后，SQL的查询范围大大减少，然后该表的数据量也是减少了很多，这样整体的报表业务查询速度就提高了很多；这次的优化主要是两个方面，SQL逻辑的优化和业务逻辑的优化，其实第一点还是相对容易想到的，但是第二点一般的开发同学估计不会去主动沟通，简单记录一下