我的列表

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

1.建立基準(zhǔn)，建立基準(zhǔn)，建立基準(zhǔn)！

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

2.性能測試，性能測試，性能測試!

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

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

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

3.減小你的schema

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

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

4.拆分你的表

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

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關(guān)系的數(shù)量將會受到整形數(shù)據(jù)的系統(tǒng)最大值的限制。

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

6.學(xué)習(xí)索引

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

你需要給你要select,group,order,join的列加上索引。顯然每個(gè)索引所需的空間正比于表的行數(shù)，所以越多的索引將會占用更多的 內(nèi)存。而且寫數(shù)據(jù)時(shí)，索引也會有影響，因?yàn)槊看螌憯?shù)據(jù)時(shí)都會更新對應(yīng)的索引。你需要取一個(gè)平衡點(diǎn)，取決每個(gè)系統(tǒng)和實(shí)施代碼的需要。

7.SQL不是C

C是經(jīng)典的過程語言，對于一個(gè)程序員來說，C語言也是個(gè)陷阱，使你錯(cuò)誤的以為SQL也是一種過程語言（當(dāng)然SQL也不是功能語言也不是面向?qū)ο蟮模?。你不要想象對?shù)據(jù)進(jìn)行操作，而是要想象有一組數(shù)據(jù)，以及它們之間的關(guān)系。經(jīng)常使用子查詢時(shí)會出現(xiàn)錯(cuò)誤的用法。

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

因?yàn)檫@個(gè)子查詢是耦合的，子查詢要使用外部查詢的信息，我們應(yīng)該使用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。它們分別有自己的性能特點(diǎn)和考慮因素。總體來講，MyISAM適合讀數(shù)據(jù)很多的情況，InnoDB適合寫數(shù)據(jù)很多的情況，但也有很多情況下正好相反。最大的區(qū)別是它們?nèi)绾翁幚鞢OUNT函數(shù)。

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

9.MySQL特定的快捷鍵

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

我能用到它們時(shí)是毫不猶豫的，因?yàn)樗鼈兒芊奖悖茉谠S多情況下發(fā)揮不錯(cuò)的效果。但是MySQL也有一些危險(xiǎn)的關(guān)鍵字，應(yīng)該少用。例如INSERT DELAYED,它告訴MySQL不需要立即插入數(shù)據(jù)(例如在寫日志的時(shí)候)。但問題是如果在很高數(shù)據(jù)量的情況下，插入可能會被無限期延遲，導(dǎo)致插入隊(duì)列 爆滿。你也可以使用MySQL的索引提示來指出哪些索引是需要使用的。MySQL大部分時(shí)間運(yùn)行是不錯(cuò)的，但如果schema設(shè)計(jì)不好的話或語句寫得不好 的話，MySQL的表現(xiàn)可能很糟糕。

10.到這里為止吧

最后，如果你關(guān)心MySQL性能優(yōu)化的話，請閱讀Peter Zaitsev的關(guān)于MySQL性能的博客，他寫了許多關(guān)于數(shù)據(jù)庫管理和優(yōu)化的博客。

原文鏈接：20bits.com

原文鏈接：http://blog.jobbole.com/13876/

zhaohang