Analysis of an optimization method for mysql associated subqueries and optimization of mysql associated queries

Analysis of an optimization method for mysql associated subqueries and optimization of mysql associated queries

This article describes an optimization method for mysql associated subqueries. We will share this with you for your reference. The details are as follows:

In many cases, the performance of subqueries implemented on mysql is poor, which sounds a little sad. IN particular, when an IN () subquery statement is used, it is difficult to estimate the time consumption of a table of a certain magnitude. I am not very familiar with mysql, so I can only gradually understand the mystery.

Suppose there is such an exists query statement:

Select * from table1where exists (select * from table2 where id >=30000 and table1.uuid = table2.uuid );

Table1 is a 100,000-row-level table, table2 is a million-row-level table, and the local test result takes 2.40 s.

Through the explain command, we can see that the SUBQUERY is a dependent SUBQUERY. Mysql scans the entire table of table1 first, and then executes the SUBQUERY successively Based on the returned uuid. If the outer table is a large table, we can imagine that the query performance will be worse than this test.

A simple optimization solution is to use the inner join method to replace subqueries. The query statement can be changed:
Copy codeThe Code is as follows: select * from table1 innner join table2 using (uuid) where table2.id >=30000;

The local test result is 0.68 s.

Through the explain command, we can see that mysql uses the SIMPLE type (subquery or query method other than union); Mysql optimizer first filters table2, perform Cartesian Product on table1 and table2 to obtain the result set, and then filter the data using the on condition.

Of course, not all associated subqueries have poor performance. The specific results must be tested to produce results.