MySQL數(shù)據(jù)庫(kù)分區(qū)管理 細(xì)節(jié)決定成敗
在MySQL數(shù)據(jù)庫(kù)中，表的不同部分在不同的位置被存儲(chǔ)為單獨(dú)的表。分區(qū)主要就是用來(lái)解決表在不同的位置存儲(chǔ)的問(wèn)題。在其他數(shù)據(jù)庫(kù)中，也會(huì)存在這種情況。他們將這種類型的數(shù)據(jù)表稱之為分區(qū)表。分區(qū)的管理，對(duì)于MySQL數(shù)據(jù)庫(kù)來(lái)說(shuō)至關(guān)重要。其直接跟數(shù)據(jù)庫(kù)的性能與安全性息息相關(guān)。對(duì)于分區(qū)的管理，筆者只有兩個(gè)字：細(xì)節(jié)。
細(xì)節(jié)一：確定所使用的版本是否支持分區(qū)
在MySQL中，并不是所有的數(shù)據(jù)庫(kù)版本都支持分區(qū)管理。為此數(shù)據(jù)庫(kù)管理員首先要做的就是，確認(rèn)自己所采用的版本是否支持這個(gè)功能。如果支持的話，則可以在后續(xù)設(shè)計(jì)與維護(hù)時(shí)利用分區(qū)的特性來(lái)提升系統(tǒng)性能、提高數(shù)據(jù)安全。反之，則不行。要判斷所使用的版本是否支持分區(qū)功能，可以通過(guò)系統(tǒng)命令來(lái)實(shí)現(xiàn)。如下圖所示：
如果上面這個(gè)變量，其顯示的值是YES，那么很慶幸的告訴你，你說(shuō)是用的版本支持分區(qū)管理。如果這個(gè)變量的值是空白的，則表示所使用的版本不支持分區(qū)管理。筆者建議對(duì)現(xiàn)有的數(shù)據(jù)庫(kù)版本進(jìn)行升級(jí)。
對(duì)于分區(qū)管理，筆者需要強(qiáng)調(diào)一點(diǎn)，即MySQL數(shù)據(jù)庫(kù)與其他數(shù)據(jù)庫(kù)在分區(qū)實(shí)現(xiàn)上的差異。對(duì)于MySQL數(shù)據(jù)庫(kù)來(lái)說(shuō)，主要是通過(guò)分區(qū)函數(shù)來(lái)實(shí)現(xiàn)分區(qū)的控制。如這可能是一個(gè)HASH函數(shù)、或者一個(gè)數(shù)值列表。雖然數(shù)據(jù)庫(kù)管理員并不需要非常了解其內(nèi)部的實(shí)現(xiàn)機(jī)制。但是需要明確的是，采用不同的分區(qū)類型，就選擇了不同的分區(qū)函數(shù)。反過(guò)來(lái)說(shuō)，分區(qū)函數(shù)的特征就確定了所選擇分區(qū)的特性。從這個(gè)角度來(lái)講，掌握函數(shù)的一些基本特征，可以幫助我們更好的理解不同類型分區(qū)之間的差異。從而有利于數(shù)據(jù)庫(kù)管理員根據(jù)企業(yè)的實(shí)際情況來(lái)設(shè)計(jì)合理的分區(qū)。
細(xì)節(jié)二：存儲(chǔ)引擎與分區(qū)之間的關(guān)系
存儲(chǔ)引擎主要用來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)的存儲(chǔ)。顯然這個(gè)存儲(chǔ)引擎與分區(qū)之間有著緊密的聯(lián)系。這個(gè)聯(lián)系主要體現(xiàn)在如下這個(gè)方面。
通常情況下，對(duì)于創(chuàng)建了分區(qū)的數(shù)據(jù)表，數(shù)據(jù)庫(kù)管理員可以使用數(shù)據(jù)庫(kù)服務(wù)器所支持的任何存儲(chǔ)引擎。也就是說(shuō)，對(duì)于數(shù)據(jù)引擎來(lái)說(shuō)，分區(qū)是透明的。這主要是因?yàn)樵贛ySQL數(shù)據(jù)庫(kù)中，分區(qū)引擎是在一個(gè)單獨(dú)的層中運(yùn)行的。并且可以和任何這樣的層進(jìn)行相互溝通。不過(guò)如果再深挖下去的話，這里仍然有一個(gè)細(xì)節(jié)問(wèn)題需要考慮，即需要注意一個(gè)限制的規(guī)則。對(duì)于同一個(gè)分區(qū)表的所有分區(qū)都必須使用同一個(gè)存儲(chǔ)引擎。舉一個(gè)簡(jiǎn)單的例子?，F(xiàn)在有一個(gè)數(shù)據(jù)表，其有兩個(gè)數(shù)據(jù)分區(qū)，分別為A與B。此時(shí)如果數(shù)據(jù)庫(kù)管理員對(duì)于分區(qū)A采用了MYISAM;那么對(duì)于分區(qū)B也只能夠使用MYISAM，而不能夠使用其它的，如INNODB。
這段話看起來(lái)好像有點(diǎn)互相矛盾的地方。其實(shí)我們可以將其總結(jié)為一句話。即不同的分區(qū)表，可以采用任何數(shù)據(jù)庫(kù)所支持的數(shù)據(jù)引擎。但是對(duì)于同一個(gè)數(shù)據(jù)表的不同分區(qū)，則只能夠使用同一個(gè)存儲(chǔ)引擎。
最后需要說(shuō)明的是，從存儲(chǔ)引擎的相關(guān)信息中，并不能夠看出其服務(wù)的數(shù)據(jù)表是否支持分區(qū)的功能。也就是說(shuō)，不能夠通過(guò)命令show engines來(lái)判斷數(shù)據(jù)庫(kù)是否支持分區(qū)。數(shù)據(jù)庫(kù)管理員只能夠通過(guò)上面的第一個(gè)命令來(lái)判斷數(shù)據(jù)庫(kù)分區(qū)的相關(guān)信息。
細(xì)節(jié)三：分區(qū)是一個(gè)整體，不能夠進(jìn)行分割
一個(gè)數(shù)據(jù)表可以根據(jù)實(shí)際情況分為多個(gè)分區(qū)。但是分割后的分區(qū)仍然是一個(gè)整體。這是什么意思呢?筆者舉一個(gè)例子，各位就可以理解了?，F(xiàn)在有一個(gè)數(shù)據(jù)表，表中有記錄和索引。在進(jìn)行分區(qū)設(shè)計(jì)時(shí)，不能夠只對(duì)數(shù)據(jù)分區(qū)而不對(duì)索引分區(qū)，也不能夠?qū)λ饕謪^(qū)而不對(duì)數(shù)據(jù)分區(qū)。這就好像是分蛋糕一樣。蛋糕會(huì)有上下兩層。在分蛋糕時(shí)，是上下兩層一起分。而不會(huì)只分上面一層奶油。為此需要切記，分區(qū)時(shí)是對(duì)數(shù)據(jù)表中的所有內(nèi)容進(jìn)行同時(shí)分區(qū)，而不能夠?qū)Σ糠诌M(jìn)行分區(qū)。
另外需要注意的是，如果要對(duì)某個(gè)表進(jìn)行分區(qū)，那么就需要對(duì)整個(gè)表進(jìn)行分區(qū)管理。而不能夠?qū)Σ糠诌M(jìn)行分區(qū)。如對(duì)某個(gè)表的上半部分不執(zhí)行分區(qū)，而只對(duì)下半部分進(jìn)行分區(qū)管理，這是不允許的。
細(xì)節(jié)四：分區(qū)如何提高查詢效率
采用分區(qū)管理，可以很好的提高查詢的效率。筆者這里舉一個(gè)零售企業(yè)的案例。如現(xiàn)在有一家超市，使用的是MySQL數(shù)據(jù)庫(kù)。一家超市，每年的銷售記錄會(huì)有幾千萬(wàn)條。幾年累積下來(lái)，數(shù)據(jù)量非常的大。現(xiàn)在如果用戶需要查詢，去年一年某個(gè)產(chǎn)品的銷售情況，那就像大海撈針一樣，速度會(huì)非常的慢。
此時(shí)如果采用分區(qū)管理的話，會(huì)明顯的提高查詢的效率。在數(shù)據(jù)庫(kù)設(shè)計(jì)的時(shí)候，可以根據(jù)時(shí)間來(lái)劃分分區(qū)。如為每一年的數(shù)據(jù)單獨(dú)設(shè)置一個(gè)分區(qū)。此時(shí)再查詢2010年某個(gè)產(chǎn)品的銷售情況時(shí)，由于指定了Where條件語(yǔ)句，則系統(tǒng)只會(huì)從2010年這個(gè)數(shù)據(jù)分區(qū)中去查找相關(guān)的內(nèi)容，而會(huì)忽略其他無(wú)關(guān)的分區(qū)，從而改善數(shù)據(jù)查詢效率。
在實(shí)際工作中，筆者還經(jīng)常將某個(gè)表分為多個(gè)分區(qū)，然后將不同的分區(qū)放置在不同的磁盤(pán)上。此時(shí)可以通過(guò)多個(gè)硬盤(pán)來(lái)分散數(shù)據(jù)查詢，來(lái)獲得更大的查詢吞吐量。如果企業(yè)數(shù)據(jù)庫(kù)服務(wù)器中，已經(jīng)使用了磁盤(pán)陣列5的話，采取這個(gè)措施就是多此一舉。如果服務(wù)器中只有一塊硬盤(pán)、或者雖然有多快硬盤(pán)但是沒(méi)有實(shí)現(xiàn)磁盤(pán)陣列的話，筆者將多個(gè)分區(qū)存放在多快硬盤(pán)上的做法，還是蠻值得推薦的。如還是以零售企業(yè)為例。如果企業(yè)一年的銷售記錄有上億條。此時(shí)要對(duì)這上億條的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析，對(duì)于硬盤(pán)的吞吐量是一個(gè)極大的考驗(yàn)。此時(shí)我們可以對(duì)這個(gè)數(shù)據(jù)表進(jìn)行分區(qū)。如可以根據(jù)季度將其分為四個(gè)區(qū)A、B、C、D。然后將AC兩個(gè)分區(qū)放在硬盤(pán)甲上，將剩余的BD兩個(gè)分區(qū)放在硬盤(pán)乙上。此時(shí)系統(tǒng)在讀取整年的數(shù)據(jù)時(shí)，會(huì)同時(shí)從兩塊硬盤(pán)上讀取數(shù)據(jù)。這么設(shè)計(jì)的話，硬盤(pán)的吞吐量就可以提高一倍(假設(shè)不考慮管理開(kāi)銷)。通過(guò)這種方式，也可以提高查詢的效率。其與磁盤(pán)陣列5有異曲同工之妙。只是其實(shí)現(xiàn)的級(jí)別不同。
可見(jiàn)采用分區(qū)之后，一些查詢能夠得到很大的優(yōu)化。這主要是因?yàn)橛脩艨梢越柚跐M足一個(gè)給定的Where語(yǔ)句的數(shù)據(jù)可以只保存在某個(gè)特定的分區(qū)內(nèi)(如2010年的交易數(shù)據(jù))。如此在查詢時(shí)就不用再查找其他剩余的分區(qū)。雖然說(shuō)分區(qū)可以在創(chuàng)建了分區(qū)表之后再進(jìn)行修改。即使剛開(kāi)始沒(méi)有考慮到這個(gè)內(nèi)容，也可以在以后有需要的時(shí)候重新組織數(shù)據(jù)，對(duì)數(shù)據(jù)表進(jìn)行分區(qū)。但是筆者并不贊成這么做。因?yàn)閷?duì)數(shù)據(jù)進(jìn)行重新組織，就好像是重新剪貼、復(fù)制了一遍數(shù)據(jù)。在記錄比較多時(shí)，這個(gè)作業(yè)會(huì)大量的消耗服務(wù)器的資源。為此筆者還是建議，在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，管理員就需要對(duì)未來(lái)的數(shù)據(jù)量能夠進(jìn)行預(yù)測(cè)。如果有必要采用分區(qū)管理的，那么要提早做。
當(dāng)然分區(qū)并不是對(duì)所有的企業(yè)都適用。如果企業(yè)的數(shù)據(jù)量比較少，又或者說(shuō)大部分是一次性使用的數(shù)據(jù)，此時(shí)采用分區(qū)的話，不會(huì)給企業(yè)帶來(lái)價(jià)值。反而會(huì)增加管理上的開(kāi)銷。
原文鏈接：http://tech.it168.com/a2011/0301/1161/000001161632.shtml
【編輯推薦】
