MySql 复杂的删除语句很慢

delete from product_parameter where parameter_id in (
select p4.parameter_id from product p1 inner join product_parts_relation p2 on p1.product_id = p2.product_id and p1.version = p2.version
inner join product_parts_element_relation p3 on p2.parts_id = p3.parts_id
inner join product_element_parameter_relation p4 on p4.element_id = p3.element_id
where
p1.`status` = 'published' and p1.is_last_version = '1' and p1.is_inuse = '1'
and p2.parts_type = 'first_product'
and p3.element_type in ('prepayFee','prepayFeeSeg')
and p1.product_id in ('abc'));

这个SQL在几百万的数据量下执行超慢，看了下执行计划，是先遍历执行delete，每一个遍历结果都执行一遍子查询，总共需要执行24W+次子查询

 

改成使用连接的方式，看执行计划，最后才执行delete语句

delete a from product_parameter a 
inner join product_element_parameter_relation p4 on p4.parameter_id = a.parameter_id 
inner join product_parts_element_relation p3 on p4.element_id = p3.element_id
inner join product_parts_relation p2 on p2.parts_id = p3.parts_id 
inner join product p1 on p1.product_id = p2.product_id and p1.version = p2.version 
where 
p1.`status` = 'published' -- and p1.is_last_version = '1' 
and p1.is_inuse = '1'
and p2.parts_type = 'first_product' 
and p3.element_type in ('prepayFee','prepayFeeSeg')
and p1.product_id in 
('abc');