談談SQL 語句的優化技術 (2)

三、優化SQL語句的系統設計原則

 

僅僅簡化還是不夠。SQL語句的優化在系統的設計階段就要通盤考慮。系統設計越合理，需要優化或後期返工的地方就越少。系統邏輯流程如果不合理，那麼常會導致本來需要一個操作就可以解決的問題卻要作好幾個操作才能實現，反映在資料庫上就是發出過多或過複雜的SQL語句。所以資料庫系統設計是系統高效能執行的首要關鍵。

 

很多系統開發者更關心功能的實現而不是資料庫系統的整體執行效能。相當多的開發者對資料庫效能優化技術不是很瞭解，寫出來的SQL語句往往缺乏效率。下面讓我們羅列一些在設計階段就應該考慮的和效能密切相關的一些原則。

 

 

1）限制結果集

 

要儘量減少返回的結果行，包括行數和欄位列數。返回的結果越大，意味著相應的SQL語句的logical reads 就越大，對伺服器的效能影響就越甚。一個很不好的設計就是返回表的所有資料：

 

Select * from tablename

 

即使表很小也會導致併發問題。更壞的情況是，如果表有上百萬行的話，那後果將是災難性的。它不但可能帶來極重的磁碟IO，更有可能把資料庫緩衝區中的其他快取資料擠出，使得這些資料下次必須再從磁碟讀取。沒有索引能夠優化沒有where子句的語句。在這樣的語句執行的時候，大量別的小語句會出現超時或緩慢的現象。一個系統只要有幾條這樣的大語句不定時執行，你幾乎肯定會注意到系統效能的不穩定性。所以，必須設計良好的SQL語句，使得其有where語句或TOP語句來限制結果集大小。它通常應該是這個樣子的：

 

Select col1,col2,……from table1 where colx=… and coly=…

 

沒有where語句，或不能預知where語句會返回多少行，是開發者常忽略的地方。程式測試的時候是沒有問題的，因為那個時候表的資料還少，不能暴露效能問題。但隨著程式部署到實際環境當中，表資料越來越多，問題就會越來越突出。一個穩定優秀的系統應該能夠考慮到資料的增長而預知SQL語句會返回多少資料，進而作相應的處理。如果你實在不能知道SQL語句會返回多少資料，那麼可以使用TOP n來限制結果集,比如：

 

Select TOP 100 col1,col2,……from table1 where colx=… and coly=…

 

其中n不要過大。我就看到有系統採用n＝20000的做法，似乎n大了點。你得想一想，我的程式確實需要返回這麼多的資料嗎？程式的使用者會看這麼多的資料嗎？

 

如果語句結果確實很多，可以考慮將結果集進行分頁。分頁是限制結果集的一種有效手段。比如說，先使用TOP n方式返回頭100條資料。只有使用者點選下一頁的時候才再發出查詢獲取下100行。

 

2）合理的表設計

 

在表的設計中，比較關鍵的問題是如何處理表的歷史資料。表資料會越來越大。你一定需要考慮表的資料增長問題。比如預先考慮一天，一個星期，或一個月內表的資料變化。常見的做法是安排作業定時把表的資料匯出到別處，使得資料庫保持一定的大小，從而獲得穩定一致的效能。也有的系統根據時間設計表，比如說根據月份設計表，如2005一月表，2005二月表，2006三月表等。這樣做的好處是每個月表的大小基本穩定一致，效能也可以保證。不好的地方是管理複雜些，程式也要設計成能夠根據時間訪問不同的月份表。

 

一個非常令人興奮的訊息是SQL Server 2005將支援表分割槽技術。利用表分割槽技術可以實現資料表的流動視窗功能。在流動視窗中可以輕易的把歷史資料移出，把新的資料加入，從而使表的大小基本保持穩定。

 

另外，表的設計未必需要非常正規化化。有一定的欄位冗餘可以增加SQL語句的效率，減少JOIN的數目，提高語句的執行速度。

 

3）OLAP和OLTP模組要分開

 

OLAP和OLTP型別的語句是截然不同的。前者往往需要掃描整個表做統計分析，索引對這樣的語句幾乎沒有多少用處。索引只能夠加快那些如sum，group by之類的聚合運算。因為這個原因，幾乎很難對OLAP型別的SQL語句進行優化。而OLTP語句則只需要訪問表的很小一部分資料，而且這些資料往往可以從記憶體快取中得到。為了避免OLAP 和OLTP語句相互影響，這兩類模組需要分開執行在不同伺服器上。因為OLAP語句幾乎都是讀取資料，沒有更新和寫入操作，所以一個好的經驗是配置一臺standby 伺服器，然後OLAP只訪問standby伺服器。

 

常常有客戶諮詢我說資料庫系統一到月底或一個月的某個時段就變得很慢。你猜猜為什麼呢？原來月底是系統生成OLAP報表的時候。報表意味著對幾乎全部表資料進行掃描統計，伺服器負擔自然很重，系統當然比平時慢。我聽說某些ERP系統生成一個報表需要好幾個小時呢。

 

4）使用儲存過程

 

可以考慮使用儲存過程封裝那些複雜的SQL語句或商業邏輯，這樣做有幾個好處。一是儲存過程的執行計劃可以被快取在記憶體中較長時間，減少了重新編譯的時間。二是儲存過程減少了客戶端和伺服器的繁複互動。三是如果程式釋出後需要做某些改變你可以直接修改儲存過程而不用修改程式，避免需要重新安裝部署程式。

 

四、結束語

 

讀完本文，你應該知道簡化SQL語句的技術以及系統設計要考慮到的一些原則。應用這些技術能夠提高資料庫系統的整體效能。資料庫系統優化是個很大的話題，本文只是羅列一些有用的經驗，更多的需要你的實踐。