Alternative Query Tuning：百万级JOIN_DATA表慢查询调优求助

存在的性能问题 #

• 对百万级的JOIN_DATA表执行了2次全表扫描，额外增加了大量IO开销
• 对数值类型的BAL字段执行了多余的TRIM()字符串处理操作，每一行都要做类型转换，不仅计算耗时还无法命中索引，严重拖慢聚合效率
• SUM_VALUE CTE已经按INS分组，返回的INS天然唯一，后续IN子句中多余的DISTINCT增加了不必要的计算成本
• GET_KEY CTE已经按KEY分组，返回的KEY天然唯一，最终查询多余的DISTINCT额外增加了去重开销

优化后查询 #

WITH SUM_VALUE AS (
  SELECT INS
  FROM JOIN_DATA
  GROUP BY INS 
  HAVING SUM(BAL) <> 0
),
GET_KEY AS (
  SELECT KEY
  FROM JOIN_DATA
  WHERE INS IN (SELECT INS FROM SUM_VALUE)
  GROUP BY KEY
  HAVING SUM(BAL) <> 0
)
SELECT KEY FROM GET_KEY

额外优化建议 #

如果需要进一步提升性能，可以为JOIN_DATA表创建两个联合索引：

• 覆盖第一个聚合逻辑的索引：(INS, BAL)
• 覆盖第二个过滤+聚合逻辑的索引：(KEY, INS, BAL)
  两个索引可以让两次查询都直接走索引覆盖扫描，无需回表读取整行数据，性能会有大幅提升。

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