MySQL數(shù)據(jù)庫(kù)優(yōu)化總結(jié)

對(duì)于一個(gè)以數(shù)據(jù)為中心的應(yīng)用，數(shù)據(jù)庫(kù)的好壞直接影響到程序的性能，因此數(shù)據(jù)庫(kù)性能至關(guān)重要。一般來(lái)說(shuō)，要保證數(shù)據(jù)庫(kù)的效率，要做好以下四個(gè)方面的工作：數(shù)據(jù)庫(kù)設(shè)計(jì)、sql語(yǔ)句優(yōu)化、數(shù)據(jù)庫(kù)參數(shù)配置、恰當(dāng)?shù)挠布Y源和操作系統(tǒng)，這個(gè)順序也表現(xiàn)了這四個(gè)工作對(duì)性能影響的大小。下面我們逐個(gè)闡明：一、數(shù)據(jù)庫(kù)設(shè)計(jì)適度的反范式，注意是適度的。我們都知道三范式，基于三范式建立的模型是最有效保存數(shù)據(jù)的方式，也是最容易擴(kuò)展的模式。我們?cè)陂_(kāi)發(fā)應(yīng)用程序時(shí)，設(shè)計(jì)的數(shù)據(jù)庫(kù)要最大程度的遵守三范式，特別是對(duì)于OLTP型的系統(tǒng)，三范式是必須遵守的規(guī)則。當(dāng)然，三范式最大的問(wèn)題在于查詢時(shí)通常需要join很多表，導(dǎo)致查詢效率很低。所以有時(shí)候基于性能考慮，我們需要有意的違反三范式，適度的做冗余，以達(dá)到提高查詢效率的日的。注意這里的反范式是適度的，必須為這種做法提供充分的理由。下面就是一個(gè)糟糕的實(shí)例：學(xué)生活動(dòng)記錄單位信息活動(dòng)標(biāo)識(shí) 金iTI 匐活動(dòng)時(shí)間 TS積容■ I單位各稱(chēng).(冗余) VA32單位標(biāo)識(shí)<p煮I<M>'單位茗禰 VA32-'"在這里，為了提高學(xué)生活動(dòng)記錄的檢索效率，把單位名稱(chēng)冗余到學(xué)生活動(dòng)記錄表里。單位信息有500條記錄，而學(xué)生活動(dòng)記錄在一年內(nèi)大概有200萬(wàn)數(shù)據(jù)量。如果學(xué)生活動(dòng)記錄表不冗余這個(gè)單位名稱(chēng)字段，只包含三個(gè)int字段和一個(gè)timestamp字段，只占用了16字節(jié)，是一個(gè)很小的表。而冗余了一個(gè)varchar(32)的字段后則是原來(lái)的3倍，檢索起來(lái)相應(yīng)也多了這么多的I/O。而

且記錄數(shù)相差懸殊，500VS2000000，導(dǎo)致更新一個(gè)單位名稱(chēng)還要更新4000條冗余記錄。由此可見(jiàn)，這個(gè)冗余根本就是適得其反。卜面這個(gè)冗余就很好可以看到，［學(xué)生考試總分］是冗余的，這個(gè)分?jǐn)?shù)完全可以通過(guò)［得分情況］匯總得到。在【學(xué)生考試總分】里，一次考試一個(gè)學(xué)生只有一條記錄，而在【得分情況】里，一個(gè)學(xué)生針對(duì)試卷里一個(gè)小題的一個(gè)小問(wèn)一條記錄，粗略的算一下比例大概是1：100。而且判卷子得分是不會(huì)輕易變的，更新的頻率不高，所以說(shuō)這個(gè)冗余是比較好的。適當(dāng)建立索引說(shuō)起提高數(shù)據(jù)庫(kù)性能，索引是最物美價(jià)廉的東西了。不用加少存，不用改程序，不用調(diào)sql，只要執(zhí)行個(gè)正確的’createindex’，查詢速度就可能提高百倍千倍，這可真有誘惑力。可是天下沒(méi)有免費(fèi)的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價(jià)的，這些寫(xiě)操作，增加了大量的I/O。由于索引的存儲(chǔ)結(jié)構(gòu)不同于表的存儲(chǔ)，一個(gè)表的索引所占空間比數(shù)據(jù)所占空間還大的情況經(jīng)常發(fā)生。這意味著我們?cè)趯?xiě)數(shù)據(jù)庫(kù)的時(shí)候做了很多額外的工作，而這個(gè)工作只是為了提高讀的效率。因此，我們建立一個(gè)索引，必須保證這個(gè)索引不會(huì)“虧本”。一般需要遵守這樣的規(guī)則：（1） 索引的字段必須是經(jīng)常作為查詢條件的字段（2） 如果索引多個(gè)字段，第一個(gè)字段要是經(jīng)常作為查詢條件的。如果只有第二個(gè)字段作為查詢條件，這個(gè)索引不會(huì)起到作用；（3） 索引的字段必須有足夠的區(qū)分度；（4） Mysql對(duì)于長(zhǎng)字段支持前綴索引。對(duì)表進(jìn)行水平劃分如果一個(gè)表的記錄數(shù)太多了，比如上千萬(wàn)條，而且需要經(jīng)常檢索，那么我們就有必要化整為零了。如果我拆成100個(gè)表，那么每個(gè)表只有10萬(wàn)條記錄。當(dāng)然這需要數(shù)據(jù)在邏輯上可以劃分。一個(gè)好的劃分依據(jù)，有利于程序的簡(jiǎn)單實(shí)現(xiàn)，也可以充分利用水平分表的優(yōu)勢(shì)。比如系統(tǒng)界面上只提供按月查詢的功能，那么把表按月拆分成12個(gè)，每個(gè)查詢只查詢一個(gè)表就夠了。如果非要按照地域來(lái)分，即使把表拆的再小，查詢還是要聯(lián)合所有表來(lái)查，還不如不拆了。所以一個(gè)好的拆分依據(jù)是最重要的。這里有個(gè)比較好的實(shí)例學(xué)生磁題本學(xué)空錯(cuò)我本標(biāo)識(shí)號(hào)int班縱標(biāo)識(shí)號(hào)int試題標(biāo)識(shí)int人員標(biāo)識(shí)號(hào)int51<學(xué)科int題目來(lái)源類(lèi)型int做題時(shí)間datetime對(duì)錯(cuò)int每個(gè)學(xué)生做過(guò)的題都記錄在這個(gè)表里，包括對(duì)題和錯(cuò)題。每個(gè)題會(huì)對(duì)應(yīng)一個(gè)或多個(gè)知識(shí)點(diǎn)，我們需要根據(jù)錯(cuò)題來(lái)分析學(xué)生在哪個(gè)知識(shí)點(diǎn)上掌握的不足。這個(gè)表很容易達(dá)到千萬(wàn)級(jí)，迫切需要拆分，那么根據(jù)什么來(lái)拆呢?從需求上看，無(wú)論是老師還是學(xué)生，最終會(huì)把焦點(diǎn)落在一個(gè)學(xué)生的身上。學(xué)生會(huì)關(guān)心自己，老師會(huì)關(guān)心自己班的學(xué)生。而且每個(gè)學(xué)科的知識(shí)點(diǎn)是不同的。所以我們很容易想到，聯(lián)合學(xué)科和知識(shí)點(diǎn)兩個(gè)字段來(lái)拆分這個(gè)表。這樣拆下來(lái)，每個(gè)表大概2萬(wàn)條數(shù)據(jù)，檢索效率非常高。對(duì)表進(jìn)行垂直劃分有些表記錄數(shù)并不多，可能也就2、3萬(wàn)條，但是字段卻很長(zhǎng)，表占用空間很大，檢索表時(shí)需要執(zhí)行大量I/O,嚴(yán)重降低了性能。這個(gè)時(shí)候需要把大的字段拆分到另一個(gè)表，并且該表與原表是一對(duì)一的關(guān)系。

Fk_Ralatif)nsliip_d1單元標(biāo)識(shí)Fk_Ralatif)nsliip_d1單元標(biāo)識(shí)ini謀信息株識(shí)mlintini本隔幾何int蜓易程度iniini慶權(quán)信息Haichwr印時(shí)頁(yè)碼ini開(kāi)物閘g細(xì)m?題號(hào)wa忙隘附2）出成恩￡&varchMww是否聲音瘢inis階ar(26■時(shí)聲音頻文件標(biāo)就施Eh田w支件懵式藉^“FCh郝$技交時(shí)間dat^tim?授艾人ini供習(xí)珊標(biāo)沮號(hào)int成覲膝iniinr審蹣問(wèn)datetims不蛔原用varchard?S0冊(cè)糅標(biāo)志int選碾1量ihl*an；riarG55)FK_Reiatk)nshiM間瓣inr冬案 vm怖NBQQQ）【試題內(nèi)容】、【答案信息】?jī)蓚€(gè)表，最初是作為幾個(gè)字段添加到【試題信息】里的，可以看到試題內(nèi)容和答案這兩個(gè)字段很長(zhǎng)，在表里有3萬(wàn)記錄時(shí)，表已經(jīng)占了1G的空間，在列試題列表時(shí)非常慢。經(jīng)過(guò)分析，發(fā)現(xiàn)系統(tǒng)很多時(shí)候是根據(jù)【冊(cè)】、【單元】、類(lèi)型、類(lèi)別、難易程度等查詢條件，分頁(yè)顯示試題詳細(xì)內(nèi)容。而每次檢索都是這幾個(gè)表做join，每次要掃描一遍1G的表，很郁悶啊。我們完全可以把內(nèi)容和答案拆分成另一個(gè)表，只有顯示詳細(xì)內(nèi)容的時(shí)候才讀這個(gè)大表，由此就產(chǎn)生了【試題內(nèi)容】、【答案信息】?jī)蓚€(gè)表。選擇適當(dāng)?shù)淖侄晤?lèi)型，特別是主鍵選擇字段的一般原則是保小不保大，能用占用字節(jié)小的字段就不用大字段。比如主鍵，我們強(qiáng)烈建議用自增類(lèi)型，不用guid，為什么?省空間啊?空間是什么？空間就是效率!按4個(gè)字節(jié)和按32個(gè)字節(jié)定位一條記錄，誰(shuí)快誰(shuí)慢太明顯了。涉及到幾個(gè)表做join時(shí)，效果就更明顯了。值得一提的是，datetime和timestamp，datetime占用8個(gè)字節(jié)，而timestamp占用4個(gè)字節(jié)，只用了一半，而timestamp表示的范圍是1970—2037，對(duì)于大多數(shù)應(yīng)用，尤其是記錄什么考試時(shí)間，登錄時(shí)間這類(lèi)信息，綽綽有余啊。文件、圖片等大文件用文件系統(tǒng)存儲(chǔ)，不用數(shù)據(jù)庫(kù)不用多說(shuō)，鐵律！??！數(shù)據(jù)庫(kù)只存儲(chǔ)路徑。外鍵表示清楚，方便建立索引我們都知道，在powerdesigner里為兩個(gè)實(shí)體建立關(guān)系，生成物理模型時(shí)會(huì)自動(dòng)給外鍵建立索引。所以我們不要怕建立關(guān)系把線拉亂，建立個(gè)ShortCut就好了。掌握表的寫(xiě)入時(shí)機(jī)在庫(kù)模式相同的情況下，如何使用數(shù)據(jù)庫(kù)也對(duì)性能有著重要作用。同樣是寫(xiě)入一個(gè)表，先寫(xiě)和后寫(xiě)對(duì)后續(xù)的操作會(huì)產(chǎn)生很大影響。例如在上面提到的適度冗余里的例子，

我們最初的日的是記錄考生的總分，以達(dá)到提高檢索效率的日的，也就是在錄入成績(jī)時(shí)寫(xiě)入這個(gè)表。在需求里有這樣的要求：列出本次考試的所有學(xué)生成績(jī)，沒(méi)有錄入成績(jī)的也顯示該學(xué)生名稱(chēng)，只是總分顯示為空。這個(gè)查詢就需要用【學(xué)生信息】leftouterjoin【學(xué)生考試總分信息】，大家都知道outerjoin的效率比join是要低的，為了避免這個(gè)問(wèn)題，我們就在布置考試的時(shí)候?qū)懭脒@個(gè)表，把所有學(xué)生都插入進(jìn)去，分?jǐn)?shù)都是null，這樣一來(lái)我們就可以用join達(dá)到這個(gè)效果了。而且還有這樣的好處：在某次考試中，安排了一個(gè)班所有學(xué)生考試，所有學(xué)生都錄入了成績(jī)?，F(xiàn)在班里轉(zhuǎn)來(lái)一個(gè)新生，那么在此時(shí)如果查詢學(xué)生成績(jī)，就會(huì)列出這個(gè)新生，結(jié)果是未錄入成績(jī)，這顯然是不對(duì)的。如果在安排的時(shí)候就寫(xiě)入，就可以記錄下該次考試中實(shí)際的考生了，這個(gè)表的作用，也就不知是冗余了。寧可集中批量操作，避免頻繁讀寫(xiě)系統(tǒng)里包含了積分部分，學(xué)生和老師通過(guò)系統(tǒng)做了操作都可以獲得積分，而且積分規(guī)則很復(fù)雜，限制每類(lèi)操作獲得積分不同，每人每天每類(lèi)積分都有上限。比如登錄，一次登錄就可以獲得1分，但是不管你登錄多少次，一天只能累積一個(gè)登錄積分。這個(gè)還是簡(jiǎn)單的，有的積分很變態(tài)，比如老師積分中有一類(lèi)是看老師判作業(yè)的情況，規(guī)則是：老師判了作業(yè)，發(fā)現(xiàn)學(xué)生有錯(cuò)的，學(xué)生改過(guò)了，老師再判，如果這時(shí)候?qū)W生都對(duì)了，就給老師加分，如果學(xué)生還是錯(cuò)的，那就接著改，知道學(xué)生都改對(duì)了，老師都判完了，才能給老師加分。如果用程序來(lái)處理，很可能每個(gè)功能都會(huì)額外的寫(xiě)一堆代碼來(lái)處理這個(gè)雞肋似的積分。不僅編程的同事干活找不到重點(diǎn)，還平白給數(shù)據(jù)庫(kù)帶來(lái)了很大的壓力。經(jīng)過(guò)和需求人員的討論，確定積分沒(méi)有必要實(shí)時(shí)累積，于是我們采取后臺(tái)腳本批量處理的方式。夜深人靜的時(shí)候，讓機(jī)器自己玩去吧。這個(gè)變態(tài)的積分規(guī)則用批處理讀出來(lái)是這樣的：selectperson_id,@semester_id,301003,0,@one_marks,assign_date,@one_marksfromhom_assignmentinfoha,hom_assign_classhacwhereha.assignment_id=hac.assignment_idandha.assign_datebetween@time_beginand@time_encandha.assignment_idnotin(selecthaa.assignment_idfromhom_assignment_appraisehaa,hom_check_assignmenthcawherehaa.appraise_id=hca.appraise_idandhaa.if_submit=1and((hca.recheck_state = 3004001 and hca.check_resultin(3003002,3003003))TOC\o"1-5"\h\zor(hca.recheck_state = 3004002 andhca.recheck_resultin(3003002,3003003))))andha.assignment_idnot in(selectassignment_idfrom hom_assignment_appraisewhereif_submit=0andresult_type=0)andha.assignment_idin(selecthaa.assignment_idfromhom_assignment_appraisehaa,hom_check_assignmenthcawherehaa.appraise_id=hca.appraise_idandhaa.if_submit=1andhca.check_resultin(3003002,3003003));這還只是個(gè)中間過(guò)程，這要是用程序?qū)崟r(shí)處理，即使編程人員不罷工，數(shù)據(jù)庫(kù)也會(huì)歇了。選擇合適的引擎Mysql提供了很多種引擎，我們用的最多的是myisam，innodb，memory這三類(lèi)。官方手冊(cè)上說(shuō)道m(xù)yisqm比innodb的讀速度要快，大概是3倍。不過(guò)書(shū)不能盡信啊，《OreIlly.High.Performance.Mysql》這本書(shū)里提到了myisam和innodb的比較，在測(cè)試中myisam的表現(xiàn)還不及innodb。至于memory，哈哈，還是比較好用的。在批處理種作臨時(shí)表是個(gè)不錯(cuò)的選擇(如果內(nèi)存夠大)。在我的一個(gè)批處理中，速度比近乎1：10。二、SQL語(yǔ)句優(yōu)化慢日志如果發(fā)現(xiàn)系統(tǒng)慢了，又說(shuō)不清楚是哪里慢，那么就該用這個(gè)工具了。只需要為mysql配置參數(shù)，mysql會(huì)自己記錄下來(lái)慢的sql語(yǔ)句。配置很簡(jiǎn)單，參數(shù)文件里配置：slow_query_log=d:/slow.txtlong_query_time=2就可以在d:/slow.txt里找到執(zhí)行時(shí)間超過(guò)2秒的語(yǔ)句了，根據(jù)這個(gè)文件定位問(wèn)題吧。mysqldumpslow.pl慢日志文件可能會(huì)很大，讓人去看是很難受的事。這時(shí)候我們可以通過(guò)mysql自帶的工具來(lái)分析。這個(gè)工具可以格式化慢日志文件，對(duì)于只是參數(shù)不同的語(yǔ)句會(huì)歸類(lèi)類(lèi)并，比如有兩個(gè)語(yǔ)句select*fromawhereid=1和select*fromawhereid=2,經(jīng)過(guò)這個(gè)工具整理后就只剩下select*fromawhereid二N，這樣讀起來(lái)就舒服多了。而且這個(gè)工具可以實(shí)現(xiàn)簡(jiǎn)單的排序，讓我們有的放矢。Explain現(xiàn)在我們已經(jīng)知道是哪個(gè)語(yǔ)句慢了，那么它為什么慢呢?看看mysql是怎么執(zhí)行的吧，用explain可以看到mysql執(zhí)行計(jì)劃，下面的用法來(lái)源于手冊(cè)EXPLAIN語(yǔ)法（獲取SELECT相關(guān)信息）EXPLAIN[EXTENDED]SELECTselect_optionsEXPLAIN語(yǔ)句可以用作DESCRIBE的一個(gè)同義詞，或獲得關(guān)于MySQL如何執(zhí)行SELECT語(yǔ)句的信息：?EXPLAINtbl_name是DESCRIBEtbl_name或SHOWCOLUMNSFROMtbl_name的一個(gè)同義詞。?如果在SELECT語(yǔ)句前放上關(guān)鍵詞EXPLAIN，MySQL將解釋它如何處理SELECT，提供有關(guān)表如何聯(lián)接和聯(lián)接的次序。11-該節(jié)解釋EXPLAIN的第2個(gè)用法。借助于EXPLAIN,可以知道什么時(shí)候必須為表加入索引以得到一個(gè)使用索引來(lái)尋找記錄的更快的SELECTo如果由于使用不正確的索引出現(xiàn)了問(wèn)題，應(yīng)運(yùn)行ANALYZETABLE更新表的統(tǒng)計(jì)(例如關(guān)鍵字集的勢(shì))，這樣會(huì)影響優(yōu)化器進(jìn)行的選擇。還可以知道優(yōu)化器是否以一個(gè)最佳次序聯(lián)接表。為了強(qiáng)制優(yōu)化器讓一個(gè)SELECT語(yǔ)句按照表命名順序的聯(lián)接次序，語(yǔ)句應(yīng)以STRAIGHT_JOIN而不只是SELECT開(kāi)頭。EXPLAIN為用于SELECT語(yǔ)句中的每個(gè)表返回一行信息。表以它們?cè)谔幚聿樵冞^(guò)程中將被MySQL讀入的順序被列出。MySQL用一遍掃描多次聯(lián)接(single-sweepmulti-join)的方式解決所有聯(lián)接。這意味著MySQL從第一個(gè)表中讀一行，然后找到在第二個(gè)表中的一個(gè)匹配行，然后在第3個(gè)表中等等。當(dāng)所有的表處理完后，它輸出選中的列并且返回表清單直到找到一個(gè)有更多的匹配行的表。從該表讀入下一行并繼續(xù)處理下一個(gè)表。當(dāng)使用EXTENDED關(guān)鍵字時(shí)，EXPLAIN產(chǎn)生附加信息，可以用SHOWWARNINGS瀏覽。該信息顯示優(yōu)化器限定SELECT語(yǔ)句中的表和列名，重寫(xiě)并且執(zhí)行優(yōu)化規(guī)則后SELECT語(yǔ)句是什么樣子，并且還可能包括優(yōu)化過(guò)程的其它注解。如果什么都做不了，試試全索引掃描如果一個(gè)語(yǔ)句實(shí)在不能優(yōu)化了，那么還有一個(gè)方法可以試試：索引覆蓋。如果一個(gè)語(yǔ)句可以從索引上獲取全部數(shù)據(jù)，就不需要通過(guò)索引再去讀表，省了很多I/Oo比如這樣一個(gè)表

vatchartJSSdp咨升若詛&號(hào)vatchartJSSdp咨升若詛&號(hào)St拿棒稱(chēng)職成唯爵踞甲人底蒿祖甲昭詼袒皆■?E瓦拿）谷主以置月一密時(shí)蚯試檢虬苴起*迥可iM，「海呼 IMhJ?號(hào) lut和姓閘昭 加cirm瞻一?A■段蟋號(hào)ini的此撮tigint仆涼氐僧一A.易將識(shí)丹inL3^1*1獄盟）int如果我要統(tǒng)計(jì)每個(gè)學(xué)生每道題的得分情況，我們除了要給每個(gè)表的主鍵外鍵建立索引，還要對(duì)【得分情況】的實(shí)際得分字段索引，這樣，整個(gè)查詢就可以從索引得到數(shù)據(jù)了。三、數(shù)據(jù)庫(kù)參數(shù)配置最重要的參數(shù)就是兇存，我們主要用的innodb引擎，所以下面兩個(gè)參數(shù)調(diào)的很大AdditionalmemorypoolthatisusedbyInnoDBtostoremetadatainformation.IfInnoDBrequiresmorememoryforthispurposeitwillstarttoallocateitfromtheOS.Asthisisfastenoughonmostrecentoperatingsystems,younormallydonotneedtochangethis#value.SHOWINNODBSTATUSwilldisplaythecurrentamountused.innodb_additional_mem_pool_size=64MInnoDB,unlikeMyISAM,usesabufferpooltocachebothindexesandrowdata.ThebiggeryousetthisthelessdiskI/Oisneededtoaccessdataintables.Onadedicateddatabaseserveryoumaysetthisparameterupto80%ofthemachinephysicalmemorysize.Donotsetittoolarge,though,becausecompetitionofthephysicalmemorymaycausepagingintheoperatingsystem.Notethaton32bitsystemsyoumightbelimitedto2-3.5Gofuserlevelmemoryperprocess,sodonotsetittoohigh.innodb_buffer_pool_size=5G對(duì)于myisam，需要調(diào)整key_buffer_size當(dāng)然調(diào)整參數(shù)還是要看狀態(tài)，用showstatus語(yǔ)句可以看到當(dāng)前狀態(tài)，以決定改調(diào)整哪些參數(shù)Cretated_tmp