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），测试上面四种情况，如下所示：
其实复杂的情况下，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.
<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'%'" />
&nb
相关文章:
-
-
How to monitor tempdb in MS SQL
Error: tempdb is full due to active_transaction. 参考链接：https://www.mssqltip... [阅读全文] -
SQL Server 更新统计信息出现严重错误，应放弃任何可能产生的结果
一台SQL Server 2008 R2版本（具体版本如下所示）的数据库，最近几天更新统计信息的作业出错，错误如下所示： Microsoft SQL... [阅读全文] -
MySQL 备份之 into outfile 逻辑数据导出（备份） 用法： 无论是什么存储引擎，本身是一种数据导出的方法，同时可以用来辅助备份，它可... [阅读全文]
-
大数据开发最核心的课程就是Hadoop框架，几乎可以说Hadoop就是大数据开发。这个框架就类似于Java应用开发的SSH/SSM框架，都是Apac... [阅读全文]
-
版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。
上一篇： HTML5 引入的新的结构化标签
发表评论