"ClickHouse 表引擎与排序键分区优化实战"

ClickHouse 表引擎与排序键分区优化实战建表示例CREATE TABLE events ( ts DateTime, user_id UInt64, action String ) ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (ts, user_id) SETTINGS index_granularity = 8192; 查询优化SELECT count() FROM events WHERE ts >= now() - INTERVAL 7 DAY AND action = 'login'; SELECT user_id, count() FROM events WHERE ts >= now() - INTERVAL 1 DAY GROUP BY user_id ORDER BY count() DESC LIMIT 20; 维护根据时间维度滚动分区，便于归档与清理合理设置 `index_granularity` 平衡扫描与索引体积总结通过时间分区与合适的排序键，可显著提升过滤与聚合效率。