PostgreSQL 分頁, offset, 傳回順序, 掃描方法原理(seqscan, index scan, index only scan, bitmap scan, parallel xx scan)，遊标

背景

一個這樣的問題：

為什麼

select x from tbl offset x limit x;

兩次查詢連續的OFFSET，會有重複資料呢？

select ctid,* from tbl where ... offset 0 limit 10;  
select ctid,* from tbl where ... offset 10 limit 10;             

為什麼多數時候offset會推薦用order by？

不使用ORDER BY的話，傳回順序到底和什麼有關？

答案是：

資料庫的掃描方法。

資料庫掃描方法，具體的原理可以到如下文檔中找到PDF，PDF内有詳細的掃描方法圖文介紹。

《阿裡雲 PostgreSQL 産品生态；案例、開發管理實踐、原理、學習資料、視訊；PG天天象上沙龍記錄 - 珍藏級》

掃描方法

1、全表掃描, seqscan

從第一個資料塊開始掃描，傳回複合條件的記錄。

2、并發全表掃描, concurrently seqscan

如果有多個會話，對同一張表進行全表掃描時，後發起的會話會與前面正在掃描的會話進行BLOCK對齊步調，也就是說，後面發起的會話，可能是從表的中間開始掃的，掃描到末尾再轉回去，避免多會話同時對一個表全表掃描時的IO浪費。

例如會話1已經掃到了第99個資料塊，會話2剛發起這個表的全表掃描，則會從第99個資料塊開始掃描，掃完在到第一個資料塊掃，一直掃到第98個資料塊。

3、索引掃描, index scan

按索引順序掃描，并回表。

4、索引ONLY掃描, index only scan

按索引順序掃描，根據VM檔案的BIT位判斷是否需要回表掃描。

5、位圖掃描, bitmap scan

按索引取得的BLOCKID排序，然後根據BLOCKID順序回表掃描，然後再根據條件過濾掉不符合條件的記錄。

這種掃描方法，主要解決了離散資料(索引字段的邏輯順序與記錄的實際存儲順序非常離散的情況)，需要大量離散回表掃描的情況。

6、并行掃描, parallel xx scan

并行的全表、索引、索引ONLY、位圖掃。首先會FORK出若幹個WORKER，每個WORKER負責一部分資料塊，一起掃描，WORKER的結果（FILTER後的）發給下一個GATER WORKER節點。

7、hash join

哈希JOIN，

8、nest loop join

嵌套循環

9、merge join

合并JOIN（排序JOIN）。

更多掃描方法，請參考PG代碼。

掃描方法決定了資料傳回順序

根據上面的這些掃描方法，我們可以知道一條QUERY下去，資料的傳回順序是怎麼樣的。

select * from tbl where xxx offset 10 limit 100;             

1、如果是全表掃描，那麼傳回順序就是資料的實體存放順序，然後偏移10條有效記錄，取下100條有效記錄。

2、如果是索引掃描，則是依據索引的順序進行掃描，然後偏移10條有效記錄，取下100條有效記錄。

不再贅述。

保證絕對的連續

如何保證第一次請求，第二次請求，第三次請求，。。。每一次偏移（offset）固定值，傳回的結果是完全有序，無空洞的。

1、使用rr隔離級别（repeatable read），并且按PK（唯一值字段、字段組合）排序，OFFSET

使用rr級别，保證一個事務中的每次發起的SQL讀請求是絕對視角一緻的。

使用唯一字段或字段組合排序，可以保證每次的結果排序是絕對一緻的。加速每次偏移的資料一樣，是以可以保證資料傳回是絕對連續的。

select * from tbl where xx order by a,b offset x limit xx;             

2、使用遊标

使用遊标，可以保證視角一緻，資料絕對一緻。

postgres=# \h declare  
Command:     DECLARE  
Description: define a cursor  
Syntax:  
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]  
    CURSOR [ { WITH | WITHOUT } HOLD ] FOR query             

begin;  
declare a cursor for select * from tbl where xx;  
fetch x from a;  
... 每一次請求，遊标向前移動    
end;             

參考

《PostgreSQL 資料離散性 與 索引掃描性能(btree & bitmap index scan)》 《PostgreSQL 11 preview - 分頁核心層優化 - 索引掃描offset優化(使用vm檔案skip heap scan)》 《PostgreSQL 範圍過濾 + 其他字段排序OFFSET LIMIT(多字段區間過濾)的優化與加速》 《PostgreSQL Oracle 相容性之 - TZ_OFFSET》 《PostgreSQL 索引掃描offset核心優化 - case》 《PostgreSQL 資料通路 offset 的質變 case》 《論count與offset使用不當的罪名 和 分頁的優化》 《PostgreSQL offset 原理，及使用注意事項》 《妙用explain Plan Rows快速估算行 - 分頁數估算》 《分頁優化 - order by limit x offset y performance tuning》 《分頁優化, add max_tag column speedup Query in max match enviroment》 《PostgreSQL's Cursor USAGE with SQL MODE - 分頁優化》

PostgreSQL 許願連結

您的願望将傳達給PG kernel hacker、資料庫廠商等, 幫助提高資料庫産品品質和功能, 說不定下一個PG版本就有您提出的功能點. 針對非常好的提議，獎勵限量版PG文化衫、紀念品、貼紙、PG熱門書籍等，獎品豐富，快來許願。

開不開森

.

9.9元購買3個月阿裡雲RDS PostgreSQL執行個體

PostgreSQL 解決方案集合