Mysql Optimization Series Delete subquery rewrite optimization _mysql

1, the problem description

A friend encounters a strange thing, a delete with a subquery, the execution efficiency is very low. The change of Delete to a select is very fast, but it is not a solution.

Here's the delete with the subquery:

[Yejr@imysql.com]mydb > EXPLAIN Delete from Trade_info where-ID in (
select a.id from
trade_in Fo A, order_info B, user C where
b.buyer = c.id and c.itv_account= ' 90000248′and a.order_id = b.ID ' temp) \g

The DDL for several tables is this:

The execution of this SQL in the above time is: 31.74 seconds
Query OK, 5 rows Affected (31.74 sec)
If we rewrite delete to select, the execution time is only 0 seconds to compare the execution plan:

[Yejr@imysql.com]mydb >explain Select ID from trade_info where
-ID in (
Select-ID from (
select-a.ID from TR Ade_info A, order_info B, user C where
b.buyer = c.id and c.itv_account= ' 90000248′and a.order_id = b.ID ' temp) \g

As you can see, the Trade_info table from the full table scan (Type=all) into a primary key equivalent query (TYPE=EQ_REF), the amount of planned scan data from 5.71 million to 1, but also to avoid back to the table, the 2 SQL contrast costs a huge difference.

2, the idea of optimization

Since this SQL changed the delete to select after the execution efficiency can be greatly improved, in addition to no special differences, may be the query optimizer some deficiencies, resulting in no direct optimization, you have to find another way.
Our idea is to change the delete simplification based on subqueries into multiple table join after delete (in general, the subquery is less efficient, you can consider to write a join), multiple table Delete syntax lesson reference: https://dev.mysql.com/doc/ refman/5.7/en/delete.html#idm140469624466800, such as this:

DELETE T1 from T1 left JOIN T2 on t1.id=t2.id WHERE t2.id is NULL;

Referring to the form above, the rewritten SQL becomes the following:

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;

You can see that the new SQL execution is a lot more efficient, and you don't need to scan 5.71 million records, it takes only 0.01 seconds to execute.

Query OK, 5 rows affected (0.01 sec)

3. Other suggestions

Although the version of MySQL 5.6 and above is optimized for subqueries, the results of this case are not satisfactory in some cases.
Therefore, if you find that some subquery SQL is less efficient, you can try to rewrite the join form to see if there is any elevation. In addition, the query optimizer should also be brave enough to find ways to bypass these pits under individual circumstances.