ClickHouse列式存储原理与MergeTree引擎
ClickHouse是面向OLAP场景的列式数据库管理系统，设计目标是在亿级数据量下实现秒级聚合查询。与MySQL等行式存储不同，ClickHouse按列存储数据，查询时只读取涉及的列，大幅减少IO扫描量。MergeTree是ClickHouse最核心的表引擎，支持数据分区、主键索引、数据TTL和后台合并机制。数据写入后生成data part片段，后台线程定期合并小片段减少文件数量，合并过程中同时执行TTL过期和数据去重。
-- 创建MergeTree表
CREATE TABLE events (
event_id UInt64,
event_time DateTime,
event_date Date MATERIALIZED toDate(event_time),
user_id UInt64,
event_type LowCardinality(String),
platform LowCardinality(String),
country LowCardinality(String),
duration_ms UInt32,
properties String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, event_time, user_id)
SETTINGS index_granularity = 8192,
ttl_only_drop_parts = 1;
-- 数据TTL：90天后自动过期
ALTER TABLE events
MODIFY TTL event_date + INTERVAL 90 DAY DELETE;LowCardinality(String)类型对低基数字符串使用字典编码，存储空间压缩至1/8到1/10。index_granularity控制索引粒度，8192表示每8192行生成一个索引标记，默认值适合大多数场景。PARTITION BY按月分区，查询时通过分区裁剪跳过不涉及的分区目录。
分布式集群分片与副本配置
ClickHouse分布式集群通过Distributed引擎实现分片查询。每个分片可以配置多个副本实现高可用。ZooKeeper或ClickHouse Keeper负责副本同步和元数据管理。
<!-- /etc/clickhouse-server/config.d/cluster.xml -->
<clickhouse>
<remote_servers>
<analytics_cluster>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>clickhouse-01</host>
<port>9000</port>
</replica>
<replica>
<host>clickhouse-02</host>
<port>9000</port>
</replica>
</shard>
<shard>
<internal_replication>true</internal_replication>
<replica>
<host>clickhouse-03</host>
<port>9000</port>
</replica>
<replica>
<host>clickhouse-04</host>
<port>9000</port>
</replica>
</shard>
</analytics_cluster>
</remote_servers>
<zookeeper>
<node>
<host>zookeeper-01</host>
<port>2181</port>
</node>
<node>
<host>zookeeper-02</host>
<port>2181</port>
</node>
<node>
<host>zookeeper-03</host>
<port>2181</port>
</node>
</zookeeper>
<macros>
<shard>01</shard>
<replica>01</replica>
</macros>
</clickhouse>创建ReplicatedMergeTree本地表和Distributed分布式表：
-- 本地复制表（每个节点执行）
CREATE TABLE events_local ON CLUSTER analytics_cluster (
event_id UInt64,
event_time DateTime,
event_date Date MATERIALIZED toDate(event_time),
user_id UInt64,
event_type LowCardinality(String),
platform LowCardinality(String),
country LowCardinality(String),
duration_ms UInt32,
properties String
) ENGINE = ReplicatedMergeTree(
'/clickhouse/tables/{shard}/events_local',
'{replica}'
)
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, event_time, user_id);
-- 分布式表（任意节点执行）
CREATE TABLE events_distributed ON CLUSTER analytics_cluster AS events_local
ENGINE = Distributed(
analytics_cluster,
default,
events_local,
rand() -- 分片键：随机分布
);分区设计与稀疏索引优化
分区设计直接影响查询裁剪效率。分区粒度过细（如按天分区）会产生大量小片段增加合并开销，粒度过粗（如按年分区）无法有效裁剪。按月分区是大多数时间序列数据的平衡选择。
-- 查询分区信息
SELECT
partition,
formatReadableSize(sum(bytes_on_disk)) AS size,
sum(rows) AS rows,
count() AS parts_count
FROM system.parts
WHERE database = 'default' AND table = 'events_local' AND active
GROUP BY partition
ORDER BY partition;
-- 验证分区裁剪效果
EXPLAIN PARTITIONS
SELECT count(), uniqExact(user_id)
FROM events_distributed
WHERE event_date BETWEEN '2026-07-01' AND '2026-07-31'
AND event_type = 'click';
-- 跳过索引优化：WHERE条件中有不在ORDER BY中的列时
-- 使用跳数索引加速
ALTER TABLE events_local
ADD INDEX idx_user_id user_id TYPE set(10000) GRANULARITY 4;
ALTER TABLE events_local
ADD INDEX idx_duration duration_ms TYPE minmax GRANULARITY 4;
ALTER TABLE events_local
ADD INDEX idx_properties properties(1000) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 1;
-- 物化索引
MATERIALIZE INDEX idx_user_id ON events_local;set索引适用于等值查询，minmax索引适用于范围查询，ngrambf_v1索引适用于模糊文本搜索。GRANULARITY参数控制索引粒度，值为4表示每4个数据粒度（8192乘4=32768行）生成一个索引条目。
物化视图与聚合表引擎实时聚合
物化视图自动将原始表数据按指定维度聚合后存储到目标表，查询时直接读取预聚合结果。SummingMergeTree引擎自动合并相同主键的行并累加数值列。
-- 创建聚合目标表
CREATE TABLE events_hourly_stats ON CLUSTER analytics_cluster (
hour DateTime,
event_type LowCardinality(String),
platform LowCardinality(String),
country LowCardinality(String),
event_count UInt64,
unique_users UInt64,
avg_duration Float64
) ENGINE = ReplicatedSummingMergeTree(
'/clickhouse/tables/{shard}/events_hourly_stats',
'{replica}'
)
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, event_type, platform, country);
-- 创建物化视图，自动从源表聚合数据
CREATE MATERIALIZED VIEW events_hourly_mv ON CLUSTER analytics_cluster
TO events_hourly_stats
AS
SELECT
toStartOfHour(event_time) AS hour,
event_type,
platform,
country,
count() AS event_count,
uniqState(user_id) AS unique_users,
avgState(duration_ms) AS avg_duration
FROM events_local
GROUP BY hour, event_type, platform, country;
-- 查询聚合表
SELECT
hour,
event_type,
sum(event_count) AS total_events,
uniqMerge(unique_users) AS total_unique_users,
avgMerge(avg_duration) AS avg_duration_ms
FROM events_hourly_stats
WHERE hour BETWEEN '2026-08-20 00:00:00' AND '2026-08-21 00:00:00'
GROUP BY hour, event_type
ORDER BY hour, event_type;uniqState和avgState是聚合状态函数，存储中间状态而非最终结果。查询时使用uniqMerge和avgMerge合并状态计算最终值。这种设计支持跨分片合并，因为每个分片存储的是可合并的聚合状态而非不可合并的标量值。
高频查询优化策略与执行计划分析
-- 分析查询执行计划
EXPLAIN actions = 1
SELECT event_type, count(), avg(duration_ms)
FROM events_distributed
WHERE event_date = '2026-08-20'
AND platform = 'web'
GROUP BY event_type
ORDER BY count() DESC
LIMIT 10;
-- 常见优化手段
-- 1. 使用PREWHERE提前过滤（在WHERE之前执行，减少读取列）
SELECT event_type, count()
FROM events_distributed
PREWHERE platform = 'web'
WHERE event_date = '2026-08-20'
GROUP BY event_type;
-- 2. 使用近似函数替代精确函数
-- uniqExact 精确去重，内存消耗大
-- uniq 近似去重（HyperLogLog），内存消耗小
SELECT uniq(user_id) AS uv -- 替代 uniqExact
FROM events_distributed
WHERE event_date = '2026-08-20';
-- 3. 避免SELECT *，只查询需要的列
SELECT event_type, count() -- 而非 SELECT *
FROM events_distributed
WHERE event_date = '2026-08-20'
GROUP BY event_type;
-- 4. 使用异步插入提升写入吞吐
SET async_insert = 1;
SET wait_for_async_insert = 0;
SET async_insert_max_data_size = 1048576; -- 1MB
SET async_insert_max_query_number = 100;PREWHERE将过滤条件下推到列读取之前，先读取过滤条件涉及的列进行过滤，再读取剩余列，减少非过滤列的IO。异步插入将小批量写入在服务端缓冲后批量落盘，提升写入吞吐量。对于精确去重场景使用uniqExact，对于UV统计等可接受误差的场景使用uniq，内存消耗可降低一个数量级。
原创文章，作者：小编，如若转载，请注明出处：https://www.yunthe.com/clickhouse-lie-shi-cun-chu-yin-qing-jia-gou-she-ji-yu-olap/
