PostgreSQL中offset...limit分頁優(yōu)化的一些常見手段

更新時間：2023年05月18日 15:45:50 作者：魂醉

我們在使用數(shù)據(jù)庫進行分頁查詢時,隨著offset過濾的數(shù)據(jù)越來越多,查詢也會越來越慢,下面這篇文章主要給大家介紹了關于PostgreSQL中offset...limit分頁優(yōu)化的一些常見手段,需要的朋友可以參考下

發(fā)現(xiàn)問題

大部分開發(fā)人員習慣使用order by offset limit進行分頁，使用該方法可能會導致掃描的數(shù)據(jù)放大，因為offset的行會被掃描。表現(xiàn)就是一般offset的行比較小的情況也，也就是翻頁，是很快的，但是一旦offset的值很大，翻頁的數(shù)量很大，那么一定會變慢。

看個例子：

#建表，插入數(shù)據(jù)，并分析收取統(tǒng)計信息
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;

下面我們翻頁獲取數(shù)據(jù)看看性能如何：

#如下SQL，創(chuàng)建一個符合索引最合適
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created;

#創(chuàng)建索引
CREATE INDEX data_created_value_idx ON data
   (created, value);


#可以看到是Index Only Scan，結果很快
postgres=# explain analyze SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created
LIMIT 50;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..126.51 rows=50 width=16) (actual time=0.022..0.195 rows=50 loops=1)
   ->  Index Only Scan using data_created_value_idx on data  (cost=0.56..801382.02 rows=318146 width=16) (actual time=0.021..0.190 rows=50 loops=1)
         Index Cond: ((value >= '0'::double precision) AND (value <= '10'::double precision))
         Heap Fetches: 0
 Planning Time: 0.084 ms
 Execution Time: 0.210 ms

#但是當我們OFFSET 200000的時候，執(zhí)行計劃就變了，代價比較高了，這就是我們前面所說的offset都要掃描
postgres=# explain analyze SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created
OFFSET 200000 LIMIT 50;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=434757.47..434763.31 rows=50 width=16) (actual time=2697.793..2704.289 rows=50 loops=1)
   ->  Gather Merge  (cost=411422.51..442355.57 rows=265122 width=16) (actual time=2627.028..2695.579 rows=200050 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=410422.49..410753.89 rows=132561 width=16) (actual time=2607.976..2613.950 rows=67369 loops=3)
               Sort Key: created
               Sort Method: external merge  Disk: 2760kB
               Worker 0:  Sort Method: external merge  Disk: 2640kB
               Worker 1:  Sort Method: external merge  Disk: 2640kB
               ->  Parallel Seq Scan on data  (cost=0.00..396876.00 rows=132561 width=16) (actual time=0.042..2551.663 rows=104958 loops=3)
                     Filter: ((value >= '0'::double precision) AND (value <= '10'::double precision))
                     Rows Removed by Filter: 10378242
 Planning Time: 0.102 ms
 Execution Time: 2704.851 ms
(14 rows)

如上我們看到的，翻頁越多，性能越差，唯一的好處，就是書寫簡單。

優(yōu)化手段1：使用游標

由于普通游標只能在單個事務的上下文中工作。因此，普通游標對于分頁的作用有限，因為在事務打開時進行用戶交互是一個非常差的體驗:長事務不僅會使表鎖保持很長時間(這可能阻塞DDL或TRUNCATE語句)，而且還會阻塞autovacuum的進程，從而導致表膨脹。

WITH HOLD | WITHOUT HOLD 默認值為WITHOUT HOLD, 使用WITH HOLD將CURSOR的使用范圍擴大到SESSION級別，WITHOUT HOLD是TRANSACTION級別，另外，WITH HOLD將消耗更多的資源（內存或臨時文件）來保持數(shù)據(jù)。

游標說明可以參考：https://www.postgresql.org/docs/13/sql-declare.html

#創(chuàng)建一個游標，取出滿足的條件的結果集
begin;
DECLARE c SCROLL CURSOR WITH HOLD FOR
SELECT value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created;
COMMIT;

#移動游標并取出50行，和OFFSET 200000 LIMIT 50效果一樣
MOVE ABSOLUTE 200000 IN c;
FETCH 50 FROM c;

#使用完游標后，必須記得關閉游標
close c;

使用游標的優(yōu)點和缺點：

優(yōu)點：

1.游標適用于所有分頁查詢，不管是第一頁還是最后一頁，效率一樣

2.游標的結果集是穩(wěn)定的

缺點：

1.當完成操作時，一定不要忘記關閉游標，否則結果集將保存在服務器上，直到數(shù)據(jù)庫會話結束

2.如果游標長時間打開，數(shù)據(jù)將變的陳舊，無法獲取動態(tài)的最新數(shù)據(jù)

3.游標長時間打開，相當于一個長事物，長事物的負面影響，相信大家有一定共識

優(yōu)化手段2：使用位點

位點的原理很簡單，就是記錄上次查詢出來的結果作為一個位點，查詢的時候基于這個點的條件去查詢。這樣也就可以去掉offset了。注意要有一個pk,沒有的話，需要加一個類似字段，這樣位點才不會重復。

例子如下：

#通過查詢記住下一頁的起始位點，例子中id作為pk,標識唯一
SELECT id, value, created
FROM data
WHERE value BETWEEN 0 AND 10
ORDER BY created, id
LIMIT 50;
  id  |        value        |        created         
------+---------------------+------------------------
.........略.........
 4568 |   7.771510504657186 | 2022-01-01 01:16:07+08
 4586 |  1.2500308700502671 | 2022-01-01 01:16:25+08
 4607 |  3.3096537558421346 | 2022-01-01 01:16:46+08
 
#我們必須記住從頁面的最后一行創(chuàng)建的id的值。然后我們可以取下一頁
SELECT id, value, created
FROM data
WHERE value BETWEEN 0 AND 10
  AND (created, id) > ('2022-01-01 01:16:46+08', 4607)
ORDER BY created, id
LIMIT 50;

postgres=# explain SELECT id, value, created
FROM data
WHERE value BETWEEN 0 AND 10
AND (created, id) > ('2022-01-01 01:16:46+08', 4607)
ORDER BY created, id
LIMIT 50;
                                                                               QUERY PLAN                                                                     
          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
----------
 Limit  (cost=4.32..194.08 rows=50 width=24)
   ->  Incremental Sort  (cost=4.32..1207236.72 rows=318103 width=24)
         Sort Key: created, id
         Presorted Key: created
         ->  Index Scan using data_created_value_idx on data  (cost=0.56..1192922.08 rows=318103 width=24)
               Index Cond: ((created >= '2022-01-01 01:16:46+08'::timestamp with time zone) AND (value >= '0'::double precision) AND (value <= '10'::double pr
ecision))
               Filter: (ROW(created, id) > ROW('2022-01-01 01:16:46+08'::timestamp with time zone, 4607))
(7 rows)

#加一個更適合的索引，執(zhí)行計劃會更好一些
CREATE INDEX data_keyset_idx ON data (created, id, value);
postgres=#   explain SELECT id, value, created
FROM data
WHERE value BETWEEN 0 AND 10
  AND (created, id) > ('2022-01-01 01:16:46+08', 4607)
ORDER BY created, id
LIMIT 50;
--------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
 Limit  (cost=0.56..160.58 rows=50 width=24)
   ->  Index Only Scan using data_keyset_idx on data  (cost=0.56..1018064.43 rows=318103 width=24)
         Index Cond: ((ROW(created, id) > ROW('2022-01-01 01:16:46+08'::timestamp with time zone, 4607)) AND (value >= '0'::double precision) AND (value <= '1
0'::double precision))
(3 rows)

位點優(yōu)化的有點和缺點：

優(yōu)點：

1.每次查詢只獲取我們需要的數(shù)據(jù)，不需要掃描不額外的數(shù)據(jù)，減少了相關資源代價

2.每個查詢將展示最新并發(fā)數(shù)據(jù)修改的當前數(shù)據(jù)

缺點：

1.需要一個專門為查詢而設計的特殊索引

2.只有事先能獲取到確切的位點，查詢時才有用

參考：
https://www.cybertec-postgresql.com/en/pagination-problem-total-result-count/#total-count