PostgreSQL、MySQL高效分页方法

对于数据库相关的业务,逃不过的数据分页场景,无论是前台分页浏览还是 页面底部自动加载 。对于分页需求,各数据库也提供了成熟的SQL支持,类似于Hibernate等ORM框架也集成了相关的方法。但是基于数据库(框架)提供的分页方法,我们能否结合业务,提供更高效更优化的分页方法呢?

基础分页技术

数据查询语句中,和分页相关的有两个参数,分别是:

  • Limit:查询数据条数
  • OFFSET:查询结果数据起始位置偏移量(跳过的行数)

MySQL中的语法:

SELECT fields_list FROM table_name  [ ORDER BY ... ]
[LIMIT offset, limit];

但这个语法不兼容PostgreSQL,兼容MySQL和PostgreSQL的语法为:

SELECT fields_list FROM table_name [ ORDER BY ... ]
[ LIMIT {number | ALL} ] [ OFFSET number];

注:LIMIT和OFFSET都是可选字段。

下文以第二种兼容的语法来进行描述。

订单表表名:orders,每页显示条数:10 page_sieze

查询第一页:

SELECT * FROM orders ORDER BY order_id
LIMIT 10 OFFSET 0;

查询第二页:

SELECT * FROM orders ORDER BY order_id
LIMIT 10 OFFSET 10;

查询第n页:

SELECT * FROM orders ORDER BY order_id
LIMIT page_sieze OFFSET page_sieze * n;

分页优化

如果熟悉Openstack API的开发人员会注意到,Openstack原生API采用了特殊的分页方式:以nova list的API为例,如果request中包含 limit 字段,则API认为要以分页模式查询结果,官方API对limit字段的说明如下:

Nova List API

Requests a page size of items. Returns a number of items up to a limit value. Use the limit parameter to make an initial limited request and use the ID of the last-seen item from the response as the marker parameter value in a subsequent limited request.

以上描述中的关键信息是:如果分页查询,返回的JSON格式中会包含marker字段,指示下一页数据第一条数据的ID。另外这个API也存在一个限制,就是分页时,只能浏览 上一页/下一页 ,而不能跳页。

分页中遇到的坑

我来评几句
登录后评论

已发表评论数()

相关站点

热门文章