开发者社区> 开源小秘书> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PolarDB-X的in常量查询

简介: 实际场景中经常需要根据一些常量指标做IN查询,并且IN值往往是分区键。例如在电商场景中,有两张表,买家表与订单表。订单的具体信息会记录到订单表中,该表按照订单ID进行哈希拆分;买家表则会保存买家ID及其关联的订单ID。
+关注继续查看

场景

实际场景中经常需要根据一些常量指标做IN查询,并且IN值往往是分区键。例如在电商场景中,有两张表,买家表与订单表。订单的具体信息会记录到订单表中,该表按照订单ID进行哈希拆分;买家表则会保存买家ID及其关联的订单ID。一个买家经常需要查询其已购买的所有订单,一种普遍的做法是首先查询买家表获取该买家的所有订单ID,然后根据上述订单ID查询订单的具体信息。假设订单表有4个分片,买家A已下单的订单ID分别为1、 2、4、5和9,那么便会产生如下的SQL,即包含5个值的IN条件查询。

逻辑sql: select * from order where order_id in (1, 2, 4, 5, 9);

执行方式

不同于MySQL,PolarDB-X的架构可以分为计算层和存储层,存储层的DN 节点(数据节点)保存所有的数据。更多背景信息可以参考专栏文章 PolarDB-X 简介。

b1.jpg

那么计算节点在收到这条逻辑SQL后,如何从DN节点拉取数据并进行计算呢?

Naive的执行方式

下发至所有分片的物理SQL中均包含所有的in值,如下所示。这种naive的执行方式会带来两个问题,一是随着订单表分片数的增多,需要扫描的分片数急剧增加,而该买家订单数据所在的分片数并未增加;二是随着in值的增多,下发的物理SQL在执行时会由索引扫描变为全表扫描,以上两个原因使得RT急剧升高。

分片1 物理sql: select * from order_01 where order_id in (1, 2, 4, 5, 9);
分片2 物理sql: select * from order_02 where order_id in (1, 2, 4, 5, 9);
分片3 物理sql: select * from order_03 where order_id in (1, 2, 4, 5, 9);
分片4 物理sql: select * from order_04 where order_id in (1, 2, 4, 5, 9);

基于动态裁剪的执行

由上述讨论可知,我们希望可以裁剪掉一定不包含所需数据的分片,由于in字段即为分区键,因此我们可以使用分区算法计算每个in值所属分区,如下所示。

hash(1) = 1, hash(2) = 2, hash(4) = 4, hash(5) = 1, hash(9) = 1

以上述例子为例,显然我们无需向分片3下发物理SQL。进一步地,因为分片1只可能包含订单ID为1、5和9的数据,而不可能包含订单ID为2或4的数据,于是我们可以进一步对物理SQL中包含的in值进行裁剪。

现在我们知道了需要下发的SQL涉及三个分片,那么如何下发这三条物理SQL呢,或者说下发SQL并等待结果的并行度是多少呢?显然,有两种极端,一是全部串行,该方式执行效率极低;二是全部并行,同时起有效分片数个线程向各个分片发送所有物理SQL,该方式的问题是如果分片数太多,会给系统带来非常大的压力,同时会有大量的线程上下文切换。综合考虑,我们默认将下发物理SQL的并行度设置为机器的CPU核数,使用类似滑动窗口的方式下发物理SQL。

此外,我们希望下发到每个分片的物理SQL中包含的in值不要太多,否则该SQL在DN节点执行时很有可能进行全表扫描而非预期的索引扫描。因此当in值较多时,我们会对in值进行切割,分批下发,执行流程如下图所示。
b1.jpg

仍然以上述的例子为例,假设我们的CN节点只有两个核,假设每次下发的物理SQL中的in值不超过两个(当然实际中不会设置的这么小),那么我们会共计下发四条物理SQL,如下所示,其会被划分为两个批次,也就是说计算节点和数据节点需要有两次网络交互。试想一下,如果我们下发的物理SQL只有两条时,那么我们便可以在一个批次中完成所有物理SQL的下发,此时计算节点和数据节点只有一次网络交互。因此,当用户对于RT要求较高时,我们建议in值数量应当较少,以保证其涉及的分片数不超过CPU核数且每个分片只会下发一条物理SQL。

//第一次下发了两条物理sql
分片1 物理sql-1: select * from order_01 where order_id in (1, 5);
分片2 物理sql: select * from order_02 where order_id in (2);
//第二次下发了两条物理sql
分片1 物理sql-2: select * from order_01 where order_id in (9);
分片4 物理sql: select * from order_04 where order_id in (4);

in值数量不固定带来的挑战

为了加速SQL的执行,我们会对参数化SQL的执行计划进行缓存,而业务代码中的in值数量有时并不相同,这会使得执行计划的缓存空间可能会被仅是in值数量不同的SQL占满,导致其他SQL的执行计划失效。解决这个问题的思路也比较简单,我们会用一个问号来替代in的列表,从而避免上述情况的发生。

测试

我们在规格为2×16C64G的节点上,针对一张分表数为64,分表记录数为百万的表在不同in值数量、不同并发下进行了测试,分区方式为哈希分区,测试结果如下。

测试场景1-1

不同并发,不同in值数量,开启IN查询动态裁剪能力,查看RT变化。
c1.jpg

测试场景1-2

不同并发,不同in值数量,关闭IN查询动态裁剪能力,查看RT变化。
c2.jpg

测试场景2-1

不同并发,不同in值数量,开启IN查询动态裁剪能力,查看吞吐变化。
c3.jpg

测试场景2-2

不同并发,不同in值数量,关闭IN查询动态裁剪能力,查看吞吐变化。
c4.jpg

测试结论

● 开启IN查询的动态裁剪能力后,吞吐和RT都有明显的改善。
● 对于RT比较敏感的客户,建议in值数量不要取的太多。

总结

本篇首先介绍了in查询的一个经典应用场景,接着分析了分布式数据库中执行in查询sql时naive的执行方式,其执行效率非常低下。为了提高执行效率,我们进行了分区裁剪与in值裁剪。进一步地,考虑到物理SQL中的in值太多会使得存储节点在执行物理SQL时有更大的可能进行全表扫描而非预期的索引扫描,因此当一个分片涉及的in值较多时,我们会将in值进行分割,分批次下发in查询。为了防止并发量过大给系统带来巨大的压力,下发物理SQL的默认并发度为机器CPU核数。此外,我们还针对in值数量不同可能导致计划缓存失效的问题进行了优化。最后,我们测试了打开与关闭in查询动态裁剪的情况下,吞吐量与延迟的变化,测试结果表明开启IN查询的动态裁剪能力后,吞吐和RT都有明显的改善。此外,我们建议对于RT比较敏感的客户,in值数量不要取的太多。

本文作者:越寒
更多好文,欢迎关注PolarDB-X知乎号

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PolarDB-X 1.0-用户指南-自定义控制指令-统计信息查询类语句
找出需调优的慢SQL后,先通过EXPLAIN查看执行计划,然后通过如下方法优化SQL:下推更多计算至存储层MySQL,适当增加索引,优化执行计划。
0 0
PolarDB-X 1.0-用户指南-SQL调优指南-SQL调优进阶-查询慢SQL明细
查询慢SQL明细 PolarDB-X将执行时间超过1秒的SQL定义为慢SQL,包括逻辑慢SQL和物理慢SQL。本文介绍这两种慢SQL的定义和查询方式。
0 0
PolarDB-X 1.0-用户指南-SQL调优指南-SQL调优进阶-查询执行器介绍
本文介绍PolarDB-X的SQL执行器如何执行 SQL中无法下推的部分。
0 0
PolarDB-X 1.0-用户指南-SQL调优指南-SQL调优进阶-查询改写与下推
下推是查询改写的一项重要优化,利用PolarDB-X的拆分信息来优化执行计划,使得算子尽量下推以达到提前过滤数据、减少网络传输、并行计算等目的。
0 0
PolarDB-X 1.0-用户指南-SQL调优指南-SQL调优进阶-查询执行器介绍
本文介绍PolarDB-X的SQL执行器如何执行 SQL中无法下推的部分。
0 0
PolarDB-X 1.0-SQL 手册-SHOW-统计信息查询
PolarDB-X提供以下语句用于查询实时统计信息。
0 0
PolarDB-X 1.0-最佳实践-在IN查询中如何选择Values个数
本文将介绍如何在PolarDB-X中做IN查询时,选择最佳的Values个数。
0 0
PolarDB-X迎来开源后首个重大版本升级,2.1版本新增5大特色功能
2022 年 5 月25日,阿里云开源 PolarDB-X 升级发布新版本!PolarDB-X 从 2009 年开始服务于阿里巴巴电商核心系统, 2015 年开始对外提供商业化服务,并于 2021 年10月正式开源。本次发布是开源后首个重大版本升级,重磅推出在稳定性、生态融合以及易用性上有了长足进步的 2.1 版本,该版本在内核能力上首次对齐商业版,新增 X-Paxos、自动分区、OSS 冷热数据分离等诸多重要特性,并在 MySQL 生态融合、K8S 生态融合方向持续迭代。
0 0
+关注
开源小秘书
开源数据库运营小编~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
PolarDB-X 2.0核心技术能力解读
立即下载
PolarDB-X 基于 X-Paxos 一致性协议的高可用
立即下载
PolarDB-X (开源版)从入门到实战
立即下载