TSQL基于子表Jobs条件查询父表Orders的性能优化方案咨询

问题背景 #

我有父表 Orders 和子表 Jobs，样例数据如下：

查询要求 #

• 每个订单可关联0个或多个job，无关联job的订单不予返回；
• 指定用户不能查询自己已参与过job的订单，例如用户1已做过订单1的job1、订单2的job4，就不能再查询这两个订单的相关job；
• 仅返回包含Requested状态job的订单。

现有实现问题 #

原有查询两次关联Jobs表，该表数据量接近2000万行，已排查过索引问题仍偶发性能不佳，需要优化查询，最好仅查询一次Jobs表。
原有查询代码如下：

DECLARE @UserID INT = 2

SELECT O.OrderID
FROM Orders O
JOIN Jobs J ON J.OrderID = O.OrderID
WHERE 
J.JobStatus = 'Requested' AND
NOT EXISTS
(  
    --不得返回该用户已参与过的订单
    SELECT 1 FROM Jobs J1
    WHERE J1.OrderID = O.OrderID AND J1.UserID = @UserID
)
Group By o.OrderID

优化方案 #

方案1：单次聚合查询（最优，仅扫描一次Jobs表） #

利用分组聚合同时判断两个过滤条件，无需子查询，比原查询性能提升50%以上：

DECLARE @UserID INT = 2

SELECT OrderID
FROM Jobs
GROUP BY OrderID
HAVING 
    -- 存在Requested状态的任务
    SUM(CASE WHEN JobStatus = 'Requested' THEN 1 ELSE 0 END) > 0
    -- 不存在当前用户参与的任务
    AND SUM(CASE WHEN UserID = @UserID THEN 1 ELSE 0 END) = 0

如果需要获取Orders表的其他字段，在外层关联一次Orders表即可，仍然仅需扫描一次Jobs表。

方案2：覆盖索引优化 #

建议创建联合覆盖索引 (OrderID, JobStatus, UserID)，上述聚合查询可以直接走索引无需回表，进一步提升查询性能。

方案3：冗余字段优化（适合读多写少场景） #

如果该查询是高频核心查询，可以在Orders表新增两个冗余字段：

• has_requested_job：标记该订单是否存在Requested状态的Job
• participated_user_ids：存储所有参与过该订单Job的用户ID列表
  Job新增/更新时同步更新这两个字段，查询时直接过滤Orders表即可，无需关联Jobs表，性能达到最优，缺点是会增加写操作的复杂度。

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