High-performance MySql evolution (10): Limitations of the query optimizer

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, MYSQL does not support

1.5 hash Association

Currently, MYSQL only supports loop nested Association and does not support HASH Association.

1.6 loose index Scanning

Description: the so-called loose index means that when a table is scanned, You can intelligently skip some records to reduce the number of records to be scanned. to better illustrate this problem, we will give an example to illustrate the benefits of loose index scanning. For example, table 1 has an index (a, B ), when you run select * from tabl1 where B between2 and 3, the following table scan modes are supported/not supported:

Column B is arranged in order, so you only need to search within a fixed range, and other records can be skipped.

B is not the first field of the index, so the last field can only be found from the first one.

The above two figures can clearly illustrate the benefits of loose indexes, but Mysql does not support this feature well and only provides this optimization for some special queries, for details, refer to the manuals of each version.

1.7 Max ()/MIN ()

Problem description:

When select max (id) from table1 where name = 'sun' is executed, MYSQL performs a full table scan if no index is created for the name.

Solution:

Convert SQL

select id from table1 use index(PRIMARY) wherename=’sun’ limit 1.

Such statements scan the table as few as possible to record 1.8 queries and updates to the same table.

Problem description:

The table cannot be updated while querying a table

Update table1t1 set  t1.cnt=(select count(*) fromtable1) 

Otherwise, an exception occurs: ERROR 1093 (HY000): You can 'tspecify target table 'ftsexchangerate' for update in FROM clause.

Solution: Convert to join table

update ftsexchangerate inner join(select currency,count(*) as cnt from ftsexchangerate group by (currency) ) as innusing(currency)set ftsexchangerate.description=inn.cnt ;