數(shù)據(jù)庫(kù)系統(tǒng)原理與設(shè)計(jì)實(shí)驗(yàn)教程 第4版 課件 第7-9章 數(shù)據(jù)庫(kù)設(shè)計(jì)、數(shù)據(jù)庫(kù)查詢執(zhí)行計(jì)劃、數(shù)據(jù)庫(kù)應(yīng)用開發(fā)

第1頁(yè)第7章數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)系統(tǒng)原理實(shí)驗(yàn)教程第4版第2頁(yè)7.1相關(guān)知識(shí)7.2實(shí)驗(yàn)十五數(shù)據(jù)庫(kù)模式腳本設(shè)計(jì)7.2.1實(shí)驗(yàn)?zāi)康呐c要求7.2.2實(shí)驗(yàn)案例7.2.3實(shí)驗(yàn)內(nèi)容目錄7.1相關(guān)知識(shí)數(shù)據(jù)庫(kù)設(shè)計(jì)過(guò)程通?？煞譃?個(gè)步驟：(1)需求分析：分析要處理的數(shù)據(jù)及數(shù)據(jù)處理原則和約束，形成需求分析說(shuō)明書。(2)概念設(shè)計(jì)：根據(jù)需求分析得到結(jié)果，建立反映現(xiàn)實(shí)的概念數(shù)據(jù)模型，如E-R圖(3)邏輯設(shè)計(jì)：將概念模型轉(zhuǎn)化為數(shù)據(jù)庫(kù)管理系統(tǒng)（如SQLServer2014）能處理的數(shù)據(jù)模型。(4)模式求精：運(yùn)用關(guān)系數(shù)據(jù)理論，對(duì)得到的關(guān)系模式進(jìn)行分析，找出潛在的問題并加以改進(jìn)和優(yōu)化。(5)物理設(shè)計(jì)：對(duì)給定的數(shù)據(jù)模型選擇一個(gè)最合適應(yīng)用環(huán)境的物理結(jié)構(gòu)，包括確定數(shù)據(jù)的存放位置、存儲(chǔ)參數(shù)的配置、索引建立等。(6)應(yīng)用與安全設(shè)計(jì)：定義數(shù)據(jù)庫(kù)角色和用戶，并授予不同權(quán)限以保證數(shù)據(jù)庫(kù)的安全性7.1相關(guān)知識(shí)數(shù)據(jù)庫(kù)實(shí)施階段得到設(shè)計(jì)結(jié)果后，進(jìn)入數(shù)據(jù)庫(kù)實(shí)施階段。此階段的任務(wù)是編寫數(shù)據(jù)庫(kù)腳本，腳本主要有以下部分：(1)創(chuàng)建數(shù)據(jù)庫(kù)；(2)創(chuàng)建表；(3)創(chuàng)建索引；(4)創(chuàng)建視圖；(5)創(chuàng)建角色及用戶；(6)數(shù)據(jù)庫(kù)授權(quán)；(7)創(chuàng)建存儲(chǔ)過(guò)程及游標(biāo)；(8)創(chuàng)建觸發(fā)器。7.2實(shí)驗(yàn)十五數(shù)據(jù)庫(kù)模式腳本設(shè)計(jì)7.2.1實(shí)驗(yàn)?zāi)康呐c要求(1)掌握將數(shù)據(jù)庫(kù)設(shè)計(jì)結(jié)果轉(zhuǎn)化為數(shù)據(jù)庫(kù)腳本的方法。(2)熟練使用DDL語(yǔ)句建立數(shù)據(jù)庫(kù)、表以及定義完整性約束。(3)熟練使用DML語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)查詢、插入、刪除和更新。(4)熟練使用DCL語(yǔ)句創(chuàng)建角色、用戶及數(shù)據(jù)庫(kù)授權(quán)。(5)熟練利用存儲(chǔ)過(guò)程和游標(biāo)進(jìn)行數(shù)據(jù)庫(kù)存取。(6)熟練利用觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)庫(kù)自動(dòng)操作。7.2實(shí)驗(yàn)十五數(shù)據(jù)庫(kù)模式腳本設(shè)計(jì)7.2.2實(shí)驗(yàn)案例以教材“網(wǎng)上書店”的6.3節(jié)得到的關(guān)系表為例，設(shè)計(jì)數(shù)據(jù)庫(kù)腳本。具體要求如下：(1)在'd:\sqlwork路徑后下創(chuàng)建bookstore數(shù)據(jù)庫(kù)；(2)創(chuàng)建登錄用戶u1;(3)創(chuàng)建6.3節(jié)中的全部關(guān)系表及向每個(gè)表中插入少量數(shù)據(jù)；(4)將全部表的所有權(quán)限授予u1;(5)創(chuàng)建存儲(chǔ)過(guò)程①查找訂書金額前20名的會(huì)員編號(hào)、姓名及總金額；②查詢每類圖書當(dāng)月熱銷圖書排行前10名；③錄入出版社信息；④查詢2007年出版的計(jì)算機(jī)方面的書籍。(6)創(chuàng)建觸發(fā)器①實(shí)現(xiàn)會(huì)員自動(dòng)升級(jí)；②只允許注冊(cè)會(huì)員在網(wǎng)上提交訂單；③當(dāng)對(duì)圖書表進(jìn)行操作時(shí)，觸發(fā)器將自動(dòng)記錄該操作者的名稱和操作時(shí)間。7.2.2實(shí)驗(yàn)案例1.創(chuàng)建登錄用戶和數(shù)據(jù)庫(kù)BookStoreDB2.增加數(shù)據(jù)庫(kù)用戶u13.創(chuàng)建表及插入數(shù)據(jù)BookStoreDB腳本代碼參見教材7.2.3實(shí)驗(yàn)內(nèi)容根據(jù)配套教材習(xí)題6.1得到的圖書管理系統(tǒng)設(shè)計(jì)結(jié)果，建立數(shù)據(jù)庫(kù)腳本。要求如下：(1)在“d:\sqlwork”路徑下創(chuàng)建LibraryDB數(shù)據(jù)庫(kù)；(2)創(chuàng)建全部關(guān)系表及向每個(gè)表中插入少量數(shù)據(jù)；(3)創(chuàng)建“圖書管理員”和“讀者”兩類角色，并授予不同的表權(quán)限；(4)創(chuàng)建用戶u1和u2，分別加入到“圖書管理員”和“讀者”角色中去；(5)創(chuàng)建不少于5個(gè)觸發(fā)器；

(6)創(chuàng)建不少于5個(gè)存儲(chǔ)過(guò)程；(7)視情況創(chuàng)建視圖及索引。第9頁(yè)第8章數(shù)據(jù)庫(kù)查詢執(zhí)行計(jì)劃數(shù)據(jù)庫(kù)系統(tǒng)原理實(shí)驗(yàn)教程第4版第10頁(yè)8.1相關(guān)知識(shí)8.1.1SQL優(yōu)化器的優(yōu)化過(guò)程8.1.2執(zhí)行計(jì)劃

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

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

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

SELECT

a.productNo,productName,quantity,priceFROMProducta,

OrderDetailbWHERE

a.productNo=b.productNo

ANDprice>3000ORDER

BY

productName

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

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

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

a.customerNo

,customerName,sum(orderSum)

訂購(gòu)總金額FROMCustomera,OrderMasterbWHERE

a.customerNo=b.customerNoGROUP

BY

a.customerNo

,customerNameHAVING

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

a.productNo,productName,quantity,priceFROMProducta,

OrderDetailbWHERE

a.productNo=b.productNo

ANDprice>3000ORDER

BY

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

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

StatementSetOptions：描述該語(yǔ)句的各種屬性值的Set值

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

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

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

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

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