3、分区裁剪原理：查询优化器如何识别分区

分区裁剪（Partition Pruning）这个词，说白了就是让数据库只扫描“有用”的分区，跳过那些跟查询条件无关的分区。我刚开始接触分区表时，以为建了分区就万事大吉，结果发现查询还是全分区扫描——嗯，那时候我才意识到，分区裁剪不是自动生效的，它需要优化器“看懂”你的查询条件。

这一节，我们就把分区裁剪的底层逻辑拆开来看。优化器到底怎么识别分区？执行计划里怎么判断裁剪生效了？谓词下推又跟分区裁剪有什么关系？

核心一句话：分区裁剪的本质是“过滤条件下推 + 元数据过滤”。优化器把 WHERE 条件里的分区键值提前算出来，然后去元数据里匹配分区边界，只保留命中的分区。

3.1 优化器识别分区的三个步骤

我个人习惯把优化器识别分区的过程分成三步：解析分区键 → 计算分区范围 → 裁剪分区列表。每一步都有坑，我们一个个看。

  1. 解析分区键：优化器先看 WHERE 条件里有没有分区键。比如表按 dt 分区，条件里写了 dt = '2024-01-15'，那优化器就知道“这个条件能用来裁剪”。
  2. 计算分区范围：如果条件里有 BETWEEN、IN、>= 等范围操作，优化器会算出具体落在哪些分区编号上。比如 dt BETWEEN '2024-01-01' AND '2024-01-31'，优化器会算出这 31 天对应哪几个分区。
  3. 裁剪分区列表：最后，优化器把不包含目标数据的分区从扫描列表中移除。这一步发生在执行计划生成阶段，不是运行时。

我在项目中遇到过一种情况：条件里用了函数，比如 DATE(dt) = '2024-01-15'，但分区键是 dt 本身。优化器没法把 DATE(dt) 反向推导回 dt 的范围，结果分区裁剪失效了。这个坑后面会细讲。

3.2 分区裁剪的执行计划分析

怎么判断分区裁剪有没有生效？看执行计划。不同数据库的展示方式不同，但核心标志是一样的：扫描的分区数量 < 总分区数量。

以 PostgreSQL 为例，执行计划里会显示 Subplans Removed: N。比如：

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders 
WHERE order_date = '2024-01-15';

输出可能长这样：

Append
  Subplans Removed: 11
  ->  Seq Scan on orders_20240115  (cost=0.00..35.50 rows=10 width=100)
        Filter: (order_date = '2024-01-15'::date)

看到 Subplans Removed: 11 了吗？这说明表有 12 个分区，优化器只保留了 1 个，裁剪掉了 11 个。这就是分区裁剪生效的标志。

在 MySQL 里，用 EXPLAIN 看 partitions 列：

EXPLAIN SELECT * FROM orders 
WHERE order_date = '2024-01-15';

输出里 partitions 列会显示 p20240115，而不是 p_all 或 p202401,p202402,...。如果看到多个分区，说明裁剪不彻底。

小技巧：我建议你在测试环境里先跑一次不带 WHERE 条件的执行计划，再跑一次带条件的，对比 partitions 列的变化。这样能直观看到裁剪效果。

3.3 谓词下推与分区裁剪的关系

谓词下推（Predicate Pushdown）和分区裁剪是两回事，但经常一起出现。我简单解释一下：

  • 分区裁剪：在表扫描之前，根据分区键条件跳过整个分区。这是“分区级别”的过滤。
  • 谓词下推：把 WHERE 条件推到数据源更近的地方执行。比如在分区表上，谓词下推会把条件推到每个分区的扫描节点上，让每个分区只返回符合条件的数据。

两者配合的效果是：分区裁剪先砍掉不需要的分区，谓词下推再在每个剩余分区里做精细过滤。举个例子：

SELECT * FROM orders 
WHERE order_date = '2024-01-15' 
  AND status = 'PAID';

分区裁剪先只扫描 p20240115 这个分区。然后谓词下推把 status = 'PAID' 推到该分区的扫描节点上，只返回已支付的订单。如果没有谓词下推，即使只扫一个分区，也会把该分区所有数据都读出来再过滤——那性能就差远了。

我曾经踩过的坑：有一次我在分区键上用了 YEAR(order_date) = 2024，结果分区裁剪完全失效。因为 YEAR() 函数把分区键包住了，优化器没法反向推导出分区范围。后来改成 order_date BETWEEN '2024-01-01' AND '2024-12-31'，裁剪就正常了。记住：分区键上不要套函数，这是分区裁剪的第一条铁律。

3.4 分区裁剪的完整流程

下面这张图展示了从 SQL 解析到分区裁剪完成的完整流程。我把它画成了 SVG，方便你理解每个环节的输入和输出。

分区裁剪执行流程 分区裁剪执行流程 SQL 解析 识别分区键与条件 计算分区范围 裁剪分区列表 生成执行计划 关键检查点 • 分区键是否裸露？ • 条件是否可计算？ • 范围是否精确？ • 有无函数包裹？ • 执行计划是否显示 Subplans Removed？ 图：分区裁剪从 SQL 解析到执行计划生成的完整流程

3.5 常见分区裁剪失效场景

根据我的经验，分区裁剪失效的场景其实就那么几种。我列了个表格，方便你对照排查：

失效场景 原因 正确写法
分区键套函数 YEAR(dt) = 2024 无法反向推导 dt BETWEEN '2024-01-01' AND '2024-12-31'
隐式类型转换 dt = '20240115' 与分区键类型不匹配 dt = '2024-01-15' 或显式转换
OR 条件包含非分区键 dt = '2024-01-15' OR status = 'PAID' 导致全分区扫描 拆成 UNION 或用 IN 代替 OR
子查询无法提前计算 dt = (SELECT MAX(dt) FROM orders) 运行时才确定值 先查出值再拼入条件
分区键在 JOIN 条件中 JOIN 条件里的分区键不会被用于裁剪 在 WHERE 里显式加上分区键条件

避坑指南：我曾经在一个生产环境里排查慢查询，发现一张 200 个分区的表每次查询都扫全表。最后定位到是 OR 条件的问题——dt = '2024-01-15' OR status = 'PAID' 导致优化器认为必须扫描所有分区才能判断 status 条件。改成 dt = '2024-01-15' AND status = 'PAID' 后，裁剪立刻生效，查询从 12 秒降到 0.3 秒。

3.6 小结

分区裁剪的原理其实不复杂：优化器通过元数据匹配分区边界，提前排除无关分区。但实际应用中，分区键是否裸露、条件是否可计算、执行计划是否显示裁剪信息，这三个点决定了裁剪能否生效。

我个人建议你在建分区表时，先想清楚查询模式。如果查询条件里经常用 BETWEEN 或 IN，那分区裁剪大概率能生效。如果条件里总套函数或隐式转换，那就要考虑改表结构或改写 SQL 了。

← 上一章 📖 返回目录 下一章 →