文档章节

分布式 | dble 分页技巧_主键是拆分列_连续翻页

爱可生
 爱可生
发布于 01/17 18:24
字数 1758
阅读 26
收藏 0

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

一 场景描述

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

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

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

© 著作权归作者所有

爱可生

爱可生

粉丝 16
博文 254
码字总数 450380
作品 1
徐汇
私信 提问
加载中

评论(0)

技术分享 | MyCat的坑如何在分布式中间件DBLE上改善(内含视频链接)

作者简介 蓝寅,开源分布式中间件DBLE项目负责人;持续专注于数据库方面的技术, 始终在一线从事开发;对数据复制,读写分离,分库分表的有深入的理解与实践。 3月14日,爱可生开源社区联合I...

爱可生
2019/03/19
116
0
开源分布式中间件 DBLE 快速入门指南

DBLE 是企业级开源分布式中间件,江湖人送外号 “MyCat Plus”;以其简单稳定,持续维护,良好的社区环境和广大的群众基础得到了社区的大力支持; 环境准备 DBLE项目资料 DBLE官方网站:htt...

爱可生
2019/01/25
56
0
社区投稿 | DBLE rule.xml 配置解析

文章来源:爱可生云数据库 作者:余朝飞 DBLE项目介绍 DBLE官方网站:https://opensource.actionsky.com 可以详细了解DBLE的背景和应用场景,本文不涉及到的细节都可在官方文档获得更细节都信...

爱可生
2019/02/18
7
0
Qt编写数据库通用翻页demo(开源)

在Qt与数据库结合编程的过程中,记录一多,基本上都需要用到翻页查看记录,翻页有个好处就是可以减轻显示数据的表格的压力,不需要一次性将数据库表的记录全部显示,也基本上没有谁在一页上需...

飞扬青云
2018/09/08
241
0
轻量级数据库中间件利器Sharding-JDBC深度解析

本文根据DBAplus社群第114期线上分享整理而成。 主题简介: 1、关系型数据库中间件核心功能介绍 2、Sharding-JDBC架构及内核解析 3、Sharding-JDBC未来展望 关系型数据库凭借灵活查询的SQL和...

张亮
2017/07/28
0
0

没有更多内容

加载失败,请刷新页面

加载更多

入门级科普:五分钟让你的应用拥有高精度定位功能

本文作者:用户_123456789 什么是智能定位服务 定位能力即帮助用户解决“我在哪”的问题,依托百度位置大数据及多种混合定位方式,百度地图开放平台为智能穿戴、用车出行、快递物流、生活服务...

百度开发者中心
2019/08/14
9
0
在Ruby on Rails中对nil v。空v。空白的简要解释 - A concise explanation of nil v. empty v. blank in Ruby on Rails

问题: I find myself repeatedly looking for a clear definition of the differences of nil? 我发现自己一再寻找nil?差异的明确定义nil? , blank? , blank? , and empty? , empty? in ......

javail
今天
15
0
DevOps与NoOps现状分析

时下的IT趋势中,DevOps 正是一个热语。它起源于几年前SPA (单页面应用) 的前端应用.我认为常态的IT技术适应就是,在新技术爆发的那一时刻开始,立马就会被敏锐的人们所采用,然后被快速传播...

tidings_
今天
21
0
OSChina 周六乱弹 —— 代码创造人工生命

Osc乱弹歌单(2020)请戳(这里) 【今日歌曲】 @小小编辑推荐:《inner universe》 - ORIGA 《inner universe》 - ORIGA 手机党少年们想听歌,请使劲儿戳(这里) 当机器人具有意识的时候,...

小小编辑
今天
20
1
怎么创建远程桌面连接

1、IIS7远程桌面 管理中文最新版是一款专业的远程桌面管理工具,更新了原09网络远程桌面管理,较之以前的版本,操作更加便捷,能够同时远程多台服务器,多台服务器间自由切换,完全无压力。I...

吹的心痒痒
今天
22
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部