MySQL 表按年月分区实现方法

在 MySQL 中，按照日期字段的年月进行分区可以提高查询性能，特别是对于大型数据表。以下是实现按月分区的详细方法：

基本分区语法

CREATE TABLE partitioned_table (
    id INT NOT NULL,
    created_at DATETIME NOT NULL,
    data VARCHAR(100)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);
CREATE TABLE partitioned_table (
    id INT NOT NULL,
    created_at DATETIME NOT NULL,
    data VARCHAR(100)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);
CREATE TABLE partitioned_table (
    id INT NOT NULL,
    created_at DATETIME NOT NULL,
    data VARCHAR(100)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

更优雅的实现方式（MySQL 5.7+）

MySQL 5.7及以上版本支持更简洁的日期分区方式：

CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date)*100 + MONTH(sale_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date)*100 + MONTH(sale_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);
CREATE TABLE sales (
    id INT NOT NULL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
)
PARTITION BY RANGE (YEAR(sale_date)*100 + MONTH(sale_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    PARTITION p202303 VALUES LESS THAN (202304),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

自动按月分区（推荐）

对于需要持续增长的表，可以设置自动按月分区：

CREATE TABLE log_data (
    id INT NOT NULL AUTO_INCREMENT,
    log_time DATETIME NOT NULL,
    message TEXT,
    PRIMARY KEY (id, log_time)
)
PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
CREATE TABLE log_data (
    id INT NOT NULL AUTO_INCREMENT,
    log_time DATETIME NOT NULL,
    message TEXT,
    PRIMARY KEY (id, log_time)
)
PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
CREATE TABLE log_data (
    id INT NOT NULL AUTO_INCREMENT,
    log_time DATETIME NOT NULL,
    message TEXT,
    PRIMARY KEY (id, log_time)
)
PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

添加新分区

当需要添加新月份分区时：

ALTER TABLE log_data REORGANIZE PARTITION p_future INTO (
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
ALTER TABLE log_data REORGANIZE PARTITION p_future INTO (
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);
ALTER TABLE log_data REORGANIZE PARTITION p_future INTO (
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

注意事项

1. 分区字段必须是主键或唯一索引的一部分
2. 分区数量有限制（MySQL 5.6+默认限制为8192个分区）
3. 查询时尽量使用分区键作为条件以获得性能提升
4. 考虑使用事件定期添加新分区

查看分区信息

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'log_data';
SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'log_data';
SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME = 'log_data';

通过合理设置按月分区，可以显著提高大型时间序列数据的查询性能和管理效率。