一 场景描述

对于订单、交易流水之类的表，常见是应用层会生成订单号、交易流水号之类的唯一编号，dble则是以这个唯一编号分库分表，而落到MySQL的物理表上，也是直接以这个编号字段作为表的主键。

在本文中，讨论在符合以下所有条件的场景下，查询的分页技巧：

• dble的拆分列（sharding key）同时也是MySQL物理表的主键
• 连续翻页
  • 每次查询的页只能是上一次查询的前一页或者后一页
  • 第一次查询必须为首页

1. 表结构

CREATE TABLE many_node_table (
    id CHAR(128) PRIMARY KEY,
    ts TIMESTAMP NOT NULL,
    branchId CHAR(5) NOT NULL,
    departId CHAR(10) NOT NULL,
    opType VARCHAR(20) NOT NULL,
    operator VARCHAR(20) NOT NULL,
    INDEX idx_ts (ts),
    INDEX idx_branchId_departId (branchId, departId)
) COMMENT 'This is an order table'

2. 拆分方式

<!-- schema.xml -->
<schema name="testdb" >
    <table name="many_node_table" rule="hash_by_id" dataNode="dn$0-127" />
</schema>

<!-- rule.xml -->
<tableRule name="hash_by_id">
    <columns>id</column>
    <algorithm>hash_128_datanodes</algorithm>
</tableRule>id

<function name="hash_128_datanodes" class="Hash">
    <property name="length">1</property>
    <property name="count">128</property>
</function>

二 直接翻页

MySQL语法支持LIMIT [start,] length语法来进行翻页，例如：

SELECT *
FROM many_node_table
WHERE ts BETWEEN TIMESTAMP('2019-01-01 00:00:00') AND TIMESTAMP('2019-03-31 23:59:59')
  AND branchId = 'user_specified_bratop Mch'
  Atop MD departId = 'user_specified_department'
ORDER BY id
-- n is the page number
-- M is the page size which means the max records of one page, should not change during the paging
LIMIT (n-1)*M, M

在获取首页（n=1）时，这个SQL的执行计划可优化为“每个MySQL各自返回符合条件的局部top M记录，然后dble对各个MySQL的局部top M记录进行进一步筛选，得到全局top M记录”。由于dble能够下推计算给MySQL（让各个MySQL计算局部top M），一方面，减少了dble需要处理的数据量，减少了对dble的空间占用和代价较高的网络传输量，另一方面，MySQL数量多于dble，下推给MySQL的计算相当于获得了并行计算的好处。因此，获取首页的理论性能并不差。

但是，在获取后续的页面时，该SQL的执行性能随着页码增大（n趋向于＋∞）而不断劣化。原因在于此时现阶段的dble无法下推计算给MySQL。以获取第2页（n=2）为例，dble无法直接否定“第一页和第二页数据都在同一个dataNode上”这种场景，所以dble交给MySQL的LIMIT子句为了照顾这种场景，假设页体积为100，那么实际下推的只能是LIMIT 0, 200，以此类推，由于从第一页到第n页数据都在同一个dataNode上的牵制，dble为了保证执行计划的安全，只能让MySQL执行LIMIT 0, n*M，导致页码n越往后，dble要处理的数据量就越大，从而性能每况愈下。

三 最佳实践

为了克服直接翻页在页数靠后时的性能劣化问题，其中一种解决思路就是解决掉dble只能下推LIMIT 0, n*M的无奈。从操作上来说，我们最终的目标是让LIMIT子句与页码n无关，最好是恒定为LIMIT 0, M(即LIMIT M)。

至此，解决思路就很明显了：让dble下推SQL给MySQL时，告知MySQL不要返回已经拿到过了的记录就好了。

id NOT IN ( retrivedIds ... )这样的WHERE条件，在页码增大时，会导致需要列举的id过多，执行效率低下，语句也很容易超出max_packet_size的限制。因此，我们应该对结果集进行基于id的排序，然后就能使用更为简洁的WHERE条件id > maxId来在MySQL层面过滤掉不需要的记录了。

下面就是基于这个思路的实践方法。

1. 获取首页

直接翻页的语句获取首页的效率已是最高，直接使用直接翻页的SQL，但对返回结果中，id字段的最小值和最大值分别记录为minId和maxId，用于后面的翻页动作。

SELECT
  *
FROM many_node_table
WHERE 
  ts BETWEEN TIMESTAMP('2019-01-01 00:00:00') AND TIMESTAMP('2019-03-31 23:59:59')
  AND branchId = 'user_specified_branch'
  AND departId = 'user_specified_department'
ORDER BY id
LIMIT M

2. 向后/向前翻页

以向后翻页为例。

替换以下SQL中的maxId后，交给dble执行。返回的记录本身按照id字段已经有序，直接就是下一页内容。记得更新minId和maxId。

SELECT
  *
FROM many_node_table
WHERE 
  ts BETWEEN TIMESTAMP('2019-01-01 00:00:00') AND TIMESTAMP('2019-03-31 23:59:59')
  AND branchId = 'user_specified_branch'
  AND departId = 'user_specified_department'
  -- tell MySQL do not return retrived rows --
  id > maxId
ORDER BY id
LIMIT M

同样道理，向前翻页就是替换以下SQL中的minId后，交给dble执行。千万要记得更新minId和maxId。

SELECT
  *
FROM many_node_table
WHERE 
  ts BETWEEN TIMESTAMP('2019-01-01 00:00:00') AND TIMESTAMP('2019-03-31 23:59:59')
  AND branchId = 'user_specified_branch'
  AND departId = 'user_specified_department'
  -- tell MySQL do not return retrived rows --
  id < minId
ORDER BY id DESC
LIMIT M

最佳实践的限制与注意事项

没有银弹方案，最佳实践由以下限制或注意事项：

• dble的拆分列（sharding key）同时也是MySQL物理表的主键
• 连续翻页
  • 每次查询的页只能是上一次查询的前一页或者后一页
  • 第一次查询必须为首页
• 翻页SQL必须是单表SQL，因为两个表JOIN的时候，结果集里1条记录的字段可能实际上来自不同的表，而导致记录有多个拆分列值，无法按照本方法翻页
• 翻页SQL必须要有ORDER BY子句
• 翻页SQL的ORDER BY后缀必须为拆分列，继续上文的例子，可以是ORDER BY id、ORDER BY ts, id，但不能是ORDER BY id, ts
• 无论是“获取首页”还是“向后/向前翻页”，其SQL一般都是广播语句（需要查询该表所有dataNode），广播语句对MySQL的max_connections连接数消耗明显，因此翻页查询应该要算到广播语句中，而广播语句的并发量建议不要超过单个MySQ的max_connections的10%，例如MySQL的max_connections为512，则包含翻页查询在内的所有广播语句的并发量建议不要超过51条
• 从保护dble内存出发，建议每页最多记录数M与逻辑分片数量dataNodeCount乘积不多于8000，即M * dataNode <= 8000
• 依赖dble的客户端控制翻页，增加了开发成本