本文给表的设计提出以下优化意见。

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

维度表和事实表的概念,请参见名称解释,事实表和维度表的指导原则如下:

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

选择合适的一级分区键

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

  • 尽可能选择被join的字段,比如是按照用户维度透视或者圈人,最好选择user_id字段。
  • 尽可能选择分布均匀的字段,比如交易ID,设备ID,用户ID等,或者选择自增列。

选择合适的二级分区键

二级分区通过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'))
            

指导原则如下:

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

选择合适的聚集索引

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

  • 查询某个必选字段,比如在电商应用中卖家透视平台,每个卖家只访问自己的数据,那么卖家ID就可以选择为聚集索引,可以保证数据的隔离性,进而使得性能有量级的提升。
  • 可以创建多个聚集索引,比如既要按照卖家ID频繁访问,又要按照品牌ID频繁访问,那么可以创建两个聚集索引。
  • 聚集索引会产生数据冗余,需要考虑存储成本的问题。

选择合适的主键

主键的作用有:

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

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

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

使用合适的列数据类型

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