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

Full text link: https://www.modb.pro/db/23201

0 problem description in MySQL, we usually use limit for page flip queries, such as limit (0J10) to list 10 pieces of data on the first page, and limit (10J10) to list the second page. However, when limit encounters order by, there may be a record on the first page when he turns to the second page. The details are as follows: SELECT

`post_ title`

`post_ date`

FROM

Post

WHERE

`post_ status` = 'publish'

ORDER BY

View_count desc

LIMIT

5, 5

When using the above SQL query, it is very likely that some record will appear that is the same as LIMIT 0Pol 5. There is no repetition if you use the following methods: SELECT

*

FROM

Post

WHERE

Post_status = 'publish'

ORDER BY

View_count desc

LIMIT

5, 5

However, because there are so many fields in the post table, I only want to use these two fields, and I don't want to find out the post_content. To solve this situation, two sorting criteria are used after ORDER BY to solve the problem, as follows: SELECT

`post_ title`

`post_ date`

FROM

Post

WHERE

`post_ status` = 'publish'

ORDER BY

View_count desc

ID asc

LIMIT

5, 5

In theory, the sorting of MySQL takes the primary key ID as the sorting condition by default, that is, if the primary key ID is the default sorting condition when the view_count is equal, there is no need for us to add ID asc. But the truth is that when MySQL is mixed with order by and limit, there is confusion in sorting. 1 Analysis of the problem on the version of MySQL 5.6.When the optimizer encounters an order by limit statement, the optimizer makes an optimization, that is, using priority queue. The purpose of using priority queue is that when index ordering cannot be used, if you want to sort, and limit n is used, then you only need to keep n records in the sorting process. Although this can not solve the overhead of sorting all records, it only needs a small amount of memory in sort buffer to complete sorting. The reason why MySQL 5.6 has the problem of data duplication on the second page is that priority queue uses the sorting method of heap sorting, and heap sorting is an unstable sorting method, that is, the results sorted by the same value may not be consistent with the order of the data read. MySQL 5.5 does not have this optimization, so this problem will not occur. In other words, the problem mentioned in this article does not exist in MySQL 5.5, which did not occur until after version 5.6. Let's take a look at the order in which MySQL interprets sql: (1) SELECT

(2) DISTINCT

(3) FROM

(4) JOIN

(5) ON

(6) WHERE

(7) GROUP BY

(8) HAVING

(9) ORDER BY

(10) LIMIT

The order of execution is form. Where... Select... Order by... Limit... Because of the above priority queue, after completing the select, all records are sorted in a heap sort, and only those with large view_count values are moved forward when order by is performed. However, due to the factors of limit, only 5 records need to be retained in the sorting process, and view_count does not have index ordering, so when the data on the second page is to be displayed, mysql will take which one it sees. Therefore, when the sort value is the same, the first sort is arranged at will, and the second time the sql is executed, the result should be the same as the first result.

.

To view the full text, please click on the link at the top of the article

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.