SQL Server大表行间数据交集/冲突检测的高效方案问询

问题说明 #

我在SQL Server里有一张表，其中Name列存储的是逗号分隔的字符串，部分行该列值为NULL。需要找出所有满足以下任一条件的ID对（要求A.ID≠B.ID）：

• 两行的Name列存在共同取值
• 其中一行的Name值为NULL

当前使用的代码在数据量小于10万时运行正常，但数据量超过2000万行时，时间与空间开销急剧增大：

SELECT DISTINCT A.ID,B.ID AS ColloidID
 FROM #Temp1 A
 CROSS APPLY #Temp1 B
 WHERE A.ID<>B.ID
 AND master.dbo.fIntersection(COALESCE(A.Name,B.Name,''),COALESCE(B.Name,A.Name,'')) = 1

示例输入 #

预期输出 #

现有方案的核心问题 #

• 笛卡尔积灾难：CROSS APPLY会生成2000万×2000万的中间结果，量级完全超出数据库处理能力
• 自定义函数开销过高：fIntersection函数需逐行调用，无法利用索引，计算成本极大
• DISTINCT雪上加霜：对海量数据排序去重进一步拖慢执行速度

优化方案 #

1. 拆分逗号分隔值，建立带索引的临时表 #

先将每个ID对应的单个Name值拆分出来，通过复合索引快速匹配共同值：

DROP TABLE IF EXISTS #TempSplit;
CREATE TABLE #TempSplit (
    ID INT,
    NameValue VARCHAR(100), -- 根据实际数据调整字段长度
    PRIMARY KEY CLUSTERED (NameValue, ID) -- 复合索引：先按Name值分组，再按ID排序
);

-- 拆分数据并插入临时表
INSERT INTO #TempSplit (ID, NameValue)
SELECT 
    t.ID,
    LTRIM(RTRIM(s.value)) AS NameValue
FROM #Temp1 t
CROSS APPLY STRING_SPLIT(t.Name, ',') s
WHERE t.Name IS NOT NULL; -- 仅拆分非NULL的Name值

注：如果SQL Server版本低于2016（不支持STRING_SPLIT），可替换为XML拆分逻辑：

SELECT
    t.ID,
    LTRIM(RTRIM(SUBSTRING(t.Name, n.value, CHARINDEX(',', t.Name + ',', n.value) - n.value))) AS NameValue
FROM #Temp1 t
CROSS APPLY (
    SELECT number FROM master.dbo.spt_values 
    WHERE type = 'P' AND number BETWEEN 1 AND LEN(t.Name) - LEN(REPLACE(t.Name, ',', '')) + 1
) n
WHERE t.Name IS NOT NULL;

2. 提取有共同值的冲突ID对 #

通过拆分后的表直接匹配共享同一Name值的ID对，彻底避免笛卡尔积：

DROP TABLE IF EXISTS #ConflictPairs;
CREATE TABLE #ConflictPairs (
    ID INT,
    ColloidID INT,
    PRIMARY KEY CLUSTERED (ID, ColloidID) -- 主键自动实现去重
);

-- 插入有共同值的冲突对
INSERT INTO #ConflictPairs (ID, ColloidID)
SELECT DISTINCT
    a.ID,
    b.ID
FROM #TempSplit a
JOIN #TempSplit b ON a.NameValue = b.NameValue AND a.ID <> b.ID;

3. 处理NULL值对应的冲突对 #

单独收集NULL行的ID，与所有非NULL行互相配对，同时处理NULL行之间的冲突：

-- 收集所有Name为NULL的行ID
DROP TABLE IF EXISTS #NullIDs;
CREATE TABLE #NullIDs (
    ID INT PRIMARY KEY CLUSTERED
);
INSERT INTO #NullIDs (ID)
SELECT ID FROM #Temp1 WHERE Name IS NULL;

-- 非NULL行 → NULL行的冲突对
INSERT INTO #ConflictPairs (ID, ColloidID)
SELECT 
    t.ID,
    n.ID
FROM #Temp1 t
CROSS JOIN #NullIDs n
WHERE t.Name IS NOT NULL;

-- NULL行 → 非NULL行的冲突对
INSERT INTO #ConflictPairs (ID, ColloidID)
SELECT 
    n.ID,
    t.ID
FROM #Temp1 t
CROSS JOIN #NullIDs n
WHERE t.Name IS NOT NULL;

-- NULL行之间的冲突对（若需求需要则保留，示例中无多个NULL行可忽略）
INSERT INTO #ConflictPairs (ID, ColloidID)
SELECT 
    a.ID,
    b.ID
FROM #NullIDs a
JOIN #NullIDs b ON a.ID <> b.ID;

4. 输出最终结果 #

直接从临时表读取即可，主键已保证数据唯一性，无需额外去重：

SELECT ID, ColloidID FROM #ConflictPairs;

优化效果说明 #

• 拆分后通过索引匹配共同值，中间结果量从万亿级降至百万/千万级，完全可控
• 替换自定义函数为内置拆分逻辑，大幅降低计算开销
• 分步用临时表存储结果，减少内存压力，同时利用主键索引加速去重和查询
• 把NULL值逻辑单独处理，避免与其他逻辑耦合增加复杂度

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