慢速自连接删除查询

Question

还有比这个查询更简单的吗?

delete a.* from matches a
    inner join matches b ON (a.uid = b.matcheduid)

是的，显然它是……因为当matches表非常大时，上面查询的性能真的很差。

matches约有2.2亿条记录。我希望这个DELETE查询将大小降低到大约15,000条记录。如何提高查询的性能?我在两列上都有索引。UID和MatchedUID是这个InnoDB表中仅有的两列，它们都是INT(10) unsigned类型。这个查询在我的笔记本电脑(i7处理器)上已经运行了14个多小时。

Answer 1

删除这么多记录可能需要一段时间，我认为如果你这样做，这是最快的。如果您不想投资更快的硬件，我建议您使用另一种方法:

如果你真的想删除2.2亿条记录，那么这个表只剩下15000条记录，这大约是所有条目的99999%。为什么不

1. 创建新表
2. 插入所有你想保存的记录，
3. 并把你的旧的换成新的?

这样做可能会更快一些:

/* creating the new table */
CREATE TABLE matches_new
SELECT a.* FROM matches a
LEFT JOIN matches b ON (a.uid = b.matcheduid)
WHERE ISNULL (b.matcheduid)
/* renaming tables */
RENAME TABLE matches TO matches_old;
RENAME TABLE matches_new TO matches;

之后，你只需要检查和创建你想要的索引，如果只处理15000条记录，这应该是相当快的。

Answer 2

运行explain select a.* from matches a inner join matches b ON (a.d uid = b. matcheduid)将解释您的索引是如何存在和使用的

Answer 3

我可能在这里设置了自己，但是在自连接中执行这样的删除操作，每次删除后查询不都必须重新计算连接索引吗?

虽然这是笨拙和暴力的，你可以考虑:

。创建一个临时表来存储内部连接产生的uid，然后连接到THAT，然后执行删除。

或

B。添加一个布尔(位)类型的列，使用连接标记每个匹配(此操作应该是FAST)，然后使用:

DELETE * FROM matches WHERE YourBitFlagColumn = True

然后删除布尔列

Answer 4

您可能需要批量删除。您可以通过使用公共表表达式的递归删除来实现这一点，或者只是在某些批处理大小上迭代它。