MySQL 表分区和事件调度器

MySQL 表分区

MySQL表分区是将一个大表分割成若干个小表的技术。这样做可以提高查询和维护的效率，并且还可以改善数据的管理方式。

注意：如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来（即分区的字段也必须是主键或者唯一索引）

分区适用场景

大型表处理
时间范围查询
数据归档和数据保留
并行查询和负载均衡
数据删除和维护

判断当前MySQL是否支持分区？

1

show variables like '%partition%';

以下是几种常见的分区类型：

RANGE分区：基于给定的一个给定列的值，将数据划分到不同的分区。
LIST分区：类似于RANGE分区，但是列值是离散的集合。
HASH分区：基于用户定义的表达式的返回值进行分区，该表达式对分区的数量进行哈希。
KEY分区：类似于HASH分区，但是使用的哈希函数是由MySQL服务器提供的。
COLUMNS分区：基于列值的范围或列表来进行分区，适用于某些特定的数据类型。

RANGE分区

以下是一个RANGE分区的示例：

假设有一个orders表，包含一个order_date字段，可以根据order_date字段的值将表分为几个分区：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


CREATE TABLE orders (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    order_amount DECIMAL(10, 2) NOT NULL
) 
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2023),
    PARTITION p1 VALUES LESS THAN (2024),
    PARTITION p2 VALUES LESS THAN (2025),
    PARTITION p3 VALUES LESS THAN (2026),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

创建了一个按年份划分的orders表，将订单按照它们的年份放入不同的分区。p0分区包含order_date在2023年以前的所有订单，p1分区包含2024年，以此类推。pmax分区包含所有2026年以后的订单。

LIST分区

LIST 分区是根据某一列的离散值将数据分布到不同的分区。

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50)
)
PARTITION BY LIST (region) (
 PARTITION p_east VALUES IN ('New York', 'Boston'), 
 PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco'), 
 PARTITION p_other VALUES IN (DEFAULT)
);

HASH分区

HASH 分区是使用哈希算法将数据均匀地分布到多个分区中。

示例：

1
2
3
4
5
6


CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50)
)
PARTITION BY HASH (id) PARTITIONS 4;

PARTITIONS 4 指定4个分区

KEY分区

KEY 分区是根据某一列的哈希值将数据分布到不同的分区。和HASH分区不同在于使用的哈希算法不同。

示例：

1
2
3
4
5
6


CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50)
)
PARTITION BY KEY (id) PARTITIONS 4;

PARTITIONS 4 指定4个分区

COLUMNS 分区

包括 RANGE COLUMNS 分区和 LIST COLUMNS 分区

RANGE COLUMNS 分区是根据列的范围值将数据分布到不同的分区的分区策略。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50),
 birthday DATE NOT NULL
)
PARTITION BY LIST COLUMNS (region, YEAR(category)) (
 PARTITION p_east VALUES LESS THAN (('New York', 2022), ('Boston', 2022)), 
 PARTITION p_west VALUES LESS THAN (('Los Angeles', 2022), ('San Francisco', 2022)), 
 PARTITION p_east_other VALUES LESS THAN (('New York', MAXVALUE)),
 PARTITION p_west_other VALUES LESS THAN (('Los Angeles', MAXVALUE))
);

LIST COLUMNS 分区是根据列的离散值将数据分布到不同的分区的分区策略。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


CREATE TABLE users (
 id INT,
 username VARCHAR(50),
 region VARCHAR(50),
 category VARCHAR(50)
)
PARTITION BY LIST COLUMNS (region, category) (
 PARTITION p_east VALUES IN (('New York', 'A'), ('Boston', 'B')), 
 PARTITION p_west VALUES IN (('Los Angeles', 'C'), ('San Francisco', 'D')), 
 PARTITION p_other VALUES IN (DEFAULT)
);

添加分区

1
2


alter table table_name add partition (partition new_part values less than (4000)); -- range 分区
alter table table_name add partition (partition new_part values in (40));  -- lists分区

删除分区

1

alter table table_name drop partition part_name;

drop方式删除会删除分区数据

1

alter table table_name remove partitioning;

remove不会删除数据

重新分区

range分区

1
2
3
4


alter table table_name partition by range(id)(
partition p1 values less than (2000),
partition p2 values less than (4000)
); 

hash分区

1

alter table table_name partition by hash(id) partitions 7;

合并分区

合并相邻两个分区

1

ALTER TABLE table_name COALESCE PARTITION p1, p2;

合并两个分区

1

alter table table_name reorganize partition p1,p3 into (partition p1 values less than (1000));

重组分区

1
2
3
4
5


alter table table_name
reorganize partition p1 into(
partition p1 values less than (100),
partition p3 values less than (1000)
);

查询分区分析

1

explain partitions select ...

分区统计信息

1

ALTER TABLE users ANALYZE PARTITION p1;

表分区操作实践

方便学习，直接在 Docker 中运行 MySQL 容器。

拉取MySQL镜像

1

docker pull mysql:8.0.35

运行 MySQL 容器

1

docker run --name mysql-partition-test -e MYSQL_ROOT_PASSWORD=a123456 -d mysql:8.0.35

--name mysql-partition-test：为当前的容器指定运行中的一个名称，这里命名为 mysql-partition-test。
-e MYSQL_ROOT_PASSWORD=a123456：设置 root 用户的密码。这里设置为a123456。
-d：在后台运行容器。
mysql:8.0.35：指定要使用的 MySQL 镜像的标签tag=8.0.35版本

如果想要自定义配置或挂载本地数据卷，可以添加如下启动配置项

-v /my/own/datadir:/var/lib/mysql 挂载数据卷,持久化存储数据,挂载一个卷到容器的 /var/lib/mysql 目录
-v /my/custom/conf:/etc/mysql/conf.d 可以通过挂载一个包含自定义 my.cnf 的卷来覆盖默认的 MySQL 配置

查看容器运行结果

1
2
3
4
5
6


~ docker run --name mysql-partition-test -e MYSQL_ROOT_PASSWORD=a123456 -d mysql:8.0.35
629ad47a2e0c31655163fdfcf7c88307729b661a762e50e00a227c9e39670566
~ docker ps
CONTAINER ID   IMAGE                        COMMAND                  CREATED          STATUS          PORTS                 NAMES
629ad47a2e0c   mysql:8.0.35                 "docker-entrypoint.s…"   4 seconds ago    Up 4 seconds    3306/tcp, 33060/tcp   mysql-partition-test

访问 MySQL 容器，并连接数据库

1

docker exec -it mysql-partition-test mysql -u root -p

连接和操作数据库

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27


~ docker exec -it mysql-partition-test mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

mysql>

创建一个订单表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


CREATE TABLE `orders` (
     `id` bigint unsigned AUTO_INCREMENT,
     `created_at` datetime(3) NULL,
     `updated_at` datetime(3) NULL,
     `order_no` varchar(30) NOT NULL,
     `uid` bigint NOT NULL,
     `amount` DECIMAL(13,2) NOT NULL DEFAULT 0,
     `status` tinyint NOT NULL DEFAULT 0,
     PRIMARY KEY (`id`),
     INDEX `idx_orders_created_at` (`created_at`),
     INDEX `idx_orders_uid` (`uid`),
     CONSTRAINT `uni_orders_order_no` UNIQUE (`order_no`)
);

查看数据库表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| orders         |
+----------------+
1 row in set (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

创建分区表

以订单表为例，用创建时间created_at按范围分区（RANGE），

如果按年分区直接使用 RANGE(YEAR(created_at))

按月分区RANGE(YEAR(created_at)*100+MONTH(created_at))

按天分区RANGE(TO_DAYS(created_at))

下面是按月分区：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


CREATE TABLE `orders` (
     `id` bigint unsigned AUTO_INCREMENT,
     `created_at` datetime(3) NULL,
     `updated_at` datetime(3) NULL,
     `order_no` varchar(30) NOT NULL,
     `uid` bigint NOT NULL,
     `amount` DECIMAL(13,2) NOT NULL DEFAULT 0,
     `status` tinyint NOT NULL DEFAULT 0,
     PRIMARY KEY (`id`),
     INDEX `idx_orders_created_at` (`created_at`),
     INDEX `idx_orders_uid` (`uid`),
     CONSTRAINT `uni_orders_order_no` UNIQUE (`order_no`)
)
PARTITION BY RANGE (YEAR(created_at)*100+MONTH(created_at)) (
    PARTITION p202505 VALUES LESS THAN (202505),
    PARTITION p202506 VALUES LESS THAN (202506),
    PARTITION p202507 VALUES LESS THAN (202507),
    PARTITION p202508 VALUES LESS THAN (202508),
    PARTITION p2026 VALUES LESS THAN MAXVALUE
);

执行创建时失败，提示分区键选择不正确，原因是分区键必须是主键和唯一索引的一部分，否则就无法确定数据存储在哪个分区。

1

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

MySQL要求：

分区表达式中使用的所有列必须是表可能使用的每个唯一键的一部分
这意味着如果表有主键或唯一索引，这些键必须包含分区列

修改索引如下，主键和唯一索引添加created_at字段：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24


CREATE TABLE `orders` (
     `id` bigint unsigned AUTO_INCREMENT,
     `created_at` datetime(3) NOT NULL,
     `updated_at` datetime(3) NULL,
     `order_no` varchar(30) NOT NULL,
     `uid` bigint NOT NULL,
     `amount` DECIMAL(13,2) NOT NULL DEFAULT 0,
     `status` tinyint NOT NULL DEFAULT 0,
     PRIMARY KEY (`id`, `created_at`),
     INDEX `idx_orders_created_at` (`created_at`),
     INDEX `idx_orders_uid` (`uid`),
     CONSTRAINT `uni_orders_order_no` UNIQUE (`order_no`, `created_at`)
)
PARTITION BY RANGE (YEAR(created_at)*100+MONTH(created_at)) (
    PARTITION p202505 VALUES LESS THAN (202506),
    PARTITION p202506 VALUES LESS THAN (202507),
    PARTITION p202507 VALUES LESS THAN (202508),
    PARTITION p202508 VALUES LESS THAN (202509),
    PARTITION p202509 VALUES LESS THAN (202510),
    PARTITION p202510 VALUES LESS THAN (202511),
    PARTITION p202511 VALUES LESS THAN (202512),
    PARTITION p202512 VALUES LESS THAN (202601),
    PARTITION p2026 VALUES LESS THAN MAXVALUE
);

注意事项:

查询性能：尽可能在查询条件中包含分区键(created_at)以实现分区裁剪

写入数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


INSERT INTO `orders` (`created_at`,`updated_at`,`order_no`,`uid`,`amount`,`status`) VALUES
(STR_TO_DATE('2025-05-01 15:30:00', '%Y-%m-%d %H:%i:%s'),now(),'1',1,100.00,0),
(STR_TO_DATE('2025-06-01 15:30:00', '%Y-%m-%d %H:%i:%s'),now(),'2',2,200.00,0),
(STR_TO_DATE('2025-07-01 15:30:00', '%Y-%m-%d %H:%i:%s'),now(),'3',3,300.00,0),
(STR_TO_DATE('2025-08-01 15:30:00', '%Y-%m-%d %H:%i:%s'),now(),'3',3,300.00,0),
(STR_TO_DATE('2025-09-01 15:30:00', '%Y-%m-%d %H:%i:%s'),now(),'3',3,300.00,0),
(STR_TO_DATE('2025-10-01 15:30:00', '%Y-%m-%d %H:%i:%s'),now(),'3',3,300.00,0),
(STR_TO_DATE('2025-11-01 15:30:00', '%Y-%m-%d %H:%i:%s'),now(),'3',3,300.00,0),
(STR_TO_DATE('2025-12-01 15:30:00', '%Y-%m-%d %H:%i:%s'),now(),'3',3,300.00,0),
(STR_TO_DATE('2026-01-01 15:30:00', '%Y-%m-%d %H:%i:%s'),now(),'3',3,300.00,0);

查询分区数据

1
2
3
4
5
6
7
8


mysql> SELECT * FROM orders PARTITION (p202505);
+----+-------------------------+-------------------------+----------+-----+--------+--------+
| id | created_at              | updated_at              | order_no | uid | amount | status |
+----+-------------------------+-------------------------+----------+-----+--------+--------+
|  1 | 2025-05-01 15:30:00.000 | 2025-05-24 14:33:07.000 | 1        |   1 | 100.00 |      0 |
+----+-------------------------+-------------------------+----------+-----+--------+--------+
1 row in set (0.00 sec)

复合分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


CREATE TABLE `orders` (
     `id` bigint unsigned AUTO_INCREMENT,
     `created_at` datetime(3) NOT NULL,
     `updated_at` datetime(3) NULL,
     `order_no` varchar(30) NOT NULL,
     `uid` bigint NOT NULL,
     `amount` DECIMAL(13,2) NOT NULL DEFAULT 0,
     `status` tinyint NOT NULL DEFAULT 0,
     PRIMARY KEY (`id`,`order_no`),
     INDEX `idx_orders_created_at` (`created_at`),
     INDEX `idx_orders_uid` (`uid`),
     CONSTRAINT `uni_orders_order_no` UNIQUE (`order_no`,`id`)
)
PARTITION BY RANGE (id)
SUBPARTITION BY KEY(order_no)
SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (1000000),
    PARTITION p1 VALUES LESS THAN (2000000),
    PARTITION p VALUES LESS THAN (MAXVALUE)
);

先按id分3个RANGE分区，每个RANGE分区中两个KEY(order_no)子分区

自动维护分区

对于动态数据，可能需要定期添加新的分区来覆盖新月份的数据。

可以写一个脚本或使用事件调度器（Event Scheduler）来自动添加新的分区。

例如，使用事件调度器每月自动添加下个月新分区的步骤如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


DELIMITER //
CREATE EVENT add_monthly_partition
ON SCHEDULE EVERY 1 MONTH
STARTS '2025-05-01 00:00:00' -- 从5月开始执行，确保不会在已存在的月份上重复添加分区
DO
BEGIN
    DECLARE next_month INT;
    SELECT YEAR(CURDATE()) * 100 + MONTH(CURDATE() + INTERVAL 1 MONTH) INTO next_month;
    SET @sql = CONCAT('ALTER TABLE orders ADD PARTITION (PARTITION p', next_month, ' VALUES LESS THAN (', next_month, '))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

查看事件调度器的状态

1

SHOW VARIABLES LIKE 'event_scheduler';

开启事件调度器

1

SET GLOBAL event_scheduler = ON;

创建和测试事件

1
2
3
4
5
6


CREATE EVENT `e_insert_partition`
ON SCHEDULE EVERY 2 SECOND
STARTS (TIMESTAMP(CURRENT_DATE, '00:00:00'))
DO
  INSERT INTO `orders` (`created_at`,`updated_at`,`order_no`,`uid`,`amount`,`status`) VALUES
    (now(),now(),'1',1,100.00,0);

这个事件每2秒执行一次insert写入一条数据，执行后事件自动开始运行。

查看所有event

1
2
3
4
5
6
7
8


mysql> show events;
+------+-----------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
| Db   | Name                  | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+------+-----------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
| test | add_monthly_partition | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | MONTH          | 2025-05-01 00:00:00 | NULL | ENABLED  |          1 | latin1               | latin1_swedish_ci    | utf8mb4_0900_ai_ci |
| test | myevent               | root@localhost | SYSTEM    | RECURRING | NULL       | 2              | SECOND         | 2025-05-24 00:00:00 | NULL | DISABLED |          1 | latin1               | latin1_swedish_ci    | utf8mb4_0900_ai_ci |
+------+-----------------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
2 rows in set (0.01 sec)

事件开启和关闭

1
2
3


ALTER EVENT `e_insert_partition` ENABLE;

ALTER EVENT `e_insert_partition` DISABLE;

删除事件

1

DROP EVENT IF EXISTS `e_insert_partition`;