MySQL关联DELETE语句远慢于查询的问题排查与优化咨询

表结构说明 #

t_source_category表（不足1000行）结构：

CREATE TABLE `t_source_category` (
  `id` smallint NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `pid` smallint DEFAULT NULL,
  `level` tinyint DEFAULT NULL,
  `path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `leaf` bit(1) DEFAULT NULL,
  `sort` decimal(12,8) DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `1` (`title`,`pid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1066 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

t_source_item__category表结构：

CREATE TABLE `t_source_item__category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `item_id` bigint DEFAULT NULL,
  `category_id` int DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=18601 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;

性能差异现象 #

• 关联查询耗时仅0.129s：

EXPLAIN ANALYZE 
    SELECT * FROM t_source_category t1 
        LEFT JOIN t_source_item__category t2 ON t2.category_id = t1.id 
    WHERE t2.item_id IS NULL AND t1.leaf = 1;

执行计划采用Hash Join，仅对t2做1次全表扫描：

-> Filter: (t2.item_id is null)  (cost=507334.85 rows=500864) (actual time=96.914..96.914 rows=0 loops=1)
    -> Left hash join (t2.category_id = t1.id)  (cost=507334.85 rows=500864) (actual time=41.850..95.894 rows=12537 loops=1)
        -> Filter: (t1.leaf = 1)  (cost=82.25 rows=405) (actual time=0.046..0.462 rows=685 loops=1)
            -> Table scan on t1  (cost=82.25 rows=810) (actual time=0.041..0.356 rows=811 loops=1)
        -> Hash
            -> Table scan on t2  (cost=16.24 rows=12367) (actual time=0.029..2.984 rows=12546 loops=1)

• 关联DELETE语句耗时5.870s：

EXPLAIN ANALYZE 
    DELETE t1 FROM t_source_category t1 
        LEFT JOIN t_source_item__category t2 ON t2.category_id = t1.id
    WHERE t2.item_id IS NULL AND t1.leaf = 1;

执行计划采用Nested Loop Join，对t2做了685次全表扫描（循环次数等于t1中符合leaf=1的行数）：

-> Delete from t1 (immediate)
    -> Filter: (t2.item_id is null)  (cost=502073.96 rows=500864) (actual time=5800.754..5800.754 rows=0 loops=1)
        -> Nested loop left join  (cost=502073.96 rows=500864) (actual time=0.100..5799.583 rows=12537 loops=1)
            -> Filter: (t1.leaf = 1)  (cost=82.25 rows=405) (actual time=0.040..2.560 rows=685 loops=1)
                -> Table scan on t1  (cost=82.25 rows=810) (actual time=0.028..2.087 rows=811 loops=1)
            -> Filter: (t2.category_id = t1.id)  (cost=3.09 rows=1237) (actual time=3.329..8.460 rows=18 loops=685)
                -> Table scan on t2  (cost=3.09 rows=12367) (actual time=0.005..7.649 rows=12546 loops=685)

核心问题 #

1. 索引缺失：t_source_item__category表的category_id字段未创建索引，导致关联时无法快速匹配t1.id，只能依赖全表扫描。
2. 执行计划选择差异：查询语句可通过Hash Join优化，仅扫描t2一次；但DELETE语句的优化器选择了Nested Loop Join，对t2进行多次全表扫描，这是耗时剧增的根本原因。

优化方案 #

1. 添加关联字段索引（最彻底的解决方法） #

给t_source_item__category的category_id创建索引，让关联操作通过索引快速定位数据，无论是查询还是DELETE都会高效执行：

CREATE INDEX idx_tsic_category_id ON t_source_item__category(category_id);

添加索引后，执行计划会改用索引扫描替代全表扫描，连接方式也会更高效，耗时会显著降低。

2. 改写DELETE语句为NOT EXISTS子查询 #

如果暂时无法添加索引，可改用NOT EXISTS子查询改写，引导优化器选择更高效的执行逻辑：

DELETE FROM t_source_category t1
WHERE t1.leaf = 1
AND NOT EXISTS (
    SELECT 1 FROM t_source_item__category t2
    WHERE t2.category_id = t1.id
);

这种写法会先筛选出t1中leaf=1的记录，再逐一检查是否存在关联的t2记录，避免对t2进行多次全表扫描。

3. 先查询待删除ID，再批量删除 #

将关联查询和删除操作分离，先获取符合条件的ID，再批量删除，逻辑更可控：

-- 第一步：查询所有需要删除的分类ID
SELECT t1.id 
FROM t_source_category t1
LEFT JOIN t_source_item__category t2 ON t2.category_id = t1.id
WHERE t2.item_id IS NULL AND t1.leaf = 1;

-- 第二步：批量删除（将上面查询的ID替换到列表中）
DELETE FROM t_source_category 
WHERE id IN (/* 第一步查询出的ID列表 */);

如果待删除ID数量较多，可使用临时表存储ID，再关联删除，避免IN子句过长的问题。

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