含OR的单条SQL查询远慢于两条独立查询的性能问题排查

这个问题我碰到过不少次，带OR的复合查询尤其是嵌套子查询时，很容易让数据库优化器卡壳，导致执行效率暴跌。咱们先从你的查询和执行计划入手分析：

首先看你的原查询：

DELETE FROM AuditTaskImpl l 
WHERE l.processInstanceId IN (SELECT spl.processInstanceId FROM ProcessInstanceLog spl WHERE spl.status IN (2,3)) 
OR NOT EXISTS (SELECT spl.processInstanceId FROM ProcessInstanceLog spl WHERE l.processinstanceid = spl.processinstanceid);

从执行计划能看到，数据库只能对audittaskimpl做全表扫描（Seq Scan），成本高达42万+，这就是速度慢的核心原因——OR条件让优化器无法高效利用索引，两个子查询的过滤逻辑没法合并成一个最优的扫描路径，只能逐行检查是否满足任一条件。

为什么拆分后更快？ #

拆分后的两个独立DELETE，每个都只处理单一条件：优化器可以针对每个查询单独选择最优执行计划，比如利用processInstanceId上的索引快速定位目标行，不用全表遍历。

具体解决方案 #

方案1：保留拆分查询（最直接有效）

既然你已经验证过拆分后速度更快，这就是最优选择，直接用两个DELETE语句：

-- 清理关联到指定状态流程实例的审计任务
DELETE FROM AuditTaskImpl l 
WHERE l.processInstanceId IN (SELECT spl.processInstanceId FROM ProcessInstanceLog spl WHERE spl.status IN (2,3));

-- 清理没有对应流程实例记录的审计任务
DELETE FROM AuditTaskImpl l 
WHERE NOT EXISTS (SELECT spl.processInstanceId FROM ProcessInstanceLog spl WHERE l.processinstanceid = spl.processinstanceid);

方案2：用UNION ALL重写为单条查询（如果必须单语句执行）

如果业务要求必须用单条语句完成，可以把两个条件的目标ID用UNION ALL合并，让优化器分别处理每个分支：

WITH target_pids AS (
    -- 获取需要清理的第一类流程实例ID
    SELECT processInstanceId FROM ProcessInstanceLog WHERE status IN (2,3)
    UNION ALL
    -- 获取需要清理的第二类流程实例ID（无对应日志的）
    SELECT processInstanceId FROM AuditTaskImpl l 
    WHERE NOT EXISTS (SELECT 1 FROM ProcessInstanceLog spl WHERE l.processinstanceid = spl.processinstanceid)
)
DELETE FROM AuditTaskImpl l
WHERE l.processInstanceId IN (SELECT processInstanceId FROM target_pids);

UNION ALL不会去重，比UNION更高效，优化器可以对每个子查询单独使用索引，再合并结果集执行删除。

方案3：补全关键索引（提升所有相关查询性能）

不管用哪种方案，添加以下索引都能大幅提速：

-- 覆盖第一个子查询的索引，避免回表查询
CREATE INDEX idx_processinstancelog_status_pid ON ProcessInstanceLog(status, processInstanceId);

-- 加速NOT EXISTS的反连接查询
CREATE INDEX idx_processinstancelog_pid ON ProcessInstanceLog(processInstanceId);

-- 加速AuditTaskImpl的ID查找
CREATE INDEX idx_audittaskimpl_pid ON AuditTaskImpl(processInstanceId);

总结 #

OR条件本身不是洪水猛兽，但当它连接两个逻辑独立的子查询时，优化器往往难以生成高效计划。拆分查询或者用UNION ALL重写，再配合合适的索引，就能解决这类性能问题。

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