概述

前面介绍了Oracle、mysql、sqlserver的分页实现方案了，所以今天主要介绍一下关于PG的分页实现。传统的精准分页(能获取到总页数并且任意跳转页码)在需要符合条件的记录总数目,不过pg在这个地方是一个大坑,pg的count查询性能不行(目测不如mysql,sqlserver,mongodb)。

pg官网wiki给了个count estimate(https://wiki.postgresql.org/wiki/Count_estimate)的解决方案,有兴趣的朋友也可以后面了解一下，下面先看一下PG的limit实现。

PG limit 语法

 select * from table limit  A  offset  B;

参数：

A就是需要多少行；

B就是查询的起点位置。

基于上面语法介绍PG的5种分页实现方案，如下：

1、方案1

select *
  from table
 where 自增列 in (select 自增列
                 from 表
                ORDER BY 自增列 desc limit (select count(*) - (页码 * 显示数)
                                           from table))
 ORDER BY 自增列 asc limit 显示数;

示例：

select * from read_sight_order_log where id in ( select id from read_sight_order_log ORDER BY id desc limit (select count(*) - (0*10) from read_sight_order_log)) ORDER BY id asc limit 10

2、方案2

select *
  from table
 where 自增列 not in
       (select 自增列 from table ORDER BY 自增列 asc limit 页码 * 显示数)
 ORDER BY 自增列 asc limit 显示数

示例：

select * from read_sight_order_log where id not in (select id from read_sight_order_log ORDER BY id asc limit 0 * 10) ORDER BY id asc limit 10

3、方案3

 select * from table order by 排序列 asc LIMIT 显示数 offset (页码-1) * 显示数

示例：

select * from read_sight_order_log order by id asc LIMIT 10 offset 0*10

4、方案4

select *
  from table
 where 自增列 BETWEEN 页码 * 显示数 and (1 + 页码) * 显示数

示例：

select * from read_sight_order_log where id BETWEEN 0 * 10 and (1+0 )*10

5、方案5

select *
  from (select *,row_number() over(order by 列 asc) as rownumber from table) t
 where t.rownumber > 页码*显示数
   and t.rownumber <= (1+页码)*显示数

示例：

select * from (select *,row_number() over(order by id asc) as idx from read_sight_order_log)t where t.idx>0 * 10 and t.idx<= (1+0)*10

6、实例演示

6.1、环境准备

$ psql -h 172.26.151.107 -p 5432 FSL-VIS postgres
\dn   --list schemas
set search_path to 'fslvis_schema'; 
\dt   --list tables
CREATE TABLE t(
	EMPNO INT PRIMARY KEY  NOT NULL,
	ENAME CHAR(10) NOT NULL,
	JOB CHAR(10) NOT NULL,
	MGR INT,
	HIREDATE date,
	SAL INT,
	COMM INT,
	DEPTNO INT
);
INSERT INTO t VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', NULL, '20');
INSERT INTO t VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO t VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO t VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', NULL, '20');
INSERT INTO t VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO t VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', NULL, '30');
INSERT INTO t VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', NULL, '10');
INSERT INTO t VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', NULL, '20');
INSERT INTO t VALUES ('7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000', NULL, '10');
INSERT INTO t VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO t VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', NULL, '20');
INSERT INTO t VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', NULL, '30');
INSERT INTO t VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', NULL, '20');
INSERT INTO t VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', NULL, '10');

6.2、limit分页实现

select *  from t
 where empno in (select empno  from t  ORDER BY empno desc limit (select count(*) - (1 * 5)  from t))
 ORDER BY empno desc limit 5;
--或者以下
select * from t order by empno desc LIMIT 5 offset 0 * 5;

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~