mysql's sub-query optimization has not been very friendly, and has been criticized by the industry.One of the most encountered problems in sql optimization, you can click here, here to get some information, mysql When processing sub-queries, the sub-queries will be rewritten.Usually, we want to complete the sub-query results from the inside to the outside, and then use the sub-queries to drive the tables of the outer query to complete the query, but On the contrary, subqueries will not be executed first; today I hope to deepen my understanding of mysql subqueries by introducing some practical cases:

Case: The user reported that the database response was slow, and many business updates were stuck; log in to the database to observe, and found the long-running sql;

Sql is:

2.Updates to other tables are blocked:

In order to restore the application as soon as possible, after killing the long-running sql, the application will return to normal;
3.Analyze the execution plan:

From the execution plan, we start to optimize step by step:
First, let's look at the second line of the execution plan, which is the part of the subquery, orderdto1_ has performed a full table scan, let's see if we can add appropriate indexes:
A.Use Covering Index:

Adding a composite index exceeds the maximum key length limit:
b.View field definitions for this table:

C.View the average length of table fields:

D.Reduce field length

Analyze the execution plan again:

It is found that the performance is still not improving.The key is that the number of rows scanned by the two tables has not decreased (8962*41005).The index added above has no great effect.Now check the execution result of the t table:

The result set is empty, so the result set of the t table needs to be used as the driving table;
4.Through the above test and verification, the performance of ordinary mysql sub-query writing is very poor.Because of the natural weakness of mysql sub-query, it is necessary to rewrite sql into an associated writing method:

5.View the execution plan:

6.Execution time:

reduced to milliseconds;