先聊一个真实场景：线上业务跑到一半，运营说要清掉一批三个月前的测试订单，你打开终端，敲完DELETE FROM orders WHERE create_time < '2024-01-01'，回车，0.3 秒后监控平台弹出告警，数据库主从延迟飙升到几十秒，核心业务接口超时。这种事故我见过不止一次，而且每一次的源头，都是同一句话：“DELETE 不是很简单吗？”
MySQL 里删除数据，语法确实简单，背都背得出来。但“会写”和“用对”完全是两码事。这篇东西我打算把 DELETE 从头拆到尾：先讲一条删除语句在 InnoDB 里到底做了什么，再讲各种删除姿势和适用场景，然后是性能、锁、大表分批删除，最后是误删数据后怎么办。面向的是已经被 DELETE 坑过或者即将被坑的开发者，也包括想搞清楚 TRUNCATE、DROP、DELETE 到底什么区别的初学者。看完之后，你至少能回答三个问题：为什么删除慢、为什么空间没变小、为什么明明删了主库还能卡死。
1. 一条DELETE语句在MySQL里是怎么执行的
很多教程会告诉你 DELETE 是“逐行删除”，这个说法对但也不全对。它背后牵扯到 InnoDB 的存储结构、binlog、undo log、purge 线程，一整套机制。理解这些，你才能解释后面所有的坑。
1.1 从语法解析到存储引擎的调用链
一条DELETE FROM t WHERE id = 100发到 MySQL 后，会先经过连接器、解析器、优化器。优化器决定走哪个索引：如果 where 条件里有主键或者普通索引，就用索引定位到那批满足条件的行；如果没有索引，只能全表扫描，把每一行读出来比对。
定位到行之后，Server 层会向 InnoDB 存储引擎发一个“删除这一行”的调用。InnoDB 拿到这个请求，不是直接把物理记录擦掉，而是在这条记录上做了一个“删除标记”，也就是把记录头里的 delete flag 置为 1。这一步同时要把行数据的旧版本信息写入 undo log，为事务回滚做准备。
这个阶段我已经要提醒你留意了：删除不是瞬间物理消失，而是逻辑删除。物理清理要等后面的 purge 线程来做。所以你在一个大事务里删一百万行，undo log 会写得飞快，redo log、binlog 跟着一起写，IO 压力自然就上来了。
1.2 隐藏列、MVCC和purge线程逐个登场
InnoDB 里的每行记录其实有隐藏列：DB_TRX_ID（最近修改事务 id）、DB_ROLL_PTR（回滚指针）、DB_ROW_ID（如果没主键才可能需要）。删除一条记录时，InnoDB 会把这行的 delete flag 置为 1，但不是立刻从聚簇索引的 B+ 树上摘掉。
为什么要保留？因为 MVCC。假设事务 A 删除了 id=100 这一行，还没提交，事务 B 在更早的时候已经开始读，而且它的读视图（Read View）里看不到事务 A 的删除，所以 B 仍然需要读到 id=100 这一行。为了满足这种“读旧版本”的需求，InnoDB 只能把这条记录继续留在索引页里，只是标记成已删除，并通过事务 id 和 undo log 保留旧版本。
等所有活跃事务都不再需要这个旧版本时，purge 线程才会真正清理。purge 会扫描那些打上删除标记的记录，把聚簇索引里不再可见的记录从页面上物理移除，同时清理二级索引对应的条目。这个动作是后台异步的，不是主线程同步完成的。
所以你可以得出一个结论：DELETE 的耗时和事务的大小强相关。你删的行越多，事务越大，undo log 越长，purge 之后的清理越重，期间产生的锁也越多，整个流程自然慢。
2. DELETE的各种删除姿势，你平时用对了几种
我面试的时候喜欢问一个问题：清空一张表用 DELETE 还是 TRUNCATE？能答对的人不少，但能说清楚什么时候必须用 DELETE 的人不多。这一节把操作层面的东西讲透。
2.1 基础DELETE：一定要先搞懂WHERE的边界
最基础的删除：
DELETE FROM t WHERE id = 1; DELETE FROM t WHERE status = 'CLOSED';第一条按主键等值删除，速度最快，锁影响最小。第二条如果 status 字段上有索引，也会走索引定位；如果没有索引，就是全表扫描，每一行都要看一遍是否匹配，这种删除在数据量大时非常危险，一方面慢，另一方面锁的范围会很大。
还有一个常见坑：WHERE条件里对索引列做了函数运算，比如WHERE DATE(create_time) = '2024-01-01'。这样写会让索引失效，MySQL 只能全表扫。正确做法是用范围条件：
DELETE FROM t WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';删除前最好先 SELECT 确认影响范围，这个习惯能救你很多次。
2.2 多表关联删除：DELETE和JOIN的配合
业务上经常会遇到“删掉所有黑名单用户的订单”这类需求，你可能会先查出用户 id 列表，再循环删除。其实 MySQL 支持多表删除，一条语句能完成：
DELETE o FROM mock_orders AS o INNER JOIN mock_users AS u ON o.user_id = u.id WHERE u.status = 'BLACKLISTED';也可以一次删多张表：
DELETE o, l FROM mock_orders AS o LEFT JOIN mock_logistics AS l ON o.id = l.order_id WHERE o.status = 'CANCELLED';注意几个细节：多表删除必须给表起别名，否则容易报错；ORDER BY和LIMIT在多表删除里不能直接用，这是 MySQL 的限制；如果同一张表在 join 里出现两次，注意区分别名，否则会触发 duplicate table alias 的报错。
多表 delete 的本质是：先根据 join 结果筛选出要删的行，再对目标表执行删除。它省掉了应用层多次网络交互，适合一次性删除，但锁的范围会比单表 delete 更大，不建议在高峰期批量跑。
2.3 用ORDER BY + LIMIT控制删除节奏
很多 DBA 喜欢这样写：
DELETE FROM t WHERE create_time < '2024-01-01' ORDER BY id LIMIT 1000;加ORDER BY id是为了让删除有顺序，配合LIMIT把一次删除锁定的行数限制在可控范围。如果不加 ORDER BY，InnoDB 在扫描时可能选择不同的索引路径，导致每次删到的行不连续，效率忽高忽低。用主键排序还有一个好处：配合二级索引定位后回表，顺序性好，减少随机 IO。
但这里有个执行层面的坑：如果 where 条件里的字段和 order by 的字段不是同一个索引，MySQL 可能会先排序，排序的结果再回表删除，而不是边扫边删。极端情况下 LIMIT 反而不能有效限制扫描行数，因为排序要先把所有满足 where 的行都找出来。我习惯的写法是尽量让条件走主键范围，比如：
DELETE FROM t WHERE id < 1000000 LIMIT 1000;这种写法对单一主键自增表非常友好，主键顺序删除，不会产生大量随机 IO。
2.4 DELETE、TRUNCATE、DROP三兄弟分清楚
清理数据时到底用哪个，很多人靠肌肉记忆，我给你整理成一张表：
| 操作 | 类型 | 能否回滚 | 是否走事务 | 自增计数 | 表空间回收 | WHERE条件 | 速度 |
|---|---|---|---|---|---|---|---|
| DELETE | DML | 可以，在事务内 | 逐行 | 不重置 | 一般不回收 | 支持 | 慢 |
| TRUNCATE | DDL | 不可以 | 隐式提交 | 重置 | 大概率回收 | 不支持 | 快 |
| DROP | DDL | 不可以 | 隐式提交 | 表都没了 | 回收 | 不支持 | 最快 |
TRUNCATE 在 InnoDB 里的实现是：把表的数据页直接重新初始化，相当于“把整个表的数据区域重置”，所以速度远快于 DELETE 逐行删。但要小心两点：如果表被外键引用，TRUNCATE 会失败；如果有 ON DELETE 触发器，TRUNCATE 不会触发逐行删除的触发器，这个是很多人没注意到的。
另外，TRUNCATE 之后自增列计数会重置，DELETE 不会。如果你清空一张表后希望 id 从 1 开始，用 TRUNCATE；如果不希望旧的自增值被复用，用 DELETE。
DROP 就直接多了，连表结构和数据文件一起干，MySQL 里删表最快的操作。恢复基本只能靠备份。
3. DELETE怎么就会把线上搞卡：性能与锁的问题
回到开头那个场景：一条 DELETE 把线上搞卡。这节专门讲性能和锁，也是很多人面试时讲不清楚的部分。
3.1 大事务和锁范围失控
InnoDB 的行锁机制决定了：你执行 DELETE 时，所有扫描过的行都会被加锁，具体加什么锁要看隔离级别。在可重复读（RR）级别下，如果 where 条件命中二级索引，InnoDB 还会使用 next-key lock，也就是不仅锁住命中的行，还会锁住索引范围内的“间隙”，防止幻读。
这意味着什么？你一条 DELETE 如果满足条件的行有一万个，实际锁住的可能是这一万行加上它们周围的一段间隙。如果这个范围很大，其他事务想插入、修改、删除这些范围内的记录，都要乖乖等着。长事务持有锁的时间越长，锁竞争越严重。
更麻烦的是，MySQL 在执行删除时不仅锁目标行，还会锁参与扫描的其他行。如果你没走索引，全表扫，那就是表锁级别的体验，虽然 InnoDB 是行锁引擎，但这条 DELETE 的低效执行会让很多无关查询被阻塞，表现为 CPU 飙高、大量线程处于 Waiting for lock。
3.2 为什么删了一百万行，主从延迟还是很高
主从架构里，主库执行 DELETE 是在本地事务里完成的，从库是通过 binlog 回放来复制数据的。默认 binlog 格式如果是 ROW，一条 DELETE 语句删除一百万行，binlog 里会记录这一百万行被删除前后的完整数据。你猜从库回放这个巨型事务要多久？
主库拿到了硬件资源、并行优化，从库往往还是单线程或者多线程并行度有限地回放。所以一旦主库执行大 DELETE，从库延迟立刻就会拉大。如果业务读流量都打到从库，从库同步延迟会让主从数据不一致，读出来的数据是删前还是删后全看命。
分批删除配合SLEEP的本质就是：把一个大事务拆成多个小事务，每个事务最多删几百行或一两千行，让从库能在一小段事务之间喘口气，把延迟控制在可接受范围。这个思路非常朴素，但在生产环境极其有效。
3.3 删除后表空间为什么没变小
很多人删除掉几千万行之后，发现磁盘空间一点没少，以为是 BUG。其实这是 InnoDB 的常见表现。前面说了，DELETE 只是逻辑删除，物理空间还在数据页里。purge 线程清理后，这些空间只是被标记为“可复用”，但不会被自动返还给操作系统。
如果你希望删完之后真正把空间还给磁盘，常规手段是：
OPTIMIZE TABLE t; ALTER TABLE t ENGINE = InnoDB;这两个操作相当于重建表，会把表中现有的有效数据复制到新表里，释放掉大量碎片空间。但注意：重建表的过程需要额外磁盘空间，而且会持有元数据锁，必须在业务低峰期操作。表如果非常大，耗时以小时计，中间还不能随便中断，否则回滚更麻烦。
经验是：如果删除的数据占比很小，没必要重建；如果删掉了表里大部分数据，腾出的空间又很紧张，才考虑重建，而且要先评估磁盘余量。
4. 大表数据删除的分批实践
讲到这里，该怎么在生产环境安全地删大数据，基本呼之欲出：分批删。我给出一套我在实际项目里用过的方案，配合存储过程可以直接抄。
4.1 为什么要分批，以及批次大小怎么定
分批的核心原因说了两遍：控制事务大小、避免锁范围过大、缓解主从延迟。那一批删多少合适？没有绝对标准，取决于你的表行宽、索引数量、服务器 IOPS。我的经验值：普通订单表、日志表，单批 500 到 2000 行比较稳；如果是行很宽的大字段表，单批 200 到 500 行就够了。
判断是否合适，看三个指标：删除耗时的 P99、主从延迟增长值、InnoDB 锁等待次数。如果删除 1000 行耗时从几十毫秒飙到几百毫秒，说明该降低批次；如果主从延迟每分钟都在涨，说明批之间间隔太短，要加大 SLEEP 时间。
4.2 一套完整的批量删除存储过程
我们拿模拟订单表mock_orders举例：要删除一年前的已关闭订单，表里有 id 主键、order_time 索引。可以写这样一个存储过程：
DELIMITER $$ CREATE PROCEDURE sp_batch_delete_orders() BEGIN DECLARE v_rows INT DEFAULT 1; WHILE v_rows > 0 DO DELETE FROM mock_orders WHERE order_time < DATE_SUB(NOW(), INTERVAL 1 YEAR) AND status = 'CLOSED' ORDER BY id LIMIT 1000; SET v_rows = ROW_COUNT(); COMMIT; DO SLEEP(0.2); END WHILE; END$$ DELIMITER ;注意几个设计细节：
ROW_COUNT()返回上一条 DELETE 实际影响的行数，当它等于 0 时说明没有满足条件的行，循环结束。ORDER BY id让每次删除的都是最小的那一批 id，避免随机扫描。COMMIT是确保每个批次独立提交，避免在过程中累积一个超长事务。DO SLEEP(0.2)是给主从同步留出回放时间，时间长短按实际延迟调整。- 如果表非常大，直接
WHERE order_time < ... AND status = 'CLOSED'走索引没问题；但如果条件筛选率很低，建议改成基于主键范围循环，比如每次WHERE id BETWEEN ? AND ?，然后逐步推进。
存储过程跑起来之后，不要直接放着不管。我会另开一个终端观察SHOW PROCESSLIST，确认这个存储过程没有长时间占用锁；再盯一下从库的Seconds_Behind_Master，一旦延迟超过预期，立刻KILL对应的存储过程连接，调整参数再重试。
4.3 比存储过程更灵活的方案：脚本循环
存储过程方便，但很多公司出于管控考虑，不允许在数据库里直接创建存储过程。这时候可以把循环逻辑放在脚本里，比如用 Python 脚本连库执行：
import time import pymysql conn = pymysql.connect(host="127.0.0.1", user="app", password="***", database="mock_db") while True: with conn.cursor() as cur: cur.execute( """ DELETE FROM mock_orders WHERE order_time < '2024-01-01' AND status = 'CLOSED' ORDER BY id LIMIT 1000 """ ) deleted = cur.rowcount conn.commit() if deleted == 0: break time.sleep(0.2) conn.close()脚本方案的好处是：可以在循环里加实时监控、日志、超时控制，甚至可以把批次大小做成动态调整。坏处是多一次网络往返，删除速度会比存储过程慢一点，但对线上安全来说，慢就是稳。
5. 常见故障排查和避坑实录
最后把实战里最容易踩到的坑集中放出来，每一类我都给出排查思路和尽量避免的措施。
5.1 DELETE卡住的排查链路
删除语句执行半天不动，第一反应不是干等，而是查锁。最直接的是看performance_schema里的锁等待：
SELECT * FROM performance_schema.data_lock_waits;或者用SHOW ENGINE INNODB STATUS看当前事务和锁等待片段。定位到阻塞者是哪个事务后，如果能确认业务低峰、阻塞事务无关紧要，可以KILL掉对应的线程 id。但千万要谨慎：kill 事务会触发回滚，如果那个事务已经删了很多行，回滚本身也会非常耗时。
排查原因时要注意一个细节：长事务持有历史版本。即使你的 DELETE 语句本身很快，之前某个未提交的事务可能一直持有一个旧快照，导致 purge 线程无法清理已删除的记录，表膨胀、后台 IO 变高。这种情况的根源不在 DELETE，而在“长时间不提交的事务”。
5.2 误删数据后的应对思路
误删是个大话题，我先说思路：发现误删之后，第一时间隔离写流量、停掉可能继续覆盖数据的任务，然后看备份和 binlog。常规恢复路径是：从最近的全量备份恢复到临时实例，再用 binlog 把删除点之后的数据回放到目标时间点。
这里有个细节：DELETE 的 binlog 在 row 格式下记录了每一行的原始内容，所以理论上可以通过解析 binlog 把删除的行“反向生成”成 INSERT 再回放。这个操作很考验功底，不建议在慌乱的线上环境中现场做。平时就应该演练过从备份恢复的流程，等真出事的时候照着步骤执行就行。
我自己每次上线删除任务前都会做三件事：先把要删除的条件写成 SELECT，确认影响行数；然后在测试环境把同样的语句跑一遍，观察锁和耗时；最后在正式环境用一个事务包裹，先 DELETE 后验证行数，再决定 COMMIT 还是 ROLLBACK。
START TRANSACTION; DELETE FROM mock_orders WHERE id BETWEEN 10000 AND 20000; SELECT ROW_COUNT(); ROLLBACK;这一步只付出很少的成本，却能让你在真正执行删除前对影响范围心里有数。
5.3 一个常被忽略的细节：DELETE和批量插入的锁竞争
最后分享一个小经验。大量删除任务跑完之后，数据库的空闲页会变多，但这些页是“可复用”状态。如果紧接着有一批高频插入任务，InnoDB 会优先复用这些空闲页，插入性能可能暂时变差，因为页内有很多已删除记录的“空洞”，插入时需要维护页内的有序性。这不是 bug，但会让你在删除后的短时间内观察到写入延迟轻微上升。
遇到这种场景，我的处理方式是把大批删除安排在业务低峰期的末尾，给数据库一段时间让后台 purge 和自适应哈希索引调整完，再进入业务高峰。如果删除任务特别重，干脆在删除结束后低峰执行一次OPTIMIZE TABLE，把碎片修整干净，代价是额外时间和磁盘开销，但收益是后续写入稳定。
说到底，DELETE 从来不是“写完就行”的语句。它和事务、锁、复制、存储结构绑定在一起。你把原理弄透了，自然就知道一条删除在生产环境该怎么写、怎么写才安全。
