通過(guò)分區(qū)（Partition）提升MySQL性能 - fanqiang.com

?

通過(guò)分區(qū)（Partition）提升MySQL性能
作者：foxcai 來(lái)源:foxcai 的 Blog (2006-05-08 14:30:34)
? 通過(guò)分區(qū)（Partition）提升MySQL性能 ?????????????? ——MySQL5.1新特性翻譯系列 幾年前，俺寫(xiě)過(guò)一篇題為“The Foundation of Excellent Performance”的文章(現(xiàn)在仍然可以在 http://www.tdan.com/i016fe03.htm 看到)，俺對(duì)SQL語(yǔ)句是影響數(shù)據(jù)庫(kù)驅(qū)動(dòng)系統(tǒng)性能的第一要素的觀點(diǎn)有點(diǎn)質(zhì)疑。其實(shí)在那時(shí)我在文章中就堅(jiān)信數(shù)據(jù)庫(kù)的物理設(shè)計(jì)在對(duì)高級(jí)數(shù)據(jù)庫(kù)的性能影響上遠(yuǎn)比其他因素重要。同時(shí)俺還給大家看了Oracle的研究，他們解釋了為什么拙劣的物理設(shè)計(jì)是數(shù)據(jù)庫(kù)停機(jī)（無(wú)論是有計(jì)劃的還是沒(méi)計(jì)劃的）背后的主要原因。這么多年都過(guò)來(lái)啦（幸好沒(méi)多少人朝俺扔磚頭），俺的觀點(diǎn)是改變了一些，但在這點(diǎn)上俺還是堅(jiān)持DBA如果想要高性能的數(shù)據(jù)庫(kù)就必須在數(shù)據(jù)庫(kù)的物理設(shè)計(jì)上多思考的觀點(diǎn)，這樣才能減少響應(yīng)時(shí)間使終端用戶滿意而不是引來(lái)罵聲一片。（陳朋奕語(yǔ)：不要那么嚴(yán)肅，嘿嘿） 俺今天這么激動(dòng)又想寫(xiě)文章的原因是MySQL5.1的發(fā)布帶來(lái)了設(shè)計(jì)超強(qiáng)動(dòng)力數(shù)據(jù)庫(kù)的強(qiáng)有力的武器，任何MySQL的DBA都應(yīng)該盡快學(xué)習(xí)并使用它。俺覺(jué)得如果能很好滴使用這個(gè)5.1版帶來(lái)的新特性，DBA可以使自己管理的VLDB（不知道什么是VLDB？告訴你，是好大好大的數(shù)據(jù)庫(kù)的意思，Very Large DB）或數(shù)據(jù)倉(cāng)庫(kù)奇跡般的獲得巨大的性能提升。 什么是數(shù)據(jù)庫(kù)分區(qū)？ 數(shù)據(jù)庫(kù)分區(qū)是一種物理數(shù)據(jù)庫(kù)設(shè)計(jì)技術(shù)，DBA和數(shù)據(jù)庫(kù)建模人員對(duì)其相當(dāng)熟悉。雖然分區(qū)技術(shù)可以實(shí)現(xiàn)很多效果，但其主要目的是為了在特定的SQL操作中減少數(shù)據(jù)讀寫(xiě)的總量以縮減響應(yīng)時(shí)間。 分區(qū)主要有兩種形式：//這里一定要注意行和列的概念（row是行，column是列） 水平分區(qū)（Horizontal Partitioning） 這種形式分區(qū)是對(duì)表的行進(jìn)行分區(qū)，通過(guò)這樣的方式不同分組里面的物理列分割的數(shù)據(jù)集得以組合，從而進(jìn)行個(gè)體分割（單分區(qū)）或集體分割（1個(gè)或多個(gè)分區(qū)）。所有在表中定義的列在每個(gè)數(shù)據(jù)集中都能找到，所以表的特性依然得以保持。 舉個(gè)簡(jiǎn)單例子：一個(gè)包含十年發(fā)票記錄的表可以被分區(qū)為十個(gè)不同的分區(qū)，每個(gè)分區(qū)包含的是其中一年的記錄。（朋奕注：這里具體使用的分區(qū)方式我們后面再說(shuō)，可以先說(shuō)一點(diǎn)，一定要通過(guò)某個(gè)屬性列來(lái)分割，譬如這里使用的列就是年份） 垂直分區(qū)（Vertical Partitioning） 這種分區(qū)方式一般來(lái)說(shuō)是通過(guò)對(duì)表的垂直劃分來(lái)減少目標(biāo)表的寬度，使某些特定的列被劃分到特定的分區(qū)，每個(gè)分區(qū)都包含了其中的列所對(duì)應(yīng)的行。 舉個(gè)簡(jiǎn)單例子：一個(gè)包含了大text和BLOB列的表，這些text和BLOB列又不經(jīng)常被訪問(wèn)，這時(shí)候就要把這些不經(jīng)常使用的text和BLOB了劃分到另一個(gè)分區(qū)，在保證它們數(shù)據(jù)相關(guān)性的同時(shí)還能提高訪問(wèn)速度。 在數(shù)據(jù)庫(kù)供應(yīng)商開(kāi)始在他們的數(shù)據(jù)庫(kù)引擎中建立分區(qū)（主要是水平分區(qū)）時(shí)，DBA和建模者必須設(shè)計(jì)好表的物理分區(qū)結(jié)構(gòu)，不要保存冗余的數(shù)據(jù)（不同表中同時(shí)都包含父表中的數(shù)據(jù)）或相互聯(lián)結(jié)成一個(gè)邏輯父對(duì)象（通常是視圖）。這種做法會(huì)使水平分區(qū)的大部分功能失效，有時(shí)候也會(huì)對(duì)垂直分區(qū)產(chǎn)生影響。 在MySQL 5.1中進(jìn)行分區(qū) ?????MySQL5.1中最激動(dòng)人心的新特性應(yīng)該就是對(duì)水平分區(qū)的支持了。這對(duì)MySQL的使用者來(lái)說(shuō)確實(shí)是個(gè)好消息，而且她已經(jīng)支持分區(qū)大部分模式： ????????? Range（范圍） – 這種模式允許DBA將數(shù)據(jù)劃分不同范圍。例如DBA可以將一個(gè)表通過(guò)年份劃分成三個(gè)分區(qū)，80年代（1980's）的數(shù)據(jù)，90年代（1990's）的數(shù)據(jù)以及任何在2000年（包括2000年）后的數(shù)據(jù)。? ????????? Hash（哈希） – 這中模式允許DBA通過(guò)對(duì)表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過(guò)這個(gè)Hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)，。例如DBA可以建立一個(gè)對(duì)表主鍵進(jìn)行分區(qū)的表。? ????????? Key（鍵值） – 上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。? ????????? List（預(yù)定義列表） – 這種模式允許系統(tǒng)通過(guò)DBA定義的列表的值所對(duì)應(yīng)的行數(shù)據(jù)進(jìn)行分割。例如：DBA建立了一個(gè)橫跨三個(gè)分區(qū)的表，分別根據(jù)2004年2005年和2006年值所對(duì)應(yīng)的數(shù)據(jù)。? ???????? ?Composite（復(fù)合模式） - 很神秘吧，哈哈，其實(shí)是以上模式的組合使用而已，就不解釋了。舉例：在初始化已經(jīng)進(jìn)行了Range范圍分區(qū)的表上，我們可以對(duì)其中一個(gè)分區(qū)再進(jìn)行hash哈希分區(qū)。 ????分區(qū)帶來(lái)的好處太多太多了，有多少？俺也不知道，自己猜去吧，要是覺(jué)得沒(méi)有多少就別用，反正俺也不求你用。不過(guò)在這里俺強(qiáng)調(diào)兩點(diǎn)好處： 性能的提升（Increased performance） - 在掃描操作中，如果MySQL的優(yōu)化器知道哪個(gè)分區(qū)中才包含特定查詢中需要的數(shù)據(jù)，它就能直接去掃描那些分區(qū)的數(shù)據(jù)，而不用浪費(fèi)很多時(shí)間掃描不需要的地方了。需要舉個(gè)例子？好啊，百萬(wàn)行的表劃分為10個(gè)分區(qū)，每個(gè)分區(qū)就包含十萬(wàn)行數(shù)據(jù)，那么查詢分區(qū)需要的時(shí)間僅僅是全表掃描的十分之一了，很明顯的對(duì)比。同時(shí)對(duì)十萬(wàn)行的表建立索引的速度也會(huì)比百萬(wàn)行的快得多得多。如果你能把這些分區(qū)建立在不同的磁盤(pán)上，這時(shí)候的I/O讀寫(xiě)速度就“不堪設(shè)想”（沒(méi)用錯(cuò)詞，真的太快了，理論上100倍的速度提升啊，這是多么快的響應(yīng)速度啊，所以有點(diǎn)不堪設(shè)想了）了。 對(duì)數(shù)據(jù)管理的簡(jiǎn)化（Simplified data management） - 分區(qū)技術(shù)可以讓DBA對(duì)數(shù)據(jù)的管理能力提升。通過(guò)優(yōu)良的分區(qū)，DBA可以簡(jiǎn)化特定數(shù)據(jù)操作的執(zhí)行方式。例如：DBA在對(duì)某些分區(qū)的內(nèi)容進(jìn)行刪除的同時(shí)能保證余下的分區(qū)的數(shù)據(jù)完整性(這是跟對(duì)表的數(shù)據(jù)刪除這種大動(dòng)作做比較的)。 此外分區(qū)是由MySQL系統(tǒng)直接管理的，DBA不需要手工的去劃分和維護(hù)。例如：這個(gè)例如沒(méi)意思，不講了，如果你是DBA，只要你劃分了分區(qū)，以后你就不用管了就是了。 站在性能設(shè)計(jì)的觀點(diǎn)上，俺們對(duì)以上的內(nèi)容也是相當(dāng)感興趣滴。通過(guò)使用分區(qū)和對(duì)不同的SQL操作的匹配設(shè)計(jì)，數(shù)據(jù)庫(kù)的性能一定能獲得巨大提升。下面咱們一起用用這個(gè)MySQL 5.1的新功能看看。 下面所有的測(cè)試都在Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM機(jī)器上（炫耀啊……），F(xiàn)edora Core 4和MySQL 5.1.6 alpha上運(yùn)行通過(guò)。 如何進(jìn)行實(shí)際分區(qū) 看看分區(qū)的實(shí)際效果吧。我們建立幾個(gè)同樣的MyISAM引擎的表，包含日期敏感的數(shù)據(jù)，但只對(duì)其中一個(gè)分區(qū)。分區(qū)的表（表名為part_tab）我們采用Range范圍分區(qū)模式，通過(guò)年份進(jìn)行分區(qū)： mysql> CREATE TABLE part_tab ??? ->?? ???(?c1 int default NULL, ??? ->?c2 varchar(30) default NULL, ??? ->?c3 date default NULL ??? -> ??? ->????? ) engine=myisam ??? ->????? PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), ??? ->????? PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , ??? ->????? PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , ??? ->????? PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , ??? ->????? PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , ??? ->????? PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), ??? ->????? PARTITION p11 VALUES LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.00 sec) 注意到了這里的最后一行嗎？這里把不屬于前面年度劃分的年份范圍都包含了，這樣才能保證數(shù)據(jù)不會(huì)出錯(cuò)，大家以后要記住啊，不然數(shù)據(jù)庫(kù)無(wú)緣無(wú)故出錯(cuò)你就爽了。那下面我們建立沒(méi)有分區(qū)的表（表名為no_part_tab）： mysql> create table no_part_tab ??? -> (c1 int(11) default NULL, ??? -> c2 varchar(30) default NULL, ??? -> c3 date default NULL) engine=myisam; Query OK, 0 rows affected (0.02 sec) 下面咱寫(xiě)一個(gè)存儲(chǔ)過(guò)程（感謝Peter Gulutzan給的代碼，如果大家需要Peter Gulutzan的存儲(chǔ)過(guò)程教程的中文翻譯也可以跟我要，chenpengyi◎gmail.com），它能向咱剛才建立的已分區(qū)的表中平均的向每個(gè)分區(qū)插入共8百萬(wàn)條不同的數(shù)據(jù)。填滿后，咱就給沒(méi)分區(qū)的克隆表中插入相同的數(shù)據(jù)： mysql> delimiter // mysql> CREATE PROCEDURE load_part_tab() ??? -> begin ??? ->?declare v int default 0; ??? ->????????? while v < 8000000 ??? ->?do ??? ->?insert into part_tab ??? ->?values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); ??? ->?set v = v + 1; ??? ->?end while; ??? -> end ??? -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> call load_part_tab(); Query OK, 1 row affected (8 min 17.75 sec) mysql> insert into no_part_tab select * from part_tab; Query OK, 8000000 rows affected (51.59 sec) Records: 8000000?Duplicates: 0?Warnings: 0 表都準(zhǔn)備好了。咱開(kāi)始對(duì)這兩表中的數(shù)據(jù)進(jìn)行簡(jiǎn)單的范圍查詢吧。先分區(qū)了的，后沒(méi)分區(qū)的，跟著有執(zhí)行過(guò)程解析（MySQL Explain命令解析器），可以看到MySQL做了什么： mysql> select count(*) from no_part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ |?? 795181 | +----------+ 1 row in set (38.30 sec) ? mysql> select count(*) from part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ |?? 795181 | +----------+ 1 row in set (3.88 sec) ? mysql> explain select count(*) from no_part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'\G *************************** 1. row *************************** ?????????? id: 1 ?select_type: SIMPLE ??????? table: no_part_tab ???????? type: ALL possible_keys: NULL ????????? key: NULL ????? key_len: NULL ????????? ref: NULL ???????? rows: 8000000 ??????? Extra: Using where 1 row in set (0.00 sec) ? mysql> explain partitions select count(*) from part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'\G *************************** 1. row *************************** ?????????? id: 1 ?select_type: SIMPLE ??????? table: part_tab ?? partitions: p1 ???????? type: ALL possible_keys: NULL ????????? key: NULL ????? key_len: NULL ????????? ref: NULL ???????? rows: 798458 ???? ???Extra: Using where 1 row in set (0.00 sec) 從上面結(jié)果可以容易看出，設(shè)計(jì)恰當(dāng)表分區(qū)能比非分區(qū)的減少90％的響應(yīng)時(shí)間。而命令解析Explain程序也告訴我們?cè)趯?duì)已分區(qū)的表的查詢過(guò)程中僅對(duì)第一個(gè)分區(qū)進(jìn)行了掃描，其他都跳過(guò)了。 嗶厲吧拉，說(shuō)阿說(shuō)……反正就是這個(gè)分區(qū)功能對(duì)DBA很有用拉，特別對(duì)VLDB和需要快速反應(yīng)的系統(tǒng)。 對(duì)Vertical Partitioning的一些看法 雖然MySQL 5.1自動(dòng)實(shí)現(xiàn)了水平分區(qū)，但在設(shè)計(jì)數(shù)據(jù)庫(kù)的時(shí)候不要輕視垂直分區(qū)。雖然要手工去實(shí)現(xiàn)垂直分區(qū)，但在特定場(chǎng)合下你會(huì)收益不少的。例如在前面建立的表中，VARCHAR字段是你平常很少引用的，那么對(duì)它進(jìn)行垂直分區(qū)會(huì)不會(huì)提升速度呢？咱們看看測(cè)試結(jié)果： mysql> desc part_tab; +-------+-------------+------+-----+---------+-------+ | Field | Type??????? | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1??? | int(11)???? | YES?|???? | NULL??? |?????? | | c2??? | varchar(30) | YES?|???? | NULL??? |?????? | | c3??? | date??????? | YES?|???? | NULL??? |?????? | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.03 sec) ? mysql> alter table part_tab drop column c2; Query OK, 8000000 rows affected (42.20 sec) Records: 8000000?Duplicates: 0?Warnings: 0 ? mysql> desc part_tab; +-------+---------+------+-----+---------+-------+ | Field | Type??? | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c1??? | int(11) | YES?|???? | NULL??? |?????? | | c3??? | date??? | YES?|???? | NULL??? |?????? | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) ? mysql> select count(*) from part_tab where ??? -> c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+ | count(*) | +----------+ |?? 795181 | +----------+ 1 row in set (0.34 sec) 在設(shè)計(jì)上去掉了VARCHAR字段后，不止是你，俺也發(fā)現(xiàn)查詢響應(yīng)速度上獲得了另一個(gè)90％的時(shí)間節(jié)省。所以大家在設(shè)計(jì)表的時(shí)候，一定要考慮，表中的字段是否真正關(guān)聯(lián)，又是否在你的查詢中有用？ 補(bǔ)充說(shuō)明? 這么簡(jiǎn)單的文章肯定不能說(shuō)全MySQL 5.1 分區(qū)機(jī)制的所有好處和要點(diǎn)（雖然對(duì)自己寫(xiě)文章水平很有信心），下面就說(shuō)幾個(gè)感興趣的： 支持所有存儲(chǔ)引擎(MyISAM, Archive, InnoDB, 等等) 對(duì)分區(qū)的表支持索引，包括本地索引local indexes，對(duì)其進(jìn)行的是一對(duì)一的視圖鏡像，假設(shè)一個(gè)表有十個(gè)分區(qū)，那么其本地索引也包含十個(gè)分區(qū)。 關(guān)于分區(qū)的元數(shù)據(jù)Metadata的表可以在INFORMATION_SCHEMA數(shù)據(jù)庫(kù)中找到，表名為PARTITIONS。 All SHOW 命令支持返回分區(qū)表以及元數(shù)據(jù)的索引。 對(duì)其操作的命令和實(shí)現(xiàn)的維護(hù)功能有（比對(duì)全表的操作還多）： ADD PARTITION DROP PARTITION COALESCE PARTITION REORGANIZE PARTITION ANALYZE PARTITION CHECK PARTITION OPTIMIZE PARTITION REBUILD PARTITION REPAIR PARTITION 站在性能主導(dǎo)的觀點(diǎn)上來(lái)說(shuō)，MySQL 5.1的分區(qū)功能能給數(shù)據(jù)性能帶來(lái)巨大的提升的同時(shí)減輕DBA的管理負(fù)擔(dān)，如果分區(qū)合理的話。如果需要更多的資料可以去 http://dev.mysql.com/doc/refman/5.1/en/partitioning.html 或 http://forums.mysql.com/list.php?106 獲得相關(guān)資料。 關(guān)于MySQL分區(qū)的使用方法很快發(fā)布上來(lái)，這里有什么錯(cuò)誤歡迎指出，或給我來(lái)信 ——2006-05-05陳朋奕 (http://www.fanqiang.com)

通過(guò)分區(qū)（Partition）提升MySQL性能