mysql分页数据重复_小心避坑：MySQL分页时出现的数据重复问题

0 问题描述

在MySQL中我们通常会采用limit来进行翻页查询&＃xff0c;比如limit(0,10)表示列出第一页的10条数据&＃xff0c;limit(10,10)表示列出第二页。但是&＃xff0c;当limit遇到order by的时候&＃xff0c;可能会出现翻到第二页的时候&＃xff0c;竟然又出现了第一页的记录。

具体如下&＃xff1a;

SELECT

&＃96;post_title&＃96;,

&＃96;post_date&＃96;

FROM

post

WHERE

&＃96;post_status&＃96; &＃61;

&＃39;publish&＃39;

ORDER

BY

view_count

desc

LIMIT

5,

5

使用上述SQL查询的时候&＃xff0c;很有可能出现和LIMIT 0,5相同的某条记录。而如果使用如下方式&＃xff0c;则不会出现重复的情况&＃xff1a;

SELECT

*

FROM

post

WHERE

post_status &＃61;

&＃39;publish&＃39;

ORDER

BY

view_count

desc

LIMIT

5,

5

但是&＃xff0c;由于post表的字段很多&＃xff0c;仅仅希望用这两个字段&＃xff0c;不想把post_content也查出来。为了解决这个情况&＃xff0c;在ORDER BY后面使用了两个排序条件来解决这个问题&＃xff0c;如下&＃xff1a;

SELECT

&＃96;post_title&＃96;,

&＃96;post_date&＃96;

FROM

post

WHERE

&＃96;post_status&＃96; &＃61;

&＃39;publish&＃39;

ORDER

BY

view_count

desc,

ID

asc

LIMIT

5,

5

按理来说&＃xff0c;MySQL的排序默认情况下是以主键ID作为排序条件的&＃xff0c;也就是说&＃xff0c;如果在view_count相等的情况下&＃xff0c;主键ID作为默认的排序条件&＃xff0c;不需要我们多此一举加ID asc。

但是事实就是&＃xff0c;MySQL再order by和limit混用的时候&＃xff0c;出现了排序的混乱情况。

1 分析问题

在MySQL 5.6的版本上&＃xff0c;优化器在遇到order by limit语句的时候&＃xff0c;做了一个优化&＃xff0c;即 使用了priority queue。

使用 priority queue 的目的&＃xff0c;

就是在不能使用索引有序性的时候&＃xff0c;如果要排序&＃xff0c;并且使用了limit n&＃xff0c;那么只需要在排序的过程中&＃xff0c;保留n条记录即可&＃xff0c;这样虽然不能解决所有记录都需要排序的开销&＃xff0c;但是

只需要 sort buffer 少量的内存就可以完成排序。

之所以MySQL 5.6出现了第二页数据重复的问题&＃xff0c;

是因为 priority queue 使用了堆排序的排序方法&＃xff0c;而堆排序是一个不稳定的排序方法&＃xff0c;也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

MySQL 5.5 没有这个优化&＃xff0c;所以也就不会出现这个问题。

也就是说&＃xff0c;MySQL 5.5是不存在本文提到的问题的&＃xff0c;5.6版本之后才出现了这种情况。

再看下MySQL解释sql语言时的执行顺序&＃xff1a;

(1)

SELECT

(

2)

DISTINCT 

(

3)

FROM 

(

4)     

JOIN 

(

5)

ON 

(

6)

WHERE 

(

7)

GROUP

BY 

(

8)

HAVING 

(

9)

ORDER

BY 

(

10)

LIMIT 

执行顺序依次为 form… where… select… order by… limit…&＃xff0c;由于上述priority queue的原因&＃xff0c;在完成select之后&＃xff0c;所有记录是以堆排序的方法排列的&＃xff0c;在进行order by时&＃xff0c;仅把view_count值大的往前移动。

但由于limit的因素&＃xff0c;排序过程中只需要保留到5条记录即可&＃xff0c;view_count并不具备索引有序性&＃xff0c;所以当第二页数据要展示时&＃xff0c;mysql见到哪一条就拿哪一条&＃xff0c;因此&＃xff0c;当排序值相同的时候&＃xff0c;第一次排序是随意排的&＃xff0c;第二次再执行该sql的时候&＃xff0c;其结果应该和第一次结果一样。

......

查看全文请点击文章顶部链接