带DISTINCT与多LEFT JOIN的SQL查询性能优化方案咨询

方案1：移除所有LEFT JOIN，改用标量子查询实现相同逻辑 #

原来的查询多次LEFT JOIN同一张codes表，很容易产生冗余行，额外引入了DISTINCT去重的开销，改写后完全移除JOIN逻辑，同时可以去掉DISTINCT，性能提升最明显。
改写后的SQL如下：

SELECT ID, ACCOUNT,
   CASE
       WHEN p.GeneralLevel = '1' THEN '1'
       WHEN p.Level3 IS NULL THEN '2'
       WHEN p.Level4 IS NULL THEN '3'
       WHEN p.Level5 IS NULL THEN '4'
       WHEN p.Level6 IS NULL THEN '5'
       WHEN p.Level7 IS NULL THEN '6'
       WHEN p.Level8 IS NULL THEN '7'
       ELSE '8'
   END AS LEVEL,
   COALESCE((SELECT codeValueDescription FROM codes WHERE code = '2' AND codeValue = p.Level2 LIMIT 1), p.Level2) AS L2_CODE,
   COALESCE((SELECT codeValueDescription FROM codes WHERE code = '3' AND codeValue = p.Level3 LIMIT 1), p.Level3) AS L3_CODE,
   COALESCE((SELECT codeValueDescription FROM codes WHERE code = '4' AND codeValue = p.Level4 LIMIT 1), p.Level4) AS L4_CODE,
   COALESCE((SELECT codeValueDescription FROM codes WHERE code = '5' AND codeValue = p.Level5 LIMIT 1), p.Level5) AS L5_CODE,
   COALESCE((SELECT codeValueDescription FROM codes WHERE code = '3' AND codeValue = p.Level6 LIMIT 1), p.Level6) AS L6_CODE,
   COALESCE((SELECT codeValueDescription FROM codes WHERE code = '3' AND codeValue = p.Level7 LIMIT 1), p.Level7) AS L7_CODE,
   p.Level8
FROM generic p

• 逻辑完全和原查询一致：COALESCE对应原来CASE的判空逻辑，子查询和原LEFT JOIN的过滤条件完全匹配
• 移除了多表JOIN产生的笛卡尔积冗余行，因此可以直接删掉DISTINCT，省去了结果集排序去重的开销

方案2：添加覆盖索引进一步提速 #

虽然无法查看执行计划，但可以给相关表添加适配查询的索引，大幅降低查询开销：

• 给codes表创建联合覆盖索引：(code, codeValue) INCLUDE (codeValueDescription)，如果桥接层不支持INCLUDE语法，直接创建(code, codeValue, codeValueDescription)联合索引即可，子查询可以直接命中索引返回结果，不需要回表查数据
• 如果generic表查询时有额外WHERE过滤条件，给过滤条件涉及的字段创建索引

其他可选优化方向 #

• 如果codes表的数据量很小、更新频率很低，可以考虑把code对应codeValue到codeValueDescription的映射关系提前缓存在应用层，查询完generic表的数据后直接在内存中替换描述，完全省去数据库层面的关联查询开销
• 确认generic表的字段是否存在冗余存储空间，比如可以提前把LEVEL字段计算好预存，省去查询时的CASE判断开销

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