MySQL 删除匹配行的更快方法？

Faster way to delete matching rows?

提问by itsmatt

I'm a relative novice when it comes to databases. We are using MySQL and I'm currently trying to speed up a SQL statement that seems to take a while to run. I looked around on SO for a similar question but didn't find one.

在数据库方面，我是一个相对新手。我们正在使用 MySQL，我目前正在尝试加速似乎需要一段时间才能运行的 SQL 语句。我环顾四周寻找类似的问题，但没有找到。

The goal is to remove all the rows in table A that have a matching id in table B.

目标是删除表 A 中在表 B 中具有匹配 id 的所有行。

I'm currently doing the following:

我目前正在做以下事情：

DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);

There are approximately 100K rows in table a and about 22K rows in table b. The column 'id' is the PK for both tables.

表 a 中大约有 100K 行，表 b 中大约有 22K 行。'id' 列是两个表的 PK。

This statement takes about 3 minutes to run on my test box - Pentium D, XP SP3, 2GB ram, MySQL 5.0.67. This seems slow to me. Maybe it isn't, but I was hoping to speed things up. Is there a better/faster way to accomplish this?

这个语句在我的测试机器上运行大约需要 3 分钟 - Pentium D、XP SP3、2GB ram、MySQL 5.0.67。这对我来说似乎很慢。也许不是，但我希望加快速度。有没有更好/更快的方法来实现这一目标？

---

---

EDIT:

编辑：

Some additional information that might be helpful. Tables A and B have the same structure as I've done the following to create table B:

一些可能有用的附加信息。表 A 和 B 的结构与我为创建表 B 所做的以下操作相同：

CREATE TABLE b LIKE a;

Table a (and thus table b) has a few indexes to help speed up queries that are made against it. Again, I'm a relative novice at DB work and still learning. I don't know how much of an effect, if any, this has on things. I assume that it does have an effect as the indexes have to be cleaned up too, right? I was also wondering if there were any other DB settings that might affect the speed.

表 a（以及表 b）有一些索引来帮助加快对其进行的查询。同样，我是 DB 工作中的相对新手，仍在学习中。我不知道这对事物有多大影响，如果有的话。我认为它确实有影响，因为索引也必须清理，对吗？我还想知道是否还有其他可能影响速度的数据库设置。

Also, I'm using INNO DB.

另外，我正在使用 INNO DB。

---

---

Here is some additional info that might be helpful to you.

以下是一些可能对您有所帮助的其他信息。

Table A has a structure similar to this (I've sanitized this a bit):

表 A 具有与此类似的结构（我对此进行了一些清理）：

DROP TABLE IF EXISTS `frobozz`.`a`;
CREATE TABLE  `frobozz`.`a` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `fk_g` varchar(30) NOT NULL,
  `h` int(10) unsigned default NULL,
  `i` longtext,
  `j` bigint(20) NOT NULL,
  `k` bigint(20) default NULL,
  `l` varchar(45) NOT NULL,
  `m` int(10) unsigned default NULL,
  `n` varchar(20) default NULL,
  `o` bigint(20) NOT NULL,
  `p` tinyint(1) NOT NULL,
  PRIMARY KEY  USING BTREE (`id`),
  KEY `idx_l` (`l`),
  KEY `idx_h` USING BTREE (`h`),
  KEY `idx_m` USING BTREE (`m`),
  KEY `idx_fk_g` USING BTREE (`fk_g`),
  KEY `fk_g_frobozz` (`id`,`fk_g`),
  CONSTRAINT `fk_g_frobozz` FOREIGN KEY (`fk_g`) REFERENCES `frotz` (`g`)
) ENGINE=InnoDB AUTO_INCREMENT=179369 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

I suspect that part of the issue is there are a number of indexes for this table. Table B looks similar to table B, though it only contains the columns idand h.

我怀疑问题的一部分是该表有许多索引。表 B 看起来与表 B 相似，但它只包含列id和h。

Also, the profiling results are as follows:

此外，分析结果如下：

starting 0.000018
checking query cache for query 0.000044
checking permissions 0.000005
Opening tables 0.000009
init 0.000019
optimizing 0.000004
executing 0.000043
end 0.000005
end 0.000002
query end 0.000003
freeing items 0.000007
logging slow query 0.000002
cleaning up 0.000002

---

---

SOLVED

解决了

Thanks to all the responses and comments. They certainly got me to think about the problem. Kudos to dotjoefor getting me to step away from the problem by asking the simple question "Do any other tables reference a.id?"

感谢所有的回复和评论。他们当然让我思考这个问题。荣誉对dotjoe为让我问一个简单的问题从问题一步之遥“做任何其他表引用a.id？”

The problem was that there was a DELETE TRIGGER on table A which called a stored procedure to update two other tables, C and D. Table C had a FK back to a.id and after doing some stuff related to that id in the stored procedure, it had the statement,

问题是表 A 上有一个 DELETE TRIGGER，它调用一个存储过程来更新另外两个表 C 和 D。表 C 有一个 FK 回到 a.id 并且在存储过程中做了一些与该 id 相关的事情之后，它有这样的声明，

DELETE FROM c WHERE c.id = theId;

I looked into the EXPLAIN statement and rewrote this as,

我查看了 EXPLAIN 语句并将其重写为，

EXPLAIN SELECT * FROM c WHERE c.other_id = 12345;

So, I could see what this was doing and it gave me the following info:

所以，我可以看到这是在做什么，它给了我以下信息：

id            1
select_type   SIMPLE
table         c
type          ALL
possible_keys NULL
key           NULL
key_len       NULL
ref           NULL
rows          2633
Extra         using where

This told me that it was a painful operation to make and since it was going to get called 22500 times (for the given set of data being deleted), that was the problem. Once I created an INDEX on that other_id column and reran the EXPLAIN, I got:

这告诉我这是一个痛苦的操作，因为它将被调用 22500 次（对于被删除的给定数据集），这就是问题所在。一旦我在 other_id 列上创建了一个 INDEX 并重新运行 EXPLAIN，我得到：

id            1
select_type   SIMPLE
table         c
type          ref
possible_keys Index_1
key           Index_1
key_len       8
ref           const
rows          1
Extra         

Much better, in fact really great.

好多了，事实上真的很棒。

I added that Index_1 and my delete times are in line with the times reported by mattkemp. This was a really subtle error on my part due to shoe-horning some additional functionality at the last minute. It turned out that most of the suggested alternative DELETE/SELECT statements, as Danielstated, ended up taking essentially the same amount of time and as soulmergementioned, the statement was pretty much the best I was going to be able to construct based on what I needed to do. Once I provided an index for this other table C, my DELETEs were fast.

我补充说 Index_1 和我的删除时间与mattkemp报告的时间一致。这对我来说是一个非常微妙的错误，因为在最后一分钟硬塞了一些额外的功能。事实证明，正如Daniel所说，大多数建议的替代 DELETE/SELECT 语句最终花费的时间基本上相同，并且正如soulmerge提到的那样，该语句几乎是我能够基于什么构建的最好的语句我需要做。一旦我为另一个表 C 提供了索引，我的 DELETE 就很快了。

Postmortem:
Two lessons learned came out of this exercise. First, it is clear that I didn't leverage the power of the EXPLAIN statement to get a better idea of the impact of my SQL queries. That's a rookie mistake, so I'm not going to beat myself up about that one. I'll learn from that mistake. Second, the offending code was the result of a 'get it done quick' mentality and inadequate design/testing led to this problem not showing up sooner. Had I generated several sizable test data sets to use as test input for this new functionality, I'd have not wasted my time nor yours. My testing on the DB side was lacking the depth that my application side has in place. Now I've got the opportunity to improve that.

事后分析：
从这次练习中吸取了两个教训。首先，很明显我没有利用 EXPLAIN 语句的强大功能来更好地了解我的 SQL 查询的影响。这是一个新手错误，所以我不会因为那个而自责。我会从那个错误中吸取教训。其次，有问题的代码是“快速完成”心态的结果，设计/测试不充分导致这个问题没有早点出现。如果我生成了几个相当大的测试数据集作为这个新功能的测试输入，我不会浪费我的时间也不会浪费你的时间。我在数据库方面的测试缺乏应用程序方面的深度。现在我有机会改进它。

Reference: EXPLAIN Statement

参考：EXPLAIN 语句

回答by Daniel Schneller

Deleting data from InnoDB is the most expensive operation you can request of it. As you already discovered the query itself is not the problem - most of them will be optimized to the same execution plan anyway.

从 InnoDB 中删除数据是您可以请求的最昂贵的操作。正如您已经发现查询本身不是问题 - 无论如何，它们中的大多数都会优化为相同的执行计划。

While it may be hard to understand why DELETEs of all cases are the slowest, there is a rather simple explanation. InnoDB is a transactional storage engine. That means that if your query was aborted halfway-through, all records would still be in place as if nothing happened. Once it is complete, all will be gone in the same instant. During the DELETE other clients connecting to the server will see the records until your DELETE completes.

虽然可能很难理解为什么所有情况下的 DELETE 都是最慢的，但有一个相当简单的解释。InnoDB 是一个事务存储引擎。这意味着，如果您的查询在中途中止，所有记录仍将保留，就好像什么也没发生一样。一旦完成，一切都会在同一瞬间消失。在 DELETE 期间，连接到服务器的其他客户端将看到记录，直到您的 DELETE 完成。

To achieve this, InnoDB uses a technique called MVCC (Multi Version Concurrency Control). What it basically does is to give each connection a snapshot view of the whole database as it was when the first statement of the transaction started. To achieve this, every record in InnoDB internally can have multiple values - one for each snapshot. This is also why COUNTing on InnoDB takes some time - it depends on the snapshot state you see at that time.

为了实现这一点，InnoDB 使用了一种称为 MVCC（多版本并发控制）的技术。它的主要作用是为每个连接提供整个数据库的快照视图，就像事务的第一个语句开始时一样。为了实现这一点，InnoDB 内部的每条记录都可以有多个值——每个快照一个。这也是为什么在 InnoDB 上进行计数需要一些时间 - 这取决于您当时看到的快照状态。

For your DELETE transaction, each and every record that is identified according to your query conditions, gets marked for deletion. As other clients might be accessing the data at the same time, it cannot immediately remove them from the table, because they have to see their respective snapshot to guarantee the atomicity of the deletion.

对于您的 DELETE 事务，根据您的查询条件标识的每条记录都会被标记为删除。由于其他客户端可能同时访问数据，因此无法立即将它们从表中删除，因为它们必须查看各自的快照以保证删除的原子性。

Once all records have been marked for deletion, the transaction is successfully committed. And even then they cannot be immediately removed from the actual data pages, before all other transactions that worked with a snapshot value before your DELETE transaction, have ended as well.

一旦所有记录都被标记为删除，事务就成功提交。即便如此，在 DELETE 事务之前使用快照值的所有其他事务也结束之前，它们也无法立即从实际数据页面中删除。

So in fact your 3 minutes are not really that slow, considering the fact that all records have to be modified in order to prepare them for removal in a transaction safe way. Probably you will "hear" your hard disk working while the statement runs. This is caused by accessing all the rows. To improve performance you can try to increase InnoDB buffer pool size for your server and try to limit other access to the database while you DELETE, thereby also reducing the number of historic versions InnoDB has to maintain per record. With the additional memory InnoDB might be able to read your table (mostly) into memory and avoid some disk seeking time.

因此，实际上您的 3 分钟并没有那么慢，考虑到必须修改所有记录才能以事务安全的方式将它们删除。当语句运行时，您可能会“听到”硬盘在工作。这是由访问所有行引起的。为了提高性能，您可以尝试增加服务器的 InnoDB 缓冲池大小，并尝试在 DELETE 时限制对数据库的其他访问，从而减少 InnoDB 每条记录必须维护的历史版本数。使用额外的内存 InnoDB 可能能够将您的表（大部分）读入内存并避免一些磁盘搜索时间。

回答by Chris Van Opstal

Try this:

尝试这个：

DELETE a
FROM a
INNER JOIN b
 on a.id = b.id

Using subqueries tend to be slower then joins as they are run for each record in the outer query.

使用子查询往往比连接慢，因为它们为外部查询中的每条记录运行。

回答by mattkemp

Your time of three minutes seems really slow. My guess is that the id column is not being indexed properly. If you could provide the exact table definition you're using that would be helpful.

你的三分钟时间似乎很慢。我的猜测是 id 列没有被正确索引。如果您可以提供您正在使用的确切表定义，那将会很有帮助。

I created a simple python script to produce test data and ran multiple different versions of the delete query against the same data set. Here's my table definitions:

我创建了一个简单的 python 脚本来生成测试数据，并针对同一数据集运行多个不同版本的删除查询。这是我的表定义：

drop table if exists a;
create table a
 (id bigint unsigned  not null primary key,
  data varchar(255) not null) engine=InnoDB;

drop table if exists b;
create table b like a;

I then inserted 100k rows into a and 25k rows into b (22.5k of which were also in a). Here's the results of the various delete commands. I dropped and repopulated the table between runs by the way.

然后我将 100k 行插入到 a 中，将 25k 行插入到 b 中（其中 22.5k 也在 a 中）。这是各种删除命令的结果。顺便说一下，我在两次运行之间删除并重新填充了表格。

mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (1.14 sec)

mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (0.81 sec)

mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (0.97 sec)

mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (0.81 sec)

All the tests were run on an Intel Core2 quad-core 2.5GHz, 2GB RAM with Ubuntu 8.10 and MySQL 5.0. Note, that the execution of one sql statement is still single threaded.

所有测试均在 Intel Core2 四核 2.5GHz、2GB RAM 和 Ubuntu 8.10 和 MySQL 5.0 上运行。注意，一条sql语句的执行仍然是单线程的。

---

---

Update:

更新：

I updated my tests to use itsmatt's schema. I slightly modified it by remove auto increment (I'm generating synthetic data) and character set encoding (wasn't working - didn't dig into it).

我更新了我的测试以使用 itsmatt 的模式。我通过删除自动增量（我正在生成合成数据）和字符集编码（不起作用 - 没有深入研究）稍微修改了它。

Here's my new table definitions:

这是我的新表定义：

drop table if exists a;
drop table if exists b;
drop table if exists c;

create table c (id varchar(30) not null primary key) engine=InnoDB;

create table a (
  id bigint(20) unsigned not null primary key,
  c_id varchar(30) not null,
  h int(10) unsigned default null,
  i longtext,
  j bigint(20) not null,
  k bigint(20) default null,
  l varchar(45) not null,
  m int(10) unsigned default null,
  n varchar(20) default null,
  o bigint(20) not null,
  p tinyint(1) not null,
  key l_idx (l),
  key h_idx (h),
  key m_idx (m),
  key c_id_idx (id, c_id),
  key c_id_fk (c_id),
  constraint c_id_fk foreign key (c_id) references c(id)
) engine=InnoDB row_format=dynamic;

create table b like a;

I then reran the same tests with 100k rows in a and 25k rows in b (and repopulating between runs).

然后我用 a 中的 100k 行和 b 中的 25k 行重新运行相同的测试（并在运行之间重新填充）。

mysql> DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE a.id=b.id);
Query OK, 22500 rows affected (11.90 sec)

mysql> DELETE FROM a USING a LEFT JOIN b ON a.id=b.id WHERE b.id IS NOT NULL;
Query OK, 22500 rows affected (11.48 sec)

mysql> DELETE a FROM a INNER JOIN b on a.id=b.id;
Query OK, 22500 rows affected (12.21 sec)

mysql> DELETE QUICK a.* FROM a,b WHERE a.id=b.id;
Query OK, 22500 rows affected (12.33 sec)

As you can see this is quite a bit slower than before, probably due to the multiple indexes. However, it is nowhere near the three minute mark.

正如您所看到的，这比以前慢了很多，可能是由于多个索引。然而，距离三分钟大关还差得很远。

Something else that you might want to look at is moving the longtext field to the end of the schema. I seem to remember that mySQL performs better if all the size restricted fields are first and text, blob, etc are at the end.

您可能想要查看的其他内容是将长文本字段移动到架构的末尾。我似乎记得如果所有大小限制字段都在前面，文本、blob 等在最后，mySQL 的性能会更好。

回答by Tom Schaefer

This is what I always do, when I have to operate with super large data (here: a sample test table with 150000 rows):

这就是我经常做的，当我必须处理超大数据时（这里：一个有 150000 行的示例测试表）：

drop table if exists employees_bak;
create table employees_bak like employees;
insert into employees_bak 
    select * from employees
    where emp_no > 100000;

rename table employees to employees_todelete;
rename table employees_bak to employees;

In this case the sql filters 50000 rows into the backup table. The query cascade performs on my slow machine in 5 seconds. You can replace the insert into select by your own filter query.

在这种情况下，sql 将 50000 行过滤到备份表中。查询级联在 5 秒内在我的慢速机器上执行。您可以通过自己的过滤器查询替换插入到选择中。

That is the trick to perform mass deletion on big databases!;=)

这就是对大型数据库执行批量删除的技巧！;=)

回答by Artem Russakovskii

I know this question has been pretty much solved due to OP's indexing omissions but I would like to offer this additional advice, which is valid for a more generic case of this problem.

我知道由于 OP 的索引遗漏，这个问题已经得到了很大的解决，但我想提供这个额外的建议，这对于这个问题的更一般的情况是有效的。

I have personally dealt with having to delete many rows from one table that exist in another and in my experience it's best to do the following, especially if you expect lots of rows to be deleted. This technique most importantly will improve replication slave lag, as the longer each single mutator query runs, the worse the lag would be (replication is single threaded).

我个人处理过必须从一个表中删除另一个表中存在的许多行，根据我的经验，最好执行以下操作，特别是如果您希望删除很多行。这项技术最重要的是将改善复制从属延迟，因为每个单独的 mutator 查询运行的时间越长，延迟就越严重（复制是单线程的）。

So, here it is: do a SELECT first, as a separate query, remembering the IDs returned in your script/application, then continue on deleting in batches (say, 50,000 rows at a time). This will achieve the following:

所以，这里是：首先做一个 SELECT，作为一个单独的查询，记住在你的脚本/应用程序中返回的 ID，然后继续批量删除（比如，一次 50,000 行）。这将实现以下目标：

• each one of the delete statements will not lock the table for too long, thus not letting replication lag to get out of control. It is especially important if you rely on your replication to provide you relatively up-to-date data. The benefit of using batches is that if you find that each DELETE query still takes too long, you can adjust it to be smaller without touching any DB structures.
• another benefit of using a separate SELECT is that the SELECT itself might take a long time to run, especially if it can't for whatever reason use the best DB indexes. If the SELECT is inner to a DELETE, when the whole statement migrates to the slaves, it will have to do the SELECT all over again, potentially lagging the slaves because it has to do the long select all over again. Slave lag, again, suffers badly. If you use a separate SELECT query, this problem goes away, as all you're passing is a list of IDs.

• 每个删除语句都不会锁定表太长时间，从而不会让复制滞后失控。如果您依靠复制为您提供相对最新的数据，这一点尤其重要。使用批处理的好处是，如果您发现每个 DELETE 查询仍然花费太长时间，您可以将其调整为更小，而不会触及任何 DB 结构。
• 使用单独的 SELECT 的另一个好处是SELECT 本身可能需要很长时间才能运行，尤其是当它由于某种原因不能使用最好的数据库索引时。如果 SELECT 在 DELETE 内部，当整个语句迁移到从属时，它将不得不重新执行 SELECT，这可能会滞后于从属，因为它必须再次执行长选择。奴隶滞后再次受到严重影响。如果您使用单独的 SELECT 查询，这个问题就会消失，因为您传递的只是一个 ID 列表。

Let me know if there's a fault in my logic somewhere.

让我知道我的逻辑是否有问题。

For more discussion on replication lag and ways to fight it, similar to this one, see MySQL Slave Lag (Delay) Explained And 7 Ways To Battle It

有关复制滞后及其解决方法的更多讨论，与此类似，请参阅MySQL Slave Lag (Delay) Explained And 7 Ways To Battle It

P.S. One thing to be careful about is, of course, potential edits to the table between the times the SELECT finishes and DELETEs start. I will let you handle such details by using transactions and/or logic pertinent to your application.

PS 当然，需要注意的一件事是在 SELECT 完成和 DELETE 开始之间对表的潜在编辑。我会让你通过使用与你的应用程序相关的事务和/或逻辑来处理这些细节。

回答by Webrsk

Try this out:

试试这个：

DELETE QUICK A.* FROM A,B WHERE A.ID=B.ID

It is much faster than normal queries.

它比普通查询快得多。

Refer for Syntax : http://dev.mysql.com/doc/refman/5.0/en/delete.html

语法参考：http: //dev.mysql.com/doc/refman/5.0/en/delete.html

回答by Evert

You're doing your subquery on 'b' for every row in 'a'.

您正在为“a”中的每一行对“b”进行子查询。

Try:

尝试：

DELETE FROM a USING a LEFT JOIN b ON a.id = b.id WHERE b.id IS NOT NULL;

回答by chaos

DELETE FROM a WHERE id IN (SELECT id FROM b)

回答by Scoregraphic

Maybe you should rebuild the indicies before running such a hugh query. Well, you should rebuild them periodically.

也许你应该在运行这样一个 hugh 查询之前重建索引。嗯，你应该定期重建它们。

REPAIR TABLE a QUICK;
REPAIR TABLE b QUICK;

and then run any of the above queries (i.e.)

然后运行上述任何查询（即）

DELETE FROM a WHERE id IN (SELECT id FROM b)

回答by soulmerge

The query itself is already in an optimal form, updating the indexes causes the whole operation to take that long. You could disable the keys on that table before the operation, that should speed things up. You can turn them back on at a later time, if you don't need them immediately.

查询本身已经处于最佳形式，更新索引会导致整个操作花费那么长时间。您可以在操作之前禁用该表上的键，这应该会加快速度。如果您不立即需要它们，您可以稍后重新打开它们。

Another approach would be adding a deletedflag-column to your table and adjusting other queries so they take that value into account. The fastest boolean type in mysql is CHAR(0) NULL(true = '', false = NULL). That would be a fast operation, you can delete the values afterwards.

另一种方法是向deleted您的表中添加一个标志列并调整其他查询，以便他们考虑该值。mysql 中最快的布尔类型是CHAR(0) NULL(true = '', false = NULL)。这将是一个快速的操作，之后您可以删除这些值。

The same thoughts expressed in sql statements:

sql语句中表达的相同想法：

ALTER TABLE a ADD COLUMN deleted CHAR(0) NULL DEFAULT NULL;

-- The following query should be faster than the delete statement:
UPDATE a INNER JOIN b SET a.deleted = '';

-- This is the catch, you need to alter the rest
-- of your queries to take the new column into account:
SELECT * FROM a WHERE deleted IS NULL;

-- You can then issue the following queries in a cronjob
-- to clean up the tables:
DELETE FROM a WHERE deleted IS NOT NULL;

If that, too, is not what you want, you can have a look at what the mysql docs have to say about the speed of delete statements.

如果这也不是您想要的，您可以查看 mysql 文档对删除语句速度的看法。