WHERE子句使用OR导致查询性能低下，如何优化改写SQL？

针对你遇到的多表OR条件组合导致查询变慢的问题，这里提供几个实用的优化思路，完全基于你提到的「单个条件查询仅耗时30ms」这个前提设计：

方案一：用UNION ALL拆分独立查询 #

既然单个条件的查询能高效利用索引，直接把三个条件拆成三个独立查询，用UNION ALL合并结果。这样数据库可以为每个子查询生成最优执行计划，避免OR条件导致的索引失效或执行计划混乱。

注：如果三个子查询结果可能存在重复且业务不允许重复数据，就用UNION代替UNION ALL——但UNION会有去重开销，优先选择UNION ALL。

改写后的SQL示例：

-- 场景1：ba表自身满足条件
SELECT pcl.COL1,
       pcl.COL2,
       pcl.COL3,
       pcl.COL4,
       (SELECT cc.COL5
          FROM T3 cc
          JOIN T4 cvp
            ON cvp.COL10 = cc.COL10
           AND cvp.COL2 = pcl.COL2) AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl
  ON pcl.COL1 = ba.COL1
 AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
JOIN T5 pscl
  ON pscl.COL2 = pcl.COL2
 AND pscl.COL4 <> 3
 AND (pscl.COL6 = ba.COL6 OR ba.COL6 = 'ALL')
LEFT JOIN T6 bba
  ON bba.COL7 = ba.COL7
LEFT JOIN T7 bsa
  ON bsa.COL7 = ba.COL7
LEFT JOIN T8 pp
  ON pp.COL1 = pcl.COL1
WHERE ba.COL8 = 617617

UNION ALL

-- 场景2：ba不满足，但bsa关联满足条件
SELECT pcl.COL1,
       pcl.COL2,
       pcl.COL3,
       pcl.COL4,
       (SELECT cc.COL5
          FROM T3 cc
          JOIN T4 cvp
            ON cvp.COL10 = cc.COL10
           AND cvp.COL2 = pcl.COL2) AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl
  ON pcl.COL1 = ba.COL1
 AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
JOIN T5 pscl
  ON pscl.COL2 = pcl.COL2
 AND pscl.COL4 <> 3
 AND (pscl.COL6 = ba.COL6 OR ba.COL6 = 'ALL')
LEFT JOIN T6 bba
  ON bba.COL7 = ba.COL7
INNER JOIN T7 bsa  -- 此处改为INNER JOIN，因为WHERE条件会过滤掉NULL行
  ON bsa.COL7 = ba.COL7
LEFT JOIN T8 pp
  ON pp.COL1 = pcl.COL1
WHERE ba.COL8 != 617617
  AND bsa.COL8 = 617617

UNION ALL

-- 场景3：ba和bsa都不满足，但bba关联满足条件
SELECT pcl.COL1,
       pcl.COL2,
       pcl.COL3,
       pcl.COL4,
       (SELECT cc.COL5
          FROM T3 cc
          JOIN T4 cvp
            ON cvp.COL10 = cc.COL10
           AND cvp.COL2 = pcl.COL2) AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl
  ON pcl.COL1 = ba.COL1
 AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
JOIN T5 pscl
  ON pscl.COL2 = pcl.COL2
 AND pscl.COL4 <> 3
 AND (pscl.COL6 = ba.COL6 OR ba.COL6 = 'ALL')
INNER JOIN T6 bba  -- 此处改为INNER JOIN，同理过滤NULL行
  ON bba.COL7 = ba.COL7
LEFT JOIN T7 bsa
  ON bsa.COL7 = ba.COL7
LEFT JOIN T8 pp
  ON pp.COL1 = pcl.COL1
WHERE ba.COL8 != 617617
  AND (bsa.COL8 != 617617 OR bsa.COL8 IS NULL)
  AND bba.COL9 = 617617

方案二：将WHERE条件移至JOIN子句，优化执行计划 #

原查询中，LEFT JOIN bsa后在WHERE里用bsa.COL8=617617，本质上已经把LEFT JOIN变成了INNER JOIN（因为LEFT JOIN产生的NULL行会被WHERE条件过滤）。同理bba也是如此。把过滤条件移到JOIN子句里，让数据库更早过滤数据，同时避免OR条件的影响：

-- 场景1：ba满足条件
SELECT pcl.COL1, pcl.COL2, pcl.COL3, pcl.COL4,
       (SELECT cc.COL5 FROM T3 cc JOIN T4 cvp ON cvp.COL10=cc.COL10 AND cvp.COL2=pcl.COL2) AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl ON pcl.COL1=ba.COL1 AND (pcl.COL2=ba.COL2 OR ba.COL2=0)
JOIN T5 pscl ON pscl.COL2=pcl.COL2 AND pscl.COL4<>3 AND (pscl.COL6=ba.COL6 OR ba.COL6='ALL')
LEFT JOIN T6 bba ON bba.COL7=ba.COL7
LEFT JOIN T7 bsa ON bsa.COL7=ba.COL7
LEFT JOIN T8 pp ON pp.COL1=pcl.COL1
WHERE ba.COL8=617617

UNION ALL

-- 场景2：通过bsa关联满足条件（ba本身不满足）
SELECT pcl.COL1, pcl.COL2, pcl.COL3, pcl.COL4,
       (SELECT cc.COL5 FROM T3 cc JOIN T4 cvp ON cvp.COL10=cc.COL10 AND cvp.COL2=pcl.COL2) AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl ON pcl.COL1=ba.COL1 AND (pcl.COL2=ba.COL2 OR ba.COL2=0)
JOIN T5 pscl ON pscl.COL2=pcl.COL2 AND pscl.COL4<>3 AND (pscl.COL6=ba.COL6 OR ba.COL6='ALL')
LEFT JOIN T6 bba ON bba.COL7=ba.COL7
JOIN T7 bsa ON bsa.COL7=ba.COL7 AND bsa.COL8=617617  -- 直接在JOIN时过滤
LEFT JOIN T8 pp ON pp.COL1=pcl.COL1
WHERE ba.COL8!=617617

UNION ALL

-- 场景3：通过bba关联满足条件（ba和bsa都不满足）
SELECT pcl.COL1, pcl.COL2, pcl.COL3, pcl.COL4,
       (SELECT cc.COL5 FROM T3 cc JOIN T4 cvp ON cvp.COL10=cc.COL10 AND cvp.COL2=pcl.COL2) AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl ON pcl.COL1=ba.COL1 AND (pcl.COL2=ba.COL2 OR ba.COL2=0)
JOIN T5 pscl ON pscl.COL2=pcl.COL2 AND pscl.COL4<>3 AND (pscl.COL6=ba.COL6 OR ba.COL6='ALL')
JOIN T6 bba ON bba.COL7=ba.COL7 AND bba.COL9=617617  -- 直接在JOIN时过滤
LEFT JOIN T7 bsa ON bsa.COL7=ba.COL7
LEFT JOIN T8 pp ON pp.COL1=pcl.COL1
WHERE ba.COL8!=617617
  AND (bsa.COL8!=617617 OR bsa.COL8 IS NULL)

方案三：优化索引结构 #

虽然你提到COL8和COL9已有索引，但可以检查是否需要联合索引来进一步提升效率：

• 对T7(bsa)：创建(COL7, COL8)联合索引——查询先通过COL7关联ba，再过滤COL8，联合索引能覆盖关联+过滤需求，避免回表。
• 对T6(bba)：创建(COL7, COL9)联合索引，同理提升JOIN+过滤的效率。
• 对T1(ba)：如果COL8是单独索引，可考虑和JOIN用到的COL1、COL2、COL6组合成覆盖索引，减少磁盘IO。

额外优化：替换相关子查询为JOIN #

原查询中的相关子查询会对pcl的每一行执行一次，改成JOIN方式可以让子查询仅执行一次，节省开销：

SELECT pcl.COL1,
       pcl.COL2,
       pcl.COL3,
       pcl.COL4,
       cc_cvp.COL5 AS COL10,
       pp.COL11
FROM T1 ba
JOIN T2 pcl ON pcl.COL1=ba.COL1 AND (pcl.COL2=ba.COL2 OR ba.COL2=0)
JOIN T5 pscl ON pscl.COL2=pcl.COL2 AND pscl.COL4<>3 AND (pscl.COL6=ba.COL6 OR ba.COL6='ALL')
LEFT JOIN (
    SELECT cvp.COL2, cc.COL5
    FROM T3 cc
    JOIN T4 cvp ON cvp.COL10=cc.COL10
) cc_cvp ON cc_cvp.COL2=pcl.COL2  -- 用JOIN替代相关子查询
LEFT JOIN T6 bba ON bba.COL7=ba.COL7
LEFT JOIN T7 bsa ON bsa.COL7=ba.COL7
LEFT JOIN T8 pp ON pp.COL1=pcl.COL1
-- 后续WHERE/UNION逻辑同上

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