Jaslabs的Justin Silverton列出了十條有關(guān)優(yōu)化MySQL查詢(xún)的語(yǔ)句，我不得不對(duì)此發(fā)表言論，因?yàn)檫@個(gè)清單非常非常糟糕。另外一個(gè)Mike也同樣意識(shí)到了。所以在這個(gè)博客中，我要做兩件事情，第一，指出為什么這個(gè)清單很糟糕，第二，列出我的清單，希望我的比較好些。繼續(xù)看吧，無(wú)畏的讀者們！
(譯者注：作者借這個(gè)題目反諷另一篇同名的文章)
為什么那個(gè)清單很糟糕
1 他的力氣沒(méi)使對(duì)地方
我們要遵循的一個(gè)準(zhǔn)則就是如果你要優(yōu)化代碼時(shí)，應(yīng)該先找出瓶頸在哪。然而Silverton先生的力氣沒(méi)有用對(duì)地方。我認(rèn)為60%的優(yōu)化是基于清楚 理解SQL和數(shù)據(jù)庫(kù)基礎(chǔ)的。你需要知道join和子查詢(xún)的區(qū)別，列索引，以及如何將數(shù)據(jù)規(guī)范化等等。另外的35%的優(yōu)化是需要清楚數(shù)據(jù)庫(kù)選擇時(shí)的性能表 現(xiàn)，例如COUNT(*)可能很快也可能很慢，要看你選用什么數(shù)據(jù)庫(kù)引擎。還有一些其他要考慮的因素，例如數(shù)據(jù)庫(kù)在什么時(shí)候不用緩存，什么時(shí)候存在硬盤(pán)上 而不存在內(nèi)存中，什么時(shí)候數(shù)據(jù)庫(kù)創(chuàng)建臨時(shí)表等等。剩下的5%就很少會(huì)有人碰到了，但Silverton先生恰好在這上面花了大量的時(shí)間。我從來(lái)就沒(méi)用過(guò) SQL_SAMLL_RESULT。
2. 很好的問(wèn)題，但是很糟糕的解決方法
Silverton先生提出了一些很好的問(wèn)題。MySQL針對(duì)長(zhǎng)度可變的列如TEXT或BLOB，將會(huì)使用動(dòng)態(tài)行格式(dynamic row format)，這意味著排序?qū)⒃谟脖P(pán)上進(jìn)行。我們的方法不是要回避這些數(shù)據(jù)類(lèi)型，而是將這些數(shù)據(jù)類(lèi)型從原來(lái)的表中分離開(kāi)，放入另外一個(gè)表中。下面的 schema可以說(shuō)明這個(gè)想法：
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. 有點(diǎn)匪夷所思……
他的許多建議都是讓人非常吃驚的，譬如“移除不必要的括號(hào)”。你這樣寫(xiě)SELECT * FROM posts WHERE (author_id = 5 AND published = 1)，還是這樣寫(xiě)SELECT * FROM posts WHERE author_id = 5 AND published = 1 ，都不重要。任何比較好的DBMS都會(huì)自動(dòng)進(jìn)行識(shí)別做出處理。這種細(xì)節(jié)就好像C語(yǔ)言中是i++快些還是++i快些。真的，如果你把精力都花在這上面了，那就不用寫(xiě)代碼了。
我的列表
看看我的列表是不是更好吧。我先從最普遍的開(kāi)始。
1. 建立基準(zhǔn)，建立基準(zhǔn)，建立基準(zhǔn)！
如果需要做決定的話，我們需要數(shù)據(jù)說(shuō)話。什么樣的查詢(xún)是最糟的？瓶頸在哪？我什么情況下會(huì)寫(xiě)出糟糕的查詢(xún)？基準(zhǔn)測(cè)試可以讓你模擬高壓情況，然后借助 性能測(cè)評(píng)工具，可以讓你發(fā)現(xiàn)數(shù)據(jù)庫(kù)配置中的錯(cuò)誤。這樣的工具有supersmack, ab, SysBench。這些工具可以直接測(cè)試你的數(shù)據(jù)庫(kù)(譬如supersmack)，或者模擬網(wǎng)絡(luò)流量（譬如ab）。
2. 性能測(cè)試，性能測(cè)試，性能測(cè)試!
那么，當(dāng)你能夠建立一些高壓情況之后，你需要找出配置中的錯(cuò)誤。這就是性能測(cè)評(píng)工具可以幫你做的了。它可以幫你發(fā)現(xiàn)配置中的瓶頸，不論是在內(nèi)存中，CPU中，網(wǎng)絡(luò)中，硬盤(pán)I/O，或者是以上皆有。
你要做的第一件事就是開(kāi)啟慢查詢(xún)?nèi)罩?slow query log)，裝上mtop。這樣你就能獲取那些惡意的入侵者的信息了。有需要運(yùn)行10秒的查詢(xún)語(yǔ)句正在破壞你的應(yīng)用程序嗎？這些家伙會(huì)展示給你看他的查詢(xún)語(yǔ)句是怎么寫(xiě)的。
在你發(fā)現(xiàn)那些很慢的查詢(xún)語(yǔ)句后，你需要用MySQL自帶的工具，如EXPLAIN，SHOW STATUS，SHOW PROCESSLIST。它們會(huì)告訴你資源都消耗在哪了，查詢(xún)語(yǔ)句的缺陷在哪，譬如一個(gè)有三次join子查詢(xún)的查詢(xún)語(yǔ)句是否在內(nèi)存中進(jìn)行排序，還是在硬盤(pán) 上進(jìn)行。當(dāng)然你也應(yīng)該使用測(cè)評(píng)工具如top，procinfo，vmstat等等獲取更多系統(tǒng)性能信息。
3. 減小你的schema
在你開(kāi)始寫(xiě)查詢(xún)語(yǔ)句之前，你需要設(shè)計(jì)schema。記住將一個(gè)表裝入內(nèi)存所需要的空間大概是行數(shù)*一行的大小。除非你覺(jué)得世界上的每個(gè)人都會(huì)在你的 網(wǎng)站注冊(cè)2兆8000億次的話，否則你不需要采用BITINT作為你的user_id。同樣的，如果一個(gè)文本列是固定大小的話（譬如US郵編，通常 是”XXXXX-XXXX”的形式），采用VARCHAR的話會(huì)給每行增加多余的字節(jié)。
有些人對(duì)數(shù)據(jù)庫(kù)規(guī)范化不以為意，他們說(shuō)這樣會(huì)形成相當(dāng)復(fù)雜的schema。然而適當(dāng)?shù)囊?guī)范化會(huì)減少化冗余數(shù)據(jù)。（適當(dāng)?shù)囊?guī)范化）就意味著犧牲少許性 能，換取整體上更少的footprint，這種性能換取內(nèi)存在計(jì)算機(jī)科學(xué)中是很常見(jiàn)的。最好的方法是IMO，就是開(kāi)始先規(guī)范化，之后如果性能需要的話，再 反規(guī)范化。你的數(shù)據(jù)庫(kù)將會(huì)更邏輯化，你也不用過(guò)早的進(jìn)行優(yōu)化。（譯者注，這一段我不是很理解，可能翻譯錯(cuò)了，歡迎糾正。）
4. 拆分你的表
通常有些表只有一些列你是經(jīng)常需要更新的。例如對(duì)于一個(gè)博客，你需要在許多不同地方顯示標(biāo)題（如最近的文章列表），只在某個(gè)特定頁(yè)顯示概要或者全文。水平垂直拆分是很有幫助的：
CREATE TABLE posts (
id int UNSIGNED NOT NULL AUTO_INCREMENT,
author_id int UNSIGNED NOT NULL,
title var char( 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是對(duì)讀數(shù)據(jù)進(jìn)行的優(yōu)化。經(jīng)常要訪問(wèn)的數(shù)據(jù)存在一個(gè)表中，那些不經(jīng)常訪問(wèn)的數(shù)據(jù)放在另一個(gè)。被拆分后，不經(jīng)常訪問(wèn)的數(shù)據(jù)占據(jù)更少的 內(nèi)存。你也可以?xún)?yōu)化寫(xiě)數(shù)據(jù)，經(jīng)常更新的數(shù)據(jù)放在一個(gè)表，不經(jīng)常更新的放在另一個(gè)表。這可以使緩存更高效，因?yàn)镸ySQL不需要讓沒(méi)有更新過(guò)的數(shù)據(jù)移出緩 存。
5. 不要過(guò)度使用artificial primary key
artificial primary key非常棒，因?yàn)樗麄兪沟胹chema更少的變化。如果我們將地理信息存在以美國(guó)郵編為基礎(chǔ)的表中，如果郵編系統(tǒng)突然改變了，那我們就會(huì)有大麻煩了。另 一方面，采用natural key有時(shí)候也很棒，譬如我們需要join多對(duì)多的關(guān)系表時(shí)，我們不應(yīng)該這樣：
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ù)量將會(huì)受到整形數(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ù)庫(kù)。對(duì)那些還沒(méi)有在數(shù)據(jù)庫(kù)學(xué)習(xí)很深入的人來(lái)說(shuō)，索引可以看作是就是hash排序。例如如果我們用查詢(xún)語(yǔ)句SELECT * FROM users WHERE last_name = ‘Goldstein’，而last_name沒(méi)有索引的話，那么DBMS將會(huì)查詢(xún)每一行，看看是否等于“Goldstein”。索引通常是B-tree（還有其他的類(lèi)型），可以加快比較的速度。
你需要給你要select,group,order,join的列加上索引。顯然每個(gè)索引所需的空間正比于表的行數(shù)，所以越多的索引將會(huì)占用更多的 內(nèi)存。而且寫(xiě)數(shù)據(jù)時(shí)，索引也會(huì)有影響，因?yàn)槊看螌?xiě)數(shù)據(jù)時(shí)都會(huì)更新對(duì)應(yīng)的索引。你需要取一個(gè)平衡點(diǎn)，取決每個(gè)系統(tǒng)和實(shí)施代碼的需要。
7. SQL不是C
C是經(jīng)典的過(guò)程語(yǔ)言，對(duì)于一個(gè)程序員來(lái)說(shuō)，C語(yǔ)言也是個(gè)陷阱，使你錯(cuò)誤的以為SQL也是一種過(guò)程語(yǔ)言（當(dāng)然SQL也不是功能語(yǔ)言也不是面向?qū)ο蟮模�。你不要想象�?duì)數(shù)據(jù)進(jìn)行操作，而是要想象有一組數(shù)據(jù)，以及它們之間的關(guān)系。經(jīng)常使用子查詢(xún)時(shí)會(huì)出現(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è)子查詢(xún)是耦合的，子查詢(xún)要使用外部查詢(xún)的信息，我們應(yīng)該使用join來(lái)代替。
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有兩種存儲(chǔ)引擎：MyISAM和InnoDB。它們分別有自己的性能特點(diǎn)和考慮因素�？傮w來(lái)講，MyISAM適合讀數(shù)據(jù)很多的情況，InnoDB適合寫(xiě)數(shù)據(jù)很多的情況，但也有很多情況下正好相反。最大的區(qū)別是它們?nèi)绾翁幚鞢OUNT函數(shù)。
MyISAM緩存有表meta-data，如行數(shù)。這就意味著，COUNT(*)對(duì)于一個(gè)結(jié)構(gòu)很好的查詢(xún)是不需要消耗多少資源的。然后對(duì)于 InnoDB來(lái)說(shuō)，就沒(méi)有這種緩存。舉個(gè)例子，我們要對(duì)一個(gè)查詢(xún)來(lái)分頁(yè)，假設(shè)你有這樣一個(gè)語(yǔ)句SELECT * FROM users LIMIT 5,10，而運(yùn)行SELECT COUNT(*) FROM users LIMIT 5,10 時(shí)，對(duì)于MyISAM很快完成，而對(duì)InnoDB就需要和第一個(gè)語(yǔ)句相同的時(shí)間。MySQL有個(gè)SQL_CALC_FOUND_ROWS選項(xiàng)，可以告訴 InnoDB運(yùn)行查詢(xún)語(yǔ)句時(shí)就計(jì)算行數(shù)，之后再?gòu)腟ELECT FOUND_ROWS()來(lái)獲取。這是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ù)(例如在寫(xiě)日志的時(shí)候)。但問(wèn)題是如果在很高數(shù)據(jù)量的情況下，插入可能會(huì)被無(wú)限期延遲，導(dǎo)致插入隊(duì)列 爆滿。你也可以使用MySQL的索引提示來(lái)指出哪些索引是需要使用的。MySQL大部分時(shí)間運(yùn)行是不錯(cuò)的，但如果schema設(shè)計(jì)不好的話或語(yǔ)句寫(xiě)得不好 的話，MySQL的表現(xiàn)可能很糟糕。
10. 到這里為止吧
最后，如果你關(guān)心MySQL性能優(yōu)化的話，請(qǐng)閱讀Peter Zaitsev的關(guān)于MySQL性能的博客，他寫(xiě)了許多關(guān)于數(shù)據(jù)庫(kù)管理和優(yōu)化的博客。