【StarRocks系列】建表优化

目录

一、数据模型选择 (核心优化)

二、分区与分桶策略 (数据分布优化)

三、字段类型与压缩

四、索引策略

五、高级特性应用

六、建表示例（关键优化整合）

参考官网 优化性能 | StarRocks

在 StarRocks 中创建表时，合理的表设计是性能优化的基石。以下是从多个关键方向考虑的优化要点：

一、数据模型选择 (核心优化)

1. 明细模型 (Duplicate Key)

• • 适用场景：需要保留原始明细数据（如日志、交易流水）、任意维度组合查询或频繁更新。
  • 优化点：仅需指定排序列（非唯一），通常选择常用过滤条件列（如 dt, user_id）。避免过多排序列增加存储开销。

1. 聚合模型 (Aggregate Key)

• • 适用场景：预聚合报表（如 PV、UV、SUM、MAX）。
  • 优化点：

• • • 精确选择聚合列和聚合函数（SUM、REPLACE、MAX等）。
    • 维度列需包含所有可能的查询分组列。
    • 值列必须是可聚合类型。

1. 主键模型 (Primary Key)

• • 适用场景：实时更新/删除（如用户状态、订单状态）。
  • 优化点：

• • • 主键列选择短且唯一的列（如 order_id），减少内存占用。
    • 启用 enable_persistent_index 持久化索引提升稳定性。
    • 合理设置 bucket_size（默认 10W）平衡内存与导入性能。

二、分区与分桶策略 (数据分布优化)

1. 分区 (Partitioning)

• • 目的：剪枝（Pruning），减少扫描数据量。
  • 优化点：

• • • 按时间列分区（如 PARTITION BY RANGE(dt)），适合时序数据。
    • 分区粒度适中：太细（如按秒）导致元数据膨胀；太粗（如按月）降低剪枝效果。
    • 使用动态分区管理（PROPERTIES 中配置自动创建/删除）。

1. 分桶 (Bucketing)

• • 目的：数据打散、并行计算优化。
  • 优化点：

• • • 分桶键选择高基数列（如 user_id, device_id），确保数据均匀分布。
    • 分桶键应常作为 JOIN 或 GROUP BY 的 Key。
    • 分桶数量：

• • • • 建议 = BE 节点数 × CPU Core × 2（推荐 8-64）。
      • 小表可设置较少桶数（如 8），大表适当增加。

• • • 避免使用 Random Distribution（除非明确需要随机分布）。

三、字段类型与压缩

1. 字段类型优化

• • 使用最小适用类型：TINYINT 代替 INT，VARCHAR(10) 代替 STRING。
  • 优先选数值类型（比字符串更快）。
  • 避免过长的 VARCHAR（影响内存和 IO）。

1. 压缩算法

• • 默认 LZ4 已足够高效，通常无需修改。
  • 极高压缩比场景可测试 Zstandard（zstd），但消耗更多 CPU。

四、索引策略

1. 前缀索引 (Prefix Index)

• • 自动生成：基于排序列的前 36 字节。
  • 优化点：将高频过滤的短字段（如 user_id）放在排序列最前面。

1. Bloom Filter 索引

• • 适用场景：高基数列的等值查询（如 user_id, order_id）。
  • 优化点：在 PROPERTIES 中添加 bloom_filter_columns = "col1,col2"。

1. Bitmap 索引

• • 适用场景：低基数列的等值/IN 查询（如 gender, city）。
  • 创建语法：INDEX idx_name (col) USING BITMAP

五、高级特性应用

1. 物化视图 (Materialized View)

• • 适用场景：加速固定维度的聚合查询。
  • 优化点：针对高频复杂查询创建异步更新的物化视图。

1. Colocate Join

• • 适用场景：频繁大表 JOIN。
  • 优化点：

• • • 相关表使用相同的分桶键和分桶数。
    • 创建时指定 colocate_with = "group_name"。

1. 冷热数据分离

• • 通过 PROPERTIES 指定 SSD/HDD 存储策略：

PROPERTIES (
  "storage_medium" = "SSD",
  "storage_cooldown_time" = "2025-01-01 00:00:00"
);

六、建表示例（关键优化整合）

CREATE TABLE user_orders (
    dt DATE NOT NULL,
    user_id INT NOT NULL,
    order_id BIGINT NOT NULL,
    amount DECIMAL(10,2) REPLACE DEFAULT "0"  -- 聚合模型示例
) 
ENGINE=OLAP
PRIMARY KEY (dt, user_id, order_id)  -- 主键模型
PARTITION BY RANGE(dt) (
    START ("2023-01-01") END ("2024-01-01") EVERY (INTERVAL 1 MONTH)
)
DISTRIBUTED BY HASH(user_id) BUCKETS 32  -- 分桶键选择
PROPERTIES (
    "replication_num" = "3", 
    "bloom_filter_columns" = "order_id,user_id",  -- Bloom Filter
    "enable_persistent_index" = "true",          -- 主键持久化
    "storage_medium" = "SSD"                     -- 热数据存SSD
);