优化SQL查询：提升百万级tblUsers2关联查询效率

问题背景 #

需要提取2023-01-01起有活动记录的经理名单：

• 经理姓名来自tblSAP表，关联tblUsers2表查询活动记录
• 经理姓名可能出现在tblUsers2的Supervisor、Level5、Level6、Level7、Level8字段
• 当前查询结果正确，但tblUsers2已有190万行且每日增长，查询耗时约90秒，需优化提速，同时希望找到经理姓名首次匹配后就处理下一位经理

原查询代码：

DECLARE
@StartDate AS Datetime
SET @StartDate = '2023-01-01'

SELECT Last + ', ' + First AS Manager_Name
FROM tblSAP
INNER JOIN tblUsers2 U2 ON Last + ', ' + First = U2.Supervisor OR
    (Last + ', ' + First = Level6) OR
    (Last + ', ' + First = Level5) OR
    (Last + ', ' + First = Level7) OR
    (Last + ', ' + First = Level8)
WHERE U2.ExtractDate >= @StartDate AND (Title LIKE '%manager%') AND (Title LIKE '%operations%') OR (Job_Desc LIKE '%manager%') AND (Job_Desc LIKE '%operations%') 
GROUP BY Last, First
ORDER BY Last, First;

核心问题分析 #

1. 多OR JOIN条件：导致数据库无法有效利用索引，只能对tblUsers2做全表扫描，随着数据量增长耗时剧增
2. WHERE子句逻辑歧义：AND优先级高于OR，原条件的逻辑实际不符合预期，会包含不符合ExtractDate >= @StartDate的记录
3. 实时字符串拼接：JOIN时拼接Last + ', ' + First会阻止索引使用，额外消耗计算资源

优化方案 #

1. 修正WHERE子句逻辑优先级 #

先将两组条件（Title和Job_Desc）用括号包裹，确保ExtractDate >= @StartDate对所有结果生效：

WHERE U2.ExtractDate >= @StartDate 
AND (
    (Title LIKE '%manager%' AND Title LIKE '%operations%') 
    OR 
    (Job_Desc LIKE '%manager%' AND Job_Desc LIKE '%operations%')
)

2. 用UNION ALL替代多OR JOIN，实现首次匹配跳过后续检查 #

将原多OR的JOIN拆分为多个独立的关联分支，按匹配概率从高到低排序（比如先查Supervisor、再查Level6），并用NOT EXISTS排除已匹配的经理，达到"找到首次匹配后处理下一位"的效果：

DECLARE @StartDate AS Datetime
SET @StartDate = '2023-01-01'

WITH ManagerMatches AS (
    -- 优先匹配Supervisor字段
    SELECT DISTINCT Last, First
    FROM tblSAP
    INNER JOIN tblUsers2 U2 ON Last + ', ' + First = U2.Supervisor
    WHERE U2.ExtractDate >= @StartDate 
    AND (
        (Title LIKE '%manager%' AND Title LIKE '%operations%') 
        OR 
        (Job_Desc LIKE '%manager%' AND Job_Desc LIKE '%operations%')
    )
    UNION ALL
    -- 匹配Level6，排除已在Supervisor中找到的经理
    SELECT DISTINCT Last, First
    FROM tblSAP
    INNER JOIN tblUsers2 U2 ON Last + ', ' + First = U2.Level6
    WHERE U2.ExtractDate >= @StartDate 
    AND (
        (Title LIKE '%manager%' AND Title LIKE '%operations%') 
        OR 
        (Job_Desc LIKE '%manager%' AND Job_Desc LIKE '%operations%')
    )
    AND NOT EXISTS (
        SELECT 1 FROM ManagerMatches mm WHERE mm.Last = tblSAP.Last AND mm.First = tblSAP.First
    )
    UNION ALL
    -- 匹配Level5，排除已匹配的经理
    SELECT DISTINCT Last, First
    FROM tblSAP
    INNER JOIN tblUsers2 U2 ON Last + ', ' + First = U2.Level5
    WHERE U2.ExtractDate >= @StartDate 
    AND (
        (Title LIKE '%manager%' AND Title LIKE '%operations%') 
        OR 
        (Job_Desc LIKE '%manager%' AND Job_Desc LIKE '%operations%')
    )
    AND NOT EXISTS (
        SELECT 1 FROM ManagerMatches mm WHERE mm.Last = tblSAP.Last AND mm.First = tblSAP.First
    )
    UNION ALL
    -- 匹配Level7，排除已匹配的经理
    SELECT DISTINCT Last, First
    FROM tblSAP
    INNER JOIN tblUsers2 U2 ON Last + ', ' + First = U2.Level7
    WHERE U2.ExtractDate >= @StartDate 
    AND (
        (Title LIKE '%manager%' AND Title LIKE '%operations%') 
        OR 
        (Job_Desc LIKE '%manager%' AND Job_Desc LIKE '%operations%')
    )
    AND NOT EXISTS (
        SELECT 1 FROM ManagerMatches mm WHERE mm.Last = tblSAP.Last AND mm.First = tblSAP.First
    )
    UNION ALL
    -- 匹配Level8，排除已匹配的经理
    SELECT DISTINCT Last, First
    FROM tblSAP
    INNER JOIN tblUsers2 U2 ON Last + ', ' + First = U2.Level8
    WHERE U2.ExtractDate >= @StartDate 
    AND (
        (Title LIKE '%manager%' AND Title LIKE '%operations%') 
        OR 
        (Job_Desc LIKE '%manager%' AND Job_Desc LIKE '%operations%')
    )
    AND NOT EXISTS (
        SELECT 1 FROM ManagerMatches mm WHERE mm.Last = tblSAP.Last AND mm.First = tblSAP.First
    )
)
SELECT Last + ', ' + First AS Manager_Name
FROM ManagerMatches
ORDER BY Last, First;

3. 创建复合索引，大幅提升查询效率 #

为tblUsers2的每个关联字段结合查询过滤字段创建复合索引，让数据库直接通过索引定位数据，避免全表扫描：

-- Supervisor字段专用索引
CREATE NONCLUSTERED INDEX IX_tblUsers2_Supervisor_ExtractDate_Title_JobDesc ON tblUsers2
(Supervisor, ExtractDate)
INCLUDE (Title, Job_Desc);

-- Level5字段专用索引
CREATE NONCLUSTERED INDEX IX_tblUsers2_Level5_ExtractDate_Title_JobDesc ON tblUsers2
(Level5, ExtractDate)
INCLUDE (Title, Job_Desc);

-- Level6字段专用索引
CREATE NONCLUSTERED INDEX IX_tblUsers2_Level6_ExtractDate_Title_JobDesc ON tblUsers2
(Level6, ExtractDate)
INCLUDE (Title, Job_Desc);

-- Level7字段专用索引
CREATE NONCLUSTERED INDEX IX_tblUsers2_Level7_ExtractDate_Title_JobDesc ON tblUsers2
(Level7, ExtractDate)
INCLUDE (Title, Job_Desc);

-- Level8字段专用索引
CREATE NONCLUSTERED INDEX IX_tblUsers2_Level8_ExtractDate_Title_JobDesc ON tblUsers2
(Level8, ExtractDate)
INCLUDE (Title, Job_Desc);

4. 优化字符串拼接性能 #

在tblSAP表新增FullName字段，默认值设为Last + ', ' + First并维护数据一致性，这样JOIN时无需实时拼接字符串，进一步降低计算开销，也方便后续索引使用。

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