优化多关联匹配计数SQL查询性能：改进与替代方案咨询

问题背景与需求 #

需要返回满足「匹配指定参数的列/关联表记录数之和≥3」的所有Individual数据：

• Individual表存在多个1:N关联的子实体（如PassportNumbers、Forenames等），通过IndividualID外键关联
• 子实体的匹配参数以逗号分隔字符串形式传入（例如'test01,test02'）

现有查询实现如下：

SELECT DISTINCT I.* FROM Individuals I
LEFT JOIN PassportNumbers PN ON PN.IndividualID = I.IndividualID AND (PN.Value IN (SELECT value FROM string_split(@PassportNumbers, ',')))
LEFT JOIN Forenames F ON F.IndividualID = I.IndividualID AND (F.Value IN (SELECT value FROM string_split(@Forenames, ',')))
LEFT JOIN LandlineNumbers L ON L.IndividualID = I.IndividualID AND (L.Value IN (SELECT value FROM string_split(@LandlineNumbers, ',')))
LEFT JOIN MobileNumbers M ON M.IndividualID = I.IndividualID AND (M.Value IN (SELECT value FROM string_split(@MobileNumbers, ',')))
LEFT JOIN UPRNs U ON U.IndividualID = I.IndividualID AND (U.Value IN (SELECT value FROM string_split(@UPRNs, ',')))
WHERE 
(       
    (SELECT 
        CASE WHEN I.Surname = @Surname THEN 1 ELSE 0 END + 
        CASE WHEN I.EmailAddress = @EmailAddress THEN 1 ELSE 0 END + 
        CASE WHEN I.MiddleName = @MiddleName THEN 1 ELSE 0 END + 
        CASE WHEN I.Alias = @Alias THEN 1 ELSE 0 END + 
        CASE WHEN @BirthDate IS NOT NULL AND I.BirthDate = @BirthDate THEN 1 ELSE 0 END + 
        CASE WHEN @DeathDate IS NOT NULL AND I.DeathDate = @DeathDate THEN 1 ELSE 0 END + 
        CASE WHEN PN.Id > 0 THEN 1 ELSE 0 END +
        CASE WHEN F.Id > 0 THEN 1 ELSE 0 END +
        CASE WHEN M.Id > 0 THEN 1 ELSE 0 END +
        CASE WHEN L.Id > 0 THEN 1 ELSE 0 END +
        CASE WHEN U.Id > 0 THEN 1 ELSE 0 END 
    )
    >= 3
)

现有查询的性能瓶颈 #

1. 结果集膨胀与去重开销：多个LEFT JOIN会生成大量笛卡尔积（比如一个Individual有3个匹配的Passport和2个匹配的Forename，会生成3*2=6条重复记录），后续DISTINCT需要额外的排序和去重操作，性能损耗极大
2. 重复计算：每个JOIN都重复调用string_split拆分参数，浪费CPU资源
3. 索引无法有效利用：JOIN条件中嵌套string_split，加上子查询里的CASE判断依赖JOIN结果，数据库难以生成最优执行计划

优化方案 #

方案1：用EXISTS替代LEFT JOIN，避免结果集膨胀 #

核心思路是用EXISTS判断子实体是否存在匹配记录，不产生笛卡尔积；同时提前将参数拆分到表变量，避免重复计算。

-- 提前拆分所有参数到表变量，仅计算一次
DECLARE @PassportValues TABLE (Value NVARCHAR(255));
INSERT INTO @PassportValues SELECT value FROM string_split(@PassportNumbers, ',');

DECLARE @ForenameValues TABLE (Value NVARCHAR(255));
INSERT INTO @ForenameValues SELECT value FROM string_split(@Forenames, ',');

DECLARE @LandlineValues TABLE (Value NVARCHAR(255));
INSERT INTO @LandlineValues SELECT value FROM string_split(@LandlineNumbers, ',');

DECLARE @MobileValues TABLE (Value NVARCHAR(255));
INSERT INTO @MobileValues SELECT value FROM string_split(@MobileNumbers, ',');

DECLARE @UPRNValues TABLE (Value NVARCHAR(255));
INSERT INTO @UPRNValues SELECT value FROM string_split(@UPRNs, ',');

SELECT I.*
FROM Individuals I
WHERE (
    -- 统计Individual自身字段的匹配数
    CASE WHEN I.Surname = @Surname THEN 1 ELSE 0 END +
    CASE WHEN I.EmailAddress = @EmailAddress THEN 1 ELSE 0 END +
    CASE WHEN I.MiddleName = @MiddleName THEN 1 ELSE 0 END +
    CASE WHEN I.Alias = @Alias THEN 1 ELSE 0 END +
    CASE WHEN @BirthDate IS NOT NULL AND I.BirthDate = @BirthDate THEN 1 ELSE 0 END +
    CASE WHEN @DeathDate IS NOT NULL AND I.DeathDate = @DeathDate THEN 1 ELSE 0 END +
    -- 用EXISTS判断子实体是否匹配，每个维度仅算1次
    CASE WHEN EXISTS(SELECT 1 FROM PassportNumbers PN WHERE PN.IndividualID = I.IndividualID AND PN.Value IN (SELECT Value FROM @PassportValues)) THEN 1 ELSE 0 END +
    CASE WHEN EXISTS(SELECT 1 FROM Forenames F WHERE F.IndividualID = I.IndividualID AND F.Value IN (SELECT Value FROM @ForenameValues)) THEN 1 ELSE 0 END +
    CASE WHEN EXISTS(SELECT 1 FROM LandlineNumbers L WHERE L.IndividualID = I.IndividualID AND L.Value IN (SELECT Value FROM @LandlineValues)) THEN 1 ELSE 0 END +
    CASE WHEN EXISTS(SELECT 1 FROM MobileNumbers M WHERE M.IndividualID = I.IndividualID AND M.Value IN (SELECT Value FROM @MobileValues)) THEN 1 ELSE 0 END +
    CASE WHEN EXISTS(SELECT 1 FROM UPRNs U WHERE U.IndividualID = I.IndividualID AND U.Value IN (SELECT Value FROM @UPRNValues)) THEN 1 ELSE 0 END
) >= 3;

方案2：预计算子实体匹配维度，再合并统计 #

先找出所有匹配参数的子实体对应的IndividualID，统计每个Individual匹配的子实体维度数，再和自身字段的匹配数相加筛选。

-- 提前拆分参数
DECLARE @PassportValues TABLE (Value NVARCHAR(255));
INSERT INTO @PassportValues SELECT value FROM string_split(@PassportNumbers, ',');

DECLARE @ForenameValues TABLE (Value NVARCHAR(255));
INSERT INTO @ForenameValues SELECT value FROM string_split(@Forenames, ',');

DECLARE @LandlineValues TABLE (Value NVARCHAR(255));
INSERT INTO @LandlineValues SELECT value FROM string_split(@LandlineNumbers, ',');

DECLARE @MobileValues TABLE (Value NVARCHAR(255));
INSERT INTO @MobileValues SELECT value FROM string_split(@MobileNumbers, ',');

DECLARE @UPRNValues TABLE (Value NVARCHAR(255));
INSERT INTO @UPRNValues SELECT value FROM string_split(@UPRNs, ',');

-- 统计每个Individual匹配的子实体维度数
WITH EntityMatches AS (
    SELECT IndividualID, 1 AS MatchFlag FROM PassportNumbers PN WHERE PN.Value IN (SELECT Value FROM @PassportValues)
    UNION ALL
    SELECT IndividualID, 1 AS MatchFlag FROM Forenames F WHERE F.Value IN (SELECT Value FROM @ForenameValues)
    UNION ALL
    SELECT IndividualID, 1 AS MatchFlag FROM LandlineNumbers L WHERE L.Value IN (SELECT Value FROM @LandlineValues)
    UNION ALL
    SELECT IndividualID, 1 AS MatchFlag FROM MobileNumbers M WHERE M.Value IN (SELECT Value FROM @MobileValues)
    UNION ALL
    SELECT IndividualID, 1 AS MatchFlag FROM UPRNs U WHERE U.Value IN (SELECT Value FROM @UPRNValues)
),
TotalEntityMatches AS (
    SELECT IndividualID, COUNT(DISTINCT MatchFlag) AS EntityMatchTotal 
    -- 用COUNT(DISTINCT)确保每个子实体维度仅算1次，即使有多个匹配记录
    FROM EntityMatches
    GROUP BY IndividualID
)
SELECT I.*
FROM Individuals I
LEFT JOIN TotalEntityMatches TEM ON I.IndividualID = TEM.IndividualID
WHERE (
    CASE WHEN I.Surname = @Surname THEN 1 ELSE 0 END +
    CASE WHEN I.EmailAddress = @EmailAddress THEN 1 ELSE 0 END +
    CASE WHEN I.MiddleName = @MiddleName THEN 1 ELSE 0 END +
    CASE WHEN I.Alias = @Alias THEN 1 ELSE 0 END +
    CASE WHEN @BirthDate IS NOT NULL AND I.BirthDate = @BirthDate THEN 1 ELSE 0 END +
    CASE WHEN @DeathDate IS NOT NULL AND I.DeathDate = @DeathDate THEN 1 ELSE 0 END +
    ISNULL(TEM.EntityMatchTotal, 0)
) >= 3;

额外性能优化建议 #

• 给子实体表创建复合索引：例如CREATE INDEX IX_PassportNumbers_IndividualID_Value ON PassportNumbers(IndividualID, Value);，加速EXISTS和IN查询的匹配速度
• 参数判空：如果传入的逗号分隔字符串为空，提前跳过对应的子实体查询，避免无效的表扫描
• 避免嵌套子查询：尽量用CTE或表变量预计算结果，让数据库更容易生成高效的执行计划

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