MySQL 分区键避免跨分区扫描 怎样处理

我第一次排查 MySQL 分区表“越查越慢”时，直觉是继续补普通索引，结果 EXPLAIN 里的 partitions 仍然列出了多块分区。后来把问题拆开才发现：索引负责缩小分区内部的行范围，分区裁剪负责先排除不可能命中的分区；真正决定能不能避免跨分区扫描的，是查询条件能否直接对应分区键。

这篇只解决一个边界：怎样让分区键帮助 MySQL 少扫分区。结论是，优先让 WHERE 直接约束分区列，并让范围与分区表达式保持同一语义；使用 KEY 分区时则优先考虑等值或较短范围。最后一定看 EXPLAIN 的 partitions 字段，不能只看表上有没有索引。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/partitioning-pruning.html

先区分分区裁剪与普通索引

分区裁剪的核心很朴素：如果优化器能判断某个分区不可能有匹配行，就不访问它。比如按日期做 RANGE COLUMNS 分区，查询明确给出一个日期范围，优化器可以把候选范围落到对应的分区。普通索引仍然有用，但它是在已经选中的分区内部继续定位行，两者不是同一层优化。

下面这个例子把日期边界直接写在分区列上。示例中的注释说明每个关键关系，便于对照自己的表结构：

CREATE TABLE orders (
    id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    customer_id BIGINT NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    -- 分区列必须纳入主键，避免与分区表唯一键规则冲突
    PRIMARY KEY (id, order_date),
    KEY idx_customer_date (customer_id, order_date)
)
PARTITION BY RANGE COLUMNS (order_date) (
    PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
    PARTITION p2025 VALUES LESS THAN ('2026-01-01'),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

SELECT id, customer_id, amount
FROM orders
-- 直接约束分区键，让优化器有机会排除无关分区
WHERE order_date >= '2025-04-01'
  AND order_date 

这里的关键不是把分区数量做得越多越好，而是让高频查询的过滤条件与分区键相遇。如果查询只按 customer_id 过滤，却没有日期边界，数据库就没有足够信息排除日期分区；此时分区表可能仍要逐个候选分区使用索引。

让条件直接暴露分区键

实际项目里最容易踩的坑，是分区键与业务条件表达的粒度不一致。例如表按 order_date 分区，但查询先把日期变成字符串、月份或别的派生值。MySQL 8.4 官方确实列出 YEAR()、TO_DAYS()、TO_SECONDS() 等可用于特定分区裁剪的函数，但这不等于任意包装都能被稳定反推。我的做法是先用直接范围写法建立基线，再根据 EXPLAIN 判断是否需要保留函数表达式。

-- 推荐：边界直接落在原始日期列上
SELECT COUNT(*)
FROM orders
WHERE order_date >= '2025-04-01'
  AND order_date 

如果业务确实按月查询，可以让分区设计与业务边界一致，或者保留原始日期范围作为额外条件。这样既给分区裁剪提供可推断的边界，也让分区内部的索引有机会工作。不要把“查询返回行数少”误认为“只扫描了一个分区”。

处理 KEY 分区的等值和短范围

KEY 分区由 MySQL 提供哈希函数，适合把某个键的值均匀分散到固定分区。它与日期范围分区的直觉不同：等值条件通常最容易定位，较短范围也可能被转换为一组值；但范围覆盖的值太多时，逐个推算分区反而没有排除价值，不能强求裁剪。

CREATE TABLE customer_events (
    event_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    event_time DATETIME NOT NULL,
    payload JSON NOT NULL,
    -- KEY 分区列同时放入主键，满足分区表唯一键约束
    PRIMARY KEY (event_id, customer_id),
    KEY idx_customer_time (customer_id, event_time)
)
PARTITION BY KEY (customer_id)
PARTITIONS 4;

SELECT event_id, event_time
FROM customer_events
-- 等值条件是 KEY 分区最清晰的裁剪入口
WHERE customer_id = 10086;

SELECT event_id, event_time
FROM customer_events
-- 短范围可能被转换为若干键值，仍需用 EXPLAIN 确认
WHERE customer_id BETWEEN 10086 AND 10090;

还有一个容易被忽略的建表限制：分区表达式中的列必须出现在表上每一个唯一键中，主键也属于唯一键。也就是说，先有一个只包含 event_id 的主键，再临时把 customer_id 加进分区表达式，可能会在建表或改表时遇到约束错误。分区键选择要和主键、唯一键一起设计。

用 EXPLAIN 检查实际扫描范围

我现在会把“分区键看起来合理”和“查询真的少扫分区”分成两件事。用 EXPLAIN 看同一条查询，重点观察 partitions 字段：它列出候选分区名时，说明优化器计划访问这些分区；如果列出很多分区，就回头检查条件是否覆盖分区键、范围是否过宽，以及表达式是否让边界难以推断。

EXPLAIN
SELECT id, customer_id, amount
FROM orders
-- 用和业务查询一致的日期边界观察 partitions 字段
WHERE order_date >= '2025-04-01'
  AND order_date 

我的采用建议

如果主要查询天然带日期范围，RANGE 或 RANGE COLUMNS 更容易把“业务时间边界”映射成“分区边界”；如果主要按客户、租户或设备编号等值访问，KEY 分区更值得评估。两种方案都不能替代索引，也不能保证所有查询只落到一个分区。

落地前我会留一张小清单：第一，写出最常见的查询条件；第二，确认它是否直接出现分区键；第三，确认分区键能否进入所有唯一键；第四，用 EXPLAIN 对比 partitions；第五，再判断分区维护成本是否值得。这样排查出来的结果比单纯增加分区数量可靠得多。

相关问答

分区表已经有索引，为什么还会跨分区扫描？

索引只解决分区内部的定位。若查询条件无法推断分区键对应的范围，优化器仍可能访问多个分区，再分别使用索引。

KEY 分区一定能避免范围扫描吗？

不一定。等值条件通常更适合 KEY 分区，短范围可能有裁剪机会；范围过宽时，候选分区集合会变大，最终仍要以 EXPLAIN 的 partitions 结果为准。