ClickHouse学习笔记

学习视频链接：
https://www.bilibili.com/video/BV1Yh411z7os/?spm_id_from=333.1007.top_right_bar_window_custom_collection.content.click&vd_source=05e91bcf9e12725fd6d21d00acc4851f

官方文档：
https://clickhouse.com/

部署推荐：128G服务器，100G内存，32线程内存。

Click House入门

---

使用过程中踩坑笔记：http://t.csdn.cn/elvDm

---

• OLAP型数据库本就不应该执行删除、更新操作。
• CH将数据划分为多个Partition，每个Partition进一步划分为多个Index Granularity（索引粒度），然后多个CPU核心分别处理器中的一部分实现并行数据处理。这种设计下，单条Query就能利用整机所有CPU，达到极致的并行处理能力，降低查询延时。
• 对于高qps（Query per Second）的查询业务，ClickHouse并不是强项。
• 不适合做初始存储，适合存储宽表。
• 避免做join操作：
  • 简单原理：假设 table_A join table_B
  • table_B的表会先加载入内存，再逐条匹配table_A中的数据，不论大小表。

数据类型

整型

• int8 - byte
• int16 - short
• int32 - int
• int64 - long
• 无符号
  • Uint8
  • Uint16
  • Uint32
  • Uint64

浮点型

建议不用double，进行运算时会精度丢失，一般用于数值比较小，不涉及大量计算统计的，精度要求不高的数据。

例：1-0.9 = 0.099999999……

• Float32 - float
• Float64 - double

布尔型

没有单独类型存储布尔值，可使用Uint8类型，取值限制为0、1

Decimal型

使用场景：一般金额字段、汇率、利率等字段为了保证小数点精度，都是用Decimal进行存储。

NULL

NOTE：
Using `Nullable` almost always negatively affects performance, keep this in mind when designing your databases.
1
2

如果要使用Null，可以定义字段中Null值为业务上没有意义的字段，比如：年龄的Null为-1。

表引擎

https://clickhouse.com/docs/zh/engines/table-engines/

表引擎（即表的类型）决定了：

特别注意：引擎的命名大小写敏感。
命名方式：大驼峰：BigTableData
1
2

• 数据的存储方式和位置，写到哪里以及从哪里读取数据。
• 支持哪些查询以及如何支持。
• 并发数据访问。
• 索引的使用（如果存在）。
• 是否可以执行多线程请求。
• 数据复制参数。

TinyLog

• 不支持索引
• 没有并发控制
• 一般保存少量的数据。

Memory

• 优点：快
• 缺点：服务器重启数据就会消失
• 不支持索引

集成引擎

含义：将ClickHouse数据直接映射到外部引擎中，在ClickHouse上进行查询。

因此：ClickHouse这种方式并不拥有数据，只做查询。

MySql

思考：
是不是可以直接把所有游戏表映射到CH中，进行查询？
1
2

https://clickhouse.com/docs/en/engines/table-engines/integrations/mysql

MergeTree

• ClickHouse最强大的表引擎
• 支持索引和分区
• 有很多衍生引擎

👉建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2,
    ...
    PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
    PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
) ENGINE = MergeTree()
ORDER BY expr // 必选项
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE example_table
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
primary key(a) // 注意：主键不存在唯一约束！！！
ORDER BY d;
1
2
3
4
5
6
7
8
9
10

PARTITION BY（可选）

👉作用

降低扫描范围，优化查询速度。

👉如果不填

只会使用一个分区-all。

👉分区目录

以列文件+索引文件+表定义文件组成。

👉并行

分区后，以分区为单位并行处理。

官方建议以“天”为分区条件。
1

• 分区不会加速查询。

文件含义

<分区值_最小分区块编号_最大分区块编号_合并层级>
👉PartitionId
	数据分区ID生成规则：
	数据分区规则由分区ID决定，分区ID由PARTITION BY分区键决定。根据分区键字段类型，ID生成规则可分为：
		未定义分区键：
		- 没有定义PARTITION BY，默认生成一个目录名为all的数据分区，所有数据均存放在all目录下。
		整形分区键：
		- 分区键为整形，那么直接用该整形值的字符串形式作为分区ID。
		日期类分区键：
		- 分区键为日期类型，或者可以转化成日期类型。
		其他类型分区键：
		- String、Float类型等，通过128位的Hash算法取其Hash值作为分区ID。
👉MinBlockNum
	最小分区块编号，自增类型，从1开始向上递增。每产生一个新的目录分区就香上递增一个数字。
👉MaxBlockNum
	最大分区块编号，新创建的分区MinBlockNum等于MaxBlockNum的编号。
👉Level
	合并的层级，被合并的次数。合并次数越多，层级值越大。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

👉bin文件：数据文件

👉mrk：标记文件

👉count.txt：存储数据条数

👉data.bin

数据文件，表里面的数据存储在这里面

老版本中（21.7之前）是每一列存储一个.bin和.mrk2文件

👉columns.txt

列的结构信息

👉primary.idx

索引文件——>稀疏索引

👉minmax_create_time.idx

分区键的最大最小值

数据写入与分区合并

任何一个批次的数据写入都会产生一个临时分区，不会纳入任何已有分区。写入后一段时间（10-15mins后），clickhouse会自动执行合并操作（也可以手动通过optimize执行合并），将临时分区的数据，合并到以后分区中。

-- 加入了分区名表示仅对某个分区进行合并
optimize table 表名 [partition 分区名] final;
1
2

Primary Key（可选）

仅提供了一级索引，但不是唯一约束。

index granularity：索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。ClickHosue中的MergeTree默认是8192。官方不建议修改这个值，除非该列存在大量重复值，例如：一个分区中几万行才有一个不同数据。

Order By（必选）

分区内排序！！！

• 由于稀疏索引的类二分查找的特点，所以必须排序。
• 有序数据查询更加迅速。

要求：主键必须是order by字段的前缀字段！！！

例如：order by的字段是(id,sku_id)，主键若只有一个，只能是id，不能是sku_id。

二级索引

在一级索引分块的基础上，再次进行汇总

create table t_order_mt2(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time Datetime,
    -- GRANULARITY 是设定二级索引对于一级索引粒度的分区粒度
    -- 例如：一级索引：[0,5],[5,10],[10,15],[15,20]……
    -- GRANULARITY 2 ----> 则二级索引:[0,10],[10,20]……
    INDEX a total_amount TYPE min max GRANULARITY 5
)engine = MergeTree
partition by toYYYYMMDD(create_time)
primary key(id)
order by(id,sku_id)
1
2
3
4
5
6
7
8
9
10
11
12
13

数据TTL

TTL：Time To Live，MergeTree提供了可以管理数据表or列的生命周期功能。

列级别TTL

TTL不能用于键列。

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#creating-a-table-with-ttl

Creating a table with TTL

CREATE TABLE example_table
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;
1
2
3
4
5
6
7
8
9
10

Adding TTL to a column of an existing table

ALTER TABLE example_table
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 DAY;
1
2
3

表级别TTL

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-table-ttl

👉过期可以移动数据到指定位置。

ReplacingMergeTree

相对于MergeTree，只是多一个去重的功能。根据order by的字段去重。

👉去重时机

数据去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以无法预先做出计划，有一些数据可能仍未被处理。

👉去重范围

去重只会在分区内部进行去重，不能执行跨分区的去重。

因此，ReplacingMergeTree能力有限，不能保证没有重复的数据出现。

create table t_order_rmt(
    in UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time Datetime
)engine = ReplacingMergeTree(create_time) -- 这里create_time为版本字段
partition by toYYYYMMDD(create_time)
primary key(id)
order by(id,sku_id);
1
2
3
4
5
6
7
8
9

ReplacingMergeTree()填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条。

• 新版本会在插入数据的时候进行一次去重
• 实际上利用order by字段作为唯一键
• 去重不能跨分区
• 只有同一批插入or合并分区才会进行去重
• 认定重复的数据，保留版本字段值最大的
• 如果版本字段相同，则按照插入数据的顺序保留最后一笔

SummingMergeTree

👉使用场景： 对于不查询明细，只关心以维度进行汇总聚合结果的场景。

👉预聚合功能：①分区内聚合；②分片合并时才会聚合。

create table t_order_rmt(
    in UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time Datetime
)engine = SummingMergeTree(total_amount) -- 这里total_amount为聚合字段
partition by toYYYYMMDD(create_time)
primary key(id)
order by(id,sku_id); -- 根据这两个字段进行group by，预聚合
1
2
3
4
5
6
7
8
9

• 以SummingMergeTree（）中指定的列作为汇总数据列；
• 可以填写多列必须数字列，如果不填，以所有**非维度列（除order by以外的所有字段）**且为数字列的字段为汇总数据列
• 以order by的列为准，作为维度列
• 其他的列按插入顺序保留第一行
• 不在一个分区的数据不会被聚合
• 只有在同一批次插入or分片合并时才会进行聚合

👉开发建议：设计聚合表的话，唯一键值、流水号可以去掉，所有字段全部是维度、度量或者时间戳。

SQL操作

Update 和 Delete

这类操作被称为Mutation查询（可变查询），可以看作Alter的一种。

• 不支持事务。
• 每次修改or删除都会导致放弃目标数据的原有分区，重新建立分区。
• 建议尽量做批量更改，不要频繁小数据的操作（例如：个别字段的修改）。

Update

alter table t_order_smt update total_amount = toDecimal32(2000.00,2) where id =102;
1

Delete

alter table t_order_smt delete where sku_id = 'sku_001';
1

实现高性能Update或delete的思路

create table A(
    a XXX,
    b XXX,
    c XXX,
    _sign UInt8,
    _version UInt32
)
-- 更新：插入一条新的数据，_version + 1
	-- 查询：where version = max_version
-- 删除：_sign 0 表示未删除，1表示已删除
	-- 查询：where sign = 0 and version = max_version
-- 问题：时间久了，数据会膨胀
	-- 需要解决：类似合并机制，清理过期数据
1
2
3
4
5
6
7
8
9
10
11
12
13

查询

• 尽量避免使用JOIN：Join操作无法使用缓存，所以即使是两次相同的JOIN语句，Click House也会是为两条新的SQL。
• 不支持自定义函数。

multiIf

聚合函数

KEY words：with rollup, with cube, with totals

-- 这两个语句等同
SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day);
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;
1
2
3

假设维度是a，b

• rollup：上卷，从右到左，维度增加

  • group by null
  • group by a
  • group by (a,b)

• cube：多维分析

  • group by null
  • group by a
  • group by b
  • group by (a,b)

• totals：总计

  • group by null
  • group by (a,b)

alter操作

• 因为是列式存储，对字段的修改会更方便

导出数据

• 用的比较少，因为存储的是宽表，可以直接用于BI表

https://clickhouse.com/docs/en/interfaces/formats

clickhouse-client --query "select * from t_order_mt where create_time = '2020-06-01'" --format CSVWithNames> [导出地址]
1

副本

• 目的：主要是保障数据的高可用性，即使一台CH节点宕机，那么也可以从其他服务器获得相同数据。

副本写入流程

• 没有主从数据库的概念

1. client—写入数据—》CH-a—提交写入日志—》zookeeper-cluster—收到写入日志—》CH-b；
2. CH-a—提交写入日志的同时—》从目标副本下载新数据—传给—》CH-b

执行计划

https://clickhouse.com/docs/en/sql-reference/statements/explain/#explain-types

---

Click House高级

---

Explain查看执行计划

EXPLAIN [AST | SYNTAX |PLAN|PIPELINE] [setting = value,...] select ...
1

Syntax

跑语句之前可以使用这个语法查看，用于优化语法；

Pipeline

用于查看Pipeline计划

建表优化

数据类型

时间字段的类型

建表时能用数值型或日期时间类型表示的字段就不要用字符串，虽然ClickHouse底层将DateTime存储为时间戳Long类型，但不建议存储Long类型，因为DateTime不需要经过函数转换，执行效率高、可读性好。

create table test(
    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

空值存储

• click house中的空值位Nullable类型，官方指出，Nullable类型几乎总是会拖累性能
  • 原因：存储Nullable列时需要创建一个额外的文件存储Null的标记，并且Nullable列无法被索引。
  • 注：除极端情况，应直接使用默认值表示空，或者自定指定一个在业务中无意义的值来代替。

分区和索引

• 一般选择按天分区。
• 1亿条数据一般选择30个左右的分区。
• 索引：order by(a,b,c) 从左到右优先索引，高频查询的字段放在前面。
• 基数特别大的不适合做索引列。
  • 基数大的列：该列的数据去重后和数据记录数越接近基数越大。
  • 为什么基数大不适合做索引：基数过大，查找时越需要依次遍历，则失去了索引意义。

表参数

• Index_granularity是用来控制索引粒度的，默认是8192，如非必须不建议调整。
• 如果表中不是必须保留全量历史数据，建议制定TTL（生存时间值）可以免去手动处理过期历史数据的麻烦，TTL也可以通过Alter table随时修改。

写入和删除优化

• 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge任务带来巨大压力；
• 不要一次写入太多分区，或数据写入太快：
  • 数据写入太快会导致Merge速度跟不上而报错，一般建议每秒钟发起2-3次写入操作，每次操作写入2w-5w条数据（具体情况依据服务器性能而定）
• 写入太快会报错：
  • “Too many parts”处理：
    1. 使用WAL预写日志，提高写入性能。
    2. 降低写入频率。
  • ”Memory limit“处理：

常见配置

👉一般CH的瓶颈出现在CPU压力过大、内存不足、IO压力过大

• 配置项主要在config.xml 和 users.xml
  • config.xml：服务端配置，用户的默认配置路径指向users.xml
    • 设定之后无法被覆盖
    • https://clickhouse.com/docs/en/operations/server-configuration-parameters/
  • users.xml：
    • https://clickhouse.com/docs/en/operations/settings/settings-users

CPU资源

内存资源

存储

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

Click House语法优化规则

• CH的SQL优化规则是基于RBO（Rule Based Optimization）

COUNT优化

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

explain syntax select count(*) from zs_game_role zgr 

-- 结果
SELECT count()
FROM zs_game_role AS zgr

1
2
3
4
5
6

消除子查询的重复字段

explain
syntax
select
	a.role_id,
	a.member_id,
	a.main_game_id
from
zs_game_role b
left join
	(
	select
		role_id,
		role_id,
		member_id,
		main_game_id,
		role_id
	from
		zs_game_role zgr 
) as a
using (role_id)
limit 3

-- 结果
SELECT
    a.role_id,
    a.member_id,
    a.main_game_id
FROM zs_game_role AS b
ALL LEFT JOIN
(
    SELECT
        role_id,
        member_id,
        main_game_id
    FROM zs_game_role AS zgr
) AS a USING (role_id)
LIMIT 3
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

谓词下推

• 原则：能提前过滤自动提前过滤
• 当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时候，having 过滤会下推到 where 提前过滤。

explain
syntax
select
	*
from(
	select
		role_id
	from
		zs_game_role zgr 
) as a
WHERE a.role_id = 20

-- 结果
SELECT role_id
FROM
(
    SELECT role_id
    FROM zs_game_role AS zgr
    WHERE role_id = 20
) AS a
WHERE role_id = 20
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

explain
syntax
SELECT
	*
FROM
	(
	select
			role_id
	from
			zs_game_role zgr
union all
	select
			role_id
	from
			zs_game_role zgr 
) as a
WHERE
	a.role_id = 20
	
-- 结果
SELECT role_id
FROM
(
    SELECT role_id
    FROM zs_game_role AS zgr
    WHERE role_id = 20
    UNION ALL
    SELECT role_id
    FROM zs_game_role AS zgr
    WHERE role_id = 20
) AS a
WHERE role_id = 20
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

聚合计算外推

explain
syntax
SELECT sum(role_id * 2)
from
zs_game_role zgr 

-- 结果
SELECT sum(role_id) * 2
FROM zs_game_role AS zgr
1
2
3
4
5
6
7
8
9

聚合函数消除

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

explain
syntax
SELECT
	sum(member_id * 2),
	max(game_id),
	any(role_id)
from
	zs_game_role zgr
group by
	role_id

-- 结果
SELECT
    sum(member_id) * 2,
    max(game_id),
    role_id
FROM zs_game_role AS zgr
GROUP BY role_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

删除重复的order by key

explain
syntax
SELECT
	role_id,
	member_id,
	main_game_id
from
	zs_game_role zgr
order by
	role_id,
	role_id ,
	member_id ,
	member_id

-- 结果
SELECT
    role_id,
    member_id,
    main_game_id
FROM zs_game_role AS zgr
ORDER BY
    role_id ASC,
    member_id ASC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

删除重复的 limit by key、using key

explain
syntax
SELECT
	role_id,
	member_id,
	main_game_id
from
	zs_game_role zgr
limit 3 by role_id ,
member_id ,
role_id ,
member_id

-- 结果：会优化
SELECT
    role_id,
    member_id,
    main_game_id
FROM zs_game_role AS zgr
LIMIT 3 BY
    role_id,
    member_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

explain
syntax
SELECT
	zgr2.role_id,
	zgr2.member_id,
	zgr2.main_game_id
from
	zs_game_role zgr
	left join zs_game_role zgr2 on zgr.role_id = zgr2.role_id 
limit 3 by zgr.role_id ,
zgr.member_id ,
zgr2.role_id ,
zgr2.member_id

-- 结果：不会优化
SELECT
    zgr2.role_id,
    zgr2.member_id,
    zgr2.main_game_id
FROM zs_game_role AS zgr
ALL LEFT JOIN zs_game_role AS zgr2 ON role_id = zgr2.role_id
LIMIT 3 BY
    role_id,
    member_id,
    zgr2.role_id,
    zgr2.member_id
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

标量替换

标量：写死的值

explain
syntax
with
(
SELECT 
	count()
from
	zs_game_role zgr3 
) as total_num
SELECT
	zgr.role_id,
	zgr.member_id,
	SUM(zgr.main_game_id) / total_num as a
from
	zs_game_role zgr
group by
	zgr.role_id,
	zgr.member_id
order by
	zgr.role_id,
	zgr.member_id
limit 5

-- 结果：with的内容替换成一个标量值了
WITH identity(_CAST(0, 'Nullable(UInt64)')) AS total_num
SELECT
    role_id,
    member_id,
    sum(main_game_id) / total_num AS a
FROM zs_game_role AS zgr
GROUP BY
    role_id,
    member_id
ORDER BY
    role_id ASC,
    member_id ASC
LIMIT 5
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

三元运算的优化

• 嵌套的if会优化为multiIf的语句

查询优化

单表查询

Prewhere替代where

​ Prewhere 和where 语句的作用相同，用来过滤数据。不同之处在于 prewhere 只支持MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 select 声明的列字段来补全其余属性。
​ 当查询列明显多于筛选列时使用Prewhere可十倍提升查询性能，Prewhere会自动优化执行过滤阶段的数据读取方式，降低io操作。
​ 在某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高。

• 大部分场景where会自动优化为prewhere

某些场景不会自动转换成prewhere：

• 使用常量表达式
• 使用默认值为alias类型的字段
• 包含了arrayJoin、golbalIn、golbalNotIn或indexHint的查询
• select查询的列字段和where的谓词完全相同
• 使用了主键字段（order by字段）

数据采样

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

select 
	Title,count(*) as pageViews
from
	table
SAMPLE 0.1
where countID = XX
group by XX
order by XX

1
2
3
4
5
6
7
8
9

列裁剪与分区裁剪

• 列裁剪：其实就是避免使用select * ，筛选出需要的字段
• 分区裁剪：避免使用select * ，在where中使用partition by字段，选择分区

orderby结合where、limit

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

• 因此，尽量不要order by单独使用

避免构建虚拟列

• 虚拟列：原表中不存在的列，计算出来的列，如：

select
	a,
	b,
	a+b -- 虚拟列
from
	table
1
2
3
4
5
6

虚拟列非常消耗资源，浪费性能。

uniqCombined替代distinct

• uniqCombined：近似去重，但是精度不会很低，差别很小

性能可提升10倍以上，uniqCombined底层采用类似HyperLogLog算法实现。

不建议对千万以上的对准确度没有要求的数据进行精确去重，使用近似去重，例如：精确活跃率1千万，近似去重：0.999千万。

物化视图

视图：保存的是SQL的操作逻辑。

物化视图：不仅保存SQL的操作逻辑，还保存操作过后的结果。

其他注意事项

查询熔断

为了避免因个别慢查询引起的服务雪崩问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询。

关闭虚拟内存

物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存。

配置join_use_nulls

为每一个账户添加join_use_nulls配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准SQL中的Null值。

批量写入时先排序

批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致 CIick House 无法及时对新导入的数据进行合并，从而影响查询性能。

关注CPU

CPU一般在50%左右会出现查询波动，达到70%会出现大范围的查询超时，CPU是关键指标，要非常关注。

多表关联

CH的JOIN：

• 原理：右表加载到内存，再匹配；
• 为什么JOIN不行：因为1；
• 非要使用，如何使用：
  • 能过滤先过滤，特别是右表；
  • 右表放小表；
  • 特殊场景可以考虑使用字典表；
  • 可以替换的话，利用IN替换JOIN

-- 建表的时候，想要复制表结构：
create table XXX as select * from XXXX where 1 = 0; -- 条件不成立，数据永远不会写进来
1
2

Join原理

A join B，将B表全部加载到内存中，A表中的数据会逐条匹配内存中的B表。

用IN代替JOIN

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

select table_a.* from table_a where table_a.count_id in (select count_id from table_b);
1

大小表JOIN

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

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

• 尽量在join之前进行过滤

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

分布式表使用GLOBAL

• 查询放大：两张分布式表进行JOIN的时候，两张表的N各节点互相发起查询，变成N*N次

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

使用字典表

• 可以是自己创建的表，也可以是外部文件。

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

提前过滤

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

数据一致性（重点！！！）

• 查询CH手册发现，即便对数据一致性支持最好的MergeTree，也只是保持最终一致性。
• 数据一致性：
  • replacingMergeTree不能保证查询时没重复，只能保证最终一致性；
  • 解决：
    • 手动optimize，生产环境不推荐；
    • 通过sql实现去重：group by -->高级一点用法，加标记字段
    • 使用final：
      • 20.5之后，final可以是多线程，但是读取part是串行的；
    • 重复一点无所谓：特定业务条件下；

准备数据

建表

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
8

写入数据

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
6
7
8
9
10
11
12
13
14
15
16
17
18
19

修改前50W行数据，修改内容包括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
7
8
9
10
11
12
13
14
15
16
17
18
19
20

手动OPTIMIZE（生产中一般不会使用）

optimize table test_a final
1

通过GroupBY去重

SELECT
	user_id,
	argMax(score,
	create_time) as score,
	argMax(deleted,
	create_time) as deleted,
	max(create_time) as ctime
from
	test_a ta
group by
	user_id
having
	deleted = 0;
1
2
3
4
5
6
7
8
9
10
11
12
13

创建视图，方便测试

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 ta
group by
	user_id
having
	deleted = 0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

插入数据，再次查询

insert into table test_a(user_id,score,create_time) values(0,'AAAA',now());

SELECT * FROM  view_test_a WHERE user_id = 0;

>>> 结果
id user_id score create_time 
0	AAAA	0	2022-09-13 15:43:38.000
1
2
3
4
5
6
7

“删除”测试数据

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

通过FINAL查询

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

在v20.5.2.7-stable版本之后，FINAL查询支持多线程执行，并且可以通过max＿final＿threads参数控制单个查询的线程数。但是目前读取part部分的动作依然是串行的。

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

参考链接：https://github.com/ClickHouse/ClickHouse/pull/10463

explain pipeline SELECT * from test_a ta WHERE create_time > toDateTime(0);

>>>结果
(Expression)
ExpressionTransform × 6
  (ReadFromMergeTree)
  MergeTreeThread × 6 0 → 1
1
2
3
4
5
6
7

物化视图

• 视图：保存的是SQL的操作逻辑。

• 物化视图：不仅保存SQL的操作逻辑，还保存操作过后的结果，结果根据相应的引擎存到磁盘或内存中。

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

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

优缺点

优点：查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总的行数少了，因为都预计算好了。

缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以如果要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。

基本语法

​ 创建时会创建一个隐藏的目标表来保存视图数据。也可以TO表明，保存到一张显式的表。没有加TO表名，表名默认就是.inner.物化视图名。

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

• [POPULATE]：添加之后，在创建视图时会遍历历史数据，会增加服务器负载，如果要历史数据，使用INSERT INTO写入数据。

• 在创建没有TO [db].[table]的物化视图时，您必须指定ENGINE- 用于存储数据的表引擎。

• 使用TO [db].[table]创建物化视图时，不得使用POPULATE.

• 物化视图的实现方式如下：向 中指定的表中插入数据时，插入的SELECT部分数据通过该SELECT查询进行转换，并将结果插入到视图中。

• 查询语句可以包含下面的子句：DISTINCT , GROUP BY , ORDER BY , LIMIT……

准备数据

create table test_a_test(
	user_id UInt64,
	score String,
	deleted UInt8 DEFAULT 0,
	create_time Date
)ENGINE = MergeTree()
partition by toYYYYMM(create_time)
order by
(create_time,
intHash32(user_id))
sample by intHash32(user_id)
SETTINGS index_granularity = 8192;

insert
	into
	test_a_test
select
	*
FROM
	test_a
limit 10000;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

创建视图

create materialized view test_mview
engine = SummingMergeTree
Partition by toYYYYMM(create_time)
order by
(create_time,
intHash32(user_id))
as
SELECT
	user_id,
	create_time,
	count(score),
	sum(deleted)
from
	test_a_test ta
WHERE
	create_time >= toDate(0)
group by user_id,create_time ;

show tables;
>>>结果
.inner_id.5bfba660-812e-49ec-885f-3fa63e16f2f4 -- 默认存储数据的表格
test_a
test_a_test
test_mview
view_test_a
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

插入数据

SELECT * from test_mview;-- 第一次查询结果为空

insert
	into
	test_a_test
select
	*
FROM
	test_a
limit 10;

SELECT * from test_mview;-- 插入后在查询有10条新增数据

select * from `.inner_id.068a0cde-c260-4fee-b902-c7f74cc4f194`; -- 自动创建的表中也有数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14

导入历史数据（重点！！！）

insert
	into
	test_mview
-- 将物化视图的逻辑再写一遍
SELECT
	user_id,
	create_time,
	count(score),
	sum(deleted)
from
	test_a_test ta
WHERE
	create_time >= toDate(0)
group by user_id,create_time ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14