当前位置：首页 > news >正文

clickhouse-题库

news 来源：原创 2025/5/25 2:18:46

1、clickhouse介绍以及架构

clickhouse一个分布式列式存储数据库，主要用于在线分析查询

2、列式存储和行式存储有什么区别？

行式存储：
1）、数据是按行存储的
2）、没有建立索引的查询消耗很大的IO
3）、建立索引和视图花费一定的物理空间和时间资源
4）、面对大量的查询，复杂的复杂的数据库必须使用大量性能才能满足

列式存储：
1)、数据按列存储，每一列单独存放
2)、只访问查询设计的列，大量降低系统的IO
3)、数据类型一致，数据特征相似就可以高效的压缩

优势：

分析场景中往往需要读大量行但是少数几个列。在行式存模式下，数据按行连续存储，所有列的数据都存储在一个block中，不参与计算的列在IO时也要全部读出，读取操作被严重放大。而列存模式下，只需要读取参与计算的列即可，极大的减低了IO
cost，加速了查询。
同一列中的数据属于同一类型，压缩效果显著。列存往往有着高达十倍甚至更高的压缩比，节省了大量的存储空间，降低了存储成本。
更高的压缩比意味着更小的 data size，从磁盘中读取相应数据耗时更短。
自由的压缩算法选择。不同列的数据具有不同的数据类型，适用的压缩算法也就不尽相同。可以针对不同列类型，选择最合适的压缩算法。
高压缩比，意味着同等大小的内存能够存放更多数据，系统cache效果更好。

3、高吞吐写入

CK采用LSM Tree结构，数据写入后定期在后台compaction，因为是LSM Tree，ck在数导入的时候全部都是顺序append；数据会在后台一定时候compaction，但是只要是查询时候，会自动的compaction

4、ck中的数据类型

整型（包括有符号和无符号）：int8、int16、int32、int64
浮点型：float32、float64
Decimal：
布尔型：
字符串：string、fixedstring（fixedstring使用null字节填充末尾字符）
枚举类型：
时间类型：datetime精确到秒、datetime64精确到亚秒、date只精确到天
数组类型：

5、表引擎

表引擎是ck的特色，决定了如何存储表
日志系列引擎

1)、tinylog 以列文件的形式保存在磁盘上，不支持索引，没有并发控制，一般保存少量数据的小表
2)、log和StripeLog

memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失，读写操作不会相互阻塞，不支持索引

合并树家族引擎

mergeTree 是ck中最强大的表引擎，支持数据分区、主键索引、数据副本、数据采样等特性，相当于mysql中的innodb
分区目录：是以列文件+索引文件+表定义文件组成的
数据写入与分区合并：任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区，一段时间过后，ck会自动执行合并操作，把临时分区的数据合并到已有的分区中（也可以手动使用optimize执行）
Primary key：主键并不用于去重，而是用于索引，加快查询速度，默认的索引粒度为8192行，为数据生成以及索引并保存到primary.idx文件内，索引数据按照primary
key排序；稀疏索引只需要少量的索引标记就能够记录大量的数据区间位置信息。数据量越大越明显
跳数索引/二级索引：在一级索引的基础之上再加一层索引，它们使ClickHouse能够跳过保证没有匹配值的数据块。——INDEX a
total_amount TYPE minmax GRANULARITY 5
（如果是把一级索引分成几块，那么二级索引的粒度就是每次可以跳几块）GRANULARITY。每个索引块由颗粒（granule）组成。例如，如果主表索引粒度为8192行，GRANULARITY为4，则每个索引“块”将为32768行。

repacingMergeTree 存储特征完全继承MergeTree，会删除排序键值相同的重复项。
ReplacingMergeTree和MergeTree的不同之处在于它会删除排序键值相同的重复项。
数据的去重只能在compaction中出现，合并会在未知的时间在后台进行或者使用optiumize进行（大会引发对数据的大量读写）

使用ORBER BY排序键作为判断重复数据的唯一键。
只有在合并分区的时候才会触发删除重复数据的逻辑。
以数据分区为单位删除重复数据。当分区合并时，同一分区内的重复数据会被删除；不同分区之间的重复数据不会被删除。
在进行数据去重时，因为分区内的数据已经基于ORBER BY进行了排序，所以能够找到那些相邻的重复数据。
在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：如果ver列未指定，保留最后一条。如果ver列已指定，保留ver值最大的版本。

SummingMergeTree

不查询明细。只关心进行汇总聚合结果的场景，提供一种预聚合的功能以order
by的列合并为准，作为维度列、不在一个分区内的数据不会被聚合，只有在用一批次插入的数据才会进行聚合
-ClickHouse会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。

AggregatingMergeTree

可以理解为SummingMergeTree的升级版，能够在合并分区时候，按照预先定义的条件聚合数据，同时根据预先定义的聚合函数计算数据并得到二进制的格式存入表内
AggregateFunction类型字段使用二进制存储，在写入数据时，需要调用state函数；在读数据时，需要调用merge函数，*表示定义时使用的聚合函数
AggregateMergeTree通常作为物化视图的引擎，与普通的MergeTree搭配使用 CollapsingMergeTree
以增代删的思路，支持行级数据修改和删除的表引擎，通过一个sign标记位字段，记录数据行的状态，如果sign标记为1，表示这是一行有效的数据，如果sign为-1，表示改行数据需要被删除，当进行分区合并时候，同一数据分区内，sign为1和-1的一组数据会被抵消

VersionedCollapsingMergeTree

VersionedCollapsingMergeTree数据折叠也是发生在分区合并时，只会对同分区的数据进折叠

6、Update和delete

ck提供了delete和update能力，被称为mutation，但是一种很重的操作，不支持事务，原因是每次修改或者和删除都会放弃目标数据的原有分区，重建新的分区，所以尽量做批量的变更，不进行小数据的操作
原理：分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上失效的标记，直到触发分区合并的时候，才会删除数据释放磁盘空间

7、分片集群

副本可以提高数据的可用性，但每台服务器实际上必须是安全量数据，对数据的横向扩容没有解决
分片是把一份完整的数据进行切分，不同的分片分布到不同的节点上，在通过distributed表引擎把数据拼接起来，该引擎本身不存数据

8、建表优化

为什么ck中能用数值或者日期时间类型就不用字符串：
ck底层将datetime存储为时间戳long类型。但不建议存储long类型，datetime不需要经过含函数转换处理，执行效率高、可读性好
空值存储
nullable类型会拖慢性能，因为存储nullable时候需要创建一额外的文件来存储null的标记，nullable列无法被索引，一般在业务中采用一个没有实际意义的值代替（-1，0，1）
表参数
如果表中不是必须要保留的全量数据，一般指定TTL，避免手动删除过期数据的麻烦
写入和删除优化
不要执行单条或者小批量的插入删除操作，会产生小分区文件（因为每一次插入都会重新分区），给merge任务带来巨大压力，不要一次写入过多分区，或者数据写入太快——会导致merge速度跟不上而报错
谓词下推
所谓的谓词，就是对数据的筛选行为，下推是尽可能将这些筛选条件优先执行（查询的最低端）
聚合计算外推
聚合函数内的计算会外推
聚合函数消除

9、分片集群

10、建表优化

11、查询优化

1）、prewhere代替where
两者的作用相同，不同之处在于perwhere只支持MergeTree系列引擎的表，会先判断数据过滤，等待数据过滤之后再读取select系列字段补全，prewhere最多可提高十倍性能，也会自动优化

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

3）、列裁剪与分区裁剪
数据量太大时应避免使用 select * 操作，查询的性能会与查询的字段大小和数量成线性表换，字段越少，消耗的 io 资源越少，性能就会越高。

4）、orderby 结合 where、limit

5）、uniqCombined 替代 distinct
性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接收 2% 左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct )会使用 uniqExact精确去重。

12、物化视图

视图只是保存了计算逻辑，不保存数据
视图和物化视图的区别：ck的物化视图是一种查询结果的持久化，用户查起来和普通的表没区别，也像是一张时时刻刻计算的表，
缺点：本质是一个流式数据的使用场景，如果一张表加了很多的物化视图，在写入的时候，就会消耗很多资源
物化视图中的数据是在创建物化视图之后，在往对应表中插入数据时候，对应数据才会写入到物化视图中；
物化视图的名字不能和表明重复

13、ck和mysql主从同步

1）、确保mysql和开启binlog和GTID模式（binglog：记录所有对数据库执行更改的sql语句，不包括查，二进制文件是mysql数据复制恢复；GTID是一种复制方式，通过gtid保证了每个在主库上提交的事务在集群中有一个唯一的id，GTID (Global Transaction ID)是全局事务ID,由主库上生成的与事务绑定的唯一标识，这个标识不仅在主库上是唯一的，在MySQL集群内也是唯一的。

2）、确保ck物化引擎打开

14、ck的数据分布式存储机制如何设计的

1）、分片和复制：ck通过分片将数据水平划分为多个部分，每个部分存储在不同的节点上，每个分片可以有一个或者多个副本，副本之间自动同步数据
2）、分布式表引擎：ck使用分布式表引擎跨节点数据查询和写入
3）、数据分区：每个分片内，数据可以进一步根据分区键被划分为多个分区
4）、负载均衡：在执行查询时候，ck能够自动在所有可用的副本之间进行负载均衡
5）、一致性容错

15、补充：CK为什么快

1）存储引擎视角

ck利用存储引擎的特殊涉及充分减少磁盘IO对查询速度的影响，用户提交的一条SQL，大部分时间消耗在磁盘的IO上

ck对写入数据进行预排序

ck写入数据文件的数据是有序的，将数据在写入磁盘前进行排序，以保证数据在磁盘上有序，预排序在实现范围查找时候，可以将大量的随机读转换为顺序读，提高IO效率；在查找时，预排序能做到和未排序的数据相同的性能

列式存储在列式数据库中，同一列的所有数据都在同一个文件中，因此在硬盘上是连续的，适合

OLAP 压缩

ck另一个降低IO的手段是压缩，可以减少读取和写入的数据量，减少IO时间；事务数据库大部分情况下是针对行的操作，如果对每一行都进行压缩和解压缩，带来的时间消耗是远大于磁盘IO时间，这也是为什么所有的事务数据库都不使用压缩技术的原因；ck的最小处理单元是块，块一般由8192行数据组成，一次压缩针对8192行数据，降低cpu的压缩和解压缩时间，并且列式数据有更好的压缩比

向量化引擎
ck采用了向量化执行引擎，能够将多个操作合并成单个向量操作，减少了函数调用和内存分配的次数，从而提高了执行效率。此外向量化执行还使得数据在内存中的布局更加紧凑
多线程与分布式处理实现了多线程处理，提高整体的处理速度

2）计算引擎视角

ck计算快是来自内部向量化引擎的加持，ck计算慢是因为缺乏代价优化器大量使用向量化运算

为了实现向量化执行，需要利用CPU的SIMD指令（single instruction Multiple data，单条指令操作多条数据），它是通过数据i你高兴以提高性能的一种实现方式，它的原理是在CPU寄存器方面实现数据的并行计算，计算机存储层次如下所示：
其中，从左往右距离CPU越近，访问速度就越快，显然能够容纳的数据大小也就越小，CPU寄存器也是可以存储数据的，CPU从寄存器中获取数据的速度是最快的，是内存的300倍，是磁盘的3000万倍

ck中的很多内置函数。使用时候ck会自动进行向量化优化，查询中不使用join，或尽可能减少join操作
ck中没有代价优化器，说明在进行join的时候会出现内存不足的情况，使用ck时候，应当避免join操作，但join操作在ODS建模时候大量存在，数据量大的时候，建模工作应当下推到spark中进行
ck快的本质 ck快的本质是利用了cpu进行加速

总结：CK快的原因

ck是列式存储、索引机制、数据压缩、ck使用了向量化引擎（寄存器）、ck使用cpu加速、ck会在内存中进行group by，并使用hash
table装载数据

补充：ck的几点不足
1、不支持事务，不能把它用于OLTP事务操作的场景
2、不擅长根据主键按行粒度进行查询，所以不应该把ck当作键值对数据库使用
3、不擅长删除和修改数据

补充：ck相关补充
1、CK提供了标准的SQL查询接口，CK大小是大小写敏感的，关键字非大小敏感
2、ck中向量化是通过数据级别的并行方式提升了性能，那么多线程处理就是通过线程级并行的方式实现了性能提升
3、在分布式领域，移动计算优于移动数据，在服务器之间网络传输的成本是很高的，相比移动数据，更好的是预先将数据分布到各台服务器，将数据的计算查询直接下推到数据所在的服务器；
4、多主架构，在传统的hdfs、spark中，都采用了主从架构，一个leader节点统筹全局；ck采用多主架构，集群中的每个节点角色对等，客户端访问任意一个节点都能得到相同的效果，集群中所有节点功能相同，避免单点故障
5、数据分片，分区时表的分区，根据关键字将最终结果写入不同的文件中；而分复用了数据库的分区，相当于在原有的分区下，作为第二层分区，是在不同节点上的体现
6、ClickHouse 最终选择了这些算法：对于常量，使用 Volnitsky 算法；对于非常量，使用 CPU 的向量化执行 SIMD，暴力优化；正则匹配使用 re2 和 hyperscan 算法。