OceanBase v4.2 特性解析:如何用分页保序功能解决MySQL模式分页查询不稳定
导言
在MySQL业务迁移OceanBase过程中,经常遇到的一个问题是分页查询结果的不稳定性,这通常需要数据库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 会主动添加排序字段,改写如上,无论计划怎么改变,查询结果都是稳定的。
相关文章:
OceanBase v4.2 特性解析:如何用分页保序功能解决MySQL模式分页查询不稳定
导言 在MySQL业务迁移OceanBase过程中,经常遇到的一个问题是分页查询结果的不稳定性,这通常需要数据库DBA介入绑定执行计划。下面简单举个例子,以便大家更好地理解为什么有的分页查询,在原来的MySQL数据库下运行没有问题…...

RK3588/算能/Nvidia智能盒子:加速山西铝业智能化转型,保障矿业皮带传输安全稳定运行
近年来,各类矿山事故频发,暴露出传统矿业各环节的诸多问题。随着全国重点产煤省份相继出台相关政策文件,矿业智能化建设进程加快。皮带传输系统升级是矿业智能化的一个重要环节,同时也是降本增效的一个重点方向。 △各省份智能矿山…...

2024.6.24 IDEA中文乱码问题(服务器 控制台 TOMcat)实测已解决
1.问题产生原因: 1.文件编码不一致:如果文件的编码方式与IDEA设置的编码方式不一致,就会产生乱码。确保文件和IDEA使用相同的编码,通常是UTF-8。2.IDEA设置问题:检查IDEA的全局编码设置和项目编码设置是否正确。3.终端…...

桌面编辑器ONLYOFFICE 功能多样性快来试试吧!
目录 ONLYOFFICE 桌面编辑器 8.1 ONLYOFFICE介绍 主要功能和特点 使用场景 1.PDF编辑器 2.幻灯片版式 3.编辑,审阅和查看模式 4.隐藏连接到云版块 5.RTL语言支持和本地化选项 6.媒体播放器 7、其他新功能 8.下载 总结 ONLYOFFICE 桌面编辑器 8.1 官网地…...
三维渲染中的散光圆
三维渲染中的散光圆 散光圆(Circle of Confusion,CoC)是三维渲染和摄影中的一个重要概念,尤其在景深(Depth of Field,DoF)效果的生成中起着关键作用。它描述了在成像过程中,焦点前后…...

Vue3 + Ant-Design 中 a-date-picke 实现选择切换年份 没有鼠标光标,输入框内自带‘年’
效果图: 效果图 <a-date-picker ref"datePicker" v-model:value"year" picker"year" value-format"YYYY年" format"YYYY年" :bordered"false" :allowClear"false" inputReadOnly change&…...

Jetpack Compose_Alignment对其+Arrangement排列
文章目录 1.Alignment 对齐1.1Alignment 对齐方式1.2AbsoluteAlignment 绝对对齐1.3BiasAlignment 偏差对齐1.4BiasAbsoluteAlignment偏差绝对对齐 2.Arrangement 排列2.1Arrangement 排列方式2.2Arrangement.Horizontal2.3Arrangement.Vertical 1.Alignment 对齐 1.1Alignmen…...
Vue进阶之Vue无代码可视化项目(五)
Vue无代码可视化项目 编排引擎smooth-dndLeftPanel.vueLayoutView.vuestores/debug.tsstores/editor.tsAppNavigator.vue添加-左侧栏添加到中间部分LayoutView.vuestore/editor.tsLeftPanel.vue移动-中间部分区域的位置更改新建文件夹utils、文件array.tsarray.tsLayoutView.vu…...

【Linux进程】Linux下的---七大进程状态(什么是进程状态?Linux下有哪些进程状态?)
目录 一、前言 二、什么是进程状态? 三、操作系统(OS)下的 --- 进程状态 🔥运行状态🔥 🔥阻塞状态🔥 🔥挂起状态🔥 四、Linux下的7种进程状态 🔥运行状态 -- R🔥…...
Linux的dev/ 和 sys/ 和 proc/ 目录
linux精神: 一切设备皆文件。 设备被抽象成文件 1、 /dev : 该目录放的设备文件,是应用程序和内核的交互文件,应用程序对这些文件的读写控制可以直接访问到实际的设备 应用程序通过mknod创建的文件,如果底层驱动对mknod的设备号…...
代码随想录算法训练营day64 | 98. 所有可达路径
图论理论基础 1、图的种类 整体上一般分为 有向图 和 无向图。 加权有向图,就是图中边是有权值的,加权无向图也是同理。 2、度 无向图中有几条边连接该节点,该节点就有几度 在有向图中,每个节点有出度和入度。出度ÿ…...

php上传zip压缩包到服务器并解压,解析压缩包内excel表格数据导入到数据库
需求: 1.需要管理后台将excel表格中的每条单词数据导入到数据库中. 2.每条单词数据对应的图片和音频文件需要上传到服务器中. 为了让客户上传数据方便,考虑了一下决定通过后台上传压缩包的方式实现 测试压缩包: 压缩包的目录结构 管理后台导入教材 public function upload…...

48-5 内网渗透 - JuicyPotato、Pipe Potato提权
Juicy Potato Juicy Potato 与 Rotten Potato(烂土豆) 的原理几乎完全相同,只是在后者的基础上做了扩展,以便更灵活地利用 Rotten Potato。Juicy Potato 不再像 Rotten Potato 那样依赖于一个现有的 Meterpreter,并且可以自定义 COM 对象加载的端口,以及根据系统版本更换…...

Windows C++ 应用软件开发从入门到精通详解
目录 1、引言 2、IDE 开发环境介绍 2.1、Visual Studio 2.2、Qt Creator 3、 C语言特性 3.1、熟悉泛型编程 3.2、了解C/C异常处理 3.3、熟练使用STL容器 3.4、熟悉C11新特性 4、Windows 平台的编程技术与调试技能 4.1、需要掌握的若干编程技术和基础知识 4.2、需…...
Leetcode 3195. Find the Minimum Area to Cover All Ones I
Leetcode 3195. Find the Minimum Area to Cover All Ones I 1. 解题思路2. 代码实现 题目链接:3195. Find the Minimum Area to Cover All Ones I 1. 解题思路 这一题还是挺简单的,只要找到所有1所在的元素的上下左右4个边界,作为目标矩形…...

ONLYOFFICE8.1版本桌面编辑器测评
目录 一、引言 二、界面设计:简洁大方,操作便捷 三、功能评测:全面升级,满足多样需求 四、性能评测:稳定流畅,高效运行 五、总结与展望 ONLYOFFICE官网链接:ONLYOFFICE - 企业在线办公应用…...
线性代数|机器学习-P15矩阵A的低秩变换下的逆矩阵
文章目录 1. 单位矩阵的秩1变换1.1 功能说明1.2 证明 2. 单位矩阵 I n I_n In的秩k变换3. 一般矩阵A的秩k变换4. 公式用途4.1 求解方程4.2 卡曼滤波 1. 单位矩阵的秩1变换 1.1 功能说明 假设我们有一个单位矩阵I,列向量u,v那么当我们对单位向量I减去秩为1的矩阵…...

强强联合 极光推送(JPush)成为华为生态市场首家推送类SDK服务商
近日,中国领先的客户互动和营销科技服务商,极光(Aurora Mobile,纳斯达克股票代码:JG)的核心产品极光推送(JPush)顺利通过华为开发者联盟的多项测试及审核,成为首家在Harm…...
防止在 Qt 中触发信号
在 Qt 中工作时,有时我们需要暂时阻止某些信号的触发。以下是一个经典场景:我们有一个 QCheckBox 对象,当用户勾选或取消勾选时,需要调用一个函数,因此我们将这个函数连接到 stateChanged(int state) 信号。然而&#…...

【UML用户指南】-17-对基本行为建模-交互
目录 1、消息的可视化表示 2、对象与角色 3、链和连接件 4、消息 5、序列 6、创建、修改和撤销 7、表示法 8、常用建模技术 8.1、对控制流建模 8.1.1、基于时间的控制流 8.1.2、基于结构的控制流 在任何有意义的系统中,对象都不是孤立存在的,…...

无法与IP建立连接,未能下载VSCode服务器
如题,在远程连接服务器的时候突然遇到了这个提示。 查阅了一圈,发现是VSCode版本自动更新惹的祸!!! 在VSCode的帮助->关于这里发现前几天VSCode自动更新了,我的版本号变成了1.100.3 才导致了远程连接出…...
【android bluetooth 框架分析 04】【bt-framework 层详解 1】【BluetoothProperties介绍】
1. BluetoothProperties介绍 libsysprop/srcs/android/sysprop/BluetoothProperties.sysprop BluetoothProperties.sysprop 是 Android AOSP 中的一种 系统属性定义文件(System Property Definition File),用于声明和管理 Bluetooth 模块相…...
论文解读:交大港大上海AI Lab开源论文 | 宇树机器人多姿态起立控制强化学习框架(一)
宇树机器人多姿态起立控制强化学习框架论文解析 论文解读:交大&港大&上海AI Lab开源论文 | 宇树机器人多姿态起立控制强化学习框架(一) 论文解读:交大&港大&上海AI Lab开源论文 | 宇树机器人多姿态起立控制强化…...

令牌桶 滑动窗口->限流 分布式信号量->限并发的原理 lua脚本分析介绍
文章目录 前言限流限制并发的实际理解限流令牌桶代码实现结果分析令牌桶lua的模拟实现原理总结: 滑动窗口代码实现结果分析lua脚本原理解析 限并发分布式信号量代码实现结果分析lua脚本实现原理 双注解去实现限流 并发结果分析: 实际业务去理解体会统一注…...

IT供电系统绝缘监测及故障定位解决方案
随着新能源的快速发展,光伏电站、储能系统及充电设备已广泛应用于现代能源网络。在光伏领域,IT供电系统凭借其持续供电性好、安全性高等优势成为光伏首选,但在长期运行中,例如老化、潮湿、隐裂、机械损伤等问题会影响光伏板绝缘层…...

无人机侦测与反制技术的进展与应用
国家电网无人机侦测与反制技术的进展与应用 引言 随着无人机(无人驾驶飞行器,UAV)技术的快速发展,其在商业、娱乐和军事领域的广泛应用带来了新的安全挑战。特别是对于关键基础设施如电力系统,无人机的“黑飞”&…...

手机平板能效生态设计指令EU 2023/1670标准解读
手机平板能效生态设计指令EU 2023/1670标准解读 以下是针对欧盟《手机和平板电脑生态设计法规》(EU) 2023/1670 的核心解读,综合法规核心要求、最新修正及企业合规要点: 一、法规背景与目标 生效与强制时间 发布于2023年8月31日(OJ公报&…...

nnUNet V2修改网络——暴力替换网络为UNet++
更换前,要用nnUNet V2跑通所用数据集,证明nnUNet V2、数据集、运行环境等没有问题 阅读nnU-Net V2 的 U-Net结构,初步了解要修改的网络,知己知彼,修改起来才能游刃有余。 U-Net存在两个局限,一是网络的最佳深度因应用场景而异,这取决于任务的难度和可用于训练的标注数…...
【FTP】ftp文件传输会丢包吗?批量几百个文件传输,有一些文件没有传输完整,如何解决?
FTP(File Transfer Protocol)本身是一个基于 TCP 的协议,理论上不会丢包。但 FTP 文件传输过程中仍可能出现文件不完整、丢失或损坏的情况,主要原因包括: ✅ 一、FTP传输可能“丢包”或文件不完整的原因 原因描述网络…...
绕过 Xcode?使用 Appuploader和主流工具实现 iOS 上架自动化
iOS 应用的发布流程一直是开发链路中最“苹果味”的环节:强依赖 Xcode、必须使用 macOS、各种证书和描述文件配置……对很多跨平台开发者来说,这一套流程并不友好。 特别是当你的项目主要在 Windows 或 Linux 下开发(例如 Flutter、React Na…...