數(shù)據(jù)庫系統(tǒng)原理與設(shè)計實驗教程 第4版 課件 第8章 數(shù)據(jù)庫查詢執(zhí)行計劃

第1頁第8章數(shù)據(jù)庫查詢執(zhí)行計劃數(shù)據(jù)庫系統(tǒng)原理實驗教程第4版第2頁8.1相關(guān)知識8.1.1SQL優(yōu)化器的優(yōu)化過程8.1.2執(zhí)行計劃

8.1.3執(zhí)行計劃展示方式8.1.4SQLServer所使用的邏輯和物理運算符8.2實驗十六執(zhí)行計劃8.2.1實驗?zāi)康呐c要求8.2.2實驗案例8.2.3實驗內(nèi)容目錄8.1相關(guān)知識SQLServer使用基于代價的查詢優(yōu)化器自動優(yōu)化查詢操作。

對于優(yōu)化器，輸入是一條查詢語句，輸出是一個執(zhí)行策略。該執(zhí)行策略是執(zhí)行這個查詢所需要的一系列步驟，數(shù)據(jù)庫的執(zhí)行代價體現(xiàn)在這個優(yōu)化算法上。

查詢優(yōu)化經(jīng)過了3個階段：查詢分析、索引選擇、合并選擇。第3頁8.1.1SQL優(yōu)化器的優(yōu)化過程首先對查詢的每條子句進(jìn)行語法分析判定是否能夠使用該子句限制查詢必須掃描的數(shù)據(jù)量，該子句可用作索引中的一個查找參數(shù)其次在對查詢進(jìn)行語法分析，找出全部查找參數(shù)后，查詢優(yōu)化器判定在查找參數(shù)上是否存在索引，并決定索引的有效性最后優(yōu)化器得出一個查詢執(zhí)行計劃查詢優(yōu)化器估算執(zhí)行該計劃的開銷8.1.2執(zhí)行計劃在SSMS中，可顯示執(zhí)行計劃。從查詢菜單選擇“顯示執(zhí)行計劃”，則執(zhí)行計劃會在結(jié)果面板窗口以圖形形式顯示出來。執(zhí)行計劃中的邏輯運算符和物理運算符描述了一個查詢或更新是如何被執(zhí)行的。物理運算符說明了用于處理一條語句例如，掃描一個聚集索引，所使用的是物理實現(xiàn)算法。執(zhí)行一條查詢或更新語句的每一步都包括一個物理運算。邏輯運算符說明了用于處理一條語句例如，執(zhí)行一個總計，所使用的是關(guān)系代數(shù)操作。并非每條查詢或更新所需要的所有步驟都包含邏輯運算符。8.1.2執(zhí)行計劃SQLServer查詢分析器的特點(1)物理運算符用于運算，例如哈希連接或嵌套循環(huán)。(2)邏輯運算符匹配物理運算符。如果邏輯運算符與物理運算符不同，它將被列在物理運算符后面。(3)估算行計數(shù)運算，輸出行的數(shù)目。(4)估算行大小，即估算每行輸出的大小。(5)估算I/O開銷，即估算全部I/O活動的開銷。(6)估算CPU開銷，即估算全部CPU活動的開銷。(7)估算在查詢期間執(zhí)行行的數(shù)目，運算執(zhí)行的次數(shù)。(8)估算查詢優(yōu)化器執(zhí)行某一查詢操作的開銷，包括該操作的開銷在整個查詢開銷中所占的百分比。(9)估算查詢優(yōu)化器執(zhí)行某一查詢操作以及同一子樹中先前操作的全部開銷。(10)參數(shù)查詢使用的判定和參數(shù)。8.1.3執(zhí)行計劃展示方式查看執(zhí)行計劃，在SQLServer2005版本以上，系統(tǒng)提供了三種展示方式：圖像方式、文本方式和XML方式。1．圖像方式[例8.1]：查詢單價高于3000元的商品編號、商品名稱、訂貨數(shù)量和訂貨單價。

SELECT

a.productNo,productName,quantity,priceFROMProducta,

OrderDetailbWHERE

a.productNo=b.productNo

ANDprice>3000ORDER

BY

productName

查詢所產(chǎn)生的預(yù)估執(zhí)行計劃，將其分成不同的運算符進(jìn)行組合，從右側(cè)的聚集索引掃描（indexscan）到最左側(cè)的結(jié)果輸出（select）。注意：圖中箭頭的方向指向的是數(shù)據(jù)的流向，箭頭線的粗細(xì)表示了數(shù)據(jù)量的大小

在圖形化執(zhí)行計劃中，每一個不同的運算符都有自身的屬性值，把鼠標(biāo)移至運算符圖標(biāo)上查看2．文本方式文本方式采用豎線（|）標(biāo)示子運算符和當(dāng)前運算的子父關(guān)系，數(shù)據(jù)流方向都是從子運算符流向父運算符的

在SSMS中需要用語句開啟，開啟的方式有兩種：只開啟|關(guān)閉執(zhí)行計劃，不包括詳細(xì)的評估值。執(zhí)行命令：SETSHOWPLAN_TEXTON|OFF開啟|關(guān)閉所有的執(zhí)行計劃明細(xì)，包括各個屬性的評估值。執(zhí)行命令：SETSHOWPLAN_ALLON|OFF[例8.2]：查找訂購總金額在5000元以上的客戶編號、客戶名稱和訂購總金額。SELECT

a.customerNo

,customerName,sum(orderSum)

訂購總金額FROMCustomera,OrderMasterbWHERE

a.customerNo=b.customerNoGROUP

BY

a.customerNo

,customerNameHAVING

sum(orderSum)>=5000執(zhí)行過程是從最里面的運算符開始執(zhí)行，數(shù)據(jù)流方向也是依次從子運算符流向父運算符3．XML方式XML方式結(jié)合文本和圖形方式的優(yōu)點，利用SQLServer2019中的XML的數(shù)據(jù)類型和內(nèi)置XQuery功能進(jìn)行查詢。此方式尤其對與超大型的查詢計劃查看非常的方便。執(zhí)行命令：SETSTATISTICSXMLON|OFF開啟|關(guān)閉[例8.3]：查詢單價高于3000元的商品編號、商品名稱、訂貨數(shù)量和訂貨單價SELECT

a.productNo,productName,quantity,priceFROMProducta,

OrderDetailbWHERE

a.productNo=b.productNo

ANDprice>3000ORDER

BY

productName單擊輸出XML文件點擊XML鏈接地址，再右擊鼠標(biāo)，出現(xiàn)XML方式展現(xiàn)了非常詳細(xì)的查詢計劃信息，簡單分析如下：

StmtSimple：描述了T-SQL的執(zhí)行文本，詳細(xì)分析了該語句的類型，以及各個屬性的評估值。

StatementSetOptions：描述該語句的各種屬性值的Set值

QueryPlan：是詳細(xì)的執(zhí)行計劃，包括執(zhí)行計劃的并行的線程數(shù)、編譯時間、內(nèi)存占有量等

OutputList：輸出參數(shù)列表，在中間部分是具體的不同的執(zhí)行運算符的信息了，且包括詳細(xì)的預(yù)估值等Sort：排序?qū)傩?.1.4SQLServer所使用的邏輯和物理運算符教材中的表8-2列出了SQLServer所使用的部分邏輯和物理運算符。在SQL查詢分析器中從右到左、從上到下讀取圖形執(zhí)行計劃輸出顯示所分析的批處理內(nèi)的每個查詢包括每個查詢的成本占批處理總成本的百分比樹結(jié)構(gòu)內(nèi)的每個節(jié)點都用一個圖標(biāo)表示，指定用于執(zhí)行部分查詢或語句的邏輯運算符和物理運算符。每個節(jié)點都與一個父節(jié)點相關(guān)。具有相同父節(jié)點的節(jié)點都繪制在相同的列內(nèi)。用箭頭將每個節(jié)點連接到其父節(jié)點。[例8.4]統(tǒng)計客戶數(shù)量SELECTcount(*)FROMCustomer此查詢生成4個運算符，每一個運算符會有兩個屬性影響其執(zhí)行的效率1）內(nèi)存消耗

所有運算符都需要一定的內(nèi)存來完成。當(dāng)一條T-SQL語句編譯生成查詢計劃后，SQLServer會選擇最優(yōu)的查詢計劃去固定內(nèi)存，目的是為了再次執(zhí)行時不需重新申請內(nèi)存，加快執(zhí)行速度。但有些運算符需額外的內(nèi)存來存儲行數(shù)據(jù)，所需內(nèi)存量和處理的數(shù)據(jù)行數(shù)成正比。出現(xiàn)如下情況則會導(dǎo)致內(nèi)存不能申請到，影響執(zhí)行性能：(1)如果服務(wù)器上正在執(zhí)行其它的類似的內(nèi)存消耗巨大的查詢，導(dǎo)致系統(tǒng)內(nèi)存剩余不足的時候，當(dāng)前的查詢就得延遲進(jìn)行，直接影響性能。(2)當(dāng)并發(fā)量過大的的情況下，多個查詢競爭有限的內(nèi)存資源，服務(wù)器會適當(dāng)?shù)目刂撇l(fā)和減少吞吐量來維護(hù)性能，這樣也會影響性能(3)如果申請到的可用內(nèi)存很少，SQLServer通常使用Tempdb臨時庫進(jìn)行操作，這個過程會很慢，如耗盡Tempdb磁盤空間則以失敗結(jié)束。比較消耗內(nèi)存的運算符主要有分類、哈希連接以及哈希聚合等連接操作

2）阻斷運算和非阻斷運算阻斷和非阻斷的區(qū)別：運算符是否在輸入數(shù)據(jù)時能直接輸出結(jié)果數(shù)據(jù)。(1)當(dāng)一個運算符在接收輸入的同時生成輸出行，這種運算符是非阻斷式的。如常用的

SelectTop...操作。(2)當(dāng)一個運算符所產(chǎn)生的輸出結(jié)果需要等待所有的數(shù)據(jù)輸入的時，這個操作運算就是阻斷運算的。如Count(*)操作需等待所有的數(shù)據(jù)行輸入才能計算出。提示：并不是所有的阻斷式操作都要消耗內(nèi)存，如Count(*)就為阻斷式，但它不消耗內(nèi)存。我們要盡量使用非阻斷式操作來代替阻斷式操作，這樣才能更好的提高相應(yīng)時間，如使用EXISTS子查詢來判斷，比用HAVINGcount(*)>0的速度要理想的多。邏輯運算符和物理運算符詳見教材案例詳見教材8.2實驗十六執(zhí)行計劃 8.2.1實驗?zāi)康呐c要求(1)掌握SQL查詢語句的執(zhí)行過程。(2)熟練使用“顯示執(zhí)行計劃”功能，查看并分析SQL語句的執(zhí)行過程。(3)能夠運用執(zhí)行計劃的結(jié)果對SQL語句進(jìn)行優(yōu)化。8.2.2實驗案例1．實驗環(huán)境(1)啟動SQLServerManagementStudio。(2)選擇要操作的數(shù)據(jù)庫，如訂單數(shù)據(jù)庫OrderDB。(3)在查詢窗口輸入一條SQL語句。(4)單擊“顯示估計的執(zhí)行計劃”按鈕單擊“顯示估計的執(zhí)行計劃”按鈕8.2.2實驗案例2．實驗案例[例8.7]查看執(zhí)行計劃和所花費的成本。SELECTproductNameROMOrderDetailb,ProductaWHEREb.productNo=a.productNo將鼠標(biāo)放在某個物理或邏輯運算符中，如右圖所示?！跋ⅰ笨蝻@示：8.2實驗十六執(zhí)行計劃 8.2.2實驗案例2．實驗案例[例8.8]查找訂購了“華為手環(huán)B3”的商品的客戶編號、客戶名稱、訂單編號、訂貨數(shù)量和訂貨金額，并按客戶編號排序輸出。SELECTa.customerNo,customerName,b.orderNo,quantity,quantity*pricetotalFROMCustomera,OrderMasterb,OrderDetailc,ProductdWHEREa.customerNo=b.customerNoANDb.orderNo=c.orderNoANDductNo=ductNoANDproductName='華為手環(huán)B3'ORDERBYa.customerNo注意：你運行的結(jié)果可能與本教材的結(jié)果不一致，其原因與本機(jī)的軟硬件環(huán)境有關(guān)。8.2實驗十六執(zhí)行計劃 8.2.2實驗案例2．實驗案例[例8.9]查詢銷售金額最大的客戶名稱和總貨款。SELECTa.customerNo,customerName,sum(orderSum)FROMcustomera,OrderMasterbWHEREa.customerNo=b.customerNoGROUPBYa.customerNo,customerNameHAVINGsum(orderSum)=(SELECTmax(sumOrder)FROM(SELECTcustomerNo,sum(orderSum)ASsumOrderFROMOrderMasterGROUPBYcustomerNo)c