表设计最佳实践

依据存储引擎的不同特性，你可以在不同的场景需求下灵活创建不同的表。我们给出以下示例。

1 MARS2 表

MARS2 表依赖 matrixts 扩展，在建表前，首先需要你在使用该存储引擎的数据库中创建扩展。

注意！
matrixts 扩展为数据库级别，一个数据库里面创建一次即可，无需重复创建。

CREATE EXTENSION matrixts;

建表时使用 USING MARS2 来指定存储引擎，基本建表示例如下。

CREATE TABLE mars2(
    time timestamp with time zone,
    tag_id int,
    i4 int4,
    i8 int8
)
USING MARS2;

我们延伸至以下车联网时序场景示例进行进一步说明。

CREATE TABLE vehicle_basic_data_mars2(
  daq_time timestamp  encoding (minmax),
  vin varchar(32)  COLLATE "C" encoding (minmax),
  lng float encoding (minmax),
  lat float encoding (minmax),
  speed float ,
  license_template varchar(16) ,
  flag integer
)
USING MARS2
WITH (compresstype=zstd, compresslevel=3)
DISTRIBUTED BY (vin)
PARTITION BY RANGE (daq_time)
( START ('2022-07-01 00:00:00') INCLUSIVE
  END ('2022-08-01 00:00:00') EXCLUSIVE
  EVERY (INTERVAL '1 day')
,default partition others);

创建 MARS2 表成功后，你必须额外创建一个 mars2_btree 类型的索引，这样才能进行正常的数据读写。使用索引排序的目的是使得同一维度或相似特性的数据尽可能的在物理上靠近，以减少 I/O 寻址的次数，提高查询效率。因此排序键的选择需要符合主要的业务查询特征。例如需求是单设备点查询，那么排序键就是时序场景中的设备号（vin)，如果需求是单设备在某时间段内明细查询、聚集查询或多设备查询，那么排序键就是设备号以及时间戳（vin,daq_time）。

CREATE INDEX idx_mars2 ON vehicle_basic_data_mars2 
USING mars2_btree(vin, daq_time) WITH(uniquemode = true);

结合上述示例表中的具体参数和语句，做最佳实践说明：

注意！
我们希冀你会根据我们的建议设计建表思路，但我们不希望你盲目套用。具体的时序场景千变万化，具体情况具体分析仍是非常必要的。

选择排序键
设计表时，就要考虑到排序键的选择。我们推荐你选择数据主体的标识字段，如果有时间戳可以加在标识字段前一起作为排序键。示例表中是 (vin, daq_time)。
排序键是索引的一部分，因此这类查询可以通过索引定位到存储位置，从而避免全表扫描。这是为了优化根据数据主体出发的查询，让数据按照数据主体的顺序存储。
同一个主体的数据是连续存放的，能够减少 I/O 数量，提升查询速度。
encoding (minmax)
这个选项的使用是为了提升计算能力。
首先，建议给所有排序键加上 minmax 选项，因为排序键都是有序的，在全表扫描时 minmax 过滤也能起到很好的过滤效果。
其次，如果你想要根据此字段进行 min、max、avg、sum、count 等聚集查询，或者进行 WHERE 条件过滤时需要加上这个选项。
这个选项的添加需要根据业务场景来确定，不要不加思考地添加至全部的字段，否则这种操作会带来很多数据写入的 CPU 以及磁盘 I/O 的消耗。
COLLATE "C"
此选项只在设备编码字段添加，其他字段无需添加。利用此操作，可以提高文本类型的排序速度和查询速度。
USING MARS2
是使用 MARS2 表的固定写法，不能改变内容及大小写方式。
WITH (compresstype=zstd, compresslevel=3)
指定压缩算法为 zstd，还有 rle_type，zlib，lz4 压缩算法，此表中推荐使用 zstd，压缩级别使用 3 级。此示例中使用了通用压缩算法，定制压缩方案请见下文“2 使用压缩”。
DISTRIBUTED BY (vin):
使用此语句选择分布键。
分布键决定数据如何分布到各个数据节点（segment），对性能影响很大。主要选择条件是：
（1）.尽量避免数据倾斜，使数据均匀分布，查询可以更充分地并行；
（2）涉及多表连接的情况，尽量在设计初期就使连接键与分布键保持一致，从而避免数据移动。
示例中我们使用设备编码字段作为分布键，从而可以查询同一个设备的的数据，并进行相关计算。避免了节点之间的数据重分布带来的性能损耗。
PARTITION BY RANGE (daq_time)
指定分区表的分区键。
我们推荐选择时间作为分区键，一方面配合自动分区管理可以自动淘汰数据，另一方面利用分区裁剪能力可以对不感兴趣的时间分区进行过滤。分区的大小，建议你控制在百万到千万数据行左右，过低会导致太多分区消耗内存，过高过滤效果不好，也不便于数据淘汰。
示例中我们使用设备采集数据的时间作为分区键。通常大量查询都是筛选数据采集的时间。例如，当你想要查询一天内的数据并进行相关的计算，你就需要加上过滤条件 WHERE daq_time >= CURRENT_DATE - INTERVAL '1 day' ，这样数据库就会快速的判断出数据在哪个分区子表上，从而快速的将数据定位并查询表出来。
( START ('2022-07-01 00:00:00') INCLUSIVE END ('2022-08-01 00:00:00') EXCLUSIVE EVERY (INTERVAL '1 days') ,default partition others);
此条SQL语句表明，你将使用START...END 语句以及INCLUSIVE、EXCLUSIVE关键字创建从 2022-07-01 零点开始到 2022-08-01 零点前截止的子分区表。
EVERY (INTERVAL '1 day')
子分区表的时间跨度为 1 天，除了以 day 为单位，你也完全可以使用 hour、month、year 等，根据数据量的规模来确定即可。例如，在一天的时间里，你的服务器接收到了高达 100 万~ 1000 万条数据，那天以“1 day”作为时间间隔就是最佳选择；如果一天的数据量只有十几万或几十万，那么选择“1 month”就很合适；如果每天的数据量不过万级，那么“1 year”子分区表生成一次就可以了。
default partition others
表示默认分区。数据的时间如果在其他分区里面找不到对应的子表存储，就会存储到这个分区里面。

注意！
当设备相同时间点数据分批上报时，MARS2 可以对相同时间（此表中为 daq_time 值）相同设备（此表中为 vin 值）的数据进行合并。合并特性需要在创建索引时手动指定 uniquemode=true，因为此选项的默认值是 false。例如，当你指定 uniquemode=true ，设备 'A01' 在 '2022-01-01 00:00:00' 这个时间传回来 3 条数据，最后会根据最后一条数据将原来的两条数据覆盖，只保留一条数据；但如果你默认了 uniquemode=false，那么设备 'A01' 在 '2022-01-01 00:00:00' 这个时间传回来的 3 条数据，最后会全部保留，不做任何处理。

2 HEAP 表 / AO 表

2.1 HEAP 表

HEAP 表为 YMatrix 默认存储引擎，因此如果你建表时没有特殊指定存储引擎，创建的则均为 HEAP 表。

CREATE TABLE disk_heap(
    time timestamp with time zone,
    tag_id int,
    read float,
    write float
)
DISTRIBUTED BY (tag_id);

2.2 AO 表

2.2.1 AORO 表

AORO 表即行存的 AO 表。行存是 AO 表的默认存储方式。
AORO 支持表级别的压缩，不支持列级别的压缩。

CREATE TABLE disk_aoro(
    time timestamp with time zone,
    tag_id int,
    read float,
    write float
)
WITH(appendonly=true,orientation=row,compresstype=zlib,compresslevel=4)
DISTRIBUTED BY (tag_id);

2.2.2 AOCO 表

AOCO 表即列存的 AO 表。
AOCO 即支持表级别的压缩，又支持列级别的压缩。

CREATE TABLE disk_aoco(
    time timestamp with time zone,
    tag_id int ENCODING(compresstype=zlib,compresslevel=5),
    read float,
    write float
)
WITH(appendonly=true,orientation=column,compresstype=zlib,compresslevel=6)
DISTRIBUTED BY (tag_id);

2.3 二选一最佳实践

确定使用 HEAP 还是 AO 表的最佳实践是：

为频繁进行更新、删除以及单个插入操作的表和分区使用 HEAP 存储引擎
为将收到并发更新、删除以及插入操作的表和分区使用 HEAP 存储引擎
为初始装载后就很少被更新并且后续只会以批操作执行插入的表和分区使用 AO 存储引擎。绝不要在 AO 表上执行单个更新、删除或者插入操作。并发的批量插入操作可以被执行但是绝不执行并发的批量更新或者删除操作
AO 表中被更新和删除的行所占用的空间不会像 HEAP 表那样被有效地回收及重用，因此 AO 存储引擎不适合于频繁更新的表。它的设计目标是用于一次装载、很少更新且频繁进行分析查询处理的大型表