MySQL 分区表怎样选择分区键

我第一次给日志表做分区时，直觉是选一列“变化比较均匀”的 ID。后来发现，真正决定分区键是否合适的不是字段看起来漂不漂亮，而是线上查询能不能缩小扫描范围、旧数据能不能按边界维护，以及主键和唯一键是否允许这张表这样分区。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/partitioning.html

这篇文章把选择过程收敛成四个问题：查询是否会按候选列过滤，数据是否有明确生命周期，分区表达式是否能和键约束共存，以及后续新增、删除分区是否简单。先给结论：有明确时间范围查询和按时间归档需求时，优先看 RANGE 或 RANGE COLUMNS；按少量稳定业务域隔离时看 LIST；只有在均匀分散比可维护性更重要时，才把 HASH 或 KEY 放进候选。

先看查询条件能不能带来分区裁剪

分区键的第一职责是让优化器有机会跳过不相关分区。MySQL 官方把这种优化称为 partition pruning：当 WHERE 条件能约束分区表达式时，优化器只检查可能包含结果的分区，而不是把所有分区都扫一遍。

因此，不要从“哪个字段基数高”开始，而要先抄出真实 SQL 里的过滤条件。比如订单表经常按 created_at 查一个月，或者按 tenant_id 查一个租户，这些条件才是候选分区键的入口。下面这个例子表达的是结构关系，注释说明每个选择点，不能代替线上执行结果。

-- 用时间范围表达最常见的查询边界，让优化器有机会排除月份之外的分区
SELECT order_id, tenant_id, total_amount
FROM orders
WHERE created_at >= '2026-09-01'
  AND created_at 

如果业务主要按 order_id 精确查，但很少按它做范围管理，用自增 ID 分区可能只得到分散数据的效果，却帮不上按月归档。相反，时间字段可能在查询、删除旧数据和冷热分层里同时出现，通常更值得优先评估。

按数据生命周期比较 RANGE、LIST、HASH 和 KEY

第二个问题是“数据以后怎么被管理”。分区类型不是语法偏好，而是维护动作的投影。

RANGE COLUMNS 和 LIST COLUMNS 可以直接使用多个列，并让这些列共同参与行归属和裁剪判断。MySQL 8.4 文档列出的可用列类型包含整数、DATE、DATETIME 以及部分字符串类型；TEXT 和 BLOB 不能作为分区列。

-- 按月划分时，边界清楚，后续可以围绕月份做维护规划
CREATE TABLE orders_by_month (
    order_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    tenant_id BIGINT NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    PRIMARY KEY (order_id, created_at)
) PARTITION BY RANGE COLUMNS (created_at) (
    PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
    PARTITION p202610 VALUES LESS THAN ('2026-11-01'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- 离散集合才适合 LIST COLUMNS；值域变动时要把维护动作纳入设计
CREATE TABLE tenant_orders (
    order_id BIGINT NOT NULL,
    tenant_id INT NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (order_id, tenant_id)
) PARTITION BY LIST COLUMNS (tenant_id) (
    PARTITION p_core VALUES IN (1, 2, 3),
    PARTITION p_other VALUES IN (4, 5, 6)
);

我的经验是：如果“删除三个月前数据”是明确的运维动作，时间分区往往比单纯追求写入均匀更实用；如果没有可解释的范围和维护边界，就不要为了分区而分区。

建表前先检查主键和唯一键

这是最容易后知后觉的一关。MySQL 8.4 的规则是：分区表达式使用的每一列，都必须出现在表上每一个唯一键中，主键也属于唯一键。也就是说，候选分区列并不是“想用就用”，它会反过来约束现有键设计。

-- created_at 出现在分区表达式中，因此主键必须包含它
CREATE TABLE events (
    id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    payload JSON NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE COLUMNS (created_at) (
    PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

-- 这个写法会破坏分区键与主键的共存关系：主键没有覆盖 created_at
-- ALTER TABLE events ADD PRIMARY KEY (id);

如果现有表只有 PRIMARY KEY(id)，却打算按 created_at 分区，先别急着执行 ALTER TABLE ... PARTITION BY。你需要重新评估复合主键、唯一约束的业务含义，以及应用是否依赖单列主键；为了通过 DDL 机械地把列塞进唯一键，可能会改变索引宽度和写入成本。

把维护动作和字段类型放进最后的筛选

最后我会把候选键放进一张“以后真的要做什么”的清单：是否需要定期增加未来分区，是否需要删除或归档旧分区，是否能接受跨分区查询，是否有 MAXVALUE 兜底，以及故障恢复时谁负责维护边界。对 RANGE 方案来说，分区范围必须覆盖新增数据；对 LIST 方案来说，新增业务值需要有归属；对 HASH/KEY 方案来说，分区边界不适合承载时间淘汰语义。

类型也要一起检查。日期分区通常可以用 RANGE COLUMNS (created_at)，或使用文档支持的日期函数表达式；KEY 能处理更多列类型，但这不等于它天然适合时间归档。另一个边界是存储引擎：MySQL 8.4 的分区支持集中在 InnoDB 和 NDB，不能把任意存储引擎都当作可分区表来设计。

可以用下面的决策顺序做评审记录：

1. 列出最常见的过滤条件，看候选列是否能触发裁剪。
2. 确认数据是否按候选列自然形成新增、保留、归档边界。
3. 检查分区表达式中的列是否覆盖主键和所有唯一键。
4. 核对分区类型允许的字段类型，并预演新增、删除、扩展分区的维护责任。

如果四项中只有“理论上能把数据打散”成立，我会保留普通表和索引方案，而不是强行分区。

常见问题

分区键是不是一定要选区分度最高的列？

不一定。分区键首先要服务于可裁剪的查询和可维护的数据边界；高区分度但不出现在查询条件里的列，未必能减少扫描。

按时间分区是不是所有大表的默认答案？

也不是。只有当查询、保留策略和维护动作都围绕时间展开时，时间分区才有明确收益；否则普通索引、归档表或按业务拆表可能更简单。

为什么分区表的主键经常要改成复合键？

因为分区表达式中的列必须出现在每个唯一键中。加入分区列可能满足 MySQL 的建表约束，但还要评估应用查询、索引宽度和唯一性语义是否仍然合理。

总结

选择 MySQL 分区键时，我会按“查询裁剪 → 数据生命周期 → 主键/唯一键 → 类型与维护”这个顺序判断。时间范围和归档边界清晰，就优先比较 RANGE；离散值域稳定，再看 LIST；HASH 和 KEY 适合均匀分散，但不要拿它们替代时间管理。最终能落地的分区键，应该同时让 SQL 有机会少扫、让运维知道怎么维护，也让现有键约束保持可解释。