我們大家都知道通過MySQL數(shù)據(jù)庫分區(qū)（Partition）可以提升MySQL數(shù)據(jù)庫的性能，那么到底什么是MySQL數(shù)據(jù)庫分區(qū)呢？以及其實際應用的好處的表現(xiàn)有哪些呢？以下的文章就是對這些內容的描述。

什么是數(shù)據(jù)庫分區(qū)？

數(shù)據(jù)庫分區(qū)是一種物理數(shù)據(jù)庫設計技術，DBA和數(shù)據(jù)庫建模人員對其相當熟悉。雖然分區(qū)技術可以實現(xiàn)很多效果，但其主要目的是為了在特定的SQL操作中減少數(shù)據(jù)讀寫的總量以縮減響應時間。 分區(qū)主要有兩種形式：//這里一定要注意行和列的概念（row是行，column是列）

水平分區(qū)（Horizontal Partitioning） 這種形式分區(qū)是對表的行進行分區(qū)，通過這樣的方式不同分組里面的物理列分割的數(shù)據(jù)集得以組合，從而進行個體分割（單分區(qū)）或集體分割（1個或多個分區(qū)）。所有在表中定義的列在每個數(shù)據(jù)集中都能找到，所以表的特性依然得以保持。

舉個簡單例子：一個包含十年發(fā)票記錄的表可以被MySQL數(shù)據(jù)庫分區(qū)為十個不同的分區(qū)，每個分區(qū)包含的是其中一年的記錄。（朋奕注：這里具體使用的分區(qū)方式我們后面再說，可以先說一點，一定要通過某個屬性列來分割，譬如這里使用的列就是年份）

垂直分區(qū)（Vertical Partitioning） 這種分區(qū)方式一般來說是通過對表的垂直劃分來減少目標表的寬度，使某些特定的列被劃分到特定的分區(qū)，每個分區(qū)都包含了其中的列所對應的行。

舉個簡單例子：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被訪問，這時候就要把這些不經常使用的text和BLOB了劃分到另一個分區(qū)，在保證它們數(shù)據(jù)相關性的同時還能提高訪問速度。

在數(shù)據(jù)庫供應商開始在他們的數(shù)據(jù)庫引擎中建立分區(qū)（主要是水平分區(qū)）時，DBA和建模者必須設計好表的物理分區(qū)結構，不要保存冗余的數(shù)據(jù)（不同表中同時都包含父表中的數(shù)據(jù)）或相互聯(lián)結成一個邏輯父對象（通常是視圖）。這種做法會使水平分區(qū)的大部分功能失效，有時候也會對垂直分區(qū)產生影響。

在MySQL 5.1中進行分區(qū)

MySQL5.1中最激動人心的新特性應該就是對水平MySQL數(shù)據(jù)庫分區(qū)的支持了。這對MySQL的使用者來說確實是個好消息，而且她已經支持分區(qū)大部分模式：

Range（范圍） – 這種模式允許DBA將數(shù)據(jù)劃分不同范圍。例如DBA可以將一個表通過年份劃分成三個分區(qū)，80年代（1980's）的數(shù)據(jù)，90年代（1990's）的數(shù)據(jù)以及任何在2000年（包括2000年）后的數(shù)據(jù)。

Hash（哈希） – 這中模式允許DBA通過對表的一個或多個列的Hash Key進行計算，***通過這個Hash碼不同數(shù)值對應的數(shù)據(jù)區(qū)域進行分區(qū)，。例如DBA可以建立一個對表主鍵進行分區(qū)的表。

Key（鍵值） – 上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產生的。

List（預定義列表） – 這種模式允許系統(tǒng)通過DBA定義的列表的值所對應的行數(shù)據(jù)進行分割。例如：DBA建立了一個橫跨三個分區(qū)的表，分別根據(jù)2004年2005年和2006年值所對應的數(shù)據(jù)。

Composite（復合模式） - 很神秘吧，哈哈，其實是以上模式的組合使用而已，就不解釋了。舉例：在初始化已經進行了Range范圍分區(qū)的表上，我們可以對其中一個分區(qū)再進行hash哈希分區(qū)。#p#

分區(qū)帶來的兩點好處：

性能的提升（Increased performance） - 在掃描操作中，如果MySQL的優(yōu)化器知道哪個分區(qū)中才包含特定查詢中需要的數(shù)據(jù)，它就能直接去掃描那些分區(qū)的數(shù)據(jù)，而不用浪費很多時間掃描不需要的地方了。需要舉個例子？好啊，百萬行的表劃分為10個分區(qū)，每個分區(qū)就包含十萬行數(shù)據(jù)，那么查詢分區(qū)需要的時間僅僅是全表掃描的十分之一了，很明顯的對比。

同時對十萬行的表建立索引的速度也會比百萬行的快得多得多。如果你能把這些分區(qū)建立在不同的磁盤上，這時候的I/O讀寫速度就“不堪設想”（沒用錯詞，真的太快了，理論上100倍的速度提升啊，這是多么快的響應速度啊，所以有點不堪設想了）了。

對數(shù)據(jù)管理的簡化（Simplified data management） - 分區(qū)技術可以讓DBA對數(shù)據(jù)的管理能力提升。通過優(yōu)良的MySQL數(shù)據(jù)庫分區(qū)，DBA可以簡化特定數(shù)據(jù)操作的執(zhí)行方式。例如：DBA在對某些分區(qū)的內容進行刪除的同時能保證余下的分區(qū)的數(shù)據(jù)完整性(這是跟對表的數(shù)據(jù)刪除這種大動作做比較的)。 此外分區(qū)是由MySQL系統(tǒng)直接管理的，DBA不需要手工的去劃分和維護。例如：這個例如沒意思，不講了，如果你是DBA，只要你劃分了分區(qū)，以后你就不用管了就是了。

站在性能設計的觀點上，俺們對以上的內容也是相當感興趣滴。通過使用分區(qū)和對不同的SQL操作的匹配設計，數(shù)據(jù)庫的性能一定能獲得巨大提升。下面咱們一起用用這個MySQL 5.1的新功能看看。

下面所有的測試都在Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM機器上（炫耀啊……），F(xiàn)edora Core 4和MySQL 5.1.6 alpha上運行通過。

如何進行實際分區(qū) 看看分區(qū)的實際效果吧。我們建立幾個同樣的MyISAM引擎的表，包含日期敏感的數(shù)據(jù)，但只對其中一個分區(qū)。分區(qū)的表（表名為part_tab）我們采用Range范圍分區(qū)模式，通過年份進行MySQL數(shù)據(jù)庫分區(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ù)不會出錯，大家以后要記住啊，不然數(shù)據(jù)庫無緣無故出錯你就爽了。

那下面我們建立沒有MySQL數(shù)據(jù)庫分區(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)  

下面咱寫一個存儲過程它能向咱剛才建立的已分區(qū)的表中平均的向每個分區(qū)插入共8百萬條不同的數(shù)據(jù)。填滿后，咱就給沒分區(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 vv = 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   

表都準備好了。咱開始對這兩表中的數(shù)據(jù)進行簡單的范圍查詢吧。先分區(qū)了的，后沒MySQL數(shù)據(jù)庫分區(qū)的，跟著有執(zhí)行過程解析（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   
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   
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)   

從上面結果可以容易看出，設計恰當表分區(qū)能比非分區(qū)的減少90％的響應時間。而命令解析Explain程序也告訴我們在對已分區(qū)的表的查詢過程中僅對***個分區(qū)進行了掃描，其他都跳過了。

嗶厲吧拉，說阿說……反正就是這個分區(qū)功能對DBA很有用拉，特別對VLDB和需要快速反應的系統(tǒng)。

對Vertical Partitioning的一些看法 雖然MySQL 5.1自動實現(xiàn)了水平分區(qū)，但在設計數(shù)據(jù)庫的時候不要輕視垂直MySQL數(shù)據(jù)庫分區(qū)。雖然要手工去實現(xiàn)垂直分區(qū)，但在特定場合下你會收益不少的。例如在前面建立的表中，VARCHAR字段是你平常很少引用的，那么對它進行垂直分區(qū)會不會提升速度呢？咱們看看測試結果：

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)   

在設計上去掉了VARCHAR字段后，不止是你，俺也發(fā)現(xiàn)查詢響應速度上獲得了另一個90％的時間節(jié)省。所以大家在設計表的時候，一定要考慮，表中的字段是否真正關聯(lián)，又是否在你的查詢中有用？

補充說明

這么簡單的文章肯定不能說全MySQL 5.1 分區(qū)機制的所有好處和要點（雖然對自己寫文章水平很有信心），下面就說幾個感興趣的：

支持所有存儲引擎(MyISAM, Archive, InnoDB, 等等)

對分區(qū)的表支持索引，包括本地索引local indexes，對其進行的是一對一的視圖鏡像，假設一個表有十個分區(qū)，那么其本地索引也包含十個分區(qū)。

關于分區(qū)的元數(shù)據(jù)Metadata的表可以在INFORMATION_SCHEMA數(shù)據(jù)庫中找到，表名為PARTITIONS。

All SHOW 命令支持返回MySQL數(shù)據(jù)庫分區(qū)表以及元數(shù)據(jù)的索引。

對其操作的命令和實現(xiàn)的維護功能有（比對全表的操作還多）：

ADD PARTITION   
DROP PARTITION   
COALESCE PARTITION   
REORGANIZE PARTITION   
ANALYZE PARTITION   
CHECK PARTITION   
OPTIMIZE PARTITION   
REBUILD PARTITION   
REPAIR PARTITION  

 以上的相關內容就是對MySQL數(shù)據(jù)庫分區(qū)的介紹，望你能有所收獲。

【編輯推薦】

1. 使用mysqldump備份數(shù)據(jù)庫的具體步驟
2. CentOS系統(tǒng)找回mysql登錄密碼
3. CentOS系統(tǒng)5編譯安裝mysql-4.1.22
4. CentOS系統(tǒng)安裝MySQL支持遠程連接的方法
5. Ubuntu mysql可以把data防止到內存盤中