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 (

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多對多的關系表時,我們不應該這樣：

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性能的博客,他寫了許多關于數據庫管理和優化的博客.

作者：20bits.com