分区表维护实战：DROP PARTITION、REORGANIZE与EXCHANGE的高效用法

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

分区表的核心价值是什么？

不是“数据分开放”，也不是“过期数据好删除”。这些是附带的好处。

分区表真正的核心价值只有一个——分区裁剪。

优化器根据WHERE条件中的分区键，自动排除不包含目标数据的分区，只扫描必要的分区。裁剪生效时，查询只扫一个分区，性能提升几十倍；裁剪失效时，扫描所有分区，比不分区还慢。

问题在于，分区裁剪不会自动生效。很多人建了分区表就以为万事大吉，结果查询跑了两年，一直是全表扫描。

今天把分区裁剪的失效场景、分区锁机制、分区维护方法一次讲透。

一、分区裁剪生效的条件

分区裁剪要生效，必须同时满足三个条件：

条件一：WHERE条件中包含分区键。 查询条件必须直接引用分区键列，不能是其他列。

条件二：分区键以原始列形式出现。 不能对分区键使用函数或表达式。

条件三：优化器能在执行计划阶段确定分区范围。 对于RANGE和LIST分区，条件必须是确定性的；对于HASH和KEY分区，条件必须是等值查询。

三个条件任何一个不满足，分区裁剪就会失效。

二、6种分区裁剪失效场景

场景一：对分区键使用函数

这是最常见的失效场景。

-- 分区键是create_time，按年RANGE分区
-- ❌ 失效：对分区键使用了YEAR函数
SELECT * FROM orders WHERE YEAR(create_time) = 2026;

-- ✅ 生效：直接用分区键做范围比较
SELECT * FROM orders 
WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';

YEAR(create_time)让优化器无法识别分区范围，只能扫描所有分区。改成范围比较后，优化器能精确锁定目标分区。

同样会导致失效的函数还有：DATE()、MONTH()、TO_DAYS()、SUBSTR()等。原则是：分区键必须以原始列形式出现在WHERE条件中。

场景二：隐式类型转换

-- 分区键是create_time（DATETIME类型）
-- ❌ 可能失效：传入了字符串，触发隐式类型转换
SELECT * FROM orders WHERE create_time = '2026-09-01';

-- 实际上MySQL会对字符串做隐式转换，相当于：
SELECT * FROM orders WHERE create_time = CAST('2026-09-01' AS DATETIME);

隐式转换本身不一定导致裁剪失效，但如果转换后的值超出了分区范围定义的精度（比如传了'2026-09'但分区是按天分的），优化器无法精确匹配分区。

场景三：OR条件跨分区

-- ❌ 失效：OR条件跨越了不同分区
SELECT * FROM orders 
WHERE create_time >= '2026-01-01' AND create_time < '2026-02-01'
   OR create_time >= '2026-06-01' AND create_time < '2026-07-01';

OR条件涉及两个不连续的时间段，优化器无法将这两个条件合并为一个连续的分区范围，最终可能扫描所有分区。MySQL 8.0之后对某些OR条件可以优化为分区并集，但老版本不行。

场景四：分区键与查询条件不匹配

-- 分区键是create_time，但查询条件用的是id
SELECT * FROM orders WHERE id = 12345;

id不是分区键，优化器不知道这条记录在哪个分区，只能扫描全部分区。

场景五：使用了不支持的比较运算符

分区裁剪支持的运算符有限。=、>、<、>=、<=、BETWEEN、IN都支持，但!=、<>、NOT IN、NOT LIKE等否定条件会导致裁剪失效。因为否定条件无法确定一个连续的分区范围。

场景六：分区键参与了表达式计算

-- ❌ 失效：分区键参与了计算
SELECT * FROM orders WHERE create_time + INTERVAL 1 DAY > '2026-09-01';

分区键一旦参与了任何表达式计算，优化器就无法用原始值来匹配分区。

三、怎么确认分区裁剪是否生效

方法一：看EXPLAIN的partitions列

EXPLAIN SELECT * FROM orders 
WHERE create_time >= '2026-09-01' AND create_time < '2026-10-01';

输出中有一列叫partitions，显示查询实际扫描的分区。如果显示多个分区名，说明只扫了这些分区；如果是NULL或ALL，说明扫描了全部分区。

-- 高效写法：只扫一个分区
EXPLAIN SELECT * FROM orders WHERE create_time = '2026-09-15';
-- partitions列显示：p202609

-- 低效写法：扫全部分区
EXPLAIN SELECT * FROM orders WHERE YEAR(create_time) = 2026;
-- partitions列显示：p202401,p202402,...,p202612（全部分区）

方法二：看EXPLAIN FORMAT=TREE

MySQL 8.0的EXPLAIN FORMAT=TREE会显示更详细的分区裁剪信息，包括哪些分区被裁剪掉了。

四、分区锁机制

分区表有一个容易被忽略的特性：分区锁与表锁的关系。

MDL锁（元数据锁） ：对分区表执行DDL操作时，会对整个表加MDL锁，阻塞所有分区的读写。这意味着，即使你只对某一个分区做ALTER TABLE ... PARTITION，也会影响其他分区的正常访问。

分区级锁：对分区表的DML操作（INSERT、UPDATE、DELETE）是分区级别的。如果一个查询只涉及一个分区，锁也只作用在那个分区上。这是分区表在写入并发上比普通表有优势的地方。

关键实践：大表的分区维护操作（如添加新分区、删除旧分区）应该在业务低峰期执行。ALTER TABLE ... ADD PARTITION和ALTER TABLE ... DROP PARTITION都会持有表级MDL锁，阻塞其他操作。

五、分区维护实战

维护操作一：添加新分区

-- 按年RANGE分区，2027年到来前需要提前添加
ALTER TABLE orders ADD PARTITION (
    PARTITION p2027 VALUES LESS THAN (2028)
);

注意：ADD PARTITION只支持在末尾添加，不支持在中间插入。如果要调整中间的分区范围，需要用REORGANIZE PARTITION。

维护操作二：删除旧分区

-- 删除2024年之前的分区，秒级完成
ALTER TABLE orders DROP PARTITION p2024;

DROP PARTITION比DELETE快几个数量级——DELETE需要逐行删除并记录Undo Log，DROP PARTITION直接删除分区的物理文件。

维护操作三：重组分区（REORGANIZE）

-- 把p2026拆成两个半年的分区
ALTER TABLE orders REORGANIZE PARTITION p2026 INTO (
    PARTITION p2026h1 VALUES LESS THAN (20260701),
    PARTITION p2026h2 VALUES LESS THAN (2027)
);

REORGANIZE会重建分区并移动数据，对大表来说可能非常耗时。建议在业务低峰期执行，并提前评估数据量。

维护操作四：分区交换（EXCHANGE）

EXCHANGE PARTITION是分区表最实用的维护工具之一——可以把一个分区和一个普通表做数据交换，常用于历史数据归档。

-- 1. 创建一张和分区表结构相同的普通表
CREATE TABLE orders_archive LIKE orders;

-- 2. 把p2024分区的数据交换到归档表（瞬间完成）
ALTER TABLE orders EXCHANGE PARTITION p2024 WITH TABLE orders_archive;

-- 3. 检查归档表数据，确认无误后删除分区
ALTER TABLE orders DROP PARTITION p2024;

EXCHANGE PARTITION只交换元数据，不移动数据，秒级完成。适合历史数据归档场景——不用DELETE，不用INSERT INTO ... SELECT，一条命令搞定。

六、小结

分区表的价值完全取决于分区裁剪能否生效。对分区键使用函数、隐式类型转换、OR条件跨分区、分区键与查询条件不匹配、否定条件、分区键参与表达式计算——这6种场景都会导致裁剪失效，查询退化为全表扫描。排查的核心方法是看EXPLAIN的partitions列。分区维护的核心工具是DROP PARTITION（秒删旧数据）、REORGANIZE（调整分区范围）、EXCHANGE（秒级归档）。

小耶在手，SQL 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~