clickhouse | 建表的时候可以做的一些优化

clickhouse 使用过程中，针对查询进行一些必要的优化和完全不做优化，在大数据量的表现完全是两回事，因此，在建表的时候，就要考虑优化的问题，避免后期重新建表的问题。

1. 选择合适的数据分区（PARTITION BY）

• 作用：减少查询时扫描的数据量。

• 优化建议：

  • 按时间分区（如toYYYYMM(date)）或低基数列分区（如地域）。

  • 避免分区过多（如按小时分区），否则可能引发文件数爆炸，影响性能。

  • 分区粒度过大会降低过滤效率，需权衡查询频率和分区大小。

2. 设计高效的排序键（ORDER BY）

• 作用：数据按排序键物理有序存储，加速范围查询和过滤。

• 优化建议：

  • 将高频过滤或范围查询的列（如时间、用户ID）放在排序键最前面。

  • 排序键通常与主键（PRIMARY KEY）一致，但主键仅用于索引，不影响存储顺序。

  • 避免过多列，一般不超过3-5列。

3. 合理使用主键（PRIMARY KEY）

• 注意：ClickHouse的主键是稀疏索引，用于定位数据块。

• 优化建议：

  • 主键应覆盖高频查询的过滤条件。

  • 主键列的顺序需与ORDER BY一致，否则会被自动调整。

  • 稀疏索引适合高基数列（如ID），低基数列（如状态码）效果较差。
    

4. 跳数索引（INDEX）

• 作用：加速特定列的查询（如WHERE条件）。

• 类型：minmax、set、ngrambf_v1（文本）、bloom_filter等。

• 优化建议：

  • 为低基数列或非排序键列添加跳数索引。

  • 权衡索引开销（存储和计算），仅对关键查询列使用。

5. 数据生命周期管理（TTL）

• 作用：自动删除过期数据或转移冷数据。

• 场景：

  • 自动删除日志：TTL date + INTERVAL 7 DAY DELETE。

  • 冷热分层：TTL date + INTERVAL 1 MONTH TO DISK 'cold_storage'。

6. 选择高效的数据类型

• 优化建议：

  • 避免Nullable：用默认值（如0或空字符串）代替NULL，减少判断开销。

  • 优先使用数值类型（如UInt32代替String存储IP地址）。

  • 时间字段用DateTime而非String。

  • 枚举类型用Enum替代String。

7. 表引擎选择

• 常用引擎：

  • MergeTree系列：基础引擎，支持分区、排序、索引。

  • ReplacingMergeTree：自动去重（需手动触发OPTIMIZE）。

  • SummingMergeTree/AggregatingMergeTree：预聚合数据，减少计算量。

  • Distributed：分布式表，用于分片集群。

8. 编码与压缩优化

• 压缩算法：

  • 默认使用LZ4，速度快但压缩率一般；ZSTD压缩率更高（如SETTINGS compression_method='zstd'）。

• 编码：

  • 对低基数列使用CODEC(T64)或CODEC(DoubleDelta)减少存储。

9. 数据采样（SAMPLE BY）

• 作用：按特定列采样查询，加速近似计算。

• 适用场景：大数据量下的统计分析（如SELECT count() FROM table SAMPLE 1/10）。

10. 分布式表优化

• 分片键（sharding_key）：

  • 数据均匀分布：如rand()或哈希列。

  • 本地性优化：按用户ID分片，减少跨节点查询。

• 副本配置：通过ReplicatedMergeTree实现高可用。

11. 避免宽表

• 问题：列过多可能导致内存压力，影响并行查询。

• 优化：

  • 将宽表拆分为多个关联表。

  • 使用JOIN或预聚合宽表数据

12. 预聚合与物化视图

• 物化视图（MATERIALIZED VIEW）：

  • 预计算常用聚合结果（如每日UV）。

  • 数据自动更新，但写入时增加开销。

示例：优化后的建表语句

CREATE TABLE user_behavior (
    user_id UInt64,
    event_time DateTime,
    url String,
    status UInt8 DEFAULT 0
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time)
PRIMARY KEY (user_id)
SETTINGS 
    index_granularity = 8192,  -- 稀疏索引粒度（默认值）
    storage_policy = 'hot_cold';  -- 冷热存储策略

关键原则

1. 按查询模式设计：所有优化需基于实际查询需求。

2. 测试验证：通过EXPLAIN和查询日志分析优化效果。

3. 平衡写入与查询：过度优化可能增加写入开销。