当业务系统中的历史数据不断累积，很多团队会选择将旧数据归档到分区表中，以此提升查询和维护效率。分区裁剪是分区表查询优化的核心机制，能够在不修改查询逻辑的前提下，自动跳过和查询条件无关的分区，从而减少数据扫描范围，提升查询性能。
分区裁剪的基本原理
分区裁剪指的是数据库优化器在执行查询时，根据查询条件中的分区键信息，判断哪些分区不需要被访问，从而直接排除这些分区的过程。比如我们按时间维度对订单表进行分区，查询2024年3月的订单时，优化器会自动跳过2024年3月之外的所有分区，只扫描对应月份的分区数据。
分区裁剪分为静态裁剪和动态裁剪两种类型：
- 静态裁剪：在查询优化阶段就能确定需要访问的分区，通常对应分区键是常量条件的场景。
- 动态裁剪：在执行阶段才能确定需要访问的分区，通常对应分区键是参数化条件或者关联查询的场景。
归档数据分区设计实践
归档数据通常具备明显的时间属性，因此按时间维度分区是最常用的方案，常见的分区类型有范围分区和列表分区。
范围分区示例
以下是MySQL中创建按月份范围分区的订单归档表的示例：
-- 创建订单归档表，按订单创建时间按月做范围分区
CREATE TABLE order_archive (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
order_amount DECIMAL(10,2),
create_time DATETIME NOT NULL,
PRIMARY KEY (order_id, create_time)
) PARTITION BY RANGE (TO_DAYS(create_time)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01'))
);
列表分区示例
如果归档数据按业务类型划分，也可以使用列表分区：
-- 创建按业务类型列表分区的日志归档表
CREATE TABLE log_archive (
log_id BIGINT NOT NULL,
log_type VARCHAR(20) NOT NULL,
log_content TEXT,
create_time DATETIME NOT NULL,
PRIMARY KEY (log_id, log_type)
) PARTITION BY LIST COLUMNS (log_type) (
PARTITION p_order_log VALUES IN ('ORDER_CREATE', 'ORDER_PAY', 'ORDER_REFUND'),
PARTITION p_user_log VALUES IN ('USER_LOGIN', 'USER_REGISTER', 'USER_UPDATE'),
PARTITION p_system_log VALUES IN ('SYSTEM_START', 'SYSTEM_STOP', 'SYSTEM_ERROR')
);
触发分区裁剪的查询编写技巧
要让查询能够触发分区裁剪，核心是保证查询条件中能够明确体现分区键的过滤逻辑，以下是一些常见的实践技巧。
使用分区键直接过滤
查询条件中直接引用分区键，并且使用常量或者确定值的表达式，最容易触发静态分区裁剪：
-- 查询2024年3月的订单，直接命中p202403分区 SELECT order_id, user_id, order_amount FROM order_archive WHERE create_time >= '2024-03-01' AND create_time < '2024-04-01';
避免对分区键做函数转换
如果对分区键使用函数处理，优化器可能无法识别分区范围，导致分区裁剪失效：
-- 以下查询对create_time做了DATE函数处理，可能导致分区裁剪失效 SELECT order_id, user_id, order_amount FROM order_archive WHERE DATE(create_time) = '2024-03-01';
优化后的写法应该避免对分区键做函数处理：
-- 优化后的查询，分区裁剪可以正常触发 SELECT order_id, user_id, order_amount FROM order_archive WHERE create_time >= '2024-03-01' AND create_time < '2024-03-02';
关联查询中的分区裁剪
当分区表和其他表关联时，只要关联条件中包含分区键的过滤逻辑，也可以触发动态分区裁剪：
-- 关联查询中，order_archive表的分区键条件来自user表的过滤结果，触发动态分区裁剪 SELECT o.order_id, o.order_amount, u.user_name FROM order_archive o JOIN user u ON o.user_id = u.user_id WHERE u.register_time >= '2024-03-01' AND u.register_time < '2024-04-01' AND o.create_time >= '2024-03-01' AND o.create_time < '2024-04-01';
常见分区裁剪失效场景与排查
在实际使用中，可能会遇到查询没有触发分区裁剪的情况，常见的失效场景和排查方法如下：
| 失效场景 | 原因分析 | 优化方法 |
|---|---|---|
| 分区键使用函数或计算 | 优化器无法根据函数结果匹配分区范围 | 调整查询条件，避免对分区键做函数处理，改用范围过滤 |
| 查询条件不包含分区键 | 优化器无法确定需要访问的分区 | 在查询条件中补充分区键的过滤逻辑 |
| 分区键使用OR条件连接 | 部分数据库优化器对OR条件的分区裁剪支持不完善 | 改用IN条件或者UNION改写查询 |
| 分区键类型不匹配 | 隐式类型转换导致优化器无法识别分区范围 | 保证查询条件中分区键的类型和分区定义中的类型一致 |
可以通过数据库的查询执行计划来确认分区裁剪是否生效，以MySQL为例，使用EXPLAIN语句查看执行计划，如果partitions列只显示需要访问的分区名称，说明分区裁剪已经触发。
-- 查看查询的执行计划，确认分区裁剪是否生效 EXPLAIN SELECT order_id, user_id, order_amount FROM order_archive WHERE create_time >= '2024-03-01' AND create_time < '2024-04-01';
实践总结
分区裁剪是提升归档数据查询性能的重要手段，不需要额外修改查询逻辑就能实现性能提升。在实践中，首先要根据归档数据的特征选择合适的分区键和分区类型，其次在编写查询时要避免破坏分区裁剪的触发条件，最后通过执行计划定期排查分区裁剪的生效情况。合理的分区设计加上正确的查询编写，能够让归档数据的查询性能提升数倍甚至数十倍，有效降低数据库的负载。