杜源

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

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

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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擁有獨(dú)特的存儲(chǔ)引擎架構(gòu)，其系統(tǒng)核心提供的多線程機(jī)制提供完全的多線程運(yùn)行模式，同時(shí)輕巧快速，系統(tǒng)資源消耗小，支持高并發(fā)連接，提供了面向C、C++、JAVA、PHP、以及Python等多種編程語(yǔ)言的編程接口，很強(qiáng)的平臺(tái)的靈活性，完美支持現(xiàn)有的所有平臺(tái)，作為一個(gè)開(kāi)源的數(shù)據(jù)庫(kù)，它毋庸置疑是開(kāi)源數(shù)據(jù)庫(kù)中的佼佼者，現(xiàn)已國(guó)內(nèi)很多大中小企業(yè)所用，但是萬(wàn)物皆有瓶頸，MySQL也不例外。針對(duì)我們所用的數(shù)據(jù)庫(kù)出現(xiàn)了瓶頸，那我們?cè)摬扇∈裁创胧@些在各大使用MySQL的企業(yè)顯得異常重要了。

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

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

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

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

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

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

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

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

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

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

3 MySQL優(yōu)化措施

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

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

1）使用join時(shí)，應(yīng)該用小的結(jié)果集驅(qū)動(dòng)大結(jié)果集。復(fù)雜的情況下，應(yīng)把join查詢拆分多個(gè)query。因?yàn)閖oin多個(gè)表是，可能導(dǎo)致更多的鎖定和堵塞。

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

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

8）不要使用 COUNT（id） ，應(yīng)該是 COUNT（*）

3.2 關(guān)于索引基本遵循的原則

1）關(guān)于索引，并不是越多越好，因?yàn)榫S護(hù)索引需要成本，有時(shí)索引大小已經(jīng)大于數(shù)據(jù)量大小，那這時(shí)你就應(yīng)該堅(jiān)持?jǐn)?shù)據(jù)庫(kù)架構(gòu)是否合理了。

2）差的索引和沒(méi)有索引效果一樣。

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

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

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

3.3 關(guān)于MySQL存儲(chǔ)引擎的選擇

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

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

3.3.1 存儲(chǔ)引擎的選擇

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

3.3.2存儲(chǔ)引擎的選擇遵循基本原則

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

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

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

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

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

2）適度使用Query Cache。

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

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

對(duì)于MyISAM，還需要注意table_cache的設(shè)置，當(dāng)table_cache 不夠用的時(shí)候，mysql會(huì)采用LRU算法踢掉最長(zhǎng)時(shí)間沒(méi)有使用的表；如果table_cache設(shè)置過(guò)小，mysql會(huì)反復(fù)打開(kāi)。關(guān)閉FRM文件，早晨一定的性能損失；如果 table_cache設(shè)置過(guò)大，mysql將會(huì)消耗很多cpu資源去處理table_cache的算法。因此table_cache值一定要設(shè)置合理，可以參考o(jì)pened_tables參數(shù)的值，如果這個(gè)值一直增加，就需要適當(dāng)增加table_cache的值。對(duì)于InnoDB，需要重點(diǎn)注意innodb_buffer_pool_size參數(shù)。

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

4 結(jié)束語(yǔ)

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

參考文獻(xiàn)：

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

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

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

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

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

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