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

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

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

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

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

vatchartJSSdp咨升若詛&號vatchartJSSdp咨升若詛&號St拿棒稱職成唯爵踞甲人底蒿祖甲昭詼袒皆■?E瓦拿）谷主以置月一密時蚯試檢虬苴起*迥可iM，「海呼 IMhJ?號 lut和姓閘昭 加cirm瞻一?A■段蟋號ini的此撮tigint仆涼氐僧一A.易將識丹inL3^1*1獄盟）int如果我要統(tǒng)計(jì)每個學(xué)生每道題的得分情況，我們除了要給每個表的主鍵外鍵建立索引，還要對【得分情況】的實(shí)際得分字段索引，這樣，整個查詢就可以從索引得到數(shù)據(jù)了。三、數(shù)據(jù)庫參數(shù)配置最重要的參數(shù)就是兇存，我們主要用的innodb引擎，所以下面兩個參數(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對于myisam，需要調(diào)整key_buffer_size當(dāng)然調(diào)整參數(shù)還是要看狀態(tài)，用showstatus語句可以看到當(dāng)前狀態(tài)，以決定改調(diào)整哪些參數(shù)Cretated_tmp