一:概念
MySQL从5.1版本开始支持分区的功能。分区是指根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言,逻辑上只有一个表或一个索引,但是实际上这个表可能由数10个物理分区对象组成,每个分区都是一个独立的对象,可以独自处理,可以作为表的一部分进行处理。分区对应用来说是完全透明的,不影响应用的业务逻辑。
二:分区优点
- 性能提升: 分区可以提高查询性能,特别是当查询只涉及到某个分区的数据时,数据库可以仅扫描相关分区,而不是整个表。这样可以减少IO操作,提高查询速度。
- 数据维护简化: 分区使得数据的维护更加灵活和简便。可以更容易地执行针对某个特定分区的数据备份、恢复、重新构建索引等操作,而不会影响整个表的数据。
- 空间管理: 分区可以帮助更有效地管理存储空间。例如,可以将历史数据移动到不同的分区,以便更容易地进行归档或删除。这有助于降低整个数据库的存储成本。
- 更好的并发控制: 在某些情况下,使用分区可以提高并发性,因为不同的分区可以并行处理不同的查询请求。
- 更容易维护大型表: 对于非常大的表,分区可以帮助提高查询性能和维护效率,使其更容易处理和管理。
- 维护成本低。如果一个成熟的业务遇到瓶颈后引入表分区技术,与分表比起来代码维护量小,基本不用改动,且不需额外创建子表。
三:分区局限性
- 必须使用分区字段才行,不然分区查询就会失败。走所有分区,这样反而导致查询变慢,性能不升反降
- 分区键选择选择不当后,可能会导致不均匀的数据分布,进而影响性能
- 分区的实施和管理可能会增加数据库的复杂性。在设计和维护分区方案时,需要考虑额外的管理和维护工作,包括分区键的选择、分区策略等。
四:分区介绍
目前MySQL支持一下四种类型的分区:
- RANGE分区:基于一个给定区间边界,得到若干个连续区间范围,按照分区键的落点,把数据分配到不同的分区;
- LIST分区:类似RANGE分区,区别在于LIST分区是基于枚举出的值列表分区,RANGE是基于给定连续区间范围分区;
- HASH分区:基于用户自定义的表达式的返回值,对其根据分区数来取模,从而进行记录在分区间的分配的模式。这个用户自定义的表达式,就是MySQL希望用户填入的哈希函数。
- KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且使用MySQL 服务器提供的自身的哈希函数。
如果表存在主键或者唯一索引时,分区列必须是唯一索引的一个组成部分,即将分区字段和唯一索引创建组合索引。
1:RANGE分区
如下创建一个test1表,创建三个分区,当time字段值小于1704038400时放入part0分区,当time字段值小于1735660800时放入part1分区,其余数据放入part2分区
CREATE TABLE `test1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`time` int(11) NOT NULL DEFAULT 0 COMMENT '时间',
PRIMARY KEY (`id`,`time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (time) PARTITIONS 3 (
PARTITION part0 VALUES LESS THAN (1704038400),
PARTITION part1 VALUES LESS THAN (1735660800),
PARTITION part2 VALUES LESS THAN MAXVALUE
);
2:LIST分区
如下创建一个test2表,创建两个分区,将status值为0和1的放入part0分区,将status值为2和3的放入part1分区
CREATE TABLE `test2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`status` tinyint(2) NOT NULL DEFAULT 0 COMMENT 'status',
PRIMARY KEY (`id`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(status) (
PARTITION part0 VALUES IN (0,1),
PARTITION part1 VALUES IN (2,3)
);
LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。所以将要匹配的任何值都必须在值列表中能够找到
3:HASH分区
如下创建一个test3表,并创建三个HASH分区,在HASH分区中,MySQL自动完成分配记录到区间的工作,你所要做的只是确定一个用来做哈希的字段或者表达式,以及指定被分区的表将要被分割成的分区数量
CREATE TABLE `test3` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`status` tinyint(2) NOT NULL DEFAULT 0 COMMENT 'status',
PRIMARY KEY (`id`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH (status) PARTITIONS 3;
- hash分区的字段不能太复杂,否则插入将会有性能的影响
- hash分区的优势在对单条数据的查找,范围查找的性能不如RANGE分区
- hash分区只支持数字分区,或用表达式将字符串转成数字
4:KEY分区
如下创建一个test4表,并创建三个KEY分区,key分区类似于hash分区,本质区别是hash分区使用的是用户自定义的表达式,而key分区函数是由MySQL 服务器提供的,不同的存储引擎使用不同的内部函数。 创建key分区的语法和hash分区差不多
CREATE TABLE `test4` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`status` tinyint(2) NOT NULL DEFAULT 0 COMMENT 'status',
PRIMARY KEY (`id`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY (status) PARTITIONS 3;
KEY分区和HASH分区区别
- 关键字由HASH替换为KEY,例如PARTITION BY KEY()
- KEY中包含0个或者多个列名。如果一个表有主键的话那么任何被用于key分区的列必须是表中主键的一部分。若表中有定义主键,且key分区中不包含任何一个列名,则表的主键列将会被用于key分区
五:分区注意事项
- MySQL中的分区在禁止空值(NULL)上没有进行处理。在RANGE分区中,无论是插入一个列值为NULL或者表达式值为NULL的记录,都被当作是小于任何其他值,会默认被保存在从低到高排好序的第一个分区。在LIST分区中,如果所有分区LIST列表值里都没有NULL值,则插入含有NULL值的记录时会报错。在hash和key分区中NULL值则都当作0处理。
- 一个表最多能有1024个分区,在5.7版本及以上可以有8196个分区
- 常见的InnoDB 、 MyISAM引擎都支持分区
六:分区常用操作sql
1:删除分区并删除数据
alter table test(表名) drop partition p1(分区名);
2:删除分区的数据,保留分区
alter table test(表名) truncate partition p1(分区名);
3:移除整个表的分区,不删除数据
alert table test(表名) remove PARTITIONING ;
文章来源:https://www.cnaaa.net,转载请注明出处:https://www.cnaaa.net/archives/11933