通過分區（Partition）提升MySQL性能（一）

From：http://www.bsdlover.cn/html/02/n-3602.html
什麼是數據庫分區？
數據庫分區是一種物理數據庫設計技術，DBA和數據庫建模人員對其相當熟悉。雖然分區技術可以實現很多效果，但其主要目的是為了在特定的SQL操作中減少數據讀寫的總量以縮減響應時間。

分區主要有兩種形式：//這裡一定要注意行和列的概念（row是行，column是列）
1. 水平分區（Horizontal Partitioning）這種形式分區是對表的行進行分區，通過這樣的方式不同分組裡面的物理列分割的數據集得以組合，從而進行個體分割（單分區）或集體分割（1個或多個分區）。所有在表中定義的列在每個數據集中都能找到，所以表的特性依然得以保持。
舉個簡單例子：一個包含十年發票記錄的表可以被分區為十個不同的分區，每個分區包含的是其中一年的記錄。（朋奕註：這裡具體使用的分區方式我們後面再說，可以先說一點，一定要通過某個屬性列來分割，譬如這裡使用的列就是年份）
2. 垂直分區（Vertical Partitioning） 這種分區方式一般來說是通過對表的垂直劃分來減少目標表的寬度，使某些特定的列 被劃分到特定的分區，每個分區都包含了其中的列所對應的行。

舉個簡單例子：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被訪問，這時候就要把這些不經常使用的text和BLOB了劃分到另一個分區，在保證它們數據相關性的同時還能提高訪問速度。
在數據庫供應商開始在他們的數據庫引擎中建立分區（主要是水平分區）時，DBA和建模者必須設計好表的物理分區結構，不要保存冗餘的數據（不同表中同時都包含父表中的數據）或相互聯結成一個邏輯父對象（通常是視圖）。這種做法會使水平分區的大部分功能失效，有時候也會對垂直分區產生影響。
在MySQL 5.1中進行分區BSD
MySQL5.1中最激動人心的新特性應該就是對水平分區的支持了。這對MySQL的使用者來說確實是個好消息，而且她已經支持分區大部分模式：
Range（範圍） – 這種模式允許DBA將數據劃分不同範圍。例如DBA可以將一個表通過年份劃分成三個分區，80年代（1980's）的數據，90年代（1990's）的數據以及任何在2000年（包括2000年）後的數據。
Hash（哈希） – 這中模式允許DBA通過對表的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的數據區域進行分區，。例如DBA可以建立一個對表主鍵進行分區的表。
Key（鍵值） – 上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。
List（預定義列表） – 這種模式允許系統通過DBA定義的列表的值所對應的行數據進行分割。例如：DBA建立了一個橫跨三個分區的表，分別根據2004年2005年和2006年值所對應的數據。
Composite（複合模式） - 很神秘吧，哈哈，其實是以上模式的組合使用而已，就不解釋了。舉例：在初始化已經進行了Range範圍分區的表上，我們可以對其中一個分區再進行 hash 值分區。
分區帶來的好處太多太多了，有多少？俺也不知道，自己猜去吧，要是覺得沒有多少就別用，反正俺也不求你用。不過在這裡俺強調兩點好處：
性能的提升（Increased performance） - 在掃瞄操作中，如果MySQL的優化器知道哪個分區中才包含特定查詢中需要的數據，它就能直接去掃瞄那些分區的數據，而不用浪費很多時間掃瞄不需要的地方了。需要舉個例子？好啊，百萬行的表劃分為10個分區，每個分區就包含十萬行數據，那麼查詢分區需要的時間僅僅是全表掃瞄的十分之一了，很明顯的對比。同時對十萬行的表建立索引的速度也會比百萬行的快得多得多。如果你能把這些分區建立在不同的磁盤上，這時候的I/O讀寫速度就「不堪設想」（沒用錯詞，真的太快了，理論上100倍的速度提升啊，這是多麼快的響應速度啊，所以有點不堪設想了）了。
對數據管理的簡化（Simplified data management） - 分區技術可以讓DBA對數據的管理能力提升。通過優良的分區，DBA可以簡化特定數據操作的執行方式。例如：DBA在對某些分區的內容進行刪除的同時能保證餘下的分區的數據完整性(這是跟對表的數據刪除這種大動作做比較的)。
此外分區是由MySQL系統直接管理的，DBA不需要手工的去劃分和維護。例如：這個例如沒意思，不講了，如果你是DBA，只要你劃分了分區，以後你就不用管了就是了。
站在性能設計的觀點上，俺們對以上的內容也是相當感興趣滴。通過使用分區和對不同的SQL操作的匹配設計，數據庫的性能一定能獲得巨大提升。下面咱們一起用用這個MySQL 5.1的新功能看看。
下面所有的測試都在Dell Optiplex box with a Pentium 4 3.00GHz processor, 1GB of RAM機器上（炫耀啊……），Fedora Core 4和MySQL 5.1.6 alpha上運行通過。
如何進行實際分區
看看分區的實際效果吧。我們建立幾個同樣的MyISAM引擎的表，包含日期敏感的數據，但只對其中一個分區。分區的表（表名為part_tab）我們採用Range範圍分區模式，通過年份進行分區：

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)

注意到了這裡的最後一行嗎？這裡把不屬於前面年度劃分的年份範圍都包含了，這樣才能保證數據不會出錯，大家以後要記住啊，不然數據庫無緣無故出錯你就爽了。那下面我們建立沒有分區的表（表名為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)

下面咱寫一個存儲過程（感謝Peter Gulutzan給的代碼，如果大家需要Peter Gulutzan的存儲過程教程的中文翻譯也可以跟我要，chenpengyi◎gmail.com），它能向咱剛才建立的已分區的表中平均的向每個分區插入共8百萬條不同的數據。填滿後，咱就給沒分區的克隆表中插入相同的數據：

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

表都準備好了。咱開始對這兩表中的數據進行簡單的範圍查詢吧。先分區了的，後沒分區的，跟著有執行過程解析（MySQL Explain命令解析器），可以看到MySQL做了什麼：

mysql>selectcount(*)fromno_part_tabwhere
->c3>date'1995-01-01'andc3<date'1995-12-31';
+———-+
|count(*)|
+———-+
|795181|
+———-+
1rowinset(38.30sec)
mysql>selectcount(*)frompart_tabwhere
->c3>date'1995-01-01'andc3<date'1995-12-31';
+———-+
|count(*)|
+———-+
|795181|
+———-+
1rowinset(3.88sec)
mysql>explainselectcount(*)fromno_part_tabwhere
->c3>date'1995-01-01'andc3<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:Usingwhere
1rowinset(0.00sec)
mysql>explainpartitionsselectcount(*)frompart_tabwhere
->c3>date'1995-01-01'andc3<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:Usingwhere
1rowinset(0.00sec)

從上面結果可以容易看出，設計恰當表分區能比非分區的減少90％的響應時間。而命令解析Explain程序也告訴我們在對已分區的表的查詢過程中僅對第一個分區進行了掃瞄，其他都跳過了。
嗶厲吧拉，說阿說……反正就是這個分區功能對DBA很有用拉，特別對VLDB和需要快速反應的系統。
站在性能主導的觀點上來說，MySQL 5.1的分區功能能給數據性能帶來巨大的提升的同時減輕DBA的管理負擔，如果分區合理的話。如果需要更多的資料可以去 http://dev.mysql.com/doc/refman/5.1/en/partitioning.html 或 http://forums.mysql.com/list.php?106獲得相關資料。

Please follow and like us: