含OR条件的JOIN关联SELECT查询性能调优求助

原条件逻辑拆解 #

原查询的核心条件(pm.prdname is null or (pm.prdname = 'EV' AND pm.prdtype = 'EType'))等价于两类记录的并集：

1. prdname为NULL的记录
2. prdname='EV'且prdtype='EType'的记录

你之前的改写错误原因：

• 第一种改写：pm.prdname IN (NULL,'EV') AND pm.prdtype = 'EType'，SQL中NULL IN (...)结果永远是未知，无法匹配到prdname为NULL的记录，同时强制要求prdtype='EType'，过滤了原条件中允许的prdname为NULL且prdtype任意的情况，因此无结果返回。
• 第二种改写：(pm.prdname IN (NULL,'EV') OR pm.prdtype = 'EType')，逻辑被扩大为prdname是NULL/EV 或 prdtype='EType'，包含了大量原条件外的记录，导致结果不匹配且性能更差。

性能优化方案 #

方案1：拆分OR条件为UNION ALL（快速见效）

OR条件会导致索引失效，将查询拆分为两个独立子查询用UNION ALL合并，每个子查询可单独利用索引：

原WHERE查询的优化版：

-- Time Taken: 预期大幅缩短
SELECT * FROM ProductMaster pm
WHERE pm.prdname IS NULL
UNION ALL
SELECT * FROM ProductMaster pm
WHERE pm.prdname = 'EV' AND pm.prdtype = 'EType';

JOIN场景的优化版：

-- 拆分LEFT JOIN为两个独立关联，合并结果
SELECT t.*, pm1.*
FROM 主表 t
LEFT JOIN ProductMaster pm1 
  ON t.Pcode=pm1.Pcode 
  AND t.SaleDate=pm1.SaleDate 
  AND t.prdscode=pm1.prdscode
  AND pm1.prdname IS NULL
UNION ALL
SELECT t.*, pm2.*
FROM 主表 t
LEFT JOIN ProductMaster pm2 
  ON t.Pcode=pm2.Pcode 
  AND t.SaleDate=pm2.SaleDate 
  AND t.prdscode=pm2.prdscode
  AND pm2.prdname = 'EV' 
  AND pm2.prdtype = 'EType';

注：如果关联后可能产生重复记录，将UNION ALL改为UNION（需去重，性能略降）。

方案2：创建复合索引（根源优化）

针对JOIN条件和过滤条件创建联合索引，让数据库直接通过索引获取数据，避免全表扫描：

-- 覆盖JOIN条件+prdname IS NULL的索引
CREATE INDEX idx_pm_join_prdname ON ProductMaster (Pcode, SaleDate, prdscode, prdname);

-- 覆盖JOIN条件+EV&EType过滤的索引
CREATE INDEX idx_pm_join_ev_type ON ProductMaster (Pcode, SaleDate, prdscode, prdname, prdtype);

也可创建一个通用索引同时覆盖两种场景：

CREATE INDEX idx_pm_comprehensive ON ProductMaster (Pcode, SaleDate, prdscode, prdname, prdtype);

方案3：等价改写非OR条件（可选）

将原条件改写为不包含OR的形式，让数据库更好地利用索引：

SELECT * FROM ProductMaster pm
WHERE COALESCE(pm.prdname, 'EV') = 'EV'
  AND (pm.prdname IS NULL OR pm.prdtype = 'EType');

注：该方案效果取决于数据库优化器，建议结合执行计划验证。

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