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

對于一個以數(shù)據(jù)為中心的應用，數(shù)據(jù)庫的好壞直接影響到程序的性能，因此數(shù)據(jù)庫性能至關重要。一般來說，要保證數(shù)據(jù)庫的效率，要做好以下四個方面的工作：數(shù)據(jù)庫設計、sql語句優(yōu)化、數(shù)據(jù)庫參數(shù)配置、恰當?shù)挠布Y源和操作系統(tǒng)，這個順序也表現(xiàn)了這四個工作對性能影響的大小。下面我們逐個闡明：
???????
?????? 一、數(shù)據(jù)庫設計

　　適度的反范式,注意是適度的

　　我們都知道三范式，基于三范式建立的模型是最有效保存數(shù) 據(jù)的方式，也是最容易擴展的模式。我們在開發(fā)應用程序時，設計的數(shù)據(jù)庫要最大程度的遵守三范式，特別是對于OLTP型的系統(tǒng)，三范式是必須遵守的規(guī)則。當 然，三范式最大的問題在于查詢時通常需要join很多表，導致查詢效率很低。所以有時候基于性能考慮，我們需要有意的違反三范式，適度的做冗余，以達到提 高查詢效率的目的。注意這里的反范式是適度的，必須為這種做法提供充分的理由。下面就是一個糟糕的實例：　　

　 　在這里，為了提高學生活動記錄的檢索效率，把單位名稱冗余到學生活動記錄表里。單位信息有500條記錄，而學生活動記錄在一年內(nèi)大概有200萬數(shù)據(jù)量。 如果學生活動記錄表不冗余這個單位名稱字段，只包含三個int字段和一個timestamp字段，只占用了16字節(jié)，是一個很小的表。而冗余了一個 varchar(32)的字段后則是原來的3倍，檢索起來相應也多了這么多的I/O。而且記錄數(shù)相差懸殊，500 VS 2000000 ，導致更新一個單位名稱還要更新4000條冗余記錄。由此可見，這個冗余根本就是適得其反。

　　下面這個冗余就很好　　

　 　可以看到，[學生考試總分]是冗余的，這個分數(shù)完全可以通過[得分情況]匯總得到。在【學生考試總分】里，一次考試一個學生只有一條記錄，而在【得分情 況】里，一個學生針對試卷里一個小題的一個小問一條記錄，粗略的算一下比例大概是1：100。而且判卷子得分是不會輕易變的，更新的頻率不高，所以說這個 冗余是比較好的。

????適當建立索引

　　說起提高數(shù)據(jù)庫性能，索引是最物美價廉的東西了。不用加內(nèi)存，不用改程序，不用調(diào)sql，只要執(zhí)行個正確的’create index’，查詢速度就可能提高百倍千倍，這可真有誘惑力。可是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，這些寫操作，增加了大量的I/O。由于索引的存儲結構不同于表的存儲，一個表的索引所占空間比數(shù)據(jù)所占空間還大的情況經(jīng)常發(fā)生。這意味著我們在寫數(shù)據(jù)庫的時候做了很多額外的工作，而這個工作只是為了提高讀的效率。因此，我們建立一個索引，必須保證這個索引不會“虧本”。一般需要遵守這樣的規(guī)則：

　　索引的字段必須是經(jīng)常作為查詢條件的字段;

　　如果索引多個字段，第一個字段要是經(jīng)常作為查詢條件的。如果只有第二個字段作為查詢條件，這個索引不會起到作用;

　　索引的字段必須有足夠的區(qū)分度;

　　Mysql 對于長字段支持前綴索引;

　　對表進行水平劃分

　 　如果一個表的記錄數(shù)太多了，比如上千萬條，而且需要經(jīng)常檢索，那么我們就有必要化整為零了。如果我拆成100個表，那么每個表只有10萬條記錄。當然這 需要數(shù)據(jù)在邏輯上可以劃分。一個好的劃分依據(jù)，有利于程序的簡單實現(xiàn)，也可以充分利用水平分表的優(yōu)勢。比如系統(tǒng)界面上只提供按月查詢的功能，那么把表按月 拆分成12個，每個查詢只查詢一個表就夠了。如果非要按照地域來分，即使把表拆的再小，查詢還是要聯(lián)合所有表來查，還不如不拆了。所以一個好的拆分依據(jù)是 最重要的。

　　這里有個比較好的實例????????

　 　每個學生做過的題都記錄在這個表里，包括對題和錯題。每個題會對應一個或多個知識點，我們需要根據(jù)錯題來分析學生在哪個知識點上掌握的不足。這個表很容 易達到千萬級，迫切需要拆分，那么根據(jù)什么來拆呢?從需求上看，無論是老師還是學生，最終會把焦點落在一個學生的身上。學生會關心自己，老師會關心自己班 的學生。而且每個學科的知識點是不同的。所以我們很容易想到，聯(lián)合學科和知識點兩個字段來拆分這個表。這樣拆下來，每個表大概2萬條數(shù)據(jù)，檢索效率非常 高。

????　對表進行垂直劃分

　　有些表記錄數(shù)并不多，可能也就2、3萬條，但是字段卻很長，表占用空間很大，檢索表時需要執(zhí)行大量I/O，嚴重降低了性能。這個時候需要把大的字段拆分到另一個表，并且該表與原表是一對一的關系。????????

　 　【試題內(nèi)容】、【答案信息】兩個表，最初是作為幾個字段添加到【試題信息】里的，可以看到試題內(nèi)容和答案這兩個字段很長，在表里有3萬記錄時，表已經(jīng)占 了1G的空間，在列試題列表時非常慢。經(jīng)過分析，發(fā)現(xiàn)系統(tǒng)很多時候是根據(jù)【冊】、【單元】、類型、類別、難易程度等查詢條件，分頁顯示試題詳細內(nèi)容。而每 次檢索都是這幾個表做join，每次要掃描一遍1G的表，很郁悶啊。我們完全可以把內(nèi)容和答案拆分成另一個表，只有顯示詳細內(nèi)容的時候才讀這個大表，由此 就產(chǎn)生了【試題內(nèi)容】、【答案信息】兩個表。

?????? 選擇適當?shù)淖侄晤愋?，特別是主鍵

　　選擇字段的一般原則是保小不保大，能用占用字節(jié)小的字段就不用大字段。比如主鍵， 我們強烈建議用自增類型，不用guid，為什么?省空間啊?空間是什么?空間就是效率!按4個字節(jié)和按32個字節(jié)定位一條記錄，誰快誰慢太明顯了。涉及到 幾個表做join時，效果就更明顯了。值得一提的是，datetime和timestamp，datetime占用8個字節(jié)，而timestamp占用4 個字節(jié)，只用了一半，而timestamp表示的范圍是1970—2037，對于大多數(shù)應用，尤其是記錄什么考試時間，登錄時間這類信息，綽綽有余啊。

　　文件、圖片等大文件用文件系統(tǒng)存儲，不用數(shù)據(jù)庫

　　不用多說，鐵律!!!數(shù)據(jù)庫只存儲路徑。

　　外鍵表示清楚，方便建立索引

　　我們都知道，在powerdesigner里為兩個實體建立關系，生成物理模型時會自動給外鍵建立索引。所以我們不要怕建立關系把線拉亂，建立個ShortCut就好了。

　　掌握表的寫入時機

　　在庫模式相同的情況下，如何使用數(shù)據(jù)庫也對性能有著重要作用。同樣是寫入一個表，先寫和后寫對后續(xù)的操作會產(chǎn)生很大影響。例如在上面提到的適度冗余里的例子，????????

　 　我們最初的目的是記錄考生的總分，以達到提高檢索效率的目的，也就是在錄入成績時寫入這個表。在需求里有這樣的要求：列出本次考試的所有學生成績，沒有 錄入成績的也顯示該學生名稱，只是總分顯示為空。這個查詢就需要用【學生信息】left outer join 【學生考試總分信息】，大家都知道outer join 的效率比join是要低的，為了避免這個問題，我們就在布置考試的時候?qū)懭脒@個表，把所有學生都插入進去，分數(shù)都是null，這樣一來我們就可以用 join達到這個效果了。而且還有這樣的好處：在某次考試中，安排了一個班所有學生考試，所有學生都錄入了成績?，F(xiàn)在班里轉(zhuǎn)來一個新生，那么在此時如果查 詢學生成績，就會列出這個新生，結果是未錄入成績，這顯然是不對的。如果在安排的時候就寫入，就可以記錄下該次考試中實際的考生了，這個表的作用，也就不 知是冗余了。

　?? 寧可集中批量操作，避免頻繁讀寫

　　系統(tǒng)里包含了積分部分，學生和老師通過系統(tǒng)做了操作都可以獲得積分，而且積分規(guī) 則很復雜，限制每類操作獲得積分不同，每人每天每類積分都有上限。比如登錄，一次登錄就可以獲得1分，但是不管你登錄多少次，一天只能累積一個登錄積分。 這個還是簡單的，有的積分很變態(tài)，比如老師積分中有一類是看老師判作業(yè)的情況，規(guī)則是：老師判了作業(yè)，發(fā)現(xiàn)學生有錯的，學生改過了，老師再判，如果這時候 學生都對了，就給老師加分，如果學生還是錯的，那就接著改，知道學生都改對了，老師都判完了，才能給老師加分。如果用程序來處理，很可能每個功能都會額外 的寫一堆代碼來處理這個雞肋似的積分。不僅編程的同事干活找不到重點，還平白給數(shù)據(jù)庫帶來了很大的壓力。經(jīng)過和需求人員的討論，確定積分沒有必要實時累 積，于是我們采取后臺腳本批量處理的方式。夜深人靜的時候，讓機器自己玩去吧。

　　這個變態(tài)的積分規(guī)則用批處理讀出來是這樣的：　　

? ? ??1select?person_id, @semester_id, 301003, 0, @one_marks, assign_date, @one_marks2?????????from?hom_assignmentinfo?? ha, hom_assign_class hac3?????????where?ha.assignment_id = hac.assignment_id4??????????????and?ha.assign_datebetween?@time_beginand?@time_end5??????????????and?ha.assignment_idnot?in6??????????????????? (7????????????????????????select?haa.assignment_idfrom?hom_assignment_appraise haa, hom_check_assignment hca8?????????????????????????where?haa.appraise_id = hca.appraise_id and?haa.if_submit=19??????????????????????????????and?(10????????????????????????????????????? (hca.recheck_state = 3004001 and?hca.check_resultin?(3003002, 3003003) )11??????????????????????????????????????or12????????????????????????????????????? (hca.recheck_state = 3004002 and?hca.recheck_resultin?(3003002, 3003003))13??????????????????????????????????? )14??????????????????? )15??????????????and?ha.assignment_idnot?in16??????????????????? (17????????????????????????select?assignment_idfrom?hom_assignment_appraisewhere?if_submit=0and?result_type = 018??????????????????? )19??????????????and?ha.assignment_idin?????20??????????????????? (21????????????????????????select?haa.assignment_idfrom?hom_assignment_appraise haa, hom_check_assignment hca22?????????????????????????where?haa.appraise_id = hca.appraise_id and?<code class="sql plain" style="border:0px !important;line-height:1.8em !important;overflow:visible !important;vertical-align:baseline !important;font-family:Consolas, 'Bitstream Vera Sa