數(shù)據(jù)庫表表面上存在索引和防錯機制，然而一個簡單的查詢就會耗費很長時間。Web應用程序或許在開發(fā)環(huán)境中運行良好，但在產(chǎn)品環(huán)境中表現(xiàn)同樣糟糕。如果你是個數(shù)據(jù)庫管理員，你很有可能已經(jīng)在某個階段遇到上述情況。因此，本文將介紹對MySQL進行性能優(yōu)化的技巧和竅門。

1.存儲引擎的選擇

如果數(shù)據(jù)表需要事務處理，應該考慮使用InnoDB，因為它完全符合ACID特性。如果不需要事務處理，使用默認存儲引擎MyISAM是比較明智的。并且不要嘗試同時使用這兩個存儲引擎。思考一下：在一個事務處理中，一些數(shù)據(jù)表使用InnoDB，而其余的使用MyISAM。結(jié)果呢?整個subject將被取消，只有那些在事務處理中的被帶回到原始狀態(tài)，其余的被提交的數(shù)據(jù)轉(zhuǎn)存，這將導致整個數(shù)據(jù)庫的沖突。然而存在一個簡單的方法可以同時利用兩個存儲引擎的優(yōu)勢。目前大多數(shù)MySQL套件中包括InnoDB、編譯器和鏈表，但如果你選擇MyISAM，你仍然可以單獨下載InnoDB，并把它作為一個插件。很簡單的方法，不是嗎?

2.計數(shù)問題

如果數(shù)據(jù)表采用的存儲引擎支持事務處理(如InnoDB)，你就不應使用COUNT(*)計算數(shù)據(jù)表中的行數(shù)。這是因為在產(chǎn)品類數(shù)據(jù)庫使用COUNT(*)，最多返回一個近似值，因為在某個特定時間，總有一些事務處理正在運行。如果使用COUNT(*)顯然會產(chǎn)生bug，出現(xiàn)這種錯誤結(jié)果。

3.反復測試查詢

查詢最棘手的問題并不是無論怎樣小心總會出現(xiàn)錯誤，并導致bug出現(xiàn)。恰恰相反，問題是在大多數(shù)情況下bug出現(xiàn)時，應用程序或數(shù)據(jù)庫已經(jīng)上線。的確不存在針對該問題切實可行的解決方法，除非將測試樣本在應用程序或數(shù)據(jù)庫上運行。任何數(shù)據(jù)庫查詢只有經(jīng)過上千個記錄的大量樣本測試，才能被認可。

4.避免全表掃描

通常情況下，如果MySQL(或者其他關(guān)系數(shù)據(jù)庫模型)需要在數(shù)據(jù)表中搜索或掃描任意特定記錄時，就會用到全表掃描。此外，通常最簡單的方法是使用索引表，以解決全表掃描引起的低效能問題。然而，正如我們在隨后的問題中看到的，這存在錯誤部分。

5.使用”EXPLAIN”進行查詢

當需要調(diào)試時，EXPLAIN是一個很好的命令，下面將對EXPLAIN進行深入探討。

首先，創(chuàng)建一個簡單的數(shù)據(jù)表：

CREATE TABLE awesome_pcq(

emp_id INT(10) NOTNULL DEFAULT '0',

full_name VARCHAR(100) NOTNULL,

email_id VARCHAR(100) NOT NULL,

password VARCHAR(50) NOT NULL,

deleted TINYINT(4) NOTNULL,

PRIMARYKEY(emp_id)

) COLLATE=utf8_general_ci

ENGINE=InnoDB

ROW_FORMAT=DEFAULT

這個數(shù)據(jù)表一目了然，共有五列，最后一列“deleted”是一個Boolean類變量flag來檢查帳號是活動的還是已被刪除。接下來，您需要用樣本記錄填充這個表(比如，100個雇員記錄)。正如你看到的，主鍵是“emp_id”。因此，使用電子郵件地址和密碼字段，我們可以很容易地創(chuàng)建一個查詢，以驗證或拒絕登錄請求，如下(實例一)：

SELECT COUNT(*) FROM awesome_pcq WHERE email_id='blahblah' AND password='blahblah' AND deleted=0

之前我們提到，要避免使用COUNT(*)。代碼糾正如下(實例二)：

SELECT emp_id FROM awesome_pcq WHERE email_id='blahblah' AND password='blahblah' AND deleted=0

現(xiàn)在回想一下，在實例一中，代碼查詢定位并返回“email_id”和“password”等于給定值的行數(shù)。在實例二中，進行了同樣的查詢，不同的是明確要求列出“emp_id”所有滿足給定的標準的值。哪個查詢更費時?

很顯然，這兩個實例都是同樣費時的數(shù)據(jù)庫查詢，因為無意間，兩個實例查詢都進行了全表掃描。為了更好地讀懂指令，執(zhí)行如下代碼：

EXPLAIN SELECT emp_id FROM awesome_pcq WHERE email_id='blahblah' AND password='blahblah' AND deleted=0  在輸出時，集中在倒數(shù)第二列：“rows”。假設(shè)我們已經(jīng)將表填充了100個記錄，它會在第一行顯示100，這是MySQL需要進行掃描用來計算查詢的結(jié)果的行數(shù)。這說明了什么?這需要全表掃描。為了克服這個弊端，則需要添加索引。

6.添加索引

先從重要的說起：給每一個可能遇到的次要問題創(chuàng)建索引并不明智。過多的索引會導致效能減慢和資源占用。在進一步討論之前，在實例中創(chuàng)建一個樣本索引：

ALTER TABLE awesome_pcq ADDINDEX LoginValidate(email_id)  接下來，再次運行該查詢：

EXPLAIN SELECT emp_id FROM awesome_pcq WHERE email_id='blahblah' AND password='blahblah' AND deleted=0

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

但是，它不是每次重復將減少到一個。例如，如果不是唯一的索引字段(如employee names列可以有兩行相同的值)，即使創(chuàng)建索引，也將有多個記錄留下。但它仍然比全表掃描好。并且，在WHERE子句中指定列的順序沒有在這個過程中發(fā)揮作用。例如，如果在上面的查詢中，改變字段的順序，使電子郵件地址出現(xiàn)在最后，MySQL仍將遍歷索引列的基礎(chǔ)上。那么，就要在索引上動腦筋，注意如何避免大量的全表掃描，并獲得更好的結(jié)果。不過，這需要經(jīng)歷一個很長的過程。

河南億恩科技股份有限公司(www.zuiquanben.com)始創(chuàng)于2000年，專注服務器托管租用，是國家工信部認定的綜合電信服務運營商。億恩為近五十萬的用戶提供服務器托管、服務器租用、機柜租用、云服務器、網(wǎng)站建設(shè)、網(wǎng)站托管等網(wǎng)絡基礎(chǔ)服務，另有網(wǎng)總管、名片俠網(wǎng)絡推廣服務，使得客戶不斷的獲得更大的收益。
服務器/云主機 24小時售后服務電話：0371-60135900
虛擬主機/智能建站 24小時售后服務電話：0371-55621053
網(wǎng)絡版權(quán)侵權(quán)舉報電話：0371-60135995
服務熱線：0371-60135900