本文介绍云原生数据仓库AnalyticDB MySQL版(简称ADB,原分析型数据库MySQL版)中如何选择表类型、分布键、分区键、主键以及聚簇,使表的性能达到最优。

选择维度表或者普通表

  • 维度表会在集群的每个节点存储一份数据,建议维度表的数据量不宜太大,每张维度表存储的数据不超过2万行。
  • 普通表也叫作分区表,是为充分利用分布式系统的查询能力而设计的一类表。普通表可存储的数据量通常比较大,可以存储千万条甚至上亿条数据。

选择合适的分布键

ADB中创建普通表时,默认需要通过DISTRIBUTED BY HASH(column_name,...)指定分布键,按照column_name的HASH值进行分区。ADB支持将多个字段作为分布键。

分布键的选择依据:

  • 尽可能选择参与JOIN的字段作为分布键,例如按照用户维度透视或者圈人,可以选择user_id作为分布键。
  • 尽可能选择值分布均匀的字段作为分布键,例如交易ID、设备ID、用户ID或者自增列作为分布键。

选择合适的分区键

如果业务明确有增量数据导入需求,创建普通表时可以同时指定分布键和分区,分区可以实现数据的增量同步。

创建普通表时,通过PARTITION BY {VALUE(column_name) | VALUE(date_format(column_name, ?)}指定分区。

例如,PARTITION BY VALUE(column_name)表示使用column_name的值做分区,PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y%m%d'))表示将column_name格式化为类似20190101的日期格式做分区。

# 直接用ds的值来做分区
PARTITION BY VALUE(ds)
# ds转换后的天做分区
PARTITION BY VALUE(DATE_FORMAT(ds, '%Y%m%d'))
# ds转换后的月做分区
PARTITION BY VALUE(DATE_FORMAT(ds, '%Y%m'))
# ds转换后的年做分区
PARTITION BY VALUE(DATE_FORMAT(ds, '%Y'))

分区的选择依据:

  • 当数据量较大时,二级分区的选择至关重要,如果数据量大的表中没有二级分区或者二级分区切分不合理,将严重影响ADB的性能。
  • 创建表时通过PARTITION BY Value(column_name | date_format)定义二级分区,数据将按照指定方式进行切分,目前切分粒度只支持年、月、日以及原始值。切分
粒度太大或太小都会影响查询性能和写入性能,甚至影响ADB的稳定性。
  • 二级分区粒度的选择原则为一个二级分区的数据量在3亿~10亿之间视为合理切分原则,如果小于3亿,表示切分粒度太小,可以增大切分粒度(例如将切分粒度由日改为月);如果大于10亿,表示切分粒度太大,可以减小切分粒度(例如将切分粒度由月改为日)。
  • 尽量使二级分区维持静态状态,不建议频繁更新二级分区,例如如果有每天频繁更新多个历史二级分区场景,应考虑使用的二级分区字段是否合理。

聚簇选择

聚簇中键值的逻辑顺序决定了表中相应行的物理顺序,每个表仅支持创建一个聚簇。

聚簇键的选择依据:

查询一定会携带的字段可以作为聚簇键。例如,电商卖家透视平台中每个卖家只访问自己的数据,卖家ID可以定义为聚集索引,保证数据的局部性,提升数据查询性能。

选择合适的主键

在表中定义主键可以去除重复数据,只有定义过主键的表支持数据更新操作(DELETE和UPDATE)。

主键的选择依据:

  • 尽可能选择单数字类型字段作为主键,表的性能相对更好。

    ADB支持将字符串或者多字段组合作为主键。

  • 主键中必须包含分布键和分区键。