數(shù)據(jù)庫(kù)系統(tǒng)概論ppt教程-第四章 查詢(xún)優(yōu)化.ppt

第四章 查詢(xún)優(yōu)化,查詢(xún)處理概述(1),關(guān)系操作是非過(guò)程化的，其存取路徑對(duì)用戶透明。用戶只需說(shuō)明“干什么”，不必指出“怎么干”。 輸入：sql語(yǔ)句 輸出：操作的結(jié)果,查詢(xún)處理概述(2),對(duì)于關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)，查詢(xún)優(yōu)化是： 挑戰(zhàn)：必須進(jìn)行好的優(yōu)化，才有可接受的性能 機(jī)會(huì)：關(guān)系表達(dá)式的語(yǔ)義層次高，提供了優(yōu)化的可能性。,查詢(xún)處理概述(3),相對(duì)于由用戶選擇存取路徑的方式： 降低了對(duì)用戶的要求，方便了用戶的使用。避免了因用戶選擇了錯(cuò)誤的存取路徑而導(dǎo)致的效率低下。 能夠取得更好的優(yōu)化效果，因?yàn)?優(yōu)化器具有豐富的可使用的信息 當(dāng)數(shù)據(jù)庫(kù)發(fā)生變化時(shí)優(yōu)化器容易再次進(jìn)行優(yōu)化 優(yōu)化器能夠?qū)Χ喾N實(shí)現(xiàn)策略逐一進(jìn)行考慮 優(yōu)化器集中了最優(yōu)秀的程序員的智慧和經(jīng)驗(yàn),查詢(xún)處理概述(4),查詢(xún)處理的基本步驟： 語(yǔ)法分析與翻譯 優(yōu)化 執(zhí)行查詢(xún)語(yǔ)句,查詢(xún)處理概述(5),查詢(xún)優(yōu)化,查詢(xún)優(yōu)化是為關(guān)系代數(shù)表達(dá)式的計(jì)算選擇最有效的查詢(xún)計(jì)劃的過(guò)程。 查詢(xún)優(yōu)化的過(guò)程： 代數(shù)優(yōu)化：力圖找出與給定關(guān)系代數(shù)表達(dá)式等價(jià)的但執(zhí)行效率更高的一個(gè)表達(dá)式。 物理優(yōu)化:查詢(xún)語(yǔ)句處理的詳細(xì)策略的選擇，例如選擇執(zhí)行運(yùn)算所采用的具體算法，選擇將使用的特定索引等等。,查詢(xún)優(yōu)化的步驟,將查詢(xún)轉(zhuǎn)換成某種內(nèi)部表示，通常是語(yǔ)法樹(shù)。 根據(jù)一定的變換規(guī)則，把語(yǔ)法樹(shù)轉(zhuǎn)換為優(yōu)化形式。 選擇低層的操作算法。 生成查詢(xún)執(zhí)行計(jì)劃（也稱(chēng)查詢(xún)執(zhí)行方案，是由一系列內(nèi)部操作構(gòu)成的）。,查詢(xún)代價(jià)的度量(1),查詢(xún)代價(jià)：查詢(xún)處理對(duì)各種資源的使用情況 總代價(jià)=i/o代價(jià)+cpu代價(jià)+通信開(kāi)銷(xiāo) i/o代價(jià)的度量方式： i/o塊數(shù)或者i/o的次數(shù),查詢(xún)代價(jià)的度量(2),一個(gè)重要的影響因素：主存中緩沖區(qū)的大小m 最好的情形，所有的數(shù)據(jù)可以讀入到緩沖區(qū)中 最壞的情形，緩沖區(qū)只能容納數(shù)目不多的數(shù)據(jù)塊大約每個(gè)關(guān)系一塊。,基本運(yùn)算的實(shí)現(xiàn),每一基本的代數(shù)運(yùn)算都有多種不同的實(shí)現(xiàn)算法。 適用于不同的情況 等值條件，范圍條件 數(shù)據(jù)是聚集的，數(shù)據(jù)是非聚集的 相關(guān)屬性上有索引，相關(guān)屬性上沒(méi)有索引 執(zhí)行代價(jià)不同,選取運(yùn)算的實(shí)現(xiàn)算法（1）,全表掃描方法：依次訪問(wèn)表的每一個(gè)塊，對(duì)于每一個(gè)元組，測(cè)試它是否滿足選擇條件。效率低，但對(duì)關(guān)系的存儲(chǔ)方式?jīng)]有要求，不需要索引。適用于任何選擇條件。 折半掃描: 對(duì)于按某一屬性排序的文件，且選擇條件是該屬性上的等值比較方法，可以使用折半的方法掃描文件。效率高，但需要有序文件,選取運(yùn)算的實(shí)現(xiàn)算法（2）,索引掃描：對(duì)于在選擇條件的屬性上建有索引的表，可以采用訪問(wèn)索 引，根據(jù)索引項(xiàng)的指示去訪問(wèn)數(shù)據(jù)元組的方法。 無(wú)序索引：訪問(wèn)滿足等值條件的元組 有序索引：訪問(wèn)滿足范圍查找條件的一系列元組。,查詢(xún)優(yōu)化的必要性（1）,例：求選修了課程2的學(xué)生姓名 select student.sname from student, sc where student.sno=sc.sno and sc.cno=2;,查詢(xún)優(yōu)化的必要性（2）,查詢(xún)優(yōu)化的必要性（3）,假設(shè): student表中有1000條學(xué)生記錄:nstudent= 1000 sc表中有10000條選課記錄: nsc= 10000 其中選修2號(hào)課程的選課記錄為50條: sc(cno,sc)=50 一個(gè)塊可以裝10個(gè)student元組或100個(gè)sc元組：fstudent= 10，fsc= 100 student表占用的塊: bstudent= 100 sc表占用的塊:bsc= 100,查詢(xún)優(yōu)化的必要性（4）,一個(gè)塊可以裝10個(gè)student和sc的連接結(jié)果元組：fjoin= 10 緩沖: 內(nèi)存中一次可以存放5塊student元組、1塊sc元組和若干塊連接結(jié)果元組 讀寫(xiě)速度：20塊/秒,查詢(xún)優(yōu)化的必要性（5）,讀數(shù)據(jù)時(shí)間=2100/20=105秒,查詢(xún)優(yōu)化的必要性（6）,查詢(xún)優(yōu)化的必要性（7）,查詢(xún)優(yōu)化的必要性（8）,查詢(xún)優(yōu)化的一般準(zhǔn)則（1）,選擇運(yùn)算應(yīng)盡可能先做。目的：減小中間關(guān)系。 在執(zhí)行連接操作前對(duì)文件適當(dāng)進(jìn)行預(yù)處理 排序 在連接屬性上建立索引 投影運(yùn)算和選擇運(yùn)算同時(shí)做。目的：避免重復(fù)掃描關(guān)系。 把投影運(yùn)算與其前面或后面的雙目運(yùn)算結(jié)合起來(lái)。目的：減少掃描關(guān)系的遍數(shù)。,查詢(xún)優(yōu)化的一般準(zhǔn)則（2）,某些選擇運(yùn)算在其前面執(zhí)行的笛卡爾積 連接運(yùn)算 找出公共子表達(dá)式,表達(dá)式的等價(jià)性,兩個(gè)表達(dá)式等價(jià)：產(chǎn)生的結(jié)果關(guān)系具有相同的屬性集和相同的元組集。,關(guān)系代數(shù)等價(jià)變換規(guī)則（1）,所謂關(guān)系代數(shù)表達(dá)式的等價(jià)是指用相同的關(guān)系代替兩個(gè)表達(dá)式中相應(yīng)的關(guān)系所得到的結(jié)果是相同的。上面的優(yōu)化策略大部分都涉及到代數(shù)表達(dá)式的變換。,關(guān)系代數(shù)等價(jià)變換規(guī)則（2）,關(guān)系代數(shù)等價(jià)變換規(guī)則（3）,關(guān)系代數(shù)等價(jià)變換規(guī)則（4）,關(guān)系代數(shù)等價(jià)變換規(guī)則（5）,關(guān)系代數(shù)等價(jià)變換規(guī)則（6）,關(guān)系代數(shù)等價(jià)變換規(guī)則（7）,關(guān)系代數(shù)等價(jià)變換規(guī)則（8）,關(guān)系代數(shù)等價(jià)變換規(guī)則（9）,關(guān)系代數(shù)等價(jià)變換規(guī)則（10）,變換規(guī)則小結(jié),1-2: 連接、笛卡爾積的交換律、結(jié)合律 3： 合并或分解投影運(yùn)算 4： 合并或分解選擇運(yùn)算 5-8：選擇運(yùn)算與其他運(yùn)算交換 5，9，10： 投影運(yùn)算與其他運(yùn)算交換,查詢(xún)樹(shù),查詢(xún)樹(shù) - 關(guān)系代數(shù)表達(dá)式的樹(shù)形表示. 輸入關(guān)系查詢(xún)樹(shù)的葉節(jié)點(diǎn) 關(guān)系操作 內(nèi)部節(jié)點(diǎn) 從底向上執(zhí)行,例子（1）,一個(gè)未優(yōu)化的關(guān)系代數(shù)表達(dá)式,例子（2）,初始查詢(xún)樹(shù),例子（3）,規(guī)則1：盡可能早地進(jìn)行選取操作,例子（4）,規(guī)則2：使用連接操作替代笛卡爾積,例子（5）,規(guī)則3：首先執(zhí)行產(chǎn)生較小結(jié)果集的連接,例子（6）,規(guī)則4：將沒(méi)有用的屬性利用投影操作去掉,性能優(yōu)化,數(shù)據(jù)庫(kù)性能是由多個(gè)因素所構(gòu)成的： 正確性（事務(wù)完整性和數(shù)據(jù)完整性） 可用性 響應(yīng)時(shí)間 性能優(yōu)化的工作是從項(xiàng)目的第一天就開(kāi)始的。對(duì)數(shù)據(jù)庫(kù)性能影響最大的是數(shù)據(jù)庫(kù)的設(shè)計(jì)和開(kāi)發(fā)。通常，所謂的性能優(yōu)化實(shí)際上就是重新開(kāi)發(fā)數(shù)據(jù)庫(kù)系統(tǒng)中設(shè)計(jì)的很糟的那一部分。,優(yōu)化準(zhǔn)則,花費(fèi)盡可能多的努力來(lái)設(shè)計(jì)數(shù)據(jù)庫(kù)模式，所有的優(yōu)化都要基于數(shù)據(jù)庫(kù)模式。 集中精力優(yōu)化運(yùn)行最頻繁的代碼，而不是那些運(yùn)行最慢的代碼。 在升級(jí)硬件之前進(jìn)行優(yōu)化。即使在速度快的服務(wù)器上，壞代碼仍舊是壞代碼。,優(yōu)化準(zhǔn)則,列出所有可能的優(yōu)化思路，即使你沒(méi)有時(shí)間在現(xiàn)在去實(shí)施它們。 新特性會(huì)與優(yōu)化競(jìng)爭(zhēng)資源；因此，最好的辦法是開(kāi)發(fā)一個(gè)維持現(xiàn)有功能，但提高了性能的版本。 優(yōu)化是一個(gè)研究與探索的過(guò)程，你很難對(duì)它做出預(yù)測(cè)。所以，最好不要對(duì)優(yōu)化的效果或交付日期做出承諾。,優(yōu)化準(zhǔn)則,以索引的形式列出系統(tǒng)中那些易于修改和優(yōu)化的部分。 集中精力修改應(yīng)用程序中性能最壞的部分。 花費(fèi)一些時(shí)間作為用戶使用應(yīng)用程序。去使用該程序的部門(mén)工作上一個(gè)星期，這將會(huì)使你產(chǎn)生很多有價(jià)值的靈感。,負(fù)載測(cè)試,數(shù)據(jù)負(fù)載測(cè)試 只有當(dāng)數(shù)據(jù)量超出了服務(wù)器內(nèi)存容量好幾倍的時(shí)候，才能夠?qū)?shù)據(jù)庫(kù)模式和查詢(xún)進(jìn)行負(fù)載測(cè)試。 用戶負(fù)載測(cè)試 只有當(dāng)使用大量的用戶來(lái)測(cè)試數(shù)據(jù)庫(kù)時(shí)才有可能發(fā)生鎖爭(zhēng)用，而鎖爭(zhēng)用會(huì)導(dǎo)致嚴(yán)重的性能問(wèn)題。,負(fù)載測(cè)試,清除測(cè)試的影響 數(shù)據(jù)庫(kù)已經(jīng)經(jīng)過(guò)優(yōu)化，它可以智能地將數(shù)據(jù)緩存在內(nèi)存中，而這將會(huì)影響到后續(xù)測(cè)試的結(jié)果。因此，測(cè)試前要刷新內(nèi)存，這可以通過(guò)停止并重新啟動(dòng)服務(wù)器實(shí)現(xiàn)。,影響性能的因素,規(guī)范化的數(shù)據(jù)庫(kù)物理模式設(shè)計(jì) 完善的和平衡的索引策略 使用基于集合的查詢(xún)方式編碼，并避免以過(guò)程化（基于行的方式）來(lái)操作數(shù)據(jù) 使用數(shù)據(jù)庫(kù)約束和觸發(fā)器來(lái)實(shí)施業(yè)務(wù)規(guī)則 精心地設(shè)計(jì)表、索引和代碼以避免鎖爭(zhēng)用,數(shù)據(jù)庫(kù)設(shè)計(jì)與性能,將數(shù)據(jù)庫(kù)規(guī)范化到第三范式，隨后精心地實(shí)施使用高性能的、單列主鍵的物理設(shè)計(jì)。 不要過(guò)度地規(guī)范化數(shù)據(jù)庫(kù)，或者說(shuō)使數(shù)據(jù)庫(kù)過(guò)度復(fù)雜化，而應(yīng)當(dāng)堅(jiān)持不懈地努力，直至找到簡(jiǎn)單而優(yōu)雅的數(shù)據(jù)庫(kù)設(shè)計(jì)為止。 避免要以事務(wù)的方式在表和表之間來(lái)回倒數(shù)據(jù)的數(shù)據(jù)庫(kù)設(shè)計(jì)。,數(shù)據(jù)庫(kù)設(shè)計(jì)與性能,如果要使用代碼來(lái)創(chuàng)建多個(gè)臨時(shí)表或者額外的工作表，那就說(shuō)明數(shù)據(jù)庫(kù)的設(shè)計(jì)是不充分的。 在設(shè)計(jì)數(shù)據(jù)庫(kù)模式的時(shí)候，必須考慮那些基于它的查詢(xún)。 必要的時(shí)候，要勇敢地將數(shù)據(jù)從oltp表復(fù)制到非規(guī)范化的、只讀的表中去，以便加快數(shù)據(jù)庫(kù)的讀速度。,約束和觸發(fā)器,要在數(shù)據(jù)庫(kù)級(jí)實(shí)施規(guī)則，以便能夠快速地執(zhí)行這些規(guī)則，并保證在任何情況下都無(wú)法避開(kāi)這些規(guī)則的檢查。 用數(shù)據(jù)庫(kù)約束來(lái)實(shí)施數(shù)據(jù)庫(kù)規(guī)則和業(yè)務(wù)規(guī)則，對(duì)于那些無(wú)法用數(shù)據(jù)庫(kù)約束來(lái)實(shí)施的規(guī)則，再使用觸發(fā)器來(lái)實(shí)施。 觸發(fā)器必須使用基于集合的dml語(yǔ)句，而不要使用游標(biāo)。因?yàn)槊恳粋€(gè)insert、update或者delete操作都會(huì)觸發(fā)觸發(fā)器，所以在優(yōu)化觸發(fā)器的代碼時(shí)應(yīng)當(dāng)加倍地努力。,查詢(xún)?cè)O(shè)計(jì)和性能,爭(zhēng)取重用查詢(xún)執(zhí)行計(jì)劃。 只使用視圖支持復(fù)雜的用戶查詢(xún)，除此之外從不在代碼中使用視圖。 使用子查詢(xún)將龐大而復(fù)雜的查詢(xún)分解為多個(gè)更小的邏輯單元。,哪些查詢(xún)不能利用索引,使用and連接到一起的多個(gè)條件可以利用索引，使用or連接在一起的多個(gè)條件則不能。 否定的搜索條件（、!、!、not exists、not in、not like）是不可優(yōu)化的。因?yàn)樽C明一行是存在的很容易，可是如果要證明一行是不存在的，就需要檢查每一行。 由通配符開(kāi)始的條件是不能使用索引的。 使用了表達(dá)式的條件也不能使用索引。 如果where子句包含了函數(shù)（例如字符串函數(shù)），就需要使用表掃描，以便可以使用函數(shù)來(lái)測(cè)試每個(gè)行中的數(shù)據(jù)。,均衡的索引策略,基礎(chǔ)索引,將每一個(gè)主鍵都作為非聚集索引來(lái)創(chuàng)建。這是因?yàn)橹麈I通常用于單行檢索。 為每個(gè)表創(chuàng)建一個(gè)聚集索引。對(duì)于主表，應(yīng)當(dāng)在那些最常用來(lái)排序的列上建立聚集索引。但應(yīng)當(dāng)注意不要在主鍵上建立聚集索引。對(duì)于從表，應(yīng)當(dāng)為最重要的外部鍵創(chuàng)建聚集索引。 除了在第二步中已經(jīng)為其創(chuàng)建了索引的外部鍵以外，對(duì)于每個(gè)外部鍵中的那些列創(chuàng)建非聚集索引。 對(duì)于where子句或者order by子句中所引用的每個(gè)列創(chuàng)建單列索引。,索引調(diào)優(yōu),索引字段應(yīng)當(dāng)包含足夠多的不同的值 表較大，但大多數(shù)的查詢(xún)只會(huì)查找其中24的記錄行 并非索引越