mysql优化delete子查询

一、背景

处理现场业务的时候，发现delete数据的时，出现了死锁的状态

二、分析

先看删除的sql

delete from 
    t_fee_water_info_history 
 where 
    history_id in (
        select 
            distinct meter_point_history_id
        from 
            t_fee_water_dosage_collect 
        where 
            area_code='730101' 
        and 
            end_date='2023-05-31' 
        and 
            meter_code = 'S200001' 
        and 
            version=1    
)

history_id字段是t_fee_water_info_history表的主键索引，

t_fee_water_dosage_collect表的查询条件没有建立索引，但是单独查询很快，只需要0.2s

但是整个sql语句删除就死锁了，使用explain查看

 

可以查看删除语句因为子查询的存在，没有使用主键索引，而是使用全表扫描，如果直接把子查询的查询结果作为条件，又是可以使用主键索引的，如下图

 

使用explain分析

 

结论：使用delete删除的时候别使用子查询结果作为条件

 三、解决措施

使用inner jin替代子查询：

DELETE Aname FROM Aname INNER JOIN Bname ON (Aname.id = Bname.id) 
and 其他查询条件;

修改后的语句

DELETE 
    t_fee_water_info_history 
FROM 
    t_fee_water_info_history 
INNER JOIN t_fee_water_dosage_collect 
ON(t_fee_water_info_history.history_id=t_fee_water_dosage_collect.meter_point_history_id)
and 
    t_fee_water_dosage_collect.area_code='730101' 
and 
    t_fee_water_dosage_collect.end_date='2023-05-31' 
and 
    t_fee_water_dosage_collect.meter_code = 'S200001' 
and 
    t_fee_water_dosage_collect.version=1;

使用explain分析，会发现delete表的主键索引可以使用了