MySql分区简单说明

优化数据库：分区策略与实战指南

原创已于 2025-03-19 19:05:53 修改 · 1.4k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#大数据

于 2022-09-14 15:27:04 首次发布

笔记同时被 2 个专栏收录

106 篇文章

订阅专栏

Mysql

17 篇文章

订阅专栏

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

常见的分区有 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会报错：