mysql - 从 InnoDB 中删除行非常慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22783945/
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
mysql - Deleting Rows from InnoDB is very slow
提问by user1938509
I got a mysql database with approx. 1 TB of data. Table fuelinjection_stroke has apprx. 1.000.000.000 rows. DBID is the primary key that is automatically incremented by one with each insert.
我有一个 mysql 数据库，大约有。1 TB 数据。表fuelinjection_stroke有大约。1.000.000.000 行。DBID 是主键，每次插入时自动加一。
I am trying to delete the first 1.000.000 rows using a very simple statement:
我正在尝试使用一个非常简单的语句删除前 1.000.000 行：
Delete from fuelinjection_stroke where DBID < 1000000;
This query is takeing very long (>24h) on my dedicated 8core Xeon Server (32 GB Memory, SAS Storage).
在我的专用 8 核至强服务器（32 GB 内存，SAS 存储）上，此查询需要很长时间（> 24 小时）。
Any idea whether the process can be sped up?
知道这个过程是否可以加快？
回答by Uriil
I believe that you table becomes locked. I've faced same problem and find out that can delete 10k records pretty fast. So you might want to write simple script/program which will delete records by chunks.
我相信你的桌子被锁定了。我遇到了同样的问题，发现可以非常快地删除 10k 条记录。因此，您可能想要编写简单的脚本/程序，它将按块删除记录。
DELETE FROM fuelinjection_stroke WHERE DBID < 1000000 LIMIT 10000;
And keep executing it until it deletes everything
并继续执行它直到它删除所有内容
回答by jmail
Are you space deprived? Is down time impossible?
你空间不足吗？停机时间是不可能的吗？
If not, you could fit in a new INT column length 1 and default it to 1 for "active" (or whatever your terminology is) and 0 for "inactive". Actually, you could use 0 through 9 as 10 different states if necessary.
如果没有，您可以放入一个新的 INT 列长度 1 并将其默认为 1 表示“活动”（或任何您的术语）和 0 表示“不活动”。实际上，如有必要，您可以将 0 到 9 用作 10 个不同的状态。
Adding this new column will take a looooooooong time, but once it's over, your UPDATEs should be lightning fast as long as you do it off the PRIMARY (as you do with your DELETE) and you don't index this new column.
添加这个新列需要很长时间，但是一旦它结束，只要您在 PRIMARY（就像您使用 DELETE 一样）并且不索引这个新列，您的 UPDATE 就应该快如闪电。
The reason why InnoDB takes so long to DELETE on such a massive table as yours is because of the cluster index. It physically orders your table based upon your PRIMARY (or first UNIQUE it finds...or whatever it feels like if it can't find PRIMARY or UNIQUE), so when you pull out one row, it now reorders your ENTIRE table physically on the disk for speed and defragmentation. So it's not the DELETE that's taking so long. It's the physical reordering after that row is removed.
InnoDB 在像您这样庞大的表上进行 DELETE 需要这么长时间的原因是集群索引。它根据您的 PRIMARY（或它找到的第一个 UNIQUE...或者如果它找不到 PRIMARY 或 UNIQUE 时的任何感觉）对您的表进行物理排序，因此当您拉出一行时，它现在对您的整个表进行物理重新排序用于速度和碎片整理的磁盘。所以不是 DELETE 需要这么长时间。这是删除该行后的物理重新排序。
When you create a new INT column with a default value, the space will be filled, so when you UPDATE it, there's no need for physical reordering across your huge table.
当您使用默认值创建一个新的 INT 列时，空间将被填充，因此当您更新它时，无需在您的大表中进行物理重新排序。
I'm not sure exactly what your schema is exactly, but using a column for a row's state is much faster than DELETEing; however, it will take more space.
我不确定您的架构到底是什么，但是使用列表示行的状态比 DELETEing 快得多；但是，它将占用更多空间。
Try setting values:
尝试设置值：
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT (for non-windows machine)
innodb_buffer_pool_size=25GB (currently it is close to 21GB)
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0...1000 (try different values, beginning with 200)
References:
参考：
MySQL docs for description of different variables.
MySQL Performance Optimization basics
回答by i-CONICA
What indexes do you have?
你有什么指标？
I think your issue is that the delete is rebuilding the index on every iteration.
我认为您的问题是删除会在每次迭代时重建索引。
I'd delete the indexes if any, do the delete, then re-add the indexes. It'll be far faster, (I think).
如果有的话，我会删除索引，删除，然后重新添加索引。它会快得多，（我认为）。
回答by user5883982
I was having the same problem, and my table has several indices that I didn't want to have to drop and recreate. So I did the following:
我遇到了同样的问题，我的表有几个我不想删除和重新创建的索引。所以我做了以下事情：
create table keepers
select * from origTable where {clause to retrieve rows to preserve};
truncate table origTable;
insert into origTable null,keepers.col2,...keepers.col(last) from keepers;
drop table keepers;
About 2.2 million rows were processed in about 3 minutes.
在大约 3 分钟内处理了大约 220 万行。
回答by user6850438
For such long tables, I'd rather use MYISAM, specially if there is not a lot of transactions needed.
对于这么长的表，我宁愿使用 MYISAM，特别是在不需要很多事务的情况下。
回答by borjab
Your database may be checking for records that need to be modified in a foreign key (cascades, delete).
您的数据库可能正在检查需要在外键（级联、删除）中修改的记录。
But I-Conica answer is a good point(+1). The process of deleting a single record and updating a lot of indexes during done 100000 times is inefficient. Just drop the index, delete all records and create it again.
但是 I-Conica 的回答是一个好点（+1）。100000 次删除单个记录并更新大量索引的过程是低效的。只需删除索引，删除所有记录并重新创建即可。
And of course, check if there is any kind of lock in the database. One user or application can lock a record or table and your query will be waiting until the user release the resource or it reachs a timeout. One way to check if your database is doing real work or just waiting is lauch the query from a connection that sets the --innodb_lock_wait_timeout parameter to a few seconds. If it fails at least you know that the query is OK and that you need to find and realease that lock. Examples of locks are Select * from XXX For update and uncommited transactions.
当然，检查数据库中是否有任何类型的锁。一个用户或应用程序可以锁定记录或表，您的查询将一直等到用户释放资源或达到超时。检查您的数据库是否正在执行实际工作或只是等待的一种方法是从将 --innodb_lock_wait_timeout 参数设置为几秒钟的连接启动查询。如果它失败，至少您知道查询没有问题，并且您需要找到并释放该锁。锁的例子是 Select * from XXX For update 和 uncommited transactions。
回答by AK47
I don't know exact ans for ur que. But writing another way to delete those rows, pls try this.
我不知道你的确切答案。但是写另一种方法来删除这些行，请试试这个。
delete from fuelinjection_stroke where DBID in
(
select top 1000000 DBID from fuelinjection_stroke
order by DBID asc
)
