企业官网建设流程全解析

如果你手上有张表，里面躺着上亿条历史数据，有一天突然需要清掉大半，第一反应是不是写一条DELETE FROM 表名 WHERE create_time < '2024-01-01'？我直接说结论：生产环境这么做，十有八九要出事。轻则锁表拖垮线上查询，重则事务日志撑爆磁盘、主从复制中断，极端情况直接把实例搞挂。今天我想把这几年在 MySQL 批量删除海量数据上踩过的坑，以及实际验证过可行的几种方法，一次性讲透。文章会覆盖主键分片、时间字段分片、新表替换、分区表删除四种主流方案，还会附上生产环境的踩坑实录和排查思路，适合正在做大表清理的后端开发、DBA，以及所有被“删数据”折磨过的同行。

先说清楚一个观念：海量数据删除的目标从来不是“删得快”，而是“删得稳”。这里说的稳，指的是不影响线上读写、不拖垮主从、不撑爆磁盘、随时能暂停能恢复。所以下面所有方法，都是围绕“稳”来设计的。

1. 为什么“直接 DELETE 大表”会出大问题：批量删除的设计思路

1.1 一条大 DELETE 在 InnoDB 里到底干了什么

很多人以为 DELETE 就是把数据抹掉，其实在 InnoDB 里一次大范围删除的代价远超你想象。我拆开来讲：

第一，锁的范围会不断扩大。InnoDB 默认是行级锁，听起来很美好，但一条DELETE WHERE create_time < '2024-01-01'如果匹配了上千万行，引擎需要逐行加锁。在 READ COMMITTED 隔离级别下锁会分批释放，但在 REPEATABLE READ 下，删除大量行时锁和 undo 的维护开销非常大，而且相邻记录之间的间隙锁还可能互相干扰。实际表现就是：你执行 DELETE 后没多久，其他查询全部堵在“Waiting for table metadata lock”或者锁等待上。

第二，undo log 和 binlog 会疯狂膨胀。InnoDB 为了保证事务回滚，每删除一行就会在 undo log 里记录原始数据。删除 5000 万行，undo 就是 5000 万条完整行记录。binlog 在 ROW 格式下更恐怖，会记录每行删除前后的完整镜像。我见过一个实际案例，删了 2 亿行，binlog 直接写了几百 GB，把数据盘撑满了。这不仅仅是空间问题，还会让主从复制从库需要回放巨大的 binlog，延迟一路飙升。

第三，buffer pool 里的热数据会被大量置换。你要删除的行如果正好是刚被查询过的热数据，InnoDB 要把这些页读入 buffer pool，然后标记删除、做 purge。大量脏页刷盘会导致磁盘 IO 飙升，线上查询响应时间瞬间恶化。

第四，最容易被忽略的是：一个超大事务长时间不提交，会阻塞 purge 线程。而 purge 线程负责清理已删除记录的版本链。它一旦被阻塞，undo log 就不能及时回收，表空间越撑越大，后续所有更新操作的性能都会被拖累。

所以你看，一条 DELETE 语句背后牵扯的是锁、日志、内存、IO 四个层面的连锁反应。直接删大表等于把压力全部瞬间砸到实例上，生产环境根本兜不住。

1.2 批量删除的核心设计原则：分片、限速、可观测

既然一次性删除不行，那就把大事务拆成很多个小事务，每个事务只处理一小批数据。这就是“分片删除”的基本盘。

我总结成四个原则，新手照着做基本不会翻车：

• 分片原则：每次删除的数据量要控制在一个安全范围内。这个范围由你的行大小、实例配置、binlog 大小共同决定，通常每批 500 到 5000 行是比较稳的起步区间。
• 限速原则：两批之间加一个 sleep 间隔，给主从复制留出回放时间，给磁盘留出刷盘时间。一批“咔咔”删完立刻下一批，和一次性猛删没有本质区别。
• 窗口原则：把任务放在业务低峰期执行。如果业务随时都有高峰，就要做成可控并发 + 可暂停的脚本，不能一把梭跑完。
• 可观测原则：明确知道当前删到了哪个位置，进度是多少，主从延迟和磁盘空间的变化趋势如何。千万别闷头执行，等到报警了才发现出了问题。

这四个原则我几乎在任何一次批量清理中都会用到，它们不绑定某个具体方案，是通用的方法论。接下来的四种方法，本质上都是在这四个原则上做出不同侧重的实现。

2. 最通用的方案：基于主键 ID 的分批删除

2.1 为什么优先用主键 ID 分片

如果表的主键是自增 ID，那这是最理想的分片键。原因有三：主键天然是聚簇索引，按 ID 范围过滤可以走索引直接定位到数据页，效率最高；所有 InnoDB 表都必须有主键，不存在“没有主键”的场景（如果没有显式主键，MySQL 会隐藏生成一个，但你也用不到）；用 ID 作为游标记录进度非常自然，删到哪个 ID 一清二楚，重启脚本可以从断点继续。

哪怕你是按时间条件来筛选要删除的数据，也建议先把“要删的 ID 范围”算出来，再按 ID 分片，而不是直接按时间范围去 DELETE。原因后面在第三节讲时间分片时你会更明白。

2.2 常用 SQL 模板与参数计算

标准做法是：先查目标数据的最小和最大 ID，然后在一个循环里，每次删除一个固定 ID 区间。

我先给一个模板，这是去掉存储过程包装后的核心 SQL：

-- 先定位要删除的数据范围 SELECT MIN(id), MAX(id) FROM your_table WHERE create_time < '2024-01-01'; -- 假设这个范围是 1000000 到 50000000 -- 每次删除 5000 行的 ID 区间，从最小值开始向前推进 DELETE FROM your_table WHERE id BETWEEN 1000000 AND 1004999 AND create_time < '2024-01-01';

你可能会问，为什么WHERE id BETWEEN后面还要加create_time条件？这是为了防止在这个区间内混入了不该删的数据，也防止边界区间的数据在上一批删除后，新插入的数据的 ID 落入了下一批的范围。说白了就是给删除条件加一道保险。

每批多少行合适？

这个值没有标准答案，我一般按下面几个口径去估：

• 估算单行大小。比如一行 0.5 KB，5000 行数据大约是 2.5 MB，对应的 undo 和 binlog 大约是这个的几倍，也就是十几 MB 的事务量，对大多数实例来说比较安全。
• 如果你的业务对锁非常敏感，起步先用 500 行测试，观察主从延迟和 CPU 波动，稳定后可以逐步加到 1000、2000。
• 有个上限提示：单批别超过 1 万行。超过 1 万行，事务耗时和锁持有时间会显著变长，碰到高峰期容易引发锁等待。

两批之间 sleep 多久？

这个必须结合主从延迟来判断。如果从库是秒级同步，硬件较好，sleep 100 毫秒就行。如果主从延迟已经在上涨，sleep 至少 300 到 500 毫秒，给从库追平的机会。我实际用下来，1000 行一批 + 200 毫秒 sleep，对一个中等配置的实例来说是比较舒服的节奏。

下面是一个可以用 Python 脚本控制的示例片段，很多 DBA 倾向用外部脚本来控制而不是塞进存储过程，因为便于打印进度、控制启停：

import time import pymysql conn = pymysql.connect(host="localhost", user="app_user", password="your_pass", database="your_db") cursor = conn.cursor() # 获取要删除的最小和最大id cursor.execute("SELECT MIN(id), MAX(id) FROM your_table WHERE create_time < '2024-01-01'") min_id, max_id = cursor.fetchone() batch_size = 1000 current_id = min_id while current_id <= max_id: end_id = min(current_id + batch_size - 1, max_id) sql = """ DELETE FROM your_table WHERE id BETWEEN %s AND %s AND create_time < '2024-01-01' """ cursor.execute(sql, (current_id, end_id)) conn.commit() print(f"deleted id range: {current_id} - {end_id}, affected: {cursor.rowcount}") current_id = end_id + 1 time.sleep(0.2) # 控制删除速率

这个脚本的生产可用版本还需要加异常重试、进度落表等逻辑，但核心就是这个循环结构。用外部脚本还有个大好处，程序崩溃后可以记录current_id，重启后从断点继续，而不用从头开始扫范围。

2.3 实操要点与避坑

第一种方案虽然最通用，但有几个细节不注意照样翻车。

• 先确认隔离级别。如果是 REPEATABLE READ，删一批数据也要小心间隙锁。实际中我有一次在默认隔离级别下跑删除，业务侧插入刚好卡在被删区间的边界，堵了十几秒。后来把会话隔离级别改成 READ COMMITTED，锁竞争明显好转。改法很简单：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;。
• 不要用DELETE FROM table WHERE create_time < ... LIMIT 1000。MySQL 的 DELETE 支持 LIMIT 语法，但它的执行计划往往是先找到满足 WHERE 条件的行然后随机取 1000 行删掉，没有稳定的游标边界，你可能反复删除到同一批数据，而且性能很差，因为每次都要重新扫描定位开头的 1000 行。除非表有合适的索引能让优化器稳定走到边界，否则不建议。
• 进度表很重要。如果删除任务会持续几小时，建议在另一个库里建一张简单的 progress 表，记录当前删到了哪个 ID、每批耗时、遇到错误次数。这样即使脚本崩了、终端关了，也能查进度并安全续跑。
• 回调压力。如果删的是订单、流水这类业务表，应用层可能会有缓存或统计依赖，删完一批后线上本来查得到的数据突然少了，要提前跟业务侧打招呼。

3. 按时间字段分片：日志表和流水表的清理套路

3.1 什么时候该用时间字段而不是主键分片

业务表的主键并不总是按时间递增的。比如订单号可能是雪花算法生成、业务单号是随机字符串，这时候你没法用主键来划定“哪些是更早的数据”，只能靠create_time来筛选。日志表、流水表基本都属于这种。

处理这类表，最容易踩的坑是：直接在create_time上 DELETE 而没有合适的索引。如果没有索引，一次 DELETE 要全表扫描，先不说锁的问题，光是扫描上亿行就够你受的。所以前提条件是先确认create_time上有索引，最好是一个(create_time, id)的复合索引。加 id 列不是闲得慌，是为了让每批删除的边界更精准，排序更稳定，也方便和主键分片方法打通。

3.2 用时间范围加 LIMIT 的存储过程实现

有了索引之后，最稳的做法是：一次不要删除整个时间范围，而是用LIMIT限制住每批行数，配合一个不断向前推进的时间游标。

先看一个通用模板，这是存储过程版本的思路：

DELIMITER $$ CREATE PROCEDURE clean_expired_logs(IN cutoff_time DATETIME, IN batch_size INT) BEGIN DECLARE done INT DEFAULT 0; DECLARE affected_rows INT DEFAULT 0; DECLARE v_start DATETIME; DECLARE v_end DATETIME; -- 先拿一条最老的数据，作为本次删除的起始游标 SELECT MIN(create_time) INTO v_start FROM log_table WHERE create_time < cutoff_time; SET v_end = v_start; WHILE v_end IS NOT NULL AND v_start < cutoff_time DO -- 每批往前推进一个时间点，但通过 LIMIT 控制行数 DELETE FROM log_table WHERE create_time >= v_start AND create_time < DATE_ADD(v_start, INTERVAL 1 HOUR) ORDER BY create_time, id LIMIT batch_size; SELECT ROW_COUNT() INTO affected_rows; -- 如果这一小时内的数据还没删完，v_start 不变，下一批继续清 -- 如果删完了，v_start 推进到下一小时 IF affected_rows < batch_size THEN SET v_start = DATE_ADD(v_start, INTERVAL 1 HOUR); SET v_end = v_start; END IF; -- 每批提交，避免长事务 COMMIT; -- 限速 DO SLEEP(0.2); END WHILE; END$$

我来说说这个流程里几个关键设计的原因。

一是“按小时推进”不是固定死的。如果你的日志表一天的数据量很大（千万级），按小时推进会导致单批可能超出 LIMIT 太多，锁范围变大；如果一天数据量很小，按小时推进又会让删除粒度太粗（一批就删光一天的数据，事务还是很大）。所以时间间隔要根据数据密度来定，我可以先做聚合查询，看每个小时的数据量，再决定用分钟还是小时作为推进梯度。

二是就算按时间区间推进，也一定要带LIMIT。因为数据分布不均匀，你以为这个小时就几万行，结果某次促销堆了百万行，没 LIMIT 就等于一次删超大事务。无论时间范围怎么切，LIMIT 始终是最后一道保险。

三是ORDER BY create_time, id的作用。它保证同一个时间区间内按稳定的顺序删除，避免并发插入导致同一批数据被反复扫描。

3.3 游标方式的适用场景与局限性

有些教程会推荐用游标一行一行删，类似DECLARE cur CURSOR FOR SELECT ...，然后 FETCH 一行 DELETE 一行。我必须说，这种方式只适合删除量很小的场景，比如几万条。因为游标循环本身有解释执行开销，而且每次 DELETE 会产生一次独立的行操作，效率极低。删除百万行以上，游标方式往往比直接 DELETE 还慢，还更容易产生碎片。

我见过的唯一适用场景是：数据稀疏分散，必须根据某些复合业务规则判断这一行删不删（比如某个状态字段加上时间字段共同决定）。这种情况下游标做的是业务过滤，不是批量清理，所以不能跟批量删除画等号。如果你是单纯按时间清理，别用游标。

4. 新表替换法：不改索引不积压事务的“搬家式”清理

4.1 思路：把“删除数据”变成“保留数据”

如果说前两种方案是“原地清理”，那这一种就是“搬走重来”。思路很简单：与其在原来的大表上删掉大量行，不如建一张新表，只把需要保留的数据拷进去，然后通过改名把两张表互换，最后删掉旧表。这种方式在“删除数据占总数据量的大部分”时特别有效。

举个例子：你的表有 1 亿行，只需要保留最近 3 个月的 1000 万行。直接 DELETE 9000 万行，参数上可能要走几小时甚至几天，而且过程中索引、锁、日志的压力全部真实作用于在线表上。但新表替换法的思路是只迁 1000 万行，迁移过程虽然也有写压力，但整体更可控，而且迁移完旧表直接 DROP，不需要再做大量 UPDATE 统计、OPTIMIZE TABLE 之类的收尾工作。

4.2 具体操作五步走

我自己按下面的顺序操作，出错的概率最低：

第一步，创建同结构的新表：

CREATE TABLE your_table_new LIKE your_table;

LIKE会完整复制原表的列定义、索引、约束。注意，它不会复制自增计数器的当前值，所以切换后如果有自增列，别忘了设置一下 AUTO_INCREMENT，否则可能新插入数据时主键冲突。

第二步，分批把要保留的数据插入新表：

INSERT INTO your_table_new (col1, col2, col3, ...) SELECT col1, col2, col3, ... FROM your_table WHERE create_time >= '2024-01-01' AND id BETWEEN ? AND ?;

这一步最关键。很多人图省事直接INSERT INTO ... SELECT * FROM ... WHERE 保留条件，一次性迁几百万行，长事务、锁竞争、undo 膨胀的问题全部回来了。必须分批迁移，每批几万行或者按时间窗口一段段迁。

第三步，原子切换表名：

RENAME TABLE your_table TO your_table_old, your_table_new TO your_table;

RENAME TABLE 在 MySQL 中是原子操作，执行瞬间只会有一个极短的元数据锁窗口，业务无感知或者感知极小。这一步是把旧表退位，新表顶上。

第四步，确认业务正常后删除旧表：

DROP TABLE your_table_old;

删除旧表是很重的操作，因为要清理表定义、数据页、索引，磁盘 IO 和 buffer pool 都会有波动。这里可以分几步：先TRUNCATE旧表（如果已不再需要），把数据页释放，再 DROP，能明显减轻最后 DROP 的瞬时压力。

第五步，检查依赖对象。视图、触发器、存储过程、外键这些对象在切换后都要重新核对。比如视图如果定义了具体的表名，切换后需要重新创建或者修改定义；触发器同理；如果这张表有外键被其他表引用，RENAME 可能会影响引用关系，需要提前连同外键一起处理。

4.3 新表替换法该不该用，先回答这几个问题

这个方法好，但不是万能药。我在评估时通常会问自己几个问题：

• 业务能不能接受一个短暂切换窗口？虽然 RENAME 本身很快，但从“新表开始接收写入”到“旧表被废弃”之间，应用可能还连着旧表连接。如果应用有数据库连接池，连接池里的旧连接可能仍然指向旧表，或者因为表名变化导致后续 statement 报错。线上切换前，最好让应用侧的 SQL 层支持自动重连，或者安排一个非常短的维护窗口。
• 切换期间如果有写入怎么办？如果写入不能停，方案就复杂了。要么用透明切换代理，要么在切换前把写入流量切到新表，但应用代码要改表名，这在大型系统里不是随时能做的。所以维护窗口很重要——哪怕只停 10 秒，也要提前说明。
• 新表数据迁移期间，旧表还在线上服务吗？在迁数据的过程中，旧表还在正常读写，业务会继续往旧表插入新数据。你迁移完旧表数据、切换到新表后，这些“切换前最后一两分钟写入的数据”是否已经同步到新表？如果不能，就要设计一个“增量追平”的步骤。

解决“增量追平”有个土办法：记录切换前最后一批插入数据的 ID 范围，在 RENAME 前再补迁一次这个增量范围，或者干脆在切换时停写几秒，以确保新表数据完整。对于可以接受短停写的场景，这个方案最简单可靠。

4.4 什么时候千万别用新表替换法

有两类场景我强烈建议放弃这个方法。

一类是保留数据占大多数。比如 1 亿行要删 1000 万行，留 9000 万行，那不如直接在原表删除，因为拷贝 9000 万行的代价远高于删除 1000 万行。 另一类是表上有大量外键或复杂关联。RENAME 后，外键关系需要全部重建，涉及对象很多，操作风险极高。

5. 分区表删数据：如果一开始设计对了，就是最省心的

5.1 分区表为什么删得快

分区表是我个人最喜欢的方案，但前提是建表时就想好了数据生命周期管理。比如日志表按月份建 RANGE 分区，每个月的数据落在一个独立分区里。删除一个月的历史数据时，执行：

ALTER TABLE log_table DROP PARTITION p202403;

这个操作本质上是在物理文件层面删除一个分区，InnoDB 只需要丢弃那部分数据和索引页，不需要逐行记录 undo、不需要逐行加锁、binlog 里也只是一条 ALTER 记录。执行速度比 DELETE 快好几个量级，而且对在线业务的影响小得多。

如果不想删除分区，只想清空某个分区的数据但保留分区结构，可以用：

ALTER TABLE log_table TRUNCATE PARTITION p202403;

TRUNCATE PARTITION 的效果相当于把这个分区的数据文件重置，同样不会产生大量行级 undo 和 binlog，比 DELETE 快很多。

5.2 建表时的分区设计要点

如果你现在要设计一张未来会持续增长的数据表，我建议认真考虑分区方案。

一个典型的按月分区建表语句：

CREATE TABLE audit_log ( id BIGINT NOT NULL AUTO_INCREMENT, created_at DATETIME NOT NULL, content TEXT, PRIMARY KEY (id, created_at) ) PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) ( PARTITION p202401 VALUES LESS THAN (202402), PARTITION p202402 VALUES LESS THAN (202403), PARTITION p202403 VALUES LESS THAN (202404), PARTITION p202404 VALUES LESS THAN (202405) );

这里的关键限制是：分区键必须包含在主键中。所以上面我把主键从单纯的id改成了(id, created_at)。如果原表主键是id，要想按时间分区，必须先调整主键结构，这是个有一定改造成本的操作，经常被开发者忽略。

另一种更简单的分区方式是直接用TO_DAYS()函数做范围分区，逻辑清晰但要注意函数表达式在分区剪枝时能否被优化器利用。

不要图方便直接分区到天：分区数量太多会导致表元数据管理开销变大，太多小分区会把性能反而拖差。日志类数据按月分区是黄金选择，查询频率高而数据量大的业务可以按周或按小时，但别轻易超过 200 个分区。

5.3 老表改造的可行路径

如果一张表已经上亿行但当初没建分区，是否值得改造？要回答这个问题，需要看业务是否允许操作期间有锁表窗口。做ALTER TABLE ... PARTITION BY ...在大部分版本里会重建表，执行期间全程锁写，线上服务几乎要停摆。如果只是清理历史数据而不要求长期保留分区结构，我反而更建议直接用第二、第三种方法。

还有一种变通做法：新建一张分区表作为归档表，把旧表的旧数据按分区迁移到归档表，原表保留近期数据。这样至少让后续的定期清理走进分区方案的快车道，不用每次都在老表上做大 DELETE。

5.4 分区表的常见误区

分区不是越多越好，这个我前面提了。还有一个常见误区是认为分区表查询一定会更快。实际上分区的主要价值是便于生命周期管理（快速删除和归档），而不是查询加速。如果查询没有带上分区键，优化器仍然会扫描全部分区，速度不会比普通表快，甚至因为分区元数据管理反而略慢。

另一个坑是跨分区更新。如果你按created_at分区，但业务上允许修改created_at字段，那么一次 UPDATE 可能触发数据从分区 A 移动到分区 B，这个操作代价很高。如果有这种需求，建议把分区键设计成业务中不会被修改的字段，比如日志的写入时间戳。

6. 生产环境踩坑实录与排查提速手册

6.1 删除过程中的典型故障速查表

这张表是我觉得全文最值得收藏的部分，几乎每一条我都真实遇到过。尤其是“删除完表空间没有变小”这条，很多新手以为删完几千万行，磁盘肯定释放了几十 GB，结果ls -lh一看文件大小没变，顿时慌了。这是因为 InnoDB 的表空间文件不会自动收缩，删除产生的空闲页会留给后续插入复用。如果非要立即收缩，只能走 OPTIMIZE 或者新表替换法。

6.2 一个历史订单删除案例的完整复盘

我来讲一个印象很深的案例。那是一个模拟环境的电商订单表，单表约 1.5 亿行，保留最近一年数据，需要删除约 1.3 亿行历史订单。

第一轮尝试直接DELETE WHERE order_time < '2022-06-01'，跑了 10 分钟后锁死了。业务侧反馈部分接口超时，主从延迟也飙到几百秒。我立即 KILL 了删除会话，但回滚花了差不多半小时，那会儿业务问询电话一直没停。

复盘后改用主键分片方案：先查出要删除的数据中主键的最小值和最大值，每批 800 行，服务端 sleep 0.3 秒，设置 session 隔离级别为 READ COMMITTED，并把任务放在凌晨低峰执行。

实际跑起来，速度并不是一直恒定。刚开始每批 800 行耗时只有几十毫秒，跑到中后段因为表膨胀、页分裂等因素，单批耗时上升到几百毫秒。于是我加了监控脚本，每 30 秒抓一次主从延迟，一旦超过 10 秒就自动 sleep 2 秒。最终整个删除过程大概用了 4 小时，删完 1.3 亿行，主从延迟峰值控制在 15 秒以内，没有影响白天业务。

这个案例给我的最大教训是：测试环境先演练不是可选项，是必选项。当时如果先在测试环境摸清每批删除对主从和锁的影响，就不会贸然上一条大 DELETE。现在我做任何批量删除，都会先在测试环境模拟线上数据量，用脚本打点计算每批耗时、IO 波动，再算出适合的参数组合，才会动线上。

7. 最后分享几个我常用的操作习惯

做批量删除这么多次，有些习惯已经固化下来了，写在这里供你参考。

第一，任何批量删除开始前，先确定备份策略。至少要有最近一次全量备份，并且确认 binlog 保留天数足够恢复。这个步骤不能省。

第二，删除期间别关监控。从库延迟、磁盘空间、CPU 负载、锁等待四个指标至少每 30 秒看一次。一旦发现趋势不对，第一时间暂停而不是调参数硬扛。

第三，如果删除任务持续时间很长，考虑把运行和管理分开：负责删除的脚本只管删，负责监控的脚本只管监控，两者通过进度表同步信息。这样某个脚本出问题，另一个还能给出有效报警。

第四，小批量多批次一定优于大批量少批次。刚开始跑的时候宁可慢，也要先把稳定性验证出来。删到一半发现参数不行，停下来改参数的成本远高于一开始多等几分钟。

最后一个小技巧：如果你要删除的数据占了表的一大半，与其拼命调 DELETE 参数，不如换个思路走新表替换法。我见过太多人花几个小时调分片大小，最后发现只要“搬走保留数据”五分钟就完事了。方案选型永远比参数调优更值钱。