删除查询的MySQL性能优化

Question

有人能帮我重写查询以加快执行时间吗？它用了37秒来执行。

DELETE FROM storefront_categories 
WHERE userid IN (SELECT userid 
                FROM MASTER 
                where expirydate<'2020-2-4' 
                )

同时，该查询只需4.69秒即可执行。

DELETE FROM storefront_categories 
WHERE userid NOT IN (SELECT userid FROM MASTER)

表storefront_categories有97K记录，在主中有40K记录。我们已经在MASTER.expirydate字段上创建了一个索引。

Answer 1

当删除40K行时，希望它需要时间。主要成本(假设有足够的索引和良好的查询)是“原子”删除的事务性语义的开销。这涉及到删除每一行的副本，以防发生崩溃。这样，InnoDB就可以使数据库恢复到崩溃前的状态。

当删除40%的表时，将行复制到另一个表中以保存到另一个表中的速度要快得多。

当删除大量行(不管百分比)时，最好是以块的形式进行。最好在PRIMARY KEY的基础上遍历表格。

我在http://mysql.rjweb.org/doc.php/deletebig中讨论了这两种技术以及其他技术。

至于查询的提法：

它依赖于版本；老版本的MySQL在某些performers.

• IN (SELECT ...)上做得很差，而NOT EXISTS往往是最糟糕的JOIN.

• (Bottom和/或EXISTS可能是better.

• "Multi-table DELETE的另一种选择。它的工作方式类似于JOIN.

• (Bottom line:您没有说您运行的是哪个版本；我无法预测哪个版本是最好的。)

• 我的博客避免了关于公式的争论。

Answer 2

这个查询看起来很好。

我建议采用以下指标进行优化：

master(expiry_date, userid)
storefront_categories(userid)

第一个索引是master上子查询的覆盖索引:它意味着数据库应该能够只通过查看索引来执行子查询(而在索引中只有expiry_date，它仍然需要查看表数据以获取相关的userid)。

第二个索引允许数据库优化in操作。

Answer 3

我会尝试使用exists：

DELETE 
FROM storefront_categories 
WHERE EXISTS (SELECT 1 
              FROM MASTER M 
              WHERE M.userid = storefront_categories.userid AND
                    M.expirydate <'2020-02-04'  
              );

在这里，索引将是元的，我希望索引在storefront_categories(userid) & MASTER(userid, expirydate)上。