• notice
  • Congratulations on the launch of the Sought Tech site

Example of instances of page optimization in mysql

Usually, we use ORDER BY LIMIT start, offset to perform paging query.For example the following SQL:

Or like the following pagination SQL without any conditions:

Generally speaking, the time-consuming of paging SQL increases sharply with the increase of the start value.Let's look at the execution time of paging SQL with the following two different starting values:

It can be seen that with the increase of the number of pages, the time-consuming of SQL query also increases dozens of times, which is obviously unscientific.Today we will analyze how to optimize this paging scheme.In general, the ultimate solution to optimize paging is: no paging, hahaha~~~, don't say I'm talking nonsense, it is true, you can hand over the paging algorithm to third-party solutions such as Sphinx, Lucence, etc., there is no need to let it do it Things it's not good at.Of course, some friends said that it is too troublesome to use a third party, so we want to use it for this paging, what should we do? Don't worry, let us analyze it slowly, first look at the following table DDL, data volume, query SQL execution plan and other information:

It can be seen that although the primary key index is scanned, the number of records to be scanned by the second SQL is too large, and about 935510 records need to be scanned first, and then 10 records are taken according to the sorting result.is very slow.In view of this situation, our optimization idea is relatively clear, there are two points:

1.Obtain data directly from the index as much as possible to avoid or reduce the frequency of directly scanning row data
2.Reduce the number of scanned records as much as possible, that is, first determine the starting range, and then take N records later.

According to this, we have two corresponding rewriting methods: subquery, table join, that is, as follows:

#Optimize by sub-query.In the sub-query, first get the largest id from the index, then sort in reverse order, and then take 10 rows of the result set
#Note that 2 times of reverse order is used here, so when taking the start value of LIMIT, add 10 to the original value, that is, 935510, otherwise the result will be inconsistent with the original

Then let's compare the execution time of these two optimized new SQLs:

Let's look at a paging SQL comparison without filter conditions:

At this point, we have seen that the use of subqueries or INNER JOIN for optimization has greatly improved.This method is also suitable for smaller pagination.Although the start position at the beginning of LIMIT is much smaller, the SQL execution time is also It is much faster, but after adopting this method, the paging with WHERE condition can improve the query efficiency by 24.9% and 156.5% respectively, and the paging without the WHERE condition can improve the query efficiency by 554.5% and 11.7% respectively.You can test it yourself.verify.In terms of the improvement ratio alone, it is quite impressive.To ensure that these optimization methods can be applied to various paging modes, they can be used from the beginning.Let's take a look at the corresponding improvement ratio of various scenarios:

201558115710029.jpg (649×215)

Conclusion: This is obvious, especially for the case of large paging, so we prefer to use the INNER JOIN method to optimize the paging algorithm.

Restart the mysqld instance for each of the above tests, and add SQL_NO_CACHE to ensure that every time Read directly from data files or index files.If the data is warmed up, the query efficiency will be improved to a certain extent, but the above-mentioned corresponding efficiency improvement ratio is basically the same.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

buy lipitor 40mg online & lt;a href="https://lipiws.top/"& gt;lipitor online order& lt;/a& gt; order atorvastatin 20mg without preion

Aoyvyj

2024-03-07

Leave a Reply

+