基于二重模式的SQL查詢優化教學實踐

　　摘要：查詢優化在關系數據庫系統中有著非常重要的地位，是影響RDBMS性能的關鍵因素。本文主要歸納總結實現查詢優化應遵循的一些原則和使用技巧，介紹二重查詢優化教學模式，以培養學生編寫出高質量的SQL語句，提高應用系統的性能。
　　關鍵詞：數據庫；SQL查詢；教學模式
　　
　　SQL語言的學習是數據庫應用技術課程教學的重點內容，學生能否靈活熟練掌握SQL語句，編寫出高質量的SQL句子，是學好該課程的關鍵[1]，也是開發與數據庫應用相關軟件的基礎。因為SQL語句可以有多種不同的寫法，所以它易學，但難于精通，是教學難點內容。在此，我們根據多年教學工作經驗，在教學中以實際數據庫應用系統中數據設計和數據查詢的使用為例，分析探索查詢優化的方法，采用“數據庫設計→設計優化→編寫SQL語句→語句優化”的二重優化模式方法，教學效果良好。本文通過歸納總結學習掌握數據設計和SQL查詢優化的方法，闡述了SQL優化教學的關鍵所在。
　　1二重查詢優化模式
　　學生學習SQL語言，通常只停留在能查詢出所要求的結果，這樣的水平并不深究查詢效率，更不進行查詢優化測試，其原因在于學生還沒有開發過數據庫應用系統，未能體會到通過優化查詢提高系統性能的意義；例如：一個基于B/S(或C/S)模式的數據庫應用系統，用戶對數據庫連接和響應速度很敏感，如果數據庫響應速度太慢，不能及時反饋用戶要查詢的數據，用戶就會難以忍受，從而直接影響用戶對整個系統的評價。因此，設計、編寫高效合理的查詢語句，提高數據庫響應速度就顯得非常重要，當然，這對編程和設計人員的要求也更高一層。在教學中，要闡明
　　
　　優化查詢的重要意義，以實際應用講授查詢優化的方法，激發學生的學習興趣，使學生在編寫基本SQL語句的基礎上，盡快掌握查詢優化技術[2]。
　　1.1查詢優化知識體系
　　在大多數實際數據庫應用系統中，查詢操作在各種數據庫操作中所占比重最大，而每個查詢操作都會有許多可供選擇的執行策略和操作算法，查詢優化就是選擇一個高效的執行的查詢處理策略。查詢優化的方法多種多樣，按照優化層次可分為代數優化和物理優化[3]。代數優化是指關系代數表達式的優化，即按照一定的規則，改變代數表達式中操作的次序和組合，使查詢執行更高效；物理優化是指存取路徑和底層操作算法的選擇，其選擇依據可以是基于規則的，也可以是基于代價的，還可以是基于語義的。查詢優化的理論基礎知識體系如圖1所示。
　　1.2二重查詢優化流程
　　依據查詢優化的理論，在數據庫應用系統的開發中，為了能夠系統地進行查詢優化，我們提出二重查詢優化模式：數據庫設計→設計優化→編寫SQL語句→語句優化，即設計優化和語句優化，兩次優化，先后順序并無固定，可循環多次，以達到最好的優化效果。整個模式操作方便、思路清晰，實施關鍵在于第一重優化和第二重優化的具體方法，文中第2部分、第3部分有詳細介紹。查詢優化的教學過程流程可以進一步擴展，如圖2所示。
　　二重查詢優化方法，第一重優化，即設計優化，實施在數據庫設計階段的關系模式優化之后，要通過修改、調整、和重構模式，經過反復嘗試和比較，得到最終的優化的關系模式，它是進行語句優化的基礎。第二重優化過程是應用程序設計層面，應按照系統支持的各種應用分別試驗它們在數據庫操作的效率，弄清它們在實際運行中能否完成預定的功能。但并不意味著設計過程結束，就不能再進行調整數據庫，在實際運行和維護過程中，調整、修改數據庫及其應用的事是常常發生的，因此，二重查詢優化，在實施中，并沒有固定順序，且可反復進行，直至達到理想效果。
　　
　　圖2二重查詢優化教學流程圖
　　2設計優化
　　二重查詢優化模式，首先是通過數據庫合理設計進行優化，對應于優化層次的物理優化，選擇合理的存取路徑和底層操作算法。一般遵循以下策略和技巧來改善查詢計劃，提高應用系統的性能。
　　2.1合理的索引設計
　　索引是數據庫設計中最重要的數據結構，利用索引可以快速訪問數據庫表中的特定信息，它是對數據庫表中的一個或多個列的值進行排序的結構，數據庫性能問題都可以采用索引技術得到解決。在設計數據庫關系圖中，可以為選定的表創建、編輯或刪除索引/鍵屬性頁中的每個索引類型，當保存附加在此索引上的表或包含此表數據關系圖時，同時被保存。
　　對索引操作，學生一般較容易掌握，學生往往在學習使用索引時，不清楚在何種情況下需使用索引，這是因為學生對應用問題理解不夠透徹。所以，教學中要強調說明，在考慮是否為一個列創建索引時，應考慮被索引的列是否以及如何用于查詢。
　　2.1.1索引的作用
　　建立索引，基本思路是考慮索引對哪些查詢有幫助，通常有如下所列的情況。
　　1) 搜索符合特定搜索關鍵字值的行(精確匹配查詢)；
　　2) 搜索其搜索關鍵字值為范圍值的行(范圍查詢)；
　　3) 根據聯接謂詞，在一個表中搜索與另一個表中的某行匹配的行(索引嵌套循環連接)；
　　4) 在不進行顯式排序操作的情況下產生經排序的查詢輸出，尤其是經排序的動態游標；
　　5) 在不進行顯式排序操作的情況下，按一種有序的順序對行進行掃描，以允許基于順序的操作，如合并聯接和流聚合；
　　6) 以優于表掃描的性能對表中所有的行進行掃描，性能提高是由于減少了要掃描的列集和數據總量；
　　7) 搜索插入和更新操作中重復的新搜索關鍵字值，以實施PRIMARY KEY和UNIQUE約束；
　　8) 搜索已定義了FOREIGN KEY約束的兩個表之間匹配的行。
　　2.1.2使用索引要遵循的原則
　　我們要考慮到索引的使用要恰到好處，指導學生使用索引要遵循如下原則。
　　1) 主鍵上建立索引是首選，另外對連接中頻繁使用的列(包括外鍵)也應作為建立索引考慮的選項。不經常連接的字段則由優化器自動生成索引；引導學生學會使用SQL事件探查器和索引優化向導幫助分析查詢，確定要創建的索引。
　　2) 覆蓋的查詢可以提高性能。覆蓋的查詢是指查詢中所有指定的列都包含在同一個索引中。
　　3) 對于不需要修改數據的查詢(SELECT語句)，大量的索引有助于提高性能。而如果一個表創建有大量的索引會影響INSERT、UPDATE和DELETE語句的性能，因為在表中的數據更改時，所有的索引都須進行適當的調整。
　　4) 在頻繁進行排序或分組(即進行GROUP BY分組或ORDER BY排序)的列上建立索引；在不同值較多的列上建立檢索，而在不同值很少的列上不建立索引。
　　5) 可以在視圖和計算列上指定索引，這樣顯得更方便和快捷。
　　2.1.3對于索引種類使用策略
　　如果需要排序的列有多個，可以在這些列上建立組合索引。經常同時存取多列，且每列都含有重復值可考慮建立組合索引；可考慮建立聚簇索引的情況有：包含有大量非重復值的列；使用BETW