0.登录
clickhouse-client --host 127.0.0.1 --port 9000 --user default --password pw //登录
clickhouse-client --host 127.0.0.1 --port 9000 --user svr --password pw //登录1.特点
- 属于列式存储数据库(一列数据存储在一起)，更适合数据统计分析(聚合、计数、求和)、数据类型一致更好存储压缩。
行式存储(mysql、oracle)，适用于增删改查都有的场景(主要是顺序写) - 多样化引擎: merge、日志、集成、其他 4大类20多种引擎.. 还有库引擎
- 和LSM tree思想一致: 追加写/后台合并 (conn.4.11)
- 多线程效率高吃cpu
2.数据类型
类型 官方文档
Int8 Int16 Int32 Int64 UInt8 UInt16 UInt32 UInt64 Float32 Float64 Decimal32(s) //等价于mysql:decimal32(9,9-s) //2^31 =10位数 //mysql: decimal(m,d) m总位数 d小数位数 Decimal64(s) //等价于mysql:decimal32(18,18-s) //2^63 =19位数 Decimal128(s) //等价于mysql:decimal32(38,38-s) //2^127=37位数 Bool //本质是用Uint(8)存储 String //类似于mysql:varchar(n) //mysql有长度限制 clickhouse没有 FixedString(n) //等价于mysql:char(n) Enum8 Enum16 //枚举把string和Int8 Int16绑定 Date //[1970-01-01, 2149-06-06] Date32 //[1970-01-01, 2299-12-31] DateTime //[1970-01-01 00:00:00, 2106-02-07 06:28:15] DateTime64 //[0000-01-01 00:00:00, 9999-12-31 23:59:59.999999999] Time //[-999:59:59, 999:59:59] Array(T) //数组 Nullable //NULL 一般用业务不会出现的值代替NULL
3.表引擎
1.集成
- 数据存储在其他app，例如mysql/kafka clickhouse本地不存数据但可以直接去其他app查询和插入数据
2.日志
- TinyLog 不支持索引，不支持并发 用于存少量数据的小表，生产环境很少使用
3.merge
MergeTree
CREATE TABLE tbl_orders ( `customer_id` UInt32, `good_id` String, `good_num` UInt32, `amount` Decimal(16, 2) TTL `d` + INTERVAL 10 SECOND, --5.[TTL] 1.字段在数据到期以后(时间>`d`+10s) 下一次合并会被设置为默认值 2.统计数据一般会要求最近的数据，所以TTL应用比较广泛 `d` Datetime, -- INDEX idx_name `amount` TYPE minmax GRANULARITY 5 --4.[二级索引] 1.主键(跳数)索引把数据分成了很多块 2.每5块记录`amount`的最大最小值 3.在对`amount`过滤的时候可以快速的知道这5块数据里面有没有符合查找条件的数据 ) ENGINE = MergeTree --0.<表引擎> PARTITION BY toYYYYMMDD(`d`) --1.[分区] 1.一个分区(Partition)有多个Part目录 2.新插入的数据会在临时Part目录 定时/大小限制/数量 触发合并(合并和LSM思路一致 但是没有数据新旧版本的概念L0>L1>L2) 3.推荐使用日期分区 PRIMARY KEY customer_id --3.[主键] 1.可以重复 2.稀疏索引(跳数建立索引) 3.必须是order by的前缀字段 默认值是order by SETTINGS index_granularity = 8192 --3.[主键] 设置跳数索引的间隔(index_granularity 默认值:8192) 在索引开销和过滤效果之间做权衡 ORDER BY (customer_id, good_id) --2.<排序> TTL `d` + INTERVAL 1 MONTH DELETE, --6.[TTL] 行数据在到期以后 下一次合并会被删除 `d` + INTERVAL 1 WEEK TO VOLUME 'aa', --6.[TTL] 行数据在到期以后 下一次合并被移动到卷aa `d` + INTERVAL 2 WEEK TO DISK 'bb'; --6.[TTL] 行数据在到期以后 下一次合并被移动到磁盘bb OPTIMIZE TABLE tbl_orders FINAL; --手动执行合并ReplacingMergeTree(按排序键替换旧版本)
CREATE TABLE tbl_orders ( `customer_id` UInt32, `good_id` String, `good_num` UInt32, `amount` Decimal(16, 2) TTL `d` + INTERVAL 1 MONTH, `d` Datetime, INDEX idx_name `amount` TYPE minmax GRANULARITY 5 ) ENGINE = ReplacingMergeTree(`d`) --1.<表引擎> 1.会根据order by在合并的时候去重 保留`d`最大行 `d`相同保留最新插入的行 2.只有同一分区去重 3.其他和MergeTree一致 PARTITION BY toYYYYMMDD(`d`) -- 4.实现去重 ①.FINAL ②.GROUP BY + argMax(good_id,d) PRIMARY KEY customer_id ORDER BY (customer_id, good_id) --2.<排序> 1.UNIQUE TTL `d` + INTERVAL 1 MONTH DELETE, `d` + INTERVAL 1 WEEK TO VOLUME 'aa', `d` + INTERVAL 2 WEEK TO DISK 'bb';SELECT --sql语义:查询用户最新的订单数据 即业务实现: 预去重/只是同一分区去重->所有分区去重 customer_id, argMax(good_id, d) AS good_id, --实现去重 2(推荐).保留d最大的那个good_id 如果最新的数据操作是删除 可以插入一条good_id=-1的哨兵值标识最新行数据已经删除 argMax(good_num, d) AS good_num, max(d) AS d FROM tbl_orders --实现去重 1(大表性能差).在这里加FINAL 相当于把后台没有完成的merge提前展示出来 GROUP BY customer_id HAVING good_id != -1; --实现去重 2.过滤掉已经删除了的数据SummingMergeTree(预聚合)
create table tbl_orders( `customer_id` UInt32, `good_id` String, `good_num` UInt32, `amount` Decimal(16,2) , `d` Datetime ) -- engine =SummingMergeTree(`amount`, `good_num`) --<表引擎> 1.会根据order by在合并的时候聚合`amount`(可以指定多列 需要为数值列) 其他字段保留最先插入的数据 2.只有同一分区聚合 partition by toYYYYMMDD(d) primary key (customer_id) order by (customer_id,good_id);
4.特殊
- Memory
4.sql
增 INSERT同mysql
insert into tbl_orders values (101,'sku_001',1,1000.00,'2020-06-01 12:00:00'), (102,'sku_002',2,2000.00,'2020-06-01 11:00:00'), (102,'sku_002',1,6000.00,'2020-06-02 12:00:00');删 DELETE
alter table tbl_orders delete where good_id ='sku_001';改 UPDATE
alter table tbl_orders update total_amount=toDecimal32(2000.00,2) where customer_id=102;删改 DELETE UPDATE 属于重型操作，对事务的支持为实验性功能
clickhouse每次修改或者删除的策略是放弃数据所在的原有文件，重建新的文件(rcu)。所以要批量更改，不要频繁小数据操作
不设计成mysql一样找到数据位置修改或者删除的原因是
clickhouse为列式存储 同时数据编码和压缩 某个值改变以后，重新编码、压缩得到的数据内容和长度都可能发生变化
如果该字段在ORDER BY，还会导致稀疏主键索引、marks(标识各列数据在压缩文件的具体偏移位置，在主键索引使用后要查找具体位置使用)等相关结构变化。
所以clickhouse干脆采用了rcu的模式
clickhouse会限制了最大并发请求数 默认值100 可配置max_concurrent_queries查 SELECT
a. 支持子查询 / 支持CTE (同mysql)
SELECT *
FROM
(
SELECT
customer_id,
sum(amount) AS sumamt
FROM tbl_orders
GROUP BY customer_id
)
WHERE sumamt > 10000; WITH tbl_sumamt AS --上面的子查询改成CTE 就是把子查询出来的表加了一个备注名
(
SELECT
customer_id,
sum(amount) AS sumamt
FROM tbl_orders
GROUP BY customer_id
)
SELECT *
FROM tbl_sumamt
WHERE sumamt > 10000;b. 支持多种JOIN算法
| 算法 | 原理 | 适用场景 |
|---|---|---|
| hash (默认) | 固定把右表导入内存构建Hash Table | 右表<内存 快 |
| direct | 拿左表的每行数据直接去右表查 | 如果右表底层是Hash-based结构，单次查询时间复杂度为O(1) |
| grace_hash | 将左右表按JOIN Key哈希分片(相同哈希函数左右表相同key会被分配到同一个分片) -> 左1分片和右1分片进行Hash Join -> 左2与右2 | 右表>内存 中快 |
| full_sorting_merge | 左右表先完整排序(排序内存不够会用到磁盘) -> merge(左右表一个指针匹配 匹配上都走一步 没匹配上数大的指针走一步) | 速度中 内存需求高 |
| partial_merge | 右表分块->右表块内排序 -> 左表分块 -> 左表每一行去右表每一块匹配 | 左右表>内存 速度慢 内存需求低 |
c. GROUP BY (abc) 操作增加了with rollup (all/a/ab/abc n+1组合) \with cube (2^n组合 其中非左前缀ac/bc/b/c会消耗更多的资源）\with totals (all/abc 2组合) 用来计算小计和总计。
d. 提供的常用函数
聚合: count、sum、avg、min、max、uniq→近似去重计数、uniqExact→精确去重计数
条件: if、multiIf→类似于case
条件聚合: countIf、sumIf
日期: today、toDate、toDateTime、toYear、toMonth、toStartOfDay()→返回这一天的开始时间、toStartOfMonth
类型转换: toString、toInt64、toFloat64
NULL: isNull(x) isNotNull(x) ifNull(x, 0)→为空返回0 coalesce(x1,x2,x3,x4)→返回第一个不为NULL的参数
字符串: length(str) lower(str) upper(str) concat(str1, str2) substring(str, 1, 3) replaceAll(str, 'old', 'new') trim(str) splitByChar(',', str)
数组: length([1,2,3]) arraySum([1,2,3]) arrayMax([1,2,3]) arrayMin([1,2,3])
数值计算:
abs(-10) → 10
round(12.345, 2) → 12.35
floor(12.8) → 12
ceil(12.1) → 13
sqrt(100) → 10
pow(2, 3) → 85.集群&副本 todo:
6.explain
- PLAN: 逻辑执行步骤(默认值)
PIPELINE: 具体执行步骤及数据流
AST: clickhouse怎么分析这个sql (一般不用)
SYNTAX: clickhouse对sql优化后的结果 - 关键字执行顺序 conn.2
- 实例
--sql语义: 从datasets system库中，统计每张表有多少个Part，降序，每个数据库只留前2条数据。 EXPLAIN PLAN SELECT --4.选字段 `database`, `table`, count(1) AS `cnt` FROM system.parts --1.查表 记录表的part信息 WHERE `database` IN ('datasets', 'system') --2.条件 字段database在('datasets', 'system')里面 GROUP BY --3.分组 `database`, `table` ORDER BY --5.排序 `database` ASC, `cnt` DESC LIMIT 2 BY `database` --6.每个database保留2条数据┌─explain───────────────────────────────────────────────────────────────┐ 1. │ Output: database, table, count() │ --7.输出结果到终端 2. │ │ 3. │ LimitBy │ --6.limit 4. │ │ Columns: database │ 5. │ │ Length 2 │ 6. │ │ Offset 0 │ 7. │ └──Sorting (Sorting for ORDER BY) │ --5.排序 这里会用min(机器核数,16)个线程数去排序 8. │ │ Sort description: database ASC, count() DESC │ 9. │ │ Per-stream LIMIT BY columns: database │ 10. │ │ Per-stream LIMIT BY length 2 │ 11. │ └──Aggregating │ --3.聚合 4.选字段 12. │ │ Keys: database, table │ 13. │ │ Aggregates: count() │ 14. │ │ Skip merging: 0 │ 15. │ └──Filter ((WHERE + Change column names to column identifiers)) │ --2.where过滤 16. │ │ Filter column: database IN ('datasets', 'system') │ 17. │ └──ReadFromSystemPartsBase │ --1.查表 18. │ Output: database, table │ └───────────────────────────────────────────────────────────────────────┘EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM numbers(10); --优化sql ┌─explain──────────────────────────────────────────────────────────────────────────┐ 1. │ SELECT if(equals(number, 1), 'hello', if(equals(number, 2), 'world', 'atguigu')) │ 2. │ FROM numbers(10) │ └──────────────────────────────────────────────────────────────────────────────────┘
7. 建表优化
- 时间定义为DateTime类型
CREATE TABLE tbl_orders ( `id` UInt32, `d` Int32 --可以直接指定为Datetime类型，在分区的时候就不用加toDate函数了 ) ENGINE = MergeTree PARTITION BY toYYYYMMDD(toDate(`d`)) -- ORDER BY id; - 尽量避免使用Nullable(T)，用逻辑上的哨兵数值代替
需要用额外的文件null_map用来标识数据是否为null
在执行聚合操作的时候需要先读null_map，额外的判断数据是否为null(在几十亿数据量下这个成本会被放大)
Nullable会给主键、排序键、skip index 等设计增加限制或额外处理(主要是指排序问题)
同mysql NULL=NULL(结果是unknow) sql需要用IS NULL判断 - 基数: 一个字段有多少不同的值
order by: 使用频率越高的字段放在越前面，基数大的字段放在order by里面会导致在做聚合的成本更高
8. 优化
- count()查统计数据直接返回 count(fld-)需要扫描列数据忽略NULL
- where 读取select列 -> 过滤不需要的行
prewhere 先读取少量的过滤列(prewhere后面的列) -> 把大量行过滤掉 -> 再按行读取select列，从而减少磁盘读取、解压的数据量
prewhere 的价值取决于过滤效果 0.比如一张表有1e行 100多个字段 select * from tbl prewhere uid=10001; 1.过滤后只有1000行 这个时候的收益就很高。2.如果过滤以后还有9000w行数据，反而会因为两次读表使得效率降低 - 数据采集
//建表的时候指定抽样规则 eg:SAMPLE BY intHash64(UserID) //hash数据会平均分布在max(uint64)里面 抽取1%时就取0~0.01*max(uint64) //确保了每次抽样数据一致，以及单个用户的行为不会被分割 即全部采集或全部不采集 //如果就是想混乱的随机采集 可以SAMPLE BY intHash64(RowID) RowID是每行唯一的字段SELECT Title,count(*) AS PageViews --sql语义: 抽样 统计CounterID=57 不同Title出现的次数 保留前1000条 FROM hits_v1 SAMPLE 0.1 --代表采样 ≈10%的数据,也可以是具体的条数 都是≈，因为单用户数据全部被采集或者全部不被采集 WHERE CounterID=57 GROUP BY Title ORDER BY PageViews DESC LIMIT 1000 - 只select需要的字段 减少io/数据解压，where使用分区字段 有效过滤
- order by结合where(减少io) limit(减少继续处理/排序的数据量)使用，如果没有使用建表的order by左前缀字段，就会全表扫描
- 避免select使用表达式 比如: a,b,c为本来就有的字段 a+b b+1这种表达式应该放到业务层去处理 而不是在sql里面
- 业务允许的时候 用近视去重uniqCombined(fld-a) 代替count(distinct fld-a)精准去重
SELECT COUNT(DISTINCT UserID) from hits_v1; --对字段UserID去重 精确计数 SELECT uniqCombined(UserID) from hits_v1; --使用的是hyperloglog和redis一致 - 批量写入时先排序，类似于顺序写和无序写的效率差
- 物化视图: 保存一个查询语句的查询结果(放在一张表里面)
插入数据视图结果也会改变，修改数据不会改变，如果需要改变可以用ReplacingMergeTree/AggregatingMergeTree 或者重新计算
创建视图的时候可以指定旧的数据是否计算，可以选择不计算，后续手动把部分需要的旧数据计算查询一遍插入视图
8. MaterializeMySQL库引擎
- 作用: clickhouse做为mysql的副本，监控mysql的Binlog实现数据的物化副本
- clickhouse的MergeTree不适合频繁的删改，会用新增代替删改 每行多加2个对应字段 _sign:行是否删除 _version:行版本号
- 实操
vim /etc/my.cnf server-id=1 log-bin=mysql-bin #开启binlog binlog_format=ROW #记录具体发生变化的数据行 gtid-mode=on #开启gtid enforce-gtid-consistency=1 #满足gtid一致性: 解决故障恢复以后 节点知道事务从哪继续执行 log-slave-updates=1 #记录本机binlog日志CREATE DATABASE ck_db ENGINE = MaterializeMySQL('127.0.0.1:3306','mysql_db','mysql_user','mysql_pw');
9. 监控
- 方案: clickhouse提供监控数据 -> Prometheus(普罗米修斯) 存数据、查数据 → Grafana 后台页面展示 （ps:都属于绿色安装比较简单）
- Grafana下载监控模板Json 官网 -> 导入模板 -> 设置告警
a. 备份
- 开源工具
- 命令
clickhouse-backup help clickhouse-backup tables --查看当前 ClickHouse 中的表 sudo clickhouse-backup create --创建备份 --schema:仅备份表结构 --data:仅备份数据 --table=dbname.*:指定表备份 sudo clickhouse-backup list --查看已有备份 sudo clickhouse-backup restore 2026-09-11T10-25-00 --恢复指定备份 --schema:仅恢复表结构 --data:仅恢复数据 --table=dbname.*:指定表恢复 sudo clickhouse-backup delete 2026-09-11T10-25-00 --删除指定备份 sudo clickhouse-backup upload 2026-09-11T10-25-00 --备份上传远程服务器 sudo clickhouse-backup upload 2026-09-11T10-25-00 --从远程存储下载备份
文档更新时间: 2026-09-14 21:30 作者：morninglu
