分区表，直接 DROP PARTITION，什么是删除分区，为什么删除分区比 DELETE 高效百倍且不产生大量 binlog
分区表删除详解
一、什么是分区表
分区表是将一张逻辑大表按照某种规则拆分成多个物理小片段存储的技术。
直观理解
普通表：
一张 orders 表
┌─────────────────────────┐
│ 所有订单数据混在一起 │
│ 2020年、2021年、2022年 │
│ 全部存在同一个文件 │
└─────────────────────────┘
分区表：
orders 表（逻辑上是一张表）
├── p202001 分区（物理文件：orders#p202001.ibd）
│ └── 2020年1月的订单
├── p202002 分区（物理文件：orders#p202002.ibd）
│ └── 2020年2月的订单
├── p202003 分区
│ └── 2020年3月的订单
└── ... 更多分区
实际创建示例
CREATE TABLE orders (
id BIGINT,
user_id BIGINT,
total_amount DECIMAL(10,2),
created_at DATETIME,
INDEX idx_user_id (user_id)
) PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
PARTITION p202401 VALUES LESS THAN (202402), -- 2024年1月
PARTITION p202402 VALUES LESS THAN (202403), -- 2024年2月
PARTITION p202403 VALUES LESS THAN (202404), -- 2024年3月
PARTITION p202404 VALUES LESS THAN (202405), -- 2024年4月
PARTITION p202405 VALUES LESS THAN (202406), -- 2024年5月
PARTITION p202406 VALUES LESS THAN (202407) -- 2024年6月
);
二、什么是删除分区
删除分区就是直接丢弃整个物理文件，而不是逐行删除数据。
-- 删除整个分区（瞬间完成）
ALTER TABLE orders DROP PARTITION p202401;
实际操作：
- MySQL 找到分区
p202401对应的物理文件orders#p202401.ibd - 直接删除这个文件（类似
rm -f orders#p202401.ibd） - 更新数据字典，标记该分区不存在
执行时间：毫秒到秒级，与分区内数据量无关（100万行和1亿行都是瞬间）
三、DELETE vs DROP PARTITION 对比
DELETE 删除数据的过程
DELETE FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
执行过程：
| 步骤 | 操作 | 耗时 | 数据量影响 |
|---|---|---|---|
| 1 | 扫描索引，找到要删除的行 | 慢 | 随数据量增长 |
| 2 | 逐行标记删除（在 B+Tree 中） | 极慢 | 每行都要操作 |
| 3 | 维护二级索引 | 极慢 | 每个索引都要更新 |
| 4 | 写入 undo log（用于回滚） | 大量 | 每行都写 |
| 5 | 写入 binlog（ROW 格式） | 大量 | 每行都写 |
| 6 | 产生 page 碎片 | 有 | 空间不释放 |
| 7 | 锁住大量行 | 有 | 阻塞业务 |
1亿行数据删除耗时：几十分钟到几小时
DROP PARTITION 删除分区的过程
ALTER TABLE orders DROP PARTITION p202401;
执行过程：
| 步骤 | 操作 | 耗时 | 数据量影响 |
|---|---|---|---|
| 1 | 找到分区对应的物理文件 | 快 | 与数据量无关 |
| 2 | 删除物理文件（系统调用） | 快 | 与数据量无关 |
| 3 | 更新数据字典 | 快 | 极少量 |
| 4 | 写入 binlog（DDL 语句） | 极少 | 只记录一条 DDL |
1亿行数据删除耗时：0.1-1 秒
四、为什么 DROP PARTITION 不产生大量 binlog
DELETE 的 binlog 内容
-- 执行 DELETE 删除 1000 万行
DELETE FROM orders WHERE created_at = '2024-01-01';
binlog 记录（ROW 格式）：
# 每条删除记录都会写入
### DELETE FROM `db`.`orders`
### WHERE
### @1=1001
### @2=123
### @3=299.00
### @4='2024-01-01 10:30:00'
### DELETE FROM `db`.`orders`
### WHERE
### @1=1002
### @2=456
### @3=599.00
### @4='2024-01-01 11:20:00'
... 重复 1000 万次
binlog 大小：约等于被删除数据的大小（可能几个 GB）
传输到从库：需要传输全部 1000 万行的记录
DROP PARTITION 的 binlog 内容
-- 执行删除分区
ALTER TABLE orders DROP PARTITION p202401;
binlog 记录：
# 只有一条 DDL 语句
ALTER TABLE orders DROP PARTITION p202401
binlog 大小：几十字节
传输到从库：只传输这条 DDL 语句，从库执行同样的 DROP PARTITION 操作
五、从库执行的差异
DELETE 方式
主库：逐行删除 1000 万行（几十分钟）
↓ 传输 5GB binlog
从库：逐行重放 1000 万行（几十分钟）
总耗时：主库 30 分钟 + 从库 30 分钟 = 1 小时
期间：从库严重延迟，无法提供读服务
DROP PARTITION 方式
主库：删除物理文件（1 秒）
↓ 传输 100 字节 binlog
从库：删除物理文件（1 秒）
总耗时：主库 1 秒 + 从库 1 秒 = 2 秒
期间：从库几乎无延迟
六、实际对比数据
测试场景：删除 2024年1月的 5000 万条订单
| 维度 | DELETE | DROP PARTITION |
|---|---|---|
| 执行时间 | 45 分钟 | 0.3 秒 |
| binlog 大小 | 12 GB | 200 字节 |
| 主从延迟 | 45 分钟 | < 1 秒 |
| 业务影响 | 锁表，大量超时 | 无感知 |
| 磁盘空间 | 不释放（有碎片） | 立即释放 |
| 能否恢复 | 可回滚（如果事务未提交） | 不可恢复（需从备份恢复） |
| 适用场景 | 少量数据删除 | 批量数据归档删除 |
七、分区删除的实际应用
场景：电商订单保留 6 个月
-- 1. 按月创建分区
CREATE TABLE orders (
id BIGINT,
created_at DATETIME,
...
) PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
PARTITION p202404 VALUES LESS THAN (UNIX_TIMESTAMP('2024-05-01')),
PARTITION p202405 VALUES LESS THAN (UNIX_TIMESTAMP('2024-06-01')),
PARTITION p202406 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01'))
);
-- 2. 每月定时任务：删除6个月前的分区
-- 例如 2024年7月1日，删除 2024年1月的分区
ALTER TABLE orders DROP PARTITION p202401;
-- 3. 同时创建新分区
ALTER TABLE orders ADD PARTITION (
PARTITION p202407 VALUES LESS THAN (UNIX_TIMESTAMP('2024-08-01'))
);
定时任务代码
import schedule
from datetime import datetime, timedelta
def cleanup_old_partitions():
"""每月1日凌晨2点执行"""
# 计算6个月前的日期
cutoff_date = datetime.now() - timedelta(days=180)
partition_name = f"p{cutoff_date.strftime('%Y%m')}"
# 删除过期分区
db.execute(f"ALTER TABLE orders DROP PARTITION {partition_name}")
# 创建下个月的新分区
next_month = datetime.now() + timedelta(days=30)
next_partition = f"p{next_month.strftime('%Y%m')}"
next_date = next_month.replace(day=1) + timedelta(days=32)
next_date = next_date.replace(day=1)
db.execute(f"""
ALTER TABLE orders ADD PARTITION (
PARTITION {next_partition}
VALUES LESS THAN (UNIX_TIMESTAMP('{next_date.strftime('%Y-%m-%d')}'))
)
""")
# 每月1日凌晨2点执行
schedule.every().month.at("02:00").do(cleanup_old_partitions)
八、分区删除的注意事项
1. 数据不可恢复
-- 一旦执行，数据永久消失
ALTER TABLE orders DROP PARTITION p202401;
-- 需要提前备份或归档
-- 先导出数据到历史库
CREATE TABLE orders_history LIKE orders;
INSERT INTO orders_history SELECT * FROM orders PARTITION (p202401);
-- 再删除分区
ALTER TABLE orders DROP PARTITION p202401;
2. 分区键必须是查询条件
-- ✅ 好：查询条件包含分区键 created_at
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
-- MySQL 只扫描 p202401 分区
-- ❌ 差：查询条件没有分区键
SELECT * FROM orders WHERE user_id = 12345;
-- MySQL 扫描所有分区，性能反而更差
3. 分区数量限制
- MySQL 单表最多 8192 个分区
- 按天分区最多支持 22 年
- 按月分区最多支持 682 年
4. 主键必须包含分区键
-- ❌ 错误：主键不包含分区键
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
created_at DATETIME,
...
) PARTITION BY RANGE (YEAR(created_at)) (
...
);
-- ERROR: A PRIMARY KEY must include all columns in the table's partitioning function
-- ✅ 正确：主键包含分区键
CREATE TABLE orders (
id BIGINT,
created_at DATETIME,
PRIMARY KEY (id, created_at) -- 联合主键
) PARTITION BY RANGE (YEAR(created_at)) (
...
);
九、总结
| 维度 | DELETE | DROP PARTITION |
|---|---|---|
| 本质 | 逐行标记删除 | 直接丢弃物理文件 |
| 速度 | 慢（O(n)） | 极快（O(1)） |
| binlog | 记录每一行 | 只记录 DDL |
| 主从延迟 | 严重 | 几乎无 |
| 锁影响 | 锁大量行 | 元数据锁（瞬间） |
| 空间释放 | 不释放（有碎片） | 立即释放 |
| 可恢复性 | 可回滚（事务内） | 不可恢复 |
| 适用场景 | 少量数据、需回滚 | 批量数据、归档清理 |
核心原理：DROP PARTITION 是元数据操作 + 文件系统操作，与数据量大小无关，因此能达到百倍甚至万倍的性能提升，且不产生大量 binlog。
这就是大公司用分区表管理海量数据的核心原因之一。
分区的应用场景
① 日志/事件类数据
用户行为日志、API 调用记录、系统监控指标
特点：只写入，很少修改；查询通常限定在近几天或几周
② 订单/交易流水（历史归档型）
电商订单、支付流水、账单记录
特点：当月数据频繁读写，历史数据只读或很少访问
③ 物联网设备上报数据
设备状态、传感器读数
特点：数据量巨大，按时间查询趋势（"查看某设备过去一个月的数据"）
静，静，静
浙公网安备 33010602011771号