代码改变世界

SQL SERVER中隐式转换的一些细节浅析

2016-09-08 15:32  潇湘隐者  阅读(5856)  评论(5编辑  收藏  举报

其实这是一篇没有技术含量的文章,精通SQL优化的请绕道。这个缘起于在优化一个SQL过程中,同事问了我一个问题,为什么SQL中存在隐式转换,但是执行计划没有变? 我思索了一下,觉得这个问题也有点意思,说不定有些对隐式转换了解得不深入的同学都有此疑问,那么下面结合上下文场景做一个细节方面的解答。

我们一个系统中使用了ORMLite框架,粗心的开发人员弄出了不少下面这样的SQL语句,都存在隐式转换问题,如下所示,表machine_stop_alarm_msg 的结构如下,字段machine_no、status都为VARCHAR(10),但是下面SQL,传入的变量@P0,@P1都是NVARCHAR(4000)类型。

 

clipboard

DECLARE  @P0 nvarchar(4000),@P1 nvarchar(4000);
 
SET @P0='1';
SET @P1='K172';
 
SELECT [recid],[machine_no] 
   ,[stop_stime] 
   ,[stop_etime] 
   ,[status] 
   ,[memo] 
   ,[createddate]  
FROM machine_stop_alarm_msg t  
WHERE 1=1  
AND t.status=@P0  
AND t.machine_no in(@P1 )  
ORDER BY machine_no, 
   stop_stime ;  

 

 

machine_stop_alarm_msg 表只有一个聚集索引PK_machine_stop_alarm_msg,字段为recid。

clipboard

 

当时我优化的时候,就觉得这个SQL语句存在两个问题:1 缺少索引; 2 存在隐式转换问题。当时创建了下面索引,并要求开发人员修改SQL,避免隐式转换。

CREATE NONCLUSTERED INDEX ix_machine_stop_alarm_msg_n1
ON [dbo].[machine_stop_alarm_msg] ([machine_no],[status])
INCLUDE ([recid],[stop_stime],[stop_etime],[memo],[createddate])
GO

 

 

在测试环境测试时,我们先不增加这个索引,就出现了下面一个场景,两者都是走聚集索引扫描:

 

1: 执行计划走聚集索引扫描(Cluster Index Scan)

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE  @P0 nvarchar(4000),@P1 nvarchar(4000);
 
SET @P0='1';
SET @P1='K172';
SELECT [recid],[machine_no] 
   ,[stop_stime] 
   ,[stop_etime] 
   ,[status] 
   ,[memo] 
   ,[createddate]  
FROM machine_stop_alarm_msg t  
WHERE 1=1  
AND t.status=@P0  
AND t.machine_no in(@P1 )  
ORDER BY machine_no, 
   stop_stime ;  
 
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

clipboard

clipboard

 

2: 执行计划走聚集索引扫描(Cluster Index Scan)

 

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE  @P0 VARCHAR(10),@P1 VARCHAR(10);
 
SET @P0='1';
SET @P1='K172';
SELECT [recid],[machine_no] 
   ,[stop_stime] 
   ,[stop_etime] 
   ,[status] 
   ,[memo] 
   ,[createddate]  
FROM machine_stop_alarm_msg t  
WHERE 1=1  
AND t.status=@P0  
AND t.machine_no in(@P1 )  
ORDER BY machine_no, 
   stop_stime ;  
 
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

clipboard

 

这里两者的执行计划一样,这个应该很好理解,缺少相关索引,而且发生隐式转换的不是索引所在的字段,那么即使存在隐式转换,它的执行计划是一样的。 这里没有太多要解释的。

那么我们接下来看看看增加了索引后,两者的实际执行计划。

 

clipboard

clipboard

 

现在同事纠结的就是即使发生了隐式转换,为什么执行计划还是走索引查找(Index Seek)呢? 其实很多人有一个误区,SQL Server当中并不是所有的隐式转换都会导致索引扫描(Index Scan),关于这个请见我这篇博客SQL SERVER中什么情况会导致索引查找变成索引扫描 。也就是说隐式转导致索引扫描也是有条件的。这里不再做展开讲,没有太多意思。另外,我们再来对比一下两者的执行计划。

 

上面发生隐式转换的SQL的执行计划,多了一个常量扫描(Constant Scan),常量扫描做的工作是根据用户输入的SQL中的常量生成一个行 ,MSDN的介绍如下:

"The Constant Scan operator introduces one or more constant rows into a query. A Compute Scalar operator is often used after a Constant

Scan to add columns to a row produced by the Constant Scan operator"

 

常量扫描会引入一个或者多个常量行到一个查询中;通常情况下紧跟常量扫描的是计算标量运算符,计算标量运算符会为常量扫描运算符产生的行添加列。

clipboard

如果你想知道执行计划里面的Expr1004、 Expr1005、Expr1003对应啥,看看执行计划就知道了(其中Expr1003为(62),一开始不明其什么意义,后面咨询了宋大神,才知道62是个flag,意思是等于号)

clipboard

 

发生隐式转换的SQL还多了一个Nested Loop(Inner Join)操作。另外,即使这两个SQL依然都是索引查找(Index Seek),但是两种的IO开销还是有所区别的。

clipboard

 

image

 

 

clipboard

 

image