一種深入解決MySQL數(shù)據(jù)庫優(yōu)化方案

杜源

摘要：總結選擇MySQL數(shù)據(jù)庫的意義，論述了數(shù)據(jù)庫設計的基本規(guī)范和設計的時候需要遵循原則，大體的列出了MySQL優(yōu)化的一些具體操作和MySQL幾種常用的數(shù)據(jù)存儲引擎，以及對幾種數(shù)據(jù)存儲引擎的優(yōu)缺點概括，最后列出了MySQL服務器調(diào)整優(yōu)化一些措施。

關鍵詞：數(shù)據(jù)庫；設計規(guī)范； 存儲引擎；優(yōu)化

中圖分類號：TP311 文獻標識碼：A 文章編號：1009-3044（2015）07-0004-03

Abstract： Conclusion the significance of the selected of MySQL， discusses the basic specification when design the database and some design principles to follow， list specific operations to optimize the MySQL and several kinds of common data storage engines of MySQL， sums up the advantages and disadvantages of several kinds of data storage engine， at last lists the adjusting and optimizing measures of the MySQL server.

Key words： database ；design specification； storage engine； optimize

1 MySQL選擇意義

眾所周知，MySQL擁有獨特的存儲引擎架構，其系統(tǒng)核心提供的多線程機制提供完全的多線程運行模式，同時輕巧快速，系統(tǒng)資源消耗小，支持高并發(fā)連接，提供了面向C、C++、JAVA、PHP、以及Python等多種編程語言的編程接口，很強的平臺的靈活性，完美支持現(xiàn)有的所有平臺，作為一個開源的數(shù)據(jù)庫，它毋庸置疑是開源數(shù)據(jù)庫中的佼佼者，現(xiàn)已國內(nèi)很多大中小企業(yè)所用，但是萬物皆有瓶頸，MySQL也不例外。針對我們所用的數(shù)據(jù)庫出現(xiàn)了瓶頸，那我們該采取什么措施，這些在各大使用MySQL的企業(yè)顯得異常重要了。

2 數(shù)據(jù)庫設計規(guī)范

2.1 數(shù)據(jù)庫設計范式

為了規(guī)范數(shù)據(jù)庫的設計，在數(shù)據(jù)庫理論發(fā)展的過程中，逐漸形成了數(shù)據(jù)庫范式的理論。到目前為止，一般認為數(shù)據(jù)庫設計中有五大范式，這五大范式又是層次遞進的。

第一范式：對于表中的每一行，必須且僅僅有唯一的行值。在一行中的每一列僅有唯一的值并且具有原子性。第二范式：要求非主鍵列是主鍵的子集，非主鍵列活動必須完全依賴整個主鍵。主鍵必須有唯一性的元素，一個主鍵可以由一個或更多的組成唯一值的列組成。一旦創(chuàng)建，主鍵無法改變，外鍵關聯(lián)一個表的主鍵。主外鍵關聯(lián)意味著一對多的關系。第三范式：要求非主鍵列互不依賴。第四范式：禁止主鍵列和非主鍵列一對多關系不受約束。第五范式：將表分割成盡可能小的塊，為了排除在表中所有的冗余。

但是現(xiàn)在反范式的一些理論也越來越流行，為什么又要反范式呢。甚至第三范式都要反？數(shù)據(jù)庫范式理論在20世紀70年代提出的，在20世紀80年代基本定型，那個時候的系統(tǒng)存在如下特征：可用的存儲器資源極其有限，幾百兆字節(jié)大小的磁盤就算很大的了，而現(xiàn)在的硬盤動輒幾百GB甚至上TB；同時，那個時候的網(wǎng)絡還不成熟，能使用網(wǎng)絡的人很少，通常只是涉及單機的計算性能。因此，數(shù)據(jù)庫范式理論強調(diào)減少依賴、降低冗余是有其歷史背景的。而現(xiàn)在，硬盤容量比當時大了幾萬倍，硬盤廉價，數(shù)據(jù)存儲不再是問題；同時，面臨高并發(fā)，業(yè)務邏輯極度復雜，低延遲要求的情況，此時，還一味的遵循范式設計理論是不當?shù)?。適當?shù)亟档头妒?，增加冗余，用空間來換時間是值得的，最低可以把范式降低到第一范式。

2.2 設計數(shù)據(jù)庫的原則

1）核心業(yè)務使用范式。在類似交易有關的這種敏感和核心業(yè)務中，強調(diào)數(shù)據(jù)安全和一致性，需要遵循范式保證機密數(shù)據(jù)不被破壞，核心業(yè)務不出現(xiàn)不一致的情況。

2）弱一致性需求--反ACID。在一些對數(shù)據(jù)一致性要求不高的場合，不必完全遵循ACID，出現(xiàn)適當?shù)臄?shù)據(jù)不一致是可以容忍的，如在線人數(shù)統(tǒng)計，靜態(tài)頁等。最近幾年流行的Nosql技術，就是基于弱一致性，降低數(shù)據(jù)完整性和一致性換取效率。

3）空間換時間，冗余換效率。由于一條可見記錄被拆分到多個表進行記錄，當數(shù)據(jù)量比較大的時候，聯(lián)表查詢就變得比較費時，SQL語句也變得復雜，難于優(yōu)化，此時就需要適當?shù)娜哂嗔?，在統(tǒng)計報表，視圖中就是對這已規(guī)則的具體體現(xiàn)。統(tǒng)計表通常會有很多列，有的甚至多到上百列，需要關聯(lián)幾個甚至幾十個表進行查詢。如果每次查看統(tǒng)計信息都進行關聯(lián)查詢，速度緩慢不說，更嚴重的情況是使用的人一多，就可能導致數(shù)據(jù)庫服務器宕機。這種情況就需要冗余表了，冗余表一般符合第一或者第二范式。那冗余表怎么處理，一般是定期轉儲，很少有人回去實時查詢3年前某個月的銷售數(shù)據(jù)明細表。

4）避免不必要的冗余。范式理論是不是想反就能反的，反范式理論不是說不要范式，而是必要的時候創(chuàng)建冗余表或者總結表、不必要的冗余表仍然是要避免的。

3 MySQL優(yōu)化措施

數(shù)據(jù)庫的優(yōu)化主要包括兩個方面：SQL程序語句的優(yōu)化和數(shù)據(jù)庫服務器和配置的優(yōu)化。

3.1 數(shù)據(jù)庫優(yōu)化基本遵循的原則

1）使用join時，應該用小的結果集驅(qū)動大結果集。復雜的情況下，應把join查詢拆分多個query。因為join多個表是，可能導致更多的鎖定和堵塞。

6）limit的基數(shù)比較大時使用 between。between限定比limit快

7）不要使用rand（）函數(shù)獲取多條隨機記錄。

8）不要使用 COUNT（id） ，應該是 COUNT（*）

3.2 關于索引基本遵循的原則

1）關于索引，并不是越多越好，因為維護索引需要成本，有時索引大小已經(jīng)大于數(shù)據(jù)量大小，那這時你就應該堅持數(shù)據(jù)庫架構是否合理了。

2）差的索引和沒有索引效果一樣。

3）每個表的索引應在5個以下，應合理利用部分索引和聯(lián)合索引。

4）不要在結果集中的結果單一的列上建索引。比如字段只有0和1兩個結果集，在這上面建索引并沒有多大的作用。

5）建索引的字段結果集最好分布均勻，或者符合正態(tài)分布。

3.3 關于MySQL存儲引擎的選擇

MySQL中有多種存儲引擎，每種存儲引擎都有自己的特色，想要好的性能， 第一步就是選擇合適的數(shù)據(jù)庫引擎。My SQL中常見的三種引擎特點如表1。

通常我們認為MyISAM 注重性能，InnoDB注重事務，故一般使用MyISAM類的表做非事務型的業(yè)務。這種觀點產(chǎn)生于早期InnoDB引擎還不成熟的時候，而現(xiàn)在并不是這樣。MySQL在高并發(fā)下的性能瓶頸是很明顯的，主要原因是鎖定機制導致的堵塞。而InnoDB在鎖定機制上采用的行級鎖，不同于MyISAM的表級鎖，行級鎖在鎖定上帶來的消耗大于表級鎖，但是在系統(tǒng)并發(fā)訪問量不就高的時候，InnoDB整體性能遠高于MyISAM。同時，InnoDB的索引不僅緩存索引本身，也緩存數(shù)據(jù)，所以InnoDB則需要更大的內(nèi)存，不過現(xiàn)在，內(nèi)存是很廉價的了。選擇最合適的存儲引擎是優(yōu)化的第一步。

3.3.1 存儲引擎的選擇

選擇存儲引擎，首先我們得了解讀寫比（R/W） 的概念，通過 在數(shù)據(jù)庫中執(zhí)行 show global status 得到系統(tǒng)當前狀態(tài)。這些變量中，形容COM_XXX的語句表示XXX語句執(zhí)行的次數(shù)，如 COM_select 表示select語句的執(zhí)行次數(shù)，以此類推。通過計算讀類型和寫類型語句的比例。即可確定一個粗糙的讀寫比例。理想的讀寫比為100：1， 當讀寫比達到10：1的時候，就認為是已寫為主的數(shù)據(jù)庫了，一般這個值30：1左右。

3.3.2存儲引擎的選擇遵循基本原則

1）采用MyISAM引擎：R/W > 100：1且update相對較少；并發(fā)不高，不需要事務；表數(shù)據(jù)量小；硬件資源有限。

2）采用InnoDB引擎： R/W 比較小，頻繁更新大字段； 表數(shù)據(jù)超過100萬，并發(fā)高； 安全性和可用性要求高。

3）采用Memory引擎：有足夠的內(nèi)存；對數(shù)據(jù)一致性要求不高，如在線人數(shù)和session等應用； 需要定期歸檔的數(shù)據(jù)。

3.4 MySQL服務器調(diào)整優(yōu)化措施

1）關閉不必要的二進制和慢查詢?nèi)罩?，僅在內(nèi)存足夠或者開發(fā)調(diào)試時打開他們，還可以使用下面語句查詢是否打開：show variables like ‘%slow%；還可以使用下面的語句查看慢查詢的條數(shù)，定期打開方便優(yōu)化show global status like ‘%slow%；但是慢查詢也會帶來一些cpu的損耗，建議間斷性打開滿日志來定位性能瓶頸。

2）適度使用Query Cache。

3）增加Mysql允許的最大連接數(shù)?？捎孟旅娴恼Z句查看Mysql循序的最大連接數(shù)。show variables like 'max_connections'；

4）對于MyISAM 表適當?shù)脑黾觡ey_buffer_size。當然這需要根據(jù)Key_cache 的命中率進行計算，當key_buffer_size值大于1%時就需要適當增加key_buffer_size了。

對于MyISAM，還需要注意table_cache的設置，當table_cache 不夠用的時候，mysql會采用LRU算法踢掉最長時間沒有使用的表；如果table_cache設置過小，mysql會反復打開。關閉FRM文件，早晨一定的性能損失；如果 table_cache設置過大，mysql將會消耗很多cpu資源去處理table_cache的算法。因此table_cache值一定要設置合理，可以參考opened_tables參數(shù)的值，如果這個值一直增加，就需要適當增加table_cache的值。對于InnoDB，需要重點注意innodb_buffer_pool_size參數(shù)。

5）從表中刪除大量行后，可允許OPTMIZE TABLE TableName 進行碎片整理。

4 結束語

MySQL數(shù)據(jù)庫的優(yōu)化工作是一個長期的、復雜的、循環(huán)往復的過程。MySQL數(shù)據(jù)庫優(yōu)化有時在訪問量大的情況下下僅僅只通過服務器參數(shù)調(diào)整，SQL語句優(yōu)化也都不一定能解決實際的高并發(fā)的的需要的，還需要做數(shù)據(jù)庫的分區(qū)、分表，更甚者需要做數(shù)據(jù)庫的分布式架構，做數(shù)據(jù)庫集群，需要做數(shù)據(jù)庫讀寫分離，異步信息隊列來避免高并發(fā)情況下所造成數(shù)據(jù)庫擊穿等現(xiàn)象，所以我們做優(yōu)化的時候還需要根據(jù)實際的應用做相應的策略。

參考文獻：

[1] 唐漢明，翟振興，關寶軍，等.深入淺出MySQL（數(shù)據(jù)庫開發(fā)，優(yōu)化與管理維護[M].2版.北京：人民郵電出版社，2014.

[2] 王小東，李軍，康建勛.高性能MySQL[M].北京：電子工業(yè)出版社，2010.

[3] 寧青，唐李洋，諸云萍.高可用MySQL：構建健壯的數(shù)據(jù)中心[M].北京：電子工業(yè)出版社，2011.

[4] 姜承堯.MySQL技術內(nèi)幕： SQL編程[M].北京：機械工業(yè)出版社華章公司，2012.

[5] 李芳，于紅蕓，邵健.深入理解MySQL核心技術[M].北京：中國電力出版社，2009.

[6] 賀春旸.MYSQL管理之道：性能調(diào)優(yōu)，高可用與監(jiān)控[M].北京：機械工業(yè)出版社，2013.