mysql--Optimizing nested queries and paging queries

Optimizing Nested Queries

nested queries (subqueries) You can use the SELECT statement to create a single-column query result, and then use the result as a filter in another query. Nested queries are simple to write and easy to understand. However, it can sometimes be replaced by a more efficient connection (join).

Now, if you want to find out customers who have never spent their time on the site, that is the customer information that is queried in the customer Customers table but not in the payment payment table.

Nested queries:

explain select * from customer where customer_id not in (select customer_id from payment);

Connection Overwrite:

explain select * from customer a left join payment b on a.customer_id = b.customer_id where b.customer_id is null;

Voice-over: Connection queries are more efficient because MySQL does not need to create temporary tables in memory to accomplish this logical two-step query, and not exists to indicate that MySQL optimized the left JOIN, Once it finds a row that matches the left join standard, it no longer searches.

Optimize paged Queries

In MySQL to do paging query, MySQL does not skip the offset line, but take Offset+n row, and then return to discard the previous offset line, return N rows, when offset is particularly large, the efficiency is very low. For example, "Limit 1000,20", when MySQL sorted out the first 1020 data only need 1001th to 1020th record, the first 1000 data will be discarded, query and sorting is very expensive. This shows that MySQL's paging process is not perfect, we need to do some optimizations on paged SQL, either control the total number of pages returned or the number of pages exceeding a certain threshold for SQL rewrite .

Voice-over: Control the total number of pages returned is not so reliable, after all, the amount of data per page can not be too large, after more data, control the total number of pages returned is not realistic. So the number of pages that exceed a specific threshold is still being overwritten with SQL.

Now suppose you want to sort the movie table film and fetch a page of data

explain select * from film order by title limit 50,5;

You can see that the optimizer actually does a full table scan, and the processing efficiency is not high.

The first way to optimize ideas

Completes the sort paging operation on the index, and finally associates the other column content that is required by the primary key back to the table query.

VoiceOver: Here are two important concepts for SQL optimization, index overrides, and back tables , which I've covered in detail in the previous article. Scan and sort through index overrides on the index (indexed), and finally, the primary key (InnoDB engine index through the primary key back table) back to the table query, to minimize the number of I/O back to the table query.

explain select * from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id = b.film_id;

The second optimization idea

Reduce the pressure of paging by converting the limit query into a query at a certain location.

Suppose now 10 data per page, to take the 42nd page of data.

explain select * from film order by title limit 410,10;

Now you need to pass one more parameter, which is the title of the last piece of data on the previous page (page 41st),

SQL can be rewritten as:

explain select * from film where title>'HOLES BRANNIGAN' order by title limit 10;

This converts the limit m,n to the limit n query, but this scenario is only suitable for a specific environment where duplicate values are not present, otherwise the paging result may lose data.

Summarize

The optimization of nested and paged queries is ultimately followed by one of the principles of SQL optimization-reducing the number of I/O times to query back tables. For paged query optimization, the first optimization scheme is recommended, with better performance and higher stability.

Reference

"In layman's MySQL"

Coderfocus

Public Number:

Declaration: This article for Bo Master Learning sentiment Summary, the level is limited, if improper, welcome correction. If you think it's a good idea, click on the button below, thanks for your support. Please specify the author and source of the reference.