歡迎您光臨本站 註冊首頁

優化MySQL語句的十個建議

←手機掃碼閱讀     火星人 @ 2014-03-12 , reply:0
  

 (譯者註:作者借這個題目反諷另一篇同名的文章)

 

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可以說明這個想法:

1
2
3
4
5
6
7
8
9
10
11
12
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.拆分你的表

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE posts (
    id int UNSIGNED NOT NULL AUTO_INCREMENT,
    author_id int UNSIGNED NOT NULL,
    title varchar(128),
    created timestamp NOT NULL,
    PRIMARY KEY(id)
);
 
CREATE TABLE posts_data (
    post_id int UNSIGNED NOT NULL,
    teaser text,
    body text,
    PRIMARY KEY(post_id)
);

上面的schema是對讀數據進行的優化。經常要訪問的數據存在一個表中,那些不經常訪問的數據放在另一個。被拆分后,不經常訪問的數據佔據更少的內存。你也可以優化寫數據,經常更新的數據放在一個表,不經常更新的放在另一個表。這可以使緩存更高效,因為MySQL不需要讓沒有更新過的數據移出緩存。

 

5.不要過度使用artificial primary key

artificial primary key非常棒,因為他們使得schema更少的變化。如果我們將地理信息存在以美國郵編為基礎的表中,如果郵編系統突然改變了,那我們就會有大麻煩了。另一方面,採用natural key有時候也很棒,譬如我們需要join多對多的關係表時,我們不應該這樣:

1
2
3
4
5
6
7
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關係的數量將會受到整形數據的系統最大值的限制。

1
2
3
4
5
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也不是功能語言也不是面向對象的)。你不要想象對數據進行操作,而是要想象有一組數據,以及它們之間的關係。經常使用子查詢時會出現錯誤的用法。

1
2
3
4
5
6
SELECT a.id,
    (SELECT MAX(created)
    FROM posts
    WHERE author_id = a.id)
AS latest_post
FROM authors a

 

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

1
2
3
4
5
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性能的博客,他寫了許多關於資料庫管理和優化的博客。

 

原文鏈接:20bits.com   編譯:伯樂在線?- 唐小娟



[火星人 ] 優化MySQL語句的十個建議已經有395次圍觀

http://coctec.com/docs/linux/show-post-74029.html