An optimization method analysis of Mysql associated subquery _mysql

This paper illustrates an optimization method of MySQL correlated subquery. Share to everyone for your reference, specific as follows:

Many times, the performance of subqueries implemented on MySQL is poor, which sounds a bit sad. In particular, in the in () subquery, it is sometimes difficult to estimate the number of tables on a certain order of magnitude. I mysql knowledge is not deep, can only slowly understand the mystery.

Suppose you have one of these exists query statements:

SELECT * FROM table1
where exists
  (SELECT * from table2 where id>=30000 and Table1.uuid=table2.uuid);

Table1 is a 100,000 row-level table, table2 for millions of rows, and native test results are 2.40s.

The subquery can be seen by explain as a correlated subquery (dependence subquery); MySQL first performs a full table scan of the outer table1, and then executes the subquery successively according to the returned UUID. If the outer table is a large table, we can imagine that query performance will be worse than the test.

A simple optimization scheme replaces subqueries with the inner join method, and the query statement can be changed to:

Copy Code code as follows:

SELECT * FROM table1 Innner join table2 using (UUID) where table2.id>=30000;

The native test results are 0.68s.

By explain you can see that MySQL uses simple types (queries other than subqueries or union); The MySQL optimizer filters the table2 first, then sets the result set on the Table1 and table2, and then filters the data through the on condition.

Of course, not all cases of the performance of the associated subquery will be poor, the specific effect needs to be tested in order to produce results.

For more information about MySQL interested readers can view the site topics: "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL common function large summary"

I hope this article will help you with the MySQL database meter.