MySQL百GB大表自关联查询超15分钟无结果 慢查询优化求助

问题基础信息 #

• 故障现象：逻辑预期执行较快的查询，实际运行超过15分钟无法返回结果，需手动终止
• 涉及表规模：
  • b_codes（别名b）：表大小14MB，属于小表
  • pass（别名p、p2，查询中做自关联）：表大小106GB，为核心大表
• 已尝试的无效优化操作：
  • 增加过滤条件缩小数据扫描范围
  • 为p表id字段、p2表查询涉及的所有字段分别创建单列索引
  • 将日期范围条件改写为BETWEEN语法
• 原始查询SQL：

SELECT b.code ,p2.result
    FROM b_codes b
    INNER JOIN pass p ON (b.id = p.id)
    INNER JOIN pass p2 ON (b.s = p2.s AND b.tn = p2.tn AND p.t_ta = p2.t_ta AND p2.s = (p.s+1))
    WHERE b.s <> ''
    AND b.id <> 0
    AND p2.date >= '2022-04-24 00:00:00' 
    AND p2.date <= '2022-06-05 23:59:59'
    AND b.j = 'Completed'
    AND b.tn IN ('538')
    GROUP BY b.code, b.id

慢查询核心根因 #

• 索引设计缺陷：p2表仅创建单列索引，无法支撑多字段等值关联+时间范围过滤的查询场景。MySQL使用单列索引时需要做索引合并，且查询字段不在索引中会产生大量回表随机IO，在106GB的表规模下，IO开销会被放大数个量级，是最核心的性能瓶颈。
• 执行逻辑冗余：GROUP BY的字段全部来自小表b，原SQL在完成p、p2两张大表的关联、产生体量巨大的中间结果集后才做分组去重，额外产生了大量不必要的计算、临时表写入和排序开销。
• 关联效率低下：自关联条件p2.s = p.s + 1没有被索引覆盖，关联时需要逐行做计算匹配，无法通过索引快速定位关联行；同时优化器无法提前识别p2表的过滤条件，导致大表关联时匹配的数据集规模失控。
• 语法不规范：原SQL在开启ONLY_FULL_GROUP_BY的MySQL环境下会直接报错，SELECT列表中的p2.result既不在GROUP BY字段中，也没有使用聚合函数，可能返回不符合业务预期的结果。

可落地优化方案 #

1. 为大表创建联合覆盖索引，消除回表开销 #

针对p2表的查询逻辑创建联合覆盖索引，将等值匹配字段放在索引最前列，范围过滤字段放在中间，查询需要返回的字段放在索引末尾，查询时直接从索引获取所有需要的数据，不需要回表访问主键数据，IO开销可降低90%以上，建索引语句如下：

CREATE INDEX idx_pass_p2 ON pass (tn, s, t_ta, date, result);

同时为p表的关联逻辑创建对应覆盖索引，避免p表关联时的回表开销：

CREATE INDEX idx_pass_p ON pass (id, t_ta, s);

2. 改写SQL逻辑，提前裁剪数据量 #

将小表b的过滤、去重逻辑提前执行，避免大表关联产生不必要的中间结果，改写后SQL如下：

SELECT 
  b_dedup.code, 
  -- 若单个(code,id)对应多个result，可根据业务需求替换为MAX/MIN/ANY_VALUE等聚合逻辑
  ANY_VALUE(p2.result) AS result
FROM (
  -- 先在14MB的小表上完成所有过滤、去重逻辑，大幅缩小后续关联的数据集
  SELECT DISTINCT b.code, b.id, b.s, b.tn
  FROM b_codes b
  WHERE b.s <> ''
    AND b.id <> 0
    AND b.j = 'Completed'
    AND b.tn = '538'
) b_dedup
INNER JOIN pass p 
  ON b_dedup.id = p.id
INNER JOIN pass p2 
  ON b_dedup.s = p2.s 
  AND b_dedup.tn = p2.tn 
  AND p.t_ta = p2.t_ta 
  AND p2.s = p.s + 1
  -- 将p2的时间过滤条件直接放在JOIN逻辑中，帮助优化器提前裁剪数据
  AND p2.date BETWEEN '2022-04-24 00:00:00' AND '2022-06-05 23:59:59';

改写点说明：

• 原SQL的GROUP BY b.code, b.id逻辑提前到b表子查询中用DISTINCT实现，不需要等关联大表后再做分组
• 单值匹配条件b.tn IN ('538')改为等值匹配，帮助优化器做更准确的执行成本估算
• 补充ANY_VALUE函数处理非分组字段，兼容ONLY_FULL_GROUP_BY语法要求，避免结果不符合预期

3. 可选进阶优化 #

如果pass表持续增长，可对pass表按date字段做范围分区，查询时优化器会自动裁剪掉时间范围外的分区，不需要扫描全量索引，进一步降低查询开销。

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