慢速自联接删除查询 [英] Slow self-join delete query

问题描述

它比此查询更简单吗?

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

是的，显然是的...因为当matches表很大时，上述查询的性能确实很差.

Yes, apparently it does... because the performance on the above query is really bad when the matches table is very large.

matches大约有2.2亿条记录.我希望这个DELETE查询将大小减小到大约15,000条记录.如何提高查询性能?我在两列上都有索引. UID和MatchUID是此InnoDB表中仅有的两列，均为无符号INT(10)类型.该查询已经在我的笔记本电脑(i7处理器)上运行了14个小时以上.

matches is about 220 million records. I am hoping that this DELETE query takes the size down to about 15,000 records. How can I improve the performance of the query? I have indexes on both columns. UID and MatchedUID are the only two columns in this InnoDB table, both are of type INT(10) unsigned. The query has been running for over 14 hours on my laptop (i7 processor).

推荐答案

删除如此多的记录可能要花费一些时间，我认为这是最快的方法.如果您不想投资更快的硬件，我建议另一种方法:

Deleting so many records can take a while, I think this is as fast as it can get if you're doing it this way. If you don't want to invest into faster hardware, I suggest another approach:

如果您确实要删除2.2亿条记录，那么该表只剩下15.000条记录，那就是所有条目的99,999％.为什么不

If you really want to delete 220 million records, so that the table then has only 15.000 records left, thats about 99,999% of all entries. Why not

1. 创建一个新表，
2. 只需插入要保留的所有记录，
3. 用新的替换旧的吗?

类似的方法可能会更快一些:

Something like this might work a little bit faster:

/* 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;

此后，您只需要检查并创建所需的索引，如果仅处理15.000条记录，这应该相当快.

After this you just have to check and create your desired indexes, which should be rather fast if only dealing with 15.000 records.

这篇关于慢速自联接删除查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！