MySQL/MariaDB DELETE语句的事务性及大数据量删除性能问询

Hey，这两个问题都是日常维护MySQL/MariaDB时非常常见的坑，我来给你拆解清楚：

Q1：MySQL/MariaDB中的DELETE语句是否作为完整事务执行？若需删除100万行数据，当删除过程中出现异常时，已删除的行是否会回滚恢复？ #

核心要看你用的存储引擎，这是决定一切的关键：

• 如果是InnoDB（现在MySQL/MariaDB的默认引擎，也是生产环境的推荐选择）：
  • 默认autocommit参数是开启的，这时候单条DELETE本身就是一个隐式事务，具备原子性——要么整批删除完全成功，要么执行中途出了异常（比如数据库崩溃、连接中断），所有已经删掉的行都会被回滚，数据恢复到执行前的状态。
  • 要是你显式开启了事务（比如先敲BEGIN;再跑DELETE），那这条DELETE就是事务的一部分，只要没执行COMMIT;，任何异常都会触发整个事务回滚，已删的数据同样会原样恢复。
• 如果是MyISAM这类不支持事务的老引擎：
  • 完全没有事务保障，删到一半出问题的话，已经删掉的行就永久丢失了，找不回来。现在基本没人用MyISAM了，建议赶紧换成InnoDB。

所以针对100万行的删除场景，只要用的是InnoDB，不管是单条DELETE还是在事务里操作，异常后已删的数据都会回滚；但MyISAM就没这个兜底能力。

Q2：删除数据时应使用单条DELETE语句（无论数据量大小）还是添加LIMIT分批删除？若需分批，每次删除的行数阈值应为多少？ #

绝对别用单条DELETE删大量数据（比如100万行），坑太多了：

• 会长时间占用锁（InnoDB本来是行锁，但如果DELETE的WHERE条件没用到索引，会直接升级成表锁），其他业务读写这个表都会被阻塞，严重影响服务可用性；
• 生成的undo日志会异常庞大，不仅占用磁盘空间，还会拖慢后续的事务提交和回滚操作；
• 大事务会导致主从复制延迟爆炸，从库要回放整个大DELETE的日志，耗时能拉得很长。

所以必须分批删除，至于每次删多少行，没有固定的标准答案，得结合你的服务器配置和业务繁忙程度来调整，给你几个实用参考：

• 常规配置服务器（4核8G内存，业务中等繁忙）：每次删1000-2000行，既能控制单次操作的耗时，又不会频繁触发操作给数据库添负担；
• 高配服务器（8核16G+，业务低峰期）：可以放宽到5000-10000行，提升删除效率；
• 业务高峰期或者服务器资源紧张：建议缩到500-1000行，尽量避免影响正常业务。

另外分批删除还有几个必注意的点：

• 一定要基于主键或唯一索引来分批，比如用DELETE FROM your_table WHERE id > last_deleted_id LIMIT 1000;，避免全表扫描，同时防止重复删除或者漏删；
• 每次删除后可以加个短暂休眠（比如SELECT SLEEP(1);），给数据库喘口气的时间；
• 如果是删除过期数据，更推荐用分区表（PARTITION BY），直接删除分区比分批DELETE高效N倍。

内容的提问来源于stack exchange，提问作者pvjhs