PostgreSQL、MySQL高效分页方法探讨

服务器

浏览数:21

2020-6-28

AD:资源代下载服务

对于数据库相关的业务,逃不过的数据分页场景,无论是前台分页浏览还是划到页面底部自动加载。对于分页需求,各数据库也提供了成熟的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采用了特殊的分页方式:Openstack分页以limitmarker两个字段进行控制,limit控制每页显示数量,marker标识数据起始位置,即本分页第一条数据的ID
以nova list的API为例,官方对两个字段的说明如下:
Nova List API

Limit字段说明

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.

Marker字段说明

The ID of the last-seen item. 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也存在一个限制,就是分页时,只能浏览上一页/下一页,而不能跳页。

不过借鉴这个思路,我们可以对某些场景的分页查询进行优化。
对于按需自动加载(划到页面底部自动加载更多内容)或者只提供上一页/下一页浏览模式的场景,可以进行如下优化:

  1. 每次查询数据时,我们记录最后一条数据的ID或最后更新时间(这个主要根据order by字段来确定)
  2. 加载下一页数据时,把本页的最后一条数据ID作为过滤条件。
  3. 加载上一页数据时,则把本页第一条数据ID作为过滤条件。

查询下一页

SELECT * FROM orders WHERE order_id > page_last_id ORDER BY order_id
LIMIT page_sieze OFFSET 0;

查询上一页

SELECT * FROM orders WHERE order_id < page_first_id ORDER BY order_id
LIMIT page_sieze OFFSET 0;

小技巧:每次查询数据时,多返回一条数据,即返回page_size + 1条数据,但显示时去掉最后一条数据,通过这多出来一条数据,我们可以用来判断数据是否还有下一页

另外对于可以跳转到任意页面的场景,也可以进行优化,这种可跳转场景,分页显示也是有限的,一般模式是第一页/上一页/当前页前后10页……/下一页/最后一页,也就是说,分页时,数据是在一定范围内(前后10页)移动,可以以当前页数据为基础,对数据进行过滤,减少数据扫描范围。
考虑orders表有10W条记录,每页显示10条,当前页码为1000时的场景,如果按照单独limit和offset模式,offset=1W,也就是数据库要扫码1W条记录。假如现在翻页要从1000页跳转到1005页,我们以第1000页最后一条数据ID为过滤条件,offset跳过1001-1004的40条数据即可。

查询1005页

SELECT * FROM orders WHERE order_id > page_1000_last_id ORDER BY order_id
LIMIT page_sieze OFFSET page_size * 4;

这种方法相比基础的分页方式,只要order by字段是主键或索引字段,数据扫描的行数从1W多条下降到了几十条,效率大大提升。

作者:乘着风