DDL 最佳实践

本文档介绍了使用 YMatrix 建表的最佳实践。

0 命名规范

规范	是否必须
数据库名、表名、列名、函数名、视图名、序列号名、别名等务必使用小写字母、下划线、数字的组合，但首字母必须为小写字母，且不可以 `pg` 开头，对象名长度不超过 `63` 个字符	是
字段（或称作列）名称不得使用保留字段名称以及关键字，如 `oid`、`xmin`、`ctid`、`select`、`insert`、`update` 等均需避免	是
主键索引名称为 `pk_字段名`，唯一索引名称为 `uk_字段名`，普通索引名称为 `idx_字段名`	是
分区表应以父表名为前缀，并以子表规则特性作为后缀，如父表 `order`、子表 `order_p_202010`、`order_p_202009`	是
字段意为 `是否xx` 时，建议使用 `is_xxx` 的方式命名，类型选用布尔类型，如 `is_deleted`	否
数据库名与应用服务名称保持一致为最佳	否
表名命令需要表意清晰，不要使用含糊的缩写，也不应过分冗长，最好遵循 `业务名称_表的作用`，视图增加前缀，临时表以 `tmp` 做命名前缀。推荐：`trade_config`、`payment_task`。避免：`tradeconfig`、`paymenttask`。	否

1 行存储与列存储

存储模式	适用于
行存	数据频繁进行更新或插入操作需要同时访问一个表的多个字段
列存	字段较多但大部分查询访问的字段较少在少量字段上进行聚合操作表中单个字段会定期被更新而不修改其他字段对于大多数 OLAP 业务与时序场景，都建议优先使用列存储

2 存储引擎

YMatrix 提供 HEAP、AO、MARS2 及 MARS3 四种存储引擎。我们通常根据数据规模与写入性能需求来选择相应的存储引擎。

存储引擎	支持	适用于	独有功能
HEAP	行存	需要频繁更新的表选择行存储的表
AO	行存/列存	表和分区表在数据初始加载后更新不频繁，且仅以批处理方式插入数据	对接向量化执行引擎
MARS2	列存	高吞吐低延迟的时序场景	对接向量化执行引擎编码链压缩存储诊断（Datainspect）
MARS3	行列混存/列存	时序场景数据高频小批量写入的 OLTP 场景数据低频大批量写入的 OLAP 场景	对接向量化执行引擎编码链压缩自动降级存储

注意！
存储引擎更多介绍请见存储引擎概述。

3 数据类型

精准的数据类型对于存储空间、内存使用，以及查询效率都有很大帮助。类型选择有如下考虑：

最小化。对于整型或者浮点型，选择能表示具体范围的最小类型。比如年龄字段，使用 int2 而不是 int4/int8。
如使用向量化执行引擎，则在满足业务需求的前提下，率先使用更易激发出向量化性能潜力的类型，如原始类型 int，float 类型等。
如使用扩展数据类型 MXKV2，则需配合向量化执行引擎才会获得其特有的性能收益。

4 分布键

YMatrix 支持将数据分散到不同的节点上，利用分布式并行的能力提高性能。分布键主要考虑以下几点：

数据均匀。平均分布数据可以有效避免短板效应，均衡不同节点的负载。例如，可以使用能将数据在所有 Segment 上均匀分布的单个列作为分布键。即作为分布键的列，取值需要多而均匀，否则将会造成数据倾斜。特殊情况下可以考虑进行随机分布，以保证数据分布绝对均匀。数据分布情况查询语句如下：SELECT gp_segment_id,count(*) FROM table_name GROUP BY gp_segment_id;。
JOIN 便利。需要进行关联的大表则尽量使用关联的字段作为分布键，通过本地连接(Join)来显著提高性能，避免数据跨节点移动。
离散字段。尽可能选择表数据中值最离散的字段作为分布键，count(distinct 字段名) （即此字段不重值个数）值越大的字段越适合做分布键。
尽量不要采用查询的 WHERE 条件中使用的列作为分布键，而采用该列作为分区键。
在初始装载数据以及增量装载数据之后进行验证，减小数据倾斜。
不要采用日期或时间戳作为分布键。

5 分区设置

YMatrix 是分布式数据库，根据数据量创建不同的分区。分区设置主要需考虑几点：

建议为分区表设置自动化分区管理策略。
如有对象存储资源，且分区表为 MARS3 表，我们建议你为表开启自动化降级存储功能。
分区大小。时序场景一般建议按照年或者月分区，不建议按天分区。实际情况中，分区规划过细并不方便维护，也不利于性能：列存储下由于物理文件过多，反而起不到查询优化的效果。
分区预留。分区不需提前预留过多，在使用前预先创建即可。例如，如果是按月分区，每年年底时再创建下一年度的 12 个新分区即可。
分区维护代价。时序场景常以时间作为分区键，同时随着时间的推移，可以以分区为单位来备份和迁移。对于确认没有数据或没有有效数据的历史分区，要及时清理。详见日常清理。
只有能基于查询条件实现分区消除（分区剪枝）时才使用分区，优先选择范围分区，舍弃列表分区，不要在同一列上对表进行分布和分区。
减少使用默认分区，不要使用多级分区，优先创建较少的分区，让每个分区中有更多数据。

对 MARS2/MARS3 表进行分区维护操作时需要注意：

对分区进行 ALTER 操作需要锁整张表，和查询间会相互阻塞；
一次查询过多的分区，速度明显慢。因此对于 MARS2/MARS3 表来说，一般控制单个 Segment 上一个分区在 5GB 左右为宜。

对 HEAP/AOCO 表进行分区维护操作时需要注意：

对于 HEAP 表来说，一般控制单个 Segment 上一个分区在 5GB 左右为宜。同时需注意控制索引大小。
对于 AOCO 表来说，一般控制单个 Segment 上一个分区在 3GB 左右为宜。同时需注意控制索引大小。

注意！
以上分区大小推荐数值适用于 YMatrix 5.X。

6 块大小

可以在 MARS2/MARS3 表级指定 compress_threshold 来配置压缩阈值，适当调大该值，可以减少 I/O 次数，但也有可能降低块的筛选率。

7 压缩与非压缩

通常推荐压缩存储，特别是列存储表采用压缩存储的效果会更好。采用压缩存储，可以用空闲的 CPU 资源置换读写 I/O 时间，缓解 I/O 压力，提高查询效率。

如果你使用了 MARS2 或 MARS3 存储引擎建表：

对于压缩类型，YMatrix 推荐使用编码链压缩。它降低了盘上存储的数据量，减少了查询需要读取的数据量。
压缩也带来了解压的开销，因此查询调优可以优先考虑解压速度快的算法。YMatrix 自研的算法除 gorilla 外，解压速度一般都优于 LZ4，可以按数据特征而选用。

HEAP 不支持显式压缩。

8 索引

索引是一种加速查询的技术，索引数据一般独立存放，因此本小节内单独说明。

对于 HEAP 表和 AO 表而言，有 Btree、Brin、Bitmap、Hash 等索引方法，对于文本、空间等数据还有 Gin、Gist、Odin、Spgist、Toin 等索引方式。

MARS2 当前支持主键索引和 Brin 索引。前者就是排序键，也即 mars2_btree 指定的索引，它单独存储在指定的索引文件里。后者指对特定列建的 minmax 索引，存储在单独的元数据文件中。需要注意的是：

如排序键为一组列，请谨慎决定排序键排列顺序。列在排序键的位置越靠后，筛选效果越差，但局部排序仍然对于压缩有正面作用。例如需求是单设备点查询，那么排序键就是时序场景中的设备号 (vin)，如果需求是单设备在某时间段内明细查询、聚集查询或多设备查询，那么排序键通常就是设备号以及时间戳 (vin,daq_time)。
Brin 索引的选择和排序后的结果有很大关系，如果被排序列本身元组数量就比较小，经排序后有可能没有筛选效果，还会带来负面开销，需要剔除。

MARS3 当前支持 Brin 索引。可选择性创建一个或多个 mars3_brin 索引，以提高查询效率（作用相当于 MARS2 表的 minmax 索引）。

9 复制表

复制表的数据在每个 Segment 上都保存了一份，这样就减少了小表数据的跨分区移动和重分布数据。
大表不适宜使用复制表分布策略，因为将大表数据复制到每个节点上无论在存储还是维护上都是有代价的。
- 例如，某场景明细数据表有亿级数据，用户权限表却只有几百条数据，现在需要通过查询语句将该场景的明细数据表关联到用户权限表。如果直接将二者进行关联，则会衍生出千亿级别的数据。只有将用户权限表复制到每个 Segement 才能避免这样大消耗的情况。
复制表使用 DISTRIBUTED REPLICATED 参数设定。
除上述功能外，使用此策略还可以删除用户定义的函数可以对节点执行的操作的限制。

10 视图

如查询场景多出现聚集查询，我们建议你创建持续聚集视图。
如查询场景多含最近一段时间内数据的持续聚集运算需求，我们建议你创建滑动窗口视图。