GeekIt

ClickHouse命令速查

clickhouse-client 连接、MergeTree 建表、导入导出、物化视图、系统表监控、副本与备份、权限与报错排查等 193 条命令

共 193 条命令 · 12 类场景 · 点击右侧图标可复制

命令 / SQL说明复制
clickhouse-client本机默认连接(127.0.0.1:9000,用户 default),进入交互式命令行
clickhouse-client --host 10.0.0.11 --port 9000 --user default --password "P@ssw0rd"远程连接原生 TCP 协议端口 9000
clickhouse-client --query "SELECT version(), hostName()"执行单条语句后退出,脚本巡检最常用
clickhouse-client --multiquery --query "SELECT 1; SELECT 2;"一次执行多条以分号分隔的语句
clickhouse-client --queries-file /tmp/check.sql执行 SQL 文件,适合批量维护脚本
clickhouse-client --format JSONEachRow --query "SELECT * FROM system.numbers LIMIT 3"指定输出格式,便于交给下游程序处理
clickhouse-client --param_tbl=events --query "SELECT count() FROM {tbl:Identifier}"参数化查询,用 --param_ 传入,语句中写占位符
clickhouse-client --query "SELECT * FROM db.t FORMAT CSV" > t.csv查询结果直接导出为 CSV
clickhouse-client --query "INSERT INTO db.t FORMAT CSV" < t.csv从 CSV 文件批量写入,比逐条 INSERT 快得多
curl "http://10.0.0.11:8123/?query=SELECT%201"通过 HTTP 8123 端口访问,方便 curl 与前端调用
curl -X POST "http://10.0.0.11:8123/?query=INSERT%20INTO%20db.t%20FORMAT%20CSV" --data-binary @t.csvHTTP 方式导入数据
clickhouse-local --query "SELECT * FROM file('/tmp/t.csv', CSV, 'id UInt32, name String') LIMIT 10"不启动服务直接分析本地文件,数据勘察神器
clickhouse-benchmark -i 5 -c 10 --query "SELECT count() FROM db.t"并发压测单条查询的 QPS 与延迟分布
SELECT version(), uptime(), currentDatabase(), hostName();确认版本、运行时长与当前节点身份
SELECT * FROM system.settings WHERE name ILIKE '%max_memory%';查看所有可用设置项及当前值
命令 / SQL说明复制
systemctl status clickhouse-server查看服务状态与最近日志
systemctl restart clickhouse-server重启服务,注意会中断正在执行的查询
sudo clickhouse-server --config-file=/etc/clickhouse-server/config.xml --daemon指定配置文件以守护进程方式手动启动
clickhouse-server --config-file=/etc/clickhouse-server/config.xml --help查看启动参数,验证配置文件路径是否正确
tail -f /var/log/clickhouse-server/clickhouse-server.log实时查看服务主日志,启动失败先看这里
tail -f /var/log/clickhouse-server/clickhouse-server.err.log错误日志,异常堆栈通常只出现在这里
SYSTEM RELOAD CONFIG;热加载 config.xml,端口、日志、集群配置修改后执行
SYSTEM RELOAD USERS;热加载 users.xml 与访问控制配置
SYSTEM RELOAD DICTIONARIES;重载外部字典定义
SYSTEM RELOAD MODELS;重载机器学习模型(CatBoost 等)
SYSTEM FLUSH LOGS;把内存中的日志刷到 system.query_log 等表,查日志前必做
SYSTEM DROP DNS CACHE;清 DNS 缓存,解决副本/分布式表解析到旧 IP 的问题
SYSTEM DROP MARK CACHE;清标记缓存,压测冷读性能时使用
SYSTEM DROP UNCOMPRESSED CACHE;清未压缩数据缓存
SYSTEM STOP MERGES;暂停后台合并,排查合并风暴或做迁移时临时使用
SYSTEM START MERGES;恢复后台合并
SYSTEM SHUTDOWN;优雅关闭实例,比 kill 进程安全
SELECT * FROM system.server_settings WHERE changed;查看被显式修改过的服务端配置项
SELECT * FROM system.users;查看已配置的用户与认证方式
命令 / SQL说明复制
SELECT cluster, shard_num, replica_num, host_name, port, is_local FROM system.clusters;查看集群拓扑与分片副本映射
SELECT * FROM system.macros;查看当前节点的 shard/replica 宏,建副本表依赖它
CREATE TABLE db.t ON CLUSTER 'ck_cluster' (...) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/db/t', '{replica}') ORDER BY id;在所有节点建副本表,ON CLUSTER 分发 DDL
CREATE TABLE db.t_all ON CLUSTER 'ck_cluster' AS db.t ENGINE = Distributed('ck_cluster', 'db', 't', rand());建分布式表作为统一查询入口
SELECT database, table, is_readonly, is_session_expired, absolute_delay, queue_size, inserts_in_queue, merges_in_queue FROM system.replicas;副本健康巡检:只读、延迟与队列堆积
SELECT * FROM system.replication_queue ORDER BY create_time LIMIT 20;复制队列明细,定位卡住的条目与 ZooKeeper 报错
SYSTEM RESTART REPLICA db.t;重启副本拉取线程,解决临时性同步中断
SYSTEM SYNC REPLICA db.t;等待副本追平主副本,故障切换后必须执行
SYSTEM SYNC REPLICA db.t LIGHTWEIGHT;轻量同步,只等待队列清空(22.8+)
SELECT * FROM system.distributed_ddl_queue ORDER BY query_create_time DESC LIMIT 10;查看 ON CLUSTER 语句在各节点的执行状态
SELECT * FROM system.zookeeper WHERE path = '/clickhouse/tables';直接查看 ZooKeeper 中的副本元数据
SELECT * FROM system.errors ORDER BY last_error_time DESC LIMIT 20;近期错误汇总,按错误码统计次数
SYSTEM RESTART REPLICAS;重启当前节点全部副本(谨慎,会短暂影响同步)
SELECT hostName(), * FROM clusterAllReplicas('ck_cluster', system.replicas);一次性拉取全部副本的复制状态做对比
命令 / SQL说明复制
CREATE DATABASE IF NOT EXISTS db;创建数据库,默认使用 Atomic 引擎
SHOW DATABASES;列出所有数据库
SHOW TABLES FROM db;列出库内所有表
SHOW CREATE TABLE db.t;查看建表语句,迁移与比对必用
DESCRIBE TABLE db.t;查看字段类型、默认值与注释
CREATE TABLE db.events (id UInt64, uid UInt64, event String, ts DateTime) ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (event, ts) TTL ts + INTERVAL 90 DAY SETTINGS index_granularity = 8192;标准 MergeTree 建表:分区、排序键、TTL 与索引粒度
CREATE TABLE db.events_replicated (...) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/db/events', '{replica}') PARTITION BY toYYYYMM(ts) ORDER BY (event, ts);单机多副本场景使用 ReplicatedMergeTree
CREATE TABLE db.events AS db.events_template;按已有表结构快速复制建表
ALTER TABLE db.events ADD COLUMN IF NOT EXISTS channel String DEFAULT '';加字段,DEFAULT 让历史数据可按需回填
ALTER TABLE db.events DROP COLUMN channel;删除字段(轻量操作,立即生效)
ALTER TABLE db.events MODIFY COLUMN uid UInt64;修改字段类型,注意可能触发表重写
ALTER TABLE db.events COMMENT COLUMN uid '用户ID';给字段加注释
ALTER TABLE db.events RENAME TO events_v2;重命名表
RENAME TABLE db.a TO db.b, db.c TO db.d;原子地批量重命名,适合切表发布
ALTER TABLE db.events DROP PARTITION 202601;删除指定分区,比 DELETE 快且不产生 mutation
ALTER TABLE db.events DETACH PARTITION 202601;卸载分区文件,数据仍留在磁盘可再挂载
ALTER TABLE db.events ATTACH PARTITION 202601;重新挂载已卸载的分区
ALTER TABLE db.events MOVE PARTITION 202601 TO TABLE db.events_archive;分区级数据搬迁,冷热分层常用
ALTER TABLE db.events REPLACE PARTITION 202601 FROM db.events_tmp;用临时表分区原子替换,实现回补数据
OPTIMIZE TABLE db.events PARTITION 202601 FINAL;强制合并分区内数据片段,清理重复行前必做
TRUNCATE TABLE IF EXISTS db.events;清空表数据,保留表结构
DROP TABLE IF EXISTS db.events SYNC;删除表并等待文件清理完成
SELECT * FROM system.tables WHERE database = 'db' AND engine LIKE '%MergeTree';按引擎筛选表清单
命令 / SQL说明复制
INSERT INTO db.events VALUES (1, 1001, 'login', now());单行插入,仅适合调试,生产批量写
INSERT INTO db.events SELECT * FROM db.events_stage WHERE ts >= today();表间批量写入,走同一引擎时效率最高
INSERT INTO db.events FORMAT JSONEachRow {"id":1,"uid":2,"event":"view","ts":"2026-11-02 10:00:00"}按行写 JSON,日志采集常用格式
clickhouse-client --query "INSERT INTO db.events FORMAT CSV" < /data/events.csv客户端导入 CSV,单批建议 10 万行以上
clickhouse-client --query "INSERT INTO db.events FORMAT Native" < /data/events.nativeNative 格式最快,由 clickhouse-client 导出生成
clickhouse-client --query "SELECT * FROM db.events FORMAT Native" > /data/events.native导出 Native 文件用于跨集群搬运
SELECT * FROM db.events INTO OUTFILE '/tmp/events.csv' FORMAT CSVWithNames;服务端直接导出到文件(需开启 output_format 权限)
INSERT INTO db.events SELECT * FROM s3('https://bucket.s3.amazonaws.com/events/*.parquet', 'AK', 'SK', 'Parquet');从对象存储批量导入 Parquet
SELECT * FROM s3('https://bucket.s3.amazonaws.com/export/*.csv.gz', 'CSVWithNames') LIMIT 10;直接查询 S3 文件,无需先入库
INSERT INTO db.events SELECT * FROM remote('10.0.0.12:9000', 'db', 'events', 'default', 'pwd');跨实例拉取数据,迁移时常用
INSERT INTO db.events SELECT * FROM remoteSecure('ck.example.com:9440', 'db', 'events', 'default', 'pwd');加密通道跨集群写入
INSERT INTO FUNCTION file('/tmp/events.csv', CSVWithNames) SELECT * FROM db.events;把查询结果写入本地文件(表函数写入)
SELECT * FROM file('/tmp/events.csv', CSVWithNames) LIMIT 5;把本地文件当表查询,无需导入
SELECT count(), max(ts) FROM db.events;写入后校验数据量与最新时间
INSERT INTO db.events SETTINGS async_insert = 1, wait_for_async_insert = 1 SELECT ...;开启异步插入,缓解高频小批量写入的 part 压力
命令 / SQL说明复制
SELECT event, count() AS cnt FROM db.events WHERE ts >= now() - INTERVAL 1 DAY GROUP BY event ORDER BY cnt DESC LIMIT 10;最常用的时间范围聚合查询
SELECT * FROM db.events PREWHERE event = 'login' WHERE uid > 1000 LIMIT 100;PREWHERE 先过滤稀疏索引可用的列,减少读取量
SELECT uniqExact(uid), uniq(uid) FROM db.events;精确去重与近似去重(uniq 误差约 0.5%,更快)
SELECT quantile(0.95)(duration) AS p95, avg(duration), max(duration) FROM db.events;分位数统计,延迟分析必备
SELECT argMax(event, ts) AS last_event FROM db.events GROUP BY uid;取每个用户最新一条记录
SELECT toStartOfHour(ts) AS h, count() FROM db.events GROUP BY h ORDER BY h;按小时聚合时序指标
SELECT event, count() FROM db.events GROUP BY event WITH ROLLUP;分组小计与总计(CUBE / TOTALS 同理)
SELECT uid, sum(amount) OVER (PARTITION BY uid ORDER BY ts) AS acc FROM db.events;窗口函数累计求和
SELECT row_number() OVER (PARTITION BY uid ORDER BY ts DESC) AS rn, * FROM db.events LIMIT 100;窗口函数编号,取每组 TopN 时配合子查询
SELECT arrayJoin([1, 2, 3]) AS x;数组展开为多行,处理标签、多值字段常用
SELECT uid, groupArray(event) FROM db.events GROUP BY uid;多行聚合成数组
SELECT * FROM db.events FINAL WHERE id = 1;FINAL 强制合并去重(ReplacingMergeTree 场景),代价较高
SELECT * FROM db.events SAMPLE 0.1;按采样比例查询,快速估算大表指标
SELECT formatReadableSize(sum(bytes_on_disk)) FROM system.parts WHERE active;把字节数格式化为可读单位
SELECT * FROM db.events LIMIT 10 BY event;每个 event 各取 10 行,做抽样预览很方便
SELECT * FROM db.events SETTINGS max_threads = 8, max_memory_usage = 20000000000;语句级覆盖并发与内存上限
命令 / SQL说明复制
CREATE MATERIALIZED VIEW db.mv_events TO db.events_agg AS SELECT toStartOfHour(ts) AS h, event, count() AS cnt FROM db.events GROUP BY h, event;写入即聚合:MV 把结果写入目标表,查询查目标表
CREATE MATERIALIZED VIEW db.mv_events ENGINE = SummingMergeTree ORDER BY (h, event) POPULATE AS SELECT ...;带自身存储的 MV,POPULATE 会回填历史(大数据量慎用)
CREATE TABLE db.events_agg (h DateTime, event String, cnt UInt64) ENGINE = SummingMergeTree ORDER BY (h, event);SummingMergeTree 聚合表,合并时按排序键求和
CREATE TABLE db.uniq_agg (h DateTime, event String, uv AggregateFunction(uniq, UInt64)) ENGINE = AggregatingMergeTree ORDER BY (h, event);AggregatingMergeTree 存中间状态,支持精确/近似去重
CREATE MATERIALIZED VIEW db.mv_uv TO db.uniq_agg AS SELECT toStartOfHour(ts) AS h, event, uniqState(uid) AS uv FROM db.events GROUP BY h, event;MV 写入 uniqState 中间状态
SELECT h, event, uniqMerge(uv) AS uv FROM db.uniq_agg GROUP BY h, event;查询时用 uniqMerge 合并状态
CREATE TABLE db.events_rt (h DateTime, event String, cnt SimpleAggregateFunction(sum, UInt64)) ENGINE = AggregatingMergeTree ORDER BY (h, event);SimpleAggregateFunction 写普通值即可,查询更简单
ALTER TABLE db.events ADD PROJECTION p_by_event (SELECT event, count() GROUP BY event);轻量级投影,避免为每种聚合建 MV
ALTER TABLE db.events MATERIALIZE PROJECTION p_by_event;物化已有数据的投影,后台执行
SELECT * FROM db.events SETTINGS optimize_use_projections = 1;确认查询可用投影加速
DROP TABLE db.mv_events;删除 MV(TO 形式的 MV 不删除目标表)
SELECT * FROM system.tables WHERE engine LIKE '%View%';查看所有视图类对象
命令 / SQL说明复制
SYSTEM FLUSH LOGS; SELECT * FROM system.query_log WHERE type = 'QueryFinish' ORDER BY event_time DESC LIMIT 10;查最近完成的查询,含耗时、读取行数与内存
SELECT query_id, user, elapsed, read_rows, memory_usage, query FROM system.processes ORDER BY elapsed DESC;查看正在执行的查询,定位慢查询与堆积
KILL QUERY WHERE query_id = 'a1b2c3';终止指定查询(SYNC 参数可等待结束)
SELECT database, table, count() AS parts, sum(rows) AS rows, formatReadableSize(sum(bytes_on_disk)) AS size FROM system.parts WHERE active GROUP BY database, table ORDER BY parts DESC LIMIT 20;按表统计活跃 part 数与体积,part 过多是写入问题信号
SELECT * FROM system.mutations WHERE is_done = 0;查看未完成的 ALTER UPDATE/DELETE 任务
SELECT database, table, elapsed, progress, num_parts FROM system.merges;查看正在进行的后台合并进度
SELECT * FROM system.errors ORDER BY value DESC LIMIT 10;错误码统计,快速发现隐性故障
SELECT * FROM system.metrics WHERE value > 0 ORDER BY value DESC;实例级实时指标(查询数、连接数等)
SELECT event, value FROM system.events WHERE event ILIKE '%InsertedRows%';累计事件计数,用于容量与写入量统计
SELECT * FROM system.asynchronous_metrics WHERE metric ILIKE '%Memory%';异步采集的系统指标(内存、磁盘、CPU)
SELECT name, path, free_space, total_space, keep_free_space FROM system.disks;磁盘空间巡检,写入前必看
SELECT * FROM system.part_log WHERE event_type = 'MergeParts' ORDER BY event_time DESC LIMIT 10;合并明细,分析合并耗时与写入放大
SELECT * FROM system.query_thread_log ORDER BY event_time DESC LIMIT 10;查询线程级明细,定位单线程瓶颈(需开启配置)
SELECT * FROM system.text_log ORDER BY event_time DESC LIMIT 20;服务端文本日志查询,比翻文件更快
SELECT * FROM system.build_options;查看编译选项与版本特性开关
命令 / SQL说明复制
EXPLAIN SELECT * FROM db.events WHERE event = 'login';查看逻辑执行计划
EXPLAIN indexes = 1 SELECT * FROM db.events WHERE event = 'login';显示索引与分区裁剪情况,优化查询第一步
EXPLAIN PIPELINE SELECT count() FROM db.events;查看查询流水线与并行度,判断线程利用
EXPLAIN AST SELECT 1;查看语法树,排查复杂表达式的解析结果
SELECT query, read_rows, read_bytes, memory_usage, ProfileEvents['SelectedParts'] AS parts FROM system.query_log WHERE type = 'QueryFinish' ORDER BY memory_usage DESC LIMIT 10;按内存排序找最贵查询,同时看扫描的 part 数
SELECT table, sum(marks) AS marks FROM system.parts WHERE active GROUP BY table;标记数近似反映索引规模,评估扫描成本
ALTER TABLE db.events ADD INDEX idx_event event TYPE bloom_filter GRANULARITY 4;加跳数索引,提升等值过滤效率
ALTER TABLE db.events ADD INDEX idx_url url TYPE ngrambf_v1(3, 10000, 3, 0) GRANULARITY 4;ngram 布隆索引支持 LIKE 模糊匹配
ALTER TABLE db.events MATERIALIZE INDEX idx_event;为已有数据补建跳数索引
SET max_threads = 8;限制单查询并发线程,避免打满 CPU
SET max_memory_usage = 20000000000;限制单查询内存(字节),防止 OOM
SET max_bytes_before_external_group_by = 8000000000;超出后落盘分组,用磁盘换内存
SET max_execution_time = 60;查询超时保护,避免长时间占用资源
SELECT * FROM db.events WHERE ts >= toDate('2026-11-01') AND ts < toDate('2026-11-02');用常量区间替代函数包裹分区列,保证分区裁剪生效
OPTIMIZE TABLE db.events FINAL;合并全部 part,查询变快但开销大,避开高峰
SELECT * FROM system.query_log WHERE query_duration_ms > 5000 AND type = 'QueryFinish' ORDER BY query_duration_ms DESC LIMIT 10;筛选 5 秒以上的慢查询做专项优化
命令 / SQL说明复制
BACKUP DATABASE db TO Disk('backups', 'db_20261102.zip');备份到本地磁盘目录(需在 config 中配置 backups disk)
BACKUP TABLE db.events TO Disk('backups', 'events_20261102.zip') SETTINGS compression_method = 'zstd';单表备份并启用压缩
BACKUP DATABASE db TO S3('https://bucket.s3.amazonaws.com/backup/db_20261102', 'AK', 'SK');备份到对象存储,跨机房容灾首选
RESTORE DATABASE db FROM Disk('backups', 'db_20261102.zip');从本地备份还原数据库
RESTORE TABLE db.events AS db.events_restore FROM Disk('backups', 'events_20261102.zip');还原为另一张表做数据比对
SELECT * FROM system.backups ORDER BY start_time DESC LIMIT 10;查看备份任务状态与失败原因
ALTER TABLE db.events FREEZE;生成分区硬链接快照,配合磁盘快照做备份
SYSTEM UNFREEZE WITH NAME 'backup';删除指定快照硬链接释放空间
clickhouse-backup create db_20261102常用第三方工具备份整实例(需单独安装)
clickhouse-backup restore db_20261102用 clickhouse-backup 还原
ALTER TABLE db.events ATTACH PARTITION 202601 FROM '/var/lib/clickhouse/data/db/events/detached/';从 detached 目录直接挂载分区文件
INSERT INTO db.events SELECT * FROM remote('10.0.0.12:9000', 'db', 'events');跨实例搬迁数据,小表直接走 remote
clickhouse-copier --config-file copier.xml --task-path /clickhouse/copier/task1大规模跨集群复制工具(新版建议用 BACKUP/RESTORE)
SELECT count(), max(ts) FROM db.events;恢复后校验数据完整性
命令 / SQL说明复制
CREATE USER IF NOT EXISTS app IDENTIFIED WITH sha256_password BY 'Str0ng!Pass' HOST IP '10.0.0.%';创建用户并限制来源网段
GRANT SELECT ON db.* TO app;授予库级只读权限
GRANT SELECT, INSERT ON db.events TO app;按表授予读写权限
REVOKE INSERT ON db.events FROM app;回收权限
SHOW GRANTS FOR app;查看用户当前全部权限
CREATE ROLE analyst; GRANT SELECT ON db.* TO analyst; GRANT analyst TO app;用角色统一管理权限,便于批量授权
CREATE SETTINGS PROFILE readonly_profile SETTINGS readonly = 1, max_execution_time = 60 TO app;限制用户只读并加超时保护
ALTER USER app SETTINGS max_memory_usage = 10000000000;为用户设置资源上限
CREATE ROW POLICY app_policy ON db.events FOR SELECT USING uid = currentUser() TO app;行级权限,实现多租户数据隔离
CREATE QUOTA q_app FOR INTERVAL 1 hour MAX queries = 1000 TO app;限流配额,防止误用打爆实例
ALTER USER app IDENTIFIED WITH sha256_password BY 'N3w!Pass';修改用户密码
SELECT name, storage, auth_type FROM system.users;查看用户与认证方式
SELECT * FROM system.grants;查看全部授权记录
SELECT * FROM system.quotas;查看配额配置
SHOW ACCESS;列出用户、角色、策略、配额等访问实体
ALTER USER default IDENTIFIED WITH sha256_password BY 'NewPass';为 default 账号设置密码,生产必须做
SELECT * FROM system.session_log ORDER BY event_time DESC LIMIT 20;登录审计(需开启 session_log)
命令 / SQL说明复制
Code: 252. Too many parts (1000). Merges are processing significantly slower than inserts写入过快:加大批次、减少分区数、检查合并与磁盘 IO
Code: 241. Memory limit (for query) exceeded查询内存超限:加 max_memory_usage 或落盘聚合,优化 GROUP BY
Code: 159. Timeout exceeded: elapsed 30 seconds超时:提高 max_execution_time 或优化查询与分区裁剪
Code: 202. Too many simultaneous queries并发超限:调大 max_concurrent_queries 或加限流与队列
Code: 242. Table is in readonly mode副本只读:查 system.replicas 与 ZooKeeper,恢复后 SYSTEM RESTART REPLICA
Code: 999. KEEPER_EXCEPTION / ZooKeeper session expiredZooKeeper 会话丢失:检查 ZK 稳定性与网络,必要时重启副本
Code: 60. Table db.t already exists建表冲突:加 IF NOT EXISTS 或先检查 system.tables
Code: 81. Database db does not exist库不存在或名写错:确认 currentDatabase 与大小写
Code: 47. Missing columns: xxx字段缺失:确认表结构版本与 SELECT 列表
Code: 27. Cannot parse input: expected ...导入格式不符:检查分隔符、列数与编码
Mutation 长时间卡住 is_done = 0查 system.mutations 的 latest_fail_reason,必要时 KILL MUTATION
KILL MUTATION WHERE database = 'db' AND table = 'events';终止未完成的数据变更任务
副本延迟持续增大 absolute_delay看 replication_queue:多为 ZooKeeper 慢、磁盘满或合并堆积
分布式表查询比本地表慢很多尽量把过滤下推、减少跨分片广播,必要时查本地表再聚合
磁盘写入报 No space left on device查 system.disks,清理旧分区或调整 TTL 与存储策略
服务启动失败但日志无异常检查 config.xml 端口占用、数据目录权限与 ulimit -n
SELECT * FROM system.parts WHERE active = 0 AND modification_time < now() - INTERVAL 1 DAY LIMIT 10;查看残留非活跃 part,确认合并是否正常

运维常用组合

快速巡检SELECT version() → system.disks 看空间 → system.replicas 看副本 → system.mutations 看残留任务
慢查询分析system.processes 找在跑的 → system.query_log 按耗时排序 → EXPLAIN indexes = 1 → 调整 ORDER BY/分区/索引
数据导入clickhouse-client --query "INSERT INTO db.t FORMAT CSV" < data.csv → SELECT count() 校验 → OPTIMIZE TABLE 合并
副本修复system.replicas 发现 is_readonly → 查 replication_queue 与 zookeeper → SYSTEM RESTART REPLICA → SYSTEM SYNC REPLICA
冷热分层TTL ts + INTERVAL 30 DAY TO VOLUME 'cold' → storage_policy 配置多磁盘 → ALTER TABLE MOVE PARTITION 手动搬迁
备份恢复BACKUP DATABASE TO Disk/S3 → system.backups 确认成功 → RESTORE 到新表 → count() 与 max(ts) 校验

常见问题排查

1. 查询先看分区裁剪

WHERE 条件直接比较分区列(如 ts >= toDate('2026-11-01')),不要用函数包裹,否则全表扫描

2. 写入要凑大批次

每秒上百次小批量写入会制造大量 part 并拖垮合并,客户端攒批或用 async_insert

3. 排序键决定一切

ORDER BY 是稀疏索引,查询常用过滤列放前面;建成后修改代价极大,建表前想清楚

4. 去重靠 ReplacingMergeTree + FINAL

MergeTree 不保证实时去重,需要精确去重时配合 FINAL 或查询时聚合

5. 副本问题先看三张表

system.replicas 看是否只读、system.replication_queue 看卡点、system.zookeeper 看元数据

6. 备份要用 BACKUP 而不是拷文件

切勿直接 cp 数据目录,用 BACKUP/RESTORE 或 clickhouse-backup 才能保证一致性

共 193 条 ClickHouse 命令 · 示例中的库名 db 与集群名 ck_cluster 请替换为实际环境

关于ClickHouse命令速查

在线 ClickHouse 命令速查手册,按连接与客户端、服务与配置管理、集群副本与分布式、库表 DDL 与 MergeTree、写入导入导出、查询语法与常用函数、物化视图与聚合表、系统表与监控、性能优化与执行分析、备份恢复与迁移、用户权限与安全、常见报错与排查等 12 类场景整理 180+ 条常用命令与 SQL,每条附中文说明与可直接复制的写法,支持关键词搜索、分类折叠和一键复制,打开网页即可查阅。涵盖 clickhouse-client 的 --host/--port/--user/--password/--query/--multiquery/--queries-file/--format/--param_ 与交互式命令行、9000 原生协议与 8123 HTTP 接口与 curl 导入导出、clickhouse-local 文件分析、clickhouse-benchmark 压测、systemctl 启停与 config.xml/users.xml、SYSTEM RELOAD CONFIG/USERS/DICTIONARIES 与 SYSTEM FLUSH LOGS/DROP DNS CACHE/STOP MERGES/SHUTDOWN、system.clusters 与 system.macros 与 ON CLUSTER 建表、ReplicatedMergeTree 与 Distributed 引擎、system.replicas 的 is_readonly 与 absolute_delay 与 queue_size、system.replication_queue 与 system.distributed_ddl_queue 与 system.zookeeper、SYSTEM RESTART REPLICA 与 SYSTEM SYNC REPLICA、CREATE TABLE 的 PARTITION BY 与 ORDER BY 与 TTL 与 index_granularity 与 storage_policy、ALTER TABLE ADD/DROP/MODIFY COLUMN 与 RENAME 与 DROP/DETACH/ATTACH/REPLACE/MOVE PARTITION 与 OPTIMIZE FINAL、INSERT VALUES/SELECT/FORMAT CSV/JSONEachRow/Native 与 s3() 与 remote() 与 remoteSecure() 与 file() 表函数、async_insert、PREWHERE 与 FINAL 与 SAMPLE 与 LIMIT BY、uniq/uniqExact/quantile/argMax/arrayJoin/groupArray 与窗口函数与 WITH ROLLUP、Materialized View 与 SummingMergeTree 与 AggregatingMergeTree 与 uniqState/uniqMerge 与 SimpleAggregateFunction 与 PROJECTION 与 MATERIALIZE PROJECTION、system.query_log 与 system.processes 与 KILL QUERY 与 system.parts 与 system.mutations 与 system.merges 与 system.errors 与 system.metrics 与 system.events 与 system.asynchronous_metrics 与 system.disks 与 system.part_log 与 system.text_log、EXPLAIN 与 EXPLAIN indexes = 1 与 EXPLAIN PIPELINE 与跳数索引 bloom_filter/ngrambf_v1 与 max_threads/max_memory_usage/max_bytes_before_external_group_by/max_execution_time、BACKUP/RESTORE 到 Disk 与 S3 与 system.backups 与 ALTER TABLE FREEZE 与 clickhouse-backup 与 clickhouse-copier、CREATE USER/ROLE 与 GRANT/REVOKE 与 SETTINGS PROFILE 与 ROW POLICY 与 QUOTA 与 system.grants/system.quotas、以及 Code 252 TOO_MANY_PARTS 与 241 内存超限 与 159 超时 与 202 并发超限 与 242 副本只读 与 999 KEEPER_EXCEPTION 与 Code 60/81/47/27 等报错排查。适用于 ClickHouse 列式数据库日常运维、实时分析与埋点数据接入、大批量导入导出、物化视图预聚合设计、副本同步与集群扩容、慢查询与内存优化、备份与容灾演练,以及大数据与 DBA 面试复习。

核心能力

  • 涵盖 clickhouse-client 的 --host/--port/--user/--password/--query/--multiquery/--queries-file/--format/--param_ 与交互式命令行、9000 原生协议与 8123 HTTP 接口与 curl 导入导出、clickhouse-local 文件分析、clickhouse-benchmark 压测、systemctl 启停与 config.xml/users.xml、SYSTEM RELOAD CONFIG/USERS/DICTIONARIES 与 SYSTEM FLUSH LOGS/DROP DNS CACHE/STOP MERGES/SHUTDOWN、system.clusters 与 system.macros 与 ON CLUSTER 建表、ReplicatedMergeTree 与 Distributed 引擎、system.replicas 的 is_readonly 与 absolute_delay 与 queue_size、system.replication_queue 与 system.distributed_ddl_queue 与 system.zookeeper、SYSTEM RESTART REPLICA 与 SYSTEM SYNC REPLICA、CREATE TABLE 的 PARTITION BY 与 ORDER BY 与 TTL 与 index_granularity 与 storage_policy、ALTER TABLE ADD/DROP/MODIFY COLUMN 与 RENAME 与 DROP/DETACH/ATTACH/REPLACE/MOVE PARTITION 与 OPTIMIZE FINAL、INSERT VALUES/SELECT/FORMAT CSV/JSONEachRow/Native 与 s3() 与 remote() 与 remoteSecure() 与 file() 表函数、async_insert、PREWHERE 与 FINAL 与 SAMPLE 与 LIMIT BY、uniq/uniqExact/quantile/argMax/arrayJoin/groupArray 与窗口函数与 WITH ROLLUP、Materialized View 与 SummingMergeTree 与 AggregatingMergeTree 与 uniqState/uniqMerge 与 SimpleAggregateFunction 与 PROJECTION 与 MATERIALIZE PROJECTION、system.query_log 与 system.processes 与 KILL QUERY 与 system.parts 与 system.mutations 与 system.merges 与 system.errors 与 system.metrics 与 system.events 与 system.asynchronous_metrics 与 system.disks 与 system.part_log 与 system.text_log、EXPLAIN 与 EXPLAIN indexes = 1 与 EXPLAIN PIPELINE 与跳数索引 bloom_filter/ngrambf_v1 与 max_threads/max_memory_usage/max_bytes_before_external_group_by/max_execution_time、BACKUP/RESTORE 到 Disk 与 S3 与 system.backups 与 ALTER TABLE FREEZE 与 clickhouse-backup 与 clickhouse-copier、CREATE USER/ROLE 与 GRANT/REVOKE 与 SETTINGS PROFILE 与 ROW POLICY 与 QUOTA 与 system.grants/system.quotas、以及 Code 252 TOO_MANY_PARTS 与 241 内存超限 与 159 超时 与 202 并发超限 与 242 副本只读 与 999 KEEPER_EXCEPTION 与 Code 60/81/47/27 等报错排查

适用场景

适用于 ClickHouse 列式数据库日常运维、实时分析与埋点数据接入、大批量导入导出、物化视图预聚合设计、副本同步与集群扩容、慢查询与内存优化、备份与容灾演练,以及大数据与 DBA 面试复习。ClickHouse命令速查全程在浏览器中完成,无需安装插件,可放心用于日常办公与排障。

常见问题

ClickHouse命令速查是免费的吗?需要注册或安装吗?
ClickHouse命令速查完全免费,无需注册、无需下载安装任何软件,用浏览器打开网页即可直接使用,也不限制使用次数。
ClickHouse命令速查支持哪些功能?
涵盖 clickhouse-client 的 --host/--port/--user/--password/--query/--multiquery/--queries-file/--format/--param_ 与交互式命令行、9000 原生协议与 8123 HTTP 接口与 curl 导入导出、clickhouse-local 文件分析、clickhouse-benchmark 压测、systemctl 启停与 config.xml/users.xml、SYSTEM RELOAD CONFIG/USERS/DICTIONARIES 与 SYSTEM FLUSH LOGS/DROP DNS CACHE/STOP MERGES/SHUTDOWN、system.clusters 与 system.macros 与 ON CLUSTER 建表、ReplicatedMergeTree 与 Distributed 引擎、system.replicas 的 is_readonly 与 absolute_delay 与 queue_size、system.replication_queue 与 system.distributed_ddl_queue 与 system.zookeeper、SYSTEM RESTART REPLICA 与 SYSTEM SYNC REPLICA、CREATE TABLE 的 PARTITION BY 与 ORDER BY 与 TTL 与 index_granularity 与 storage_policy、ALTER TABLE ADD/DROP/MODIFY COLUMN 与 RENAME 与 DROP/DETACH/ATTACH/REPLACE/MOVE PARTITION 与 OPTIMIZE FINAL、INSERT VALUES/SELECT/FORMAT CSV/JSONEachRow/Native 与 s3() 与 remote() 与 remoteSecure() 与 file() 表函数、async_insert、PREWHERE 与 FINAL 与 SAMPLE 与 LIMIT BY、uniq/uniqExact/quantile/argMax/arrayJoin/groupArray 与窗口函数与 WITH ROLLUP、Materialized View 与 SummingMergeTree 与 AggregatingMergeTree 与 uniqState/uniqMerge 与 SimpleAggregateFunction 与 PROJECTION 与 MATERIALIZE PROJECTION、system.query_log 与 system.processes 与 KILL QUERY 与 system.parts 与 system.mutations 与 system.merges 与 system.errors 与 system.metrics 与 system.events 与 system.asynchronous_metrics 与 system.disks 与 system.part_log 与 system.text_log、EXPLAIN 与 EXPLAIN indexes = 1 与 EXPLAIN PIPELINE 与跳数索引 bloom_filter/ngrambf_v1 与 max_threads/max_memory_usage/max_bytes_before_external_group_by/max_execution_time、BACKUP/RESTORE 到 Disk 与 S3 与 system.backups 与 ALTER TABLE FREEZE 与 clickhouse-backup 与 clickhouse-copier、CREATE USER/ROLE 与 GRANT/REVOKE 与 SETTINGS PROFILE 与 ROW POLICY 与 QUOTA 与 system.grants/system.quotas、以及 Code 252 TOO_MANY_PARTS 与 241 内存超限 与 159 超时 与 202 并发超限 与 242 副本只读 与 999 KEEPER_EXCEPTION 与 Code 60/81/47/27 等报错排查。
ClickHouse命令速查适合哪些场景使用?
适用于 ClickHouse 列式数据库日常运维、实时分析与埋点数据接入、大批量导入导出、物化视图预聚合设计、副本同步与集群扩容、慢查询与内存优化、备份与容灾演练,以及大数据与 DBA 面试复习。