🐧运维日志
数据库运维18 min read

ClickHouse 列存数据库运维实战

ClickHouse列存OLAP时序数据大数据分析MergeTree

为什么选 ClickHouse

OLAP（联机分析处理）场景下，行存数据库（MySQL/PostgreSQL）在亿级数据聚合查询时力不从心。ClickHouse 用列存 + 向量化执行做到了极致的查询性能：

维度 MySQL/PG（行存） ClickHouse（列存）
10 亿行 COUNT/SUM 30-60s 0.1-0.5s
10 亿行 GROUP BY 60-120s 0.5-2s
写入吞吐（单机） 1-5 万行/秒 10-50 万行/秒
数据压缩率 1-3× 5-15×
点查（按主键查单行） 快 慢（不擅长）
事务支持 ACID 无（仅批量原子写入）
并发查询 千级 百级（不适合高并发点查）

核心适用场景：

  • 日志/事件分析（千万级日志实时聚合）
  • 监控指标存储与查询（类似 InfluxDB 但更强）
  • 用户行为分析（漏斗/留存/路径）
  • 广告/推荐系统特征存储

不适合的场景：OLTP 事务、高并发点查、频繁 UPDATE/DELETE。

安装部署

单机安装

# CentOS/RHEL
cat > /etc/yum.repos.d/clickhouse.repo << 'EOF'
[clickhouse-stable]
name=ClickHouse Stable Repository
baseurl=https://packages.clickhouse.com/rpm/stable
gpgcheck=1
gpgkey=https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key
enabled=1
EOF

yum install -y clickhouse-server clickhouse-client
systemctl enable --now clickhouse-server

# Ubuntu/Debian
apt install -y apt-transport-https ca-certificates
wget -qO- https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key | apt-key add -
echo "deb https://packages.clickhouse.com/deb stable main" > /etc/apt/sources.list.d/clickhouse.list
apt update && apt install -y clickhouse-server clickhouse-client
systemctl enable --now clickhouse-server

核心目录

路径 用途
/etc/clickhouse-server/config.xml 全局配置
/etc/clickhouse-server/users.xml 用户与配额配置
/var/lib/clickhouse/ 数据存储目录
/var/log/clickhouse-server/ 日志目录
/var/lib/clickhouse/tmp/ 临时文件（大查询的中间结果）

基本配置调优

<!-- /etc/clickhouse-server/config.xml -->
<clickhouse>
    <listen_host>0.0.0.0</listen_host>
    <http_port>8123</http_port>
    <tcp_port>9000</tcp_port>
    
    <!-- 内存限制 -->
    <max_server_memory_usage_to_ram_ratio>0.8</max_server_memory_usage_to_ram_ratio>
    <max_thread_pool_size>10000</max_thread_pool_size>
    
    <!-- 存储策略 -->
    <storage_configuration>
        <disks>
            <default>
                <path>/var/lib/clickhouse/</path>
            </default>
            <ssd_disk>
                <path>/data/ssd/clickhouse/</path>
            </ssd_disk>
        </disks>
        <policies>
            <hot_cold>
                <volumes>
                    <hot>
                        <disk>ssd_disk</disk>
                    </hot>
                    <cold>
                        <disk>default</disk>
                    </cold>
                    <move_factor>0.2</move_factor>
                </volumes>
            </hot_cold>
        </policies>
    </storage_configuration>
</clickhouse>

# 连接
clickhouse-client --host localhost --port 9000

# 设置管理员密码
clickhouse-client
:) SET PASSWORD FOR default = 'StrongPassword123!';

表引擎：MergeTree 家族

ClickHouse 的核心是 MergeTree 引擎族——所有高性能场景都用它。

MergeTree 基础

CREATE TABLE events (
    event_time    DateTime,
    event_date    Date DEFAULT toDate(event_time),
    user_id       UInt64,
    event_type    LowCardinality(String),
    event_data    String,
    ip            IPv4
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)     -- 按月分区
ORDER BY (event_type, user_id, event_time)  -- 排序键（也是主键）
SETTINGS index_granularity = 8192;     -- 索引粒度（每 8192 行一个索引标记）

关键概念：

概念 说明
PARTITION 物理分区，按分区独立存储和管理，旧分区可独立删除
ORDER BY 排序键 = 主键索引，数据按此排序存储，查询时跳过不匹配的 granule
index_granularity 索引粒度，每 N 行一个索引标记，默认 8192
Data part 每次 INSERT 产生一个 part，后台 merge 合并

分区设计原则

-- 日志类：按月分区（每月一个分区，方便 TTL 清理旧数据）
PARTITION BY toYYYYMM(event_date)

-- 时序监控：按天分区（每天数据量不大，天级分区管理灵活）
PARTITION BY toDate(timestamp)

-- 超大表：按周分区（平衡分区数和单分区大小）
PARTITION BY toYYYYWW(event_date)

-- 不要按过细粒度分区（如按小时），分区数过多会拖慢 merge

⚠️ 避坑 #1：单表分区数建议 < 1000。分区过多 = 后台 merge 线程忙不过来 = 查询性能下降。按月分区 10 年才 120 个分区，安全。

ORDER BY 排序键设计

排序键决定了查询跳过能力（类似聚簇索引）：

-- 好：查询条件在前，范围在后
ORDER BY (event_type, user_id, event_time)
-- SELECT * FROM events WHERE event_type='click' AND user_id=123
-- → 高效跳过不匹配的 granule

-- 坏：高基数列在前，低基数在后
ORDER BY (user_id, event_type, event_time)
-- SELECT * WHERE event_type='click'
-- → 无法跳过（user_id 不在条件里），全表扫描

排序键选择原则：

  1. 查询过滤条件中最常用的列放前面
  2. 低基数列（如 type/status）优先放前面
  3. 时间列通常放最后（范围查询用）
  4. 排序键列数建议 3-5 个，不宜过多

ReplacingMergeTree（去重引擎）

-- 自动去重相同主键的记录（后台 merge 时生效）
CREATE TABLE users (
    user_id    UInt64,
    updated_at DateTime,
    name       String,
    email      String
) ENGINE = ReplacingMergeTree(updated_at)  -- 保留 updated_at 最大的版本
ORDER BY (user_id)
PARTITION BY toYYYYMM(updated_at);

⚠️ 避坑 #2：ReplacingMergeTree 的去重是最终一致的——merge 完成前仍有重复。查询时需加 FINAL 关键字强制去重：SELECT * FROM users FINAL。但 FINAL 性能差，大数据量时避免使用。

SummingMergeTree（预聚合引擎）

-- 自动对相同主键的数值列求和
CREATE TABLE page_views_daily (
    view_date  Date,
    page_id    UInt64,
    visits     UInt64,
    duration   UInt32
) ENGINE = SummingMergeTree()
ORDER BY (view_date, page_id)
PARTITION BY toYYYYMM(view_date);

TTL 自动过期

-- 30 天后自动删除数据
ALTER TABLE events MODIFY TTL event_date + INTERVAL 30 DAY;

-- 30 天后移动到冷存储，90 天后删除
ALTER TABLE events MODIFY TTL 
    event_date + INTERVAL 30 DAY TO VOLUME 'cold',
    event_date + INTERVAL 90 DAY DELETE;

数据写入

批量 INSERT（推荐）

-- 单次大批量写入（最优）
INSERT INTO events VALUES
    ('2026-07-21 10:00:00', '2026-07-21', 1001, 'click', '{"btn":"buy"}', '1.2.3.4'),
    ('2026-07-21 10:01:00', '2026-07-21', 1002, 'view', '{"page":"home"}', '1.2.3.5'),
    ...;

-- 从文件导入
clickhouse-client --query "INSERT INTO events FORMAT CSV" < events.csv
clickhouse-client --query "INSERT INTO events FORMAT JSONEachRow" < events.json

写入吞吐关键：

  • 每次插入 1-10 万行（太少产生过多 part，太多锁表久）
  • 每秒不超过 1 次 INSERT（合并 part 的速度跟不上高频小批量）
  • 用 Buffer 表缓冲小批量写入

Buffer 表引擎（缓冲小写入）

-- 底层表
CREATE TABLE events_raw (...) ENGINE = MergeTree() ...;

-- 缓冲表
CREATE TABLE events_buffer AS events_raw
ENGINE = Buffer(currentDatabase, events_raw, 
    16,    -- num_layers（并发缓冲层数）
    600,   -- min_time（秒，缓冲最短时间）
    3600,  -- max_time（秒，缓冲最长时间后强制刷盘）
    10000, -- min_rows（最少行数才刷盘）
    1000000, -- max_rows（最大行数后强制刷盘）
    10000000, -- min_bytes
    100000000 -- max_bytes（100MB 后强制刷盘）
);

-- 写入 buffer 表，自动合并后写入 raw 表
INSERT INTO events_buffer VALUES (...);

流式写入（Kafka）

-- Kafka 引擎消费消息
CREATE TABLE events_kafka (
    event_time DateTime,
    user_id    UInt64,
    event_type String,
    event_data String
) ENGINE = Kafka()
SETTINGS 
    kafka_broker_list = 'kafka1:9092,kafka2:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse_consumer',
    kafka_format = 'JSONEachRow';

-- 物化视图自动消费 Kafka 写入 MergeTree
CREATE MATERIALIZED VIEW events_consumer TO events_raw AS
SELECT * FROM events_kafka;

⚠️ 避坑 #3：ClickHouse 不适合频繁 UPDATE/DELETE。ALTER TABLE ... UPDATE 是异步重写整个分区，极慢。需要更新的场景用 ReplacingMergeTree + 版本号。

查询优化

跳过索引（Data Skipping Index）

CREATE TABLE events (
    ...
    INDEX idx_user user_id TYPE minmax GRANULARITY 4,      -- minmax：数值范围
    INDEX idx_data event_data TYPE tokenbf_v1(4096, 3, 0) GRANULARITY 4,  -- 布隆过滤器：文本包含
    INDEX idx_ip ip TYPE set(100) GRANULARITY 4            -- set：去重值集合
) ENGINE = MergeTree() ...;
索引类型 适用 说明
minmax 数值/日期 记录每 N 个 granule 的 min/max，范围查询跳过
set(N) 低基数字符串 记录去重值集合，等值查询跳过
bloom_filter 任意 布隆过滤器，等值/IN 查询跳过
tokenbf_v1 文本 分词布隆过滤器，LIKE 查询
ngrambf_v1 文本 N-gram 布隆过滤器，子串查询

查询性能技巧

-- 1. 分区裁剪：只在需要的分区查询
SELECT count() FROM events 
WHERE event_date = '2026-07-21';           -- ✓ 只扫一个分区

SELECT count() FROM events 
WHERE event_time >= '2026-07-21 00:00:00'; -- ✗ 扫所有分区（没有用分区列过滤）

-- 2. PREWHERE 优化（先过滤再读取其他列）
SELECT user_id, event_data FROM events
PREWHERE event_type = 'click'              -- 先只读 event_type 列过滤
WHERE event_type = 'click' AND user_id > 1000;

-- 3. 避免高基数 GROUP BY
-- ✓ 好：GROUP BY 维度少
SELECT event_type, count() FROM events GROUP BY event_type;

-- ✗ 坏：GROUP BY user_id（百万级分组，内存爆炸）
SELECT user_id, count() FROM events GROUP BY user_id;
-- 替代方案：先采样或用近似函数
SELECT user_id, count() FROM events GROUP BY user_id LIMIT 100;
-- 或用近似去重
SELECT uniqExact(user_id) FROM events;

-- 4. 近似函数（大数据量时接受精度换速度）
SELECT uniq(user_id) FROM events;         -- 近似去重（HyperLogLog）
SELECT uniqExact(user_id) FROM events;    -- 精确去重（慢）
SELECT quantile(0.99)(latency) FROM events;  -- 近似分位数

⚠️ 避坑 #4：SELECT * 是 ClickHouse 的性能杀手。列存数据库读取每一列都要单独 I/O，查 100 列比查 5 列慢 20 倍。永远只 SELECT 需要的列。

集群架构

分片 + 副本

                    ┌──────────────────┐
   应用 ──写入──→  │  ClickHouse 节点  │ (分片1 副本1)
                    └────────┬─────────┘
                             │ ReplicatedMergeTree 复制
                    ┌────────▼─────────┐
                    │  ClickHouse 节点  │ (分片1 副本2)
                    └──────────────────┘

    分片1 [副本1, 副本2]     分片2 [副本1, 副本2]
    数据范围: user_id % 2=0   数据范围: user_id % 2=1
  • 分片（Shard）：数据水平切分到多节点，提升写入吞吐和存储容量
  • 副本（Replica）：同一分片的数据冗余，高可用 + 读分散

ZooKeeper 配置（副本必需）

<!-- /etc/clickhouse-server/config.xml -->
<zookeeper>
    <node>
        <host>zk1.internal</host>
        <port>2181</port>
    </node>
    <node>
        <host>zk2.internal</host>
        <port>2181</port>
    </node>
    <node>
        <host>zk3.internal</host>
        <port>2181</port>
    </node>
</zookeeper>

<!-- 集群定义 -->
<remote_servers>
    <analytics_cluster>
        <shard>
            <replica>
                <host>ch1.internal</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>ch2.internal</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
            <replica>
                <host>ch3.internal</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>ch4.internal</host>
                <port>9000</port>
            </replica>
        </shard>
    </analytics_cluster>
</remote_servers>

ReplicatedMergeTree 建表

-- 在所有节点上执行（相同语句）
CREATE TABLE events_replicated (
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String)
) ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/events_replicated',  -- ZK 路径（{shard} 宏自动替换）
    '{replica}'                                        -- 副本名（{replica} 宏自动替换）
)
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

<!-- 每个节点的 config.xml 定义宏 -->
<macros>
    <shard>01</shard>     <!-- ch1/ch2: 01, ch3/ch4: 02 -->
    <replica>ch1</replica> <!-- 每节点不同 -->
</macros>

Distributed 表（查询路由）

-- 本地表（每个分片上各自存一部分数据）
CREATE TABLE events_local ON CLUSTER analytics_cluster (
    ...
) ENGINE = ReplicatedMergeTree(...) ...;

-- 分布式表（查询入口，自动路由到各分片）
CREATE TABLE events_all ON CLUSTER analytics_cluster AS events_local
ENGINE = Distributed(
    analytics_cluster,   -- 集群名
    currentDatabase(),   -- 数据库
    events_local,        -- 本地表名
    rand()               -- 分片键（写入时用，查询时不影响路由）
);

-- 写入分布式表 → 自动分散到各分片
INSERT INTO events_all VALUES (...);

-- 查询分布式表 → 自动并行查所有分片聚合
SELECT event_type, count() FROM events_all GROUP BY event_type;

⚠️ 避坑 #5：写入分布式表有一个性能陷阱——数据先写入接收节点内存，再异步转发到各分片。如果接收节点宕机，内存中的未转发数据丢失。生产环境推荐直写本地表（应用层按分片键路由），分布式表只用于查询。

备份与恢复

clickhouse-backup 工具（推荐）

# 安装
wget https://github.com/Altinity/clickhouse-backup/releases/download/v2.0.0/clickhouse-backup-linux-amd64.tar.gz
tar xzf clickhouse-backup-linux-amd64.tar.gz
mv clickhouse-backup /usr/local/bin/

# 配置
cat > /etc/clickhouse-backup/config.yml << 'EOF'
general:
  remote_storage: s3
  disable_progress_bar: false
clickhouse:
  host: localhost
  port: 9000
  username: default
  password: 'StrongPassword123!'
s3:
  access_key: AKIAxxx
  secret_key: xxx
  bucket: clickhouse-backup
  region: cn-north-1
EOF

# 创建备份
clickhouse-backup create backup-20260721

# 上传到 S3
clickhouse-backup upload backup-20260721

# 恢复
clickhouse-backup download backup-20260721
clickhouse-backup restore backup-20260721

文件级备份（替代方案）

# 1. 冻结表（创建硬链接快照）
clickhouse-client --query "ALTER TABLE events FREEZE PARTITION '202607'"

# 2. 备份快照目录
rsync -av /var/lib/clickhouse/shadow/ /backup/clickhouse-$(date +%Y%m%d)/

# 3. 清理快照
clickhouse-client --query "ALTER TABLE events UNFREEZE PARTITION '202607'"

监控与运维

-- 关键运维查询
-- 1. 磁盘使用
SELECT 
    database, table, 
    formatReadableSize(sum(bytes_on_disk)) AS size,
    sum(rows) AS rows,
    count() AS parts
FROM system.parts 
WHERE active 
GROUP BY database, table 
ORDER BY size DESC;

-- 2. part 合并进度
SELECT database, table, elapsed, progress, num_parts 
FROM system.merges;

-- 3. 慢查询
SELECT query, elapsed, formatReadableSize(memory_usage) AS memory, read_rows 
FROM system.processes 
WHERE elapsed > 10 
ORDER BY elapsed DESC;

-- 4. 复制延迟
SELECT database, table, queue_size, log_pointer, last_queue_update 
FROM system.replicas 
WHERE is_readonly = 0;

-- 5. ZooKeeper 连接状态
SELECT name, host, port, is_connected 
FROM system.zookeeper_connection;

关键告警阈值：

指标 警告 严重 说明
磁盘使用 >80% >90% 需扩容或清理旧分区
part 数量 >500/表 >2000/表 merge 跟不上，需优化写入频率
复制队列 >100 >1000 副本同步滞后
慢查询 >30s >120s 需优化 SQL 或加索引
ZK 延迟 >100ms >1s ZooKeeper 性能瓶颈
内存使用 >85% >95% 需调 max_memory_usage

常见故障排查

故障 诊断 解决
Too many parts system.parts count 高 减少写入频率；增大 batch size
内存不足 Memory limit exceeded 调大 max_memory_usage；优化查询减少聚合维度
复制停止 system.replicas is_readonly=1 检查 ZooKeeper 连接；重启复制 SYSTEM RESTART REPLICA
查询超时 system.processes 找慢查询 KILL QUERY；加 PREWHERE/分区裁减
ZK 超时 system.zookeeper_connection ZK 集群扩容；清理 ZK 旧日志
磁盘满 df -h 删旧分区 ALTER TABLE ... DROP PARTITION
part 不合并 system.merges 为空 手动触发 OPTIMIZE TABLE events FINAL

⚠️ 避坑 #6：OPTIMIZE TABLE ... FINAL 会重写整个表的所有 part，极慢且消耗大量 I/O。只在 part 数量异常时用，不要当日常维护操作。

十条避坑清单

  1. 别用 ClickHouse 做 OLTP：没有事务、不支持高频点查、UPDATE 极慢——它是 OLAP 专用
  2. 分区别太细：按月分区是黄金标准，按天/小时分区会导致 part 数量爆炸
  3. 排序键决定性能：最常用的过滤条件放前面，低基数优先——选错了全表扫描
  4. SELECT * 是禁忌：列存数据库读 100 列比读 5 列慢 20 倍，只查需要的列
  5. 写入要批量：单次 1-10 万行，每秒不超 1 次 INSERT——太多小写入 = Too many parts
  6. ReplacingMergeTree 去重不实时：查询加 FINAL 性能差，设计时避免依赖实时去重
  7. 别直写 Distributed 表：数据先入内存再转发，宕机会丢——直写 Local 表更安全
  8. ZooKeeper 是命脉：副本依赖 ZK，ZK 挂了复制停摆——ZK 至少 3 节点，独立部署
  9. TTL 自动清理：日志类数据必设 TTL，否则磁盘迟早被撑爆
  10. 近似函数优先：大数据量用 uniq() 代替 count(DISTINCT)，精度够用速度快 10 倍

总结

ClickHouse 运维核心要点：

  • MergeTree 是根基——PARTITION BY 管分区裁剪，ORDER BY 管查询跳过，两者选对了查询就快
  • 写入要大不要频——大批量少频率，用 Buffer/Kafka 缓冲小写入
  • 集群 = 分片 + 副本——ReplicatedMergeTree + Distributed 是标配，ZooKeeper 是命脉
  • 查询只读必要列——列存 + PREWHERE + 分区裁剪 + 近似函数，四板斧覆盖 90% 优化
  • 监控 part 数量和复制队列——这两个指标最能反映集群健康度

掌握表引擎选择、排序键设计、集群部署、查询优化这四块，ClickHouse 生产运维就能覆盖日志分析、监控指标、用户行为分析等主流 OLAP 场景。

分享：

评论区