2019獨角獸企業重金招聘Python工程師標準>>>

對于一個以數據為中心的應用，數據庫的好壞直接影響到程序的性能，因此數據庫性能至關重要。一般來說，要保證數據庫的效率，要做好以下四個方面的工作：數 據庫設計、sql語句優化、數據庫參數配置、恰當的硬件資源和操作系統，這個順序也表現了這四個工作對性能影響的大小。下面我們逐個闡明：
??????
?????? 一、數據庫設計

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

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

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

　　下面這個冗余就很好　　

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

??? 適當建立索引

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

　　索引的字段必須是經常作為查詢條件的字段;

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

　　索引的字段必須有足夠的區分度;

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

　　對表進行水平劃分

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

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

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

??? 　對表進行垂直劃分

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

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

?????? 選擇適當的字段類型，特別是主鍵

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

　　文件、圖片等大文件用文件系統存儲，不用數據庫

　　不用多說，鐵律!!!數據庫只存儲路徑。

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

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

　　掌握表的寫入時機

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

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

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

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

　　這個變態的積分規則用批處理讀出來是這樣的：　　

? ? ??

1selectperson_id, @semester_id, 301003, 0, @one_marks, assign_date, @one_marks 2?????????fromhom_assignmentinfo?? ha, hom_assign_class hac 3?????????whereha.assignment_id = hac.assignment_id 4??????????????andha.assign_datebetween@time_beginand@time_end 5??????????????andha.assignment_idnotin 6??????????????????? ( 7????????????????????????selecthaa.assignment_idfromhom_assignment_appraise haa, hom_check_assignment hca 8?????????????????????????wherehaa.appraise_id = hca.appraise_idandhaa.if_submit=1 9??????????????????????????????and( 10????????????????????????????????????? (hca.recheck_state = 3004001andhca.check_resultin(3003002, 3003003) ) 11??????????????????????????????????????or 12????????????????????????????????????? (hca.recheck_state = 3004002andhca.recheck_resultin(3003002, 3003003)) 13??????????????????????????????????? ) 14??????????????????? ) 15??????????????andha.assignment_idnotin 16??????????????????? ( 17????????????????????????selectassignment_idfromhom_assignment_appraisewhereif_submit=0andresult_type = 0 18??????????????????? ) 19??????????????andha.assignment_idin????? 20??????????????????? ( 21????????????????????????selecthaa.assignment_idfromhom_assignment_appraise haa, hom_check_assignment hca 22?????????????????????????wherehaa.appraise_id = hca.appraise_idandhaa.if_submit=1 23??????????????????????????????andhca.check_resultin(3003002, 3003003) 24??????????????????? );

?

　　這還只是個中間過程，這要是用程序實時處理，即使編程人員不罷工，數據庫也會歇了。

　　選擇合適的引擎

　Mysql提供了很多種引擎，我們用的最多的是myisam，innodb，memory這三類。官方手冊上說道myisqm比innodb的讀速度要 快，大概是3倍。不過書不能盡信啊，《OreIlly.High.Performance.Mysql》這本書里提到了myisam和innodb的比 較，在測試中myisam的表現還不及innodb。至于memory，哈哈，還是比較好用的。在批處理種作臨時表是個不錯的選擇(如果內存夠大)。在我的一個批處理中，速度比近乎1：10。

?

??? 二、SQL語句優化

　Sql語句優化工具

　　·慢日志

　　如果發現系統慢了，又說不清楚是哪里慢，那么就該用這個工具了。只需要為mysql配置參數，mysql會自己記錄下來慢的sql語句。配置很簡單，參數文件里配置：

　　slow_query_log=d:/slow.txt

　　long_query_time = 2

　　就可以在d:/slow.txt里找到執行時間超過2秒的語句了，根據這個文件定位問題吧。

　　·mysqldumpslow.pl

慢日志文件可能會很大，讓人去看是很難受的事。這時候我們可以通過mysql自帶的工具來分析。這個工具可以格式化慢日志文件，對于只是參數不同的語句 會歸類類并，比如有兩個語句select * from a where id=1 和select * from a where id=2，經過這個工具整理后就只剩下select * from a where id=N，這樣讀起來就舒服多了。而且這個工具可以實現簡單的排序，讓我們有的放矢。

???? 　Explain

　　現在我們已經知道是哪個語句慢了，那么它為什么慢呢?看看mysql是怎么執行的吧，用explain可以看到mysql執行計劃，下面的用法來源于手冊

　　EXPLAIN語法(獲取SELECT相關信息)

　　EXPLAIN [EXTENDED] SELECT select_options

　　EXPLAIN語句可以用作DESCRIBE的一個同義詞，或獲得關于MySQL如何執行SELECT語句的信息：

　　· EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一個同義詞。

　　· 如果在SELECT語句前放上關鍵詞EXPLAIN，MySQL將解釋它如何處理SELECT，提供有關表如何聯接和聯接的次序。

　　該節解釋EXPLAIN的第2個用法。

　　借助于EXPLAIN，可以知道什么時候必須為表加入索引以得到一個使用索引來尋找記錄的更快的SELECT。

　　如果由于使用不正確的索引出現了問題，應運行ANALYZE TABLE更新表的統計(例如關鍵字集的勢)，這樣會影響優化器進行的選擇。

　　還可以知道優化器是否以一個最佳次序聯接表。為了強制優化器讓一個SELECT語句按照表命名順序的聯接次序，語句應以STRAIGHT_JOIN而不只是SELECT開頭。

　EXPLAIN為用于SELECT語句中的每個表返回一行信息。表以它們在處理查詢過程中將被MySQL讀入的順序被列出。MySQL用一遍掃描多次聯 接(single-sweep multi-join)的方式解決所有聯接。這意味著MySQL從第一個表中讀一行，然后找到在第二個表中的一個匹配行，然后在第3個表中等等。當所有的 表處理完后，它輸出選中的列并且返回表清單直到找到一個有更多的匹配行的表。從該表讀入下一行并繼續處理下一個表。

　　當使用EXTENDED關鍵字時，EXPLAIN產生附加信息，可以用SHOW WARNINGS瀏覽。該信息顯示優化器限定SELECT語句中的表和列名，重寫并且執行優化規則后SELECT語句是什么樣子，并且還可能包括優化過程的其它注解。

　?? 如果什么都做不了，試試全索引掃描

　　如果一個語句實在不能優化了，那么還有一個方法可以試試：索引覆蓋。

　　如果一個語句可以從索引上獲取全部數據，就不需要通過索引再去讀表，省了很多I/O。比如這樣一個表????????

　　如果我要統計每個學生每道題的得分情況，我們除了要給每個表的主鍵外鍵建立索引，還要對【得分情況】的實際得分字段索引，這樣，整個查詢就可以從索引得到數據了。

?

　三、數據庫參數配置

????? 最重要的參數就是內存，我們主要用的innodb引擎，所以下面兩個參數調的很大

　　# Additional memory pool that is used by InnoDB to store metadata

　　# information. If InnoDB requires more memory for this purpose it will

　　# start to allocate it from the OS. As this is fast enough on most

　　# recent operating systems, you normally do not need to change this

　　# value. SHOW INNODB STATUS will display the current amount used.

　　innodb_additional_mem_pool_size = 64M

　　# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and

　　# row data. The bigger you set this the less disk I/O is needed to

　　# access data in tables. On a dedicated database server you may set this

　　# parameter up to 80% of the machine physical memory size. Do not set it

　　# too large, though, because competition of the physical memory may

　　# cause paging in the operating system. Note that on 32bit systems you

　　# might be limited to 2-3.5G of user level memory per process, so do not

　　# set it too high.

　　innodb_buffer_pool_size = 5G

　　對于myisam，需要調整key_buffer_size

　　當然調整參數還是要看狀態，用show status語句可以看到當前狀態，以決定改調整哪些參數

　　Cretated_tmp_disk_tables 增加tmp_table_size

　　Handler_read_key 高表示索引正確 Handler_read_rnd高表示索引不正確

　　Key_reads/Key_read_requests 應小于0.01 計算緩存損失率，增加Key_buffer_size

　　Opentables/Open_tables 增加table_cache

　　select_full_join 沒有實用索引的鏈接的數量。如果不為0，應該檢查索引。

　　select_range_check 如果不為0，該檢查表索引。

　　sort_merge_passes 排序算法已經執行的合并的數量。如果該值較大，應增加sort_buffer_size

　　table_locks_waited 不能立即獲得的表的鎖的次數，如果該值較高，應優化查詢

　　Threads_created 創建用來處理連接的線程數。如果Threads_created較大，要增加 thread_cache_size值。

　　緩存訪問率的計算方法Threads_created/Connections。

　

????? 四、合理的硬件資源和操作系統

　　如果你的機器內存超過4G，那么毋庸置疑應當采用64位操作系統和64位mysql

　　讀寫分離

　　如果數據庫壓力很大，一臺機器支撐不了，那么可以用mysql復制實現多臺機器同步，將數據庫的壓力分散。　　

　　Master

　　Slave1

　　Slave2

　　Slave3

　　 主庫master用來寫入，slave1—slave3都用來做select，每個數據庫分擔的壓力小了很多。

　要實現這種方式，需要程序特別設計，寫都操作master，讀都操作slave，給程序開發帶來了額外負擔。當然目前已經有中間件來實現這個代理，對程 序來讀寫哪些數據庫是透明的。官方有個mysql-proxy，但是還是alpha版本的。新浪有個amobe for mysql，也可達到這個目的，結構如下　　

　　使用方法可以看amobe的手冊。

轉載于:https://my.oschina.net/xiaomaoandhong/blog/192533

總結

以上是生活随笔為你收集整理的[转]MySQL数据库优化总结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。