OceanBase v4.2.1特性解读：如何解决MySQL模式下分页查询结果不稳定的问题

导言

MySQL业务迁移经常遇到分页查询结果不稳定，导致业务迁移需要DBA介入绑定计划的问题。举个例子说明下为什么有的分页查询在MySQL数据库下运行没有问题，迁移到OceanBase会出问题。

select t1.* 
from t1 left join t2 on t1.c1 = t2.c1
limit 10,10;

因为MySQL 5.6只有Nested Loop Join算法，这个查询只会生成下面的计划：

================================================
|ID|OPERATOR               |NAME|EST. ROWS|COST|
------------------------------------------------
|0 |LIMIT                  |    |10       |862 |
|1 | NESTED-LOOP OUTER JOIN|    |20       |862 |
|2 |  TABLE SCAN           |t1  |20       |46  |
|3 |  TABLE GET            |t2  |1        |40  |
================================================

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t2.c1]), filter(nil), limit(10), offset(10)
  1 - output([t1.c1], [t2.c1]), filter(nil),
      conds(nil), nl_params_([t1.c1])
  2 - output([t1.c1]), filter(nil),
      access([t1.c1]), partitions(p0)
  3 - output([t2.c1]), filter(nil),
      access([t2.c1]), partitions(p0)

从这个计划来看，查询输出会继承t1表的序，如果客户直接在这个计划的基础上面做业务分页处理，业务代码可以正常运行。但是，一旦用户对t1表做了索引变更，引起MySQL的计划变化，该查询的分页结果也会随之改变，导致业务出错。这个问题的本质是业务使用了非标准的分页查询，而MySQL的“特性”使得业务能正常运行。

如果业务迁移到OceanBase，由于OceanBase支持多种join算法，可能会生成如下的计划：

================================================
|ID|OPERATOR              |NAME|EST. ROWS|COST |
------------------------------------------------
|0 |LIMIT                 |    |10       |85285|
|1 | HASH RIGHT OUTER JOIN|    |20       |85285|
|2 |  TABLE SCAN          |t2  |100000   |38681|
|3 |  TABLE SCAN          |t1  |20       |46   |
================================================

Outputs & filters: 
-------------------------------------
  0 - output([t1.c1], [t2.c1]), filter(nil), limit(10), offset(10)
  1 - output([t1.c1], [t2.c1]), filter(nil), 
      equal_conds([t1.c1 = t2.c1]), other_conds(nil)
  2 - output([t2.c1]), filter(nil), 
      access([t2.c1]), partitions(p0)
  3 - output([t1.c1]), filter(nil), 
      access([t1.c1]), partitions(p0)

这个计划就不会继承t1表的序，如果客户直接在这个计划的基础上面做业务分页处理，业务代码运行结果可能不对。

为了能够让这一类业务平滑迁移到OceanBase，我们需要为这种分页查询主动添加order by。

注意：我们不推荐使用该功能迁移MySQL业务

分页保序功能开关

针对上述问题，OceanBase 4.2.1 BP2引入了分页保序功能，只在MySQL租户下可以使用，增加隐藏配置项_preserve_order_for_pagination、查询hint PRESERVE_ORDER_FOR_PAGINATION用于控制查询是否使用分页保序功能。

隐藏配置项

_preserve_order_for_pagination隐藏配置为租户级别，可以控制租户是否打开或关闭分页保序功能，当前默认行为是关闭。如果需要打开租户级别的分页保序功能，可以使用如下命令：

alter system set _preserve_order_for_pagination = 1;

下一个章节会介绍打开该功能后，数据库会做一些什么事情，达到什么效果。

查询HINT

为了增加更细粒度的控制手段，OceanBase还提供了查询级别的控制方式，例如，如果需要针对特定查询打开分页保序功能：

select /*+OPT_PARAM('PRESERVE_ORDER_FOR_PAGINATION', 'TRUE')*/
* from t1 limit 10;

注意，该HINT不能用于控制特定的查询块是否打开分页保序功能，只能用于控制整个查询是否打开分页保序功能！

对于上面介绍的两种控制方式，OceanBase优先使用HINT。举个例子说明，如果租户设置了隐藏配置项为打开状态，同时在查询中使用/*+OPT_PARAM('PRESERVE_ORDER_FOR_PAGINATION', 'FALSE')*/，那么该查询不会开启分页保序功能。

分页保序功能

保序场景1

用户打开分页保序功能之后OceanBase会做什么事情呢？还是举个例子说明：

select t1.c1, t1.c2 from t1, t2 where t1.c1 = t2.c1 limit 10,10;

针对这种查询，OceanBase首先会对查询结果排序，然后对排序的结果分页处理，改写之后的查询如下：

select t1.c1, t1.c2 from t1, t2 where t1.c1 = t2.c1 
order by t1.c1, t2.c2 limit 10,10;

排序之后的分页结果必然是稳定的，无论OceanBase选择的是什么计划，或者分页过程有任何计划变化，都不会影响分页查询的结果。

保序场景2

还有一种场景，用户查询已经有order by语句块了，但是由于只针对部分结果排序，导致分页结果还是不稳定，例如：

select t1.c1, t1.c2 from t1, t2 where t1.c1 = t2.c1 
order by t1.c1 limit 10,10;

针对这种场景，如果打开了分页保序功能，OceanBase会在用户期望排序结果的前提下，额外增加排序字段。

select t1.c1, t1.c2 from t1, t2 where t1.c1 = t2.c1 
order by t1.c1, t2.c2 limit 10,10;

保序场景3

另外还存在一种和场景 2 相似的场景，查询中有 order by 语句块针对部分结果排序，但 order by 语句块在视图内部。该场景下，如果打开了分页保序功能，无论 order by 语句块外层还有多少层视图，OceanBase 都会额外增加排序字段，例如：

select * from 
	select * from (
  	select t1.c1, t1.c2 from t1, t2 where t1.c1 = t2.c1 
			order by t1.c1
  ) 
)limit 10,10;

==>

select * from 
	select * from (
  	select t1.c1, t1.c2 from t1, t2 where t1.c1 = t2.c1 
			order by t1.c1, t1.c2
  ) 
)limit 10,10;

保序场景4

对于其他场景，预期想要的结果序会被改变，例如：

select t2.c1, t2.c2
from (select * from t1 order by t1.c1, t1.c2) V1
left join t2 on V.c1 = t2.c1
limit 10,10;

这个查询用户预期可能是按照t1表排序之后再分页查询结果，但是实际上任何SQL语义并不会保证查询的数据一定需要按照t1表的结果排序之后再分页。OceanBase会在最外层增加排序字段t2.c1,t2.c2，执行结果是按照输出结果排序（即t2表的字段），然后再分页，其等价查询如下：

select t2.c1, t2.c2
from t1
left join t2 on V.c1 = t2.c1
order by t2.c1, t2.c2
limit 10,10;

性能影响

同时需要注意到一点，打开分页保序功能后，会引入额外的排序计算，部分查询可能会出现性能回退。

开启注意事项

用户打开分页保序功能之后，查询输出结果可能与未开启分页保序功能时不同！

select t1.c1, t1.c2 from t1, t2 where t1.c1 = t2.c1 limit 10,10;

未开启分页保序功能时，上面的查询输出结果会随着数据库的计划改变而变化，不会输出稳定的结果。

select t1.c1, t1.c2 from t1, t2 where t1.c1 = t2.c1 
order by t1.c1, t2.c2 limit 10,10;

打开分页保序功能后，OceanBase 会主动添加排序字段，改写如上，无论计划怎么改变，查询结果都是稳定的。