T-SQL之变量导致索引无效

T-SQL之变量导致索引无效

 (一)问题提出

1，在开发中是否遇到一个情况，就是在where后写明具体值时可以用到索引，使用变量时却不行了呢？

2，是否开始怀疑MS SQL 出现了编译问题。

（二）测试过程

1，建立测试数据

CREATE TABLE t_order (
  orderid     INT   IDENTITY ( 1 , 1 )   PRIMARY KEY,
  ordertime   DATETIME,
  productname VARCHAR(50))
GO
--创建索引
CREATE INDEX idx_ordertime ON t_order (
      ordertime)
GO
--插入1000000条记录
WITH cte
     AS (SELECT NUMBER + 1 AS NUMBER
         FROM   master..spt_values a
         WHERE  a.TYPE = 'P'
                AND NUMBER < 1000)
INSERT INTO t_order
           (ordertime,
            productname)
SELECT Getdate() - a.NUMBER,
       LEFT(Newid(),10)
FROM   cte a
       CROSS JOIN cte b
GO

 

2，分别查询

SET STATISTICS io  ON
－－查询一采用变量
DECLARE  @date DATETIME
SET @date = Getdate()
SELECT *
FROM   t_order
WHERE  ordertime > @date
GO
－－查询二采用变量给出具体值
SELECT *
FROM   t_order
WHERE  ordertime > Getdate()

 3，对比执行计划发现相差太太太太大了。

查询1扫描了整个表，查询2确实很好的一个seek加Look up

（三） 原因分析以及验证

1，原因分析

因为当你使用变量时，查询语句在编译时，并不做SET操作。换句话说，即是SET操作是编译完成后，执行的时候才执行。所以编译的时候MS SQL 并不知道◎date的值，所以不能产生一个正确的执行计划。

2,验证

MS SQL在这种情况总按照一个固定的估计值在产生执行计划（即30%），所以做一个全表扫描更划算。让我们来论证一下，我们对该表插入了1000000条记录，按照30% ，所以预估行数就该是300000,查看执行计划，果然如此（注意红色方框）：

 （四）解决方案

解决方案1：（使用option(RECOMPILE)，在执行时重新编译）：

declare @date datetime
set @date=GETDATE()
select * from T_order where ordertime>@date
option(RECOMPILE)

 解决方案2：给定一个参数提示给该查询

declare @date datetime
set @date=GETDATE()
select * from T_order where ordertime>@date
option(OPTIMIZE FOR (@date='2012-04-29'))

  解决方案3：封装成存储过程，有人就会疑问了，为什么存储过程可以呢？在这里大家别把参数和变量混淆了，在SQL SERVER里面写法都一样，但意义不完全一样。存储过程的编译实在第一次执行的时候才产生执行计划。

--创建存储过程
CREATE PROC Sp_select_t_order
           @date DATETIME
AS
  SELECT *
  FROM   t_order
  WHERE  ordertime > @date
GO
--执行存储过程
DECLARE  @date DATETIME
SET @date = Getdate()
EXEC Sp_select_t_order @date

 解决方案4：参数化查询

sp_executesql
N'select * from T_order where ordertime>@date',
N'@date datetime',
@date='2012-04-29'

 

以上四种解决方案的执行计划都如下，实际环境推荐封装成存储过程：