MYSQL分区

一：概念

MySQL从5.1版本开始支持分区的功能。分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言，逻辑上只有一个表或一个索引，但是实际上这个表可能由数10个物理分区对象组成，每个分区都是一个独立的对象，可以独自处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

二：分区优点

1. 性能提升： 分区可以提高查询性能，特别是当查询只涉及到某个分区的数据时，数据库可以仅扫描相关分区，而不是整个表。这样可以减少IO操作，提高查询速度。
2. 数据维护简化： 分区使得数据的维护更加灵活和简便。可以更容易地执行针对某个特定分区的数据备份、恢复、重新构建索引等操作，而不会影响整个表的数据。
3. 空间管理： 分区可以帮助更有效地管理存储空间。例如，可以将历史数据移动到不同的分区，以便更容易地进行归档或删除。这有助于降低整个数据库的存储成本。
4. 更好的并发控制： 在某些情况下，使用分区可以提高并发性，因为不同的分区可以并行处理不同的查询请求。
5. 更容易维护大型表： 对于非常大的表，分区可以帮助提高查询性能和维护效率，使其更容易处理和管理。
6. 维护成本低。如果一个成熟的业务遇到瓶颈后引入表分区技术，与分表比起来代码维护量小，基本不用改动，且不需额外创建子表。

三：分区局限性

1. 必须使用分区字段才行，不然分区查询就会失败。走所有分区，这样反而导致查询变慢，性能不升反降
2. 分区键选择选择不当后，可能会导致不均匀的数据分布，进而影响性能
3. 分区的实施和管理可能会增加数据库的复杂性。在设计和维护分区方案时，需要考虑额外的管理和维护工作，包括分区键的选择、分区策略等。

四：分区介绍

目前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分区区别

1. 关键字由HASH替换为KEY，例如PARTITION BY KEY()
2. KEY中包含0个或者多个列名。如果一个表有主键的话那么任何被用于key分区的列必须是表中主键的一部分。若表中有定义主键，且key分区中不包含任何一个列名，则表的主键列将会被用于key分区

五：分区注意事项

1. MySQL中的分区在禁止空值（NULL）上没有进行处理。在RANGE分区中，无论是插入一个列值为NULL或者表达式值为NULL的记录，都被当作是小于任何其他值，会默认被保存在从低到高排好序的第一个分区。在LIST分区中，如果所有分区LIST列表值里都没有NULL值，则插入含有NULL值的记录时会报错。在hash和key分区中NULL值则都当作0处理。
2. 一个表最多能有1024个分区，在5.7版本及以上可以有8196个分区
3. 常见的InnoDB 、 MyISAM引擎都支持分区

六：分区常用操作sql

1：删除分区并删除数据

alter table test(表名) drop partition p1(分区名);

2：删除分区的数据,保留分区

alter table test(表名) truncate partition p1(分区名);

3：移除整个表的分区,不删除数据

alert table test(表名) remove PARTITIONING ;