快捷導(dǎo)航

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

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

我們?cè)谑褂脭?shù)據(jù)庫(kù)進(jìn)行分頁(yè)查詢時(shí),隨著offset過(guò)濾的數(shù)據(jù)越來(lái)越多,查詢也會(huì)越來(lái)越慢,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL中offset...limit分頁(yè)優(yōu)化的一些常見(jiàn)手段,需要的朋友可以參考下

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

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

看個(gè)例子：

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

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

#如下SQL，創(chuàng)建一個(gè)符合索引最合適
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，結(jié)果很快
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

#但是當(dāng)我們OFFSET 200000的時(shí)候，執(zhí)行計(jì)劃就變了，代價(jià)比較高了，這就是我們前面所說(shuō)的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è)越多，性能越差，唯一的好處，就是書(shū)寫(xiě)簡(jiǎn)單。

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

由于普通游標(biāo)只能在單個(gè)事務(wù)的上下文中工作。因此，普通游標(biāo)對(duì)于分頁(yè)的作用有限，因?yàn)樵谑聞?wù)打開(kāi)時(shí)進(jìn)行用戶交互是一個(gè)非常差的體驗(yàn):長(zhǎng)事務(wù)不僅會(huì)使表鎖保持很長(zhǎng)時(shí)間(這可能阻塞DDL或TRUNCATE語(yǔ)句)，而且還會(huì)阻塞autovacuum的進(jìn)程，從而導(dǎo)致表膨脹。

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

游標(biāo)說(shuō)明可以參考：https://www.postgresql.org/docs/13/sql-declare.html

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

#移動(dòng)游標(biāo)并取出50行，和OFFSET 200000 LIMIT 50效果一樣
MOVE ABSOLUTE 200000 IN c;
FETCH 50 FROM c;

#使用完游標(biāo)后，必須記得關(guān)閉游標(biāo)
close c;

使用游標(biāo)的優(yōu)點(diǎn)和缺點(diǎn)：

優(yōu)點(diǎn)：

1.游標(biāo)適用于所有分頁(yè)查詢，不管是第一頁(yè)還是最后一頁(yè)，效率一樣

2.游標(biāo)的結(jié)果集是穩(wěn)定的

缺點(diǎn)：

1.當(dāng)完成操作時(shí)，一定不要忘記關(guān)閉游標(biāo)，否則結(jié)果集將保存在服務(wù)器上，直到數(shù)據(jù)庫(kù)會(huì)話結(jié)束

2.如果游標(biāo)長(zhǎng)時(shí)間打開(kāi)，數(shù)據(jù)將變的陳舊，無(wú)法獲取動(dòng)態(tài)的最新數(shù)據(jù)

3.游標(biāo)長(zhǎng)時(shí)間打開(kāi)，相當(dāng)于一個(gè)長(zhǎng)事物，長(zhǎng)事物的負(fù)面影響，相信大家有一定共識(shí)

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

位點(diǎn)的原理很簡(jiǎn)單，就是記錄上次查詢出來(lái)的結(jié)果作為一個(gè)位點(diǎn)，查詢的時(shí)候基于這個(gè)點(diǎn)的條件去查詢。這樣也就可以去掉offset了。注意要有一個(gè)pk,沒(méi)有的話，需要加一個(gè)類似字段，這樣位點(diǎn)才不會(huì)重復(fù)。

例子如下：

#通過(guò)查詢記住下一頁(yè)的起始位點(diǎn)，例子中id作為pk,標(biāo)識(shí)唯一
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
 
#我們必須記住從頁(yè)面的最后一行創(chuàng)建的id的值。然后我們可以取下一頁(yè)
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)

#加一個(gè)更適合的索引，執(zhí)行計(jì)劃會(huì)更好一些
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)

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

優(yōu)點(diǎn)：

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

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

缺點(diǎn)：

1.需要一個(gè)專門(mén)為查詢而設(shè)計(jì)的特殊索引

2.只有事先能獲取到確切的位點(diǎn)，查詢時(shí)才有用

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

總結(jié)

到此這篇關(guān)于PostgreSQL中offset...limit分頁(yè)優(yōu)化的一些常見(jiàn)手段的文章就介紹到這了,更多相關(guān)PostgreSQL offset...limit分頁(yè)優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！