当前位置： 移动技术网 > IT编程>数据库>MSSQL > SQL Server中LIKE %search_string% 走索引查找（Index Seek）浅析

SQL Server中LIKE %search_string% 走索引查找（Index Seek）浅析

2018年10月18日  | 移动技术网IT编程  | 我要评论

大岛薰,邪恶力量第三季下载,邢台二手货车百姓网

 

在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

                    

                        

                            
                        

                    

                    


                    
                    

                        
    
                            
  • 
                                
                            
    • 
                        

                    


                    
                    

                    

                        
您可能感兴趣的文章:

                        
                    

                    

                        如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！
                        点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网