内容阅读

在知识库中专注阅读，并随时返回相关工具与课程

运维管理19 分钟阅读

ClickHouse 运维命令 100 条

ClickHouse 的运维思路与传统 OLTP 数据库差异很大。很多问题并不是行锁或事务阻塞，而是分区设计不合理、数据分片不均、后台合并堆积、Mutation 长时间未完成、复制队列阻塞、查询内存超限或者磁盘上的数据 Part 数量过多。

2026年7月31日阅读—点赞—收藏—

dba100clickhouse墨力计划

打开目录

前言

下面整理了 ClickHouse DBA 日常使用频率较高的 100 条命令，覆盖连接、数据库对象、MergeTree 表、分区、查询诊断、系统指标、数据 Part、Mutation、分布式集群、复制、用户权限、备份恢复和服务日志等场景。

本文主要面向当前主流 ClickHouse 版本。不同版本、开源自建环境和 ClickHouse Cloud 之间可能存在差异，执行前应先确认版本、部署架构和账号权限。文中的集群名、数据库名、表名、路径、IP 和用户均为示例。

DROP、TRUNCATE、KILL、ALTER DELETE、OPTIMIZE FINAL、停止后台合并、恢复副本和恢复备份等操作具有风险，生产环境执行前必须确认影响范围。

一、连接与基础信息

1. 使用 clickhouse-client 连接数据库

Bash

1clickhouse-client \
2  --host 192.168.1.10 \
3  --port 9000 \
4  --user dba \
5  --password

指定数据库：

Bash

1clickhouse-client --host 192.168.1.10 --database appdb --user dba --password

2. 通过 HTTPS 接口执行查询

Bash

1curl -sS 'https://clickhouse.example.com:8443/?query=SELECT%201'

生产环境应使用认证和 TLS，避免将密码直接写入命令历史。

3. 查看 ClickHouse 版本

SQL

1SELECT version();

4. 查看当前节点名称

SQL

1SELECT hostName();

5. 查看当前数据库和用户

SQL

1SELECT
2    currentDatabase(),
3    currentUser();

6. 查看服务器时区

SQL

1SELECT
2    timezone(),
3    now(),
4    now('UTC');

7. 查看服务器运行时间

SQL

1SELECT
2    uptime() AS uptime_seconds,
3    formatReadableTimeDelta(uptime()) AS uptime;

8. 查看服务器端口

SQL

1SELECT
2    name,
3    value
4FROM system.server_settings
5WHERE name IN ('tcp_port', 'http_port', 'https_port', 'tcp_port_secure');

9. 查看构建选项

SQL

1SELECT *
2FROM system.build_options
3ORDER BY name;

10. 查看当前节点告警

SQL

1SELECT *
2FROM system.warnings;

二、数据库、表与元数据

11. 查看所有数据库

SQL

1SHOW DATABASES;

详细查看：

SQL

1SELECT name, engine, data_path, metadata_path
2FROM system.databases
3ORDER BY name;

12. 创建数据库

SQL

1CREATE DATABASE appdb;

集群范围创建：

SQL

1CREATE DATABASE appdb ON CLUSTER production_cluster;

13. 查看当前数据库中的表

SQL

1SHOW TABLES FROM appdb;

14. 查看表结构

SQL

1DESCRIBE TABLE appdb.events;

15. 查看建表语句

SQL

1SHOW CREATE TABLE appdb.events;

16. 查看所有表引擎

SQL

1SELECT *
2FROM system.table_engines
3ORDER BY name;

17. 查看表引擎和排序键

SQL

1SELECT
2    database,
3    name,
4    engine,
5    partition_key,
6    sorting_key,
7    primary_key,
8    total_rows,
9    total_bytes
10FROM system.tables
11WHERE database = 'appdb'
12ORDER BY total_bytes DESC;

18. 修改表名

SQL

1RENAME TABLE appdb.events TO appdb.events_old;

19. 清空表

SQL

1TRUNCATE TABLE appdb.stage_events;

集群范围执行：

SQL

1TRUNCATE TABLE appdb.stage_events ON CLUSTER production_cluster;

20. 删除表

SQL

1DROP TABLE appdb.events_old;

删除不可回退，必须先确认备份和依赖关系。

三、MergeTree 与表结构管理

21. 创建 MergeTree 表

SQL

1CREATE TABLE appdb.events
2(
3    event_date Date,
4    event_time DateTime,
5    user_id UInt64,
6    event_type LowCardinality(String),
7    payload String
8)
9ENGINE = MergeTree
10PARTITION BY toYYYYMM(event_date)
11ORDER BY (event_date, user_id, event_time);

ORDER BY 决定数据物理排序和稀疏索引结构，是 ClickHouse 表设计中最重要的部分之一。

22. 创建 ReplicatedMergeTree 表

SQL

1CREATE TABLE appdb.events_local ON CLUSTER production_cluster
2(
3    event_date Date,
4    event_time DateTime,
5    user_id UInt64,
6    event_type LowCardinality(String)
7)
8ENGINE = ReplicatedMergeTree(
9    '/clickhouse/tables/{shard}/appdb/events_local',
10    '{replica}'
11)
12PARTITION BY toYYYYMM(event_date)
13ORDER BY (event_date, user_id, event_time);

23. 创建 Distributed 表

SQL

1CREATE TABLE appdb.events_all ON CLUSTER production_cluster
2AS appdb.events_local
3ENGINE = Distributed(
4    production_cluster,
5    appdb,
6    events_local,
7    cityHash64(user_id)
8);

24. 增加字段

SQL

1ALTER TABLE appdb.events
2ADD COLUMN source LowCardinality(String) DEFAULT 'unknown';

25. 修改字段类型

SQL

1ALTER TABLE appdb.events
2MODIFY COLUMN payload String;

修改类型可能触发数据重写，应评估表规模和兼容性。

26. 删除字段

SQL

1ALTER TABLE appdb.events
2DROP COLUMN payload;

27. 增加跳数索引

SQL

1ALTER TABLE appdb.events
2ADD INDEX idx_event_type event_type TYPE set(100) GRANULARITY 4;

对已有数据物化索引：

SQL

1ALTER TABLE appdb.events
2MATERIALIZE INDEX idx_event_type;

28. 查看表索引

SQL

1SELECT
2    database,
3    table,
4    name,
5    type,
6    expression,
7    granularity
8FROM system.data_skipping_indices
9WHERE database = 'appdb'
10  AND table = 'events';

29. 增加 TTL

SQL

1ALTER TABLE appdb.events
2MODIFY TTL event_date + INTERVAL 180 DAY DELETE;

30. 物化 TTL

SQL

1ALTER TABLE appdb.events
2MATERIALIZE TTL;

该操作可能触发大量后台合并和数据删除，应在低峰期评估执行。

四、数据查询与导入导出

31. 插入数据

SQL

1INSERT INTO appdb.events
2VALUES
3(
4    '2026-07-27',
5    '2026-07-27 10:00:00',
6    1001,
7    'login',
8    '{}'
9);

32. 从查询结果插入数据

SQL

1INSERT INTO appdb.events
2SELECT *
3FROM appdb.events_stage
4WHERE event_date = '2026-07-27';

33. 查询表行数

SQL

1SELECT count()
2FROM appdb.events;

34. 查看近一天数据量

SQL

1SELECT
2    event_type,
3    count() AS rows
4FROM appdb.events
5WHERE event_time >= now() - INTERVAL 1 DAY
6GROUP BY event_type
7ORDER BY rows DESC;

35. 查看查询执行计划

SQL

1EXPLAIN
2SELECT count()
3FROM appdb.events
4WHERE user_id = 1001;

36. 查看执行管道

SQL

1EXPLAIN PIPELINE
2SELECT count()
3FROM appdb.events
4WHERE event_date >= today() - 7;

37. 查看索引裁剪信息

SQL

1EXPLAIN indexes = 1
2SELECT *
3FROM appdb.events
4WHERE event_date = today()
5  AND user_id = 1001;

38. 导出 CSV

Bash

1clickhouse-client \
2  --query="SELECT * FROM appdb.events FORMAT CSVWithNames" \
3  > events.csv

39. 导入 CSV

Bash

1clickhouse-client \
2  --query="INSERT INTO appdb.events FORMAT CSV" \
3  < events.csv

如果文件包含表头，应使用与文件匹配的格式。

40. 使用 clickhouse-local 查询文件

Bash

1clickhouse-local \
2  --file events.csv \
3  --input-format CSVWithNames \
4  --query "SELECT event_type, count() FROM table GROUP BY event_type"

五、会话、查询与性能诊断

41. 查看正在执行的查询

SQL

1SELECT
2    query_id,
3    user,
4    address,
5    elapsed,
6    read_rows,
7    read_bytes,
8    memory_usage,
9    query
10FROM system.processes
11ORDER BY elapsed DESC;

42. 查看集群全部节点上的查询

SQL

1SELECT
2    hostName() AS host,
3    query_id,
4    user,
5    elapsed,
6    memory_usage,
7    query
8FROM clusterAllReplicas('production_cluster', system.processes)
9ORDER BY elapsed DESC;

43. 终止指定查询

SQL

1KILL QUERY
2WHERE query_id = 'query-id'
3SYNC;

44. 查看近期执行失败的 SQL

SQL

1SELECT
2    event_time,
3    query_id,
4    user,
5    exception_code,
6    exception,
7    query
8FROM system.query_log
9WHERE type IN ('ExceptionBeforeStart', 'ExceptionWhileProcessing')
10  AND event_time >= now() - INTERVAL 1 HOUR
11ORDER BY event_time DESC;

45. 查看耗时最高的 SQL

SQL

1SELECT
2    query_id,
3    user,
4    query_duration_ms,
5    read_rows,
6    formatReadableSize(read_bytes) AS read_size,
7    formatReadableSize(memory_usage) AS memory,
8    query
9FROM system.query_log
10WHERE type = 'QueryFinish'
11  AND event_time >= now() - INTERVAL 1 HOUR
12ORDER BY query_duration_ms DESC
13LIMIT 20;

46. 查看内存消耗最高的 SQL

SQL

1SELECT
2    query_id,
3    user,
4    formatReadableSize(memory_usage) AS memory,
5    query_duration_ms,
6    query
7FROM system.query_log
8WHERE type = 'QueryFinish'
9  AND event_time >= now() - INTERVAL 1 HOUR
10ORDER BY memory_usage DESC
11LIMIT 20;

47. 查看当前指标

SQL

1SELECT
2    metric,
3    value,
4    description
5FROM system.metrics
6ORDER BY metric;

48. 查看累计事件

SQL

1SELECT
2    event,
3    value,
4    description
5FROM system.events
6ORDER BY value DESC;

49. 查看异步指标

SQL

1SELECT
2    metric,
3    value
4FROM system.asynchronous_metrics
5ORDER BY metric;

50. 刷新系统日志表

SQL

1SYSTEM FLUSH LOGS;

执行后再查询 system.query_log，可以减少日志尚未落表造成的遗漏。

六、磁盘、数据 Part 与 Mutation

51. 查看磁盘空间

SQL

1SELECT
2    name,
3    path,
4    formatReadableSize(free_space) AS free,
5    formatReadableSize(total_space) AS total,
6    round(free_space * 100 / total_space, 2) AS free_pct
7FROM system.disks;

52. 查看存储策略

SQL

1SELECT *
2FROM system.storage_policies
3ORDER BY policy_name, volume_name, volume_priority;

53. 查看大表排行

SQL

1SELECT
2    database,
3    table,
4    sum(rows) AS rows,
5    formatReadableSize(sum(bytes_on_disk)) AS disk_size
6FROM system.parts
7WHERE active
8GROUP BY database, table
9ORDER BY sum(bytes_on_disk) DESC
10LIMIT 20;

54. 查看分区大小

SQL

1SELECT
2    database,
3    table,
4    partition,
5    sum(rows) AS rows,
6    count() AS parts,
7    formatReadableSize(sum(bytes_on_disk)) AS disk_size
8FROM system.parts
9WHERE active
10  AND database = 'appdb'
11  AND table = 'events'
12GROUP BY database, table, partition
13ORDER BY partition;

55. 查看 Part 数量

SQL

1SELECT
2    database,
3    table,
4    count() AS active_parts
5FROM system.parts
6WHERE active
7GROUP BY database, table
8ORDER BY active_parts DESC;

Part 数量过多通常与写入批次过小或后台合并跟不上有关。

56. 查看正在进行的合并

SQL

1SELECT
2    database,
3    table,
4    elapsed,
5    progress,
6    num_parts,
7    result_part_name,
8    formatReadableSize(total_size_bytes_compressed) AS size
9FROM system.merges
10ORDER BY elapsed DESC;

57. 强制合并数据

SQL

1OPTIMIZE TABLE appdb.events FINAL;

FINAL 可能产生大量 CPU、磁盘 I/O 和临时空间消耗，不应作为日常定时维护命令。

58. 查看 Mutation

SQL

1SELECT
2    database,
3    table,
4    mutation_id,
5    command,
6    create_time,
7    parts_to_do,
8    is_done,
9    latest_fail_reason
10FROM system.mutations
11ORDER BY create_time DESC;

59. 异步删除数据

SQL

1ALTER TABLE appdb.events
2DELETE WHERE event_date < today() - 180;

该操作会创建 Mutation。大范围删除优先考虑按分区删除。

60. 删除整个分区

SQL

1ALTER TABLE appdb.events
2DROP PARTITION '202601';

按分区删除通常比行级 Mutation 更高效，但必须确认分区表达式和目标分区值。

七、分布式集群与复制

61. 查看集群拓扑

SQL

1SELECT
2    cluster,
3    shard_num,
4    replica_num,
5    host_name,
6    host_address,
7    port,
8    is_local
9FROM system.clusters
10ORDER BY cluster, shard_num, replica_num;

62. 在集群所有节点执行查询

SQL

1SELECT
2    hostName() AS host,
3    version()
4FROM clusterAllReplicas('production_cluster', system.one);

63. 查看副本状态

SQL

1SELECT
2    database,
3    table,
4    is_leader,
5    is_readonly,
6    is_session_expired,
7    queue_size,
8    inserts_in_queue,
9    merges_in_queue,
10    absolute_delay,
11    zookeeper_exception
12FROM system.replicas
13ORDER BY absolute_delay DESC;

64. 查看复制队列

SQL

1SELECT
2    database,
3    table,
4    replica_name,
5    position,
6    node_name,
7    type,
8    create_time,
9    num_tries,
10    last_exception
11FROM system.replication_queue
12ORDER BY create_time;

65. 等待副本同步

SQL

1SYSTEM SYNC REPLICA appdb.events_local;

66. 重启单表副本状态

SQL

1SYSTEM RESTART REPLICA appdb.events_local;

执行期间表会短暂不可用，只应在确认副本状态异常后使用。

67. 查看分布式发送队列

SQL

1SELECT
2    database,
3    table,
4    data_path,
5    is_blocked,
6    error_count,
7    last_exception
8FROM system.distribution_queue
9ORDER BY error_count DESC;

68. 刷新 Distributed 表发送队列

SQL

1SYSTEM FLUSH DISTRIBUTED appdb.events_all;

69. 查看 Keeper/ZooKeeper 连接状态

SQL

1SELECT *
2FROM system.zookeeper_connection;

70. 查看 ON CLUSTER DDL 队列

SQL

1SELECT
2    entry,
3    host,
4    port,
5    status,
6    exception_code,
7    exception_text,
8    query
9FROM system.distributed_ddl_queue
10ORDER BY entry DESC;

八、用户、权限与参数

71. 查看用户

SQL

1SHOW USERS;

详细查看：

SQL

1SELECT *
2FROM system.users;

72. 创建用户

SQL

1CREATE USER appuser
2IDENTIFIED WITH sha256_password BY 'Replace_With_Strong_Password';

73. 修改用户密码

SQL

1ALTER USER appuser
2IDENTIFIED WITH sha256_password BY 'Replace_With_New_Strong_Password';

74. 创建角色

SQL

1CREATE ROLE readonly_role;

75. 授予只读权限

SQL

1GRANT SELECT ON appdb.* TO readonly_role;
2GRANT readonly_role TO appuser;

76. 回收权限

SQL

1REVOKE SELECT ON appdb.* FROM readonly_role;

77. 查看授权

SQL

1SHOW GRANTS FOR appuser;

78. 查看当前会话参数

SQL

1SELECT
2    name,
3    value,
4    changed,
5    description
6FROM system.settings
7ORDER BY name;

79. 修改当前会话内存限制

SQL

1SET max_memory_usage = 10000000000;

该设置只影响当前会话，具体值应结合节点内存和并发量确定。

80. 设置查询最大执行时间

SQL

1SET max_execution_time = 300;

九、备份、恢复与数据维护

81. 备份单表到本地备份磁盘

SQL

1BACKUP TABLE appdb.events
2TO Disk('backups', 'appdb_events_20260727.zip');

需要先在服务器配置中定义 backups 磁盘。

82. 备份整个数据库

SQL

1BACKUP DATABASE appdb
2TO Disk('backups', 'appdb_20260727.zip');

83. 异步执行备份

SQL

1BACKUP DATABASE appdb
2TO Disk('backups', 'appdb_async_20260727.zip')
3ASYNC;

84. 查看备份任务

SQL

1SELECT *
2FROM system.backups
3ORDER BY start_time DESC;

85. 恢复单表

SQL

1RESTORE TABLE appdb.events
2FROM Disk('backups', 'appdb_events_20260727.zip');

86. 恢复为新表

SQL

1RESTORE TABLE appdb.events AS appdb.events_restore
2FROM Disk('backups', 'appdb_events_20260727.zip');

恢复到新表更适合先做数据校验。

87. 冻结表分区

SQL

1ALTER TABLE appdb.events
2FREEZE PARTITION '202607'
3WITH NAME 'events_202607';

FREEZE 生成硬链接快照，但不等同于完整的异地备份。

88. 解除冻结备份

SQL

1SYSTEM UNFREEZE WITH NAME 'events_202607';

89. 停止指定表后台合并

SQL

1SYSTEM STOP MERGES appdb.events;

90. 恢复指定表后台合并

SQL

1SYSTEM START MERGES appdb.events;

长期停止合并会造成 Part 堆积，只能作为短期故障处理手段。

十、服务、日志与巡检

91. 查看 ClickHouse 服务状态

Bash

1systemctl status clickhouse-server

92. 启动 ClickHouse

Bash

1systemctl start clickhouse-server

93. 停止 ClickHouse

Bash

1systemctl stop clickhouse-server

停库前应确认业务、复制和后台任务状态。

94. 重启 ClickHouse

Bash

1systemctl restart clickhouse-server

95. 查看服务日志

Bash

1journalctl -u clickhouse-server --since "1 hour ago"

96. 查看默认日志文件

Bash

1tail -200 /var/log/clickhouse-server/clickhouse-server.log
2tail -200 /var/log/clickhouse-server/clickhouse-server.err.log

实际路径以配置文件为准。

97. 查看 ClickHouse 进程

Bash

1ps -ef | grep '[c]lickhouse-server'

98. 查看监听端口

Bash

1ss -lntp | grep -E ':(8123|9000|9009|8443|9440)\b'

不同协议和安全配置使用的端口可能不同。

99. 查看磁盘和 I/O

Bash

1df -h
2df -i
3iostat -x 1 5

ClickHouse 对磁盘吞吐和延迟较敏感，空间告警还要结合 Part、Merge、Mutation 和复制队列一起分析。

100. 执行快速巡检摘要

SQL

1SELECT 'running_queries' AS item, toString(count()) AS value
2FROM system.processes
3UNION ALL
4SELECT 'active_merges', toString(count())
5FROM system.merges
6UNION ALL
7SELECT 'unfinished_mutations', toString(count())
8FROM system.mutations
9WHERE NOT is_done
10UNION ALL
11SELECT 'replica_queue', toString(sum(queue_size))
12FROM system.replicas
13UNION ALL
14SELECT 'disk_free',
15       arrayStringConcat(
16           groupArray(concat(name, ':', formatReadableSize(free_space))),
17           ', '
18       )
19FROM system.disks;

结语

ClickHouse DBA 排查问题时，不能只盯着 CPU 和一条慢 SQL。更有效的顺序通常是先确认磁盘空间和节点状态，再看 system.processes、system.query_log、system.parts、system.merges、system.mutations、system.replicas 和 system.replication_queue。

如果 Part 数量持续增长，通常要检查写入批次和合并能力；如果 Mutation 长时间不结束，要判断是否扫描和重写了过多数据；如果副本延迟，则要进一步区分网络、Keeper、复制队列和磁盘 I/O 问题。

真正适合生产环境的 ClickHouse 运维，不是频繁执行 OPTIMIZE FINAL，而是通过合理的分区、排序键、批量写入、存储策略和复制设计，让后台任务能够长期稳定运行。

官方资料

ClickHouse 官方文档：https://clickhouse.com/docs/
系统表：https://clickhouse.com/docs/reference/system-tables/overview
SYSTEM 命令：https://clickhouse.com/docs/reference/statements/system
备份与恢复：https://clickhouse.com/docs/operations/backup/overview

DBA 实战课程系统学习 Oracle DBA查看完整课程

前言

DROP、TRUNCATE、KILL、ALTER DELETE、OPTIMIZE FINAL、停止后台合并、恢复副本和恢复备份等操作具有风险，生产环境执行前必须确认影响范围。

一、连接与基础信息

1. 使用 clickhouse-client 连接数据库

Bash

1clickhouse-client \
2  --host 192.168.1.10 \
3  --port 9000 \
4  --user dba \
5  --password

指定数据库：

Bash

1clickhouse-client --host 192.168.1.10 --database appdb --user dba --password

2. 通过 HTTPS 接口执行查询

Bash

1curl -sS 'https://clickhouse.example.com:8443/?query=SELECT%201'

生产环境应使用认证和 TLS，避免将密码直接写入命令历史。

3. 查看 ClickHouse 版本

SQL

1SELECT version();

4. 查看当前节点名称

SQL

1SELECT hostName();

5. 查看当前数据库和用户

SQL

1SELECT
2    currentDatabase(),
3    currentUser();

6. 查看服务器时区

SQL

1SELECT
2    timezone(),
3    now(),
4    now('UTC');

7. 查看服务器运行时间

SQL

1SELECT
2    uptime() AS uptime_seconds,
3    formatReadableTimeDelta(uptime()) AS uptime;

8. 查看服务器端口

SQL

1SELECT
2    name,
3    value
4FROM system.server_settings
5WHERE name IN ('tcp_port', 'http_port', 'https_port', 'tcp_port_secure');

9. 查看构建选项

SQL

1SELECT *
2FROM system.build_options
3ORDER BY name;

10. 查看当前节点告警

SQL

1SELECT *
2FROM system.warnings;

二、数据库、表与元数据

11. 查看所有数据库

SQL

1SHOW DATABASES;

详细查看：

SQL

1SELECT name, engine, data_path, metadata_path
2FROM system.databases
3ORDER BY name;

12. 创建数据库

SQL

1CREATE DATABASE appdb;

集群范围创建：

SQL

1CREATE DATABASE appdb ON CLUSTER production_cluster;

13. 查看当前数据库中的表

SQL

1SHOW TABLES FROM appdb;

14. 查看表结构

SQL

1DESCRIBE TABLE appdb.events;

15. 查看建表语句

SQL

1SHOW CREATE TABLE appdb.events;

16. 查看所有表引擎

SQL

1SELECT *
2FROM system.table_engines
3ORDER BY name;

17. 查看表引擎和排序键

SQL

1SELECT
2    database,
3    name,
4    engine,
5    partition_key,
6    sorting_key,
7    primary_key,
8    total_rows,
9    total_bytes
10FROM system.tables
11WHERE database = 'appdb'
12ORDER BY total_bytes DESC;

18. 修改表名

SQL

1RENAME TABLE appdb.events TO appdb.events_old;

19. 清空表

SQL

1TRUNCATE TABLE appdb.stage_events;

集群范围执行：

SQL

1TRUNCATE TABLE appdb.stage_events ON CLUSTER production_cluster;

20. 删除表

SQL

1DROP TABLE appdb.events_old;

删除不可回退，必须先确认备份和依赖关系。

三、MergeTree 与表结构管理

21. 创建 MergeTree 表

SQL

1CREATE TABLE appdb.events
2(
3    event_date Date,
4    event_time DateTime,
5    user_id UInt64,
6    event_type LowCardinality(String),
7    payload String
8)
9ENGINE = MergeTree
10PARTITION BY toYYYYMM(event_date)
11ORDER BY (event_date, user_id, event_time);

ORDER BY 决定数据物理排序和稀疏索引结构，是 ClickHouse 表设计中最重要的部分之一。

22. 创建 ReplicatedMergeTree 表

SQL

1CREATE TABLE appdb.events_local ON CLUSTER production_cluster
2(
3    event_date Date,
4    event_time DateTime,
5    user_id UInt64,
6    event_type LowCardinality(String)
7)
8ENGINE = ReplicatedMergeTree(
9    '/clickhouse/tables/{shard}/appdb/events_local',
10    '{replica}'
11)
12PARTITION BY toYYYYMM(event_date)
13ORDER BY (event_date, user_id, event_time);

23. 创建 Distributed 表

SQL

1CREATE TABLE appdb.events_all ON CLUSTER production_cluster
2AS appdb.events_local
3ENGINE = Distributed(
4    production_cluster,
5    appdb,
6    events_local,
7    cityHash64(user_id)
8);

24. 增加字段

SQL

1ALTER TABLE appdb.events
2ADD COLUMN source LowCardinality(String) DEFAULT 'unknown';

25. 修改字段类型

SQL

1ALTER TABLE appdb.events
2MODIFY COLUMN payload String;

修改类型可能触发数据重写，应评估表规模和兼容性。

26. 删除字段

SQL

1ALTER TABLE appdb.events
2DROP COLUMN payload;

27. 增加跳数索引

SQL

1ALTER TABLE appdb.events
2ADD INDEX idx_event_type event_type TYPE set(100) GRANULARITY 4;

对已有数据物化索引：

SQL

1ALTER TABLE appdb.events
2MATERIALIZE INDEX idx_event_type;

28. 查看表索引

SQL

1SELECT
2    database,
3    table,
4    name,
5    type,
6    expression,
7    granularity
8FROM system.data_skipping_indices
9WHERE database = 'appdb'
10  AND table = 'events';

29. 增加 TTL

SQL

1ALTER TABLE appdb.events
2MODIFY TTL event_date + INTERVAL 180 DAY DELETE;

30. 物化 TTL

SQL

1ALTER TABLE appdb.events
2MATERIALIZE TTL;

该操作可能触发大量后台合并和数据删除，应在低峰期评估执行。

四、数据查询与导入导出

31. 插入数据

SQL

1INSERT INTO appdb.events
2VALUES
3(
4    '2026-07-27',
5    '2026-07-27 10:00:00',
6    1001,
7    'login',
8    '{}'
9);

32. 从查询结果插入数据

SQL

1INSERT INTO appdb.events
2SELECT *
3FROM appdb.events_stage
4WHERE event_date = '2026-07-27';

33. 查询表行数

SQL

1SELECT count()
2FROM appdb.events;

34. 查看近一天数据量

SQL

1SELECT
2    event_type,
3    count() AS rows
4FROM appdb.events
5WHERE event_time >= now() - INTERVAL 1 DAY
6GROUP BY event_type
7ORDER BY rows DESC;

35. 查看查询执行计划

SQL

1EXPLAIN
2SELECT count()
3FROM appdb.events
4WHERE user_id = 1001;

36. 查看执行管道

SQL

1EXPLAIN PIPELINE
2SELECT count()
3FROM appdb.events
4WHERE event_date >= today() - 7;

37. 查看索引裁剪信息

SQL

1EXPLAIN indexes = 1
2SELECT *
3FROM appdb.events
4WHERE event_date = today()
5  AND user_id = 1001;

38. 导出 CSV

Bash

1clickhouse-client \
2  --query="SELECT * FROM appdb.events FORMAT CSVWithNames" \
3  > events.csv

39. 导入 CSV

Bash

1clickhouse-client \
2  --query="INSERT INTO appdb.events FORMAT CSV" \
3  < events.csv

如果文件包含表头，应使用与文件匹配的格式。

40. 使用 clickhouse-local 查询文件

Bash

1clickhouse-local \
2  --file events.csv \
3  --input-format CSVWithNames \
4  --query "SELECT event_type, count() FROM table GROUP BY event_type"

五、会话、查询与性能诊断

41. 查看正在执行的查询

SQL

1SELECT
2    query_id,
3    user,
4    address,
5    elapsed,
6    read_rows,
7    read_bytes,
8    memory_usage,
9    query
10FROM system.processes
11ORDER BY elapsed DESC;

42. 查看集群全部节点上的查询

SQL

1SELECT
2    hostName() AS host,
3    query_id,
4    user,
5    elapsed,
6    memory_usage,
7    query
8FROM clusterAllReplicas('production_cluster', system.processes)
9ORDER BY elapsed DESC;

43. 终止指定查询

SQL

1KILL QUERY
2WHERE query_id = 'query-id'
3SYNC;

44. 查看近期执行失败的 SQL

SQL

1SELECT
2    event_time,
3    query_id,
4    user,
5    exception_code,
6    exception,
7    query
8FROM system.query_log
9WHERE type IN ('ExceptionBeforeStart', 'ExceptionWhileProcessing')
10  AND event_time >= now() - INTERVAL 1 HOUR
11ORDER BY event_time DESC;

45. 查看耗时最高的 SQL

SQL

1SELECT
2    query_id,
3    user,
4    query_duration_ms,
5    read_rows,
6    formatReadableSize(read_bytes) AS read_size,
7    formatReadableSize(memory_usage) AS memory,
8    query
9FROM system.query_log
10WHERE type = 'QueryFinish'
11  AND event_time >= now() - INTERVAL 1 HOUR
12ORDER BY query_duration_ms DESC
13LIMIT 20;

46. 查看内存消耗最高的 SQL

SQL

1SELECT
2    query_id,
3    user,
4    formatReadableSize(memory_usage) AS memory,
5    query_duration_ms,
6    query
7FROM system.query_log
8WHERE type = 'QueryFinish'
9  AND event_time >= now() - INTERVAL 1 HOUR
10ORDER BY memory_usage DESC
11LIMIT 20;

47. 查看当前指标

SQL

1SELECT
2    metric,
3    value,
4    description
5FROM system.metrics
6ORDER BY metric;

48. 查看累计事件

SQL

1SELECT
2    event,
3    value,
4    description
5FROM system.events
6ORDER BY value DESC;

49. 查看异步指标

SQL

1SELECT
2    metric,
3    value
4FROM system.asynchronous_metrics
5ORDER BY metric;

50. 刷新系统日志表

SQL

1SYSTEM FLUSH LOGS;

执行后再查询 system.query_log，可以减少日志尚未落表造成的遗漏。

六、磁盘、数据 Part 与 Mutation

51. 查看磁盘空间

SQL

1SELECT
2    name,
3    path,
4    formatReadableSize(free_space) AS free,
5    formatReadableSize(total_space) AS total,
6    round(free_space * 100 / total_space, 2) AS free_pct
7FROM system.disks;

52. 查看存储策略

SQL

1SELECT *
2FROM system.storage_policies
3ORDER BY policy_name, volume_name, volume_priority;

53. 查看大表排行

SQL

1SELECT
2    database,
3    table,
4    sum(rows) AS rows,
5    formatReadableSize(sum(bytes_on_disk)) AS disk_size
6FROM system.parts
7WHERE active
8GROUP BY database, table
9ORDER BY sum(bytes_on_disk) DESC
10LIMIT 20;

54. 查看分区大小

SQL

1SELECT
2    database,
3    table,
4    partition,
5    sum(rows) AS rows,
6    count() AS parts,
7    formatReadableSize(sum(bytes_on_disk)) AS disk_size
8FROM system.parts
9WHERE active
10  AND database = 'appdb'
11  AND table = 'events'
12GROUP BY database, table, partition
13ORDER BY partition;

55. 查看 Part 数量

SQL

1SELECT
2    database,
3    table,
4    count() AS active_parts
5FROM system.parts
6WHERE active
7GROUP BY database, table
8ORDER BY active_parts DESC;

Part 数量过多通常与写入批次过小或后台合并跟不上有关。

56. 查看正在进行的合并

SQL

1SELECT
2    database,
3    table,
4    elapsed,
5    progress,
6    num_parts,
7    result_part_name,
8    formatReadableSize(total_size_bytes_compressed) AS size
9FROM system.merges
10ORDER BY elapsed DESC;

57. 强制合并数据

SQL

1OPTIMIZE TABLE appdb.events FINAL;

FINAL 可能产生大量 CPU、磁盘 I/O 和临时空间消耗，不应作为日常定时维护命令。

58. 查看 Mutation

SQL

1SELECT
2    database,
3    table,
4    mutation_id,
5    command,
6    create_time,
7    parts_to_do,
8    is_done,
9    latest_fail_reason
10FROM system.mutations
11ORDER BY create_time DESC;

59. 异步删除数据

SQL

1ALTER TABLE appdb.events
2DELETE WHERE event_date < today() - 180;

该操作会创建 Mutation。大范围删除优先考虑按分区删除。

60. 删除整个分区

SQL

1ALTER TABLE appdb.events
2DROP PARTITION '202601';

按分区删除通常比行级 Mutation 更高效，但必须确认分区表达式和目标分区值。

七、分布式集群与复制

61. 查看集群拓扑

SQL

1SELECT
2    cluster,
3    shard_num,
4    replica_num,
5    host_name,
6    host_address,
7    port,
8    is_local
9FROM system.clusters
10ORDER BY cluster, shard_num, replica_num;

62. 在集群所有节点执行查询

SQL

1SELECT
2    hostName() AS host,
3    version()
4FROM clusterAllReplicas('production_cluster', system.one);

63. 查看副本状态

SQL

1SELECT
2    database,
3    table,
4    is_leader,
5    is_readonly,
6    is_session_expired,
7    queue_size,
8    inserts_in_queue,
9    merges_in_queue,
10    absolute_delay,
11    zookeeper_exception
12FROM system.replicas
13ORDER BY absolute_delay DESC;

64. 查看复制队列

SQL

1SELECT
2    database,
3    table,
4    replica_name,
5    position,
6    node_name,
7    type,
8    create_time,
9    num_tries,
10    last_exception
11FROM system.replication_queue
12ORDER BY create_time;

65. 等待副本同步

SQL

1SYSTEM SYNC REPLICA appdb.events_local;

66. 重启单表副本状态

SQL

1SYSTEM RESTART REPLICA appdb.events_local;

执行期间表会短暂不可用，只应在确认副本状态异常后使用。

67. 查看分布式发送队列

SQL

1SELECT
2    database,
3    table,
4    data_path,
5    is_blocked,
6    error_count,
7    last_exception
8FROM system.distribution_queue
9ORDER BY error_count DESC;

68. 刷新 Distributed 表发送队列

SQL

1SYSTEM FLUSH DISTRIBUTED appdb.events_all;

69. 查看 Keeper/ZooKeeper 连接状态

SQL

1SELECT *
2FROM system.zookeeper_connection;

70. 查看 ON CLUSTER DDL 队列

SQL

1SELECT
2    entry,
3    host,
4    port,
5    status,
6    exception_code,
7    exception_text,
8    query
9FROM system.distributed_ddl_queue
10ORDER BY entry DESC;

八、用户、权限与参数

71. 查看用户

SQL

1SHOW USERS;

详细查看：

SQL

1SELECT *
2FROM system.users;

72. 创建用户

SQL

1CREATE USER appuser
2IDENTIFIED WITH sha256_password BY 'Replace_With_Strong_Password';

73. 修改用户密码

SQL

1ALTER USER appuser
2IDENTIFIED WITH sha256_password BY 'Replace_With_New_Strong_Password';

74. 创建角色

SQL

1CREATE ROLE readonly_role;

75. 授予只读权限

SQL

1GRANT SELECT ON appdb.* TO readonly_role;
2GRANT readonly_role TO appuser;

76. 回收权限

SQL

1REVOKE SELECT ON appdb.* FROM readonly_role;

77. 查看授权

SQL

1SHOW GRANTS FOR appuser;

78. 查看当前会话参数

SQL

1SELECT
2    name,
3    value,
4    changed,
5    description
6FROM system.settings
7ORDER BY name;

79. 修改当前会话内存限制

SQL

1SET max_memory_usage = 10000000000;

该设置只影响当前会话，具体值应结合节点内存和并发量确定。

80. 设置查询最大执行时间

SQL

1SET max_execution_time = 300;

九、备份、恢复与数据维护

81. 备份单表到本地备份磁盘

SQL

1BACKUP TABLE appdb.events
2TO Disk('backups', 'appdb_events_20260727.zip');

需要先在服务器配置中定义 backups 磁盘。

82. 备份整个数据库

SQL

1BACKUP DATABASE appdb
2TO Disk('backups', 'appdb_20260727.zip');

83. 异步执行备份

SQL

1BACKUP DATABASE appdb
2TO Disk('backups', 'appdb_async_20260727.zip')
3ASYNC;

84. 查看备份任务

SQL

1SELECT *
2FROM system.backups
3ORDER BY start_time DESC;

85. 恢复单表

SQL

1RESTORE TABLE appdb.events
2FROM Disk('backups', 'appdb_events_20260727.zip');

86. 恢复为新表

SQL

1RESTORE TABLE appdb.events AS appdb.events_restore
2FROM Disk('backups', 'appdb_events_20260727.zip');

恢复到新表更适合先做数据校验。

87. 冻结表分区

SQL

1ALTER TABLE appdb.events
2FREEZE PARTITION '202607'
3WITH NAME 'events_202607';

FREEZE 生成硬链接快照，但不等同于完整的异地备份。

88. 解除冻结备份

SQL

1SYSTEM UNFREEZE WITH NAME 'events_202607';

89. 停止指定表后台合并

SQL

1SYSTEM STOP MERGES appdb.events;

90. 恢复指定表后台合并

SQL

1SYSTEM START MERGES appdb.events;

长期停止合并会造成 Part 堆积，只能作为短期故障处理手段。

十、服务、日志与巡检

91. 查看 ClickHouse 服务状态

Bash

1systemctl status clickhouse-server

92. 启动 ClickHouse

Bash

1systemctl start clickhouse-server

93. 停止 ClickHouse

Bash

1systemctl stop clickhouse-server

停库前应确认业务、复制和后台任务状态。

94. 重启 ClickHouse

Bash

1systemctl restart clickhouse-server

95. 查看服务日志

Bash

1journalctl -u clickhouse-server --since "1 hour ago"

96. 查看默认日志文件

Bash

1tail -200 /var/log/clickhouse-server/clickhouse-server.log
2tail -200 /var/log/clickhouse-server/clickhouse-server.err.log

实际路径以配置文件为准。

97. 查看 ClickHouse 进程

Bash

1ps -ef | grep '[c]lickhouse-server'

98. 查看监听端口

Bash

1ss -lntp | grep -E ':(8123|9000|9009|8443|9440)\b'

不同协议和安全配置使用的端口可能不同。

99. 查看磁盘和 I/O

Bash

1df -h
2df -i
3iostat -x 1 5

ClickHouse 对磁盘吞吐和延迟较敏感，空间告警还要结合 Part、Merge、Mutation 和复制队列一起分析。

100. 执行快速巡检摘要

SQL

1SELECT 'running_queries' AS item, toString(count()) AS value
2FROM system.processes
3UNION ALL
4SELECT 'active_merges', toString(count())
5FROM system.merges
6UNION ALL
7SELECT 'unfinished_mutations', toString(count())
8FROM system.mutations
9WHERE NOT is_done
10UNION ALL
11SELECT 'replica_queue', toString(sum(queue_size))
12FROM system.replicas
13UNION ALL
14SELECT 'disk_free',
15       arrayStringConcat(
16           groupArray(concat(name, ':', formatReadableSize(free_space))),
17           ', '
18       )
19FROM system.disks;

结语

官方资料

ClickHouse 官方文档：https://clickhouse.com/docs/
系统表：https://clickhouse.com/docs/reference/system-tables/overview
SYSTEM 命令：https://clickhouse.com/docs/reference/statements/system
备份与恢复：https://clickhouse.com/docs/operations/backup/overview

ClickHouse 运维命令 100 条 - DBA 学习之路