大数据技术之ClickHouse | 慵懒的像一只猫 (gitee.io)

ClickHouse学习笔记（一）邋遢的流浪剑客的博客-CSDN博客

分析型数据库追求的就是查询速度，一些地方不能类比以前所学的那些关系型数据库

1 MergeTree引擎

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
) ENGINE = MergeTree()  # MergeTree引擎没有参数。
ORDER BY expr  # ORDER BY是必选子句
[PARTITION BY expr]
[PRIMARY KEY expr] # clickhouse的主键没有约束唯一值
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。

如果设置了分区，那么打开保存表的目录，可以看到几个表示不同分区的子目录，分区目录文件命名规则：分区值_最小分区_最大分区块编号_合并层级

通常按日期（天）分区。

并行：分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。

ORDER BY是MergeTree引擎唯一的必选子句。主键必须是order by字段的前缀字段（因为只有这样才肯定是有序的），即符合索引的第一个字段。比如order by字段是（A,B,C），那么主键字段只能是（A）或（A,B）或（A,B,C）。不设置primary key 的话，默认使用的order by字段作为主键。

二级索引 适用于大量重复数据效果更好，作用是加快找到一级索引。官网建议二级索引和主索引关联性要较强。

ReplacingMergeTree唯一多出的功能是去重，接受参数为一系列版本列，称ver。

在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键（order by）的行中选择一行留下：

  • 如果 ver 列未指定，保留最后一条（较迟插入的数据）。
  • 如果 ver 列已指定，保留 ver 值最大的版本。

去重不能跨分区，数据的去重只会在数据合并期间（分区合并或者插入同一批次数据）进行。在合并之前，仍可能有重复的数据出现；一批次数据插入时也会做一次去重。合并会在后台一个不确定的时间进行。虽然用 OPTIMIZE 语句立刻发起合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写。

因此，ReplacingMergeTree 适用于在后台不定时清除重复的数据以节省空间。

——如果我刚插入的数据还没有合并到原来的分区内，那么是否会影响实时查看去重结果？

GPT——

如果你刚插入的数据还没有合并到原来的分区内，那么在实时查看去重结果时，这些新插入的数据将不会被考虑在内。

当你插入数据时，ClickHouse会将数据写入一个新的分区，然后定期合并相邻分区中的数据。如果你查询的数据所在的分区尚未被合并，则新插入的数据将不会被包含在查询结果中。

一般来说，ClickHouse的合并操作是异步执行的，这意味着数据可能需要一些时间才能完全合并到原来的分区中。如果你想要实时查看去重结果，可以考虑使用“FINAL”修饰符来查询最终结果，如下所示：

Copy

1
SELECT DISTINCT FINAL * FROM your_table;

这将返回去重后的最终结果，包括所有已经合并的数据。需要注意的是，使用FINAL修饰符会导致额外的计算成本，因此在查询大型数据集时需要谨慎使用。

总之，如果你刚插入的数据还没有合并到原来的分区中，那么在实时查看去重结果时，这些新插入的数据将不会被考虑在内。你可以使用FINAL修饰符来查询最终结果，但需要注意额外的计算成本。

SummingMergeTree该引擎继承自 MergeTree。当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，保留最早的版本（较早插入的数据）。

参数：columns - 包含了将要被汇总的列的列名的元组。可选参数。 所选的列必须是数值类型，并且不可位于主键中。

数据片段的合并发生在数据合并期间。

2 建表优化

必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by 指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大（也就是重复率特别小）的不适合做索引列，如用户表的 userid 字段；通常筛选后的数据满足在百万以内为最佳。

准确地说，基数特别大的不适合作为第一个索引列。因为如果查询条件不包含第一个索引时，这会使得通用排除搜索算法的效率变差。一个折中的做法是，索引列按照基数大小进行升序排列，基数特别大的作为索引的最后一列。

GPT——

在ClickHouse中，对于基数（cardinality）特别大的字段，不适合将其作为索引列。这是因为在ClickHouse中，索引是通过建立分布式哈希索引实现的。在处理基数巨大的字段时，哈希索引可能会变得非常大，从而占用过多的内存和磁盘空间。此外，由于哈希索引是分布式的，因此在查询时可能需要扫描多个节点，从而导致查询性能下降。

官方——

因为 ClickHouse 表的行数据存储在按主键列排序的磁盘上，所以在具有较低基数的列之前，在主键或复合主键中具有非常高基数的列（如 UUID 列）是 不利于其他表列的压缩率。 最快检索和最佳数据压缩之间的折衷是使用复合主键，其中 UUID 是最后一个键列，位于低（呃）基数键列之后，用于确保表的某些列具有良好的压缩率。

跳数索引的另一个候选者是高基数表达式，其中任何一个值在数据中都相对稀疏。一个可能的例子是跟踪API请求中的错误代码的可观察性平台。某些错误代码虽然在数据中很少出现，但对搜索来说可能特别重要。error_code列上的set skip索引将允许绕过绝大多数不包含错误的块，从而显著改善针对错误的查询。

比如官方案例的 hits_v1 表：

1
2
3
4
……
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
……

visits_v1 表：

1
2
3
4
……
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
……

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

在ClickHouse中，默认一条insert插入的数据为同一个数据分区，不同insert插入的数据为不同的分区。当插入数据时，ClickHouse会将数据写入一个新的分区（所以ReplacingMergeTree是以分区为单位进行去重的，也就是说只有在相同的数据分区内，重复数据才可以被删除掉）。尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台 Merge 任务带来巨大压力。

3 稀疏索引

官方文档

考虑到与B-Tee索引相关的挑战，ClickHouse中的表引擎使用了一种不同的方法。ClickHouseMergeTree Engine引擎系列被设计和优化用来处理大量数据。

这些表被设计为每秒接收数百万行插入，并存储非常大(100 pb)的数据量。

数据被一批一批的快速写入表中，并在后台应用合并规则。

在ClickHouse中，每个数据部分（data part）都有自己的主索引。当他们被合并时，合并部分的主索引也被合并。

在大规模中情况下，磁盘和内存的效率是非常重要的。因此，不是为每一行创建索引，而是为一组数据行（称为颗粒（granule））构建一个索引条目。

之所以可以使用这种稀疏索引，是因为ClickHouse会按照主键列的顺序将一组行存储在磁盘上。

与直接定位单个行(如基于B-Tree的索引)不同，稀疏主索引允许它快速(通过对索引项进行二分查找)识别可能匹配查询的行组。

然后潜在的匹配行组(颗粒)以并行的方式被加载到ClickHouse引擎中，以便找到匹配的行。

颗粒index_granularity的默认值为8192，表示每8192行为一个行组。非必要不修改。

为了提高内存效率，我们显式地指定了一个主键，只包含查询过滤的列。基于主键的主索引被完全加载到主内存中。

如果同时指定了主键和排序键，则主键必须是排序键的前缀。

出于数据处理的目的，表的列值在逻辑上被划分为多个颗粒。颗粒是流进ClickHouse进行数据处理的最小的不可分割数据集。这意味着，ClickHouse不是读取单独的行，而是始终读取(以流方式并并行地)整个行组（颗粒）。

列值并不物理地存储在颗粒中，颗粒只是用于查询处理的列值的逻辑组织方式。

最后一个颗粒（1082颗粒）是少于8192行的。

键列(UserID, URL)中的一些列值标记为橙色。这些橙色标记的列值是每个颗粒中每个主键列的最小值。这里的例外是最后一个颗粒(上图中的颗粒1082)，最后一个颗粒我们标记的是最大的值。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE hits_UserID_URL
(
    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime
)
ENGINE = MergeTree
PRIMARY KEY (UserID, URL)
ORDER BY (UserID, URL, EventTime)
SETTINGS index_granularity = 8192, index_granularity_bytes = 0;

image-20230603213201761

主索引是基于上图中显示的颗粒创建的。这个索引是一个未压缩的扁平数组文件(primary.idx)，包含从0开始的所谓的数字索引标记。

image-20230603213543050

image-20230603213554606

当查询对主键的第一列进行过滤时，ClickHouse将主键索引标记运行二分查找算法。

文档中以计算UserID 749927693点击次数最多的10个url为例：

1
2
3
4
5
6
SELECT URL, count(URL) AS Count
FROM hits_UserID_URL
WHERE UserID = 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
┌─URL────────────────────────────┬─Count─┐
│ http://auto.ru/chatay-barana.. │   170 │
│ http://auto.ru/chatay-id=371...│    52 │
│ http://public_search           │    45 │
│ http://kovrik-medvedevushku-...│    36 │
│ http://forumal                 │    33 │
│ http://korablitz.ru/L_1OFFER...│    14 │
│ http://auto.ru/chatay-id=371...│    14 │
│ http://auto.ru/chatay-john-D...│    13 │
│ http://auto.ru/chatay-john-D...│    10 │
│ http://wot/html?page/23600_m...│     9 │
└────────────────────────────────┴───────┘

10 rows in set. Elapsed: 0.005 sec.
Processed 8.19 thousand rows,
740.18 KB (1.53 million rows/s., 138.59 MB/s.)

ClickHouse客户端的输出显示，没有进行全表扫描，只有8.19千行流到ClickHouse。

如果trace logging打开了，那ClickHouse服务端日志会显示ClickHouse正在对1083个UserID索引标记执行二分查找以便识别可能包含UserID列值为749927693的行的颗粒。这需要19个步骤，平均时间复杂度为O(log2 n)：

我们可以在上面的跟踪日志中看到，1083个现有标记中有一个满足查询。

为了确认(或排除)颗粒176中的某些行包含UserID列值为749.927.693，需要将属于此颗粒的所有8192行读取到ClickHouse。

为了读取这部分数据，ClickHouse需要知道颗粒176的物理地址。

在ClickHouse中，我们表的所有颗粒的物理位置都存储在标记文件中。与数据文件类似，每个表的列有一个标记文件。标记文件也是一个扁平的未压缩数组文件(*.mrk)，其中包含从0开始编号的标记。一旦ClickHouse确定并选择了可能包含查询所需的匹配行的颗粒的索引标记，就可以在标记文件数组中查找，以获得颗粒的物理位置。

一旦ClickHouse确定并选择了可能包含查询所需的匹配行的颗粒的索引标记，就可以在标记文件数组中查找，以获得颗粒的物理位置。定位到的颗粒中的所有8192行数据都会被ClickHouse加载然后进一步处理。

文档中对于如何找到颗粒地址有详细图文解说，这里就不贴了。

为什么需要MARK文件

为什么主索引不直接包含与索引标记相对应的颗粒的物理位置？

因为ClickHouse设计的场景就是超大规模数据，非常高效地使用磁盘和内存非常重要。

主索引文件需要放入内存中。

对于我们的示例查询，ClickHouse使用了主索引，并选择了可能包含与查询匹配的行的单个颗粒。只有对于这一个颗粒，ClickHouse才需定位物理位置，以便将相应的行组读取以进一步的处理。

扁平的未压缩数组文件只适用于主键索引，并且只在使用MergeTree引擎时才会使用。这种格式可以提供快速的索引查找和访问速度，并且不需要额外的解压缩步骤。

当查询对联合主键的非第一个键列进行过滤时，ClickHouse使用的通用排除搜索算法(而不是二分查找)在前一个键列基数较低时最有效。

在我们的示例数据集中，两个键列(UserID、URL)都具有类似的高基数，并且，如前所述，当URL列的前一个键列具有较高基数时，通用排除搜索算法不是很有效。对URL列创建二级跳数索引同样也不会有太多改善。

因此，如果我们想显著提高过滤具有特定URL的行的示例查询的速度，那么我们需要使用针对该查询优化的主索引。

此外，如果我们想保持过滤具有特定UserID的行的示例查询的良好性能，那么我们需要使用多个主索引。

文档中对于为什么跳数索引没用有详细图文解说，这里就不贴了。

主键索引调优

如果我们想显著加快我们的两个示例查询——一个过滤具有特定UserID的行，一个过滤具有特定URL的行——那么我们需要使用多个主索引，通过使用这三个方法中的一个：

  • 新建一个不同主键的新表。
  • 创建一个物化视图。
  • 增加projection。

方法一：

显然可以在新表中将URL作为第一个主键列，其余与上文类似。

方法二：

物化视图（Materialized View）：

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

物化视图的计算和更新是在后台自动进行的，无需手动计算或更新物化视图。当物化视图的源表发生更改时，ClickHouse会自动更新物化视图的数据，以保持其与源表的一致性。

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

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

在原表上创建物化视图：

1
2
3
4
5
6
7
CREATE MATERIALIZED VIEW mv_hits_URL_UserID
ENGINE = MergeTree() # 可以用另一个引擎去组织数据
PRIMARY KEY (URL, UserID) # 以URL为第一主键
ORDER BY (URL, UserID, EventTime)
POPULATE # 使用POPULATE关键字，以便用源表hits_UserID_URL中的所有887万行立即导入新的物化视图
AS 
SELECT * FROM hits_UserID_URL;

物化视图由一个隐藏表支持，该表的行顺序和主索引基于给定的主键定义，如果在源表hits_UserID_URL中插入了新行，那么这些行也会自动插入到隐藏表中

image-20230603224326994