删除庞大的 MySQL 表的最快方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/879327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Quickest way to delete enormous MySQL table
提问by Kyle Kaitan
I have an enormous MySQL (InnoDB) database with millions of rows in the sessions table that were created by an unrelated, malfunctioning crawler running on the same server as ours. Unfortunately, I have to fix the mess now.
我有一个巨大的 MySQL (InnoDB) 数据库，会话表中有数百万行，这些行是由与我们在同一台服务器上运行的不相关的故障爬虫创建的。不幸的是，我现在必须解决这个烂摊子。
If I try to truncate table sessions;it seems to take an inordinately long time (upwards of 30 minutes). I don't care about the data; I just want to have the table wiped out as quickly as possible. Is there a quicker way, or will I have to just stick it out overnight?
如果我尝试，truncate table sessions;它似乎需要非常长的时间（超过 30 分钟）。我不在乎数据；我只想尽快把桌子擦掉。有没有更快的方法，还是我必须在一夜之间坚持下去？
采纳答案by womp
The quickest way is to use DROP TABLE to drop the table completely and recreate it using the same definition. If you have no foreign key constraints on the table then you should do that.
最快的方法是使用 DROP TABLE 完全删除表并使用相同的定义重新创建它。如果您在表上没有外键约束，那么您应该这样做。
If you're using MySQL version greater than 5.0.3, this will happen automatically with a TRUNCATE. You might get some useful information out of the manual as well, it describes how a TRUNCATE works with FK constraints. http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html
如果您使用的 MySQL 版本高于 5.0.3，这将通过 TRUNCATE 自动发生。您也可以从手册中获得一些有用的信息，它描述了 TRUNCATE 如何处理 FK 约束。 http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html
EDIT: TRUNCATE is not the same as a drop or a DELETE FROM. For those that are confused about the differences, please check the manual link above. TRUNCATE will act the same as a drop if it can (if there are no FK's), otherwise it acts like a DELETE FROM with no where clause.
编辑：TRUNCATE 与 drop 或 DELETE FROM 不同。对于那些对差异感到困惑的人，请查看上面的手册链接。如果可以（如果没有 FK），TRUNCATE 将与 drop 相同，否则它就像一个没有 where 子句的 DELETE FROM。
回答by searlea
(As this turned up high in Google's results, I thought a little more instruction might be handy.)
（由于这在 Google 的结果中出现了高位，我认为多一点说明可能会很方便。）
MySQL has a convenient way to create empty tables like existing tables, and an atomic table rename command. Together, this is a fast way to clear out data:
MySQL 有一种方便的方法来创建像现有表一样的空表，以及一个原子表重命名命令。总之，这是一种清除数据的快速方法：
CREATE TABLE new_foo LIKE foo;
RENAME TABLE foo TO old_foo, new_foo TO foo;
DROP TABLE old_foo;
Done
完毕
回答by adnan.
The best way I have found of doing this with MySQL is:
我发现使用 MySQL 执行此操作的最佳方法是：
DELETE from table_name LIMIT 1000;
Or 10,000 (depending on how fast it happens).
或 10,000（取决于发生的速度）。
Put that in a loop until all the rows are deleted.
将其放入循环中，直到删除所有行。
Please do try this as it will actually work. It will take some time, but it will work.
请尝试这个，因为它实际上会起作用。这将需要一些时间，但它会起作用。
回答by Nate
Couldn't you grab the schema drop the table and recreate it?
你不能抓住模式删除表并重新创建它吗？
回答by Paul Sonier
drop tableshould be the fastest way to get rid of it.
drop table应该是摆脱它的最快方法。
回答by shylent
If you just want to get rid of the table altogether, why not simply dropit?
如果你只是想完全摆脱这张桌子，为什么不干脆把它扔掉呢？
回答by Zenshai
Have you tried to use "drop"? I've used it on tables over 20GB and it always completes in seconds.
您是否尝试过使用“drop”？我在超过 20GB 的表上使用过它，它总是在几秒钟内完成。
回答by Jeff Ferland
Truncate is fast, usually on the order of seconds or less. If it took 30 minutes, you probably had a case of some foreign keys referencing the table you were truncating. There may also be locking issues involved.
截断速度很快，通常在几秒或更短的时间内。如果花费了 30 分钟，您可能会遇到一些外键引用您正在截断的表的情况。可能还涉及锁定问题。
Truncate is effectively as efficient as one can empty a table, but you may have to remove the foreign key references unless you want those tables scrubbed as well.
截断与清空表的效率一样有效，但您可能必须删除外键引用，除非您还希望清除这些表。
回答by Brent Baisley
I'm not sure why it's taking so long. But perhaps try a rename, and recreate a blank table. Then you can drop the "extra" table without worrying how long it takes.
我不知道为什么要花这么长时间。但也许尝试重命名，并重新创建一个空白表。然后您可以删除“额外”表而无需担心需要多长时间。
回答by Brian Hogan
We had these issues. We no longer use the database as a session store with Rails 2.x and the cookie store. However, dropping the table is a decent solution. You may want to consider stopping the mysql service, temporarily disable logging, start things up in safe mode and then do your drop/create. When done, turn on your logging again.
我们遇到了这些问题。我们不再使用数据库作为 Rails 2.x 和 cookie 存储的会话存储。但是，删除表是一个不错的解决方案。您可能需要考虑停止 mysql 服务，暂时禁用日志记录，以安全模式启动，然后执行删除/创建操作。完成后，再次打开日志记录。
