如何优雅地实现分页查询

分页功能是很常见的功能,特别是当数据量越来越大的时候,分页查询是必不可少的。实现分页功能有很多种方式,如果使用的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列是主键,所以具有唯一性,故可以使用这种方式分页。如果用来排序的列不具有唯一性,比如说是时间戳,那么这种分页方式也可能出现重复,大家可以想想是为什么。

今天就分享这么多了,如果觉得对你有用,可以点击底部赞赏,不赞赏也没关系,点个赞随手转发也是对我的一种支持。

原文发布于微信公众号 - Java架构沉思录(code-thinker)

原文发表时间:2018-06-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏社区的朋友们

MongoDb 简单介绍

最近一段时间使用 mongodb 做媒资数据的接入,简单介绍一下 mongodb 的特性和语法。希望对大家有所帮助。

1450
来自专栏Java技术分享

高并发分布式系统中生成全局唯一Id汇总

数据在分片时,典型的是分库分表,就有一个全局ID生成的问题。 单纯的生成全局ID并不是什么难题,但是生成的ID通常要满足分片的一些要求:    1 不能有单...

2765
来自专栏Linux驱动

46.Linux-创建rc红外遥控平台设备,实现重复功能(2)

在上章分析了红外platform_driver后,已经修改bug后,接下来我们自己创建一个红外platform_device平台设备,其实写一个平台设备很简单.

1402
来自专栏Java技术分享

高并发分布式系统中生成全局唯一Id汇总

数据在分片时,典型的是分库分表,就有一个全局ID生成的问题。 单纯的生成全局ID并不是什么难题,但是生成的ID通常要满足分片的一些要求:    1 不能有单点故...

3065
来自专栏我的小碗汤

听说你还没掌握Normalizer的使用方法?

在 Elasticsearch 中处理字符串类型的数据时,如果我们想把整个字符串作为一个完整的 term 存储,我们通常会将其类型 type 设定为 keywo...

1404
来自专栏你不就像风一样

Hibernate各种主键生成策略与配置详解

主键由外部程序负责生成,在 save() 之前必须指定一个。Hibernate不负责维护主键生成。与Hibernate和底层数据库都无关,可以跨数据库。在存储对...

1032
来自专栏喵了个咪的博客空间

phalcon-入门篇7(Model层基础使用)

#phalcon-入门篇7(Model层基础使用)# ? 本教程基于phalcon2.0.9版本 ##前言## 先在这里感谢各位phalcon技术爱好者,我们提...

3939
来自专栏IT可乐

mybatis 详解(八)------ 懒加载

  本章我们讲如何通过懒加载来提高mybatis的查询效率。   本章所有代码:http://pan.baidu.com/s/1o8p2Drs 密码:trd6 ...

3409
来自专栏有趣的Python

5- Flask构建弹幕微电影网站-项目分析、搭建目录及模型设计项目优化与模型设计

已上线演示地址: http://movie.mtianyan.cn 项目源码地址:https://github.com/mtianyan/movie_proj...

4695
来自专栏蓝天

一个简单的支持MySQL和SQLite3的DB接口

simple_db.zip 相关联代码:https://github.com/eyjian/mooon/tree/master/common_library/...

1022

扫码关注云+社区