MySql分区简单说明

数据库分区，是数据库管理系统 提供的 一个 比较好的功能。当数据量大时，除了 分表，还可以对一个表进行分区。这样，就可以 再一个分区内 操作数据，提高性能。

常见的分区有 range、hash，一般情况下，range分区比较普遍。

1. 非分区表操作：

注意的是，表分区，一定是在 创建表的时候 进行分区，后面才可以增加分区、删除分区。也就是说，分区操作的前提是 表 是一个 分区表。

比如：如果不是分区表，但是想增加分区时，会报错：

 1505 - Partition management on a not partitioned table is not possible

这种情况，如果 必须创建分区的话，可以执行下面的命令，将非分区表转为分区表：

alter table table_name PARTITION by range columns(`id`)
(
PARTITION  p1 values less than (5),
PARTITION  p2 values less than (10),
PARTITION  p3 values less than (30),
PARTITION  p4 values less than (40))
 

2. 分区表操作：

创建分区表的时候，分区字段必须是：

1. 主键

2. 非空

创建非分区表：

CREATE TABLE `t_1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

创建分区表： 

注意，在创建语句后面的 分号 ; 要去掉，不然 后面的分区语句就是单独的命令了。

CREATE TABLE `t_1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

 PARTITION by range columns(`create_time`)
(
PARTITION  p1 values less than ('2022-04-01 00:00:00'),
PARTITION  p2 values less than ('2022-07-01 00:00:00'),
PARTITION  p3 values less than ('2022-10-01 00:00:00'),
PARTITION  p4 values less than ('2023-01-01 00:00:00'))

在分区表上 新增分区：

alter table t_1 add PARTITION 
(
PARTITION  p5 values less than ('2023-04-01 00:00:00'),
PARTITION  p6 values less than ('2023-07-01 00:00:00'),
PARTITION  p7 values less than ('2023-10-01 00:00:00'),
PARTITION  p8 values less than ('2024-01-01 00:00:00')) 

在分区表上 删除分区：

alter table t_1 drop partition p8; 

分区维护 似乎比较麻烦，需要手动处理。可以设置定时任务 来完成 分区表的维护。

查询分区表 ：

如果 没有加分区，则是全分区扫描：

 可以在查询语句中加上分区号，则只在 指定 分区中 扫描

select * from t_1  partition(p1) where id=1

3. 分区特殊操作

3.1 maxvalue

创建分区时，设置一个默认分区，当数据不在其他分区内时，就会落在这个默认分区内。然后再对该默认分区进行 重新组织

alter table z_student_p PARTITION by range columns(`id`)
(
PARTITION  p1 values less than (5),
PARTITION  p2 values less than (10),
PARTITION  p3 values less than (30),
PARTITION  p4 values less than (40),
PARTITION  pmax values less than maxvalue)

设置分区时，创建了一个名为 pmax 的分区，该分区包含了 小于 maxvalue 的所有数据。对于pmax分区，就把他当成一个普通的分区即可，和 p1/p2/p3/p4 这种分区没有区别，唯一不同的是，pmax分区 数据的范围更大而已。也可以对pmax分区进行删除等操作。

3.2 REORGANIZE 

对 pmax 分区重新分配

alter table z_student_p  REORGANIZE  PARTITION pmax into 
 (
   PARTITION  p5 values less than (90),
   PARTITION  p6 values less than (100),
   PARTITION  pmax values less than maxvalue
 )

注意的是，重新分配时，仍然需要再加上 pmax 分区才行。

4. 分区字段

一般使用 日期字段分区，mysql 日期字段有4种：

date,datetime,time,timestamp

一般使用 datetime或者 date做分区字段。

如果是time,timestamp会报错：

 Field 'create_time' is of a not allowed type for this type of partitioning

5. 非分区表 改造为 分区表

第一种情况，如果表数据量比较小，直接在原表上改

第二种情况，如果表数据量比较打，直接改为分区表，锁表时间长，影响到业务的话，可以用一个新的分区表同步，同步之后在rename

首先： 创建分区表 t_test_partition

然后： 同步历史数据

insert into t_test_partition (所有字段)  select 所有字段 from t_test where create_time 条件

因为数据量大，可以分批同步；每个批次200w的数据量即可

需要注意的是，可以先将历史数据，比如 当前时间 几个小时之前创建的数据，用create_time作为条件先同步好

最后：重命名表

rename table t_test to t_test_old

rename table t_test_partition to t_test

replace into  t_test (所有字段) select  所有字段 where update_time > ''

 注意：同步历史数据 用create_time，rename 之后 用 update_time

insert into 和 replace into的区别：

insert into 如果遇到主键冲突，会停止执行，可以使用 ON DUPLICATE KEY UPDATE column=VALUES(column) 执行 更新 指定字段

replace into ，如果遇到主键冲突，会根据主键 先删除 原数据，再插入新数据

分区注意事项

1. 如果 创建分区的时候，没有 

PARTITION pmax VALUES LESS THAN MAXVALUE

当插入的数据 不在分区范围内时，会报错：1526 - Table has no partition for value from column_list

要不要加这个最大分区？ 如果 加了最大分区，但是 有些数据 不合理 就会分配到 最大分区内。

2. 如果 表 没有设置主键，也没有设置唯一索引，分区字段 没有特殊要求

3. 如果 表设置的主键 PRIMARY KEY 或者 设置了唯一索引 UNIQUE KEY

那么，主键 或者 唯一索引 必须包含 分区字段，否则会报：

1503 - A PRIMARY KEY must include all columns in the table's partitioning function

为什么？

如果 两条数据 ，根据分区字段 分在了不同的分区，那么 主键 可能会相同，违反了唯一性约束

对于 MySQL 分区表，无法从数据库层面保证非分区列在表级别的唯一性，只能确保其在分区内的唯一性。

所以，需要在主键中包含分区字段