mysql 优化过程 针对DELETE慢的优化

针对delete操作, 常常会慢的严重, 明明查询出来需要删除的记录才70多条, 执行一次都得1分多钟

需求, 下单未支付同样的信息的, 删除除了最大的一条记录的信息(这边假设单人下单不超过2次相同的下单信息的, 我实际的操作是多执行几次就够了)

• 原sql

delete from course_orders
where 
pid=1 
AND state=1
AND created_at<date_sub(now(),interval 7 DAY)
AND id IN (
    SELECT max_order_id FROM (
        SELECT use_uid,course_id,COUNT(1) AS num,MAX(order_id) AS max_order_id FROM `course_order_items`
        WHERE 
        pid=1 
        AND state=0
        AND created_at>'2020-01-01'
        GROUP BY use_uid,course_id
    ) a
    WHERE num>1
)

共 73 行受到影响

执行耗时   : 59.794 sec
传送时间   : 0.001 sec
总耗时      : 59.796 sec

• 优化后

delete co from course_orders co inner join (
    SELECT max_order_id FROM (
        SELECT use_uid,course_id,COUNT(1) AS num,MAX(order_id) AS max_order_id FROM `course_order_items`
        WHERE 
        pid=1 
        AND state=0
        AND created_at>'2020-01-01'
        GROUP BY use_uid,course_id
    ) a
    WHERE num>1
) bb on pid=1 
AND state=1
AND created_at<date_sub(now(),interval 7 DAY)
AND co.id=bb.max_order_id

执行结果
共 73 行受到影响

执行耗时   : 0.066 sec
传送时间   : 0.008 sec
总耗时      : 0.075 sec