SQLServer中LIKE%search_string%走索引查找（IndexSeek）浅析

SQLServer中LIKE%search_string%⾛索引查找（IndexSeek）
浅析
在SQL Server的SQL优化过程中，如果遇到WHERE条件中包含LIKE '%search_string%'是⼀件⾮常头痛的事情。

这种情况下，⼀般要修改业务逻辑或改写SQL才能解决SQL执⾏计划⾛索引扫描或全表扫描的问题。

最近在优化SQL语句的时候，遇到了⼀个很有意思的问题。

某些使⽤LIKE '%' + @search_string + '%'(或者 LIKE @search_string)这样写法的SQL语句的执⾏计划居然⾛索引查找（Index Seek）。

下⾯这篇⽂章来分析⼀下这个奇怪的现象。

⾸先，我们来看看WHERE查询条件中使⽤LIKE的⼏种情况,这些是我们对LIKE的⼀些常规认识：
1： LIKE 'condition%'
执⾏计划会⾛索引查找（Index Seek or Clustered Index Seek）。

2: LIKE '%condition'
执⾏计划会⾛索引扫描（Index Scan or Clustered Index Scan）或全表扫描（Table Scan）
3: LIKE '%condition%'
执⾏计划会⾛索引扫描（Index Scan or Clustered Index Scan）或全表扫描（Table Scan）
4： LIKE 'condition1%condition%';
执⾏计划会⾛索引查找（Index Seek）
下⾯我们以AdventureWorks2014⽰例数据库为测试环境（测试环境为SQL Server 2014 SP2），测试上⾯四种情况，如下所⽰：
其实复杂的情况下，LIKE 'search_string%'也有⾛索引扫描（Index Scan）的情况，上⾯情况并不是唯⼀、绝对的。

如下所⽰
在表Person.Person的 rowguid字段上创建有唯⼀索引AK_Person_rowguid
那么我们来看看上⾯所说的这个特殊案例（这⾥使⽤⼀个现成的案例，懒得构造案例了），如何让LIKE %search_string%⾛索引查找（Index Seek），这个技巧就是使⽤变量，如下SQL对⽐所⽰：
如下所⽰，表[dbo].[GEN_CUSTOMER]在字段CUSTOMER_CD有聚集索引。

可以看到CUSTOMER_CD LIKE '%' + @CUSTOMER_CD + '%'这样的SQL写法（或者CUSTOMER_CD LIKE @CUSTOMER_CD也可以），执⾏计划就⾛聚集索引查找（Clustered Index Seek）了，⽽条件中直接使⽤CUSTOMER_CD LIKE '%00630%' 反⽽⾛聚集索引扫描（Clustered Index Scan），另外可以看到实际执⾏的Cost开销⽐为4% VS 96% ，初⼀看，还真的以为第⼀个执⾏计划⽐第⼆个执⾏的代价要⼩很多。

但是从IO开销，以及CPU time、elapsed time对⽐来看，两者⼏乎没有什么差异。

在这个案例中，并不是⾛索引查找（Index Seek）就真的开销代价⼩很多。

考虑到这⾥数据量较⼩，我使⽤⽹上的⼀个脚本，在AdventureWorks2014数据库构造了⼀个10000000的⼤表，然后顺便做了⼀些测试对⽐
CREATE TABLE dbo.TestLIKESearches
(
ID1 INT
,ID2 INT
,AString VARCHAR(100)
,Value INT
,PRIMARY KEY (ID1, ID2)
);
WITH Tally (n) AS
(
SELECT TOP 10000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT INTO dbo.TestLIKESearches
(ID1, ID2, AString, Value)
SELECT 1+n/500, n%500
,CASE WHEN n%500 > 299 THEN
SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26, 1) +
SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26, 1) +
SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26, 1) +
RIGHT(1000+n%1000, 3) +
SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26, 1) +
SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26, 1) +
SUBSTRING('abcdefghijklmnopqrstuvwxyz', 1+ABS(CHECKSUM(NEWID()))%26, 1)
END
,1+ABS(CHECKSUM(NEWID()))%100
FROM Tally;
CREATE INDEX IX_TestLIKESearches_N1 ON dbo.TestLIKESearches(AString);
如下测试所⽰，在⼀个⼤表上⾯，LIKE @search_string这种SQL写法，IO开销确实要⼩⼀些，CPU Time也要⼩⼀些。

个⼈多次测试都是这种结果。

也就是说对于数据量较⼤的表，这种SQL写法性能确实要好⼀些。

现在回到最开始那个SQL语句，个⼈对执⾏计划有些疑惑，查看执⾏计划，你会看到优化器对CUSTOMER_CD LIKE '%' + @CUSTOMER_CD + '%' 进⾏了转换。

如下截图或通过执⾏计划的XML，你会发现上⾯转换为使⽤三个内部函数LikeRangeStart, LikeRangeEnd, LikeRangeInfo.
seek range (using the runtime value of the @Like variable). Finally, the Nested Loops Join drives the seek using the computed range information as correlated values.
The upper tooltip shows that the Compute Scalar uses three internal functions, LikeRangeStart, LikeRangeEnd, and LikeRangeInfo. The first two functions describe the range as an open interval. The third function returns a set of flags encoded in an integer, that are used internally to define certain seek properties for the Storage Engine. The lower tooltip shows the seek on the open interval described by the result of LikeRangeStart and LikeRangeEnd, and the application of the residual predicate ‘LIKE @Like’.
不管你返回的记录有多少，执⾏计划Nested Loops & Compute Scalar 等步骤的Cost都为0，如下测试所⽰，返回1000条记录，它的成本估算依然为0 ，显然这样是不够精确的。

深层次的原因就不太清楚了。

执⾏计划Cost不可靠的案例很多。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @CUSTOMER_CD NVARCHAR(10);
SET @CUSTOMER_CD=N'%44%'
SELECT * FROM [dbo].[GEN_CUSTOMER] WHERE CUSTOMER_CD LIKE @CUSTOMER_CD
另外，其实还⼀点没有搞清楚的时候在什么条件下出现Index Seek的情况。

有些情况下，使⽤变量的⽅式，依然是索引扫描
不过我在测试过程，发现有⼀个原因是书签查找(Bookmark Lookup：键查找（Key Lookup）或RID查找（RID Lookup）)开销过⼤会导致索引扫描。

如下测试对⽐所⽰：
CREATE NONCLUSTERED INDEX [IX_xriteWhite_N1] ON.[dbo].[xriteWhite]([Item_NO])INCLUDE ([Iden],[WI_CE],[CIE],[Operate_Time])
参考资料：
https:///varund/2009/11/30/index-usage-by-like-operator-query-tuning/
https:///questions/1388059/sql-server-index-columns-used-in-like。