SQLSERVER隱式轉(zhuǎn)換剖析

1、SQL SERVE中隱式轉(zhuǎn)換的一些細(xì)節(jié)淺析其實(shí)這是一篇沒(méi)有技術(shù)含量的文章，精通 SQL優(yōu)化的請(qǐng)繞道。這個(gè)緣起于在優(yōu)化一個(gè) SQL過(guò)程中，同 事問(wèn)了我一個(gè)問(wèn)題，為什么 SQL中存在隱式轉(zhuǎn)換，但是執(zhí)行計(jì)劃沒(méi)有變？我思索了一下，覺(jué)得這個(gè)問(wèn)題也有點(diǎn)意思，說(shuō)不定有些對(duì)隱式轉(zhuǎn)換了解得不深入的同學(xué)都有此疑問(wèn)，那么下面結(jié)合上下文場(chǎng)景做一個(gè)細(xì)節(jié)方面的解答。我們一個(gè)系統(tǒng)中使用了 ORMLite框架，粗心的開(kāi)發(fā)人員弄岀了不少下面這樣的SQL語(yǔ)句，都存在隱式轉(zhuǎn)換問(wèn)題，如下所示，表 machine_stop_alarm_msg 的結(jié)構(gòu)如下，字段 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 表只有一個(gè)聚集索弓丨 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療派隱耆當(dāng)時(shí)我優(yōu)化的時(shí)候，就覺(jué)得這個(gè) SQL語(yǔ)句存在兩個(gè)問(wèn)題：1缺少索引；2存在隱式轉(zhuǎn)換問(wèn)題。當(dāng)時(shí) 創(chuàng)建了下面索引，并要求開(kāi)發(fā)人員修改 SQL避免隱式轉(zhuǎn)換。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在測(cè)試環(huán)境測(cè)試時(shí)，我們先不增加這個(gè)索引，就出現(xiàn)了下面一個(gè)場(chǎng)景，兩者都是走聚集索引掃描:1:執(zhí)

6、行計(jì)劃走聚集索引掃描(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衛(wèi)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 %現(xiàn)在同事糾結(jié)的就是即使發(fā)生了隱式轉(zhuǎn)換，為什么執(zhí)

15、行計(jì)劃還是走索引查找(Index Seek)呢？ 其實(shí)很多人有一個(gè)誤區(qū)，SQL Server當(dāng)中并不是所有的隱式轉(zhuǎn)換都會(huì)導(dǎo)致索引掃描(Index Scan)也 就是說(shuō)隱式轉(zhuǎn)導(dǎo)致索引掃描也是有條件的。這里不再做展開(kāi)講，沒(méi)有太多意思。另外，我們?cè)賮?lái)對(duì)比 一下兩者的執(zhí)行計(jì)劃。上面發(fā)生隱式轉(zhuǎn)換的SQL的執(zhí)行計(jì)劃，多了一個(gè)常量掃描(Constant Scan )，常量掃描做的工作是 根據(jù)用戶輸入的SQL中的常量生成一個(gè)行，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常量掃描會(huì)引入一個(gè)或者多個(gè)常量行到一個(gè)查詢中；通常情況下緊跟常量掃描的是計(jì)算標(biāo)量運(yùn)算符， 計(jì)算標(biāo)量運(yùn)算符會(huì)為常量掃描運(yùn)算符產(chǎn)生的行添加列。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灤擔(dān)隧咅0Node !DOutput ListExprlOC4, ExprlDO5 ExprlOOS如果你想知道執(zhí)行計(jì)劃里面的Expr1004、Expr1005、Expr1003對(duì)應(yīng)啥，看看執(zhí)行計(jì)劃就知道了(其中Expr1003為(62), 開(kāi)始不明其什么意義，后面咨詢了宋大神，才知道62是個(gè)flag,意思是等于號(hào))5 Im tT extSELECT recidR電 stop_stime* stop_etim電* stag. createdldateISrtCOBDER BT; CtASCthA

19、SC)|-=U6st*d Lospsdmer JoinDiqFR 驅(qū)陀RHHT骼仃花pHnmi 臥嚴(yán)liYlEl 廉町計(jì)個(gè)門朗”|onpute Scalar Q1EFIHE (Esrpr 100*1, Esrpr 1.005, EKpri.003)=GifttR8ingeThronghConvert (P1 p(62)|弋口n雲(yún)tent Sew|Index Seek (OBJECT: (EECSJ3B. dbo). machine_s.toji_alarmjisg. Lix_machine_stop_alarm_msig_rLl AS tj), SE發(fā)生隱式轉(zhuǎn)換的SQL還多了一個(gè)Nested

20、Loop (Inner Join )操作。另外，即使這兩個(gè) SQL依然都是 索引查找(Index Seek )，但是兩種的IO開(kāi)銷還是有所區(qū)別的。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執(zhí)行時(shí)間:CPU時(shí)間=0毫秒,占用時(shí)間=o皇秒。SQL Server執(zhí)行時(shí)間:CPU時(shí)間=0亮秒，SQL Strvtr分析和偏譯時(shí)間：CPU時(shí)間=0毫秒，占用時(shí)間=10 秒。占用時(shí)間=0壷秒。(1111 row (s)

22、affected)表achine.stop.alarmsg。掃描計(jì)數(shù)1，邏輯讀取18次，物理讀取0次,|預(yù)讀0次，lob邏輯讀取0次，lob物理讀取(1 row(s) affected)SQL Server執(zhí)行時(shí)間：CPU時(shí)間=15奎祝占用時(shí)間=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