表设计优化建议

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

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

• 维度表的大小通常不超过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）。