解析MySQL資料庫效能最佳化的六大技巧

資料庫表表面上存在索引和防錯機制，然而一個簡單的查詢就會耗費很長時間。Web應用程式或許在開發環境中運行良好，但在產品環境中表現同樣糟糕。如果你是個資料庫管理員，你很有可能已經在某個階段遇到上述情況。因此，本文將介紹對MySQL進行效能最佳化的技巧和竅門。

1.儲存引擎的選擇
如果資料表需要交易處理，應該考慮使用InnoDB，因為它完全符合ACID特性。如果不需要交易處理，使用預設儲存引擎MyISAM是比較明智的。並且不要嘗試同時使用這兩個儲存引擎。思考一下：在一個交易處理中，一些資料表使用InnoDB，而其餘的使用MyISAM。結果呢?整個subject將被取消，只有那些在交易處理中的被帶回到原始狀態，其餘的被提交的資料轉存，這將導致整個資料庫的衝突。然而存在一個簡單的方法可以同時利用兩個儲存引擎的優勢。目前大多數MySQL套件中包括InnoDB、編譯器和鏈表，但如果你選擇MyISAM，你仍然可以單獨下載InnoDB，並把它作為一個外掛程式。很簡單的方法，不是嗎?

2.計數問題
如果資料表採用的儲存引擎支援交易處理(如InnoDB)，你就不應使用COUNT(*)計算資料表中的行數。這是因為在產品類資料庫使用COUNT(*)，最多返回一個近似值，因為在某個特定時間，總有一些交易處理正在運行。如果使用COUNT(*)顯然會產生bug，出現這種錯誤結果。

3.反覆測試查詢
查詢最棘手的問題並不是無論怎樣小心總會出現錯誤，並導致bug出現。恰恰相反，問題是在大多數情況下bug出現時，應用程式或資料庫已經上線。的確不存在針對該問題切實可行的解決方案，除非將測試樣本在應用程式或資料庫上運行。任何資料庫查詢只有經過上千個記錄的大量樣本測試，才能被認可。

4.避免全表掃描
通常情況下，如果MySQL(或者其他關聯式資料庫模型)需要在資料表中搜尋或掃描任意特定記錄時，就會用到全表掃描。此外，通常最簡單的方法是使用索引表，以解決全表掃描引起的低效能問題。然而，正如我們在隨後的問題中看到的，這存在錯誤部分。

5.使用”EXPLAIN”進行查詢
當需要調試時，EXPLAIN是一個很好的命令，下面將對EXPLAIN進行深入探討。
首先，建立一個簡單的資料表：複製代碼 代碼如下:CREATETABLE'awesome_pcq'(
'emp_id'INT(10)NOTNULL
DEFAULT'0',
'full_name'VARCHAR(100)NOTNULL,
'email_id'VARCHAR(100)NOTNULL,
'password'VARCHAR(50)NOTNULL,
'deleted'TINYINT(4)NOTNULL,
PRIMARYKEY('emp_id')
) COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

這個資料表一目瞭然，共有五列，最後一列“deleted”是一個Boolean類變數flag來檢查帳號是活動的還是已被刪除。接下來，您需要用樣本記錄填充這個表(比如，100個僱員記錄)。正如你看到的，主鍵是“emp_id”。因此，使用電子郵件地址和密碼欄位，我們可以很容易地建立一個查詢，以驗證或拒絕登入請求，如下(執行個體一)：複製代碼 代碼如下:SELECTCOUNT(*)FROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

之前我們提到，要避免使用COUNT(*)。代碼糾正如下(執行個體二)：複製代碼 代碼如下:SELECTemp_idFROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

現在回想一下，在執行個體一中，代碼查詢定位並返回“email_id”和“password”等於給定值的行數。在執行個體二中，進行了同樣的查詢，不同的是明確要求列出“emp_id”所有滿足給定的標準的值。哪個查詢更費時?
很顯然，這兩個執行個體都是同樣費時的資料庫查詢，因為無意間，兩個執行個體查詢都進行了全表掃描。為了更好地讀懂指令，執行如下代碼：複製代碼 代碼如下:EXPLAINSELECTemp_idFROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

在輸出時，集中在倒數第二列：“rows”。假設我們已經將表填充了100個記錄，它會在第一行顯示100，這是MySQL需要進行掃描用來計算查詢的結果的行數。這說明了什麼?這需要全表掃描。為了克服這個弊端，則需要添加索引。

6.添加索引
先從重要的說起：給每一個可能遇到的次要問題建立索引並不明智。過多的索引會導致效能減慢和資源佔用。在進一步討論之前，在執行個體中建立一個樣本索引：複製代碼 代碼如下:ALTERTABLE'awesome_pcq'ADDINDEX'LoginValidate'('email_id')

接下來，再次運行該查詢： 複製代碼 代碼如下:EXPLAINSELECTemp_idFROMawesome_pcqWHERE
email_id='blahblah'ANDpassword='blahblah'ANDdeleted=0

請注意運行後的值。不是100，而是1。因此，為了給出查詢結果，MySQL只掃描了1行，多虧先前建立的索引。你可能會注意到，索引只在電子郵件地址欄位建立，而查詢對其他欄位同樣進行了搜尋。這表明MySQL先執行了一個cros-check，檢查是否有在WHERE子句中的定義的值有索引指定，如果有這樣的值就執行相應的操作。

但是，它不是每次重複將減少到一個。例如，如果不是唯一的索引欄位(如employee names列可以有兩行相同的值)，即使建立索引，也將有多個記錄留下。但它仍然比全表掃描好。並且，在WHERE子句中指定列的順序沒有在這個過程中發揮作用。例如，如果在上面的查詢中，改變欄位的順序，使電子郵件地址出現在最後，MySQL仍將遍曆索引列的基礎上。那麼，就要在索引上動腦筋，注意如何避免大量的全表掃描，並獲得更好的結果。不過，這需要經曆一個很長的過程。