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

1、開發(fā)人員對(duì)數(shù)據(jù)庫(kù)性能優(yōu)化的深化了解易車網(wǎng) 王泊 weibo/brantwong.提綱性能問(wèn)題的出現(xiàn)性能問(wèn)題的定位性能問(wèn)題的分析、處理.開發(fā)人員對(duì)數(shù)據(jù)庫(kù)性能優(yōu)化的深化了解性能問(wèn)題的出現(xiàn).性能問(wèn)題的出現(xiàn)訪問(wèn)量過(guò)多SQL語(yǔ)句性能差業(yè)務(wù)復(fù)雜硬件問(wèn)題.開發(fā)人員對(duì)數(shù)據(jù)庫(kù)性能優(yōu)化的深化了解性能問(wèn)題的定位.性能問(wèn)題的定位來(lái)源IO(set statistics IO on)邏輯讀取物理讀取CPU(set statistics time on)編譯時(shí)間執(zhí)行時(shí)間.性能問(wèn)題的定位邏輯讀取查詢所需訪問(wèn)從緩存區(qū)頁(yè)的次數(shù)物理讀取從磁盤讀取的頁(yè)數(shù).性能問(wèn)題的定位編譯時(shí)間執(zhí)行時(shí)間set statistics time on.開

2、發(fā)人員對(duì)數(shù)據(jù)庫(kù)性能優(yōu)化的深化了解性能問(wèn)題的分析.執(zhí)行方案SQL語(yǔ)句被編譯成執(zhí)行方案迭代器內(nèi)存耗費(fèi)分類、哈希聚合、哈希鏈接非阻斷迭代器與阻斷迭代器.方案緩存和重編譯方案緩存即席查詢自動(dòng)化參數(shù)預(yù)定義查詢，運(yùn)用sp_executesql或API存儲(chǔ)過(guò)程或其他編譯對(duì)象觸發(fā)器、TVF等等.索引構(gòu)造聚集索引一表一個(gè)非聚集索引一表多個(gè).掃描和查找掃描查找堆表掃描聚集索引聚集索引掃描聚集索引查找非聚集索引索引掃描索引查找.索引覆蓋查詢單列可覆蓋Col=1Col100Col BETWEEN 1 AND 10Col like 100%Col IN (2,6,7,9)不可覆蓋ABS(Col)=1Col+1=9Col

3、 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 Between 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

4、 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 tableIf R1 joins with R2OutputR1,R2If R1 did not joinOutputR1,nullEnd.合并銜接Get first row R1 from input 1Get first row R2 from input 2While not at the end of either in

5、putBeginIf R1 JOINS WITH R2BEGINOUTPUT(R1,R2)GET NEXT ROW R2 FORM R2EndElse if R1R2Get next row R1 from input 1ElseGet next row R2 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

6、tableBeginCalculate hash value on R2 join keysFor each row R1 in the corresponding hash bucketif R1 join with R2Output (R1,R2)End.銜接特點(diǎn)對(duì)比嵌套循環(huán)合并連接哈希連接最佳用于相對(duì)小的輸入，同時(shí)又索引在內(nèi)表的連接鍵上中大型輸入，同時(shí)有索引在等值連接鍵上提供排序或者在連接后排序數(shù)據(jù)倉(cāng)庫(kù)需要中型或大型輸入，可擴(kuò)展的并發(fā)執(zhí)行并發(fā)性支持大的并發(fā)有索引提供排序支持大并發(fā)小并發(fā)阻斷和繼續(xù)否否是需要等值否是是外連接 半連接只有左鏈接全部全部使用tempdb否是（對(duì)多對(duì)多）是需要排序

7、否是否保留排序是（只對(duì)于外輸入）是否支持游標(biāo)是否否.聚合聚合函數(shù)聚合種類流聚合哈希聚合.聚合聚合函數(shù)COUNTSUMAVGMINMAX.流聚合Clear the current aggregate resultsClear the current 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

8、 current group by columns to the input rowEndUpdate the aggregate result with the input rowend.哈希聚合For each input rowBeginCalculate 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.結(jié)合Union AllUnion.子查詢不相關(guān)vs.相關(guān)子查詢標(biāo)量vs.多行子查詢.高級(jí)索引運(yùn)算動(dòng)態(tài)索引運(yùn)算索引結(jié)合索引交叉索引視圖.增刪改INSERTDELETEUPDATE鎖rowlock、tablelock、slock、xlock自銜接的運(yùn)用.表設(shè)計(jì)三范式反范式約束聚集索引的位置非聚集索引的位置計(jì)算列.綜合算法分頁(yè)TOP顛覆法鏈接方式