有哪些常见的Hologres分页实践_实时数仓 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;