本文介绍了一种针对InnoDB表进行大批量数据删除的方法，该方法通过创建一个空的副本表并插入需要保留的数据，然后通过重命名操作将原表替换为副本表，最后删除原表来实现。这种方法可以有效减少锁定时间并避免锁表大小限制的问题。
InnoDB Tables
If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:
1.Select the rows not to be deleted into an empty table that has the same structure as the original table:
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
2.Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
RENAME TABLE t TO t_old, t_copy TO t;
3.Drop the original table:
DROP TABLE t_old;
If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:
1.Select the rows not to be deleted into an empty table that has the same structure as the original table:
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
2.Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
RENAME TABLE t TO t_old, t_copy TO t;
3.Drop the original table:
DROP TABLE t_old;
No other sessions can access the tables involved while RENAME TABLE executes, so the rename operation is not subject to concurrency problems.
quote from mysql 5.7 reference manual
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
