更换关联列后SQL查询变慢的原因排查请求

问题背景 #

执行以下查询时耗时约1.5小时，但将关联列从ACC_ID切换为ULT_ID后，查询耗时超过5小时。已知ACC_ID和ULT_ID的唯一值数量大致相同，需排查基础层面的遗漏点。

原查询（耗时1.5小时）

SELECT  DISTINCT
  op."Acc_ID"
, op."Oppty_ID"
, op."Prod1op"
, op."Prod2op"
, CASE WHEN ac1."Acc_ID" IS NOT NULL AND  ac2."Acc_ID" IS NOT NULL AND ac3."Acc_ID" IS NOT NULL
       THEN  'Match @ ALL Levels'
       WHEN ac1."Acc_ID" IS NOT NULL AND  ac2."Acc_ID" IS NOT NULL 
       THEN  'Match @ ACC_ID, Prod1 Levels'
       WHEN ac1."Acc_ID" IS NOT NULL
       THEN 'Match @ ACC_ID Level'
       ELSE 'No Match @ ACC_ID Level' END CF       
FROM Oppty op
LEFT JOIN Acc ac1 ON op."Acc_ID" = ac1."Acc_ID"
LEFT JOIN Acc ac2 ON op."Acc_ID" = ac2."Acc_ID"
                   AND ac2."Prod1acc" = op."Prod1op"
LEFT JOIN Acc ac3   ON op."Acc_ID" = ac3."Acc_ID"
                   AND ac3."Prod1acc" = op."Prod1op"
                   AND ac3."Prod2acc" = op."Prod2op" 
  ORDER BY op."Acc_ID", op."Prod1op"

耗时更长的查询（超5小时）

SELECT  DISTINCT
  op."ULT_ID"
, op."Oppty_ID"
, op."Prod1op"
, op."Prod2op"
, CASE WHEN ac1."ULT_ID" IS NOT NULL AND  ac2."ULT_ID" IS NOT NULL AND ac3."ULT_ID" IS NOT NULL
       THEN  'Match @ ALL Levels'
       WHEN ac1."ULT_ID" IS NOT NULL AND  ac2."ULT_ID" IS NOT NULL 
       THEN  'Match @ ULT_ID, Prod1 Levels'
       WHEN ac1."ULT_ID" IS NOT NULL
       THEN 'Match @ ULT_ID Level'
       ELSE 'No Match @ ULT_ID Level' END CF       
FROM Oppty op
LEFT JOIN Acc ac1 ON op."ULT_ID" = ac1."ULT_ID"
LEFT JOIN Acc ac2 ON op."ULT_ID" = ac2."ULT_ID"
                   AND ac2."Prod1acc" = op."Prod1op"
LEFT JOIN Acc ac3   ON op."ULT_ID" = ac3."ULT_ID"
                   AND ac3."Prod1acc" = op."Prod1op"
                   AND ac3."Prod2acc" = op."Prod2op" 
  ORDER BY op."ULT_ID", op."Prod1op"

基础层面排查点 #

• 索引有效性检查

  • 确认Acc表上是否存在ULT_ID的单独索引，或者包含关联条件（如ULT_ID + Prod1acc、ULT_ID + Prod1acc + Prod2acc）的复合索引。原查询用ACC_ID时，大概率Acc表有ACC_ID的主键索引或高效复合索引，而ULT_ID索引缺失/不合理会导致关联时全表扫描或低效扫描。
  • 检查Oppty表的ULT_ID是否有索引，排序阶段依赖该列，无索引会大幅增加排序耗时。

• 数据分布与关联基数

  • 唯一值数量相同不代表每个值对应的行数一致。统计Acc表中每个ULT_ID和ACC_ID的平均行数，看是否存在ULT_ID对应大量行的情况：

    -- 查看ULT_ID对应的行数分布
    SELECT "ULT_ID", COUNT(*) FROM Acc GROUP BY "ULT_ID" ORDER BY COUNT(*) DESC LIMIT 10;
    -- 查看ACC_ID对应的行数分布
    SELECT "ACC_ID", COUNT(*) FROM Acc GROUP BY "ACC_ID" ORDER BY COUNT(*) DESC LIMIT 10;
    

  • 若Oppty表中ULT_ID重复率远高于ACC_ID，关联后中间结果集会急剧膨胀，后续DISTINCT和排序的压力也会陡增。

• 执行计划对比

  • 分别查看两个查询的执行计划，重点关注：
    • 关联算法（嵌套循环/哈希连接/合并连接）的选择
    • Acc表的访问方式（索引扫描/全表扫描）
    • 中间结果集的预估行数与实际行数是否偏差过大（统计信息过时会导致优化器决策错误）

• 统计信息更新

  • 若数据库统计信息过时，优化器会基于旧数据生成低效执行计划。手动更新Acc和Oppty表的统计信息后重新测试：
    • PostgreSQL：ANALYZE Acc; ANALYZE Oppty;
    • MySQL：ANALYZE TABLE Acc, Oppty;
    • SQL Server：UPDATE STATISTICS Acc; UPDATE STATISTICS Oppty;

• DISTINCT的必要性

  • 去掉DISTINCT后测试耗时，如果耗时大幅降低，说明ULT_ID关联生成了大量重复行。此时可以考虑用GROUP BY替代DISTINCT，或者优化关联逻辑减少重复行的产生（比如先在Acc表按ULT_ID+关联条件聚合，再与Oppty关联）。

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