分页功能是很常见的功能, 特别是当数据量越来越大的时候, 分页查询是必不可少的. 实现分页功能有很多种方式, 如果使用的 ORM 框架是 mybatis 的话, 有开源的分页插件可以使用, 如: Mybatis-PageHelper. 如果不使用分页插件, 那么就需要手动分页了, 由于不同的数据库实现分页的 SQL 语句并不一致, 如 Mysql 使用的是 limit 关键字, 而 Oracle 使用的是 rownum, 所以本文本文讲解的分页方案只适用于 Mysql 数据库.
基于 limit 的分页方案
首先讲讲分页操作必须满足的几个要求: 一个是有序性, 一个是不重复. 有序性可以看成是不重复的前提条件, 因为假如数据是无序的, 那么就不能保证多个分页之间是不重复的. 因此分页操作往往需要先对数据进行排序, 然后再加上分页条件. 我们讲的第一种方案是基于 limit 的分页方案, 也是很多分页插件使用的分页方案. 我们先来看看我们的测试数据.
先看一下表结构:
- mysql> desc user;
- +-------+------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +-------+------------+------+-----+---------+-------+
- | id | bigint(20) | NO | PRI | NULL | |
- | name | char(50) | NO | | NULL | |
- +-------+------------+------+-----+---------+-------+
- 2 rows in set
复制代码
可以看到我们的 user 表只有 2 列, 分别是 bigint 型的 id 和 char 型的 name.
接下来看下表数据:
- mysql> select count(*) from user;
- +----------+
- | count(*) |
- +----------+
- | 45116 |
- +----------+
- 1 row in set
- mysql> select * from user order by id asc limit 10;
- +----+--------+
- | id | name |
- +----+--------+
- | 0 | user_0 |
- | 1 | user_1 |
- | 2 | user_2 |
- | 3 | user_3 |
- | 4 | user_4 |
- | 5 | user_5 |
- | 6 | user_6 |
- | 7 | user_7 |
- | 8 | user_8 |
- | 9 | user_9 |
- +----+--------+
- 10 rows in set
复制代码
可以看到数据总行数大概 45000 条.
基于 limit 实现分页是比较简单的:
- mysql> select * from user order by id asc limit 10000,10;
- +-------+------------+
- | id | name |
- +-------+------------+
- | 10000 | user_10000 |
- | 10001 | user_10001 |
- | 10002 | user_10002 |
- | 10003 | user_10003 |
- | 10004 | user_10004 |
- | 10005 | user_10005 |
- | 10006 | user_10006 |
- | 10007 | user_10007 |
- | 10008 | user_10008 |
- | 10009 | user_10009 |
- +-------+------------+
- 10 rows in set
复制代码
其中, limit 后面的第一个参数表示下标, 也就是从第 10000 行记录开始取, 第二个参数表示总共取 10 行记录.
使用 limit 实现分页功能使用起来非常简单, 但是有没有什么问题呢?
我们先来回顾一下前面说的分页需要满足的 2 个要素: 有序性和不重复. 上述的语句我们已经使用了 order by 进行排序, 所以是可以满足有序性的, 但满足了不重复了吗? 假设在查询当前页跟下一页之间插入了一条记录, 且该数据的 id 小于当前页记录中最大的 id, 会怎么样呢? 我们测试一下就知道了:
- mysql> select * from user order by id asc limit 10000,10;
- +-------+------------+
- | id | name |
- +-------+------------+
- | 10000 | user_10000 |
- | 10001 | user_10001 |
- | 10002 | user_10002 |
- | 10003 | user_10003 |
- | 10004 | user_10004 |
- | 10005 | user_10005 |
- | 10006 | user_10006 |
- | 10007 | user_10007 |
- | 10008 | user_10008 |
- | 10009 | user_10009 |
- +-------+------------+
- 10 rows in set
- mysql> insert into user(id,name) values(-1,'user_-1');
- Query OK, 1 row affected
- mysql> select * from user order by id asc limit 10010,10;
- +-------+------------+
- | id | name |
- +-------+------------+
- | 10009 | user_10009 |
- | 10010 | user_10010 |
- | 10011 | user_10011 |
- | 10012 | user_10012 |
- | 10013 | user_10013 |
- | 10014 | user_10014 |
- | 10015 | user_10015 |
- | 10016 | user_10016 |
- | 10017 | user_10017 |
- | 10018 | user_10018 |
- +-------+------------+
- 10 rows in set
复制代码
可以看到, 当我们在相邻的 2 页查询之间插入一条记录的时候, 后面一页跟前面一页有记录重复了 (id 为 10009 的记录在相邻 2 页中都出现了). 原因在于插入一条记录之后, 分页结构已经改变了, 所以才会出现重复数据.
因此, 使用 limit 进行分页似乎不是很优雅啊, 接下来将介绍另外一种分页的写法.
基于 limit 与比较的分页方案
另外一种分页的写法可以这样考虑, 比如我们要取的是从第 10000 行开始的 10 行记录, 那么我们可以先把大于或等于 10000 行的数据查出来并排序, 然后再取出前 10 行记录, 这样也可以完成分页. 接下来看具体的 SQL 语句:
- mysql> select * from user where id>=10000 order by id asc limit 10;
- +-------+------------+
- | id | name |
- +-------+------------+
- | 10000 | user_10000 |
- | 10001 | user_10001 |
- | 10002 | user_10002 |
- | 10003 | user_10003 |
- | 10004 | user_10004 |
- | 10005 | user_10005 |
- | 10006 | user_10006 |
- | 10007 | user_10007 |
- | 10008 | user_10008 |
- | 10009 | user_10009 |
- +-------+------------+
- 10 rows in set
复制代码
那么这种写法可以防止上面出现的问题吗? 我们做个试验就知道了.
- mysql> select * from user where id>=10000 order by id asc limit 10;
- +-------+------------+
- | id | name |
- +-------+------------+
- | 10000 | user_10000 |
- | 10001 | user_10001 |
- | 10002 | user_10002 |
- | 10003 | user_10003 |
- | 10004 | user_10004 |
- | 10005 | user_10005 |
- | 10006 | user_10006 |
- | 10007 | user_10007 |
- | 10008 | user_10008 |
- | 10009 | user_10009 |
- +-------+------------+
- 10 rows in set
- mysql> insert into user(id,name) values(-4,'user_-4');
- Query OK, 1 row affected
- mysql> select * from user where id>=10010
- order by id asc limit 10;
- +-------+------------+
- | id | name |
- +-------+------------+
- | 10010 | user_10010 |
- | 10011 | user_10011 |
- | 10012 | user_10012 |
- | 10013 | user_10013 |
- | 10014 | user_10014 |
- | 10015 | user_10015 |
- | 10016 | user_10016 |
- | 10017 | user_10017 |
- | 10018 | user_10018 |
- | 10019 | user_10019 |
- +-------+------------+
- 10 rows in set
复制代码
可以看到, 当在相邻的两页查询之间插入数据时, 分页查询结果不会出现重复. 其实也很好理解, 因为虽然插入记录后, 分页的结构变了, 但是由于我们现在的分页查询是从固定的 id 开始查的, 所以插入新的数据对后面的分页结果没有影响.
当然, 这种分页查询也是有限制的. 其只适用于用来排序的列具有唯一性的情况, 在上述例子中, id 列是主键, 所以具有唯一性, 故可以使用这种方式分页. 如果用来排序的列不具有唯一性, 比如说是时间戳, 那么这种分页方式也可能出现重复, 大家可以想想是为什么.
来源: https://juejin.im/post/5b821874e51d4538da22ea9e