HTAP标准版性能调优

不要用SELECT *，去除冗余的字段，函数计算等。

开启方法：通过DAS连接HTAP实例，执行命令SET GLOBAL enable_query_cache=true;

作用：优化同一批聚合查询SQL，频繁执行的场景。

添加原则：

• 经常作为查询条件的列，建议选为排序列。

• 排序键的顺序按使用频次和数据基数确认，高基数优先。
• 当多个字段单独查询的比例差不多时，表中按一个顺序指定排序键，再单独创建物化视图，按其他字段作为排序键。
• 排序键不要超过5个，字段长度不要超过36字节，遇到varchar会截断，不能包含 float ，double，bit 类型的列。

添加方法：在创建同步任务时，通过表同步功能功能添加排序键。

作用：减少数据扫描量

样例：

TP侧创建订单表。

CREATE TABLE `orders` (
`O_ORDERKEY` bigint NOT NULL,
`O_CUSTKEY` bigint NOT NULL,
`O_ORDERSTATUS` char(1) COLLATE utf8mb4_bin NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) COLLATE utf8mb4_bin NOT NULL,
`O_CLERK` char(15) COLLATE utf8mb4_bin NOT NULL,
`O_SHIPPRIORITY` bigint NOT NULL,
`O_COMMENT` varchar(79) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`O_ORDERKEY`)
);

默认不做任何处理，同步到AP表时默认按主键作为排序键，表定义如下：

CREATE TABLE `orders` (
`O_ORDERKEY` bigint(20) NOT NULL COMMENT "",
`O_CUSTKEY` bigint(20) NOT NULL COMMENT "",
`O_ORDERSTATUS` varchar(4) NOT NULL COMMENT "",
`O_TOTALPRICE` decimal(15, 2) NOT NULL COMMENT "",
`O_ORDERDATE` date NOT NULL COMMENT "",
`O_ORDERPRIORITY` varchar(60) NOT NULL COMMENT "",
`O_CLERK` varchar(60) NOT NULL COMMENT "",
`O_SHIPPRIORITY` bigint(20) NOT NULL COMMENT "",
`O_COMMENT` varchar(316) NOT NULL COMMENT ""
) ENGINE=OLAP
PRIMARY KEY(`O_ORDERKEY`)
DISTRIBUTED BY HASH(`O_ORDERKEY`)
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"enable_persistent_index" = "false",
"replicated_storage" = "true",
"compression" = "LZ4"
);

但AP侧查询时，主要使用O_CUSTKEY字段，对订单进行统计:

select count(*) from orders where O_CUSTKEY = 123;

此时可以调整AP表的定义，将O_CUSTKEY作为表的排序键（主键模型默认使用主键作为排序键），在创建数据同步任务时单独指定表的排序键ORDER BY (O_CUSTKEY);调整后的表结构为

CREATE TABLE `orders` (
`O_ORDERKEY` bigint(20) NOT NULL COMMENT "",
`O_CUSTKEY` bigint(20) NOT NULL COMMENT "",
`O_ORDERSTATUS` varchar(4) NOT NULL COMMENT "",
`O_TOTALPRICE` decimal(15, 2) NOT NULL COMMENT "",
`O_ORDERDATE` date NOT NULL COMMENT "",
`O_ORDERPRIORITY` varchar(60) NOT NULL COMMENT "",
`O_CLERK` varchar(60) NOT NULL COMMENT "",
`O_SHIPPRIORITY` bigint(20) NOT NULL COMMENT "",
`O_COMMENT` varchar(316) NOT NULL COMMENT ""
) ENGINE=OLAP
PRIMARY KEY(`O_ORDERKEY`)
DISTRIBUTED BY HASH(`O_ORDERKEY`)
ORDER BY(`O_CUSTKEY`)
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"enable_persistent_index" = "false",
"replicated_storage" = "true",
"compression" = "LZ4"
);

添加原则：

• 值不会变化的时间列经常用于WHERE过滤，使用该列创建分区，一般建议按月做分区。
• 分区个数不能超过1024个，单分区数据量不要超过100GB。
• 添加分区时一并评估单分区的桶数，单桶的数据量建议1-10G。

添加方法：在创建同步任务时，通过表同步功能功能添加分区。

• 添加桶

  添加原则：

  • 常用于查询的列，基数比较高。
  • 数据不出现倾斜。

  添加方法：在创建同步任务时，通过表同步功能功能添加分桶。

  作用：减少数据扫描量。

• 桶个数及数据量

  设置原则：每个桶数据量1~10G

  设置方法：

  • 创建同步任务时设置库同步如下参数：

    expect_tablet_size：桶数据量大小，单位是GB，默认为3。

    expect_tablet_num_for_one_partition：桶的个数，根据表的数据量大小创建桶，默认为0。

  • 通过表同步设置指定

    distributed by (column1) buckets 3;

  • 设置分区键时需一并设置桶个数，默认会每个分区都按表的桶数设置，可能导致表的分片数据过多。

  作用：减少内存占用，提升数据合并效率和并行度。

• 样例：

  TP侧有如下订单表。

  CREATE TABLE `orders` (
  `O_ORDERKEY` bigint NOT NULL,
  `O_CUSTKEY` bigint NOT NULL,
  `O_ORDERSTATUS` char(1) COLLATE utf8mb4_bin NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) COLLATE utf8mb4_bin NOT NULL,
  `O_CLERK` char(15) COLLATE utf8mb4_bin NOT NULL,
  `O_SHIPPRIORITY` bigint NOT NULL,
  `O_COMMENT` varchar(79) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`O_ORDERKEY`)
  );

  默认不做任何处理，同步到AP表时默认按主键做HASH分桶，定义如下：

  CREATE TABLE `orders` (
  `O_ORDERKEY` bigint(20) NOT NULL COMMENT "",
  `O_CUSTKEY` bigint(20) NOT NULL COMMENT "",
  `O_ORDERSTATUS` varchar(4) NOT NULL COMMENT "",
  `O_TOTALPRICE` decimal(15, 2) NOT NULL COMMENT "",
  `O_ORDERDATE` date NOT NULL COMMENT "",
  `O_ORDERPRIORITY` varchar(60) NOT NULL COMMENT "",
  `O_CLERK` varchar(60) NOT NULL COMMENT "",
  `O_SHIPPRIORITY` bigint(20) NOT NULL COMMENT "",
  `O_COMMENT` varchar(316) NOT NULL COMMENT ""
  ) ENGINE=OLAP
  PRIMARY KEY(`O_ORDERKEY`)
  DISTRIBUTED BY HASH(`O_ORDERKEY`)
  PROPERTIES (
  "replication_num" = "1",
  "in_memory" = "false",
  "enable_persistent_index" = "false",
  "replicated_storage" = "true",
  "compression" = "LZ4"
  );

  AP侧查询时，主要使用O_CUSTKEY和O_ORDERDATE字段，对订单进行统计，数据量按日期进行分区，不会发生严重倾斜。

  select count(*) from orders where O_CUSTKEY = 123 and O_ORDERDATE='2025-01-01';

  此时可以调整AP表的定义，将O_ORDERDATE作为分区键。O_CUSTKEY作为分桶键，DISTRIBUTED BY (O_CUSTKEY) 。O_CUSTKEY, O_ORDERDATE作为表的排序键。由于分区键，分桶键必须作为主键的一部分，需要同步将主键调整为KEY COLUMNS(O_ORDERKEY，O_ORDERDATE，O_CUSTKEY)调整后的表结构为：

  CREATE TABLE `orders` (
  `O_ORDERKEY` bigint(20) NOT NULL COMMENT "",
  `O_ORDERDATE` date NOT NULL COMMENT "",
  `O_CUSTKEY` bigint(20) NOT NULL COMMENT "",
  `O_ORDERSTATUS` varchar(4) NOT NULL COMMENT "",
  `O_TOTALPRICE` decimal(15, 2) NOT NULL COMMENT "",
  `O_ORDERPRIORITY` varchar(60) NOT NULL COMMENT "",
  `O_CLERK` varchar(60) NOT NULL COMMENT "",
  `O_SHIPPRIORITY` bigint(20) NOT NULL COMMENT "",
  `O_COMMENT` varchar(316) NOT NULL COMMENT ""
  ) ENGINE=OLAP
  PRIMARY KEY(`O_ORDERKEY`, `O_ORDERDATE`, `O_CUSTKEY`)
  PARTITION BY date_trunc('month', O_ORDERDATE)
  DISTRIBUTED BY HASH(`O_CUSTKEY`)
  ORDER BY(`O_CUSTKEY`, `O_ORDERDATE`)
  PROPERTIES (
  "replication_num" = "1",
  "in_memory" = "false",
  "enable_persistent_index" = "false",
  "replicated_storage" = "true",
  "compression" = "LZ4"
  );

• bitmap索引

  添加原则：经常用于过滤，不在排序键里，适合基数在10000-100000左右的列或多个低基数列组合查询。

  添加方法：

  • 添加索引：

    CREATE INDEX column1_index1 ON table (column1) USING BITMAP;

  • 查看进度：

    SHOW ALTER TABLE COLUMN [FROM db_name];

  • 查看已创建的索引：

    SHOW { INDEX[ES] | KEY[S] } FROM [db_name.]table_name [FROM db_name];

  • 删除索引

    DROP INDEX index_name ON [db_name.]table_name;

  约束限制：不支持 FLOAT、DOUBLE、BOOLEAN 和 DECIMAL 类型。

• bloomfilter索引

  添加原则：适合基数在100000+的列，列的重复度很低。

  添加方法：

  • 添加索引：

    ALTER TABLE table1 SET ("bloom_filter_columns" = "k1,k2,v1");

  • 查看进度：

    SHOW ALTER TABLE

  作用：减少数据扫描量。

添加原则：有多个频繁使用的查询，与排序键顺序不一致时，可以单独创建物化视图，优化这类查询。

作用：重新生成一份新的数据，满足特定场景的查询。

适用场景：当涉及多表JOIN，QPS无法提升的场景。

使用方法：将多表组装成大宽表，在大宽表上进行查询。