mysql删除表数据报错_mysql 删除innodb 表里面的大量数据的问题

整理一个表table(innodb 引擎)的数据，需要大量的删除以前的数据，数据大概有999w条左右

一、直接执行 delete from table where id < 111111111;

执行很久后，报错，而且执行中途去查看，发现总条数是一样多的，并没有删除一条：

The total number of locks exceeds the lock table size.

此报错可以通过 show variables like "%_buffer%"; 调整 innodb_buffer_pool_size的值，得到一定的控制，但是这样数据量大的话，还是会有一些问题：

1、这种长事务，delete执行时会加锁，无论你有没有加锁，默认涉及到的行加写锁和Gap锁，所有相关的行都会被锁定，如果数据量大直接导致其他连接无法访问该表数据，导致业务无法使用

2、delete数据量大的时候，如果不加limilt，容易占cpu资源，导致越删越慢。

二、改用方案 用limit 的方式

直接在navicat 中执行删除100w 条的sql ，发现直接报错：

Lock wait timeout exceeded; try restarting transaction

用SELECT * FROM information_schema.innodb_trx; 命令查看 事务的情况(如下图)。

原因是：

之前的那条语句以为被kill掉了，show processlist; 里面查到确实Command 里面确实也是显示killed ，但是 查询事务的话，发现事务还是一直在堵塞 trx_state 一直是 ROLLING BACK 回滚的状态，怎么也kill 不掉。这种情况，只能等 trx_rows_modified 为0 后，此trx_id才会退出

三、用CLI 执行delete limit 的方式

trx_id 退出后，发现直接写sql在navicat里面执行，一直等待执行完了，再手动运行，这样得一直盯着。所以改用用代码CLI的方式，先查询总条数，然后循环每次删除10w条 ，然后配合

SELECT * FROM information_schema.innodb_trx; 查看事务的情况，确实是事务一直在，只要等到 trx_rows_modified 达到删除的条数时，才会执行删除成功。

总结：

如果删除的数据占表的数量百分比过大的话，建议把旧表重命令，用相同的结构建新表，然后insert into select ... where ... ; 把需要的数据导入新表。

如果删除的数据占表的数量百分比不大，但是本身数据量又不小的话，可以采用 delete limit 的CLI 代码执行的方式，期间可以观察事务的运行情况，尽量也保证删除数据的时候，数据不要写入。