開發(fā)人員對(duì)數(shù)據(jù)庫性能優(yōu)化的深入理解.

1、開發(fā)人員對(duì)數(shù)據(jù)庫性能優(yōu)化的深入理解易車網(wǎng) 王泊 statistics IO on)邏輯讀取物理讀取CPU(set statistics time on)編譯時(shí)間執(zhí)行時(shí)間性能問題的定位邏輯讀取查詢所需訪問從緩存區(qū)頁的次數(shù)物理讀取從磁盤讀取的頁數(shù)性能問題的定位編譯時(shí)間執(zhí)行時(shí)間set statistics time on開發(fā)人員對(duì)數(shù)據(jù)庫性能優(yōu)化的深入理解性能問題的分析執(zhí)行計(jì)劃SQL語句被編譯成執(zhí)行計(jì)劃迭代器內(nèi)存消耗分類、哈希聚合、哈希鏈接非阻斷迭代器與阻斷迭代器計(jì)劃緩存和重編譯計(jì)劃緩存即席查詢自動(dòng)化參數(shù)預(yù)定義查詢，使用sp_executesql或API存儲(chǔ)過程或其他編譯對(duì)象（觸發(fā)器、TVF等等）索引

2、結(jié)構(gòu)聚集索引一表一個(gè)非聚集索引一表多個(gè)掃描和查找掃描掃描查找查找堆表掃描聚集索引聚集索引掃描聚集索引查找非聚集索引索引掃描索引查找索引覆蓋查詢（單列）可覆蓋Col=1Col100Col BETWEEN 1 AND 10Col like 100%Col IN (2,6,7,9)不可覆蓋ABS(Col)=1Col+1=9Col like %abc索引覆蓋查詢（多列）查找命中Col1=1 and Col2=2Col1=1 and Col24剩余謂詞Col1100 and Col2=2Col1 like abc% and Col2=4查找未命中Col2=2Col1+1=9 and Col2 Betwe

3、en 1 and 9Col1like %abc and Col2 in (1,2,3)索引覆蓋查詢（多列）關(guān)鍵列已覆蓋列書簽查找輸出列決定書簽查找索引查找聚集索引查找（RID查找）INNER JOIN連接嵌套循環(huán)合并連接哈希連接嵌套循環(huán)（內(nèi)連接）For each row R1 in the outer tableFor each row R2 in the inner tableIf R1 joins with R2Return (R1,R2)嵌套循環(huán)（外連接）For each row R1 in the outer tableBeginFor each row R2 in the inner

4、 tableIf R1 joins with R2Output（R1,R2）If R1 did not joinOutput（R1,null）End合并連接Get first row R1 from input 1Get first row R2 from input 2While not at the end of either inputBeginIf R1 JOINS WITH R2BEGINOUTPUT(R1,R2)GET NEXT ROW R2 FORM R2EndElse if R1R2Get next row R1 from input 1ElseGet next row R2

5、from input 2End哈希連接For each row R1 in the build tableBeginCalculate hash value on R1 join keysInsert R1 into the appropriate hash bucketEndFor each row R2 in the probe tableBeginCalculate hash value on R2 join keysFor each row R1 in the corresponding hash bucketif R1 join with R2Output (R1,R2)End連接特

6、點(diǎn)對(duì)比嵌套循環(huán)嵌套循環(huán)合并連接合并連接哈希連接哈希連接最佳用于相對(duì)小的輸入，同時(shí)又索引在內(nèi)表的連接鍵上中大型輸入，同時(shí)有索引在等值連接鍵上提供排序或者在連接后排序數(shù)據(jù)倉庫需要中型或大型輸入，可擴(kuò)展的并發(fā)執(zhí)行并發(fā)性支持大的并發(fā)有索引提供排序支持大并發(fā)小并發(fā)阻斷和繼續(xù)否否是需要等值否是是外連接 半連接只有左鏈接全部全部使用tempdb否是（對(duì)多對(duì)多）是需要排序否是否保留排序是（只對(duì)于外輸入）是否支持游標(biāo)是否否聚合聚合函數(shù)聚合種類流聚合哈希聚合聚合聚合函數(shù)COUNTSUMAVGMINMAX流聚合Clear the current aggregate resultsClear the current

7、group by columnsFor each input rowIf the input row does not match the current group by columnsBeginOutput the current aggregate resultClear the current aggregate resultsSet the current group by columns to the input rowEndUpdate the aggregate result with the input rowend哈希聚合For each input rowBeginCal

8、culate hash value on group by columnsCheck for a matching row in the hash tableIf matching row not foundInsert a new row into the hash tableElseUpdate the matching row with the input rowEndOutput all rows in the hash table聯(lián)合Union AllUnion子查詢不相關(guān)vs.相關(guān)子查詢標(biāo)量vs.多行子查詢高級(jí)索引運(yùn)算動(dòng)態(tài)索引運(yùn)算索引聯(lián)合索引交叉索引視圖增刪改INSERTDELETEUPDATE鎖（rowlock、tablelock、slock、xlock）自連接的應(yīng)用表設(shè)計(jì)三范式反范式約束聚集索引的位置非聚集索引的位置計(jì)算列綜合算法分頁TOP顛覆法鏈接方式IN的問題*Hints查詢Hint表Hint*架構(gòu)數(shù)據(jù)庫架