17370845950

时间序列数据优先选RANGE分区，因其天然适配时间连续性，支持按月/天自动切分且可覆盖未来时间点；LIST需显式枚举值，维护成本高、不适用时间维度。

为什么时间序列数据优先选 RANGE 分区而不是 LIST

RANGE 分区天然适配时间序列的连续性特征，比如按月、按天切分 created_at 字段；而 LIST 分区要求显式枚举每个分区值（如 '2025-01', '2025-02'），无法自动覆盖未来时间点，维护成本高。MySQL 8.0+ 和 PostgreSQL 的分区机制都对 RANGE 时间分区有原生优化，LIST 在时间维度上基本不适用。

MySQL 中创建 RANGE 分区的最小可用模板

注意：必须使用 DATE、DATETIME 或 TIMESTAMP 类型字段，且该字段需为分区键（不能是表达式）；VALUES LESS THAN 必须严格递增，且最后一个分区建议用

CREATE TABLE logs (
  id BIGINT NOT NULL,
  created_at DATETIME NOT NULL,
  content TEXT
) PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')),
  PARTITION p202503 VALUES LESS THAN (TO_DAYS('2025-04-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

• TO_DAYS() 是 MySQL 常用转换函数，避免直接用 DATETIME 值导致语法错误
• 不要用 YEAR(created_at) 或 MONTH(created_at)，它们不单调，会导致分区重叠或空洞
• 新增分区需用 ALTER TABLE ... ADD PARTITION，不能靠插入自动触发

PostgreSQL 中按时间 RANGE 分区的声明式写法

PostgreSQL 10+ 支持声明式分区，语法更直观，但必须先建主表并指定 PARTITION BY RANGE，再逐个创建子分区；子分区的 FOR VALUES FROM 和 TO 是左闭右开区间，且不能有间隙或重叠。

CREATE TABLE logs (
  id BIGSERIAL,
  created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  content TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_202501 PARTITION OF logs
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE logs_202502 PARTITION OF logs
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE logs_default PARTITION OF logs
  DEFAULT;

• 子分区名无强制规范，但建议含时间标识，便于运维识别
• DEFAULT 分区可兜底未匹配的数据，但会削弱查询剪枝效果，慎用
• 若需定期添加新分区，得配合脚本调用 CREATE TABLE ... PARTITION OF，PG 不支持自动滚动

容易被忽略的三个硬约束

无论 MySQL 还是 PostgreSQL，以下限制一旦违反，分区就失效或报错：

• 分区键字段不能为 NULL —— 时间字段缺失会导致插入失败或落入 DEFAULT 分区，破坏时序局部性
• 查询条件中若未包含分区键（如只查 id = 123），优化器无法剪枝，全分区扫描性能反降
• MySQL 中 TO_DAYS() 对 NULL 返回 NULL，而 NULL 永远不满足任何 LESS THAN 条件，这类记录会被丢弃（不是存入 MAXVALUE 分区）

时间分区不是设完就一劳永逸的事，关键在持续管理分区边界和确保写入数据的时间字段始终有效。