Detailed explanation of subquery examples encountered in mysql in the production library

DBAs or developers who have used Oracle or other relational databases all have this experience. They all think that the database has been optimized for subqueries and can make good choices to drive table execution, and then transplant this experience to mysql. database, but unfortunately, mysql may disappoint you in the processing of subqueries. In our production system, we encountered this problem:

select  i_id, sum(i_sell) as i_sell
from table_data
where i_id in (select i_id from table_data where Gmt_create >= ‘2011-10-07 00:00:00’)
group by i_id;

(Note: The business logic of sql can For example: first query the 100 books newly sold on 10-07, and then query the sales of these 100 newly sold books throughout the year).

The performance problem of this SQL is due to the weakness of the mysql optimizer in processing subqueries. When the mysql optimizer processes subqueries, it will rewrite the subqueries. Normally, we hope to complete the results of the subquery from the inside to the outside first, and then use the subquery to drive the outer query table to complete the query; but mysql processing will first scan all the data in the outer table, and each The data will be transferred to the subquery and associated with the subquery. If the table is very large, there will be performance problems;

For the above query, since the data in the table_data table has 70W of data, At the same time, there is a lot of data in the subquery, and a large amount of it is repeated, which requires nearly 700,000 correlations. The large number of correlations causes this sql to be executed for several hours without being completed, so we need to rewrite the sql:

SELECT t2.i_id, SUM(t2.i_sell) AS sold
FROM (SELECT distinct i_id FROM table_data
WHERE gmt_create >= ‘2011-10-07 00:00:00’) t1,  table_data t2
WHERE t1.i_id = t2.i_id GROUP BY t2.i_id;

We changed the subquery to association, and added distinct to the subquery to reduce the number of times t1 is associated with t2;

After the transformation, the execution time of sql dropped to less than 100ms.

The above is the detailed content of Detailed explanation of subquery examples encountered in mysql in the production library. For more information, please follow other related articles on the PHP Chinese website!