Mysql optimized series DELETE subquery rewriting optimization, mysqldelete

Mysql optimized series DELETE subquery rewriting optimization, mysqldelete

1. Problem Description

A friend encounters a strange problem. The execution efficiency of a DELETE statement using subqueries is very low. After changing the DELETE statement to the SELECT statement, the statement is executed quickly.

Here is the DELETE statement that uses the subquery:

[yejr@imysql.com]mydb > EXPLAIN delete from trade_info where id in (select id from (select a.id from trade_info a, order_info b, user c whereb.buyer = c.id and c.itv_account='90000248′ and a.order_id = b.id) temp)\G

The DDL statements for several tables are as follows:

The execution time of the preceding SQL statement is: 31.74 seconds.
Query OK, 5 rows affected (31.74 sec)
If we rewrite the DELETE statement to the SELECT statement, the execution time is only 0 seconds. Let's compare the execution plan:

[yejr@imysql.com]mydb >EXPLAIN select id from trade_info whereid in (select id from (select a.id from trade_info a, order_info b, user c whereb.buyer = c.id and c.itv_account='90000248′ and a.order_id = b.id) temp)\G

As you can see, the trade_info table changes from the full table scan (type = ALL) to the primary key-based equivalent query (type = eq_ref), and the amount of data to be scanned is also changed from 5.71 million to 1, in addition, you can avoid going back to the table. The cost of comparing the two SQL statements varies greatly.

2. Optimization ideas

Since the execution efficiency of this SQL statement can be greatly improved after the DELETE statement is changed to SELECT statement, the Query Optimizer may be insufficient to directly optimize the statement, you have to find another solution.
Our idea is to simplify the DELETE statement based on subqueries into the DELETE statement after multi-table JOIN (in general, if the efficiency of subqueries is relatively low, you can consider rewriting to JOIN ), for more information about the syntax of Multi-table DELETE, see explain:

DELETE t1 FROM t1 left join t2 ON t1.id = t2.id WHERE t2.id is null;

With reference to the above form, the rewritten SQL becomes as follows:

DELETE trade_infoFROMtrade_info,(SELECTa.idFROMtrade_info aJOIN order_info b ON a.order_id = b.idJOIN user c ON b.buyer = c.idWHEREc.itv_account = ‘90000248') t2 where trade_info.id = t2.id;

It can be seen that the execution efficiency of the new SQL statement is much higher, and 5.71 million records do not need to be scanned. The execution time is only 0.01 seconds.

Query OK, 5 rows affected (0.01 sec)

3. Other suggestions

Although MySQL 5.6 and later versions have been optimized for subqueries, the results of this case show that it is still unsatisfactory in some cases.
Therefore, if you find that the SQL efficiency of some subqueries is poor, you can try to rewrite it to the JOIN form to see if it has improved. In addition, you must be brave enough to suspect that the query optimizer is insufficient in some cases and try to bypass these pitfalls.