全部产品
云市场

表设计最佳实践

更新时间:2019-09-04 11:27:16

选择维度表或者普通表

  • 维度表会在集群的每个节点存储一份数据,建议维度表的数据量不宜太大,每张维度表存储的数据不超过2万行。

  • 普通表也叫作分区表,是为充分利用分布式系统的查询能力而设计的一类表。普通表可存储的数据量通常比较大,可以存储千万条甚至上亿条数据。

选择合适的分布键

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

分布键的选择依据:

  • 尽可能选择参与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的日期格式做分区。

  1. # 直接用ds的值来做分区
  2. PARTITION BY VALUE(ds)
  3. # ds转换后的天做分区
  4. PARTITION BY VALUE(DATE_FORMAT(ds, '%Y%m%d'))
  5. # ds转换后的月做分区
  6. PARTITION BY VALUE(DATE_FORMAT(ds, '%Y%m'))
  7. # ds转换后的年做分区
  8. PARTITION BY VALUE(DATE_FORMAT(ds, '%Y'))

分区的选择依据:

  • 每个集群中分区数不超过102400,请提前规划好分区。

  • 充分利用分区,避免每个分区的数据量过小。例如,以天做分区时,如果每天的数据量很小,可考虑以月做分区。

选择合适的聚集索引

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

聚集索引的选择依据:

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

选择合适的主键

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

主键的选择依据:

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

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

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