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

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