【2021-08-11】记一次sql优化的过程

场景

当上游系统大批量进行发放交易的时候时候,会短时间内集中调用系统的正交易接口,产生百万级交易数据.系统处理交易数据的时候为了保证数据库访问性能,交易的部分业务采用了异步处理的方式,由定时任务定期去通过SQL捞取数据,SQL在该场景下出现性能问题.

问题

超出系统定时任务短期处理能力,导致系统捞取待处理的交易信息的sql扫描数据过多,执行效率低下.

• 慢sql原因分析
  1. 因为数据堆积,导致sql执行时扫描行数过多.
  2. 查询条件粗泛,索引选择性不高.
  3. 因为业务需要,很难增加新的查询条件来限制查询范围和使用选择性更高的索引.

解决方案

• 痛点
  因为查询条件部分受到了业务上的限制,无法进行添加更多的更细致的条件缩小查询范围;
  t.STATUS字段是该查询中唯一指定的有效条件,虽然有索引但是由于该字段在实际业务中只有两个状态,导致索引选择性不高,其余业务场景下为偏向索引,未处理状态的数据少不会有慢SQL问题,但是在批量发放交易场景下,已处理状态的数据少,未处理状态的数据多,导致该索引并不能有效约束数据的查询范围;
  limit语句虽然限制了返回结果集的大小,但是并没有对SQL执行时查询的范围做出约束.

• 增加新的定时任务
  针对批充场景的特殊性,增加一个新的定时任务专门处理因批充业务导致激增的交易数据.
  查询未累加的交易数据分两步进行:先查询最新的交易数据的ID,然后根据当前定时任务的分片数,单片单批次定时任务处理的数据数量计算该次定时任务处理的总的数据量(分片数 * 批次处理数量),通过这两个值计算出该次定时任务处理的数据范围区间,提代limit语句指定sql扫描数据范围,再查询待累加的交易数据,提高性能.

• 原定时任务
  原定时任务逻辑不动,兜底.

• SQL优化方案

  1. 原S