AnalyticDB(原ADS)分区列的最佳实践-阿里云开发者社区

开发者社区> 吴永栋> 正文

AnalyticDB(原ADS)分区列的最佳实践

简介: AnalyticDB 是阿里巴巴自研的在线数据库。 本文会对分区的概念进行解释,并帮助用户选择合适的分区,并尽可能修改查询来更加合理的使用分区从而提高查询性能。
+关注继续查看

AnalyticDB(简称ADS,以下ADS均代表AnalyticDB) 是阿里巴巴自研的一款面向互联网,高并发的在线数据库。目前已经广泛应用于阿里巴巴集团内部,并在阿里公有云上进行售卖,同时也可以通过阿里专有云的形式向大客户提供高效的计算分析能力。

由于ADS产品设计的目的就是要解决PB级,千万级到万亿记录的复杂查询性能。对一些传统数据库的用户来说有些概念有很大不同,需要理解其背后含义才能充分发挥ADS的最强性能。在所有的名词中分区可能是较难理解,同时又对ADS性能影响最大的一个关键点。本文会对分区的概念进行解释,并帮助用户选择合适的分区,并尽可能修改查询来更加合理的使用分区从而提高查询性能。

ADS的分区概念

如下图所示,LocalNode是ADS处理数据的计算节点。

_001_jpeg

上图中假设这个Database有m个LocalNode,表有l个分区,这个表有n列。并且这个表设计了2级分区,一共保存了90天的二级分区,每个二级分区保存一天的数据。可以看出每个LocalNode会处理 [l/m] 或者 [l/m]+1 个一级分区,每个一级分区都会保存90个二级分区的数据。每个二级分区内都保存这个表在这一天的所有列(col1,col2......coln)。

ADS的一级分区最大的目的是将数据均匀分散,这样可以充分发挥ADS分布式计算的能力,一级分区是HASH分区。而二级分区更像传统数据库的分区,目的是将数据分散到不同的存储文件上。

ADS的分区选择

ADS的一级分区列选择

如上图所示每个Database在创建的时候都会根据用户选择自动创建多个LocalNode来并发的处理数据。举个例子,如果我们创建了一个database,这个database创建的时候选择有8个ECU,对应会有8个LocalNode在同时提供服务。如果我们现在有一张订单表(orders)有8000万条记录。表的结构如下:

create table orders(
 order_id long,
 store_id long,
 order_total float,
 order_time timestamp,
 order_comment varchar,
 ...
 )
 partition by hash key(order_id)
 partition number(256);

那如何将这8000万条记录分到这8个LocalNode去处理呢? ADS使用分区列来决定怎么将8000万条记录分配到8个LocalNode,也就是上图ddl中的partition by hash key(order_id)中选择的order_id作为分区列。ADS会对order_id 进行函数计算来决定分配到哪个分区,ADS内部同时又会对256个分区进行计算来决定是哪个LocalNode去处理哪个分区。

这里简单起见我们假设所有的算法都是用取模来完成,也就是说每个LocalNode会处理(256/8)=32个分区。

如果我们假设order_id没有重复的,也就是说count(distinct order_id)=8000万,这样每个分区的记录数大约为 80,000,000/256= 31,250条记录。 根据上图所示可知,每个LocalNode处理 31,250*32 =10,000,000条记录。这样看来似乎非常简单,但这里有个最关键的概念就是数据平均分配,而且我们假设order_id是没有重复值的,但这样的情况在现实中不一定完全满足。

如果我们的查询有90%的是按照store_id进行统计分析,那这时候用order_id作为分区列就不一定是最佳选择。如果我们打算用store_id来作为分区列,我们根据经验也知道store_id在订单系统中不可能每条记录都是唯一的,也就是说


count(distct store_id) <> count(*) 

但是我们还是计划使用store_id来作为分区列,那如何在数据导入ADS前就知道是否存在数据倾斜呢?如果原始数据在odps或者oracle等其他数据库里的话,可以使用如下sql:

select count(distinct store_id),count(*) as total_cnt from orders ;
select store_id,count(*) as top_cnt from orders group by store_id order by cnt desc limit 1;

根据上述两个SQL的查询结果,有下面几种情况:

  1. 如果上面第一个查询里store_id大于分区数目,并且第二个查询里top_cnt小于 total_cnt/分区数(80,000,000/256) 31250条记录的话,可以大概率认为这个数据是没有倾斜的,store_id可以作为分区列。
  2. 如果上面第一个查询里store_id 数目小于分区列的数目,那store_id不适合作为分区列,必须使用其它列。
  3. 如果第一个查询store_id大于分区数目,但是第二个查询store_id对应的记录数大于 31250,数据会有倾斜,这时候必须先把数据导入到ADS,并尝试执行查询,如果效率还可以接受,并且数据倾斜也在可以接受范围内的话才可以继续使用,但从长远来看尽量不建议这样选择分区列。

ADS的二级分区列选择

二级分区列的选择相对一级分区列较为简单,我们推荐尽量选择和时间有关的列来作为二级分区列,因为二级分区列有一个最大的特点是会按照数量滚动删除。和上面的例子相同,我们修改为增加二级分区列,增加ds这个列,按照每天的交易数据把数据归并到某个二级分区里。

create table orders(
 order_id long,
 store_id long,
 order_total float,
 order_time timestamp,
 order_comment varchar,
 ...
 )
 partition by hash key(store_id)
 partition number(256)
 SUBPARTITION BY LIST KEY (ds bigint)
 SUBPARTITION OPTIONS (available_partition_num = 90);

这里的二级分区列我们使用交易日期ds,按照上图所示available_partition_num = 90,也就是说我们会保存90天的历史数据,当第91天的记录进入到ADS后,ADS会自动把91天前的记录删除,这样的设计可以极大的简化DBA的日常运维工作。

常见的分区设计错误

  1. 分区数目过少,小于LocalNode的数目,如上图所示,如果分区数目过少,可能会出现有的LocalNode没有分区需要处理。假设分区数目为8个,而LocalNode的个数为16个,那必然会出现有8个LocalNode没有任何数据需要处理,这时候资源会出现浪费现象。
  2. 分区数目不是LocalNode的倍数,如上图所示,如果某个表的分区数目是100个,而LocalNode的个数是64个,那么必然会出现有36个LocalNode处理2个分区,而另外28个分区需要处理1个分区。这时候的查询有很可能会出现处理36个分区的LocalNode成为慢节点。
  3. 二级分区数目过多,例如有的表每天只有30w的数据,但是需要存储1年的数据,这时候如果每天的记录都作为二级分区会导致查询效率降低。最佳选择是每个月做一个二级分区,这样每个二级分区大概有900w数据,一共有12个二级分区。
  4. 避免数据倾斜是分区的最主要目的,在数据均匀分布的情况下尽可能将查询中最常使用的谓词列或者多表Join的时候JOIN的列作为分区列可以极大提高查询效率。

查询语句中使用分区列的最佳实践

在建表语句确定以后,我们在查询语句中如何使用分区列来加速我们的查询呢?下面是几个最佳实践

在查询的谓词中尽可能添加一级分区列

假设我们的order_id和store_id是有对应关系的,例如order_id前5位就是store_id的,如果从开发角度,我们可以直接写

select * from orders where order_id=99999111;
select * from orders where order_id=88888222;

这样的查询,但如果想提高sql的查询性能,最好使用

select * from orders where order_id=99999111 and store_id=99999;
select * from orders where order_id=88888222 and store_id=88888;

在查询语句的谓词中尽可能包含二级分区列的查询条件

如果我们要查询所有商店的某一天时间段的交易数据,我们可以直接写

select store_id,sum(order_total) from orders where order_time <'2017-11-10 18:00:00' and order_time > '2017-11-10 08:00:00' group by store_id;

这样的查询,但这样的查询会导致ADS在所有的二级分区都进行一遍检索。所以如果想提高sql的查询性能,可以使用下面这样的SQL:

select store_id,sum(order_total) from orders where ds=20171110 and order_time <'2017-11-10 18:00:00' and order_time > '2017-11-10 08:00:00' group by store_id;

这样ADS可以只在20171110这个分区去做检索,提高查询效率。

结语

为了充分发挥并行计算的特点,ADS设计了一级分区列的概念,一级分区列的首要选择是避免数据倾斜。其次要尽量选择会参与谓词运算的列。同时为了减少DBA的负担,ADS又设计了二级分区的概念,二级分区列尽量按照时间去选择,二级分区列尽量也参与到谓词运算同样可以提高查询效率。

根据上文的介绍,相信用户一定能对ADS的分区概念有更深刻的理解,只要用户能设计较好的分区列,并对SQL进行简单改造,就可以体验百亿级记录表在秒级或者毫秒级返回查询结果的震撼效果,更好的帮助分析人员对业务进行快速查询,从数据中更快更多的发掘真正的业务价值。

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

相关文章
ADB日志分析最佳实践
## 背景 利用服务器日志做分析是很多公司进入大数据分析的第一步,也是很关键的一步。大部分情况下,这些公司在考虑进行大数据分析的时候,都会遇到以下问题: * 团队里面缺乏了解大数据技术栈的工程师 * 都听过Hadoop,想要学习Hadoop,但是不知道从何入手 * 从市面上寻找大数据人才效果不理想 * 不愿意一下子投入过多的资金去组建一个专门的大数据团队 虽然Hadoop没
1482 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
2184 0
广播的最佳实践——实现强制下线功能(Android_Broadcast)
 关键词:Broadcast, 生命周期,继承 本项目基本思路: 1.先创建一个ActivityCollector类用于管理所有的活动; 2.然后创建一个BaseActivity类作为所有活动的父类: 父类功能有二: 其一,用于辅助统筹管理本项目的所有活动。
581 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
3961 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
5689 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
10718 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
3810 0
+关注
吴永栋
ADS
1
文章
0
问答
来源圈子
更多
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载