SQLSERVER隐式转换剖析

1、SQL SERVER中隐式转换的一些细节浅析其实这是一篇没有技术含量的文章，精通 SQL优化的请绕道。这个缘起于在优化一个 SQL过程中，同 事问了我一个问题，为什么 SQL中存在隐式转换，但是执行计划没有变？ 我思索了一下，觉得这个 问题也有点意思， 说不定有些对隐式转换了解得不深入的同学都有此疑问， 那么下面结合上下文场景 做一个细节方面的解答。我们一个系统中使用了 ORMLite 框架， 粗心的开发人员弄出了不少下面这样的 SQL语句，都存在隐式 转换问题，如下所示，表 machine_stop_alarm_msg 的结构如下，字段 machine_no 、status 都为 VARCH

2、AR(10)但, 是下面 SQL，传入的变量 P，0 P1都是 NVARCHA(R4000)类型。DECLARE P0 nvarchar(4000),P1 nvarchar(4000);SET P0=1SET P1=K172SELECT recid,machine_no,stop_stime,stop_etime,status,memo,createddateFROM machine_stop_alarm_msg tWHERE 1=1AND t.status=P0AND t.machine_noin (P1 )ORDER BY machine_no, stop_stime ;machine_s

3、top_alarm_msg 表只有一个聚集索引 PK_machine_stop_alarm_msg ，字段为 recid 。当时我优化的时候，就觉得这个 SQL语句存在两个问题： 1 缺少索引； 2 存在隐式转换问题。当时 创建了下面索引，并要求开发人员修改 SQL，避免隐式转换。CREATE NONCLUSTEREDINDEX ix_machine_stop_alarm_msg_n1ON dbo.machine_stop_alarm_msg (machine_no,status)INCLUDE(recid,stop_stime,stop_etime,memo,createddate)GO在测

4、试环境测试时，我们先不增加这个索引，就出现了下面一个场景，两者都是走聚集索引扫描：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,createddateFROM machine_stop_alarm_msg tWHERE 1=1AND t

5、.status=P0AND t.machine_noin (P1 )ORDER BY machine_no,stop_stime ;SET STATISTICS IO OFF; SET STATISTICS TIME OFF;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_etim

6、e ,status ,memo,createddateFROM machine_stop_alarm_msg tWHERE 1=1AND t.status=P0AND t.machine_noin (P1 )ORDER BY machine_no, stop_stime ;SET STATISTICS IO OFF; SET STATISTICS TIME OFF;这里两者的执行计划一样， 这个应该很好理解， 缺少相关索引， 而且发生隐式转换的不是索引所在的 字段，那么即使存在隐式转换，它的执行计划是一样的 。 这里没有太多要解释的。那么我们接下来看看看增加了索引后，两者的实际执行计划。16S

7、ET STATISTICS 10 ON;17SET STATISTICS TIME ON:18DECLARE P0 VARCHAR-： 10). P1 W1920SET P0 1 ：21SET 1=,K172,:22 t1 SELECT recid, machme_no23,stop_stime24,stop_etime25,status26,nemo27,createddate28FROM ftachine_stop_alarm_Bsg t29WHERE 1=130AND t. status P031AND t Machine.no in P132ORDER BY machine。，33st

8、np_stimft3435SET STATISTICS 10 OFF:36SET STATISTICS TIME OFF.100% I ResultsMessagesExecution planQuery 1: Query cost (relative to the batch): 100% SELECT recid,machine no ,stop stime , stop etSELECTCost: 0 %SortCon: 77 %Index Seek (NonClusrered)ma rfiine_stop_alartt_irsg.【iX-=achirk.Cost: 23 %现在同事纠结

9、的就是即使发生了隐式转换，为什么执行计划还是走索引查找(Index Seek)呢？ 其实很多人有一个误区， SQL Server 当中并不是所有的隐式转换都会导致索引扫描( Index Scan ) 也 就是说隐式转导致索引扫描也是有条件的。这里不再做展开讲，没有太多意思。另外，我们再来对比 一下两者的执行计划。上面发生隐式转换的 SQL的执行计划，多了一个常量扫描( Constant Scan )，常量扫描做的工作是 根据用户输入的 SQL中的常量生成一个行 ， MSDN的介绍如下：The Constant Scan operator introducesone or more consta

10、nt rows intoaquery. A Compute Scalar operator is often used after a ConstantScan to add columns to a row produced by the Constant Scan operator 常量扫描会引入一个或者多个常量行到一个查询中；通常情况下紧跟常量扫描的是计算标量运算符， 计算标量运算符会为常量扫描运算符产生的行添加列。如果你想知道执行计划里面的 Expr1004 、 Expr1005 、Expr1003对应啥，看看执行计划就知道了(其 中Expr1003为(62)，一开始不明其什么意义，后

11、面咨询了宋大神，才知道62是个 flag, 意思是等于号)发生隐式转换的 SQL还多了一个 Nested Loop ( Inner Join )操作。另外，即使这两个 SQL依然都是 索引查找( Index Seek )，但是两种的 IO 开销还是有所区别的。67891011121314151617181920212223242526 )%-SET STATISTICS IO ON:SET STATISTICS TIME ON;DECLARE P0 nvarchar(4000).P1 nvarchar(4000):SET(SP0= f :SET Pl二K172:三 SELECT recid.

12、machine_no,stop_stiae,stop.etime status,memo,createddateFROM machine stop alarm msg tWHERE 1=1AND t. status=P0AND t. machine.no in1：Pl )ORDER BY machme.no,stop_stime :SET STATISTICS IO OFF;SET STATISTICS TIME OFFResults J二 Execution planSQL Server执行时间:CPU时间=0毫秒,占用时间=o皇秒。SQL Server执行时间:CPU时间=0亮秒，SQL

13、Strvtr分析和偏译时间：CPU时间=0毫秒，占用时间=10 秒。占用时间=0壷秒。(1111 row (s) affected)表achine.stop.alarmsg。扫描计数1，逻辑读取18次，物理读取0次,|预读0次，lob逻辑读取0次，lob物理读取(1 row(s) affected)SQL Server执行时间：CPU时间=15奎祝占用时间=55奎秒。Physical)pLog .ArgumentDefinedValuesEstimateRowsEstiiwatelOMULLHULLNULLNULL542.4252MULLSortSortORDER BT: (t nachin_

14、no ASC, t.slop_stimt.HULL542.42520.0112612SNested L.In.OUTER REFERENCES: (Exprl0O4, Exprl005r (Expr.HULL542.42520. 009050926Compute Co.DEFINE: (Expr 1004, Exprl005, Expr 1003)=GetR.(Zxprl004,.1 0Constant.CoNULLNULL10Index SekIn.OBJECT: (KCCSDB dbo machine stop alarm msg)t. recid.542.42520. 009050926

15、6 7 8 9 0 1111)22SEI STATISTICS IO OKSEI STATISTICS TIME OR;DECLARE QI?O VARCHAR10). CPI7ABHUR 102324252627282930313233343536SEI OPO二 TSEI &4K】72.SHECT (recxdj. (nachine_no) ,stop.stiMestatus,ne*oere航eddxeFRCNop. al axVHERE 1=1ANI t. status F0AWI t. nachine.no)OKI ER BY Bachme.no, rtop.tiw :SEI STATISTICS IO OFF SEI STATISTICS THE OFFResults -J -wa( ； xcuii&n血Wxr机饥寸目cn时间=o致趴占用时间=o董杪* 沁S“xr号忻和cn Bjfl = 5交秒占用时il = 5交秒。(Lili r () eld)* 心3.，5亠5亠丸打1