4. 分区裁剪与查询优化：WHERE条件与分区裁剪、JOIN操作中的分区裁剪、子查询中的分区裁剪

分区裁剪（Partition Pruning）这词听起来挺唬人，说白了就是——只扫描需要的数据分区，跳过那些无关的分区。我见过太多开发同学，分区表建得漂漂亮亮，但SQL一写，分区裁剪根本没生效，全表扫描，性能直接崩了。

这一章，咱们就聊聊三种最常见的场景：WHERE条件、JOIN操作、子查询。我会把我在项目中踩过的坑、总结的经验都抖出来。

4.1 WHERE条件与分区裁剪

这是最基础、也是最容易出问题的场景。你想想看，WHERE条件写得对不对，直接决定了优化器能不能“剪掉”多余的分区。

4.1.1 分区键直接比较

最理想的情况，就是WHERE条件里直接对分区键做等值或范围比较。比如这张订单表按 order_date 做了范围分区：

-- 分区表定义（按月范围分区）
CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404),
    PARTITION p202404 VALUES LESS THAN (202405)
);

-- 能触发分区裁剪的查询
SELECT * FROM orders
WHERE order_date >= '2024-02-01' AND order_date < '2024-03-01';

这个查询，优化器能精确算出只访问 p202402 这一个分区。我在一次性能压测中看到，同样的数据量，分区裁剪生效时查询耗时从12秒降到了0.3秒——差距就是这么大。

4.1.2 常见的“裁剪失效”陷阱

嗯，这里要注意。很多同学以为只要WHERE条件里写了分区键就行，其实不然。我列几个我亲手修过的案例：

写法	是否生效	原因
WHERE order_date = '2024-02-15'	✅ 生效	直接等值比较
WHERE DATE(order_date) = '2024-02-15'	❌ 失效	函数包裹了分区键
WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29'	✅ 生效	范围比较，优化器能推导
WHERE order_date + INTERVAL 1 DAY = '2024-02-16'	❌ 失效	分区键参与了表达式运算
WHERE CAST(order_date AS CHAR) = '2024-02-15'	❌ 失效	隐式类型转换

4.2 JOIN操作中的分区裁剪

JOIN场景下的分区裁剪，比单表查询复杂得多。我个人习惯把JOIN裁剪分成两类：驱动表裁剪和被驱动表裁剪。

4.2.1 驱动表的分区裁剪

如果JOIN的驱动表（通常是左表）上有WHERE条件过滤分区键，那驱动表的裁剪和单表一样，直接生效。比如：

-- 驱动表 orders 按 order_date 分区
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-02-01' 
  AND o.order_date < '2024-03-01';

这里 orders 表只会扫描2月份的分区，没问题。

4.2.2 被驱动表的分区裁剪

这才是真正的技术活。被驱动表的分区裁剪，依赖于JOIN条件能否传递分区键信息。举个例子：

-- 订单明细表 order_items 按 order_date 分区
SELECT *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
                    AND o.order_date = oi.order_date  -- 关键：JOIN条件包含分区键
WHERE o.order_date >= '2024-02-01' 
  AND o.order_date < '2024-03-01';

这里，优化器通过 o.order_date = oi.order_date 这个条件，能把 o 表的分区过滤信息“传递”给 oi 表。于是 oi 表也只扫描2月份的分区。

4.2.3 JOIN裁剪失效的典型场景

• JOIN条件里没有分区键：比如只 ON o.order_id = oi.order_id，没有日期关联，被驱动表只能全分区扫描。
• 分区键类型不匹配：一个表是 DATE，另一个是 VARCHAR，隐式转换导致裁剪失效。
• 使用了OUTER JOIN且分区键在非驱动表侧：比如 LEFT JOIN，右表的分区键过滤条件可能被优化器忽略。

4.3 子查询中的分区裁剪

子查询的分区裁剪，嗯，这里水比较深。我把它分成相关子查询和非相关子查询来聊。

4.3.1 非相关子查询

非相关子查询（子查询不依赖外层查询的列），优化器通常会先执行子查询，再把结果传给外层。这种情况下，外层表的分区裁剪取决于外层WHERE条件：

-- 外层表 orders 按 order_date 分区
SELECT *
FROM orders
WHERE order_date >= '2024-02-01'
  AND order_date < '2024-03-01'
  AND customer_id IN (
      SELECT customer_id FROM vip_customers WHERE level = 'gold'
  );

这里，外层 orders 表的裁剪不受子查询影响，只要日期条件在，裁剪就生效。

4.3.2 相关子查询

相关子查询就麻烦了。子查询里引用了外层表的列，优化器可能无法提前确定分区范围。比如：

-- 这个查询的分区裁剪可能失效
SELECT *
FROM orders o
WHERE o.amount > (
    SELECT AVG(amount) 
    FROM orders o2 
    WHERE o2.customer_id = o.customer_id
);

为什么？因为对于外层 orders 表的每一行，子查询都要重新计算。优化器没法在编译阶段确定要扫描哪些分区——它得一行一行跑。我曾经在MySQL 5.7上遇到过这种场景，一个本来该走分区裁剪的查询，硬是变成了全分区扫描。

4.4 分区裁剪核心逻辑图

下面这张图，我把三种场景的裁剪逻辑串起来了。你可以看到，WHERE条件是最直接的入口，JOIN和子查询的裁剪都依赖于它能否“传递”分区信息。

4.5 实战建议总结

说了这么多，我最后给你几个能直接用的建议：

1. WHERE条件里，分区键别穿“马甲”——别套函数、别做运算、别隐式转换。直接拿原字段比较。
2. JOIN两张分区表时，尽量在JOIN条件里带上分区键。这样两张表都能裁剪，性能翻倍。
3. 子查询优先用JOIN改写。尤其是相关子查询，能改就改。实在改不了，用 EXPLAIN 看看是不是走了全分区扫描。
4. 养成看执行计划的习惯。我每次写完SQL，都会用 EXPLAIN PARTITIONS（MySQL）或 EXPLAIN (PARTITIONS)（PostgreSQL）看一眼，确认裁剪生效了才放心。