就职于某大型国有银行，多年从事MySQL和分布式中间件的方案设计与实施工作；资深MySQL数据库专家，架构师；DBLE开源项目积极贡献者。

一、场景描述

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

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

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

1. 表结构

<code>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'/<code>

2. 拆分方式

<code><schema>    <table>/<schema>/<code>

<code><tablerule>    <columns>id    <algorithm>hash_128_datanodes/<algorithm>/<columns>/<tablerule>id<function>    <property>1/<property>    <property>128/<property>/<function>/<code> 

二、直接翻页

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

<code>SELECT *FROM many_node_tableWHERE 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 pagingLIMIT (n-1)*M, M/<code>

在获取首页（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，用于后面的翻页动作。

<code>SELECT  *FROM many_node_tableWHERE   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 idLIMIT M/<code> 

2. 向后/向前翻页

以向后翻页为例。

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

<code>SELECT  *FROM many_node_tableWHERE   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 > maxIdORDER BY idLIMIT M/<code>

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

<code>SELECT  *FROM many_node_tableWHERE   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 < minIdORDER BY id DESCLIMIT M/<code>

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

• 没有银弹方案，最佳实践由以下限制或注意事项：
• 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 连接数消耗明显，因此翻页查询应该要算到广播语句中，而广播语句的并发量建议不要超过单个 MySQL 的 max_connections 的 10%，例如 MySQL 的 max_connections 为 512，则包含翻页查询在内的所有广播语句的并发量建议不要超过 51 条。
• 从保护 dble 内存出发，建议每页最多记录数 M 与逻辑分片数量 dataNodeCount 乘积不多于 8000，即 M * dataNode <= 8000
• 依赖 dble 的客户端控制翻页，增加了开发成本。

閱讀更多 愛可生 的文章

關鍵字: 注意事项 MySQL 银弹