全部产品
阿里云办公

8.8.1 表规划及设计实践

更新时间:2018-10-09 07:48:15

此章节通过特定业务场景举例说明ADS数据库表的设计和规划,如何根据业务场景设计ADS的表来存储数据,规划表的分区、主键、聚集列、(realtime/batch)及其他属性。重点从查询性能的角度考虑表的设计,实现千亿记录秒级查询的能力,充分利用ADS的高性能特性。ADS是分布式分析型数据库,面向大数据的实时OLAP业务场景,和传统数据仓库的表设计从性能角度考虑有一定的差异性。

1. 数据库对象

首先需要介绍ADS的几个表相关的概念,帮助我们理解更好的理解ADS的使用。如下图所示,ADS逻辑存储对象包括:数据库(Database)、表组(table group)、表(table),其中表可分为维度表和事实表。db-object | center | 454x308

  • 数据库(Database):是最高层的对象,按数据库进行资源的分配、隔离和管理,实现了多租户的管理能力。

  • 表组(table group):可发生关联的数据表的集合,通常可以按业务模块划分不同的表组。一个数据库可以包含多个表组。简单业务场景下一般规划一个表组即可。表组分为2种类型:维度表组、普通表组。

    • 维度表组:创建数据库时自动创建,有且只有一个,不可修改和删除,用于存放维度表。
    • 普通表组:或称事实表组,用来存放数据量较大的表,可以创建多个,有如下要求:
      • 同一个表组上所有表副本数一致,通常情况副本数为2个;
      • 同一个表组上所有表的一级分区(hash)数目必须一致,避免join时跨阶段数据迁移和数据倾斜;
      • 同一个表组上的表可以join,避免多个普通表组join,除了维度表组外;
      • 同一个表组可以共享一些配置参数,便于管理;

2.分布式逻辑存储

ADS存储层物理上采用行、列混存,支持大数据的分析查询,也满足详单查询业务。如下介绍ADS的逻辑分布策略。

2.1.事实表逻辑存储

ADS的事实表支持2级分区策略,将表数据分布到不同的节点,一级分区采用hash算法,二级分区采用list算法。如下图所示,事实表按ID进行一级分区,通过CRC32算法将不同ID值分布到不同的节点。二级分区采用按日期(bigint类型)进行分区—每天一个二级分区。reality-tbl | center | 704x380

2.2.维度表逻辑存储

维度表,采用复制的方式存储在每个节点上。

dimension-tbl | center | 704x382

3.智能索引

ADS自动为每列构建索引,用户无需手工为表创建任何索引。支持任意列的索引查询,非常适合用户任意列的自定义报表业务。

4.表结构优化

4.1.分区列选择

基本原理分析型数据库的表一级分区采用hash分区,可指定任意一列(不支持多列)作为分区列,然后采用以下标准CRC算法,计算出CRC值,并将CRC值模分区数,得出每条记录的分区号。空值的hash值与字符串”-1”相同。分析型数据库的调度模块会将同一个表组下所有表的相同分区分配在同一个计算节点上。好处在于,当出现多表使用分区列join时,单计算节点内部直接计算,避免跨机计算。分区列选择依据(按优先级高低排序):

  1. 如果有多个事实表(不包括维度表)进行join,选择参与join的列作为分区列。如果有多列join怎么办?可根据查询重要程度或者查询性能要求(例如某SQL的查询频率特别高),选择某列作为分区列,这样可以保证基于分区列join的查询性能具有较好的性能。
  2. 选择group by 或distinct 包含的列作为分区列。
  3. 选择值分布均匀的列,不要选择分区倾斜的列作为分区列。
  4. 常用SQL包含某列的等值或in查询条件,选择该列作为分区列。

例如:

select * from table where id=123 and ….;

select * from table where user in(1, 2,3);

4.2.一级分区键规避数据倾斜

对于大数据的表,如果一级分区键选择不合理,上线后遇到数据倾斜,带来诸多问题。本小节说明如何在选择一级分区键时进行必要的评估规避数据倾斜问题。

首先需要澄清数据倾斜程度,即与数据均匀度相差程度,足够引起ADS问题,如:SQL查询长尾、后台数据上线超时、单节点资源不足等问题。理想情况下,我们尽量选择既符合业务访问SQL要求,又能将数据均匀分布的列作为一级分区键。实际业务数据很难符合理想平均分布,ADS要求一定程度的均匀。这个“度“按如下方式进行评估:

有数据可统计,当表的数据量存储在其他系统上,如ODPS或其他数据源,通过select 分区列,count(*) from tabname group by 分区列; 查询是否存在明显的数据分布倾斜。另外一种情况,没有数据可以统计情况下,需要与业务相关人员进行讨论并获得相关信息。评估倾斜的几个指标,同时满足如下条件,需要考虑更换分区键:

  1. 一级分区列不同值的个数相比一级分区数(一般最大256)只是1-10倍关系
  2. 单个值记录数超过10万
  3. 最大值相比平均值的2倍以上

4.3.一级分区个数

基本原理分析型数据库的COMPUTENODE Local/Merge计算引擎(大部分查询主要的计算引擎),会在每个分区并行计算,每个分区计算使用一个线程,分区计算结果汇总到FRONTNODE。因此分区数过小,会导致并发低,单查询RT时间长。而如果分区数过多,会导致计算结果数过多,增加FRONTNODE压力;同时由于分区数过大,更容易产生长尾效应。因此需要根据资源配置和查询特点,选择合适的分区数。一级分区个数选择依据注意:一级分区数不可修改。如需修改,必须删表重建。

  • 参快速join的多个事实表分区数必须相同。
  • 单分区的数据记录数建议为300万条到2000万之间。如果为二级分区,保证每个一级分区下的二级分区的记录数为300万条到2000万条之间。
  • 分区数应该大于ECU数量 X 6,同时需要考虑到将来扩容。例如:某DB为8个C1,则分区数需要大于8*6=48。同时分区数要小于一定的值,容易出现的问题过多的分区数:2个ECU,设置了128个分区。
  • 单表一级分区数最大值为256。在某些极其特殊的环境中,可能最大值为512。
  • 单计算节点的分区数(包括二级分区)不能超过1万。

4.4. 二级分区

每个一级分区下会包含多个二级分区,二级分区主要是解决数据按固定时间周期(例如,天,周,月)增量数据,同时需要保留一定时间范围的历史数据设计的。二级分区采用list分区,二级分区列为表的一个bigint类型的列,通常为日期对应的bigint类型,如2017091210。不同值的个数即为二级分区数。

语法形式:

  1. SUBPARTITION BY LIST KEY (biz_date)
  2. SUBPARTITION OPTIONS (available_partition_num = 30)

其中biz_date为二级分区列,是一个bigint数据类型列,分区数为30个。

何时需要设计二级分区,一般情况利用二级分区实现一级分区数据量随时间增加增大到超过单个一级分区记录数最佳推荐最大值(一般2000万-3000万),同时可以利用二级分区实现历史数据的自动清除。二级分区可以理解为按队列方式管理分区个数,当超过最大定义数,最小值分区自动删除,循环使用空间。

在执行查询过程,计算引擎能够自动根据查询条件,筛选出满足的二级分级,然后对每个符合条件的二级分区执行计算。如果二级分区过多,由于每个二级分区作为独立查询单元,导致多次索引查询,性能下降;同时由于每个二级分区有独立的meta,因此会占用更大内存。如果二级分区较少的话,用户导入频率会降低,会影响数据的实时性。因此用户需要根据实际情况综合评估合理的二级分区更新间隔,以及保留个数。

最佳实践,单表二级分区数小于等于90,同时每个计算节点上总二级分区个数不超过1万个。每个一级分区下的二级分区包含的数据条数在300万到2000万之间。通常情况,假设单个分区每日增量数据超过300万,则推荐按天进行二级分区,如需要存储更长时间范围,可以按周、月进行规划。

4.5. 聚集列的选择和设置

基本原理,分析型数据库数据存储支持按一列或多列排序(先按第一列排序,第一列相同情况下,使用第二列排序),保证该列值相同或相近的数据在磁盘同一位置。它的好处是,当以聚集列为查询条件时,查询结果保存在磁盘相同位置,可以减少IO次数。由于主聚集列只有一列,因此需要最合适的列作为主聚集列。聚集列选择依据

  • 主要或大多数查询条件包括这一列,且该条件具有较高的筛选率。
  • Join 等值条件列(通常为一级分区列)作为聚集列。

示例:

有如下表,一级分区键为org_code,同时设置了聚集列:CLUSTERED BY(org_code)。可以通过DMS管理工具修改表的聚集列,realtime类型的表修改后,对于新insert数据,在optimize后实际生效。

  1. CREATE TABLE t_fact_mail_status (
  2. mail_id varchar COMMENT '',
  3. scan_timestamp timestamp COMMENT '',
  4. biz_date bigint COMMENT '',
  5. org_code varchar COMMENT '',
  6. org_name varchar COMMENT '',
  7. dlv_person_name varchar COMMENT '',
  8. receiver_name varchar COMMENT '',
  9. receiver_phone varchar COMMENT '',
  10. receiver_addr varchar COMMENT '',
  11. product_no varchar COMMENT '',
  12. mag_no varchar COMMENT '',
  13. PRIMARY KEY (mail_id,org_code,biz_date)
  14. )
  15. PARTITION BY HASH KEY (org_code) PARTITION NUM 128
  16. SUBPARTITION BY LIST KEY (biz_date)
  17. SUBPARTITION OPTIONS (available_partition_num = 30)
  18. CLUSTERED BY (org_code)
  19. TABLEGROUP ads_demo
  20. OPTIONS (UPDATETYPE='realtime')
  21. COMMENT '';

效果上,当设置CLUSTERED BY(org_code)后,相同org_code值的记录首先按一级分区规则分布在某一个计算节点CN上,同时通过聚集列设置,相同org_code记录将尽可能的存储在同一个数据块上。如下SQL的访问IO将大大减少数百倍。

  1. select mail_id,biz_date,org_code,org_name
  2. from t_fact_mail_status
  3. where org_code='203202'
  4. and bz_date=20171221;

假设总共有10万个不同的org_code,每个org_code每天有100—5000不等记录,假设org_code=203202的记录为1000条,则通过如上一级分区方案和聚集列,则这1000条记录存储在连续的几个数据块上,如上SQL只需扫描几个数据块。

4.6. 列类型选择

基本原理ADS处理数值类型的性能远好于处理字符串类型。原因在于:

  • 值类型定长,占用内存少,存储空间小;
  • 数值类型计算更快,尤其是join时;

因此,强烈建议用户尽可能使用数值类型,减少使用字符串类型。常见将字符串转换为数值类型方法:

  • 包含字符前缀或后缀,例如E12345,E12346等。可以直接去掉前缀或者将前缀映射为数字。
  • 该列只有少数几个值,例如国家名。可以对每个国家编码,每个国家对应一个唯一数字。
ADS数据类型 MySQL数据类型 差异
boolean bool,boolean 一致
tinyint tinyint 一致
smallint smallint 一致
int int,integer 一致
bigint bigint 一致
float float[(m,d)] 分析型数据库不支持自定义m和d,而mysql可以
double double[(m,d)] 分析型数据库不支持自定义m和d,而mysql可以
varchar varchar 一致
date date 一致
timestamp timestamp 分析型数据库只支持到精确到毫秒,而mysql是可以定经度的
multivalue - 分析型数据库特有的,MYSQL无此类型

5.主键

ADS的realtime类型的表必须包含主键字段,ADS支持realtime表insert/delete,通过主键进行相同记录的判断,确定唯一记录。主键组成:(业务id+一级分区键+二级分区键),有些情况,业务id与一级分区相同。对于记录量特别大的表,从存储空间和insert性能考虑,一定要减少主键的字段数。

6. 典型业务场景

6.1. 电子商务

简化的业务场景有3个表:客户信息表、订单表、商品类型表(维度表)。业务场景要求:统计不同客户群体,在不同时间段的统计数据,rt<1分钟, qps,偶尔查询。

表的逻辑设计

客户信息表—t_fact_customers

数据量在5亿客户。

字段名 数据类型 说明
customer_id varchar  客户编号
customer_name varchar 客户姓名
phone_number varchar 电话
address varchar 地址
last_login_time timestamp 最近登录时间
age int 年龄

订单表—t_fact_orders

数据量:每日订单量5000万,存储3年的数据,总数据量为550亿左右。

字段名 数据类型 说明
order_id varchar 订单编号
customer_id varchar 客户编号
goods_id bigint 商品编号
numbers bigint 数量
total_price double 总价
order_time timestamp 订单时间
order_date bigint 订单日期—二级分区

商品类型表—t_dim_goods

商品总数在100万左右

字段名 数据类型 说明
goods_id bigint 商品id
price double 单价
class bigint 类型
name varchar 名称
update_time timestamp 更新时间

 表的物理设计

 物理设计的目的是获得最佳性能,在进行表的物理设计前,必须了解表的查询SQL,才能决策表的最佳分片策略。这里假设大部分查询都需要关联查询客户表+订单表,那么我们优先考虑给予customer_id进行一级分区,每个节点的计算都是本地数据。

首先创建表组ads_demo ,2个副本,SQL查询超时为30秒。

  1. create tablegroup ads_demo options(minRedundancy=2 executeTimeout=30000);
  1. CREATE TABLE t_fact_customers (
  2. customer_id varchar COMMENT '',
  3. customer_name varchar COMMENT '',
  4. phone_number varchar COMMENT '',
  5. address varchar COMMENT '',
  6. last_login_time timestamp COMMENT '',
  7. age int COMMENT '',
  8. PRIMARY KEY (customer_id)
  9. )
  10. PARTITION BY HASH KEY (customer_id) PARTITION NUM 128
  11. TABLEGROUP ads_demo
  12. OPTIONS (UPDATETYPE='realtime')
  13. COMMENT ''

t_fact_customers表,每个分区数据量 781万 = 5亿/(128节点/2副本)

  1. CREATE TABLE t_fact_orders (
  2. order_id varchar COMMENT '',
  3. customer_id varchar COMMENT '',
  4. goods_id bigint COMMENT '',
  5. numbers bigint COMMENT '',
  6. total_price double COMMENT '',
  7. order_time timestamp COMMENT '',
  8. order_date bigint COMMENT '',
  9. PRIMARY KEY (order_id,customer_id,order_date)
  10. )
  11. PARTITION BY HASH KEY (customer_id) PARTITION NUM 128
  12. SUBPARTITION BY LIST KEY (order_date)
  13. SUBPARTITION OPTIONS (available_partition_num = 90)
  14. TABLEGROUP ads_demo
  15. OPTIONS (UPDATETYPE='realtime')
  16. COMMENT '';

t_fact_orders表,二级分区键order_date为bigint数据类型,根据数据量和存储总时间,按月(201712)间隔,一个月1个二级分区,每个二级分区数据量为:2387万 = 550亿/(128节点/2副本)/(3年*12个月)。

  1. CREATE DIMENSION TABLE t_dim_goods (
  2. goods_id bigint comment '',
  3. price double comment '',
  4. class bigint comment '',
  5. name VARCHAR comment '',
  6. update_time TIMESTAMP comment '',
  7. primary key (goods_id)
  8. )
  9. OPTIONS (UPDATETYPE='realtime')
  10. ;

指导原则:

  • 同一个表组下所有的事实表采用相同数量的一级分区数;
  • 一级分区键的选择需要考虑表的关联及数据能均衡分布;
  • 二级分区根据数据存储时间范围的需要规划间隔,要求创建一个bigint类型列;
  • 每个二级分区的数据量控制在2000万左右;

查询SQL: 统计一段时间内,按年龄段统计订单的客户数量、订单销售总额。

  1. /* +engine=MPP */
  2. SELECT
  3. case when cus.age<=30 then '<20' when cus.age>20
  4. and cus.age<=30 then '20-30' when cus.age>30
  5. and cus.age<=40 then '30-40' else '>40' end as age_range,
  6. COUNT(distinct cus.customer_id),
  7. SUM(total_price)
  8. FROM
  9. t_fact_customers cus left join t_fact_orders ord
  10. on cus.customer_id=ord.customer_id
  11. where ord.order_time>='2017-10-01 00:00:00' and ord.order_time<'2017-11-01 00:00:00'
  12. AND ord.order_date=201710
  13. group by age_range;

提示:需要增加二级分区条件可以进行二级分区裁剪,提高查询效率。

6.2.物流快递

 简化业务场景:邮件包裹状态事实监控,包裹从订单、收单、装车、…、投递分为不同的状态,实时更新状态。业务需要:每个机构实时统计当前不同处理状态邮件数量。业务性能要求:

  • 实时新增数据实时统计,1秒以内返回—RT<1s;
  • 查询并发量:qps> 300

表的逻辑设计

邮件状态表—t_fact_mail_status

单日数据量 1亿记录,有 15万机构或者快递员,每个机构的邮件量为—100到3000不等,存储30天的数据。为满足高QPS,从设计上采用大宽表,冗余字段,避免表join

字段名 字段类型 说明
mail_id varchar 邮件订单唯一码
scan_timestamp timestamp 收件时间
biz_date bigint 日期—二级分区
org_code varchar 机构编码
org_name varchar 机构名称
dlv_person_name varchar 投递员名称
receiver_name varchar  收件人名
receiver_phone varchar 收件人电话
receiver_addr varchar 收件人地址
product_no varchar 产品编码
mag_no varchar 包裹编号
op_1_timestamp bigint 状态1操作时间
op_2_timestamp bigint 状态2操作时间
op_3_timestamp bigint 状态3操作时间
op_4_timestamp bigint 状态4操作时间
op_5_timestamp bigint 状态5操作时间

 表的物理设计

规划表的一级分区、二级分区,主要的业务查询按机构查询,要求高qps。选择按org_code一级分区。

  1. CREATE TABLE t_fact_mail_status (
  2. mail_id varchar COMMENT '',
  3. scan_timestamp timestamp COMMENT '',
  4. biz_date bigint COMMENT '',
  5. org_code varchar COMMENT '',
  6. org_name varchar COMMENT '',
  7. dlv_person_name varchar COMMENT '',
  8. receiver_name varchar COMMENT '',
  9. receiver_phone varchar COMMENT '',
  10. receiver_addr varchar COMMENT '',
  11. product_no varchar COMMENT '',
  12. mag_no varchar COMMENT '',
  13. op_1_timestamp bigint COMMENT '',
  14. op_2_timestamp bigint COMMENT '',
  15. op_3_timestamp bigint COMMENT '',
  16. op_4_timestamp bigint COMMENT '',
  17. op_5_timestamp bigint COMMENT '',
  18. PRIMARY KEY (mail_id,org_code,biz_date)
  19. )
  20. PARTITION BY HASH KEY (org_code) PARTITION NUM 128
  21. SUBPARTITION BY LIST KEY (biz_date)
  22. SUBPARTITION OPTIONS (available_partition_num = 30)
  23. TABLEGROUP ads_demo
  24. OPTIONS (UPDATETYPE='realtime')
  25. COMMENT '';

 备注:

  • 按机构号一级分区,每个分区数据量:156万= 1亿/(128节点/2副本)

  • 二级分区 biz_date 每天一个分区,存储30天

查询SQL: 查询当天某机构号,不同状态的邮件数据量,核查需要操作的邮件量,要求rt<1s,高QPS。

必须利用分区裁剪,单个查询在不同的节点上运行。

  1. select
  2. sum( case when t.op_1_timestamp >= 20171128000000 and t.op_1_timestamp <= 20171128235900
  3. and(( t.op_2_timestamp is null or t.op_2_timestamp > 20171128235900 or t.op_2_timestamp < 20171128000000 )
  4. and( t.op_3_timestamp is null or t.op_3_timestamp > 20171128235900 or t.op_3_timestamp < 20171128000000 )
  5. and( t.op_4_timestamp is null or t.op_4_timestamp > 20171128235900 or t.op_4_timestamp < 20171128000000 )) then 1 else 0 end ) as cn
  6. from
  7. t_fact_mail_status t
  8. where
  9. t.org_code = '21111101'
  10. and t.biz_date = 20171128

小技巧:当where条件可以比较精确的返回结果集,通过sum(case when),替换count(*) where …,减少了列扫描的次数和复杂度。