7.1 表设计最佳实践

1. 选择合适的事实表和维度表

维度表和事实表的概念,请参考3.1节的基本概念, 事实表和维度表的指导原则如下:

  • 维度表的大小通常不超过1w行
  • 事实表的大小通常比较大,比如千万、亿等

2. 选择合适的一级分区键

一级分区键通过distribute by来指定,指导原则如下:

  • 选择尽可能选择被join的字段,比如是按照用户维度透视或者圈人,按选择user_id再合适不过了
  • 选择尽可能分布均匀的字段,比如交易id,设备id,用户id等,或者选择自增列

3. 选择合适的二级分区键

二级分区通过partition by 来指定,如:

# 直接用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'))

指导原则如下:

  • 一个database能承载的最大二级分区数目是有限的,当前限制是10240。请提前规划后这个db的所有表二级分区键
  • 尽量充分利用二级分区,不要让每个二级分区的数据量过小,如:你使用天做二级分区,但是每天数据量很小,这时可考虑用月来做二级分区

4. 选择合适的聚集索引

聚集索会根据表的某个字段排序建索引,适合的场景有:

  • 查询一定会带的某个字段,比如电商中卖家透视平台,每个卖家只访问自己的数据,那卖家id就是可以选择为聚集索引,可以保证数据的locality,进而性能有量级的提升。
  • 聚集索引可以建多个,比如既要按照卖家id频繁访问,又要按照品牌id频繁访问,那可以建2个聚集索引
  • 聚集索引会有数据冗余,要适当考虑成本问题。

5. 选择合适的主键

主键的作用有:

  • 数据消重(Replace into)
  • 数据更新操作(Delete、Update)

主键的选择通常有如下几个原则:

  • 尽可能选择单数字类型字段,相对性能比较好;当然字符串或者多字段组合主键也是支持的。
  • 主键必须包含一级分区键(如果是事实表)
  • 主键必须包含二级分区键 (如果表有二级分区的话)

6. 使用尽可能小的列大小

列类型要尽可能选择匹配的列,比如性别就可以用boolean或者byte类型,交易笔数不大的可以用int等等

results matching ""

    No results matching ""