qileilove

Jaslabs的Justin Silverton列出了十條有關優化MySQL查詢的語句，我不得不對此發表言論，因為這個清單非常非常糟糕。另外一個Mike也同樣意識到了。所以在這個博客中，我要做兩件事情，第一，指出為什么這個清單很糟糕，第二，列出我的清單，希望我的比較好些。繼續看吧，無畏的讀者們！

　　為什么那個清單很糟糕

　　1、他的力氣沒使對地方

　　我們要遵循的一個準則就是如果你要優化代碼時，應該先找出瓶頸在哪。然而Silverton先生的力氣沒有用對地方。我認為60%的優化是基于清楚理解SQL和數據庫基 礎的。你需要知道join和子查詢的區別，列索引，以及如何將數據規范化等等。另外的35%的優化是需要清楚數據庫選擇時的性能表現，例如 COUNT（*）可能很快也可能很慢，要看你選用什么數據庫引擎。還有一些其他要考慮的因素，例如數據庫在什么時候不用緩存，什么時候存在硬盤上而不存在 內存中，什么時候數據庫創建臨時表等等。剩下的5%就很少會有人碰到了，但Silverton先生恰好在這上面花了大量的時間。我從來就沒用過 SQL_SAMLL_RESULT。

　　2、很好的問題，但是很糟糕的解決方法

　　 Silverton先生提出了一些很好的問題。MySQL針對長度可變的列如TEXT或BLOB，將會使用動態行格式（dynamic row format），這意味著排序將在硬盤上進行。我們的方法不是要回避這些數據類型，而是將這些數據類型從原來的表中分離開，放入另外一個表中。下面的 schema可以說明這個想法：

CREATE TABLE posts (       id int UNSIGNED NOT NULL AUTO_INCREMENT,       author_id int UNSIGNED NOT NULL,       created timestamp NOT NULL,       PRIMARY KEY(id)   );       CREATE TABLE posts_data (       post_id int UNSIGNED NOT NULL.       body text,       PRIMARY KEY(post_id)   );

　　3、有點匪夷所思……

　 　他的許多建議都是讓人非常吃驚的，譬如“移除不必要的括號”。你這樣寫SELECT * FROM posts WHERE （author_id = 5 AND published = 1），還是這樣寫SELECT * FROM posts WHERE author_id = 5 AND published = 1 ，都不重要。任何比較好的DBMS都會自動進行識別做出處理。這種細節就好像C語言中是i++快些還是++i快些。真的，如果你把精力都花在這上面了，那 就不用寫代碼了。

　　我的列表

　　看看我的列表是不是更好吧。我先從最普遍的開始。

　　1、建立基準，建立基準，建立基準！

　　如果需要做決定的話，我們需要數據說話。什么樣的查詢是最糟的？瓶頸在哪？我什么情況下會寫出糟糕的查詢？基準測試可以讓你模擬高壓情況，然后借助性能測評工具，可以讓你發現數據庫配置中的錯誤。這樣的工具有supersmack， ab， SysBench。這些工具可以直接測試你的數據庫（譬如supersmack），或者模擬網絡流量（譬如ab）。

　　2、性能測試，性能測試，性能測試！

　　那么，當你能夠建立一些高壓情況之后，你需要找出配置中的錯誤。這就是性能測評工具可以幫你做的了。它可以幫你發現配置中的瓶頸，不論是在內存中，CPU中，網絡中，硬盤I/O，或者是以上皆有。

　　你要做的第一件事就是開啟慢查詢日志（slow query log），裝上mtop。這樣你就能獲取那些惡意的入侵者的信息了。有需要運行10秒的查詢語句正在破壞你的應用程序嗎？這些家伙會展示給你看他的查詢語句是怎么寫的。

　 　在你發現那些很慢的查詢語句后，你需要用MySQL自帶的工具，如EXPLAIN，SHOW STATUS，SHOW PROCESSLIST。它們會告訴你資源都消耗在哪了，查詢語句的缺陷在哪，譬如一個有三次join子查詢的查詢語句是否在內存中進行排序，還是在硬盤 上進行。當然你也應該使用測評工具如top，procinfo，vmstat等等獲取更多系統性能信息。

　3、減小你的schema

　　在你開始寫查詢語句之前，你需要設計schema。記住將一個表裝入內存所需要的空間大概是行數*一行的大小。除非你覺得世界上的每個人都會在 你的網站注冊2兆8000億次的話，否則你不需要采用BITINT作為你的user_id。同樣的，如果一個文本列是固定大小的話（譬如US郵編，通常 是”XXXXX-XXXX”的形式），采用VARCHAR的話會給每行增加多余的字節。

　　有些人對數據庫規范化不以為意，他們說這樣會形成相當復雜的schema。然而適當的規范化會減少化冗余數據。（適當的規范化）就意味著犧牲少 許性能，換取整體上更少的footprint，這種性能換取內存在計算機科學中是很常見的。最好的方法是IMO，就是開始先規范化，之后如果性能需要的 話，再反規范化。你的數據庫將會更邏輯化，你也不用過早的進行優化。（譯者注，這一段我不是很理解，可能翻譯錯了，歡迎糾正。）

　　4、拆分你的表

　　通常有些表只有一些列你是經常需要更新的。例如對于一個博客，你需要在許多不同地方顯示標題（如最近的文章列表），只在某個特定頁顯示概要或者全文。水平垂直拆分是很有幫助的：

CREATE TABLE posts_tags (       relation_id int UNSIGNED NOT NULL AUTO_INCREMENT,       post_id int UNSIGNED NOT NULL,       tag_id int UNSIGNED NOT NULL,       PRIMARY KEY(relation_id),       UNIQUE INDEX(post_id, tag_id)   );

　　artificial key完全是多余的，而且post-tag關系的數量將會受到整形數據的系統最大值的限制。

CREATE TABLE posts_tags (       post_id int UNSIGNED NOT NULL,       tag_id int UNSIGNED NOT NULL,       PRIMARY KEY(post_id, tag_id)   );

　　6、學習索引

　　你選擇的索引的好壞很重要，不好的話可能破壞數據庫。對那些還沒有在數據庫學習很深入的人來說，索引可以看作是就是hash排序。例如如果我們 用查詢語句SELECT * FROM users WHERE last_name = ‘Goldstein’，而last_name沒有索引的話，那么DBMS將會查詢每一行，看看是否等于“Goldstein”。索引通常是B- tree（還有其他的類型），可以加快比較的速度。

　　你需要給你要select，group，order，join的列加上索引。顯然每個索引所需的空間正比于表的行數，所以越多的索引將會占用更 多的內存。而且寫數據時，索引也會有影響，因為每次寫數據時都會更新對應的索引。你需要取一個平衡點，取決每個系統和實施代碼的需要。

　　7、SQL不是C

　　C是經典的過程語言，對于一個程序員來說，C語言也是個陷阱，使你錯誤的以為SQL也是一種過程語言（當然SQL也不是功能語言也不是面向對象的）。你不要想象對數據進行操作，而是要想象有一組數據，以及它們之間的關系。經常使用子查詢時會出現錯誤的用法。

SELECT a.id,       (SELECT MAX(created)       FROM posts       WHERE author_id = a.id)   AS latest_post   FROM authors a

　因為這個子查詢是耦合的，子查詢要使用外部查詢的信息，我們應該使用join來代替。

SELECT a.id, MAX(p.created) AS latest_post   FROM authors a   INNER JOIN posts p       ON (a.id = p.author_id)   GROUP BY a.id

　　8、理解你的引擎

　　MySQL有兩種存儲引擎：MyISAM和InnoDB。它們分別有自己的性能特點和考慮因素。總體來講，MyISAM適合讀數據很多的情況，InnoDB適合寫數據很多的情況，但也有很多情況下正好相反。最大的區別是它們如何處理COUNT函數。

　　MyISAM緩存有表meta-data，如行數。這就意味著，COUNT(*)對于一個結構很好的查詢是不需要消耗多少資源的。然后對于 InnoDB來說，就沒有這種緩存。舉個例子，我們要對一個查詢來分頁，假設你有這樣一個語句SELECT * FROM users LIMIT 5，10，而運行SELECT COUNT(*) FROM users LIMIT 5，10 時，對于MyISAM很快完成，而對InnoDB就需要和第一個語句相同的時間。MySQL有個SQL_CALC_FOUND_ROWS選項，可以告訴 InnoDB運行查詢語句時就計算行數，之后再從SELECT FOUND_ROWS()來獲取。這是MySQL特有的。但使用InnoDB有時候是非常必要的，你可以獲得一些功能（如行鎖定，stord procedure等）。

　　9、MySQL特定的快捷鍵

　　MySQL提供了許多擴展，方便使用。譬如INSERT … SELECT， INSERT … ON DUPLICATE KEY UPDATE， 以及REPLACE。

　　我能用到它們時是毫不猶豫的，因為它們很方便，能在許多情況下發揮不錯的效果。但是MySQL也有一些危險的關鍵字，應該少用。例如 INSERT DELAYED，它告訴MySQL不需要立即插入數據(例如在寫日志的時候)。但問題是如果在很高數據量的情況下，插入可能會被無限期延遲，導致插入隊列 爆滿。你也可以使用MySQL的索引提示來指出哪些索引是需要使用的。MySQL大部分時間運行是不錯的，但如果schema設計不好的話或語句寫得不好 的話，MySQL的表現可能很糟糕。

　　10、到這里為止吧

　　最后，如果你關心MySQL性能優化的話，請閱讀Peter Zaitsev的關于MySQL性能的博客，他寫了許多關于數據庫管理和優化的博客。