AWS Aurora MySQL添加SUM后原本快速的查询严重变慢如何解决

你现有字段顺序为(paid_at, kind, sub_kind)的联合索引对第一个查询属于覆盖索引：查询需要的所有字段（paid_at、kind、sub_kind）都存储在索引的叶子节点中，不需要回表访问主键聚簇索引，因此Extra显示Using where; Using index，性能极高。

第二个查询新增sum(iugu_fee_cents)聚合逻辑后，iugu_fee_cents字段不在现有联合索引中，虽然仍能命中索引过滤paid_at条件，但需要对每一条匹配的记录回表到聚簇索引查询iugu_fee_cents的值做聚合计算。Extra显示的Using index condition是ICP（索引条件下推）优化开启的标识，本质只是避免了不必要的回表，还是需要对所有符合paid_at过滤条件的记录执行回表操作，数据量稍大就会产生大量随机IO，直接导致性能暴跌。

• 最优方案：修改联合索引为覆盖索引
  将iugu_fee_cents追加到现有联合索引的末尾，新的索引字段顺序调整为(paid_at, kind, sub_kind, iugu_fee_cents)。调整后第二个查询需要的所有字段都可以直接从索引中获取，不需要回表，Extra会恢复为Using where; Using index，性能和第一个查询基本持平。
• 备选方案1：按时间字段做表分区
  如果业务侧存在约束无法随意修改索引，可以对revenues表按paid_at做RANGE分区，过滤paid_at = '2021-11-17'时只会扫描对应日期的单分区数据，大幅缩小回表的扫描范围，降低IO开销。
• 备选方案2：新增预聚合汇总表
  如果业务对查询的实时性要求不高，可以新增按天维度统计的预聚合汇总表，提前通过定时任务或者触发器计算好每天每个kind、sub_kind对应的总条数、iugu_fee_cents总和，查询时直接访问汇总表即可，响应时间可控制在毫秒级。
• 临时优化方案：调整缓存配置
  短期不想修改表结构的话，可以调大innodb_buffer_pool_size参数，让更多聚簇索引页缓存到内存中，减少回表时的磁盘随机IO开销，也能一定程度提升查询速度。

内容的提问来源于stack exchange，提问作者Marcovecchio