MySQL DELETE查询性能调优求助：如何改写查询提升执行速度？

你的问题里，IN子查询版本的DELETE耗时37秒，而NOT IN版本仅用4.69秒，这是MySQL对子查询优化逻辑差异导致的典型情况。下面拆解原因并给出具体优化方案：

为什么原查询这么慢？ #

先看你的原查询：

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

虽然MASTER.expirydate有索引，但MySQL处理IN子查询时，有时会将其转化为相关子查询——简单说就是对storefront_categories的每一行都执行一次子查询验证。如果MASTER里过期的userid数量较多，这种重复查询会直接拖慢整体速度。而NOT IN版本因为是反匹配逻辑，MySQL优化器更容易选择高效的反连接执行计划，所以速度差异明显。

优化方案1：用JOIN替代IN子查询 #

这是最直接有效的优化方式，JOIN能让MySQL直接通过userid关联两张表，避免子查询的重复执行：

DELETE sc
FROM storefront_categories sc
INNER JOIN MASTER m 
    ON sc.userid = m.userid
WHERE m.expirydate < '2020-02-04';

额外提醒：如果storefront_categories.userid还没建索引，一定要补上——这会让JOIN的关联速度大幅提升：

CREATE INDEX idx_storefront_userid ON storefront_categories(userid);

优化方案2：用EXISTS替代IN #

EXISTS子查询的逻辑是“是否存在匹配行”，MySQL对EXISTS的优化通常比IN更友好，尤其是关联字段有索引时：

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

这里用SELECT 1而非SELECT userid，是因为数据库只需要确认存在匹配行即可，不需要返回实际字段值，能节省一点性能开销。

验证优化效果 #

你可以用EXPLAIN命令对比原查询和优化后查询的执行计划：

EXPLAIN DELETE FROM storefront_categories WHERE userid IN (SELECT userid FROM MASTER where expirydate<'2020-2-4' );
EXPLAIN DELETE sc FROM storefront_categories sc JOIN MASTER m ON sc.userid = m.userid WHERE m.expirydate < '2020-02-04';

重点看type列（是否出现ref或eq_ref这类高效关联类型）、rows列（预计扫描的行数），就能直观看到优化后的查询减少了多少不必要的扫描操作。

内容的提问来源于stack exchange，提问作者Cbpro Ads