Azure SQL中IN子句值过多时索引查找转扫描的问题求助

我们在Azure SQL中创建了如下非聚集唯一索引：

ALTER TABLE [Allocation].[allocation_plan_detail] ADD  CONSTRAINT [UQ_AP_TENANT_TYPE_STATUS_PLAN_ITEM_CLUB] UNIQUE NONCLUSTERED 
(
    [tenant_id] ASC,
    [item_type] ASC,
    [allocation_plan_status] ASC,
    [allocation_plan_type] ASC,
    [item_nbr] ASC,
    [club_nbr] ASC
)

现象描述 #

• 当IN子句包含少量item_nbr值时，执行计划显示为理想的Index Seek，对应查询语句：

  SELECT *
  FROM   Allocation.allocation_plan_detail
  WHERE  tenant_id = 'sams_us'
         AND item_type = 'inseason'
         AND allocation_plan_status = 'draft'
         AND allocation_plan_type = 'continuous'
         AND item_nbr IN ( 10177, 107, 109, 112,
                           511993, 117, 120, 122, 31889 )
  

• 当IN子句包含200+个item_nbr值时，执行计划出现Constant Scan，查询速度极慢，对应语句：

  select * from Allocation.allocation_plan_detail where item_nbr in (72512,207317,...N(200+)) and allocation_plan_status='draft' and item_type ='inseason' and tenant_id='sams_us' and allocation_plan_type='continuous'
  

问题解答 #

1. 解决查询速度慢的方案 #

• 使用表值参数替代IN子句：把大量item_nbr存入表值参数，通过JOIN关联查询，让优化器更准确评估行数，更易选择Index Seek计划。示例代码：

  -- 先定义表值参数类型
  CREATE TYPE ItemNbrList AS TABLE (item_nbr INT PRIMARY KEY);
  GO
  
  -- 声明并填充参数
  DECLARE @Items ItemNbrList;
  INSERT INTO @Items VALUES (72512), (207317), ... -- 填入200+个item_nbr值
  GO
  
  -- 改写后的查询
  SELECT d.*
  FROM Allocation.allocation_plan_detail d
  JOIN @Items i ON d.item_nbr = i.item_nbr
  WHERE d.tenant_id = 'sams_us'
        AND d.item_type = 'inseason'
        AND d.allocation_plan_status = 'draft'
        AND d.allocation_plan_type = 'continuous';
  

• 拆分批量查询：将200+个item_nbr拆分成多个小批次（比如每50个一组），分别执行查询后合并结果。每轮小查询都会走Index Seek，整体耗时可能优于单轮大查询。
• 添加索引提示兜底：如果确认目标索引是最优选择，可强制优化器使用该索引。示例：

  SELECT *
  FROM Allocation.allocation_plan_detail WITH (INDEX([UQ_AP_TENANT_TYPE_STATUS_PLAN_ITEM_CLUB]))
  WHERE item_nbr IN (72512,207317,...N(200+)) 
        AND allocation_plan_status='draft' 
        AND item_type ='inseason' 
        AND tenant_id='sams_us' 
        AND allocation_plan_type='continuous';
  

  注意：索引提示是兜底方案，优先尝试前两种方法，避免后续索引变更时出现适配问题。

2. Constant Scan与Table Scan的区别 #

二者完全不同：

• Constant Scan：是生成常量数据集的操作，把IN子句里的大量值转换成临时常量结果集，本身不扫描表。但当常量集过大时，后续关联操作的成本会急剧上升，导致整体查询变慢。
• Table Scan：是直接扫描整个基表的所有数据行，逐一筛选符合条件的记录，属于对表的全量扫描操作，通常在无合适索引或优化器认为全表扫描成本更低时触发。

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