GBase8a优化单表order by ... limit ... offset类型sql性能思路

1.问题场景

GBase 8a作为联机查询的数据源库时，会有以下类似的场景，业务上前端界面采用了分页展示的方式，具体sql特征为

1. 基于单表和简单过滤条件的查询
2. 展示结果采用了分页方式
3. 分页展示的sql类似：select … from 表名 where 过滤列 = … order by 排序列 limit m offset n

其中m为分页展示条数大小，n为第n页时的偏移量

这类sql容易产生翻页上的性能问题，表现为当n值较小时，执行上述sql的性能较快，当n值逐渐变大，则分页sql的执行性能随n的增大而变慢。

2.问题产生原因分析

产生上述问题的原因，需要理解GBase 8a执行，如select … from 表名 where 过滤列 = … order by 排序列 limit m offset n 这类sql的执行计划

由于GBase 8a是分布式集群系统，一张表的数据在集群内部已根据distribution分布信息而被透明的划分成了n个分片，并根据建表时选取的hash列（或者采用随机分布的方式），将数据打散到各分片中在不同的节点进行存储。

在执行select … from 表名 where 过滤列 = … order by 排序列 limit m offset n 类型的sql时优化后的执行计划类似如下原理：

从执行计划原理图可以分析，step2各节点并行进行命中数据的排序，相当于local sort，并将TOP m+n条结果发给step3的一个节点进行global sort并最终取出limit n offset m命中的数据，从执行计划角度看：

1）step2在各节点执行性能对于当分页大小n一定时，变量m变化来说应该是基本稳定的，因为local sort是对分片上命中的数据进行全排序，排序时间在m变大时是相同的，排序后取出前m+n条，此时物化m+n条数据的耗时随m的增大会有增加，在表非大宽表m值非上亿的情况下，step2的主要耗时在local sort排序上，应基本稳定；

2）step3在单节点上进行 N * （m + n）条数据的排序，其中N为节点数，m为offset值，n为limit分页大小，此时排序的数据量与节点个数，m大小成指数级增长，因此评估排序性能也会成指数级别衰减

综上造成翻页性能随m增加衰减的主要原因是执行计划中最后一步global sort时的数据量与m成指数级负相关的原因。

3.问题优化思路

根据上述分析，对于此类问题的优化需要考虑优化step3步骤中的order by部分，如果能去掉order by就能让分页的性能稳定在一个数值上。

可以参考的具体思路如下：

需要注意的是，虽然经过改造后临时表temp只有一个分片有数据，且数据已经在该分片上按照c2列有序存储了，但如果执行select … from temp limit n offset m时仍有一定概率会出现分页结果集不稳定问题，该问题的原因是物化阶段8a根据物化结果集大小和gbase_parallel_threshold参数值进行比较，如果gbase_parallel_threshold参数大小超过了物化结果集大小（这种场景是limit n中的n的大小），则物化阶段使用单线程，单线程情况下可以保证分页时每页的结果是固定的，如果物化结果集大小大于了gbase_parallel_threshold则在物化阶段会采用多线程方式，可能会因单机上多线程的执行顺序导致分页数据不稳定，因此建议最终显示分页数据时评估n与gbase_parallel_threshold的大小关系，适当调节gbase_parallel_threshold的大小保证分页结果集稳定，或者在查询temp表分页数据时将gbase_parallel_degree参数session级别调整为1，保证查询时各阶段包括物化阶段是单线程执行。

3.一个实际例子的示范

选择ssbm模型中的lineorder表作为查询表，分页查询的语句如下：

select lo_custkey, lo_orderkey, lo_orderdate, lo_orderpriority from lineorder4 where lo_custkey = 7381 order by lo_orderdate limit 10 offset 30;

lineorder4表的建表语句：

 CREATE TABLE lineorder4 (
 lo_orderkey bigint(20) DEFAULT NULL,
 lo_linenumber int(11) DEFAULT NULL,
 lo_custkey int(11) DEFAULT NULL,
 lo_partkey int(11) DEFAULT NULL,
 lo_suppkey int(11) DEFAULT NULL,
 lo_orderdate int(11) DEFAULT NULL,
 lo_orderpriority varchar(15) DEFAULT NULL COMMENT 'lookup',
 lo_shippriority varchar(1) DEFAULT NULL COMMENT 'lookup',
 lo_quantity int(11) DEFAULT NULL,
 lo_extendedprice int(11) DEFAULT NULL,
 lo_ordtotalprice int(11) DEFAULT NULL,
 lo_discount int(11) DEFAULT NULL,
 lo_revenue int(11) DEFAULT NULL,
 lo_supplycost int(11) DEFAULT NULL,
 lo_tax int(11) DEFAULT NULL,
 lo_commitdate int(11) DEFAULT NULL,
 lo_shipmode varchar(10) DEFAULT NULL COMMENT 'lookup'
) DISTRIBUTED BY('lo_custkey')

生成100w条lineorder表数据并加载至lineorder4表中

上图中的step1中的sql：

create temporary table order_lineorder distributed by ('lo_custkey ') select /*+distribute_exec*/lo_custkey, lo_orderkey, lo_orderdate, lo_orderpriority from lineorder4 where lo_custkey = 7381 order by lo_orderdate;

此时临时表order_lineorder表内有数据89条数据

集群默认参数gbase_parallel_threshold = 10000 大于 limit n offset m中的n值10，因此查询临时表order_lineorder时物化阶段可以保证是单线程，因此我们查看order_lineorder表中的数据是按lo_orderdate有序排列的，为了方面对比，将rowid伪列也取出，具体如下：

此时原始业务查询sql可以改写为，step2中的查询语句，为了方便：

select lo_custkey, lo_orderkey, lo_orderdate, lo_orderpriority, rowid from order_lineorder limit 10 offset 30；

显示信息如下：

如果我们将gbase_parallel_threshold调小到n=10的分页大小以下，再观察固定sql的分页查询执行出现显示结果不稳定的情况

set  gbase_parallel_threshold = 2;

多次执行select lo_custkey, lo_orderkey, lo_orderdate, lo_orderpriority, rowid from order_lineorder limit 10 offset 30；

两次查询显示的结果排列顺序不同

因此如果是前端页面显示的分页，适当调节gbase_parallel_threshold参数值的大小避免上述显示不稳定的问题。

3.总结

按照上述方案进行优化后，由于去掉了原始sql查询中需要进行global sort的步骤，避免了global sort步骤随offset m的值增大时耗时指数增加的问题，可以是翻页的性能趋于稳定。