MySQL全面瓦解29：分库分表之Partition功能详解

hxiaoyv

发布于 2022-9-20 11:25

浏览

0收藏

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 分表语句

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),
 PARTITION p9 VALUES LESS THAN MAXVALUE
 );
 Query OK, 0 rows affected

最后一行注意一下，是将89年之后出生的都归属到第10个分区上，我们这边模拟的都是80年代出生的用户，实际业务中跟据具体情况进行拆分。

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'20 );

注意：此种分区算法目前使用的比较少，使用服务器提供的哈希函数有不确定性，对于后期数据统计、整理存在会更复杂，所以我们更倾向于使用由我们定义表达式的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*/
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'4 INDEX DIRECTORY = '/data8/idx');

5.3.2 新增 HASH/KEY 分区

/* 将分区总数扩展到n个。n请用数值代替 */
ALTER TABLE users_part ADD PARTITION PARTITIONS n;

5.3.3 给已有的表加上分区

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

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);

本文转载自公众号架构与思维

分类

其他

标签

sql

已于2022-9-20 11:25:51修改

51CTO

51CTO博客

51CTO学堂

MySQL全面瓦解29：分库分表之Partition功能详解

目录

订阅鸿蒙技术特刊，精选内容抢先看