数据库DELETE数据使用IN很慢

🔊 原始SQL
有个需求，是通过主表删除子表的数据，通常的SQL写法是：

DELETE FROM childTable WHERE RELATION_ID IN(SELECT ID FROM parentTable WHERE YEAR=2023)
1

执行这个SQL语句结果报错代码超时了，好几百秒还没执行完
来解析一下这个代码出了啥问题

rows： 是读取数据的时候需要扫描的记录条数，我们没有索引，也没有走索引，所以是全表扫描的。
filtered： 一般是连表的时候才需要注意的内容，意思是过滤后剩下的数据百分比，刚入门的时候说过连表要小表驱动大表，因为大表执行的慢，所以执行次数要尽量小，单位 % 。
解析一下执行结果的含义：
SQL执行过程中外面的delete是驱动表，内部select是被驱动表，结果就是要对子查询执行37734*100%次，也就是驱动子查询执行37734次（这就分析出来数据执行的逻辑了，意思是挨个数据执行一下看看满足不满足子查询条件），所以如果被删除内容的子表稍微大一点执行很慢。

最终执行的SQL大概是这样的
//遍历3W多条数据
for row in childTable{
	//然后每一条的去判断满足子查询条件吗
	if row.RELATION_ID IN (SELECT ID FROM parentTable WHERE YEAR=2023)
		DELETE;
}	
1
2
3
4
5
6
7

这个写法不管加不加索引都不走索引的。

🔊 优化

DELETE a 
FROM childTable  a
JOIN (
	SELECT ID FROM parentTable WHERE YEAR = 2023 
) b ON a.RELATION_ID = b.ID
1
2
3
4
5

优化后的结果，几秒执行完，EXPLAIN 解析一下这个SQL删除的结果：

看看这个结果是分析用parentTable表作为扫描表，子表childTable表作为被扫描表，连表驱动了17282*5% = 864次，所以驱动了删除864次。
解析一下执行结果的含义：
这个分析结果是我们理想的执行结果，我们写SQL的逻辑的时候想的就是删除子表中满足主表查询条件的内容，也就是先查主表，然后通过主表的结果删除子表

最终执行的SQL大概是这样的：
//查出来了N条ID
SELECT ID FROM parentTable WHERE YEAR = 2023  
//执行遍历删除，遍历3W多条数据
for row in childTable{
	//然后每一条的去判断满足子查询条件吗
	if row.RELATION_ID IN (IDS)
		DELETE;
}	
1
2
3
4
5
6
7
8
9

这才是我们想的SQL改执行的效果。
这个SQL如果用在SpringBoot+Mybatis中需要在Mapper方法加注解：

@InterceptorIgnore(blockAttack = "true")
1

🔊 加上索引
上面只是大表小表驱动问题，当然如果加上索引效率会更高一些，再看一下加上索引的执行效率，执行时间几乎忽略。

解析一下执行结果的含义：
因为走了索引，所以子表已经通过索引减少了扫描的行数，通过where条件已经定位到了需要的数据。

最终执行的SQL大概是这样的：
//查出来了N条ID
SELECT ID FROM parentTable WHERE YEAR = 2023  
//然后遍历，这里需要注意的遍历的条数只有通过索引定位的2条数据
for row in childTable{ //
	//每一条的去判断满足子查询条件吗
	if row.RELATION_ID IN (IDS)
		DELETE;
}	
1
2
3
4
5
6
7
8
9