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

                    
                

赞 (0)

打赏 微信扫一扫

相关文章:

• 

  是我绿了么

  家有呆萌小女友、相处三个月没越雷池一步、昨晚一起看电影、拉了一下她的手、她就哭闹着说怀孕了、真的笑死我了！让她相信自己没怀孕、今天故意带她去医院“打胎”、更搞笑... [阅读全文]
• 

  你们检讨吧

  眼看着就十一月份了，怎么还没人给我拜个早年？你们自己检讨一下吧。 [阅读全文]
• 

  小时候大家才是幸福的

  还记得小时候我们都很快乐，因为那个时候我们丑和穷得都还不是那么明显。 [阅读全文]
• 

  我们一起打

  晚上睡觉前和老婆聊天，说到生孩子的问题，我问她：喜欢男孩还是女孩。她说：如果生女孩，我们一起宠爱她，把她打扮的漂漂亮亮的，如果生男孩，我们两就一起打他。。。一起... [阅读全文]
• 

  炼丹炉

  看见烘培的美图，心血来潮买了个烤箱。第一次烘培选了自以为最简单的面包，在面点功夫等于零的情况下折腾了一下午，结果烤好的面包竟然跟石头一样，还是发黑的颜色。。。老... [阅读全文]

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 [e-mail] 举报，一经查实将立刻删除。

发表评论

验证码：