表设计最佳实践
选择维度表或者普通表
维度表会在集群的每个节点存储一份数据,建议维度表的数据量不宜太大,每张维度表存储的数据不超过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
的日期格式做分区。
# 直接用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'))
分区的选择依据:
每个集群中分区数不超过102400,请提前规划好分区。
充分利用分区,避免每个分区的数据量过小。例如,以天做分区时,如果每天的数据量很小,可考虑以月做分区。
选择合适的聚集索引
聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序,每个表仅支持创建一个聚集索引。
聚集索引的选择依据:
查询一定会携带的字段可以作为聚集索引。例如,电商卖家透视平台中每个卖家只访问自己的数据,卖家ID可以定义为聚集索引,保证数据的局部性,提升数据查询性能。
选择合适的主键
在表中定义主键可以去除重复数据,只有定义过主键的表支持数据更新操作(DELETE和UPDATE)。
主键的选择依据:
尽可能选择单数字类型字段作为主键,表的性能相对更好。
AnalyticDB for MySQL支持将字符串或者多字段组合作为主键。
主键中必须包含分布键和分区键。
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论