優(yōu)化MySQL語(yǔ)句的幾個(gè)絕招Jaslabs的JustinSilverton列出了十條有關(guān)優(yōu)化MySQL查詢(xún)的語(yǔ)句，我不得不對(duì)此發(fā)表言論，因?yàn)檫@個(gè)清單非常非常糟糕。另外一個(gè)Mike也同樣意識(shí)到了。所以在這個(gè)博客中，我要做兩件事情，第一，指出為什么這個(gè)清單很糟糕，第二，列出我的清單，希望我的比較好些。繼續(xù)看吧，無(wú)畏的讀者們！為什么那個(gè)清單很糟糕

1.很好的問(wèn)題，但是很糟糕的解決方法Silverton先生提出了一些很好的問(wèn)題。MySQL針對(duì)長(zhǎng)度可變的列如TEXT或BLOB，將會(huì)使用動(dòng)態(tài)行格式(dynamicrowformat)，這意味著排序?qū)⒃谟脖P(pán)上進(jìn)行。我們的方法不是要回避這些數(shù)據(jù)類(lèi)型，而是將這些數(shù)據(jù)類(lèi)型從原來(lái)的表中分離開(kāi)，放入另外一個(gè)表中。下面的schema可以說(shuō)明這個(gè)想法：CREATETABLEposts(idintUNSIGNEDNOTNULLAUTO_INCREMENT,author_idintUNSIGNEDNOTNULL,createdtimestampNOTNULL,PRIMARYKEY(id));

CREATETABLEposts_data(post_idintUNSIGNEDNOTNULL.bodytext,PRIMARYKEY(post_id));2.他的力氣沒(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。3.有點(diǎn)匪夷所思……他的許多建議都是讓人非常吃驚的，譬如“移除不必要的括號(hào)”。你這樣寫(xiě)SELECT*FROMpostsWHERE(author_id=5ANDpublished=1)，還是這樣寫(xiě)SELECT*FROMpostsWHEREauthor_id=5ANDpublished=1，都不重要。任何比較好的DBMS都會(huì)自動(dòng)進(jìn)行識(shí)別做出處理。這種細(xì)節(jié)就好像C語(yǔ)言中是i++快些還是++i快些。真的，如果你把精力都花在這上面了，那就不用寫(xiě)代碼了。\o"mysql"

我的列表看看我的列表是不是更好吧。我先從最普遍的開(kāi)始。

1.減小你的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ò)了，歡迎糾正。）2.拆分你的表通常有些表只有一些列你是經(jīng)常需要更新的。例如對(duì)于一個(gè)博客，你需要在許多不同地方顯示標(biāo)題（如最近的文章列表），只在某個(gè)特定頁(yè)顯示概要或者全文。水平垂直拆分是很有幫助的：CREATETABLEposts_tags(relation_idintUNSIGNEDNOTNULLAUTO_INCREMENT,post_idintUNSIGNEDNOTNULL,tag_idintUNSIGNEDNOTNULL,PRIMARYKEY(relation_id),UNIQUEINDEX(post_id,tag_id));artificialkey完全是多余的，而且post-tag關(guān)系的數(shù)量將會(huì)受到整形數(shù)據(jù)的系統(tǒng)最大值的限制。CREATETABLEposts_tags(post_idintUNSIGNEDNOTNULL,tag_idintUNSIGNEDNOTNULL,PRIMARYKEY(post_id,tag_id));3.學(xué)習(xí)索引你選擇的索引的好壞很重要，不好的話可能破壞數(shù)據(jù)庫(kù)。對(duì)那些還沒(méi)有在數(shù)據(jù)庫(kù)學(xué)習(xí)很深入的人來(lái)說(shuō)，索引可以看作是就是hash排序。例如如果我們用查詢(xún)語(yǔ)句SELECT*FROMusersWHERElast_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í)施代碼的需要。4.建立基準(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）。5.性能測(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)罩?slowquerylog)，裝上mtop。這樣你就能獲取那些惡意的入侵者的信息了。有需要運(yùn)行10秒的查詢(xún)語(yǔ)句正在破壞你的應(yīng)用程序嗎？這些家伙會(huì)展示給你看他的查詢(xún)語(yǔ)句是怎么寫(xiě)的。在你發(fā)現(xiàn)那些很慢的查詢(xún)語(yǔ)句后，你需要用MySQL自帶的工具，如EXPLAIN，SHOWSTATUS，SHOWPROCESSLIST。它們會(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)性能信息。(電腦屏幕分辨率/html/xwzx/6625.html

)6.SQL不是CC是經(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ò)誤的用法。SELECTa.id,(SELECTMAX(created)FROMpostsWHEREauthor_id=a.id)ASlatest_postFROMauthorsa因?yàn)檫@個(gè)子查詢(xún)是耦合的，子查詢(xún)要使用外部查詢(xún)的信息，我們應(yīng)該使用join來(lái)代替。SELECTa.id,MAX(p.created)ASlatest_postFROMauthorsaINNERJOINpostspON(a.id=p.author_id)GROUPBYa.id7.理解你的引擎MySQL有兩種存儲(chǔ)引擎：MyISAM和InnoDB。它們分別有自己的性能特點(diǎn)和考慮因素。總體來(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*FROMusersLIMIT5,10，而運(yùn)行SELECTCOUNT(*)FROMusersLIMIT5,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)腟ELECTFOUND_ROWS()來(lái)獲取。這是MySQL特有的。但使用InnoDB有時(shí)候是非常必要的，你可以獲得一些功能（如行鎖定，stordprocedure等）。8.MySQL特定的快捷鍵MySQL提供了許多擴(kuò)展，方便使用。譬如INSERT…SELECT,INSERT…ONDUPLICATEKEYUPDATE,以及REPLACE。我能用到它們時(shí)是毫不猶豫的，因?yàn)樗鼈兒芊奖?，能在許多情況下發(fā)揮不錯(cuò)的效果。但是MySQL也有一些危險(xiǎn)的關(guān)鍵字，應(yīng)該少用。例如INSERTDELAYED,它告訴MySQL不需要立即插入數(shù)據(jù)(