MySQL 分区表的分区裁剪条件设计

MySQL 分区表能不能减少扫描，关键不在于“分了多少个区”，而在于分区键、分区边界和 WHERE 条件能否形成同一条可比较的范围。设计时可以把分区裁剪理解为一个结构约束：优化器先根据谓词判断哪些分区不可能命中，再只保留候选分区。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/

实用原则是：让最常用的时间或编号过滤直接落在分区键上，优先使用清晰的等值、IN 或半开区间；分区只是缩小候选范围，剩下的数据过滤仍然要依赖索引和执行计划。

先把分区键和边界设计成查询能直接比较的结构

假设订单表主要按创建时间查询和归档，分区键应选择查询中稳定出现的 created_at，而不是一个很少出现在条件里的随机哈希列。日期分区通常更适合用 RANGE COLUMNS 表达自然边界：

CREATE TABLE orders (
    id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    customer_id BIGINT NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
    -- 每个分区保存一个明确的时间段，边界使用左闭右开语义理解
    PARTITION p2024 VALUES LESS THAN ('2025-01-01 00:00:00'),
    PARTITION p2025 VALUES LESS THAN ('2026-01-01 00:00:00'),
    -- 兜底分区接住尚未规划的新日期，避免插入直接越界
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

这里的重点不是分区名称，而是边界与业务查询的粒度一致。按年归档就按年切；如果查询常按月扫描，年度分区可能仍然太宽，应该在写入量、维护成本和单区扫描范围之间做取舍。RANGE COLUMNS 还可以使用多个分区列，优化器会把这些列一起纳入分区放置和裁剪判断。

让 WHERE 条件稳定落到可裁剪的分区集合

对上面的表，最容易让优化器判断范围的是直接比较分区列。查询时间段时建议使用左闭右开的写法，避免把当天最后一秒、微秒精度和下一段边界混在一起：

SELECT id, customer_id, amount
FROM orders
WHERE created_at >= '2025-06-01 00:00:00'
  AND created_at 

等值条件和短的 IN 集合也容易映射到有限分区；较短的范围条件在某些分区类型下也可能被转成等值集合。真正需要关注的是谓词与分区表达式是否同构：如果建表时使用了 YEAR(created_at)，查询条件和边界就应围绕同一表达式设计；如果使用的是 RANGE COLUMNS (created_at)，就不要再把列包进另一种日期函数后才过滤。

-- 先观察优化器计划中的 partitions 字段，再决定是否需要改写谓词
EXPLAIN
SELECT id, customer_id, amount
FROM orders
WHERE created_at >= '2025-06-01 00:00:00'
  AND created_at 

不要把“命中了分区表”误认为“只扫描了一个分区”。如果时间范围跨越两个月，候选集合就可能包含两个或更多分区；如果条件没有限定分区键，分区裁剪也可能无法明显缩小范围。EXPLAIN 的 partitions 列是设计复查的入口，不应只凭分区名称猜测。

反例不是“分区失效”，而是条件和边界没有对齐

常见问题有三类。第一，查询只按 customer_id 过滤，却把分区键定为 created_at，优化器没有足够信息排除日期分区。第二，查询对分区列做了额外函数变换，例如把 created_at 转成字符串再比较，条件就不再是清晰的原始范围。第三，分区边界过粗，虽然发生了裁剪，但一次仍需扫描很大的时间段。

迁移时不要只追求“分区数量更多”。分区裁剪解决的是候选分区筛选，分区内部仍然需要合适的普通索引；如果一条查询必须跨越大多数分区，维护成本可能已经超过收益。对固定日期范围的报表，可以把查询模板固定成半开区间，减少调用方各自拼接边界造成的计划差异。

上线前的四项设计清单

1. 确认访问路径：列出最常见的查询、更新和删除条件，确认其中确实有稳定的分区键过滤。
2. 确认边界策略：为历史区、当前区和未来数据分别定义边界；使用 MAXVALUE 时安排后续拆分或重组方案。
3. 确认键约束：检查分区表达式涉及的列是否满足主键和唯一键限制，尤其不要在迁移后才发现唯一约束无法创建。
4. 确认计划入口：用代表性等值、IN 和时间范围语句查看 EXPLAIN，把实际的 partitions 记录到迁移说明中。

最后，分区键不是越“技术化”越好，而是要和读写路径、归档周期及约束条件同时成立。能直接比较的分区键、清晰的边界和稳定的查询模板，才是分区裁剪真正可维护的组合。

相关问题

分区表一定比普通表查询快吗？

不一定。只有查询能排除一部分分区，且分区内部仍有合适索引时，裁剪才可能带来收益；跨越大多数分区的查询还会承担分区管理成本。

为什么用了 RANGE 分区却看不到明显裁剪？

先看查询是否直接限制分区键，再看分区表达式与谓词是否一致，最后用 EXPLAIN 观察 partitions，不要只看表定义。