Shulou(Shulou.com)06/02 Report--

MySql paging query slow how to solve, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Limit paging principle

When we turn to the last few pages, the sql for the query is usually: select * from table where column=xxx order by xxx limit 1000000pm 20.

The query is very slow. But when we look at the first few pages, the speed is not slow. This is because the offset of limit is too large.

The principle of MySql when using limit is (use the example above):

MySql will query out 1000020 records.

Then drop the previous 1000000 records.

Return the remaining 20 records.

The above process is confirmed in the book "High performance MySql".

Solution

The solution is to use index override scanning as much as possible, that is, we check out index columns after select instead of

All columns, and the column of this index is preferably id. Then do another associative query to return all the columns.

The above sql can be written as follows:

SELECT * FROM

Table t

INNER JOIN (

SELECT

Id

FROM

Table

WHERE

Xxx_id = 143381

LIMIT 800000 ON t.id 20) T1 ON t.id = t1.id

The real experiment we did in mysql:

Image1

The figure above shows an unoptimized sql with an execution time of more than 2 seconds. After optimization, it is as follows:

Image2

The execution time is 0.3s, and the performance has been greatly improved. Although it has been optimized, the performance will decline as the offset increases, although the MySql official also gives

Other solutions, but it is difficult to use in actual development.

Some students may ask whether it is possible to use IN to nest subqueries instead of INNER JOIN, and the answer is no, because MySql cannot use LIMIT in subqueries.

So much for MySql paging optimization.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.