MySQL：按天做了RANGE分区，查某一天还是扫了所有分区，分区键到底该怎么选才能让裁剪真正命中？

一、问题的本质：裁剪是一次“可证性”判定

1.1 现象复述与常见误判

典型场景是这样的：一张订单表按 order_date 做了 RANGE (TO_DAYS(order_date)) 分区，每天一个分区。业务方执行 WHERE order_date = '2025-03-01'，EXPLAIN 的 partitions 列却列出了全部几十个分区。第一反应往往是“分区没生效”“MySQL 分区是假的”，于是转向分库分表或换数据库。

但更准确的描述是：分区是存在的，只是优化器无法从这条谓词中“证明”目标分区集合。裁剪（partition pruning）不是建表时的静态属性，而是每次查询执行前的一次动态推导。推导失败，裁剪就不发生，与分区定义是否正确无关。笔者认为，把这类问题称为“分区失效”是一种语义偷换，它掩盖了真正需要修复的环节——谓词与分区表达式之间的可证关系。

1.2 本文主线：裁剪可证性

所谓裁剪可证性，指优化器能否在给定分区表达式 P(col) 与查询谓词 C(col) 的前提下，机械地推导出“满足 C 的行必然落在分区集合 S 内”，且 S 是全部分区的一个真子集。这个推导必须满足三个条件：

• 表达式可逆或可比较：P 必须是单调、确定性的函数，优化器才能把对 col 的约束映射为对 P(col) 的区间约束。
• 谓词形态可识别：C 必须落在优化器支持的谓词模板内（等值、范围、IN 列表等），且两侧类型可比。
• 推导发生在正确的阶段：裁剪发生在优化阶段而非执行阶段，任何阻断优化的写法都会让证据链中断。

本文评述：这三个条件构成了一条“证据链”，任一环节断裂，裁剪即失效。工程上排查时，不应笼统地问“为什么没裁剪”，而应逐环节定位“证据在哪一步丢失”。这条主线将贯穿后文所有章节。

1.3 为什么这个问题值得单独讨论

MySQL 的分区实现与 Oracle、PostgreSQL 存在结构性差异。MySQL 8.0 的分区表在存储引擎层是“一个表、多个物理分区”，分区裁剪由优化器在 sql/partition_info.cc 等模块中完成，且对分区表达式的支持有明显边界：分区函数必须是确定性的、返回整数，且不能是存储函数或子查询。这些约束直接决定了可证性的上限。

另一方面，按天分区在时序数据、日志、订单等场景极为普遍，一旦裁剪不命中，全分区扫描的代价随分区数线性增长。以模拟数据为例：单分区 200 万行、共 90 个日分区，全扫描与单分区扫描的缓冲池读放大可达两个数量级（模拟数据，基于 InnoDB 页读取模型估算，非实测）。因此，裁剪问题不是“优化一点点”，而是决定查询是否可用的分水岭。

二、裁剪判定链路：从SQL到分区迭代

2.1 一条查询的完整路径

要定位证据丢失点，必须先理解路径。一条带分区表的查询大致经过：解析（parse）→ 预处理（prepare，含类型推导与常量折叠）→ 优化（optimize，含谓词下推、等价类推导、分区裁剪）→ 执行（execute，按分区迭代读取）。分区裁剪发生在优化阶段，输入是 WHERE 条件树与分区元数据，输出是待访问分区列表。

-- 裁剪推导的抽象过程（伪代码，示意）
input:  predicate C, partition_expr P, partition_list
step1:  将 C 规范化为 col op const 的合取式
step2:  若 P 单调，求 P(col) 在 C 下的取值区间 [lo, hi]
step3:  遍历分区边界，保留与 [lo, hi] 相交的分区
output: 分区集合 S（若 S == 全部分区，则裁剪未命中）

本文评述：这条路径的关键在于 step1 与 step2 之间。step1 依赖谓词形态，step2 依赖分区表达式的数学性质。很多失效案例中，step1 就失败了——谓词被写成了 DATE_FORMAT(order_date,'%Y-%m-%d') = '2025-03-01' 这类形式，优化器无法把它还原为对 order_date 的区间约束，step2 自然无从谈起。

2.2 分区表达式的数学性质要求

优化器要完成 step2，分区表达式 P 必须满足：单调性（col 增大时 P(col) 不减或不变）与确定性（相同输入恒得相同输出）。TO_DAYS()、YEAR()、UNIX_TIMESTAMP() 在合理输入域内满足单调性；而 RAND()、UUID() 不满足确定性，MD5() 不满足单调性，均不能作为分区表达式。

需要特别指出，MySQL 官方文档明确分区函数必须返回整数且为确定性函数，并禁止使用存储函数、可加载函数和子查询。这一约束是硬边界，不是实现缺陷。笔者认为，理解这一点可以避免大量无效尝试——例如试图用 HASH(TO_DAYS(d)) 做“更均匀”的分区，结果反而破坏了单调性，裁剪彻底失效。

2.3 裁剪的三种粒度

裁剪并非全有或全无，而是分粒度的：

本文评述：工程排查中最容易忽略的是“分区级裁剪命中但分区内仍全扫”。此时 EXPLAIN 的 partitions 列看起来正常，但 rows 很大，性能依旧差。因此验证必须同时看 partitions 与 rows/type，不能只看前者。

三、六类典型失效模式与机制拆解

3.1 模式一：谓词包了函数，证据在第一步就丢了

最常见写法：WHERE DATE_FORMAT(order_date,'%Y-%m-%d') = '2025-03-01'。分区表达式是 TO_DAYS(order_date)，但谓词作用在 DATE_FORMAT(...) 的结果上。优化器无法把对格式化字符串的等值约束反推为对 order_date 的区间，step1 失败。

改写方向：把函数从列上移到常量侧，写成 order_date >= '2025-03-01' AND order_date < '2025-03-02'。这样谓词直接作用于分区列，区间可证。注意不要写成 DATE(order_date) = '2025-03-01'，同样是列上包函数。

3.2 模式二：类型不匹配导致隐式转换

若 order_date 是 DATETIME，而应用传入字符串或数值，比较时可能触发隐式转换。更隐蔽的是分区键为整型（如 TO_DAYS() 结果），查询却用日期字符串，优化器需要在类型层面做等价推导，某些版本或写法下推导不完整。

操作建议：统一参数类型，应用层显式绑定与列类型一致的参数；在 SQL 中避免对分区列做算术或字符串拼接。可用 SHOW WARNINGS 观察是否有隐式转换提示。

3.3 模式三：OR 条件与跨分区谓词

当 WHERE 中出现 OR，且分支分别作用于不同列或不可证形式时，整体可证性下降。例如 WHERE order_date='2025-03-01' OR status=1，第二个分支与分区键无关，优化器只能保留全部分区。

本文评述：这不是缺陷而是逻辑必然——只要存在一条不约束分区键的可行路径，就无法排除任意分区。改写策略是拆分查询并在应用层合并，或把 status 条件改为分区键可证的形式（如按状态做二级分区，但需谨慎评估）。

3.4 模式四：参数化查询与预处理语句

使用 PREPARE 或驱动端参数绑定时，裁剪发生在执行计划生成阶段。MySQL 8.0 对预处理语句支持在执行时进行分区裁剪，但若计划被缓存且参数分布差异大，可能出现计划复用导致的裁剪不理想。需要结合 EXPLAIN FOR CONNECTION 观察实际执行计划。

操作路径：对分区表上的高频查询，评估是否关闭计划缓存或使用 SET optimizer_switch 相关开关；在应用侧记录慢查询的实际 partitions 列。

3.5 模式五：分区键选错，业务查询根本不带它

这是选型层面的根因。表按 order_date 分区，但绝大多数查询按 user_id 过滤。此时无论 SQL 怎么写，都无法裁剪，因为证据链的第一环——谓词涉及分区键——就不存在。

本文评述：分区键选型的第一原则不是“按时间好维护”，而是“高频查询的过滤列必须能覆盖分区键”。维护便利性是第二位的。若业务同时有按时间和按用户两类查询，单层 RANGE 分区只能服务其中一类，需要评估复合分区或放弃分区改用其他手段。

3.6 模式六：分区过多与元数据开销

即使裁剪命中，分区数量过多也会带来打开表、元数据锁、DDL 等开销。按天分区一年 365 个分区，若保留多年则上千。MySQL 对分区数量没有硬性上限，但每个分区对应独立的表空间与元数据，优化器遍历分区边界的时间随分区数增长。

操作建议：按数据量与查询模式选择天/周/月粒度；对历史分区做归档或 EXCHANGE PARTITION 转出；避免“为未来预留”而预先建大量空分区。

四、分区键选型：决策框架与操作步骤

4.1 选型的四个判据

笔者认为，分区键选型应同时满足四个判据，缺一不可：

• 覆盖性：Top N 高频查询的 WHERE 中必须包含分区键，且以可证形式出现。
• 单调性：分区表达式对分区键单调，保证区间可映射。
• 均衡性：各分区数据量差异可控，避免热点分区。
• 可维护性：分区粒度与数据生命周期匹配，便于归档与 DDL。

四者冲突时，优先级应为覆盖性 > 单调性 > 均衡性 > 可维护性。因为前两者决定裁剪是否可能发生，后两者只影响效率与运维成本。

4.2 操作步骤：从查询画像到建表

第一步，采集查询画像。从慢查询日志、performance_schema 的 events_statements_summary_by_digest 中提取该表的 Top 查询，统计 WHERE 列的出现频次与形态。

第二步，识别候选分区键。选择出现频次最高、且能写成等值/范围谓词的列。若最高频列是 user_id，则应考虑 HASH 或 KEY 分区而非 RANGE。

第三步，验证可证性。对候选键构造典型查询，在测试环境用 EXPLAIN 检查 partitions 列是否为真子集。若否，回到第二步。

第四步，评估均衡与维护。用模拟数据或抽样统计各分区行数，确认无极端倾斜；确定分区粒度与保留策略。

-- 按天 RANGE 分区的推荐写法（分区键为日期列）
CREATE TABLE orders (
  id BIGINT NOT NULL,
  order_date DATE NOT NULL,
  user_id BIGINT NOT NULL,
  amount DECIMAL(12,2),
  PRIMARY KEY (id, order_date)
) PARTITION BY RANGE COLUMNS(order_date) (
  PARTITION p20250301 VALUES LESS THAN ('2025-03-02'),
  PARTITION p20250302 VALUES LESS THAN ('2025-03-03')
);

本文评述：RANGE COLUMNS 相比 RANGE(TO_DAYS(...)) 的优势在于分区键直接是日期列，谓词无需函数映射，可证性更强，边界也更直观。代价是分区边界必须写成日期字面量，需由运维脚本生成。笔者认为，在新项目中应优先考虑 RANGE COLUMNS。

4.3 复合分区与替代方案

当业务同时需要按时间和按用户裁剪时，可考虑 RANGE + HASH 复合分区：先按天 RANGE，再按 user_id HASH 子分区。这样时间查询可裁剪到日分区，用户查询可在子分区层面部分裁剪。但复合分区的子分区裁剪支持有限，需实测验证。

若两类查询都极高频且数据量大，分区可能不是最优解，分库分表或列式存储更合适。本文评述：分区是“单机内的物理组织优化”，不应被当作分布式扩展手段，边界要认清。

五、改写手法：让谓词变成“可证形式”

5.1 谓词改写对照表

5.2 应用层改造与参数绑定

很多不可证写法来自 ORM 或报表工具的自动生成。操作路径是：在数据访问层统一封装时间条件构造器，强制输出半开区间；对报表 SQL 做静态扫描，用正则匹配 DATE_FORMAT|YEAR\(|MONTH\(|LEFT\( 等模式并告警。

本文评述：治理裁剪问题，SQL 改写只是治标，把“可证形式”固化为团队规范与代码检查规则才是治本。否则新代码会持续引入同类问题。

5.3 索引与分区的协同

分区裁剪解决“访问哪些分区”，索引解决“分区内访问哪些行”。两者是乘法关系。若分区内无合适索引，裁剪命中后仍是分区内全扫。建议在分区表上保留以分区键为前缀的本地索引，并针对高频非分区键条件建二级索引。

延伸阅读：MySQL 官方分区裁剪文档 dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html；分区表限制 partitioning-limitations。

六、验证与观测：EXPLAIN与运行时证据

6.1 EXPLAIN 的正确读法

对分区表执行 EXPLAIN，重点看三列：partitions（实际访问分区）、type（访问方式）、rows（估算行数）。partitions 为 NULL 或列出全部分区，说明裁剪未命中；partitions 正确但 type=ALL 且 rows 大，说明分区内未走索引。

EXPLAIN SELECT * FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-03-02'\G
-- 关注输出中的 partitions: p20250301
-- 若显示 p20250301,p20250302,... 则裁剪失败

6.2 运行时证据与监控

EXPLAIN 是估算，运行时还需证据。可用 performance_schema 的语句事件统计，结合 EXPLAIN ANALYZE（MySQL 8.0.18+）观察实际扫描行数与分区访问。对慢查询，记录其 digest 与 partitions 分布，形成基线。

本文评述：建议把“分区裁剪命中率”纳入日常巡检指标——对分区表 Top 查询，统计 partitions 列非全集的占比。这个指标比单纯看慢查询更能提前发现退化。

6.3 一个可复现的验证脚本

在测试库构造小表，分别用可证与不可证写法执行，对比 partitions 列。注意脚本中的行数为模拟数据，仅用于演示差异，不代表生产性能。

-- 模拟数据：每分区约 1000 行（模拟）
EXPLAIN SELECT COUNT(*) FROM orders
WHERE DATE_FORMAT(order_date,'%Y-%m-%d')='2025-03-01';
-- 预期 partitions 列出全部分区

EXPLAIN SELECT COUNT(*) FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-03-02';
-- 预期 partitions 仅 p20250301

七、对比、局限与前沿预判

7.1 与其他数据库的裁剪能力对比

本文评述：对比的意义不在于评判优劣，而在于理解 MySQL 的裁剪是“保守可证”而非“激进推断”。这种保守性保证了正确性，但要求开发者显式写出可证谓词。把这一设计哲学讲清楚，比罗列版本差异更有价值。

7.2 局限与边界条件

第一，分区裁剪不改变分区内访问方式，不能替代索引。第二，分区表对唯一索引有约束：唯一索引必须包含分区键，否则无法创建。第三，外键在分区表上支持受限。第四，分区表的 DDL 与备份恢复复杂度高于普通表。

笔者认为，这些边界决定了分区不是“默认优化项”，而是“有明确适用场景的物理设计手段”。在引入前应做成本收益评估，而非看到大表就分区。

7.3 前沿预判

从社区与官方路线看，MySQL 在优化器层面对表达式推导的能力在逐步增强，但分区裁剪的核心约束（确定性、整数返回、无子查询）短期内不会改变。工程上的趋势是：把时间维度的裁剪前移到应用层的分区路由，或借助代理层做 SQL 改写。另一个方向是列式引擎与分区结合，在分析场景降低全分区扫描代价。

本文评述：与其等待优化器变聪明，不如把“可证性”作为设计约束前置到建模阶段。这是成本最低、收益最确定的路径。

八、结论与落地清单

回到开头的问题：按天 RANGE 分区却全分区扫描，根因不是分区没建对，而是查询谓词与分区表达式之间缺少可证关系。修复路径应按“证据链”逐环排查：谓词是否作用于分区键、形态是否可识别、类型是否一致、分区表达式是否单调、查询是否带分区键。

延伸资源：MySQL 分区官方手册 dev.mysql.com/doc/refman/8.0/en/partitioning.html；EXPLAIN 输出说明 explain-output；性能模式文档 performance-schema。

主要参考文献

1. Oracle. MySQL 8.0 Reference Manual: Partitioning Pruning. 2024. https://dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html
2. Oracle. MySQL 8.0 Reference Manual: Partitioning Limitations Relating to Functions. 2024.
3. Oracle. MySQL 8.0 Reference Manual: EXPLAIN Output Format. 2024.
4. Oracle. MySQL 8.0 Reference Manual: Performance Schema Statement Digests. 2024.
5. Oracle. MySQL 8.0 Reference Manual: RANGE COLUMNS Partitioning. 2024.
6. Oracle. MySQL 8.0 Reference Manual: Partition Selection and Pruning. 2024.
7. PostgreSQL Global Development Group. PostgreSQL 16 Documentation: Table Partitioning. 2023.
8. Oracle. MySQL 8.0 Reference Manual: Restrictions and Limitations on Partitioning. 2024.
9. Oracle. MySQL 8.0 Reference Manual: EXCHANGE PARTITION. 2024.

说明：本文涉及的行数、分区数等性能数字除特别标注外均为模拟数据，用于说明机制差异，不代表任何生产系统的实测结果；文献引用以官方手册与公开文档为主，未虚构作者或实验。