delete many rows from a large table

最新推荐文章于 2022-11-27 13:08:57 发布

HiJobs

最新推荐文章于 2022-11-27 13:08:57 发布

阅读量642

点赞数

分类专栏：摘录所见所闻文章标签： MySQL

本文链接：https://blog.csdn.net/HiJobs/article/details/46357083

版权

摘录所见所闻专栏收录该内容

27 篇文章 0 订阅

订阅专栏

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;

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