ClickHouseNotes

附录：附录：ClickHouse 速查手册

zjc 于 2026-02-02 发布

这是《ClickHouse 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。

连接与查看

clickhouse-client --host ch-01 --port 9000 --user default
curl 'http://ch-01:8123/?query=SELECT%201'

SELECT version();
SHOW DATABASES;
SHOW TABLES FROM analytics;
DESCRIBE TABLE analytics.events_local;
SHOW CREATE TABLE analytics.events_local;

建表模板

CREATE TABLE analytics.events_local
(
    event_date Date,
    event_time DateTime,
    event_id String,
    user_id UInt64,
    event_type LowCardinality(String),
    city_id UInt32,
    amount Decimal64(2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type, city_id, user_id)
TTL event_date + INTERVAL 13 MONTH;

常用引擎

引擎 语义
MergeTree 通用明细
ReplacingMergeTree 按排序键和版本替换
SummingMergeTree 数值列求和
CollapsingMergeTree sign 抵消
VersionedCollapsingMergeTree 版本化抵消
ReplicatedMergeTree 副本
Distributed 分片入口
Kafka 消息消费
Memory 内存临时表

最新状态查询

SELECT
    order_id,
    argMax(order_status, version) AS status,
    argMax(amount, version) AS amount,
    max(updated_at) AS updated_at
FROM analytics.orders_local
GROUP BY order_id;

写入

clickhouse-client --query "INSERT INTO analytics.events_local FORMAT JSONEachRow" < events.jsonl
clickhouse-client --query "INSERT INTO analytics.events_local FORMAT CSVWithNames" < events.csv
clickhouse-client --query "INSERT INTO analytics.events_local FORMAT Parquet" < events.parquet

推荐写入：

单批 1 万到 10 万行
间隔数百毫秒到数秒
固定并发
失败重试并死信

查询优化

SELECT
    query_id,
    query,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 30;

检查顺序：

分区条件
排序键前缀
读取列
过滤条件
聚合基数
JOIN 大小
资源限制

运行中查询

SELECT query_id, elapsed, read_rows, memory_usage, query
FROM system.processes
ORDER BY elapsed DESC;

KILL QUERY WHERE query_id = 'target-query-id';

part 与合并

SELECT table, partition, count() AS parts, sum(rows) AS rows
FROM system.parts
WHERE active
GROUP BY table, partition
ORDER BY parts DESC;

SELECT database, table, elapsed, num_parts
FROM system.merges;

副本状态

SELECT
    database,
    table,
    replica_name,
    is_readonly,
    queue_size,
    absolute_delay
FROM system.replicas;

集群与磁盘

SELECT cluster, shard_num, replica_num, host_name, is_local
FROM system.clusters;

SELECT name, path, total_space, free_space
FROM system.disks
ORDER BY free_space;

Kafka 消费

SELECT database, table, consumer_id, assignments.topic
FROM system.kafka_consumers;

SYSTEM STOP CONSUMERS analytics.kafka_events;
SYSTEM START CONSUMERS analytics.kafka_events;

权限

CREATE ROLE analyst_ro;
GRANT SELECT ON analytics.* TO analyst_ro;

CREATE USER analyst IDENTIFIED BY 'strong-password';
GRANT analyst_ro TO analyst;

常见错误

错误 方向
Too many parts 小写入、分区过多、合并慢
MEMORY_LIMIT_EXCEEDED 高基数聚合、大 JOIN、扫描过大
Disk space insufficient TTL、扩容、停写
Replica is readonly Keeper、元数据、恢复状态
Timeout 查询成本、资源争抢
DNS error 域名、网络、远程节点

本章小结

本速查手册汇总连接、建表、写入、查询、运维和排障的常用命令。生产使用时要以当前版本文档为准，并把团队常用 SQL 固化到运维平台中。