在MySQL中删除数百万行 [英] Deleting millions of rows in MySQL
问题描述
我最近发现并修复了一个我正在处理的站点中的错误，该错误导致表中数百万行数据的重复行，即使没有行，行也将非常大(甚至数百万行).我可以轻松找到这些重复的行，并可以运行一个删除查询将其全部杀死.问题是试图一击删除这么多行会长时间锁定表，如果可能的话，我想避免这种情况.我可以看到摆脱这些行而又不占用站点(通过锁定表)的唯一方法是:
I recently found and fixed a bug in a site I was working on that resulted in millions of duplicate rows of data in a table that will be quite large even without them (still in the millions). I can easily find these duplicate rows and can run a single delete query to kill them all. The problem is that trying to delete this many rows in one shot locks up the table for a long time, which I would like to avoid if possible. The only ways I can see to get rid of these rows, without taking down the site (by locking up the table) are:
- 编写一个脚本，该脚本将循环执行数千个较小的删除查询.从理论上讲，这将解决锁定表的问题，因为其他查询将能够使其进入队列并在删除之间运行.但是它仍然会在一定程度上增加数据库的负载，并且需要很长时间才能运行.
- 重命名表并重新创建现有表(现在为空).然后在重命名的表上进行清理.重命名新表，重命名旧表，然后将新行合并到重命名表中.这种方法需要花费更多的步骤，但是应该以最小的中断来完成工作.这里唯一棘手的部分是，所涉及的表是报表表，因此一旦将其重命名并放了一个空表，所有历史报表都将消失，直到我将其放回原位.另外，由于要存储的数据类型，合并过程可能会有些麻烦.总的来说，这是我目前可能的选择.
我只是想知道以前是否有人遇到过这个问题，如果是这样，那么您如何在不关闭站点的情况下进行处理，希望对用户的干扰降到最低(如果有的话)?如果我使用2号或其他类似的方法，我可以将这些内容安排在深夜运行，并在第二天清晨进行合并，只需要提前通知用户即可，所以这不是什么大问题.我只是想看看是否有人对更好或更轻松的清理方式有任何想法.
I was just wondering if anyone else has had this problem before and, if so, how you dealt with it without taking down the site and, hopefully, with minimal if any interruption to the users? If I go with number 2, or a different, similar, approach, I can schedule the stuff to run late at night and do the merge early the next morning and just let the users know ahead of time, so that's not a huge deal. I'm just looking to see if anyone has any ideas for a better, or easier, way to do the cleanup.
推荐答案
DELETE FROM `table`
WHERE (whatever criteria)
ORDER BY `id`
LIMIT 1000
洗涤，漂洗，重复直到影响零行.也许在脚本之间，在两次迭代之间休眠一到两秒.
Wash, rinse, repeat until zero rows affected. Maybe in a script that sleeps for a second or three between iterations.
这篇关于在MySQL中删除数百万行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
