SQL Server参数化LIKE查询触发索引扫描而非索引查找问题

• 硬编码常量查询的编译逻辑：前缀写死的LIKE 'name%'查询在编译阶段，优化器可以直接获取常量值，统计该前缀对应的实际匹配行数，判定匹配行数少，走3个非聚集索引查找+结果合并+键查找回表的成本低于全表扫描，因此生成索引查找的执行计划。
• 局部变量的基数估计偏差：你用declare @search声明的是局部变量，SQL Server查询优化器在编译执行计划时不会读取局部变量的运行时赋值，针对LIKE @search + '%'这种前缀匹配逻辑，默认会按照匹配表中10%的行数做保守估计，远高于你的实际匹配行数。
• OR多条件的成本判定偏差：三个字段的OR查询走索引需要执行3次独立的非聚集索引查找，再对结果合并去重，加上你的三个非聚集索引都是单列索引，查询SELECT *需要额外做键查找回聚集索引取全量字段，当优化器高估匹配行数时，会判定这套操作的总成本远高于直接扫描聚集索引的成本，因此最终选择全表扫描。

1. 增加重编译提示，让优化器获取实际参数值
   在查询末尾加option(recompile)，让SQL Server在执行查询前再生成执行计划，此时可以读取到@search的实际值，做准确的基数估计，大概率会生成索引查找的计划：

declare @search varchar(30) = 'name'
select * 
from [dbo].[user] 
where FirstName like @search + '%' 
   or LastName like @search  + '%' 
   or MiddleName like @search  + '%'
option (recompile)

注意：user是SQL Server内置保留关键字，作为表名使用时需要用方括号包裹，避免语法错误。

2. 把单列非聚集索引改造为覆盖索引，消除回表成本
   给三个非聚集索引增加INCLUDE列，覆盖查询需要的所有字段，不需要再回聚集索引取数，大幅降低索引查找的成本，优化器会更倾向于选择索引查找：

-- 改造FirstName索引，其他两个字段的索引做同样修改即可
CREATE NONCLUSTERED INDEX [M_FIRSTNAME]
ON [dbo].[user]([FirstName] ASC) 
INCLUDE ([UserGuid], [CreateDate], [LastModifiedDate], [Active], [MiddleName], [LastName], [DateOfBirth], [Email], [HomePhone], [WorkPhone])
WITH (DROP_EXISTING = ON);

3. 改写OR查询为UNION形式，明确引导优化器走独立索引
   把OR拆分成分开的查询用UNION合并，避免OR条件带来的索引选择逻辑混乱，配合重编译提示效果更好：

declare @search varchar(30) = 'name'
select * from [dbo].[user] where FirstName like @search + '%'
union
select * from [dbo].[user] where LastName like @search + '%'
union
select * from [dbo].[user] where MiddleName like @search + '%'
option (recompile)

如果业务允许返回少量重复行，也可以用UNION ALL替代UNION，省去去重步骤性能更高。

4. 固定参数优化值（适合存储过程场景）
   如果查询是写在存储过程里，搜索参数的选择性普遍较高，可以指定优化参考值，不需要每次重编译：

CREATE PROCEDURE SearchUser @search varchar(30)
AS
BEGIN
select * 
from [dbo].[user] 
where FirstName like @search + '%' 
   or LastName like @search  + '%' 
   or MiddleName like @search  + '%'
option (optimize for (@search = 'name'))
END

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