MySQL 5.7使用JOIN加LIMIT分批次删除9亿+行数据执行报错如何解决

问题代码错误排查 #

• 语法错误1：order是MySQL保留关键字，作为表名使用必须加反引号包裹，否则直接触发语法报错
• 语法错误2：子查询内别名笔误，order_item_custom_data的别名定义为oicd，但关联条件中误写为不存在的ocid
• 语法错误3：派生表的SELECT语句末尾多写了语句结束符;，嵌套查询内部不允许加结束符
• 逻辑错误1：关联删除的匹配条件仅用mfg_id关联，会误删order_item_custom_data表中所有同mfg_id的行，远超出你筛选的10万条范围
• 逻辑错误2：原关联条件oicd.catalog_id = o.catalog_id AND ocid.catalog_id = o.order_id逻辑冲突，属于笔误，推测是要关联oicd.order_id = o.order_id

修正后的分批次删除语句 #

建议直接用主键关联删除，性能最高且不会误删数据：

DELETE a
FROM order_item_custom_data a
JOIN (
    SELECT oicd.order_item_custom_data_id 
    FROM order_item_custom_data oicd 
    JOIN `order` o
      ON oicd.mfg_id = o.mfg_id 
      AND oicd.catalog_id = o.catalog_id 
      AND oicd.order_id = o.order_id
    WHERE o.order_date < '2020-01-01 00:00:00'
    ORDER BY oicd.order_item_custom_data_id
    LIMIT 100000
) b ON a.order_item_custom_data_id = b.order_item_custom_data_id;

大批量删除优化建议 #

• 单次删除量调整为1万~5万即可，避免长事务持锁时间过长影响线上业务
• 如果待删除数据占表总量的70%以上，建议先将需要保留的数据导出到新表，再用重命名表的方式替换原表，执行速度比分批次删除快数十倍
• 删除前确保order表的order_date字段、order_item_custom_data的关联字段已建索引，避免每次查询扫全表
• 可以搭配存储过程循环执行上述删除语句，直到返回影响行数为0即可停止

内容的提问来源于stack exchange，提问作者Nav