優化你的 MySQL 數據庫運作

要提高一個 PHP 應用系統的效能，除了優化算法、原碼外，還有很多方面可以著手，資深軟件工程師 Mike Willbanks 發表了一篇文章討論優化 MySQL 數據庫的運作，包括懶惰連接、不做無謂的查詢、小心規劃數據庫結構等。雖然這篇文章針對 MySQL，不過提及的技巧在其他數據庫引擎上同樣有用。

應用程式層面的 SQL 效能

應用程式層面的 SQL 效能與 SQL 查詢的效能屬於不同的課題，這是有關如何在設計上使 SQL 與應用程式互相配合讓工作做得更好，Mike Willbanks 認為這方面的研究主要集中於如何降低查詢的次數，從而提高可擴展性和改善效能，不過，高的效能並不等於好的可擴展性，正如具有可擴展性並不等於有高的效能。以下有關應用層面的 SQL 效能建議，很多都已經在 Mike Willbanks 的個人網誌上討論過：

懶惰的連接

對於無需利用數據庫，或者在某些請求中無須使用數據庫的應用程式，懶惰的連接可以使效能有大幅的改進，它的概念是除非到了絕對必要的時候，不會初始化數據庫連接，以免寶貴的系統浪費在處理大量不活躍的連接上。

一個懶惰連接的簡單例子

以下是一個處理懶惰連接的簡單例子，雖然它並不完整，但已經展示了箇中的技巧：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

class My_Db {
    private $_connected = false;
    private $_connection;
    public function connect($host, $user, $pass, $db) {
        //method will simply set the connection variables
    }

    private function _connect() {
        if ($this->_connection = mysql_connect()) {
            $this->_connected = true;
        }
    }

    public function query($query) {
        if (!$this->_connected) {
            $this->_connect();
        }
        mysql_query($query, $this->_connection);
    }
}

循環查詢

Mike Willbanks 認為循環查詢是他檢視別人的代碼，甚至一些開放原碼專案時，最常見到的問題。所謂循環查詢是指在迴圈中執行的查詢，這是非常消耗資源的做法，而且很多時候是絕對不需要的。

循環查詢的錯誤例子

1
2
3
4
5
6
7

if (isset($_GET['ids'])) {
    foreach($_GET['ids'] as $id) {
        $rs = mysql_query('SELECT * FROM my_table WHERE my_id = ' . (int) $id);
        $row = mysql_fetch_assoc($rs);
        print_r($row);
    }
}

改正這個錯誤例子

1
2
3
4
5
6
7
8

if (isset($_GET['ids'])) {
    $ids = array_map('intval', $_GET['ids']);
    $ids = implode(',', $ids);
    $rs = mysql_query('SELECT * FROM my_table WHERE my_id IN (' . $ids . ')');
    while($row = mysql_fetch_assoc($rs)) {
        print_r($row);
    }
}

需要為本的查詢

請勿查詢你不需要的資料，首先，這增加了記憶體的需求，其次，從數據庫擷取不必要的欄位也浪費 I/O 時間。查詢越多數據，所花的時間越多，記憶體的需求也越大，對於 TEXT 和 BLOG 類型的欄位情況尤其嚴重。

拙劣的 SELECT *

SELECT * 壞在兩方面，第一個也是比較值得關注的問題，是當你需要修改應用程式時，如何得知查詢的結果將要如何運用？假說你有一個龐大的應用程式，裏面有成千上百個檔案都是用 SELECT * 來進行查詢，不對每一個 SELECT * 進行仔細的研究，你無法得知從數據庫得到的變量如何被運用，與及被送到甚麼地方。很快這個應用程式的可維護性會蕩然無存。第二個方面前面已經提過，就是效能和記憶體需求的問題，簡單地說，不要使用 SELECT *。人們聽到這個建議後總會問：如果我需要查詢所有欄位應怎麼辦？這種情況仍然不能用 SELECT * 嗎？請問你肯定程式的這個位置，在三個月、六個月、一年、甚至五年後，還是需要查詢所有欄位嗎？

使用正確的數據類型（不要把所有數據都加上引號）

不錯，在查詢中使用正確的數據類型是十分要緊的，因為你可以能會導致數據庫不能使用索引欄位，或者送回錯誤的結果，除此以外，數據庫也因為要把數據轉化為正確的類型而變慢。

不正確數據類型的例子

1
2
3
4

if (isset($_GET['id'])) {
    $id = mysql_real_escape_string($_GET['id']);
    $rs = mysql_query("SELECT * FROM my_table WHERE my_id = '{$id}'");
}

正確數據類型的例子

1
2
3
4

if (isset($_GET['id'])) {
    $id = (int) $_GET['id'];
    $rs = mysql_query('SELECT * FROM my_table WHERE my_id = ' . $id);
}

順帶一提，第二個例子會執行得較快，因為它只是把 $_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 手冊」。

• MySQL
• 優化