ClickHouse—高级

第一章 Explain查看执行计划

ClickHouse 20.6版本后引入原生的执行计划语法，本文档基于稳定版21.7.3.14

基本语法

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] 
SELECT ... [FORMAT ...]
1
2

PLAN：用于查看执行计划

• header：打印计划中各个步骤的 head 说明，默认值为0，关闭
• description：打印计划中各个步骤的描述，默认值为1，开启
• actions：打印计划中各个步骤的详细信息，默认值为0，关闭

AST：用于查看语法树

SYNTAX：用于优化语法

PIPELINE：用于查看PIPELINE 计划

• header：打印计划中各个步骤的 head 说明，默认关闭
• graph：用 DOT 图形语言描述管道图，默认关闭，查看相关的图形需要graphviz配合查看
• actions：若开启了 graph，紧凑打印，默认开启

注：PLAN、PIPELINE 还可以进行额外的显示设置，如上参数所示

案例实操

官网在线测试链接：ClickHouse Playground | ClickHouse Docs

1）查看PLAN

简单查询

explain plan select arrayJoin([1,2,3,null,null]);
1

复杂 SQL 的执行计划

explain select database,table,count(1) cnt from system.parts where 
database in ('datasets','system') group by database,table order by 
database,cnt desc limit 2 by database;
1
2
3

打开全部参数的执行计划

EXPLAIN header=1, actions=1,description=1 SELECT number from 
system.numbers limit 10;
1
2

2）AST语法树

EXPLAIN AST SELECT number from system.numbers limit 10;
1

3）SYNTAX语法优化

//先做一次查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'yingzi') FROM numbers(10);
//查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'yingzi') FROM numbers(10);

//开启三元运算符优化
SET optimize_if_chain_to_multiif = 1;
//再次查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'yingzi') FROM numbers(10);
//返回优化后的语句
SELECT multiIf(number = 1, \'hello\', number = 2, \'world\', \'xyz\') FROM numbers(10)
1
2
3
4
5
6
7
8
9
10
11

4）查看PIPELINE

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;
//打开其他参数
EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM 
numbers_mt(10000) GROUP BY number%20;
1
2
3
4

第二章 建表优化

数据类型

时间字段：建表时能用数值型或日期时间型表示的字段就不要用字符串，全 String 类型在以 Hive 为中心的数仓建设中常见，但ClickHouse环境不应受此影响

ClickHouse 底层将 DataTime 存储为时间戳 Long 类型，但不建议存储 Long 类型，因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好

create table t_type2(
     id UInt32,
     sku_id String,
     total_amount Decimal(16,2) ,
     create_time Int32 
) engine =ReplacingMergeTree(create_time)
     partition by toYYYYMMDD(toDate(create_time)) –-需要转换一次，否则报错
     primary key (id)
     order by (id, sku_id);
1
2
3
4
5
6
7
8
9

空值存储类型：官方指出 Nullable 类型几乎总是会拖累性能，存储Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示为空，或自行指定一个在业务中无意义的值

官网说明：可为空（类型名称) | ClickHouse Docs

分区和索引

分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为 Tuple()，以单表一亿数据为例，分区大小控制在 10-30 个为最佳

必须指定索引列，ClickHouse 中的索引列即排序列，通过order by指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；

• 满足高级列在前、查询频率大的在前原则.
• 基于特别大的不适合做索引列

表参数

Index_granularity 是用来控制索引粒度的，默认为8192，若非必要不建议调整

若表中不是必须保留全量数据，建议指定 TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL也可以通过 alter table 语句随时修改

写入和删除优化

• 尽量不要执行单条或小批量删除和插入操作，会产生小分区文件，给后台 Merge 任务带来巨大压力
• 不要一次写入太多分区，或数据写入太快。数据写太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）

写入太快报错

1. Code: 252, e.displayText() = DB::Exception: Too many parts(304). 
Merges are processing significantly slower than inserts
2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query) 
exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888 
bytes), maximum: 9.31 GiB
1
2
3
4
5

处理方式；

• Too many parts 处理：使用 WAL 预写日志，提高写入性能
• in_memory_parts_enable_wal：默认为 true

在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现

在服务器内存不充裕情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过

max_bytes_before_external_group_by、max_bytes_before_external_sort 参数来实现

常见配置

配置项主要在 config.xml、users.xml 中，基本上都在 users.xml 里

config.xml：Server Settings | ClickHouse Docs

users.xml：Settings | ClickHouse Docs

CPU资源

内存资源

存储

ClickHouse 不支持设置多数据目录，为了提升数据 io 性能，可以挂载虚拟卷组，一个卷组绑定多块物理磁盘提升读写性能，多数据查询场景 SSD 会比普通机械硬盘快 2-3 倍

第三章 ClickHouse 语法优化规则

ClickHouse 的 SQL优化规则是基于 RBO（Rule Based Optimization），下面列出一些优化规则

准备测试用表

1）上传官方数据集

// 解压到 clickhouse 数据路径
sudo tar -xvf hits_v1.tar -C /var/lib/clickhouse
sudo tar -xvf visits_v1.tar -C /var/lib/clickhouse
//修改所属用户
sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/data/datasets
sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/metadata/datasets
1
2
3
4
5
6

2）重启 clickhouse-server

sudo clickhouse restart
1

3）执行查询

clickhouse-client --query "SELECT COUNT(*) FROM datasets.hits_v1"
clickhouse-client --query "SELECT COUNT(*) FROM datasets.visits_v1"
1
2

注意：官方的 tar 包，包含了建库、建表语句、数据内容，这种方式不需要手动建库、建表，最方便

• hits_v1 表有130 多个字段，880多万条数据
• visits_v1 表有180 多个字段，160多万条数据

COUNT 优化

在调用 count 函数时，若使用的是 count()、count(*)，且没有 where 条件，则会直接使用 system.tables 的 total_rows

EXPLAIN SELECT count()FROM datasets.hits_v1;
Union
 Expression (Projection)
     Expression (Before ORDER BY and SELECT)
         MergingAggregated
         	ReadNothing (Optimized trivial count)
1
2
3
4
5
6

注意 Optimized trivial count，这是对 count 的优化

若 count 具体的列字段，则不会使用此项优化

EXPLAIN SELECT count(CounterID) FROM datasets.hits_v1;
Union
 Expression (Projection)
     Expression (Before ORDER BY and SELECT)
         Aggregating
             Expression (Before GROUP BY)
             	ReadFromStorage (Read from MergeTree)
1
2
3
4
5
6
7

消除子查询重复字段

下面语句子查询有两个重复的 id 字段，会被去重

EXPLAIN SYNTAX SELECT 
     a.UserID,b.VisitID,a.URL,b.UserID
     FROM hits_v1 AS a 
     LEFT JOIN ( 
         SELECT UserID, UserID as HaHa, VisitID 
     FROM visits_v1) AS b 
     USING (UserID)limit 3;

//返回优化语句
SELECT UserID,VisitID,URL,b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN 
(
 SELECT UserID,VisitID
 FROM visits_v1
) AS b 
USING (UserID)LIMIT 3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

谓词下推

当 group by 有 having 子句，但没有 with cube、with rollup 或者 with totals 修饰的时候，having 过滤会下推到 where 提前过滤。例如在下面的查询中，HAVING name 变成了 WHERE name，在group by之前过滤

EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID HAVING UserID = 
'8585742290196126178';

//返回优化语句
SELECT UserID
FROM hits_v1
WHERE UserID = \'8585742290196126178\'
GROUP BY UserID
1
2
3
4
5
6
7
8

子查询也支持谓词下推

EXPLAIN SYNTAX
SELECT *
FROM 
(
 SELECT UserID
 FROM visits_v1
)
WHERE UserID = '8585742290196126178'

//返回优化后的语句
SELECT UserID
FROM 
(
 SELECT UserID
 FROM visits_v1
 WHERE UserID = \'8585742290196126178\'
)
WHERE UserID = \'8585742290196126178\'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

再来一个复杂例子：

EXPLAIN SYNTAX 
SELECT * FROM (
 SELECT * 
 FROM 
 (
 SELECT UserID 
 FROM visits_v1) 
 UNION ALL 
 SELECT *
 FROM 
 (
 SELECT UserID 
 FROM visits_v1)
)
WHERE UserID = '8585742290196126178'

//返回优化后的语句
SELECT UserID
FROM 
(
 SELECT UserID
 FROM 
 (
 SELECT UserID
 FROM visits_v1
 WHERE UserID = \'8585742290196126178\'
 )
 WHERE UserID = \'8585742290196126178\'
 UNION ALL
 SELECT UserID
 FROM 
 (
 SELECT UserID
 FROM visits_v1
 WHERE UserID = \'8585742290196126178\'
 )
 WHERE UserID = \'8585742290196126178\'
)
WHERE UserID = \'8585742290196126178\'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

聚合计算外推

聚合函数内的计算，会外推

EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM visits_v1

//返回优化后的语句
SELECT sum(UserID) * 2
FROM visits_v1
1
2
3
4
5
6
7

聚合函数消除

如果对聚合键，也就是 group by key 使用 min、max、any 聚合函数，则将函数消除

EXPLAIN SYNTAX
SELECT sum(UserID * 2),max(VisitID),max(UserID)
FROM visits_v1
GROUP BY UserID

//返回优化后的语句
SELECT sum(UserID) * 2,max(VisitID),UserID
FROM visits_v1
GROUP BY UserID
1
2
3
4
5
6
7
8
9

删除重复的 order by key

重复的聚合键 id 字段会被去重

EXPLAIN SYNTAX
SELECT *
FROM visits_v1
ORDER BY
 UserID ASC,
 UserID ASC,
 VisitID ASC,
VisitID ASC

//返回优化后的语句：
select
……
FROM visits_v1
ORDER BY 
 UserID ASC,
VisitID ASC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

删除重复的 limit by key

重复声明的 name 字段会被去重

EXPLAIN SYNTAX
SELECT *
FROM visits_v1
LIMIT 3 BY
 VisitID,
 VisitID
LIMIT 10
//返回优化后的语句：

select
……
FROM visits_v1
LIMIT 3 BY VisitID
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14

删除重复的 USING Key

重复的关联键 id 字段会被去重

EXPLAIN SYNTAX
SELECT
 a.UserID,
 a.UserID,
 b.VisitID,
 a.URL,
 b.UserID
FROM hits_v1 AS a
LEFT JOIN visits_v1 AS b USING (UserID, UserID)

//返回优化后的语句：
SELECT 
 UserID,
 UserID,
 VisitID,
 URL,
 b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN visits_v1 AS b USING (UserID)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

标量替换

若子查询只返回一行数据，在被引用的时候用标量替换

EXPLAIN SYNTAX
WITH 
 (
 SELECT sum(bytes)
 FROM system.parts
 WHERE active
 ) AS total_disk_usage
SELECT
 (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

//返回优化后的语句：
WITH CAST(0, \'UInt64\') AS total_disk_usage
SELECT 
 (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
 table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

三元运算优化

若开启了 optimize_if_chain_to_multiif 参数，三元运算符会被替换成 multilf 函数

EXPLAIN SYNTAX 
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'yingzi') 
FROM numbers(10) 
settings optimize_if_chain_to_multiif = 1;

//返回优化后的语句：
SELECT multiIf(number = 1, \'hello\', number = 2, \'world\', \'yingzi\')
FROM numbers(10)
SETTINGS optimize_if_chain_to_multiif = 1
1
2
3
4
5
6
7
8
9

第四章 查询优化

单表查询

Prewhere 替代 where

Prewhere 和 where 语句的作用相同，用来过滤数据。prewhere 只支持*MergeTree族系列引擎的表

• 读取指定的列数据，判断数据过滤，等待数据过滤后再读取 select 声明的列字段来补全其余属性
• 当查询列明显多于筛选列时，使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作
• 再某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高

#关闭 where 自动转 prewhere(默认情况下， where 条件会自动优化成 prewhere)
set optimize_move_to_prewhere=0; 
1
2

默认情况下，我们不会关闭 where 自动优化成 prewhere，但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere

• 使用常量表达式
• 使用默认值为 alias 类型的字段
• 包含了 arrayJOIN、globalin、globalNotln、indexHint的查询
• select 查询的列字段和 where 的谓词相同
• 使用了主键字段

数据采样

通过采样运算可极大提升数据分析的性能

SELECT Title,count(*) AS PageViews 
FROM hits_v1
SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000
1
2
3
4
5
6

采样修饰符只有在 MergeTree engine 表中才有效，且在创建表时需要指定采样策略

列裁剪与分区裁剪

数据量太大时应避免使用 select * 操作，查询的性能会与查询的字段大小和数量成线性变化，字段越少，消耗的 io 资源越少，性能就会越高

反例：
select * from datasets.hits_v1;
正例：
select WatchID, 
 JavaEnable, 
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID
from datasets.hits_v1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

分区裁剪就是只读取需要的分区，再过滤条件中指定

select WatchID, 
 JavaEnable, 
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID
from datasets.hits_v1
where EventDate='2014-03-23';
1
2
3
4
5
6
7
8
9
10
11
12
13

orderby 结合 where、limit

千万以上数据集进行 order by查询时需要搭配 where 条件和 limit 语句一起使用

#正例：
SELECT UserID,Age
FROM hits_v1 
WHERE CounterID=57
ORDER BY Age DESC LIMIT 1000

#反例：
SELECT UserID,Age
FROM hits_v1 
ORDER BY Age DESC
1
2
3
4
5
6
7
8
9
10

避免构建虚拟列

如非必须，不要再结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑再前端进行处理，或者再表中构造实际字段进行额外存储

反例：
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;

正例：拿到 Income 和 Age 后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
SELECT Income,Age FROM datasets.hits_v1;
1
2
3
4
5

uniqCombined 替代 distinct

性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接收 2% 左右的数据误差，可直接使用这种去重方式提升查询性能

Count(distinct)会使用 uniqExact 精确去重

不建议在千万级不同数据上执行 distinct 去重查询，改为近似去重 uniqCombined

反例：
select count(distinct rand()) from hits_v1;
正例：
SELECT uniqCombined(rand()) from datasets.hits_v1
1
2
3
4

使用物化视图

参考第六章

其他注意事项

• 查询熔断：为了避免因个别慢查询引起的服务雪崩问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，若用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作
• 关闭虚拟内存：物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存
• 配置 join_use_nulls：为每一个账户添加 join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相 应字段会返回该字段相应数据类型的默认值，而不是标准 SQL 中的 Null 值
• 批量写入时先排序：批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对 需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致 ClickHouse 无法及时对 新导入的数据进行合并，从而影响查询性能
• 关注CPU：CPU一般会在50%左右会查询波动，达到70%会大范围的查询超时，cpu 是最关键的指标

多表关联

准备表和数据

#创建小表
CREATE TABLE visits_v2 
ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from visits_v1 limit 10000;

#创建 join 结果表：避免控制台疯狂打印数据
CREATE TABLE hits_v2 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from hits_v1 where 1=0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

用 IN 代替 JOIN

当多表联查时，查询的数据仅从其中一张表出时，会考虑用 IN 操作而不是 JOIN

insert into hits_v2
select a.* from hits_v1 a where a. CounterID in (select CounterID from  visits_v1);

#反例：使用 join
insert into table hits_v2
select a.* from hits_v1 a left join visits_v1 b on a. CounterID=b. 
CounterID;
1
2
3
4
5
6
7

大小表JOIN

多表 join 时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较， ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录 到左表中查找该记录是否存在，所以右表必须是小表

(1)小表在右
insert into table hits_v2
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. CounterID;

(2)大表在右
insert into table hits_v2
select a.* from visits_v2 b left join hits_v1 a on a. CounterID=b. CounterID;
1
2
3
4
5
6
7

注意谓词下推（版本差异）

ClickHouse 在 join 查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作，需要注意的是，是否执行谓词下推，对性能影响差别很大（新版本中已经不存在此问 题，但是需要注意谓词的位置的不同依然有性能的差异）

Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. 
CounterID
having a.EventDate = '2014-03-17';
Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. 
CounterID
having b.StartDate = '2014-03-17';
insert into hits_v2
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. 
CounterID
where a.EventDate = '2014-03-17';
insert into hits_v2
select a.* from (
 select * from 
 hits_v1 
 where EventDate = '2014-03-17'
) a left join visits_v2 b on a. CounterID=b. CounterID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

分布式表使用 GLOBAL

两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字，右表只会在接收查询请求 的那个节点查询一次，并将其分发到其他节点上。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N²次（N 是该分布式表的分片数量），这就是查询放大，会带来很大开销

使用字典表

将一些需要关联分析的业务创建成字典表进行join操作，前提是字典表不宜太大，因为字典表会常驻内存

提前过滤

通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的

第五章 数据一致性（重点）

查询 CK 手册发现，即便对数据一致性支持最好的 Mergetree，也只是保证最终一致性

在使用 ReplacingMergeTree、SummingMergeTree 这类表引擎的时候，会出现短暂数据不一致的情况

在某些对一致性非常敏感的场景，通常有以下几种解决方案：

准备测试表和数据

（1）创建表

CREATE TABLE test_a(
 user_id UInt64,
 score String,
 deleted UInt8 DEFAULT 0,
 create_time DateTime DEFAULT toDateTime(0)
)ENGINE= ReplacingMergeTree(create_time)
ORDER BY user_id;
1
2
3
4
5
6
7

其中：

• user_id：数据去重更新的标识
• create_time：版本号字段，每组数据中create_time 最大的一行表示最新的数据
• deleted：自定的一个标记位，0代表未删除，1代表删除

（2）写入1000万，测试数据

INSERT INTO TABLE test_a(user_id,score)
WITH(
 SELECT ['A','B','C','D','E','F','G']
)AS dict
SELECT number AS user_id, dict[number%7+1] FROM numbers(10000000);
1
2
3
4
5

（3）修改前 50 万行数据，修改内容包括 name 字段、create_time 版本号字段

INSERT INTO TABLE test_a(user_id,score,create_time)
WITH(
 SELECT ['AA','BB','CC','DD','EE','FF','GG']
)AS dict
SELECT number AS user_id, dict[number%7+1], now() AS create_time FROM 
numbers(500000);
1
2
3
4
5
6

（4）统计总数

SELECT COUNT() FROM test_a;
1

手动OPTIMIZE

在写入数据后，立刻执行 OPTIMIZE 强制触发新写入分区的合并动作

OPTIMIZE TABLE test_a FINAL;

语法：OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | 
PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE [BY expression]]
1
2
3
4

通过 Group by 去重

（1）执行去重的查询

SELECT
 user_id ,
 argMax(score, create_time) AS score, 
 argMax(deleted, create_time) AS deleted,
 max(create_time) AS ctime 
FROM test_a 
GROUP BY user_id
HAVING deleted = 0;
1
2
3
4
5
6
7
8

• argMax(field1,field2)：按照field2的最大值取field1的值

当我们更新数据时，会写入一行新的数据，例如上面语句中，通过查询最大的create_time得到修改后的score字段值

（2）创建视图，方便测试

CREATE VIEW view_test_a AS
SELECT
 user_id ,
 argMax(score, create_time) AS score, 
 argMax(deleted, create_time) AS deleted,
 max(create_time) AS ctime 
FROM test_a 
GROUP BY user_id
HAVING deleted = 0;
1
2
3
4
5
6
7
8
9

（3）插入重复数据，再次查询

#再次插入一条数据
INSERT INTO TABLE test_a(user_id,score,create_time)
VALUES(0,'AAAA',now())
#再次查询
SELECT *
FROM view_test_a
WHERE user_id = 0;
1
2
3
4
5
6
7

（4）删除数据测试

#再次插入一条标记为删除的数据
INSERT INTO TABLE test_a(user_id,score,deleted,create_time) 
VALUES(0,'AAAA',1,now());
#再次查询，刚才那条数据看不到了
SELECT *
FROM view_test_a
WHERE user_id = 0;
1
2
3
4
5
6
7

通过FINAL查询

在查询语句后增加 FINAL 修饰符，这样在查询的过程中将会执行 Merge 的特殊逻辑（例如数据去重预聚合等）

但是这种方法在早期版本级别没人使用，因为在增加 FINAL 之后，我们的查询将会变成一个单线程的执行过程，查询速度非常慢

FINAL 查询最终的性能和很多因素相关，列字段的大小、分区的数量等等都会影响到最 终的查询时间，所以还要结合实际场景取舍

参考链接：Parallel final by KochetovNicolai · Pull Request #10463 · ClickHouse/ClickHouse (github.com)

第六章 物化视图

ClickHouse 的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提升。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算的表，创建的过程它是用了一个特殊引擎，加上后来 as select，就是 create 一个 table as select 的写法

“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表 join 之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照（snapshot）

概述

物化视图与普通视图的区别

• 物化视图：将查询的结果根据相应的引擎存入到了磁盘或内存中，或对数据重新进行了组织，物化视图可以理解为是完全的一种新表
• 普通视图：不保存数据，仅保存查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询

优缺点

• 优：查询速度快，要是把物化视图的规则全部写好，比原数据查询快很多，总的行数少了，因为都预计算了
• 缺：它的本质是一个流式数据的使用场景，是累加式的技术，故要用历史数据做去重、去核的分析，在物化视图里不太好用。若一张表加了好多物化视图，在写这张表时会消耗很多机器资源，如数据带宽占满、存储以下增加很多

基本语法

create 语法，会创建一个隐藏的目标来保存视图数据，也可以 TO 表名，保存到一张显示的表，没有加 TO 表名，表名默认是 .inner.物化视图名

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] 
[ENGINE = engine] [POPULATE] AS SELECT ...
1
2

1）创建物化视图的限制

• 必须指定物化视图的 engine 用于数据存储
• TO [db].[table]语法的时候，不得使用 POPULATE
• 查询语句（select）可以包含下面的子句：DISTINCT, GROUP BY, ORDER BY, LIMIT…
• 物化视图的 alter 操作有些限制，操作起来不大方便
• 若物化视图的定义使用了 TO [db].name 子语句，则可以将目标表的视图卸载DETACH再装载ATTACH

2）物化视图的数据更新

• 物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新
• POPULATE 关键字决定了物化视图的更新策略
  • 若有 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 create table … as
  • 若无 POPULATE 则物化视图在创建之后没有数据，只有同步之后写入源表的数据
  • clickhouse 官方并不推荐使用 POPULATE，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图
• 物化视图不支持同步删除，若源表的数据不存在（删除了）则物化视图的数据任然保留
• 物化视图是一种特殊的数据表，可以用 show tables 查看
• 物化视图数据的删除
• 物化视图的删除

案例实操

对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免查询时重复计算的过程，物化视图会在有新数据插入时进行更新

准备测试用表和数据

#建表语句
CREATE TABLE hits_test
(
 EventDate Date, 
 CounterID UInt32, 
 UserID UInt64, 
 URL String, 
 Income UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
1
2
3
4
5
6
7
8
9
10
11
12
13
14

#导入一些数据
INSERT INTO hits_test 
 SELECT 
 EventDate,
 CounterID,
 UserID,
 URL,
 Income 
FROM hits_v1 
limit 10000;
1
2
3
4
5
6
7
8
9
10

创建物化视图

#建表语句
CREATE MATERIALIZED VIEW hits_mv 
ENGINE=SummingMergeTree
PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID)) 
AS SELECT
UserID,
EventDate,
count(URL) as ClickCount,
sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate >= '2014-03-20' #设置更新点,该时间点之前的数据可以另外通过
 #insert into select …… 的方式进行插入
GROUP BY UserID,EventDate;

##或者可以用下列语法，表 A 可以是一张 mergetree 表
CREATE MATERIALIZED VIEW 物化视图名 TO 表 A
AS SELECT FROM 表 B;

#不建议添加 populate 关键字进行全量更新
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

导入增量数据

#导入增量数据
INSERT INTO hits_test 
SELECT 
 EventDate,
 CounterID,
 UserID,
 URL,
 Income 
FROM hits_v1 
WHERE EventDate >= '2014-03-23' 
limit 10;
#查询物化视图
SELECT * FROM hits_mv;
1
2
3
4
5
6
7
8
9
10
11
12
13

导入历史数据

#导入增量数据
INSERT INTO hits_mv
SELECT
 UserID,
 EventDate,
 count(URL) as ClickCount,
 sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate = '2014-03-20'
GROUP BY UserID,EventDate
#查询物化视图
SELECT * FROM hits_mv;
1
2
3
4
5
6
7
8
9
10
11
12

第七章 MaterializeMySQL引擎

概述

MySQL 的用户群体很大，为了能够增强数据的时效性，很多解决会利用 binlog 将数据写入到 ClickHouse。为了能够监听 binloh 事件，我们需要用类似 canal 这样的第三方中间件，这无疑增加了系统的复杂度

ClickHouse 20.8.2.3 版本新增加了 MaterializeMySQL 的 database 引擎，该 database 能 映 射 到 MySQL 中 的 某 个 database ， 并 自 动 在 ClickHouse 中 创 建 对 应 的 ReplacingMergeTree。ClickHouse 服务做为 MySQL 副本，读取 Binlog 并执行 DDL 和 DML 请 求，实现了基于 MySQL Binlog 机制的业务数据库实时同步功能

特点

MaterializeMySQL 同时支持全量和增量同步，在 database 创建之初会全量同步 MySQL 中的表和数据，之后则会通过 binlog 进行增量同步

MaterializeMySQL database 为其所创建的每张 ReplacingMergeTree 自动增加了 _sign 和 _version 字段。

• _version：用作ReplacingMergeTree 的 ver 版本参数，每当监听到 insert、update 和 delete 事件时，在 databse 内全局自增
• _sign：用于标记是否被删除，取值 1 或 -1

目前 MaterializeMySQL 支持如下几种 binlog 事件

• MYSQL_WRITE_ROWS_EVENT：_sign = 1，_version ++
• MYSQL_DELETE_ROWS_EVENT：_sign = -1，_version ++
• MYSQL_UPDATE_ROWS_EVENT：新数据 _sign = 1
• MYSQL_QUERY_EVENT：支持 CREATE TABLE 、DROP TABLE 、RENAME TABLE 等

使用细则

（1）DDL 查询

MySQL DDL 查询被转换成相应的 ClickHouse DDL 查询（ALTER, CREATE, DROP, RENAME），如果ClickHouse 不能解析某些 DDL 查询，该查询将被忽略

（2）数据复制

MaterializeMySQL 不支持直接插入，删除和更新查询，而是将 DDL 语句进行相应转换：

• MySQL INSERT 查询被转换为 INSERT with _sign = 1
• MySQL DELETE 查询被转换为 INSERT with _sign = -1
• MySQL UPDATE 查询被转换成 INSERT with _sign=1 和 INSERT with _sign = -1

（3）SELECT 查询

若在 SELECT 查询中没有指定 _version，则使用 FINAL 修饰符，返回 _version 的最大值对应的数据，即最新版本的数据

若在 SELECT 查询中没有指定 _sign，则该默认使用 WHERE _ign = 1，即返回未删除状态（_sign = 1）的数据

（4）索引转换

ClickHouse 数据库表会自动将 MySQL 主键和索引子句转换为 ORDER BY 元组。ClickHouse 只有一个物理顺序，由 ORDER BY 子句决定。如果需要创建新的物理顺序， 请使用物化视图

案例实操

MySQL 开启 binlog 和 GTID 模式

（1）确保 MySQL 开启了 binlog 功能，且格式为 ROW

sudo vim /etc/my.cnf

server-id=1 
log-bin=mysql-bin
binlog_format=ROW
1
2
3

（2）开启 GTID 模式

若 clickhouse 使用的是 20.8 prestable 之后发布的版本，那么 MySQL 还需要配置开启 GTID 模式，这种方式在 mysql 主从模式下可以确保数据同步的一致性（主从切换时）

gtid-mode=on
enforce-gtid-consistency=1 # 设置为主从强一致性
log-slave-updates=1 # 记录日志
1
2
3

GTID 是 MySQL 复制增强版，从 MySQL 5.6 版本开始支持，目前已经是 MySQL 主流 复制模式。它为每个 event 分配一个全局唯一 ID 和序号，我们可以不用关心 MySQL 集群主从拓扑结构，直接告知 MySQL 这个 GTID 即可

sudo systemctl restart mysqld
1

准备 MySQL 表和数据

（1）在 MySQL 中创建数据表并写入数据

CREATE DATABASE testck;
CREATE TABLE `testck`.`t_organization` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int NOT NULL,
 `name` text DEFAULT NULL,
 `updatetime` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY (`code`)
) ENGINE=InnoDB;
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1000,'Realinsight',NOW());
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1001, 'Realindex',NOW());
INSERT INTO testck.t_organization (code, name,updatetime) 
VALUES(1002,'EDT',NOW());
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

（2）创建第二张表

CREATE TABLE `testck`.`t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO testck.t_user (code) VALUES(1);
1
2
3
4
5
6

开启 ClickHouse 物化引擎

set allow_experimental_database_materialize_mysql=1;
1

创建复制管道

（1）ClickHouse 中创建 MaterializeMySQL 数据库

CREATE DATABASE test_binlog ENGINE = 
MaterializeMySQL('hadoop1:3306','testck','root','000000');
1
2

其中 4 个参数分别是 MySQL 地址、databse、username 和 password

（2）查看ClickHouse 的数据

use test_binlog;
show tables;
select * from t_organization;
select * from t_user;
1
2
3
4

修改数据

（1）在 MySQL 中修改数据

update t_organization set name = CONCAT(name,'-v1') where id = 1
1

（2）查看 clickhouse 日志可以看到 binlog 监听事件，查询 clickhouse

select * from t_organization;
1

删除数据

（1）MySQL 删除数据：

DELETE FROM t_organization where id = 2;
1

（2）ClickHouse，日志有 DeleteRows 的 binlog 监听事件，查看数据

select * from t_organization;
1

（3）在刚才的查询中增加 _sign和_version虚拟字段

select *,_sign,_version from t_organization order by _sign desc,_version desc;
1

在查询时，对于已经被删除的数据，_sign = -1，ClickHouse 会自动重写 SQL，将 _sign = -1，的数据过滤掉

对于修改的数据，则自动重写 SQL，为其增加FINAL修饰符

select * from t_organization
等同于
select * from t_organization final where _sign = 1
1
2
3

删除表

（1）在 mysql 执行删除表

drop table t_user;
1

（2）此时在 clickhouse 处会同步删除对应表，如果查询会报错

show tables;
select * from t_user;
DB::Exception: Table scene_mms.scene doesn't exist..
1
2
3

（3）mysql 新建表，clickhouse 可以查询到

CREATE TABLE `testck`.`t_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `code` int,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO testck.t_user (code) VALUES(1);
#ClickHouse 查询
show tables;
select * from t_user;
1
2
3
4
5
6
7
8
9

第八章 常见问题排查

分布式 DDL 某数据节点的副本不执行

• 问题：使用分布式 ddl 执行命令 create table on cluster xxxx 某个节点上没有创建表，但是 client 返回正常，查看日志有如下报错

 xxx.xxx: Retrying createReplica(), because some other replicas 
were created at the same time
1
2

• 解决：重启该不执行的节点

数据副本表和数据不一致

• 问题：由于某个数据节点副本异常，导致两数据副本表不一致，某个数据副本缺少表，需要将两个数据副本调整一致

• 解决：在缺少表的数据副本节点上创建缺少的表，创建为本地表，表结构可以在其他数据副本通过 show create table xxxx 获取

表结构创建后，clickhouse 会自动从其他副本同步该表数据，验证数据量是否一致即可

副本节点全量恢复

• 问题：某个数据副本异常无法启动，需要重新搭建副本

• 解决：清空异常副本节点的 metadata 和 data 目录。从另一个正常副本将 metadata 目录拷贝过滤（这一步之后可以启动数据库，但是只有表结构没有数据）

  执行sudo -u clickhouse touch /data/clickhouse/flags/force_restore_data

  启动数据库

数据副本启动缺少 zk 表

• 问题：某个数据副本表在 zk 上丢失数据，或者不存在，但是 metadata 元数据里存在，导致启动异常

  Can’t get data for node /clickhouse/tables/01-
  02/xxxxx/xxxxxxx/replicas/xxx/metadata: node doesn’t exist (No node): 
  Cannot attach table xxxxxxx
  1
  2
  3

• 解决：metadata 中移除该表的结构文件，如果多个表报错都移除 mv metadata/xxxxxx/xxxxxxxx.sql /tmp/

  启动数据库

  手工创建缺少的表，表结构从其他节点 show create table 获取

  创建后会自动同步数据，验证数据是否一致

ZK table replicas 数据未删除，导致重建表报错

• 问题：重建表的过程，先使用 drop table xxx on cluster xxx，各节点在 clickhouse 上table 已物理删除，但是zk 里面针对某个 clickhouse 节点的 table meta 信息被删除（低概率事件），因 zk 里存在该表的 meta 信息，导致再次创建该表 create table xxx on cluster，该节点无法创建表，报错

  Replica /clickhouse/tables/01-03/xxxxxx/xxx/replicas/xxx already exists.
  1

• 解决：从其他数据副本 cp 该 table 的 metadata sql 过来

  重启节点

Clickhouse 节点意外关闭

• 问题：模拟其中一个节点意外宕机，在大量 insert 数据的情况下，关闭某个节点

• 现象：数据写入不受影响、数据查询不受影响、建表 DDL 执行到异常节点会卡住，报错

  Code: 159. DB::Exception: Received from localhost:9000. DB::Exception: 
  Watching task /clickhouse/task_queue/ddl/query-0000565925 is executing 
  longer than distributed_ddl_task_timeout (=180) seconds. There are 1 
  unfinished hosts (0 of them are currently active), they are going to 
  execute the query in background
  1
  2
  3
  4
  5

• 解决：启动异常节点，期间其他副本写入数据会自动同步，其他副本的建表 DLL 也会同步

其他问题参考：

[常见问题 (aliyun.com)](