MySQL使用Partition功能實現水平分區的策略

Posted on by

1 回顧

上一節我們詳細講解瞭如何對數據庫進行分區操作，包括瞭 垂直拆分（Scale Up 縱向擴展）和水平拆分（Scale Out 橫向擴展） ，同時簡要整理瞭水平分區的幾種策略，現在來回顧一下。

2 水平分區的5種策略

2.1 、Hash（哈希）

這種策略是通過對表的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的數據區域進行分區。例如我們可以建立一個對表的日期的年份進行分區的策略，這樣每個年份都會被聚集在一個區間。

 PARTITION BY HASH(YEAR(createtime))
 PARTITIONS 10

2.2、 Range（范圍） 

這種策略是將數據劃分不同范圍。例如我們可以將一個千萬級別的表通過id劃分成4個分區，每個分區大約500W的數據，超過750W後的數據統一放在第4個分區。

PARTITION BY RANGE(id) (
 PARTITIONP0 VALUES LESS THAN(2500001),
 PARTITIONP1 VALUES LESS THAN(5000001),
 PARTITIONp2 VALUES LESS THAN(7500001),
 PARTITIONp3 VALUES LESS THAN MAXVALUE
 )

2.3、Key（鍵值）

Hash策略的一種延伸，這裡的Hash Key是MySQL系統產生的。

2.4、List（預定義列表）

這種策略允許系統通過定義列表的值所對應的行數據進行分割。例如，我們根據崗位編碼進行分區，不同崗位類型的編碼對應到不同的分區去，達到分治的目的。

 PARTITION BY LIST(gwcode) (
 PARTITIONP0 VALUES IN (46,77,89),
 PARTITIONP1 VALUES IN (106,125,177),
 PARTITIONP2 VALUES IN (205,219,289),
 PARTITIONP3 VALUES IN (302,317,458,509,610)
)

上述的SQL腳本，使用瞭列表匹配LIST函數對員工崗位編號進行分區，共分為4個分區，行政崗位 編號為46,77,89的對應在分區P0中，技術崗位 106,125,177類別在分區P1中，依次類推即可。

2.5、Composite（復合模式）

復合模式其實就是對上面幾種模式的組合使用，比如你在Range的基礎上，再進行Hash 哈希分區。

3 測試Range策略

3.1 建立總表與分表

我們建立一個普通的用戶表 users，再建立一個分區表users_part，將80年代出生的用戶按照年份進行瞭分區，如下：

3.1.1 總表語句

mysql> CREATE TABLE users
(
 "id" int(10) unsigned NOT NULL,
  "name" varchar(100) DEFAULT NULL,
  "birth" datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected

3.1.2 分表語句

最後一行註意一下，是將89年之後出生的都歸屬到第10個分區上，我們這邊模擬的都是80年代出生的用戶，實際業務中跟據具體情況進行拆分。

 mysql> create table users_part
 (
   "id" int(10) unsigned NOT NULL,
    "name" varchar(100) DEFAULT NULL,
    "birth" datetime
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  PARTITION BY RANGE (year(birth)) (
  PARTITION p0 VALUES LESS THAN (1981),
  PARTITION p1 VALUES LESS THAN (1982),
 PARTITION p2 VALUES LESS THAN (1983),
 PARTITION p3 VALUES LESS THAN (1984),
 PARTITION p4 VALUES LESS THAN (1985),
 PARTITION p5 VALUES LESS THAN (1986),
PARTITION p6 VALUES LESS THAN (1987),
 PARTITION p7 VALUES LESS THAN (1988),
 PARTITION p8 VALUES LESS THAN (1989),17 PARTITION p9 VALUES LESS THAN MAXVALUE
 );
 Query OK, 0 rows affected

3.2 初始化表數據

我們可以使用函數或者存儲過程批量進行數據初始化,這邊插入1000W條數據。

DROP PROCEDURE IF EXISTS init_users_part;

delimiter $     /* 設定語句終結符為 $*/
CREATE PROCEDURE init_users_part()
  begin
   DECLARE srt int default 0;
   while
    srt < 10000000  /* 設定寫入1000W的數據 */
   do
    insert into `users_part` values (srt, concat('username_',idx1),adddate('1980-01-01',rand() * 3650)); /*在10年的時間內隨機取值*/
    set srt = srt + 1;
   end while;
  end $
delimiter ;


call init_users_part();

3.3 同步數據至完整表中

mysql> insert into users select * from users_part;      //將1000w數據復制到未分區的完整表users 中

 Query OK, 10000000 rows affected (51.59 sec) 

 Records: 10000000 Duplicates: 0 Warnings: 0

3.4 測試執行SQL的效率

mysql> select count(*) from users_part where `birth`  > '1986-01-01' and `birth` < '1986-12-31';
+----------+
| count(*) |
+----------+
|   976324 |
+----------+
1 row in set (0.335 sec)
mysql> select count(*) from users where `birth`  > '1986-01-01' and `birth` < '1986-12-31';
+----------+
| count(*) |
+----------+
|   976324 |
+----------+
1 row in set (5.187 sec)

結果比較清晰，分區表的執行效率確實比較高，執行時間是未分區表 1/10 都不到。

3.5 使用Explain執行計劃分析

mysql> explain select count(*) from users_part where `birth`  > '1986-01-01' and `birth` < '1986-12-31';
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | users_part | p7         | ALL  | NULL          | NULL | NULL    | NULL | 987769|   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(*) from users where  `birth`  > '1986-01-01' and `birth` < '1986-12-31';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |10000000 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

這邊關註兩個關鍵參數：一個 是partitions，users_part中是p7，說明數據檢索在第七分區中，users表是null的，說明是全區域掃描，無分區。

另外一個參數是rows，是預測掃描的行數，users表明顯是全表掃描。

3.6 建索引提效

因為我們使用birth字段進行分區和條件查詢，所以這邊嘗試在birth字段上簡歷索引進行效率優化。

mysql> create index idx_user on users(birth);
Query OK, 0 rows affected (1 min 7.04 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> create index idx_user_part on users_part(birth);
Query OK, 0 rows affected (1 min 1.05 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

創建索引後的數據庫文件大小列表：

2008-05-24 09:23             8,608 no_part_tab.frm
2008-05-24 09:24       255,999,996 no_part_tab.MYD
2008-05-24 09:24        81,611,776 no_part_tab.MYI
2008-05-24 09:25                 0 part_tab#P#p0.MYD
2008-05-24 09:26             1,024 part_tab#P#p0.MYI
2008-05-24 09:26        25,550,656 part_tab#P#p1.MYD
2008-05-24 09:26         8,148,992 part_tab#P#p1.MYI
2008-05-24 09:26        25,620,192 part_tab#P#p10.MYD
2008-05-24 09:26         8,170,496 part_tab#P#p10.MYI
2008-05-24 09:25                 0 part_tab#P#p11.MYD
2008-05-24 09:26             1,024 part_tab#P#p11.MYI
2008-05-24 09:26        25,656,512 part_tab#P#p2.MYD
2008-05-24 09:26         8,181,760 part_tab#P#p2.MYI
2008-05-24 09:26        25,586,880 part_tab#P#p3.MYD
2008-05-24 09:26         8,160,256 part_tab#P#p3.MYI
2008-05-24 09:26        25,585,696 part_tab#P#p4.MYD
2008-05-24 09:26         8,159,232 part_tab#P#p4.MYI
2008-05-24 09:26        25,585,216 part_tab#P#p5.MYD
2008-05-24 09:26         8,159,232 part_tab#P#p5.MYI
2008-05-24 09:26        25,655,740 part_tab#P#p6.MYD
2008-05-24 09:26         8,181,760 part_tab#P#p6.MYI
2008-05-24 09:26        25,586,528 part_tab#P#p7.MYD
2008-05-24 09:26         8,160,256 part_tab#P#p7.MYI
2008-05-24 09:26        25,586,752 part_tab#P#p8.MYD
2008-05-24 09:26         8,160,256 part_tab#P#p8.MYI
2008-05-24 09:26        25,585,824 part_tab#P#p9.MYD
2008-05-24 09:26         8,159,232 part_tab#P#p9.MYI
2008-05-24 09:25             8,608 part_tab.frm
2008-05-24 09:25                68 part_tab.par

再次測試SQL性能

mysql> select count(*) from users_part where `birth`  > '1986-01-01' and `birth` < '1986-12-31';
+----------+
| count(*) |
+----------+
|   976324 |
+----------+
1 row in set (0.171 sec)

mysql> select count(*) from users where `birth`  > '1986-01-01' and `birth` < '1986-12-31';
+----------+
| count(*) |
+----------+
|   976324 |
+----------+
1 row in set (0.583 sec)

這邊可以看到，在關鍵的字段添加索引並重啟（net stop mysql，net start mysql）之後，分區的表性能有略微提升。而未分區的全表性能提升最明顯，幾乎接近分區的效率。

3.7 跨區執行效率分析

通過上面的分析可以看出，在單個區內執行，比不分區效率又很明顯的差距，這是因為分區之後掃描非范圍縮小瞭。

那如果我們上面條件增加出生年份的范圍，讓他產生跨區域的情況，效果會怎麼樣呢，我們測試一下。

mysql> select count(*) from users_part where `birth`  > '1986-01-01' and `birth` < '1987-12-31';
+----------+
| count(*) |
+----------+
|   976324 |
+----------+
1 row in set (1.914 sec)

mysql> select count(*) from users where `birth`  > '1986-01-01' and `birth` < '1987-12-31';
+----------+
| count(*) |
+----------+
|   976324 |
+----------+
1 row in set (3.871 sec)

可見，跨區之後性能會差一些。這邊應該這樣理解，跨區的越多，性能越差，所以做分區設計的時候應該意識到，避免那種頻繁的跨區情況發生，謹慎判斷分區邊界條件。

3.8 總結

1、分區和未分區占用文件空間大致相同 （數據和索引文件）

2、查詢語句中關鍵字段未建立索引字段時，分區時間遠遠優於未分區時間

3、如果查詢語句中字段建立瞭索引，分區和未分區的差別縮小，但是仍然優於未分區情況，而且隨著數據量增加，這個優勢會更明顯。

4、對於大數據量，還是建議使用分區功能，無論他有沒有建立索引。

5、根據MySQL手冊， 增加myisam_max_sort_file_size 會增加分區性能(mysql重建索引時允許使用的臨時文件最大大小)

6、對分區進行設計時，謹慎判斷分區邊界條件，避免有過度頻繁的跨區操作，否則性能不會理想。

4 分區策略詳解

4.1 、HASH（哈希）

HASH分區主要用來確保數據在預先確定數目的分區中平均分佈，而在RANGE和LIST分區中，必須明確指定一個給定的列值或列值集合應該保存在哪個分區中，

而在HASH分區中，MySQL自動完成這些工作，

你所要做的隻是基於將要被哈希的列值指定一個列值或表達式，以及指定被分區的表將要被分割成的分區數量。 示例如下：

/*Hash*/
drop table if EXISTS  `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(YEAR(createtime))
PARTITIONS 4(
     PARTITION P0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
     PARTITION P1 DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
     PARTITION P2 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
     PARTITION P3 DATA DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);

上面的例子，使用HASH函數對createtime日期進行HASH運算，並根據這個日期來分區數據，這裡共分為10個分區。

建表語句上添加一個“PARTITION BY HASH (expr)”子句，其中“expr”是一個返回整數的表達式，它可以是字段類型為MySQL 整型的一列的名字，也可以是返回非負數的表達式。

另外，可能需要在後面再添加一個“PARTITIONS num”子句，其中num 是一個非負的整數，它表示表將要被分割成分區的數量。

每個分區都有自己獨立的數據、索引文件的存放目錄，並且這些目錄所在的物理磁盤分區可能也都是完全獨立的，可以提高磁盤IO吞吐量。

4.2、 RANGE（范圍）

基於屬於一個給定連續區間的列值，把多行分配給同一個分區，這些區間要連續且不能相互重疊，使用VALUES LESS THAN操作符來進行定義。示例如下：

/*Range*/
drop table if EXISTS  `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(gwcode) (
PARTITION P0 VALUES LESS THAN(101) DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
PARTITION P1 VALUES LESS THAN(201) DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
PARTITION P2 VALUES LESS THAN(301) DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
PARTITION P3 VALUES LESS THAN MAXVALUE DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);

上面的示例，使用瞭范圍RANGE函數對崗位編號進行分區，共分為4個分區，

崗位編號為1~100 的對應在分區P0中，101~200的編號在分區P1中，依次類推即可。那麼類別編號大於300，可以使用MAXVALUE來將大於300的數據統一存放在分區P3中即可。

每個分區都有自己獨立的數據、索引文件的存放目錄，並且這些目錄所在的物理磁盤分區可能也都是完全獨立的，可以提高磁盤IO吞吐量。

4.3 、LIST（預定義列表）

類似於按RANGE分區，區別在於LIST分區是基於列值匹配一個離散值集合中的某個值來進行選擇分區的。LIST分區通過使用“PARTITION BY LIST(expr)”來實現，其中“expr” 是某列值或一個基於某個列值、並返回一個整數值的表達式，

然後通過“VALUES IN (value_list)”的方式來定義每個分區，其中“value_list”是一個通過逗號分隔的整數列表。 示例如下：

/*List*/
drop table if EXISTS  `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST(`gwcode`) (
PARTITION P0 VALUES IN (46,77,89) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
PARTITION P1 VALUES IN (106,125,177) DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
PARTITION P2 VALUES IN (205,219,289) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
PARTITION P3 VALUES IN (302,317,458,509,610) DATA DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);

上面的例子，使用瞭列表匹配LIST函數對員工崗位編號進行分區，共分為4個分區，編號為46,77,89的對應在分區P0中，106,125,177類別在分區P1中，依次類推即可。

不同於RANGE的是，LIST分區的數據必須匹配列表中的崗位編號才能進行分區，所以這種方式隻是適合比較區間值確定並少量的情況。

每個分區都有自己獨立的數據、索引文件的存放目錄，並且這些目錄所在的物理磁盤分區可能也都是完全獨立的，可以提高磁盤IO吞吐量。

4.4 KEY（鍵值）

類似於按HASH分區，區別在於KEY分區隻支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數。必須有一列或多列包含整數值。 示例如下：

/*key*/
drop table if EXISTS  `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(gwcode)
PARTITIONS 4(
     PARTITION P0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
     PARTITION P1 DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
     PARTITION P2 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
     PARTITION P3 DATA DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);

註意：此種分區算法目前使用的比較少，使用服務器提供的哈希函數有不確定性，對於後期數據統計、整理存在會更復雜，所以我們更傾向於使用由我們定義表達式的Hash，大傢知道其存在和怎麼使用即可。

4.5 嵌套分區（子分區）

嵌套分區（子分區）是針對 RANGE/LIST 類型的分區表中每個分區的再次分割。再次分割可以是 HASH/KEY 等類型。

drop table if EXISTS `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (id) SUBPARTITION BY HASH (id% 4) SUBPARTITIONS 2(
     PARTITION p0 VALUES LESS THAN (5000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
     PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx'

);

如上，對RANGE 分區再次進行子分區劃分，子分區采用 HASH 類型。

5 分區管理

5.1 刪除分區

/*刪除分區 P1*/
2  ALERT TABLE users_part DROP PARTITION P1;

5.2 重建分區

5.2.1 RANGE 分區重建

/*這邊將原來的 P0,P1 分區合並起來，放到新的 P0 分區中，並重新設定條件為少於5000000。*/
ALTER TABLE users_part REORGANIZE PARTITION P0,P1 INTO (PARTITION P0 VALUES LESS THAN (5000000));

用於因空間過於浪費而產生的合並情況。

5.2.2 LIST 分區重建

/*將原來的 P0,P1 分區合並起來，放到新的 P0 分區中,跟上一個的意思有點像。*/
ALTER TABLE users_part REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(1,4,5,8,9,12,13,101,555));

5.2.3 HASH/KEY 分區重建

/*用 REORGANIZE 方式重建分區的數量變成2，在這裡數量隻能減少不能增加。想要增加可以用 ADD PARTITION 方法。*/
ALTER TABLE users_part REORGANIZE PARTITION COALESCE PARTITION 2;

5.3 新增分區

5.3.1 新增 RANGE 分區

 /*新增一個RANGE分區*/
 ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19) 
 DATA DIRECTORY = '/data8/data'
 INDEX DIRECTORY = '/data8/idx');

5.3.2 新增 HASH/KEY 分區

/* 將分區總數擴展到n個。n請用數值代替 */
ALTER TABLE users_part ADD PARTITION PARTITIONS n;

5.3.3 給已有的表加上分區

alter tableuser_part partition by RANGE (month(birth))
(
PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2) ,
PARTITION p2 VALUES LESS THAN (3) ,
PARTITION p3 VALUES LESS THAN (4) ,
PARTITION p4 VALUES LESS THAN (5) ,
PARTITION p5 VALUES LESS THAN (6) ,
PARTITION p6 VALUES LESS THAN (7) ,
PARTITION p7 VALUES LESS THAN (8) ,
PARTITION p8 VALUES LESS THAN (9) ,
PARTITION p9 VALUES LESS THAN (10) ,
PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION P12 VALUES LESS THAN (13)
);

6 去除分區主鍵限制

默認分區限制分區字段必須是主鍵（PRIMARY KEY)的一部分，需要去除此限制。

如果表中設立主鍵，會報出如下提示：A PRIMARY KEY must include all columns in the table’s partitioning function (prefixed columns are not considered).

一種解決方式就是使用主鍵來做為分區條件：

ALTER TABLE users_part  PARTITION BY HASH(id)  PARTITIONS 4;

另外一種方式就是把分區條件字段加入主鍵中，變成聯合主鍵。如下，id和gwcode 組成瞭聯合主鍵：

 alter table users_part drop PRIMARY KEY;
 alter table users_part add PRIMARY KEY(id, gwcode);

到此這篇關於MySQL使用Partition功能實現水平分區的文章就介紹到這瞭,更多相關mysql水平分區內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：