優化你的 MySQL 數據庫運作
阿恆
要提高一個 PHP 應用系統的效能,除了優化算法、原碼外,還有很多方面可以著手,資深軟件工程師 Mike Willbanks 發表了一篇文章討論優化 MySQL 數據庫的運作,包括懶惰連接、不做無謂的查詢、小心規劃數據庫結構等。雖然這篇文章針對 MySQL,不過提及的技巧在其他數據庫引擎上同樣有用。
應用程式層面的 SQL 效能
應用程式層面的 SQL 效能與 SQL 查詢的效能屬於不同的課題,這是有關如何在設計上使 SQL 與應用程式互相配合讓工作做得更好,Mike Willbanks 認為這方面的研究主要集中於如何降低查詢的次數,從而提高可擴展性和改善效能,不過,高的效能並不等於好的可擴展性,正如具有可擴展性並不等於有高的效能。以下有關應用層面的 SQL 效能建議,很多都已經在 Mike Willbanks 的個人網誌上討論過:
懶惰的連接
對於無需利用數據庫,或者在某些請求中無須使用數據庫的應用程式,懶惰的連接可以使效能有大幅的改進,它的概念是除非到了絕對必要的時候,不會初始化數據庫連接,以免寶貴的系統浪費在處理大量不活躍的連接上。
一個懶惰連接的簡單例子
以下是一個處理懶惰連接的簡單例子,雖然它並不完整,但已經展示了箇中的技巧:
|
|
循環查詢
Mike Willbanks 認為循環查詢是他檢視別人的代碼,甚至一些開放原碼專案時,最常見到的問題。所謂循環查詢是指在迴圈中執行的查詢,這是非常消耗資源的做法,而且很多時候是絕對不需要的。
循環查詢的錯誤例子
|
|
改正這個錯誤例子
|
|
需要為本的查詢
請勿查詢你不需要的資料,首先,這增加了記憶體的需求,其次,從數據庫擷取不必要的欄位也浪費 I/O 時間。查詢越多數據,所花的時間越多,記憶體的需求也越大,對於 TEXT 和 BLOG 類型的欄位情況尤其嚴重。
拙劣的 SELECT *
SELECT * 壞在兩方面,第一個也是比較值得關注的問題,是當你需要修改應用程式時,如何得知查詢的結果將要如何運用?假說你有一個龐大的應用程式,裏面有成千上百個檔案都是用 SELECT * 來進行查詢,不對每一個 SELECT * 進行仔細的研究,你無法得知從數據庫得到的變量如何被運用,與及被送到甚麼地方。很快這個應用程式的可維護性會蕩然無存。第二個方面前面已經提過,就是效能和記憶體需求的問題,簡單地說,不要使用 SELECT *。人們聽到這個建議後總會問:如果我需要查詢所有欄位應怎麼辦?這種情況仍然不能用 SELECT * 嗎?請問你肯定程式的這個位置,在三個月、六個月、一年、甚至五年後,還是需要查詢所有欄位嗎?
使用正確的數據類型(不要把所有數據都加上引號)
不錯,在查詢中使用正確的數據類型是十分要緊的,因為你可以能會導致數據庫不能使用索引欄位,或者送回錯誤的結果,除此以外,數據庫也因為要把數據轉化為正確的類型而變慢。
不正確數據類型的例子
|
|
正確數據類型的例子
|
|
順帶一提,第二個例子會執行得較快,因為它只是把 $_GET[‘id’] 的類型強制轉換為整數,而不是把它送給 mysql_real_escape_string() 進行轉換。
分層數據
當你使用導航樹時,你應該確保你利用了適當的算法和數據結構,而不是壓迫數據庫來達成此目的,此外,請使用緩存來儲存結果,不要在每次處理請求時都重新到數據庫執行查詢,有一篇很棒的文章「在 MySQL 中管理分層數據」對這個問題有詳細的研究,所以不在此深入討論。但無論任何時候你要管理一個樹狀結構的數據時,請正確使用它們,不要給其他開發者帶來麻煩。
數據庫設計
很多時候你會發現數據庫設計是問題的根源,一個差勁的數據模型可以使應用程式的效能和可維護性都深受影響,值得注意的是,一個越是為效能而優化的數據庫,可維護性也越低(這一點是有爭議的,它其實要視乎應用程式的大小和規模)。Mike Willbanks 從一個開發人員的角度列出一些優化數據庫設計的技巧,這不是一份包羅萬有的名單,也不是一份全面的指引,若果你需要深入的資料,Mike Willbanks 建議大家閱讀「MySQL 的數據庫設計手冊」。
正規化(Normalization)
正規化是一個用來儘量減少資料重複的技巧,通常這是最佳的起步點,若果你正遇到問題而你的數據庫沒有正規化,這便是你首要處理的問題。
使用欄位而非資料表的例子
一個在應用程式中常見的問題是把數據儲存在欄位而非一個「一對多(one-to-many)」資料表,例如:
app_user:
這裏的情況可能是用戶要求最多儲存三個網站地址,負責的開發人員認為這是最極端的情況了,不可能需要更多的了,所以定義了三個儲存網址的欄位,正是這裏需要從新規劃以取得更佳的模組化以及正規化。
使用資料表而非欄位的例子
使用資料表代替欄位將可以更好地支援此項功能,也允許將來進一步擴展。
app_user:
app_user_website:
你可能會想,這跟效能有甚麼關係?其實它改善了系統的可維護性,更好地處理索引鍵,以及降低資料表在最初階段的大小,當你逐步為資料表加入索引欄位,資料表的大小便越來越重要,利用以上兩個例子嘗試搜尋沒有網站的用戶,假設資料表 user_website 沒有索引欄位,執行 SQL 語句 select user_id from app_user where user_id NOT IN (select distinct user_id from app_user_website) 將會非常緩慢,你可能考慮增加一個索引欄位,但這倒不如從一開始便為 app_user_website 資料表建立一個外部關鍵欄(foreign key),若果你使用正規化技術來設計資料庫,這些索引和關鍵欄一早已經為你建立。
反正規化
反正規化的概念是把一個資料表的數據複製到另一個資料表,從而減少聯合資料表查詢的機會,通常這些都由 trigger 來達成以確保數據的完整性,若果你不能處理 trigger,請確保任何建立、修改或刪除資料的操作都被封包起來,否則有機會出現陳舊失效的資料。一般來說,除非你已經用盡所有方法,包括檢查及建立索引鍵,仍然無法有效取得你需要的數據,才會使用反正規化的手段。
資料表類型
根據你正在做的或試圖做的事,選擇正確的資料表類型,Mike Willbanks 建議大家建立一個矩陣,列出你的數據庫和資料表大致上需要怎麼樣的操作,很多開發人員都忽視了這重要的第一步。
以上名單遠非完整,但你至少應該紀錄你需要甚麼,你的數據庫需要甚麼樣的操作,例如,你要在同一個資料表上進行讀和寫,而寫入的比率超過 15%,MyISAM 未必是一個理想的儲存引擎,因為遇上資料表寫入操作時,MyISAM 會鎖定整個資料表,其他讀取和寫入操作都無法執行,直至寫入完結,若果寫入的操作相對很多,便會嚴重影響讀取數據的效率。所以,建立一份數據庫操作情況的名單,並計算出需要甚麼,這肯定對設計數據庫結構有很大幫助。
優化 SQL 查詢
優化 SQL 查詢並非甚麼尖端科技,不過,當一個應用程式開始被廣泛使用,往往情況便開始崩潰,這時只會集中進行功能上的改進,極少會考慮修改數據庫的運作。一個簡單的規則是,利用數據庫的架構特點來優化你的 SQL 查詢,當出現無法做到的情況,便著手修改和調整數據庫。
簡單的規則
- 使用解釋計劃(explain plan)/執行計劃(execution plan)
- 越少聯合資料表查詢越好
- 確保使用了你的索引欄位(見第一點)
- 處理複雜的數據時,善用臨時資料表(temporary tables)
- 避免使用衍生資料表(derived tables)及 non-materialized views
- 只查詢你需要的欄位,而不是 SELECT *
如果你正在尋找更好的優化查詢的方法,可以參考「優化查詢 MySQL 手冊」。