删除巨大的MySQL表的最快方法 [英] Quickest way to delete enormous MySQL table

问题描述

我有一个巨大的MySQL(InnoDB)数据库，会话表中有数百万行，这些数据库是由与我们服务器相同且运行不正常的爬网程序创建的.不幸的是，我必须立即解决问题.

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.

如果我尝试truncate table sessions;，这似乎要花费很长的时间(最多30分钟).我不在乎数据；我只是想尽快清理桌子.有没有一种更快的方法，还是我必须一夜之间将其伸出?

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?

推荐答案

最快的方法是使用DROP TABLE完全删除表并使用相同的定义重新创建.如果表上没有外键约束，则应该这样做.

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.

如果您使用的MySQL版本高于5.0.3，则会通过TRUNCATE自动发生.您可能还会从手册中获得一些有用的信息，它描述了TRUNCATE如何与FK约束一起工作. http://dev.mysql.com/doc/refman/5.0 /en/truncate-table.html

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

TRUNCATE与拖放或DELETE FROM不同.对于那些对差异感到困惑的人，请查看上面的手册链接.如果可以的话，TRUNCATE的作用与放置相同(如果没有FK的话)，否则它的作用就像没有where子句的DELETE FROM.

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.

这篇关于删除巨大的MySQL表的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！