程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL數據庫性能優化六大技巧

MySQL數據庫性能優化六大技巧

編輯: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進行深入探討。

首先,創建一個簡單的數據表:

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

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

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

現在回想一下,在實例一中,代碼查詢定位並返回“email_id”和“password”等於給定值的行數。在實例二中,進行了同樣的查詢,不同的是明確要求列出“emp_id”所有滿足給定的標准的值。哪個查詢更費時?

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

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

6.添加索引

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

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。因此,為了給出查詢結果,MySQL只掃描了1行,多虧先前創建的索引。你可能會注意到,索引只在電子郵件地址字段創建,而查詢對其他字段同樣進行了搜索。這表明MySQL先執行了一個cros-check,檢查是否有在WHERE子句中的定義的值有索引指定,如果有這樣的值就執行相應的操作。

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

 

作者kongdaoxian

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved