删除匹配行的更快方法？

Question

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

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

我目前正在执行以下操作：

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

表a中约有10万行，表b中有约22k行。列“ id”是两个表的PK。

在我的测试箱上运行此语句大约需要3分钟-Pentium D，XP SP3、2GB内存，MySQL
5.0.67。在我看来，这很慢。也许不是，但是我希望加快速度。是否有更好/更快的方法来完成此任务？

编辑：

一些其他信息可能会有所帮助。表A和B具有与创建表B相同的结构：

CREATE TABLE b LIKE a;

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

另外，我正在使用INNO DB。

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

表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;

我怀疑问题的一部分在于此表有许多索引。表B类似于表B，虽然它仅包含列id和h。

此外，分析结果如下：

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

解决了

感谢所有的答复和评论。他们当然让我考虑了这个问题。 dotjoe致以 荣誉，让我通过问一个简单的问题“还有其他表引用a.id吗？”来解决这个问题。

问题是表A上有一个DELETE
TRIGGER，它调用了存储过程来更新其他两个表C和D。表C的FK返回a.id，并且在存储过程中做了一些与该id相关的事情之后，它有一条语句，

DELETE FROM c WHERE c.id = theId;

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

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

因此，我可以看到它在做什么，并且它提供了以下信息：

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

这告诉我这是一个很痛苦的操作，并且由于要被调用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

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

我添加了Index_1，删除时间与 mattkemp 报告的时间 一致
。就我而言，这是一个非常微妙的错误，因为在最后一刻鞋拔了一些附加功能。事实证明，正如 丹尼尔 所说，大多数建议的替代DELETE /
SELECT语句最终花费的时间基本上是相同的，并且正如 soulmerge所言
，该语句几乎是我将能够根据所构造的最好的语句我需要做。一旦为另一个表C提供了索引，我的DELETE就很快了。

验尸 ：
从这个练习中学到了两个教训。首先，很明显，我没有利用EXPLAIN语句的功能来更好地了解SQL查询的影响。那是一个菜鸟错误，所以我不会为那个问题而自责。我将从错误中学习。其次，令人反感的代码是“快速完成”心态的结果，而设计/测试不足则导致该问题不会很快出现。如果我生成了几个可观的测试数据集以用作此新功能的测试输入，那么我也不会浪费我的时间，也不会浪费你的时间。我在数据库方面的测试缺乏应用程序方面的深度。现在，我有机会改善这一点。

参考：EXPLAIN声明

Answer 1

从InnoDB删除数据是您可以要求的最昂贵的操作。正如您已经发现的那样，查询本身不是问题-无论如何，大多数查询都将针对相同的执行计划进行优化。

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

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

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

一旦所有记录都标记为删除，就成功提交事务。即使这样，也不能在将所有与快照值一起使用的其他事务（在DELETE事务之前）都结束之前，立即将它们从实际数据页中删除。

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