ClickHouse 24.3：MergeTree 引擎分区与排序优化

一、分区优化策略

分区（PARTITION BY）用于管理数据存储结构，优化点包括：

1. 分区粒度控制

   • 推荐按时间分区（如按月）：PARTITION BY toYYYYMM(event_time)
   • 分区数上限建议：$$ \text{分区数量} \leq 1000 $$
   • 过小分区导致"文件碎片化"：$$ \text{性能损失} \propto \frac{1}{\text{分区大小}} $$

2. 分区修剪（Partition Pruning）

   -- 优化前（全表扫描）
   SELECT * FROM logs WHERE event_date >= '2024-01-01'
   
   -- 优化后（分区裁剪）
   ALTER TABLE logs MODIFY PARTITION BY toDate(event_date)
   SELECT * FROM logs WHERE event_date = '2024-03-15'
   

3. 冷热数据分层

   ALTER TABLE logs 
   MODIFY SETTING storage_policy = 'hot_cold'
   -- 热数据：SSD（最近3月）
   -- 冷数据：HDD（历史数据）
   

二、排序键优化策略

排序键（ORDER BY）决定数据物理存储顺序，核心优化：

1. 基数优先级原则

   • 高基数列在前：ORDER BY (user_id, timestamp)
   • 低基数列在后：避免ORDER BY (country, gender)
   • 查询效率提升：$$ \text{查询速度} \propto \text{排序键匹配度} $$

2. 跳数索引联动

   ALTER TABLE user_actions 
   ADD INDEX uid_idx user_id TYPE minmax GRANULARITY 4
   -- 排序键需包含索引列
   ORDER BY (user_id, action_time)
   

3. 压缩率优化

   原始数据： [100, 100, 101, 100, 102] 
   排序后：  [100, 100, 100, 101, 102]  → LZ4压缩率提升30%
   

三、完整优化示例

CREATE TABLE user_events (
    event_date    Date,
    user_id       UInt64,
    event_type    String,
    duration_ms   UInt32
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)  -- 月分区
ORDER BY (user_id, event_type)     -- 优先高基数user_id
SETTINGS:
    index_granularity = 8192,       -- 适当增大颗粒度
    min_bytes_for_wide_part = 0     -- 禁用宽表优化

四、性能验证方法

1. 分区效率检查

   SELECT partition, count() 
   FROM system.parts 
   WHERE table = 'user_events'
   GROUP BY partition
   

   • 理想结果：各分区数据量均衡

2. 排序键命中分析

   EXPLAIN indexes = 1
   SELECT * FROM user_events 
   WHERE user_id = 10123 AND event_type = 'click'
   

   • 关键指标：Index Keys: 2/2 used

五、注意事项

1. 避免过度分区

   • 每个分区至少保留 100MB 数据
   • 监控命令：SELECT * FROM system.merges

2. 排序键修改限制

   • 仅能通过重建表修改：CREATE TABLE new ... INSERT FROM old
   • 24.3 新特性：支持轻量级排序键调整（实验功能）

3. TTL 与分区协同

   ALTER TABLE logs 
   MODIFY TTL event_date + INTERVAL 6 MONTH 
   TO DISK 'cold_storage'  -- 自动迁移冷数据
   

通过合理配置分区键和排序键，MergeTree 引擎的查询性能可提升 $3\sim5\times$ ，同时降低 $40%$ 的存储空间占用。建议结合 EXPLAIN 和系统表监控持续调优。