表设计最佳实践

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

MARS3
MARS2
HEAP
AO

1 MARS3 表

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

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

=# CREATE EXTENSION matrixts;

建表时使用 USING MARS3 来指定存储引擎，并使用 ORDER BY 来指定排序键，基本建表示例如下：

=# CREATE TABLE mars3(
    time timestamp with time zone,
    tag_id int,
    i4 int4,
    i8 int8
)
USING MARS3 ORDER BY (tag_id, time);

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

=# CREATE TABLE vehicle_basic_data_mars3(
  daq_time timestamp ,
  vin varchar(32)  COLLATE "C" ,
  lng float ,
  lat float ,
  speed float ,
  license_template varchar(16) ,
  flag integer
)
USING MARS3
WITH (compresstype=zstd, compresslevel=3,compress_threshold=1200,
        mars3options='rowstore_size=64')
DISTRIBUTED BY (vin)
ORDER BY (vin, daq_time)
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);

创建 MARS3 表成功后，你可以选择在排序键上建立一个 mars3_brin 索引。mars3_brin 是一个稀疏索引，对磁盘空间和插入性能影响极小，结合排序键的有序特性，可以达到对排序键的极致查询效率。

在非排序键同样也可以创建 mars3_brin 索引，但是因为没有有序性，查询性能会有一些折扣，可以按需添加。

=# CREATE INDEX idx_mars3 ON vehicle_basic_data_mars3 USING mars3_brin(vin, daq_time);

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

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

选择排序键

设计表时，就要考虑到排序键的选择。排序键的目的是使得同一时间维度或相似属性的数据尽可能的在物理上靠近，以减少 I/O 寻址的次数，提高查询效率。因此排序键的选择需要符合主要的业务查询特征。例如需求是单设备点查询，那么排序键就是时序场景中的设备号（vin），如果需求是单设备在某时间段内明细查询、聚集查询或多设备查询，那么排序键就是设备号以及时间戳（vin,daq_time）。

我们推荐你选择数据主体的标识字段，如果有时间戳可以加在标识字段前一起作为排序键。示例表中是 (vin, daq_time)。

COLLATE "C"

此选项只在设备编码字段添加，其他字段无需添加。利用此操作，可以提高文本类型的排序速度和查询速度。

USING MARS3

是使用 MARS3 表的固定写法，不能改变内容。

WITH (compresstype=zstd, compresslevel=3, mars3options='compress_threshold=1200, rowstore_size=64')

此表中推荐使用 zstd，压缩级别使用 3 级。此示例中使用了通用压缩算法，定制压缩方案请见使用压缩。

此表中 L0 Run 大小为 64MB；压缩阈值为 1200。

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

表示默认分区。数据的时间如果在其他分区里面找不到对应的子表存储，就会存储到这个分区里面。

使用 VACUUM/VACUUM FULL 实现极致查询性能

执行 VACUUM/VACUUM FULL 的作用：可以把无序的行存 Run 转换为有序的列存 Run，而 VACUUM FULL 除去上面 VACUUM 做的事情，还会把多个 Run 合并成一个 Run，从而达到更极致的查询性能。但 VACUUM FULL 要求在其工作的表上得到一个排他锁，因此无法和对此表的其他使用并行。

执行 VACUUM/VACUUM FULL 的时间：如果在持续写入数据到 MARS3 表的过程中，则不需关心清理动作。当表内数据已经趋于稳定，则需在进行查询操作之前进行清理操作；如果整个数据库或某张表有大数据量的变动，则也推荐在每次变动完成后执行一次清理。

如为数据分批场景，则可以选择开启 Unique Mode

当设备相同时间点数据分批上报时，MARS3 可以对相同时间（此表中为 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 条数据，最后会全部保留，不做任何处理。

注意！
如开启 Unique Mode，则 ORDER BY 子句的第一个字段在定义时需要添加 NOT NULL 约束。MARS3 的 Unique Mode 暂时不支持删除。

如开启，则此示例建表语句如下：

=# CREATE TABLE vehicle_basic_data_mars3_um(
  daq_time timestamp ,
  vin varchar(32)  COLLATE "C" NOT NULL,
  lng float ,
  lat float ,
  speed float ,
  license_template varchar(16) ,
  flag integer
)
USING MARS3
WITH (compresstype=zstd, compresslevel=3,compress_threshold=1200,uniquemode=true,
        mars3options='rowstore_size=64')
DISTRIBUTED BY (vin)
ORDER BY (vin, daq_time)
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);

2 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);

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

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

选择排序键

设计表时，就要考虑到排序键的选择。我们推荐你选择数据主体的标识字段，如果有时间戳可以加在标识字段前一起作为排序键。示例表中是 (vin, daq_time)。

排序键是索引的一部分，因此这类查询可以通过索引定位到存储位置，从而避免全表扫描。这是为了优化根据数据主体出发的查询，让数据按照数据主体的顺序存储。
同一个主体的数据是连续存放的，能够减少 I/O 数量，提升查询速度。

encoding (minmax)

这个选项的使用是为了提升计算能力。

首先，建议给所有排序键加上 minmax 选项，因为排序键都是有序的，在全表扫描时 minmax 过滤也能起到很好的过滤效果。

其次，如果你想要根据此字段进行 min、max、avg、sum、count 等聚集查询，或者进行 WHERE 条件过滤时需要加上这个选项。

这个选项的添加需要根据业务场景来确定，不要不加思考地添加至全部的字段，否则这种操作会带来很多数据写入的 CPU 以及磁盘 I/O 的消耗。

COLLATE "C"

同上文 MARS3 示例。

USING MARS2

是使用 MARS2 表的固定写法，不能改变内容。

WITH (compresstype=zstd, compresslevel=3)

此表中推荐使用 zstd，压缩级别使用 3 级。此示例中使用了通用压缩算法，定制压缩方案请见使用压缩。

DISTRIBUTED BY (vin)

同上文 MARS3 示例。

PARTITION BY RANGE (daq_time)

同上文 MARS3 示例。

( START ('2022-07-01 00:00:00') INCLUSIVE END ('2022-08-01 00:00:00') EXCLUSIVE EVERY (INTERVAL '1 days'),default partition others);

同上文 MARS3 示例。

EVERY (INTERVAL '1 day')

同上文 MARS3 示例。

DEFAULT PARTITION OTHERS

同上文 MARS3 示例。

如为数据分批场景，则可以选择开启 Unique Mode

当设备相同时间点数据分批上报时，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 条数据，最后会全部保留，不做任何处理。

如开启，则此示例中创建索引语句如下：

=# CREATE INDEX idx_mars2_um ON vehicle_basic_data_mars2_um 
USING mars2_btree(vin, daq_time) WITH(uniquemode = true);

注意！
一张 MARS2 表只支持一个索引，且创建后不可删除。

3 HEAP 表 / AO 表

3.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);

3.2 AO 表

3.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);

3.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);

3.3 二选一最佳实践

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

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