SQL存储过程如何高效删除千万级数据：分批Delete与事务提交优化策略
为什么直接执行DELETE FROM table WHERE ...删除千万级数据风险极高？
当需要清理数据库中的千万级历史数据时，直接运行一条范围DELETE语句是极其危险的操作。它会瞬间锁定海量数据行，在InnoDB存储引擎下，行锁极易升级为表级锁或间隙锁，导致数据库的读写操作被长时间阻塞，业务系统陷入停滞。同时，事务日志（redo log与undo log）会急剧增长，可能迅速耗尽磁盘空间，并引发操作超时、主从复制严重延迟等一系列连锁问题。在MySQL默认的“可重复读”隔离级别下，一个未提交的巨型删除事务，会使后续所有依赖行锁的查询（如SELECT ... FOR UPDATE）陷入无限等待。
在执行大规模删除前，必须完成以下准备工作：
- 使用
SELECT COUNT(*)精确估算待删除数据量，避免对数据规模产生误判。 - 全面检查目标表是否存在外键约束、触发器或特殊的数据库复制过滤规则，这些因素会显著影响删除性能并增加复杂性。
- 务必选择在业务流量最低的时段进行操作，并提前与数据库管理员（DBA）沟通，重点监控
innodb_row_lock_waits（行锁等待）和Threads_running（运行线程数）等关键性能指标。
如何编写分批删除的核心控制逻辑？
实现高效分批删除的关键，在于设计一个能够精准、稳定定位每一批数据的“游标”机制。简单地使用LIMIT配合无明确排序的ORDER BY，极易导致数据漏删或重复删除。最佳实践是选择一个具有唯一性且有序的字段（如自增主键ID或时间戳字段）作为分批推进的依据。以下是一个基于主键ID进行分批删除的可靠示例：
DELETE FROM orders WHERE created_at < '2020-01-01' AND id <= (SELECT id FROM orders WHERE created_at < '2020-01-01' ORDER BY id DESC LIMIT 1 OFFSET 9999) ORDER BY id DESC LIMIT 10000;
在实现分批逻辑时，需特别注意以下几点：
- 对于超大的数据偏移量，
OFFSET子句的性能损耗会非常严重。更优的方案是记录上一批已删除数据的最大ID值，下一批直接使用WHERE id < last_max_id作为条件进行定位。 ORDER BY子句必须与LIMIT配合使用，以确保每一批数据的边界是确定且可预测的。- 在每批次删除操作完成后，可以主动引入一个短暂的暂停，例如在存储过程中执行
DO SLEEP(0.1)，以缓解高并发下的磁盘I/O压力，让数据库获得喘息之机。
如何设置事务提交频率以平衡性能与稳定性？
关于事务提交频率的设置，存在一个普遍误区：认为每删除固定行数（如1000行）就提交一次是最佳选择。实际上，过于频繁的COMMIT操作会带来巨大的事务管理开销；而单次事务包含的行数过多（例如10万行），则可能导致undo日志空间暴涨，并在需要回滚时耗费极长时间。更科学的策略是根据“单次事务的执行时长”来动态控制提交节奏。
- 建议将单次事务的执行时间目标控制在0.5秒至2秒之间。可以通过
BENCHMARK函数或在测试环境进行模拟压测来估算合适的批次大小。 - 在存储过程的循环体内，使用
SELECT UNIX_TIMESTAMP(NOW(3))获取毫秒级时间戳来记录事务开始时间，并实时判断是否达到预设的时间阈值。 - 尽量避免在事务内部执行
SELECT COUNT(*)这类会触发全表扫描的统计操作，可考虑使用EXPLAIN进行行数预估，或采用采样统计的方式替代。 - 如果目标表上建有多个二级索引，删除操作需要同步更新所有相关的索引页，这会增加开销。此时，将每批删除量调整为5000行可能比10000行更稳定。
如何在存储过程中实现安全终止与断点续删？
一个可能持续运行数小时的数据清理任务，必须考虑意外中断后的恢复能力。实现“断点续删”机制是保障操作可管控性的核心。建议创建一张轻量的进度记录表来持久化删除状态：
CREATE TABLE delete_checkpoint ( table_name VARCHAR(64), max_id BIGINT UNSIGNED, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (table_name) );
随后，在存储过程中集成以下关键逻辑：
- 每次成功提交事务后，立即使用
REPLACE INTO delete_checkpoint语句更新本次删除到的最大id值。 - 存储过程启动时，首先查询此控制表，获取上一次中断时的断点位置，并将其作为本次循环的起始条件（例如
WHERE id < ?）。 - 通过
DECLARE EXIT HANDLER FOR SQLEXCEPTION声明异常处理器，捕获执行过程中的错误，并将错误代码和详细信息记录到专用的日志表中，便于排查。 - 不要仅依赖
@@ROW_COUNT来判断删除是否完成。更可靠的做法是，在循环结束后，执行一次SELECT COUNT(*) FROM ... WHERE ... AND id > last_max_id来最终确认是否还有残留数据。
最后，切勿忽略“善后”工作。大规模删除操作会产生大量索引碎片，并使表的统计信息变得过时。因此，删除任务完成后，务必执行ANALYZE TABLE来更新统计信息，帮助优化器生成更优的执行计划。至于是否需要进行OPTIMIZE TABLE来重建表并整理碎片，则需要根据表的实际碎片率和业务影响来评估——请注意，OPTIMIZE TABLE在大表上会锁表，必须安排在独立的维护窗口进行。
