分页查询的重复数据问题

一、前言

相信在平常数据库的使用过程中，大家都有使用过分页查询语来获取数据.不知道有没有使用过类似下面（1）（2）的查询语句。

（1）select * from user limit 0, 4 ;

（2）select * from user limit 4, 4;

以上查询语句等价于调用DSF的分页查询方法时不传入排序参数。如果使用过，那可就要小心了，这里面可能会有一个隐藏的问题。

二、问题阐述

2.1分页造成数据缺失

假设现在有一张user表，有以下数据。

Id(主键)

User_name

Createtime

9

A

2017-04-26 09:36:20

8

B

2017-04-26 09:36:19

7

C

2017-04-26 09:36:18

6

D

2017-04-26 09:36:17

5

E

2017-04-26 09:36:17

4

F

2017-04-26 09:36:17

3

G

2017-04-26 09:36:16

2

H

2017-04-26 09:36:15

1

I

2017-04-26 09:36:14

仍然执行上面两条sql，假设先执行（1）再执行（2）。

我们期待数据库按id有序输出。在执行完（1）后，返回id值为1,2,3,4的数据，执行完（2）后返回id值为5，6，7，8的数据。

在mysql中，确实会有MyISAM 表按插入顺序作为默认的排序方式输出结果，InnoDB 表按id默认排序输出结果。但是，这种默认排序并不是一定会严格执行的。数据的增删改，以及数据的缓存，都可能会对mysql的结果输出顺序造成影响。所以，极有可能执行完（1）后返回1，2，3，4，执行完（2）后返回4，5，6，7的情况，造成部分数据重复查询，部分数据漏查的情况(ORACLE默认排序也会有同样的问题)。要解决这一问题，其实很简单，只需要对结果排序，不再使用mysql不靠谱的默认排序 ，就可以解决这一问题。

（3）select * from user  limit 0, 4 orderby id;

（4）select * from user  limit 4, 4order by id;

这样在执行语句（3）（4）时，一定会严格按照id的顺序返回正确的结果。

2.2排序列必须唯一

         在执行（3），（4）时，按id排序可以得到正确结果。可是，我们的查询语句，很多时候是如下形式:

（5）select * from user  limit 0, 4 orderby createtime;

（6）select * from user  limit 4, 4 order by createtime;

         按照createtime排序能解决这个问题吗？答案是不能。查看user表的数据可以发现，id值为4,5，6的数据其createtime是一样的。这就意味着，mysql对4,5,6进行排序时，其排序结果是不确定的。也就是说，执行语句（5）时，有可能返回的是1，2，3，4，有可能返回的是1,2,3,5。因为createtime一致，部分数据的排序位置并不固定，所以真正要解决重复数据问题，不仅要排序，排序的列还要严格唯一，如语句（7）（8），增加唯一的主键作为排序依据，可以解决这个问题。

（7）select * from user  limit 0, 4 orderby createtime,id;

（8）select * from user  limit 4, 4order by createtime,id;

以上查询语句可以消除相同的createtime值对排序结果的影响。

2.3插入和删除带来新问题

      上面的示例中，我们并未考虑数据插入或者删除的情况。如果在查询时，数据存在插入或者删除，语句（3）（4）、（7）（8）能得到正确的结果吗？答案依旧是——不能。同样，依旧使用user表进行举例。首先执行语句（3），返回结果1、2、3、4。假设此时，执行了删除语句，删除了id为1的数据。接下来,再执行语句（4），返回的结果会变为6,7,8,9，出现漏查数据5的情况。这是因为，删除id为1的数据后，id为5的数据排序提升一个位置，提升到第4位，故不再出现在语句（4）的结果集中。在查询过程中，插入数据同样会导致数据顺序发生变化，进而导致结果集出现重复或者遗漏的情况。如何解决这个问题呢，分页查询需要换成如下写法：

（9）select * from userwhere id>0 limit 0, 4 order by id;

（10）select * from userwhere id>4 limit 0, 4 order by id;

这种写法，分页的页码不再变化，一直为0，同时新增查询条件id，id比较值为上一次分页查询结果集中id的最大值。例如，在执行完语句（9）之后，返回结果为1,2,3,4。最大的id值为4，故语句（10）中增加id>4的查询条件。当执行完语句（10）后，返回结果为5,6,7,8.如果要查询第三页的数据，则查询语句写为：

（11）select * from userwhere id>8 limit 0, 4 order by id;

这种查询方式，每次都是查询的第一页，但是通过已查询数据的id最大值对已经查询出来的数据进行过滤。

(三)总结

     综上，分页查询时，采取哪种sql语句的形式，主要看对分页查询数据出现缺失或者重复时的容忍程度。

（1）如果完全不在意数据是否有缺失或者重复，无需进行任何排序。

（2）如果不考虑数据有删除、插入对查询结果集造成影响的情况，则采用排序的方法可以有效避免分页查询结果重复或者缺失的情况，最好采用唯一列进行排序。

（3）如果要避免有删除、插入对查询结果集造成影响，则需要利用上一次分页查询结果作为下一次查询的条件，不断对查询结果进行过滤，来确保得到正确的查询结果。