SQL Server 2008 处理隐式数据类型转换在执行计划中的增强

什么是隐式数据类型转换：

当我们在语句的where 条件等式的左右提供了不同数据类型的列或者变量，SQL Server在处理等式之前，将其中一端的数据转换成跟另一端数值的数据类型一致，这个过程叫做隐式数据类型转换。

比如 char(50)=varchar(50), char(50)=nchar(50), int=float, int=char(20) 这些where 条件的等式都会触发隐式数据类型转换。

但是，对于某些数据类型转换过程中，可以转换的方向只是单向的。例如：

如果你试图比较INT和FLOAT的列，INT数据类型必须被转换成FLOAT型 "CONVERT(FLOAT,C_INT) = C_FLOAT".

如果你试图比较char和nchar的列，char数据类型必须被转换成unicode型 "CONVERT(nchar,C_char) = C_nchar"

因此，我们在.net 或者java的程序中，经常容易遇到以下类型的性能问题：

CREATE TABLE [TEST_TABLE] (

[TAB_KEY] [varchar] (5) NOT NULL ,

[Data] [varchar] (10) NOT NULL ,

CONSTRAINT [TEST_TABLE_PK] PRIMARY KEY CLUSTERED

(

[TAB_KEY]

) ON [PRIMARY]

) ON [PRIMARY]

GO

declare @p1 int

set @p1=0

exec sp_prepexec @p1 output,N'@P0 nvarchar(4000)',N'select TAB_KEY,Data from TEST_TABLE where TAB_KEY = @P0',N'0'

select @p1

在这个例子中，表的定义TAB_KEY是varchar型的字段，而程序传递的参数类型为Unicode类型的nvarchar(4000). 当语句执行到where TAB_KEY = @P0，SQL Server会按照如下方式执行：

select TAB_KEY,Data from TEST_TABLE where convert(nvarchar(5),TAB_KEY)=@p0

语句一旦变成这样，TAB_KEY上创建的clustered index就无法快速的查找索引并返回对应的行了。

在这里顺便提一下为什么java程序默认传递unicode类型的参数. 如果使用的是Microsoft JDBC provider，那么这里有个参数sendStringParametersAsUnicode，默认是true. 这个参数是用来控制传递的参数是不是unicode 类型的。如果我们的SQL Server表的数据类型都不是unicode，需要在connection string中声明这个参数是false。https://msdn.microsoft.com/en-us/library/ms378988.aspx

我们使用以下脚本往测试表中插入几千行数据，然后检查执行计划：

declare @i int

set @i=0

while(1=1)

begin

insert into TEST_TABLE values(@i,'a')

set @i=@i+1

end

SQl2008、2005、2000 execution plan

 

Rows Executes StmtText

------------ ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------

1 1 select TAB_KEY,Data from TEST_TABLE where TAB_KEY = @P0

1 1 |--Clustered Index Scan(OBJECT:([aaa].[dbo].[TEST_TABLE].[TEST_TABLE_PK]), WHERE:(CONVERT_IMPLICIT(nvarchar(5),[aaa].[dbo].[TEST_TABLE].[TAB_KEY],0)=[@P0]))

在SQL 2000和2005，该语句得到的执行计划是同样的， SQL Server对测试表TEST_TABLE做了clustered index 扫描，即全表扫描，然后返回一行数据。在这里我们很清楚的看到有个CONVERT_IMPLICIT发生，并且将TAB_KEY转换成了nvarchar(5),由于索引上的列发生了数据类型转换，导致索引保存的数据无法直接用来做比较，因此SQL Server需要将所有行的TAB_KEY扫描转换后跟@p0做比较得到需要返回的数据。

当我们在SQL 2008 中做同样的测试是，我们发现执行计划变了！

Rows Executes StmtText

----------- ------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 1 select TAB_KEY,Data from TEST_TABLE where TAB_KEY = @P0 1 0

1 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))

1 1 |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@P0],[@P0],(62))))

1 1 | |--Constant Scan

1 1 |--Clustered Index Seek(OBJECT:([aa].[dbo].[TEST_TABLE].[TEST_TABLE_PK]), SEEK:([aa].[dbo].[TEST_TABLE].[TAB_KEY] > [Expr1005] AND [aa].[dbo].[TEST_TABLE].[TAB_KEY] < [Expr1006]), WHERE:(CONVERT_IMPLICIT(nvarchar(5),[aa].[dbo].[TEST_TABLE].[TAB_KEY],0)=[@P0]) ORDERED FORWARD)

这个执行计划看起来变得复杂了很多，我们注意到，这里出现了一个操作叫做GetRangeThroughConvert(),在这里，SQL Server由于不能直接对varchar(5)的列用nvarchar(4000)的值进行seek，因此，SQL Server必须将nvarchar转换成varchar。但是由于这个转换可能导致数据丢失，SQL Server采用了另一种做法，首先扩展了一个varchar类型的范围，确保可以转换成我们目标的nvarchar值的varchar数据落在这个范围之内，然后使用这个范围去对index直接做seek。得到了返回的满足范围的少量数据以后，对这个范围内的少量数据进行数据类型转换，然后用来和nvarchar的值比较，最终准确的返回结果集。在这样一个过程中，SQL Server采用了一种迂回的方式使用了index seek而避免了表扫描。

Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert([@P0],[@P0],(62)))) --在这里计算出来范围的两个边界值，然后将语句重写为以下模式：

select TAB_KEY, Data from TEST_TABLE where TAB_KEY>[Expr1005] and TAB_KEY < [Expr1006] and convert(nvarchar(5),TAB_KEY)=@p0

其中“TAB_KEY>[Expr1005] and TAB_KEY < [Expr1006]” 就用来做index seek。

因此，在SQL 2008中我们就不会再次面对由于客户程序定于参数的类型和数据表的数据类型不一致而带来的无法使用index的问题。