Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the methods of MySQL optimization statement execution". In daily operation, I believe many people have doubts about the methods of MySQL optimization statement execution. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what are the methods of MySQL optimization statement execution?" Next, please follow the editor to study!

Like leader optimization

Like fuzzy queries such as'% AAA%' 'and'% AAA' will not use indexes, but this form may inevitably be needed in business.

There are two common methods:

Optimization plan 1: use the overlay index, that is, the queried columns can be obtained only with the index, without querying the table records, thus leaving the index.

Optimization plan 2: use locate function or position function instead of like query: for example, table.field like'% AAA%' can be changed to locate ('AAA', table.field) > 0 or POSITION (' AAA' IN table.field) > 0

In and exist

If the two tables of the query are of the same size, there is little difference between using in and exists. If one of the two tables is smaller and the other is a large table, the large subquery table uses exists, and the subquery table small uses in: for example: table A (small table), table B (large table)

Example 1:

Example 2:

Not in and not exist

If the query statement uses not in, then both the inside and outside of the table are scanned without using the index, while the subquery of not exist can still use the index on the table. So no matter which watch is big, using not exists is faster than not in!

Subquery optimization

Versions prior to MySQL 5.6 deal with subqueries: the result set of the query is not calculated to be used as an join,outer table with other tables. Every time a piece of data is scanned, the subquery is re-executed.

MySQL 5.6 subquery processing: the result set of the subquery cache into the temporary table, the temporary table index is mainly used to remove duplicate records, and may also be used to do join query, this technique is called materialized subquery in 5.6. the materialized subquery can see that the select_type field is subquery, and in 5.5 is DEPENDENT SUBQUERY.

Generally, the subquery can be changed into the associated query of the table, and the subquery will have the creation and destruction of temporary tables, which is inefficient.

Straight_join

Mysql hint:

When dealing with the association of multiple tables, the Mysql optimizer is likely to choose the wrong driver table for association, resulting in an increase in the number of associations, which makes the execution of sql statements very slow.

At this time, you need an experienced DBA to judge and choose the correct driver table, and then straightjoin plays a role. Let's take a look at the case of using straight_join for optimization:

Try to use the user table as the driver table and use straight_join to force the join order:

Efficient paging

Traditional pagination:

Select * from table limit 10000 no. 10

Limit principle:

Limit 10000,10

The greater the offset, the slower the offset.

Recommended pagination:

Optimization of complex correlation SQL

First of all, the query returns the result set, usually the result set returned by the query is very few, there is room for optimization.

By looking at the execution plan and the driver table selected by the optimizer, the problem can be roughly reflected from the rows of the execution plan.

Find out the relationship between the tables and see if the associated fields have the appropriate index.

Use the straight_join keyword to force the selection of the driver table to validate the idea of optimization.

Split the complex SQL if conditions permit. Keep it as simple as possible.

Force index

Sometimes the optimizer may not choose the execution plan of * * because of inaccurate statistical information, and can artificially change the execution plan of mysql, for example:

Optimization of count

Sorted by efficiency, count (field)

At this point, the study of "what are the ways to optimize the execution of MySQL statements" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.