由于在实际java开发中的字段类型无法与数据库一一匹配，且java很多通用框架，由于为了兼顾字符类型大多采用NVARCHAR(4000)这样的设置，导致很多数据库低于此优先级的字段类型匹配的时候做了大量隐式转换，导致性能低下，甚至是一些索引失效。所以我做了一个数据库面向开发语句的通用框架，提供调用方法和接口给开发者，开发者不用考虑字段类型如何定义。接口会自动实现对应字段条件的匹配。

但是实际过程中发现，可以解决大部分索引失效等问题。但是如果遇到like做为查询条件的时候会还是有索引失效的问题。摸索之后找到解决办法 但是原因不明。我来整理下过程，看哪位高手能指点指点。

 

首先我说明一下环境，以及语句

1.表有400万数据左右。

2.like的字段为ticketserialno,聚集索引字段,%只出现在最后（因为我的ticketserialno是以时间为开头的字符再加一些特定的随机字符组成，所以我的按照时间查询来做的查询可以借用聚集索引的seek,缩小查找范围.）

3.分页语句。

4.考虑通用性与安全性，框架对字符串的条件应用必须要写成参数模式，而不能做简单的字符拼接。

现在开始：

1.普通写法

 

  1. dbcc dropcleanbuffers; 
  2. declare @p0 varchar(32),@p1 datetime,@p2 datetime 
  3. set @p0='20130321%' 
  4. set @p1='20130321 00:00:000' 
  5. set @p2='20130321 23:00:000' 
  6.  
  7. select top (15) val from ( 
  8. select top (1*15) ticketSerialNo val,  
  9. case ROW_NUMBER() over (order by ticketSerialNo asc)%15  
  10. when 0 then round (ROW_NUMBER() over (order by  ticketSerialNo asc)/15,0)  
  11. else round (ROW_NUMBER() over (order by ticketSerialNo asc)/15,0)+1 end as pagenum 
  12. from MatchTicket  with(nolock)  WHERE 1=1  AND ticketSerialNo like '20130321%' AND anteTime >= @p1 AND anteTime <= @p2 
  13. order by ticketSerialNo asc 
  14. ) splpage  
  15. where pagenum=1 

执行io,time

 

  1. (15 行受影响) 
  2. 表 'MatchTicket'。扫描计数 1，逻辑读取 29 次，物理读取 26 次，预读 2416 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 
  3.  
  4.  SQL Server 执行时间: 
  5.    CPU 时间 = 0 毫秒，占用时间 = 437 毫秒。 

 

2.直接编写方式 条件是用参数替代原来的字符串

 

  1. dbcc dropcleanbuffers; 
  2. declare @p0 varchar(32),@p1 datetime,@p2 datetime 
  3. set @p0='20130321%' 
  4. set @p1='20130321 00:00:000' 
  5. set @p2='20130321 23:00:000' 
  6.  
  7. select top (15) val from ( 
  8. select top (1*15) ticketSerialNo val,  
  9. case ROW_NUMBER() over (order by ticketSerialNo asc)%15  
  10. when 0 then round (ROW_NUMBER() over (order by  ticketSerialNo asc)/15,0)  
  11. else round (ROW_NUMBER() over (order by ticketSerialNo asc)/15,0)+1 end as pagenum 
  12. from MatchTicket  with(nolock)  WHERE 1=1  AND ticketSerialNo like @p0 AND anteTime >= @p1 AND anteTime <= @p2 
  13. order by ticketSerialNo asc 
  14. ) splpage  
  15. where pagenum=1 

执行结果和time,io

 

  1. (15 行受影响) 
  2. 表 'MatchTicket'。扫描计数 3，逻辑读取 118944 次，物理读取 1139 次，预读 123117 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 
  3.  
  4.  SQL Server 执行时间: 
  5.    CPU 时间 = 2593 毫秒，占用时间 = 23625 毫秒。 

我想大家看见了吧，两次执行 物理读取次数差异异常巨大，而且时间也由400ms变成了23625ms了，

我查看了执行计划，之前那次查询时直接做的Clustered Index SEEK,而改用参数之后，就发生了变化 不是使用seek 而是采用 Clustered Index Scan 导致全表扫描，性能低下。

此时如果我把框架代码改为字符串拼接，性能问题是可以解决，但是安全性和防注入必然会有所欠缺，所以我继续寻求解决方案。然后发现使用如下代码可以解决性能问题。代码如下

 

  1. dbcc dropcleanbuffers; 
  2. declare @p0 varchar(32),@p1 datetime,@p2 datetime 
  3. set @p0='20130321%' 
  4. set @p1='20130321 00:00:000' 
  5. set @p2='20130321 23:00:000' 
  6.  
  7.  
  8. EXEC SP_EXECUTESQL N'dbcc dropcleanbuffers ;set statistics io,time on; select top (15) val from ( 
  9. select top (1*15) ticketSerialNo val,  
  10. case ROW_NUMBER() over (order by ticketSerialNo asc)%15  
  11. when 0 then round (ROW_NUMBER() over (order by  ticketSerialNo asc)/15,0)  
  12. else round (ROW_NUMBER() over (order by ticketSerialNo asc)/15,0)+1 end as pagenum 
  13. from MatchTicket  with(nolock)  WHERE 1=1  AND ticketSerialNo like @p0 AND anteTime >= @p1 AND anteTime <= @p2 
  14. order by ticketSerialNo asc 
  15. ) splpage  
  16. where pagenum=1',N'@p0 varchar(32),@p1 datetime,@p2 datetime',@p0,@p1,@p2 
执行io和耗时
  1. (15 行受影响) 
  2. 表 'MatchTicket'。扫描计数 5，逻辑读取 2382 次，物理读取 38 次，预读 2432 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 
  3.  
  4.  SQL Server 执行时间: 
  5.    CPU 时间 = 125 毫秒，占用时间 = 895 毫秒。 


明显较第二种 带参数的方式性能好很多依然使用的为 聚集索引查找。




次方式可以保证带入的是参数，但是为什么会和第二种查询方式有这么大的差异？


 


又为什么第二种的查询方式会比第一种差那么多？执行计划中几乎失效了 seek。




总结一下


性能


直接字符串拼接 和 sp_executesql都能用到聚集索引seek


但是 直接使用 sql语句 带上参数就不行 也就是 exec(@字符串方式。)


另外 还发现：


如果条件中只有  WHERE 1=1  AND ticketSerialNo like @p0


后面的条件不写  第二 和第三种 编写 方式 都回无法 使用 索引seek 而直接使用 聚集索引全表扫描。


原因无法得知，而实际使用中如果正常我们使用like 会比直接 写时间范围快很多，但是由于这个原因我们是不是只能放弃like方式呢？


请高手要是看见了能不吝赐教。感激不尽。