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

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