在JOIN中避免LIKE '%值%'的SQL查询优化方案探讨

我有一个参考表（Lookups），用带分隔符的层级列（Ancestry）存储父子孙关系，比如条目#11隶属于#1时，Ancestry值为~1~11~。这个查找表规模小且稳定（约150行，2-3层深度），但引用它的数据表（Items）规模庞大（约500万行，年增100万行）。

在应用程序中，我会缓存查找表数据，通过以下C#代码获取关联ID再查询数据：

var lookupIds = _lookupCache.Where(x => x.Ancestry.Contains($"~{id}~")).Select(x => x.LookupId).ToList();
var data = _context.Items.Where(x => lookupIds.Contains(x.LookupId))
    // 后续筛选、分页等操作...

这种方式避免了文本搜索，运行效果良好。但在报表的SQL查询场景中，我想避免使用双侧通配符的JOIN写法：

SELECT * FROM [Items] i  
INNER JOIN [Lookups] l ON i.LookupId = l.LookupId  
WHERE l.Ancestry LIKE '%' + @pLookupId + '%'  

我的思路是复刻应用端的逻辑：先从查找表中查出符合祖先路径的ID列表，再用IN({ID列表})筛选数据表，避免在两表连接的笛卡尔积上执行通配符搜索。我想知道：

• 上述JOIN写法是否会因笛卡尔积影响性能？
• SQL Server是否会自动将其优化为仅在查找表执行通配符检查？
• 分两步查询是否更优？如果是，对应的TSQL写法是什么？

1. JOIN写法是否会因笛卡尔积影响性能？ #

不会直接产生笛卡尔积，因为INNER JOIN是基于LookupId的等值连接，SQL Server只会匹配两边LookupId相同的行。但实际性能瓶颈在于l.Ancestry LIKE '%...%'这个条件：

• 双侧通配符导致SQL Server无法利用Ancestry列的索引，只能对匹配JOIN条件的Lookups行做全扫描；
• 虽然Lookups表只有150行，扫描成本极低，但Items表有500万行，JOIN操作会先匹配所有i.LookupId = l.LookupId的行，再过滤符合Ancestry条件的记录——这会导致数据库先处理大量Items行的匹配，再做过滤，整体效率远不如先筛选出目标LookupId再关联。

2. SQL Server是否会优化为仅在查找表执行检查？ #

大概率会，但不是绝对的。SQL Server的查询优化器会评估执行计划的成本：

• 由于Lookups表极小，优化器通常会先扫描Lookups表找出符合Ancestry LIKE '%@pLookupId%'的行，拿到LookupId列表后，再用这个列表去Items表做索引查找（如果Items.LookupId有索引的话）；
• 但如果统计信息过时，或者优化器误判了过滤后的LookupId数量，可能会选择先做JOIN再过滤的低效计划。

3. 分两步查询是否更优？ #

是的，分两步查询可以强制逻辑顺序，避免优化器做出低效决策，同时逻辑更清晰，性能更稳定。对应的TSQL写法有三种：

写法一：临时表存储目标ID #

-- 第一步：筛选出目标LookupId存入临时表
SELECT LookupId INTO #TargetLookups
FROM [Lookups]
WHERE Ancestry LIKE '%' + @pLookupId + '%';

-- 第二步：关联临时表查询Items数据
SELECT i.*
FROM [Items] i
INNER JOIN #TargetLookups tl ON i.LookupId = tl.LookupId
-- 后续筛选、分页等操作...

-- 清理临时表（会话结束会自动销毁，可选执行）
DROP TABLE #TargetLookups;

写法二：CTE（公用表表达式） #

WITH TargetLookups AS (
    SELECT LookupId
    FROM [Lookups]
    WHERE Ancestry LIKE '%' + @pLookupId + '%'
)
SELECT i.*
FROM [Items] i
INNER JOIN TargetLookups tl ON i.LookupId = tl.LookupId
-- 后续筛选、分页等操作...

写法三：子查询直接嵌入 #

SELECT i.*
FROM [Items] i
WHERE i.LookupId IN (
    SELECT LookupId
    FROM [Lookups]
    WHERE Ancestry LIKE '%' + @pLookupId + '%'
)
-- 后续筛选、分页等操作...

这种写法最简洁，优化器通常会将其处理成和分两步查询一致的高效执行计划。

额外优化建议 #

给Items.LookupId创建非聚集索引，能让第二步的关联/IN操作速度大幅提升。

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