开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL Server中LIKE %search_string% 走索引查找(Index Seek)浅析

简介: 原文:SQL Server中LIKE %search_string% 走索引查找(Index Seek)浅析   在SQL Server的SQL优化过程中,如果遇到WHERE条件中包含LIKE '%search_string%'是一件非常头痛的事情。
+关注继续查看
原文:SQL Server中LIKE %search_string% 走索引查找(Index Seek)浅析

 

在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),测试上面四种情况,如下所示:

 

clip_image001

 

 

clip_image002

 

 

clip_image003

 

clip_image004

 

 

其实复杂的情况下,LIKE 'search_string%'也有走索引扫描(Index Scan)的情况,上面情况并不是唯一、绝对的。如下所示

 

在表Person.Person的 rowguid字段上创建有唯一索引AK_Person_rowguid

 

 

clip_image005

 

 

那么我们来看看上面所说的这个特殊案例(这里使用一个现成的案例,懒得构造案例了),如何让LIKE %search_string%走索引查找(Index Seek),这个技巧就是使用变量,如下SQL对比所示:

 

如下所示,表[dbo].[GEN_CUSTOMER]在字段CUSTOMER_CD有聚集索引。

 

 

clip_image006

 

 

可以看到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)就真的开销代价小很多。

 

 

clip_image007

 

 

考虑到这里数据量较小,我使用网上的一个脚本,在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写法性能确实要好一些。

 

clip_image008

 

clip_image009

 

 

现在回到最开始那个SQL语句,个人对执行计划有些疑惑,查看执行计划,你会看到优化器对CUSTOMER_CD LIKE '%' + @CUSTOMER_CD + '%' 进行了转换。如下截图或通过执行计划的XML,你会发现上面转换为使用三个内部函数LikeRangeStart, LikeRangeEnd,  LikeRangeInfo.

 

clip_image010

 

<OutputList>
                    <ColumnReference Column="Expr1007" />
                    <ColumnReference Column="Expr1008" />
                    <ColumnReference Column="Expr1009" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1007" />
                        <ScalarOperator ScalarString="LikeRangeStart((N'%'+[@CUSTOMER_CD])+N'%')">
                          <Identifier>
                            <ColumnReference Column="ConstExpr1004">
                              <ScalarOperator>
                                <Intrinsic FunctionName="LikeRangeStart">
                                  <ScalarOperator>
                                    <Arithmetic Operation="ADD">
                                      <ScalarOperator>
                                        <Arithmetic Operation="ADD">
                                          <ScalarOperator>
                                            <Const ConstValue="N'%'" />
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="@CUSTOMER_CD" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Arithmetic>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="N'%'" />
                                      </ScalarOperator>
                                    </Arithmetic>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="" />
                                  </ScalarOperator>
                                </Intrinsic>
                              </ScalarOperator>
                            </ColumnReference>
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Expr1008" />
                        <ScalarOperator ScalarString="LikeRangeEnd((N'%'+[@CUSTOMER_CD])+N'%')">
                          <Identifier>
                            <ColumnReference Column="ConstExpr1005">
                              <ScalarOperator>
                                <Intrinsic FunctionName="LikeRangeEnd">
                                  <ScalarOperator>
                                    <Arithmetic Operation="ADD">
                                      <ScalarOperator>
                                        <Arithmetic Operation="ADD">
                                          <ScalarOperator>
                                            <Const ConstValue="N'%'" />
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="@CUSTOMER_CD" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Arithmetic>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="N'%'" />
                                      </ScalarOperator>
                                    </Arithmetic>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="" />
                                  </ScalarOperator>
                                </Intrinsic>
                              </ScalarOperator>
                            </ColumnReference>
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Column="Expr1009" />
                        <ScalarOperator ScalarString="LikeRangeInfo((N'%'+[@CUSTOMER_CD])+N'%')">
                          <Identifier>
                            <ColumnReference Column="ConstExpr1006">
                              <ScalarOperator>
                                <Intrinsic FunctionName="LikeRangeInfo">
                                  <ScalarOperator>
                                    <Arithmetic Operation="ADD">
                                      <ScalarOperator>
                                        <Arithmetic Operation="ADD">
                                          <ScalarOperator>
                                            <Const ConstValue="N'%'" />
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="@CUSTOMER_CD" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Arithmetic>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="N'%'" />
                                      </ScalarOperator>
                                    </Arithmetic>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="" />
                                  </ScalarOperator>
                                </Intrinsic>
                              </ScalarOperator>
                            </ColumnReference>
                          </Identifier>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>

 

 

另外,你会发现Nested Loops & Compute Scalar 等步骤的Cost都为0.后面在Dynamic Seeks and Hidden Implicit Conversions这篇博客里面看到了一个新名词Dynamic Seeks。文字提到因为成本估算为0,所以,你看到的执行计划的Cost又是不准确的,具体描述如下:

 

The plan now contains an extra Constant Scan,  a Compute Scalar and a Nested Loops Join.  These operators are interesting because they have zero cost estimates: no CPU, no I/O, nothing.  That’s because they are purely architectural: a workaround for the fact that SQL Server cannot currently perform a dynamic seek within the Index Seek operator itself.  To avoid affecting plan choices, this extra machinery is costed at zero.

The Constant Scan produces a single in-memory row with no columns.  The Compute Scalar defines expressions to describe the covering 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的情况。有些情况下,使用变量的方式,依然是索引扫描

 

 

clip_image011

 

 

不过我在测试过程,发现有一个原因是书签查找(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])

 

 

clip_image012

 

 

 

 

 

 

参考资料:

 

http://sqlblog.com/blogs/paul_white/archive/2012/01/18/dynamic-seeks-and-hidden-implicit-conversions.aspx

https://blogs.msdn.microsoft.com/varund/2009/11/30/index-usage-by-like-operator-query-tuning/

https://sqlperformance.com/2017/02/sql-indexes/seek-leading-wildcard-sql-server

https://stackoverflow.com/questions/1388059/sql-server-index-columns-used-in-like

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL server 2008 最小化安装(学习专用)
之后下载这两个安装包,根据电脑实际情况下载,这里我选择x64版本的两个轻量级安装包,其他版本比较大,学习的话这两个版本就够了
0 0
VS2017调用SQL server 2019 和C++连接语句字符串
VS2017调用SQL server 2019 和C++连接语句字符串
0 0
函数计算 Python 连接 SQL Server
函数计算 Python 连接 SQL Server 自制脑图
0 0
SQL Server 2000 函数使用---CAST 和 CONVERT
日期格式样式,借以将 datetime 或 smalldatetime 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型);或者字符串格式样式,借以将 float、real、money 或 smallmoney 数据转换为字符数据(nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型)。
0 0
【Sql Server】进阶之行数据转为列显示
在开发系统维护阶段,经常需要进行各种数据统计,各种报表之类的。 这个时候,行数据转数据显示就发挥作用了。 场景:行数据的某列值想作为字段列显示
0 0
【Sql Server】时间转换和查询时间范围查询不正确的原因
最近在做时间方法封装的时候发现了一个问题! 如果sql语句输出的时间字段转为了字符串输出,那么在使用此字段作为时间范围筛选时发现无效了,没法过滤对应的时间范围内记录
0 0
【Sql Server】基础之统计库龄语句,仅作为语句使用
知识点的综合使用 分组、数据转换、Case when then、max、min、count、sum、left join多表关联等知识点
0 0
【Sql Server】sql语句文件组分区函数分组方案对应分区表的简单步骤
本篇文章中,主要讲讲sql server数据库中通过sql语句方式对组分区函数的使用 在实际项目中,sql server数据库中有分区的概念,因为在一个表存在大量数据的情况下,需要通过分区方式保存数据来提供查询性能
0 0
【Sql Server】存储过程通过定时执行添加记录作业
通过上篇了解了什么是存储过程,创建存储过程的方法,以及调用存储过程的方法 本次将通过数据库中的作业功能,进行定时执行存储过程,这样就可以完成我们刚开始假设的场景
0 0
【Sql Server】创建触发器,以及通过触发器来统计阅读数量(下)
在本篇文章中,主要来讲讲数据库中的触发器功能 在实际项目中,触发器多数用到在数据添加、修改、删除时,额外去做的一些操作,比如:数据删除了,会通过触发器将删除数据备份到另一个表,再或者文章被阅读,通过触发器更新阅读数量
0 0
+关注
杰克.陈
一个安静的程序猿~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Server在电子商务中的应用与实践
立即下载
SQL Server云化思考与实践
立即下载
RDS SQL Server CPU高使用率性能优化
立即下载