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步骤如下：
    1. 为了避免每次子查询都创建一张临时表，导致临时表泛滥，可以事先建立一张临时表。
      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');
    2. 对于每个复杂子查询，将结果写入临时表。
      insert into query_result_table_tmp(query_id, key1, ...)
-- ?填入query_id
SELECT ?, key1, ... FROM (
  ... -- 复杂SQL子查询
  );
    3. 分页语句实现如下。
      select *
from query_result_table_tmp
order by key1
limit n offset s;