SQL Server使用IN子句关联临时表查询过慢的优化咨询

问题原因分析 #

• 你使用的表变量@tableIds默认没有索引，且SQL Server查询优化器对表变量的行数预估通常默认是1行，面对200万行的ItemPieces表时会生成不合理的执行计划，大概率走了全表扫描，导致查询耗时过长。
• 你之前写EXISTS返回全表，是因为漏写了内外查询的关联条件，没有将ItemPieces的ItemId和表变量中的id做匹配。

优化方案 #

1. 先优化表变量定义 #

给表变量添加主键索引，让关联查询时可以快速匹配id：

DECLARE @tableIds TABLE (id uniqueidentifier PRIMARY KEY)
-- 后续INSERT逻辑保持不变，注：你原写法中关联条件I.Id = UI.UserId大概率是笔误，建议核对修正为I.Id = UI.ItemId
INSERT INTO @tableIds
    SELECT TOP 25 I.Id
    FROM Items I 
    INNER JOIN UserItems UI ON I.Id = UI.ItemId 
    WHERE UI.UserId = 'b846371d-5afc-eb11-b563-0003ff2a40c4'
      AND I.Valid = 1
    ORDER BY I.[TimeStamp] DESC

如果你的SQL Server版本在2016及以上，可以在最终查询末尾加OPTION(RECOMPILE)，让优化器根据表变量的实际行数生成执行计划，效果更好。

2. 正确的高性能查询写法 #

两种写法性能差异不大，你可以根据实际执行计划选择更优的：

写法1：INNER JOIN关联查询

SELECT 
    IPs.Id, 
    IPs.ItemId, 
    IPs.[Name], 
    IPs.PartNo, 
    IPs.PartPosition, 
    IPs.Size,
    IPs.Step
FROM ItemPieces IPs
INNER JOIN @tableIds t ON IPs.ItemId = t.id
-- 可选加重编译选项
-- OPTION(RECOMPILE)

写法2：正确的EXISTS子查询

SELECT 
    IPs.Id, 
    IPs.ItemId, 
    IPs.[Name], 
    IPs.PartNo, 
    IPs.PartPosition, 
    IPs.Size,
    IPs.Step
FROM ItemPieces IPs
WHERE EXISTS (
    SELECT 1 FROM @tableIds t 
    WHERE t.id = IPs.ItemId -- 此关联条件不可省略，省略就会返回全表
)
-- 可选加重编译选项
-- OPTION(RECOMPILE)

3. 长期生效的索引优化方案 #

给ItemPieces表创建以ItemId为键的覆盖索引，直接通过索引就能返回你需要的所有字段，不需要回表查询数据，性能会有数量级提升：

CREATE NONCLUSTERED INDEX IX_ItemPieces_ItemId ON ItemPieces (ItemId)
INCLUDE (Id, [Name], PartNo, PartPosition, Size, Step)

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