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采用了特殊的分页方式：Openstack分页以limit和marker两个字段进行控制，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多条下降到了几十条，效率大大提升。