postgresql之翻页优化

列表和翻页是所有应用系统里面必不可少的需求，但是当深度翻页的时候，越深越慢。下面是几种常用方式

准备工作

CREATE UNLOGGED TABLE data (
   id bigint GENERATED ALWAYS AS IDENTITY,
   value double precision NOT NULL,
   created timestamp with time zone NOT NULL
);

/* 设置随机数种子 */
SELECT setseed(0.2740184);
/* 初始化数据 */
INSERT INTO data (value, created)
SELECT random() * 1000, d
FROM generate_series(
        TIMESTAMP '2022-01-01 00:00:00 UTC',
        TIMESTAMP '2022-12-31 00:00:00 UTC',
        INTERVAL '1 second'
     ) AS d(d);

/* 添加主键 */
ALTER TABLE data ADD PRIMARY KEY (id);

/* 回收空间，并对表数据进行统计分析 */
VACUUM (ANALYZE) data;

我们的查询目标是下面的SQL

SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created;

为了加速查询我们可以创建对应索引

CREATE INDEX data_created_value_idx ON data
   (created, value);

简单分页 LIMIT ? OFFSET ?

这是第一种方式

-- 首页
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created
LIMIT 50;
-- 第 深n 页
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created
OFFSET 180000 LIMIT 50;

不论是mysql 还是pg数据库直接使用offset limit 这种查询的时候都是扫描处理前 n * pageSIze 数据然后丢弃前面（n-1） * pageSize页的数据，数据库是否使用索引是有优化策略的，当经过一系列复杂的预估之后，假如数据库优化器判定走索引还没全表扫描效率高的时候就会放弃走索引，这个时候我们的查询就会比较慢，基本上都是秒级别的了
上面两个sql的执行计划如下
在这里插入图片描述

全表扫描最大的问题不仅仅是某个查询慢，更严重的是会导致锁表

缺点

页码越深，性能越差，但是假如用户只关心前面几页的数据，是没有什么问题的
并发情况下翻页会有跳数据或者重复数据的问题，比如用户A是在查看数据翻页的，用户B在第一页加了一条数据，这个时候用户A翻页，那么原来第一页最后的数据就会被挤到第二页，类似这种情况，但是一般的时候非高并发情况可以不考虑

优点

简单，不管怎么翻都一招鲜吃遍天

使用游标 WITH HOLD CURSORS

WITH HOLD CURSORS是一种特殊的游标，与普通游标主要区别如下

普通游标：普通游标（或称为会话级游标）的生命周期通常与创建它的事务相同。这意味着，当事务提交或回滚时，游标也会被关闭，其相关的资源会被释放。
WITH HOLD CURSORS：WITH HOLD游标在事务结束后仍然保持打开状态，即使原始事务已经提交或回滚。这使得游标可以在多个事务之间保持活动状态，直到显式关闭或会话结束
但是在分页情况下我们一般一页是一个请求，这个时候肯定是不同的事务，所以这个时候普通游标是无法满足我们的要求的
我们使用WITH HOLD CURSORS

DECLARE c SCROLL CURSOR WITH HOLD FOR
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created;

在这里插入图片描述
获取任意数据

-- 移动游标
MOVE ABSOLUTE 4950 IN c;
-- 获取数据
FETCH 50 FROM c;

注意继续FETCH会继续后翻页
在这里插入图片描述
还需要注意的是这个游标用完可一定要关闭、关闭；关闭，重要的事情说三遍

-- 关闭游标
CLOSE c;

优点

适用于所有查询，不管是第一页还是最后一页，效率一样
结果集是稳定的，没有像OFFSET和LIMIT那样跳过或重复结果
可以跳页，从第5页，直接跳到第100页

缺点

当完成操作时，一定不要忘记关闭游标，否则结果集将保存在服务器上，直到数据库会话结束
如果游标长时间打开，数据将变的陈旧，无法获取动态的最新数据
游标长时间打开，相当于一个长事务

KEYSET PAGINATION

暂时翻译为位点，原理上就是记录上一次数据最后一条内容，所以，这个值必须是唯一的强有序的，这样翻页的时候才不会重复或者跳过数据

-- 首页查询
SELECT id, value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created, id
LIMIT 50;

-- 基于上面位点的下一页
SELECT id, value, created
FROM data
WHERE value BETWEEN 0 AND 10
  AND (created, id) > ('2022-01-01 01:27:35+01', 5256)
ORDER BY created, id
LIMIT 50;

在这里插入图片描述
位点查询

如果使用这种方式的话我们添加这个索引效率会更好

CREATE INDEX data_keyset_idx ON data
   (created, id, value);

优点

每次查询只获取我们需要的数据，不需要扫描不额外的数据，减少了相关资源代价
每个查询将展示最新并发数据修改的当前数据

缺点：

需要一个专门为查询而设计的特殊索引
需要对业务进行改造只有事先能获取到确切的位点，查询时才有用，并且当我们修改排序字段或者条件的时候，这个位点可能跟之前的就不一致了。
前后端都需要改造

其他

一般情况下我们可能也需要count这个值，上面三种方式中WITH HOLD CURSORS是天然支持的

MOVE ALL IN c;

在这里插入图片描述
剩下两种都是扫描全表，使用昂贵的资源，简单点跟产品battle 不支持，tips：对于ES深度翻页，直接limit比PG,MySQL资源更严重，因为ES会在每个分区上进行计算到协作节点进行统一聚合，所以一般的ES可能会关闭这种深度分页或者使用游标，类似Feed流，下一个下一个。整体思想是一致的。