MySQL中对数据库表按月进行水平分区

要在 MySQL 5.5 中对数据库 shunsync 的表 sl_history_data 按月进行水平分区，基于类型为 DATE 的字段 time，你可以按照以下步骤来完成设置。
1. 进入数据库

首先，进入 shunsync 数据库：

sql

USE shunsync;

2. 创建分区表

假设表 sl_history_data 还未创建，我们可以创建表时通过 PARTITION BY RANGE 来按月份对 time 字段进行分区。具体 SQL 语句如下：

sql

CREATE TABLE sl_history_data (
id INT NOT NULL,
time DATE NOT NULL,
data VARCHAR(255),
PRIMARY KEY (id, time)
)
PARTITION BY RANGE (YEAR(time) * 100 + MONTH(time)) (
PARTITION p202301 VALUES LESS THAN (202302),
PARTITION p202302 VALUES LESS THAN (202303),
PARTITION p202303 VALUES LESS THAN (202304),
PARTITION p202304 VALUES LESS THAN (202305),
PARTITION p202305 VALUES LESS THAN (202306),
PARTITION p202306 VALUES LESS THAN (202307),
PARTITION p202307 VALUES LESS THAN (202308),
PARTITION p202308 VALUES LESS THAN (202309),
PARTITION p202309 VALUES LESS THAN (202310),
PARTITION p202310 VALUES LESS THAN (202311),
PARTITION p202311 VALUES LESS THAN (202312),
PARTITION p202312 VALUES LESS THAN (202401)
);

解释：

PARTITION BY RANGE：使用 YEAR(time) * 100 + MONTH(time) 公式将日期转化为整数值，以便按范围进行分区。
PARTITION p202301 等分区：每个分区存储一整个月的数据，比如 p202301 存储 2023 年 1 月的数据，VALUES LESS THAN (202302) 表示截止到 2023 年 2 月 1 日之前的数据都放入 p202301 分区中。
PRIMARY KEY：主键包括 id 和 time，因为分区表要求主键或唯一键必须包含分区字段。

3. 如果表已存在，需要修改表结构

如果表 sl_history_data 已经存在，可以使用 ALTER TABLE 命令将其修改为分区表。首先，需要确保表没有现存的外键约束，然后对表进行分区：

sql

ALTER TABLE sl_history_data
PARTITION BY RANGE (YEAR(time) * 100 + MONTH(time)) (
PARTITION p202301 VALUES LESS THAN (202302),
PARTITION p202302 VALUES LESS THAN (202303),
PARTITION p202303 VALUES LESS THAN (202304),
PARTITION p202304 VALUES LESS THAN (202305),
PARTITION p202305 VALUES LESS THAN (202306),
PARTITION p202306 VALUES LESS THAN (202307),
PARTITION p202307 VALUES LESS THAN (202308),
PARTITION p202308 VALUES LESS THAN (202309),
PARTITION p202309 VALUES LESS THAN (202310),
PARTITION p202310 VALUES LESS THAN (202311),
PARTITION p202311 VALUES LESS THAN (202312),
PARTITION p202312 VALUES LESS THAN (202401)
);

4. 插入数据

当你插入数据时，MySQL 会根据 time 字段的值自动将数据放入对应的分区。例如：

sql

INSERT INTO sl_history_data (uid, time, attr, value)
VALUES ('653b0d8cb23ec96d1146dea8', '2024-10-15 1:01:59', 'noise', '88.888888'),
   ('653b0d8cb23ec96d1146dea8', '2024-10-15 3:08:59', 'pm2_5', '66.888888'),
   ('653b0d8cb23ec96d1146dea8', '2024-10-15 5:02:59', 'precipitation', '99.888888');

INSERT INTO sl_history_data (time, attr, value)
VALUES ('2024-10-15 1:01:59', 'noise', '88.888888'),
   ('2024-10-15 3:08:59', 'pm2_5', '66.888888'),
   ('2024-10-15 5:02:59', 'precipitation', '99.888888');

DELETE FROM sl_history_data
WHERE time BETWEEN '2024-10-14 00:00:00' AND '2024-10-15 23:59:59';

5. 查询分区数据

查询时，MySQL 只会扫描相关分区的数据，查询效率更高。例如：

sql

SELECT * FROM sl_history_data WHERE time BETWEEN '2023-02-01' AND '2023-02-28';

这个查询只会访问 p202302 分区，忽略其他分区，从而提升性能。
6. 动态管理分区

select TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,TABLE_ROWS from information_schema.partitions where table_name='sl_history_data';

在 MySQL 中，要查看现有分区的详细信息，包括 VALUES LESS THAN 后面的值，可以通过 SHOW CREATE TABLE 命令来查看表的分区定义。这个命令将展示表的创建语句，包括分区的详细信息。

SHOW CREATE TABLE sl_history_data;

每当进入新的月份，可以使用 ALTER TABLE 命令动态添加新的分区。例如，添加 2024 年 1 月的分区：

sql

SHOW CREATE TABLE sl_history_data;

ALTER TABLE sl_history_data
ADD PARTITION (
PARTITION p108 VALUES LESS THAN ('2026-01-01') ENGINE = InnoDB
);

同样，如果需要删除旧分区（比如删除 2023 年 1 月的数据），可以执行：

ALTER TABLE sl_history_data
DROP PARTITION p202301;

总结

MySQL 5.5 支持基于 DATE 字段的范围分区，通过 YEAR() 和 MONTH() 函数可以实现按月分区的功能。
动态添加或删除分区可以方便地管理不断变化的数据量。

MySQL中对数据库表按月进行水平分区

No Comments