表设计与数据分布最佳实践

表设计最佳实践

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

MARS3
HEAP
AORO

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 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 AORO 表

AORO 表即行存的 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.3 二选一最佳实践

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

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

数据分布最佳实践

哈希分布（HASH）

建表语句

CREATE TABLE finance_voucher_main (
  subject_id INT NOT NULL PRIMARY KEY,
  company_id INT NOT NULL ,
  voucher_date DATE NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency_type VARCHAR(3) DEFAULT 'CNY',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) 
USING MARS3
DISTRIBUTED BY (company_id,subject_id);

查看表结构

postgres=# \d+ finance_voucher_main
                                                Table "public.finance_voucher_main"
  Column     |            Type             | Collation | Nullable |         Default          | Storage  | Stats target | Description
---------------+-----------------------------+-----------+----------+--------------------------+----------+--------------+-------------
subject_id    | integer                     |           | not null |                          | plain    |              |
company_id    | integer                     |           | not null |                          | plain    |              |
voucher_date  | date                        |           | not null |                          | plain    |              |
amount        | numeric(18,2)               |           | not null |                          | main     |              |
currency_type | character varying(3)        |           |          | 'CNY'::character varying | extended |              |
create_time   | timestamp without time zone |           |          | CURRENT_TIMESTAMP        | plain    |              |
update_time   | timestamp without time zone |           |          | CURRENT_TIMESTAMP        | plain    |              |
Indexes:
  "finance_voucher_main_pkey" PRIMARY KEY, mars3_btree (subject_id)
Distributed by: (company_id, subject_id)
Access method: mars3

随机分布（RANDOM）

建表语句

CREATE TABLE etl_finance_temp (
  subject_id INT NOT NULL PRIMARY KEY,
  company_id INT NOT NULL ,
  voucher_date DATE NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency_type VARCHAR(3) DEFAULT 'CNY',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) 
USING MARS3
DISTRIBUTED RANDOMLY;

查看表结构

postgres=# \d+ etl_finance_temp
                                                  Table "public.etl_finance_temp"
  Column     |            Type             | Collation | Nullable |         Default          | Storage  | Stats target | Description
---------------+-----------------------------+-----------+----------+--------------------------+----------+--------------+-------------
subject_id    | integer                     |           | not null |                          | plain    |              |
company_id    | integer                     |           | not null |                          | plain    |              |
voucher_date  | date                        |           | not null |                          | plain    |              |
amount        | numeric(18,2)               |           | not null |                          | main     |              |
currency_type | character varying(3)        |           |          | 'CNY'::character varying | extended |              |
create_time   | timestamp without time zone |           |          | CURRENT_TIMESTAMP        | plain    |              |
update_time   | timestamp without time zone |           |          | CURRENT_TIMESTAMP        | plain    |              |
Indexes:
  "etl_finance_temp_pkey" PRIMARY KEY, mars3_btree (subject_id)
Distributed randomly
Access method: mars3

复制分布（REPLICATED）

建表语句

CREATE TABLE dict_subject (
  subject_id INT NOT NULL PRIMARY KEY,
  company_id INT NOT NULL ,
  voucher_date DATE NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency_type VARCHAR(3) DEFAULT 'CNY',
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) 
USING MARS3
DISTRIBUTED REPLICATED;

查看表结构

postgres=# \d+ dict_subject
                                                    Table "public.dict_subject"
  Column     |            Type             | Collation | Nullable |         Default          | Storage  | Stats target | Description
---------------+-----------------------------+-----------+----------+--------------------------+----------+--------------+-------------
subject_id    | integer                     |           | not null |                          | plain    |              |
company_id    | integer                     |           | not null |                          | plain    |              |
voucher_date  | date                        |           | not null |                          | plain    |              |
amount        | numeric(18,2)               |           | not null |                          | main     |              |
currency_type | character varying(3)        |           |          | 'CNY'::character varying | extended |              |
create_time   | timestamp without time zone |           |          | CURRENT_TIMESTAMP        | plain    |              |
update_time   | timestamp without time zone |           |          | CURRENT_TIMESTAMP        | plain    |              |
Indexes:
  "dict_subject_pkey" PRIMARY KEY, mars3_btree (subject_id)
Distributed Replicated
Access method: mars3