MariaDB InnoDB大表删半量数据后，DELETE语句（含10行）挂起求助

这种大表归档删除的坑我太熟了——4亿行的InnoDB表，删到一半就卡DELETE，哪怕只删10行都挂，确实头大。先帮你拆解下问题根源，再给几个能落地的方案：

InnoDB的DELETE本质是标记删除，不会立刻释放磁盘空间，大表批量删除后会产生大量数据碎片。你用ALTER TABLE ... ENGINE=InnoDB（也就是InnoDB版的OPTIMIZE）回收空间后，表会被重建，碎片暂时消失，但再次删除时，大概率是这几个原因导致挂起：

• 锁范围过大：哪怕你只删10行，如果WHERE条件没用到合适的索引，InnoDB会扫描大量数据页，锁定远超目标行的资源，引发锁等待；
• 事务日志压力：大量DELETE会生成巨量undo/redo日志，日志刷盘跟不上就会拖慢操作；
• 长事务阻塞：如果有其他长时间运行的SELECT/写事务持有锁，你的DELETE会一直处于等待状态。

1. 放弃单条DELETE，改用小批量循环删除 #

不管你要删多少行，都拆分成分批小事务，严格加LIMIT控制锁范围，比如：

SET autocommit = 0;
REPEAT
  -- 替换成你的归档条件，LIMIT值根据业务调整（比如1000-5000行）
  DELETE FROM your_big_table 
  WHERE archive_flag = 1 
  LIMIT 1000;
  COMMIT;
  DO SLEEP(0.1); -- 给数据库留缓冲，避免日志过载
UNTIL ROW_COUNT() = 0 END REPEAT;
SET autocommit = 1;

哪怕只删10行，也用LIMIT 10，这样InnoDB只会锁定目标行对应的索引和数据页，不会扩大锁范围，同时每次COMMIT都会释放锁，避免长事务占用资源。

2. 排查锁等待，干掉阻塞源 #

当DELETE挂起时，立刻执行以下SQL查锁情况：

-- 查看当前所有活跃事务
SELECT trx_id, trx_state, trx_started, trx_query FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁等待关系
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

如果发现有其他事务（比如一个跑了几小时的全表SELECT）持有锁，先终止这个阻塞事务（生产环境要谨慎，确认不会影响业务再操作）：

KILL trx_id; -- 替换成查到的阻塞事务ID

如果你的归档是按时间/固定维度（比如创建时间、地区）划分的，直接把表改成分区表，归档时直接DROP分区，这比DELETE快N倍，还不会产生碎片：

-- 假设按创建时间的月份分区，先修改表结构（MariaDB支持在线DDL，锁表时间短）
ALTER TABLE your_big_table 
PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
  PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
  -- 按需添加其他分区
);
-- 归档时直接删除对应分区，瞬间完成
ALTER TABLE your_big_table DROP PARTITION p202301;

这个操作是原子性的，几乎没有性能开销，也不会触发锁等待，是大表归档的最优解。

你之前花11小时做的ALTER操作，本质是重建表，不仅耗时，还会锁表（除非用在线DDL）。InnoDB的碎片只要没严重影响查询性能，完全不用刻意回收。如果一定要清理碎片，推荐用Percona的pt-online-schema-change工具，它能在线重建表，几乎不影响业务，但要在低峰期操作。

如果你的数据库配置偏保守，可以微调以下参数：

• 调大undo日志：innodb_undo_logs = 128、innodb_undo_tablespaces = 4，减少undo日志的竞争；
• 调大事务日志：把innodb_log_file_size调到2G（注意要先停库、删除旧日志文件再重启），减少日志刷盘频率；
• 关闭自动提交：分批删除时已经用到，但单条操作时关闭自动提交能减少事务开销。

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