关于ＳＱＬ中使用变量参数时执行效率的问题

Question

例如语句：
sql1:
declare @s nvchar(30)
set @s='ss'
select * from table1 where c1=1 and (@s='' or c2=@s)
sql2:
select * from table1 where c1=1 and ('ss'='' or c2='ss')
从结构上来看实际上是一模一样的ＳＱＬ结构，但是在执行的时候发现两者效率相差极大
无外界影响情况下ＳＱＬ２在＜１秒出结果，ＳＱＬ２却需要１８秒从执行计划看
ＳＱＬ１使用的clustered index scan，预计行数是表全集
ＳＱＬ２使用的clustered index seek，预计行数为１条记录请问这个是什么原理导致的，该如何解决呢？

Answer 1

的确有这样的问题,以前也遇到过.sql1只有改成:
exec('select * from table1 where c1=1 and ('''+@s+'''='''' or c2='''+@s+''')')
才和sql2等效.

Answer 2

sql1: 
select * from table1 where c1=1 and (ss='' or c2=@s) sql2:
select * from table1 where c1=1 and ('ss'='' or c2='ss')
这2句的条件不一样，当然效率有差别，虽然说结构一样

Answer 3

汗，写得有点错误sql1: 
select * from table1 where c1=1 and (ss='' or c2=ss) sql2: 
select * from table1 where c1=1 and ('ss'='' or c2='ss') 

Answer 4

如果你想达到sql server的正常优化,也只能使用拼成串.再exec
为避免注入,我一般是在拼完的串外再加 select * from (已拼好串)as a
就算对方注入,这样直接语法错误,而无法执行注入sql.

Answer 5

把攻击代码存到了数据库
-------------
存在库里也就只有单引号可以截断sql,那么replace成两个单引号不就可以了?

Answer 6

select * from table1 where c1=1 and (@s='' or c2=@s) 理论上说，因为含有 OR 关键字，在Compile阶段，优化器没办法确定变量的值，所以无法使用静态的索引查找, 生成不了
简单的使用索引查找的计划。有两个方法可以参考修改:1)
  IF @S=''
      SELECT * FROM Table WHERE C1=1
  ELSE 
      SELECT * FROM Table WHERE C1=1 AND C2=@s2)
  SELECT * FROM Table WHERE C1=1 AND C2=@s
  UNION 
  SELECT * FROM Table WHERE C1=1 AND @s=''

Answer 7

刚才测试了一下80W数据量，的确是这样，我想这种情况可能是因为查询条件中存在变量的话，SQL没办法优化，所以没办法走索引

Answer 8

SQL1跟SQL2的结构尽管一样，但是SQL2中条件中使用的是常量,
所以在Complie阶段就可以确定使用索引查找. 但SQL1因为使用了变量而且含有OR 关,所以不能简单的使用静态索引查找。
舉个很简单的例子,(Northwind资料库，Customers表)
有以下SQL:Declare @s nvarchar(30),@t nvarchar(30)
set @s='BONAP'
SET @t=''select * from Customers where CustomerID =@s OR CustomerID =@T--
在编译这条语句时，优化器是没办法知道@s和@t的值的，它们的值是在运行时确定的。
Index Seek(CustomerID='BONAP' OR CustomerID='' )操作的底层实际上是这样子的:先按第一个条件走一次索引，取出匹配的行，然後再按OR後面的条件走一次索引，再取出匹配的行。
如果CustomerID=@s OR CustomerID=@t 强行走Index Seek,万一@s跟@t的值相同，就会可能出同时取出两笔一样的资料，这样的结果不是我们想要的。所以你仔细看这条语句生成的执行计划，它会扫描@s跟@t的值，合并成资料表，然後再去除重复的值，再进行索引查找。这个过程叫叫Dynamic Index Seek.记住一点就够了，执行计划是在编译阶段生成。