MySQL DELETE子查询性能优化实战：FROM JOIN改写技巧

问题现象：DELETE子查询效率极低

在实际业务中，开发者常遇到一种奇怪的现象：一条用于删除数据的SQL语句，将其中的DELETE关键字替换为SELECT后查询速度极快，但保留DELETE却极其缓慢。这通常意味着查询优化器在执行删除操作时，选择了次优的执行计划。

以下是一个涉及三张表（trade_info、order_info、user）的子查询DELETE示例：

EXPLAIN DELETE FROM trade_info WHERE id IN (
  SELECT id FROM (
    SELECT a.id 
    FROM trade_info a, order_info b, user c 
    WHERE b.buyer = c.id 
      AND c.itv_account = '90000248' 
      AND a.order_id = b.id
  ) temp
)\G

涉及的表结构定义如下：

执行该DELETE语句的实际耗时高达 31.74秒，仅影响了5行数据：

Query OK, 5 rows affected (31.74 sec)

若将操作改为SELECT验证结果集：

EXPLAIN SELECT id FROM trade_info WHERE id IN (
  SELECT id FROM (
    SELECT a.id 
    FROM trade_info a, order_info b, user c 
    WHERE b.buyer = c.id 
      AND c.itv_account = '90000248' 
      AND a.order_id = b.id
  ) temp
)\G

执行计划对比显示，SELECT查询中trade_info表使用了主键等值查询（type=eq_ref），预计扫描行数仅为1行；而DELETE查询中，trade_info表退化为全表扫描（type=ALL），需扫描约571万行记录。这种巨大的代价差异直接导致了性能的断崖式下跌。

优化思路：将子查询改写为多表JOIN

既然相同逻辑的SELECT能快速执行，说明数据筛选逻辑本身没有性能问题，瓶颈在于优化器对IN子查询的处理策略。MySQL优化器在处理IN(subquery)时，有时会优先选择外驱动表的逐行扫描，而非先内层过滤再外联。

有效的解决策略是利用MySQL的多表DELETE语法，将子查询转换为JOIN连接。标准语法参考如下：

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

参照此模式，我们将原SQL改写为基于FROM子句的多表删除：

DELETE trade_info 
FROM trade_info,
(
  SELECT a.id 
  FROM trade_info a 
  JOIN order_info b ON a.order_id = b.id 
  JOIN user c ON b.buyer = c.id 
  WHERE c.itv_account = '90000248'
) t2 
WHERE trade_info.id = t2.id;

在这个改写中：

• 内部子查询被保留用于快速筛选出需要删除的ID列表。
• 外层DELETE通过FROM ... JOIN (...)的方式，将主表trade_info与筛选出的ID集合t2进行连接。
• WHERE trade_info.id = t2.id强制优化器使用主键进行匹配。

改写后的执行计划及效果如下图所示：

新的执行计划完全避免了全表扫描，直接通过主键定位数据。实际执行耗时从31.74秒骤降至 0.01秒：

Query OK, 5 rows affected (0.01 sec)

优化建议总结

尽管MySQL 5.6及以上版本在子查询优化上有所改进，但在复杂的多表关联场景中，IN或EXISTS子查询仍可能触发非预期的全表扫描。建议在遇到此类性能问题时：

1. 怀疑优化器限制：当SELECT正常但DELETE/UPDATE极慢时，优先考虑执行计划选择的差异。
2. 改用JOIN重写：利用DELETE t1 FROM t1 JOIN subquery ON ...的结构，帮助优化器尽早确定匹配行。
3. 验证执行计划：始终使用EXPLAIN检查type字段，确保关联操作使用的是索引访问（如ref、eq_ref、range），而非ALL。

以上就是MySQL DELETE子查询性能优化的详细内容，更多关于SQL调优与数据库性能的资料请关注本站其它相关文章！