3、Range分区实战：基于时间范围的分区设计（如按年/月/日分区），MySQL与PostgreSQL实现对比。

时间范围分区，是我在实际项目中用得最多的分区策略。说白了，就是把数据按时间切块——1月的数据放一个区，2月的数据放另一个区。为什么这么流行？因为大部分业务数据都有时间属性，而且查询也经常带着时间范围。

我记得刚入行时接手过一个日志系统，单表2亿条记录，按时间查一个月的数据要跑40秒。后来改成按月分区，同样的查询降到0.5秒。嗯，这就是Range分区的魅力。

3.1 核心概念：Range分区到底在做什么？

Range分区的逻辑很简单：根据列值的范围，把数据分配到不同的物理分区。每个分区就是一个独立的存储单元，可以单独管理。

举个例子，你有一张订单表，按月份分区：

• 2024-01-01到2024-01-31的数据 → 分区p202401
• 2024-02-01到2024-02-29的数据 → 分区p202402
• 以此类推...

查询时，数据库会自动裁剪掉不相关的分区。你查1月份的数据，它只扫描p202401这一个分区，而不是全表。

3.2 MySQL实现：按年/月/日分区实战

MySQL从5.1开始支持分区。我个人习惯用RANGE COLUMNS，因为它支持更多数据类型，而且分区表达式更灵活。

3.2.1 按年分区

适合数据量增长稳定、查询跨度大的场景。比如历史归档表。

CREATE TABLE orders_yearly (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE COLUMNS(order_date) (
    PARTITION p2022 VALUES LESS THAN ('2023-01-01'),
    PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

这里有个坑——主键必须包含分区列。MySQL要求分区列必须是主键或唯一键的一部分。我曾经在这个问题上卡了半天，后来才反应过来。

3.2.2 按月分区

这是最常见的粒度。适合月活数据几百万到几千万的系统。

CREATE TABLE orders_monthly (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE COLUMNS(order_date) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    PARTITION p202403 VALUES LESS THAN ('2024-04-01'),
    PARTITION p202404 VALUES LESS THAN ('2024-05-01'),
    PARTITION p202405 VALUES LESS THAN ('2024-06-01'),
    PARTITION p202406 VALUES LESS THAN ('2024-07-01'),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

3.2.3 按日分区

日分区适合高频写入、查询粒度细的场景。比如实时日志、交易流水。

CREATE TABLE logs_daily (
    log_id BIGINT NOT NULL,
    log_time DATETIME NOT NULL,
    message TEXT,
    PRIMARY KEY (log_id, log_time)
)
PARTITION BY RANGE COLUMNS(log_time) (
    PARTITION p20240101 VALUES LESS THAN ('2024-01-02 00:00:00'),
    PARTITION p20240102 VALUES LESS THAN ('2024-01-03 00:00:00'),
    PARTITION p20240103 VALUES LESS THAN ('2024-01-04 00:00:00'),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

注意：日分区数量会快速增长。一年就是365个分区。MySQL官方建议分区数不超过8192个，但实际超过1000个时，DDL操作就会变慢。我个人建议日分区只保留最近30天，旧数据归档到按月分区的历史表。

3.3 PostgreSQL实现：声明式分区更优雅

PostgreSQL从10开始支持声明式分区。说实话，我第一次用的时候觉得比MySQL顺手多了——不需要操心主键约束，分区裁剪也更智能。

3.3.1 按年分区

CREATE TABLE orders_yearly (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_yearly_2022 PARTITION OF orders_yearly
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE orders_yearly_2023 PARTITION OF orders_yearly
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_yearly_2024 PARTITION OF orders_yearly
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

PostgreSQL的分区定义更直观——FROM ... TO ...，左闭右开。而且分区表本身不需要主键包含分区列，自由度更高。

3.3.2 按月分区

CREATE TABLE orders_monthly (
    order_id INT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_monthly_202401 PARTITION OF orders_monthly
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_monthly_202402 PARTITION OF orders_monthly
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE orders_monthly_202403 PARTITION OF orders_monthly
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

3.3.3 按日分区

CREATE TABLE logs_daily (
    log_id BIGINT NOT NULL,
    log_time TIMESTAMP NOT NULL,
    message TEXT
) PARTITION BY RANGE (log_time);

CREATE TABLE logs_daily_20240101 PARTITION OF logs_daily
    FOR VALUES FROM ('2024-01-01') TO ('2024-01-02');

CREATE TABLE logs_daily_20240102 PARTITION OF logs_daily
    FOR VALUES FROM ('2024-01-02') TO ('2024-01-03');

3.4 MySQL vs PostgreSQL：Range分区核心差异对比

对比维度	MySQL	PostgreSQL
分区语法	PARTITION BY RANGE COLUMNS	PARTITION BY RANGE
主键约束	分区列必须在主键/唯一键中	无此限制
分区定义	VALUES LESS THAN	FOR VALUES FROM ... TO ...
默认分区	支持MAXVALUE	不支持，需手动创建
分区裁剪	较成熟，但复杂表达式可能失效	更智能，支持多列分区裁剪
DDL灵活性	分区数多时DDL慢	分区数多时性能下降不明显
子分区	支持	支持（通过复合分区）

我个人更倾向PostgreSQL的分区设计，尤其是它的分区裁剪能力。但MySQL胜在生态成熟、运维工具多。选哪个，看你的团队技术栈。

3.5 避坑指南：我踩过的那些坑

做Range分区这么多年，我总结了几条血泪教训：

• 分区列不要用函数包裹：比如PARTITION BY RANGE (YEAR(order_date))，MySQL会失效分区裁剪。直接用原始列。
• 预留分区很重要：我曾经忘记给MySQL加MAXVALUE分区，结果数据插入时报错，线上事故。现在我的模板里永远有一个兜底分区。
• 分区不是越多越好：分区数超过1000后，MySQL的DDL操作会变得很慢。PostgreSQL稍好，但也不建议超过2000个。
• 注意时区问题：如果业务跨时区，分区列建议用UTC时间，避免夏令时导致数据错乱。

3.6 一张图看懂Range分区流程

下面这张图展示了Range分区的核心流程：数据写入时如何路由到正确的分区，查询时如何裁剪不相关的分区。

流程很简单：数据写入时，数据库根据分区列的值判断应该进入哪个分区；查询时，数据库自动裁剪掉不匹配的分区，只扫描相关分区。这就是Range分区性能提升的核心原理。

3.7 小结

Range分区是时间序列数据的首选方案。MySQL和PostgreSQL的实现各有千秋——MySQL胜在生态成熟，PostgreSQL胜在灵活智能。我个人建议：如果团队以MySQL为主，就用MySQL的分区；如果追求更优雅的架构，PostgreSQL是更好的选择。