【MySQL】多表delete优化案例

前言：今天在叶老网站看到一篇多表delete优化案例，这里做一个学习总结。

原SQL：

DELETE
FROM trade_info
WHERE id IN (
SELECT id
FROM (
SELECT a.id
FROM trade_info a, order_info b, USER c
WHERE
b.buyer = c.id AND c.itv_account=’90000248′ AND a.order_id = b.id) temp)

可以看到执行计划最终是走的全表扫描，效率很低

优化思路：
本例的优化思路是利用多表join关联消除子查询造成的全表扫描

改写成SELECT：

SELECT id
FROM trade_info
WHERE
id IN (
SELECT id
FROM (
SELECT a.id
FROM trade_info a, order_info b, USER c
WHERE
b.buyer = c.id AND c.itv_account=’90000248′ AND a.order_id = b.id) temp)

执行计划如下：

可以看到执行计划由全表扫描变为基于主键的等值查询，效率大大提升

改写多表delete

官网例子：

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

按照官网例子改写如下

DELETE trade_info
FROM
trade_info,
(
SELECT
a.id
FROM
trade_info a
JOIN order_info b ON a.order_id = b.id
JOIN USER c ON b.buyer = c.id
WHERE
c.itv_account = '90000248'
) t2
WHERE trade_info.id = t2.id;

参考文章:http://imysql.com/2016/06/29/mysql-optimization-rewrite-delete-subquery-to-join.shtml