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.csv | HTTP 方式导入数据 | |
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.native | Native 格式最快,由 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 expired | ZooKeeper 会话丢失:检查 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 REPLICATTL 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 才能保证一致性
相关工具
Linux命令速查
文件、文本、权限、进程、网络、磁盘、systemd与防火墙等 Linux 常用命令速查手册
Docker命令速查
镜像、容器、网络、数据卷、Compose、Swarm、清理与排障等 Docker 常用命令速查手册
Nginx命令速查
服务管理、配置指令、反向代理、HTTPS、限流缓存、日志排查等 Nginx 常用命令速查手册
K8S命令速查
集群上下文、Pod调试、工作负载、Service与Ingress、存储、权限、排障等 kubectl 常用命令速查手册
Windows CMD命令速查
文件目录、网络诊断、进程服务、系统信息、用户权限、注册表、批处理等 Windows CMD 常用命令速查手册
systemctl服务管理速查
服务启停、开机自启、unit文件编写、日志查看、定时器、依赖分析与排障等 systemctl 命令速查手册
关于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 面试复习。