🔊 原始SQL
有个需求,是通过主表删除子表的数据,通常的SQL写法是:
DELETE FROM childTable WHERE RELATION_ID IN(SELECT ID FROM parentTable WHERE YEAR=2023)
执行这个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;
}
这个写法不管加不加索引都不走索引的。
🔊 优化
DELETE a
FROM childTable a
JOIN (
SELECT ID FROM parentTable WHERE YEAR = 2023
) b ON a.RELATION_ID = b.ID
优化后的结果,几秒执行完,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;
}
这才是我们想的SQL改执行的效果。
这个SQL如果用在SpringBoot+Mybatis中需要在Mapper方法加注解:
@InterceptorIgnore(blockAttack = "true")
🔊 加上索引
上面只是大表小表驱动问题,当然如果加上索引效率会更高一些,再看一下加上索引的执行效率,执行时间几乎忽略。
解析一下执行结果的含义:
因为走了索引,所以子表已经通过索引减少了扫描的行数,通过where条件已经定位到了需要的数据。
最终执行的SQL大概是这样的:
//查出来了N条ID
SELECT ID FROM parentTable WHERE YEAR = 2023
//然后遍历,这里需要注意的遍历的条数只有通过索引定位的2条数据
for row in childTable{ //
//每一条的去判断满足子查询条件吗
if row.RELATION_ID IN (IDS)
DELETE;
}