SQLSERVER隐式转换剖析

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

2、SQL传入的变量 P0 卩都是NVARCHAR4000)类型。3334 machine.stop_alargResuits 一MessagesColwm_nameTywComputedLengthFreeScalei reci ds bi$i nt-Jnoe190nomachi ne_riLavar charno10noEtopstiimedatetimienosyesslop.etimedatetimeno0yessta曹ar charno10yesfhinovar charno255yesdatetimenoey电sno(n/a)1azimoddatQdatetinie noATrimTr

3、ailingBLaiiks Fix* dLeidfullInS o (n/a)(nA)nonctn/yas(n/ a)HamsOwnerTypeCreateddatetime* machineMstop_user table2C16-03-03 11:50:02 193DECLARE P0 n varchar(4000),P1 n varchar(4000);SET P0=1SET P1 = K172SELECT recid,machi ne_no,stop_stime,stop_etime,status,memo,createddateFROMmachi ne_stop_alarm_msg

4、tWHERE1=1AND t.status=POAND t.machine_no in (P1 )ORDER BY machine_no, stop_stime ;machine_stop_alarm_msg 表只有一个聚集索弓丨 PK_machine_stop_alarm_msg，字段为 recid。323334sp help index ? machine st op al a rm msgTixid&x_descripti onindex_keysI FK_m achi ne_E t op_al m_ib e g dustred nhi g口也 pr imary key lccatd o

5、n PRIHARY racid療派隐耆当时我优化的时候，就觉得这个 SQL语句存在两个问题：1缺少索引；2存在隐式转换问题。当时 创建了下面索引，并要求开发人员修改 SQL避免隐式转换。CREATE NONCLUSTEREDINDEX ix_machine_stop_alarm_msg_n1 ONdbo.machi ne_stop_alarm_msg (machi ne_no ,status)INCLUDE (recid,stop_stime,stop_etime,memo,createddate)GO在测试环境测试时，我们先不增加这个索引，就出现了下面一个场景，两者都是走聚集索引扫描:1:执

6、行计划走聚集索引扫描(Cluster Index Sca n )SET STATISTICS IO ON;SET STATISTICS TIME ONDECLARE P0 n varchar(4000),P1 n varchar(4000);SET P0=1;SET P1 = K172;SELECT recid,machi ne_no,stop_stime,stop_etime,status,memo,createddateFROMmachi ne__stop_alarm_msg tWHERE1=1AND t.status=P0AND t.machine_noin

7、 (P1 )ORDER BY machine_no,stop_stime ;SET STATISTICS IO OFF;SET STATISTICS TIME OFF;151118192D2D222324252621232930 引32閃3435J53733ICO %，SET STATISTICS 10 OH:SET STATISTICS TlfflE ONDECLARE P0 nvarchar (4000，硼 1 nvarchar 4000)SET PCk? 1F .SET SP1= K172P ；-SELECT recidp nachine.no) j stop_stine stop_et

8、inef status、nsmo,createddatejFROM machme_ st op_al arg t INHERE 1-1ANT t, status=P0AND t. iachine_no in (Pl ) ORDER BY Rachine_noi stop_stime .SET STATISTICS 10 OFF; SET STATISTICS TIfflE OFF口 RttZ* | 也旺 j 盯 Ixecutioii planQuery 1： Query cost (relative to the tatch) : 100 SE LECT reeid F Iftachine：

9、no f | stop stiiae , stHICost;: 11 VClusueredma ch 1.TL4H3 g PK m鼻归hi卫Clustered Index ScanClustered Index Scan (Clustered)Seanning a clustered index, tntirdy or only a range.Physical OperationClustered Index ScanLogical OperationClustered Index ScanActual Execution ModeRowEstimated Execution ModeRow

10、StorageRowStoreActual Number of Rows1111Actual Number of Batches0Estimated I/O Cost1.32016Estimaled Operator Cost2.10371 (89%)EstimUd Subtree Cost2,10371Estimated CPU Cost0.283549E&timMed Number of Executions1Number of Executions1ErtimaUd Number of Rows354.346Estimated Row Size179 BActual Rebinds0Ac

11、tual Rewinds0OrderedFalseNode ID2PredicateCONVERTlMPLLCITrwBixhE10KCCDBJclb1=,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。，33stnp_stimft3435SET STATI

12、STICS 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 %6-SET STATISTICS 10 ON;7SET

13、STATISTICS TIME ON;8nDECLARE P0 nvarchar(4000), Pl nvarchar 4000y10SET1 ;11SET P1 K172n ;12-SELECT recid, machine_no13j stop_stime14stop_已time15status16j jTLeJTLLl17creat已山士班:已18FF10M iTLachine_st up_alariTL_jTLSg t19WERE 1 120AND t. status=P021AND t machine_no inPl22ORDER BY machine_no23stup_stime

14、;2425SET STATISTICS 10 OFF;26SET STATISTICS TIME OFF;LOO %-4届 R 苗 ult I 嚣冲 A Ex e cut ion planQuery 1: Query cost (relativ皂 to 匸li皂 Joatcli) :100%SELECT recid r 皿且chine no f 3top 3time f stop etime f status f |memo r createddcma dh in e_sla rirnrs g - ix_ir.achiri.-C-ost: 34 %现在同事纠结的就是即使发生了隐式转换，为什么执

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

16、nto aquery. A Compute Scalar operator is ofte n used after a Con sta ntScan to add colum ns to a row produced by the Con sta nt Scan operator常量扫描会引入一个或者多个常量行到一个查询中；通常情况下紧跟常量扫描的是计算标量运算符， 计算标量运算符会为常量扫描运算符产生的行添加列。Compute ScalarCompute new vlue ifrom existingvalues in a row.Physical OperationCompute Sca

17、larLogical OperationCompute ScalarActual Execution ModeRowEstimated Execution ModeRowActual Number of Rows1Actual Number of Batches0Estimated Operator Cost0 (0%)Estimated I/O Cost0Estimated Subtree Cost0Estimated CPU Cort0Number of Executions1Estimated Number of Executions1Estimated Number of Rows1E

18、stimated Row Size27 BActual Rebinds0Actual Rewinds滦擔隧咅0Node !DOutput ListExprlOC4, ExprlDO5 ExprlOOS如果你想知道执行计划里面的Expr1004、Expr1005、Expr1003对应啥，看看执行计划就知道了(其中Expr1003为(62), 开始不明其什么意义，后面咨询了宋大神，才知道62是个flag,意思是等于号)5 Im tT extSELECT recidR电 stop_stime* stop_etim电* stag. createdldateISrtCOBDER BT; CtASCthA

19、SC)|-=U6st*d Lospsdmer JoinDiqFR 驱陀RHHT骼仃花pHnmi 臥严liYlEl 廉町计個门朗”|onpute Scalar Q1EFIHE (Esrpr 100*1, Esrpr 1.005, EKpri.003)=GifttR8ingeThronghConvert (P1 p(62)|弋口n雲tent Sew|Index Seek (OBJECT: (EECSJ3B. dbo). machine_s.toji_alarmjisg. Lix_machine_stop_alarm_msig_rLl AS tj), SE发生隐式转换的SQL还多了一个Nested

20、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. machine_no,stop_stiae,stop.etime status,memo,createddateF

21、ROM 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 Strvtr分析和偏译时间：CPU时间=0毫秒，占用时间=10 秒。占用时间=0壷秒。(1111 row (s)

22、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_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: (KC