含多OR条件的SQL查询在5000万记录表执行过慢，如何优化？

问题根源 #

• 原查询逻辑是先完成4张大表的全量内连接，再通过跨表OR条件过滤符合增量时间的记录，最后对结果去重，海量数据下关联计算成本极高
• 跨多表的OR条件会导致数据库无法高效命中各表的UpdatedDate索引，大概率触发全表扫描
• 全量关联后再做DISTINCT去重的开销远高于提前过滤缩小数据集后再去重

优化方案 #

核心逻辑是用UNION代替跨表OR，把4个OR条件拆为独立的子查询，每个子查询只过滤单表的更新时间，提前命中索引缩小数据集后再合并去重，避免全量关联，且UNION自带去重能力无需额外写DISTINCT。

优化后代码如下：

DECLARE @LastRunDate DATETIME
SELECT @LastRunDate=LastRunDateTime FROM [DataAudit].[t_DeltaSetting] da 
WHERE da.[InterfaceName] = 'ATKInnovationTargetedCustomersToHANA'

-- 子查询1：过滤主表initi满足更新时间条件的记录
SELECT initi.RecordID
FROM WebData.t_Initiative initi
INNER JOIN WebData.t_InitiativeCustomer ic on initi.RecordID=ic.InitiativeId
INNER JOIN WebData.t_Tracker track on track.InitiativeId = initi.RecordID
INNER JOIN WebData.t_TrackerCustomer tc on tc.TrackerId=track.RecordID
WHERE initi.UpdatedDate > @LastRunDate

UNION

-- 子查询2：过滤ic表满足更新时间条件的记录
SELECT initi.RecordID
FROM WebData.t_Initiative initi
INNER JOIN WebData.t_InitiativeCustomer ic on initi.RecordID=ic.InitiativeId
INNER JOIN WebData.t_Tracker track on track.InitiativeId = initi.RecordID
INNER JOIN WebData.t_TrackerCustomer tc on tc.TrackerId=track.RecordID
WHERE ic.UpdatedDate > @LastRunDate

UNION

-- 子查询3：过滤track表满足更新时间条件的记录
SELECT initi.RecordID
FROM WebData.t_Initiative initi
INNER JOIN WebData.t_InitiativeCustomer ic on initi.RecordID=ic.InitiativeId
INNER JOIN WebData.t_Tracker track on track.InitiativeId = initi.RecordID
INNER JOIN WebData.t_TrackerCustomer tc on tc.TrackerId=track.RecordID
WHERE track.UpdatedDate > @LastRunDate

UNION

-- 子查询4：过滤tc表满足更新时间条件的记录
SELECT initi.RecordID
FROM WebData.t_Initiative initi
INNER JOIN WebData.t_InitiativeCustomer ic on initi.RecordID=ic.InitiativeId
INNER JOIN WebData.t_Tracker track on track.InitiativeId = initi.RecordID
INNER JOIN WebData.t_TrackerCustomer tc on tc.TrackerId=track.RecordID
WHERE tc.UpdatedDate > @LastRunDate

附加索引优化建议 #

为了让每个子查询都能高效命中索引，建议新增以下覆盖索引：

• WebData.t_Initiative：建立联合索引 (UpdatedDate, RecordID)
• WebData.t_InitiativeCustomer：建立联合索引 (InitiativeId, UpdatedDate)
• WebData.t_Tracker：建立联合索引 (InitiativeId, RecordID, UpdatedDate)
• WebData.t_TrackerCustomer：建立联合索引 (TrackerId, UpdatedDate)

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