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