mysql怎么删除匹配_mysql – 更快的方式删除匹配的行？

我是一个相对的新手，当谈到数据库。我们使用MySQL，我目前正在加速一个似乎需要一段时间运行的SQL语句。我环顾了一下类似的问题，但没有找到一个。

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

我目前正在做以下事情：

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

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

这个语句在我的测试盒上运行大约3分钟 – 奔腾D，XP SP3，2GB RAM，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

解决了

感谢所有的回应和评论。他们肯定让我想到这个问题。通过询问一个简单的问题“做任何其他表引用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查询的影响。这是一个新秀的错误，所以我不会打败自己的那一个。我会从这个错误中学习。第二，冒犯的代码是“快速完成”的心态的结果，不适当的设计/测试导致这个问题没有更快出现。如果我生成了几个相当大的测试数据集用作这个新功能的测试输入，我不会浪费我的时间也不是你的。我在数据库端的测试缺乏我的应用程序端到位的深度。现在我有机会改进。