Hologres實現高效能分頁的多種方案-即時數倉 Hologres-阿里雲

Hologres支援為即時報表等情境提供分頁能力，本文為您介紹幾種常見的分頁實踐，您可以根據業務需求選擇合適的方案，以達到更佳的查詢效能。

分頁決策樹

分頁SQL命令文法如下。

SELECT ...
FROM ...
ORDER BY key
LIMIT N OFFSET S;

SQL實現原理：對於掃描出來的記錄，按照key排序，取TOP N+S條記錄（通過部分排序PARTIAL SORT實現），然後丟棄前S條記錄，返回剩下的N條記錄。

在分頁方案中，核心的考慮因素如下：

N：每頁的記錄條數。一般數值範圍為10~1000。
S：分頁起始大小。如果S很大（比如第1000頁，一般稱為“深分頁”），則TOP N+S的記錄就非常多，因此求TOP的代價就非常大。
W：每條記錄的大小。排序時會有大量的記錄COPY動作，如果W很大，在排序時要COPY的內容就會非常大，這會極大的影響查詢效能。

方案1：基本分頁法

適用情境：適用於S不大（S<100*N，淺分頁）且W不大（每條記錄大小W<10KB）的情境。
SQL樣本如下。
重要
如果未使用ORDER BY子句，則輸出結果的順序是未定義的。因此使用LIMIT N OFFSET S文法進行兩次分頁時，可能會輸出重複結果或者跳過某些記錄的結果。
```
SELECT *
FROM table1
ORDER BY key1
LIMIT N OFFSET S;
```
該SQL文法等價於MySQL的如下文法：
```
-- 在MySQL中如下兩種SQL都是可使用
SELECT * FROM table1 ORDER BY key1 LIMIT S, N;
SELECT * FROM table1 ORDER BY key1 LIMIT N OFFSET S;
```

方案2：主鍵排序分頁方案

適用情境：適用於表有主鍵（Primary Key）且分頁是按照表的主鍵進行排序的情境。
SQL命令文法如下。
```
SELECT *
FROM table1
ORDER BY pk
LIMIT N OFFSET S;
```
對於深分頁（S較大）或者每條記錄的大小W比較大的情境，如下SQL文法可以節省資源和提高查詢效率：
```
SELECT *
FROM table1
-- ?填入上一頁最後一條記錄的pk
WHERE pk > ?
ORDER BY pk
LIMIT N;
```
說明
此情境中應用側會記住上一頁的位置，下一頁直接從該位置開始讀取。由於在掃描時已經對資料進行過濾，所以排序的資料量會變小，從而提高查詢效率。

方案3：基於暫存資料表的分頁方案

適用情境：適用於查詢過程中涉及多張表，且需要經過複雜計算後再對結果集進行分頁的情境（如使用JOIN串連多張表的查詢結果）。

SQL命令文法如下。

SELECT *
FROM
 (
   ... 複雜SQL子查詢
)
ORDER BY key1
LIMIT N OFFSET S;

該情境下，由於每次分頁都需要把複雜SQL子查詢重新執行一遍，因此效能會很差。建議先把複雜SQL子查詢的結果寫入一張暫存資料表中，然後查詢操作基於該暫存資料表進行，對應的SQL步驟如下：

為了避免每次子查詢都建立一張暫存資料表，導致暫存資料表泛濫，可以事先建立一張暫存資料表。

BEGIN
CREATE TABLE query_result_table_tmp(
  query_id text,
  key1 text,
  ...
  );
CALL set_table_property('query_result_table_tmp', 'distribution_key', 'query_id');
CALL set_table_property('query_result_table_tmp', 'clustering_key', 'query_id,key1');
CALL set_table_property('query_result_table_tmp', 'time_to_live', '24 hour');

對於每個複雜子查詢，將結果寫入暫存資料表。

INSERT INTO query_result_table_tmp(query_id, key1, ...)
-- ?填入query_id
SELECT ?, key1, ... FROM (
  ... -- 複雜SQL子查詢
  );

分頁語句實現如下。

SELECT *
FROM query_result_table_tmp
ORDER BY key1
LIMIT N OFFSET S;