High-Performance MySql evolution (10): Limitations of the query optimizer _ MySQL

High-Performance MySql evolution (10): Limitations of the query optimizer bitsCN.com

The article "common methods of the query optimizer" lists some common optimization methods of the optimizer. The query optimizer also has some limitations while providing these features. these limitations are often improved with the upgrade of the MYSQL version, so this article will list some common limitations, and does not contain all.

1.1 associate a subquery

Description:

Because select... From table1 t1 where t1.id in (select t2.fk from table2 t2 wheret2.id = '... ') Statements of the type are often optimized to select .... From table1 t1 where exists (select * from table2 t2 where t2.id = '... 'And t2.fk = t1.id). because the value of table1 cannot be determined during tabl2 query, table1 is scanned in full table.

Solution:

Try to replace IN () with inner join, and rewrite it to select * from table1 t1 inner jointable2 t2 using (id) where t2.id = '... '

1.2 UNION restrictions

Description:

The UNION operation does not push operations outside the UNION to each subset.

Solution:

Add separate restrictions for each sub-operation

For example, the student table has 10000 records and the member table has 10000 records. if you want to sort the first 20 records of the two tables by name, if you want to add limit to each subquery, the limit operation on the outermost layer will retrieve 20 from 40 records; otherwise, 20 from 20000 records.

(select name from student order by name limit 20) union all (select name from memberorder by member limit 20) limit 20 

1.3 equivalent transfer

Operations such as IN, ON, and Using usually share the values of a list among multiple tables during query operations, for convenience of optimization, the optimizer copies the values in the list for each related table. if the list is very large, it will affect the performance.

So far, there are no good strategies to deal with this problem.

1.4 parallel execution

Currently