正文

clickhouse-尚硅谷（12.高级-数据一致性*）学习笔记(代码片段)

yuan_404  yuan_404  2023-02-18  405

关键词：

下一篇：（11. 高级-查询优化）学习笔记

下一篇：（13. 高级-物化视图）学习笔记

文章目录

1 准备测试表和数据
2 手动 OPTIMIZE(不推荐)
3 通过 Group by 去重
4 通过 FINAL 查询
- 4.1 老版本测试
- 4.2 新版本测试

查询 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;
```
其中:
- user_id 是数据去重更新的标识;
- create_time 是版本号字段，每组数据中 create_time 最大的一行表示最新的数据;
- deleted 是自定的一个标记位，比如 0 代表未删除，1 代表删除数据。

写入 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);

修改前 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);

统计总数
```
SELECT COUNT() FROM test_a;
10500000
```
还未触发分区合并，所以还未去重。

2 手动 OPTIMIZE(不推荐)

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

OPTIMIZE TABLE test_a FINAL;

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

3 通过 Group by 去重

执行去重的查询
```
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;
```
函数说明：

argMax(field1，field2):取 field2 最大值所在行的 field1 字段值

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

创建视图，方便测试

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;

插入重复数据，再次查询

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

删除数据测试

#再次插入一条标记为删除的数据
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;

这行数据并没有被真正的删除，而是被过滤掉了。在一些合适的场景下，可以结合表级别的 TTL 最终将物理数据删除。

4 通过 FINAL 查询

在查询语句后增加 FINAL 修饰符，这样在查询的过程中将会执行 Merge 的特殊逻辑（例如数据去重，预聚合等）。
但是这种方法在早期版本基本没有人使用，因为在增加 FINAL 之后，我们的查询将会变成一个单线程的执行过程，查询速度非常慢。
在 v20.5.2.7-stable 版本中，FINAL 查询支持多线程执行，并且可以通过 max_final_threads 参数控制单个查询的线程数。但是目前读取 part 部分的动作依然是串行的。
FINAL 查询最终的性能和很多因素相关，列字段的大小、分区的数量等等都会影响到最终的查询时间，所以还要结合实际场景取舍。

参考链接：https://github.com/ClickHouse/ClickHouse/pull/10463
分别安装了 20.4.5.36 和 21.7.3.14 两个版本的 ClickHouse 进行对比。

4.1 老版本测试

普通查询语句

select * from visits_v1 WHERE StartDate = '2014-03-17' limit 100;

FINAL 查询

select * from visits_v1 FINAL WHERE StartDate = '2014-03-17' limit 100;

先前的并行查询变成了单线程。

4.2 新版本测试

普通语句查询

select * from visits_v1 WHERE StartDate = '2014-03-17' limit 100 settings max_threads = 2;

查看执行计划：

explain pipeline select * from visits_v1 WHERE StartDate = '2014-03-17' limit 100 settings max_threads = 2;

(Expression) 
	ExpressionTransform × 2
	(SettingQuotaAndLimits) 
		(Limit) 
		Limit 2 → 2
			(ReadFromMergeTree) 
			MergeTreeThread × 2 0 → 1

明显将由 2 个线程并行读取 part 查询。

FINAL 查询

select * from visits_v1 final WHERE StartDate = '2014-03-17' limit 100 
settings max_final_threads = 2;

查询速度没有普通的查询快，但是相比之前已经有了一些提升,查看 FINAL 查询的执行计划：

explain pipeline select * from visits_v1 final WHERE StartDate = '2014-03-17' limit 100 settings max_final_threads = 2;

(Expression) 
ExpressionTransform × 2 
(SettingQuotaAndLimits) 
	(Limit) 
	Limit 2 → 2 
		(ReadFromMergeTree) 
		ExpressionTransform × 2 
			CollapsingSortedTransform × 2
				Copy 1 → 2 
					AddingSelector 
						ExpressionTransform
							MergeTree 0 → 1

从 CollapsingSortedTransform 这一步开始已经是多线程执行，但是读取 part 部分的动作还是串行。

clickhouse-尚硅谷（13.高级-物化视图）学习笔记(代码片段)

....2创建物化视图2.3导入增量数据2.4导入历史数据1概述 ClickHouse的物化视图是一种查查看详情

clickhouse-尚硅谷（11.高级-查询优化）学习笔记(代码片段)

上一篇：（10.高级-语法优化规则）学习笔记下一篇：（12.高级-数据一致性）学习笔记文章目录1单表查询1.1Prewhere替代where1.2数据采样1.3列裁剪与分区裁剪1.4orderby结合where、limit1.5避免构建虚拟列1.6uniqCombined... 查看详情

clickhouse-尚硅谷（15.高级-常见问题排查）学习笔记(代码片段)

...动缺少zk表5ZKtablereplicas数据未删除，导致重建表报错6Clickhouse节点意外关闭7其他问题参考1分布式DDL某数据节点的副本不执行问题：使用查看详情

clickhouse-尚硅谷（15.高级-常见问题排查）学习笔记(代码片段)

...动缺少zk表5ZKtablereplicas数据未删除，导致重建表报错6Clickhouse节点意外关闭7其他问题参考1分布式DDL某数据节点的副本不执行问题：使用分布式ddl执行命令createtableonclusterxxxx某个节点上没有创建表，但是client返回正常&#... 查看详情

clickhouse-尚硅谷（14.高级-materializemysql引擎）学习笔记(代码片段)

...实操2.1MySQL开启binlog和GTID模式2.2准备MySQL表和数据2.3开启ClickHouse物化引擎2.4创建复制管道2.5修改数据2.6删除数据2.7删除表1概述 My 查看详情

clickhouse-尚硅谷（9.高级-建表优化）学习笔记(代码片段)

上一篇：（8.高级-Explain查看执行计划）学习笔记下一篇：（10.高级-语法优化规则）学习笔记文章目录1数据类型1.1时间字段的类型1.2空值存储类型2分区和索引3表参数4写入和删除优化5常见配置5.1CPU资源5.2... 查看详情

clickhouse-尚硅谷（11.高级-查询优化）学习笔记(代码片段)

clickhouse-尚硅谷（13.高级-物化视图）学习笔记(代码片段)

....2创建物化视图2.3导入增量数据2.4导入历史数据1概述 ClickHouse的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提升。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算... 查看详情

clickhouse-尚硅谷（14.高级-materializemysql引擎）学习笔记(代码片段)

...实操2.1MySQL开启binlog和GTID模式2.2准备MySQL表和数据2.3开启ClickHouse物化引擎2.4创建复制管道2.5修改数据2.6删除数据2.7删除表1概述 MySQL的用户群体很大，为了能够增强数据的实时性，很多解决方案会利用binlog将数据写入... 查看详情

clickhouse-尚硅谷（9.高级-建表优化）学习笔记(代码片段)

...xff0c;全String类型在以Hive为中心的数仓建设中常见，但ClickHouse环境不应受此影响。虽然ClickHouse底层将DateTime存储为时间戳Long类型，但不建议存储Long类型，因为DateTime不需要经过函数转换处理，执行效率高、可... 查看详情

clickhouse-尚硅谷（1.入门-概述）学习笔记(代码片段)

下一篇：（2.入门-安装）学习笔记文章目录1.ClickHouse概述2.ClickHouse的特点2.1列式存储2.2DBMS的功能2.3多样化引擎2.4高吞吐写入能力2.5数据分区与线程级并行2.6性能对比1.ClickHouse概述 ClickHouse是俄罗斯的Yandex于2016年开... 查看详情

clickhouse-尚硅谷（7.入门-分片集群）学习笔记(代码片段)

上一篇：（6.入门-副本）学习笔记下一篇：（8.高级-Explain查看执行计划）学习笔记文章目录1概述2集群写入流程（3分片2副本共6个节点）3集群读取流程（3分片2副本共6个节点）43分片2副本共... 查看详情

clickhouse-尚硅谷（7.入门-分片集群）学习笔记(代码片段)

clickhouse-尚硅谷（3.入门-数据类型）学习笔记(代码片段)

上一篇：（2.入门-安装）学习笔记下一篇：（4.入门-表引擎）学习笔记文章目录1.整型2.浮点型3.布尔型4.Decimal型5.字符串6.枚举类型7.时间类型8.数组9.其他1.整型固定长度的整型，包括有符号整型(有... 查看详情

clickhouse-尚硅谷（2.入门-安装）学习笔记(代码片段)

...装依赖1.4CentOS取消SELINUX2.单机安装2.1在/opt/software下创建clickhouse目录2.2将/2.资料/ClickHo 查看详情

clickhouse-尚硅谷（8.高级-explain查看执行计划）学习笔记(代码片段)

....1查看PLAIN2.2AST语法树2.3SYNTAX语法优化2.4查看PIPELINE 在clickhouse20.6版本之前要查看SQL语句的执行计划需要设置日志级别为trace才能可以看到，并且只能真正执行sql，在执行日志里面查看。在20.6版本引入了原生的执行计划... 查看详情

clickhouse-尚硅谷（4.入门-表引擎）学习笔记(代码片段)

上一篇：（3.入门-数据类型）学习笔记下一篇：（5.入门-SQL操作）学习笔记文章目录1表引擎的使用2TinyLog3Memory4MergeTree*4.1partitionby分区(可选)4.2primarykey主键(可选)4.3orderby（必选）4.4二级索引(跳数索... 查看详情

clickhouse-尚硅谷（5.入门-sql操作）学习笔记(代码片段)

...insertinto[table_name]selecta,b,cfrom[table_name_2]2Update和Delete ClickHouse提供了Delete和Update的能力，这类操作被称为Mutation查询，它可以看做Alter的一种。虽然可以实现修改和删除，但是和一般的OLTP数据库不一样，Mutati... 查看详情