如何做网站活动封面,建设公司官网的请示,六安人,旅游网站设计思路列表和翻页是所有应用系统里面必不可少的需求#xff0c;但是当深度翻页的时候#xff0c;越深越慢。下面是几种常用方式
准备工作
CREATE UNLOGGED TABLE data (id bigint GENERATED ALWAYS AS IDENTITY,value double precision NOT NULL,created timestamp with time zon…列表和翻页是所有应用系统里面必不可少的需求但是当深度翻页的时候越深越慢。下面是几种常用方式
准备工作
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 CURSORSWITH 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 10AND (created, id) (2022-01-01 01:27:3501, 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流下一个下一个。整体思想是一致的。
参考
