clickhouse的主键和索引分析

　　以(CounterID, Date)主键为例。在这种情况下，排序和索引可以说明如下：

  Whole data:     [---------------------------------------------]
  CounterID:      [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]
  Date:           [1111111222222233331233211111222222333211111112122222223111112223311122333]
  Marks:           |      |      |      |      |      |      |      |      |      |      |
                  a,1    a,2    a,3    b,3    e,2    e,3    g,1    h,2    i,1    i,3    l,3
  Marks numbers:   0      1      2      3      4      5      6      7      8      9      10

　　如果数据查询指定：

CounterID in ('a', 'h') [0, 3)，服务器读取标记和范围内的数据[6, 8)。
CounterID IN ('a', 'h') AND Date = 3 [1, 3)，服务器读取标记和范围内的数据[7, 8)。
Date = 3，服务器读取标记范围内的数据 [1, 10]。

　　1）使用索引总是比完全扫描更有效。

　　2）稀疏索引允许读取额外的数据。读取单个范围的主键时，index_granularity * 2每个数据块中最多可以读取额外的行。

　　3）稀疏索引允许处理大量表行，因为在大多数情况下，此类索引适合计算机的 RAM。

　　4）ClickHouse 不需要唯一的主键。可以使用相同的主键插入多行。

　　可以在and子句中使用Nullable-typed 表达式，但强烈建议不要这样做。要允许此功能，请打开allow_nullable_key设置。NULLS_LAST原则适用于子句中的值。PRIMARY KEYORDER BYNULLORDER BY

　　可以理解为一对的(CounterID、Date)间隔地生成了一个Marks，例如(a,1),(a,2)；根据Marks又生成了相应的Marks numbers。(a,1),(a,2)这2个索引之间，间隔了好几个数据，即：
　　（1）index_granularity这个参数规定了数据按照索引规定排序以后，间隔多少行会建立一个索引的Marks，即索引值
　　（2）稀疏索引的意义即是Clickhouse不对所以的列都建立索引（相比较Mysql的B树索引会为每行都建立），而是间隔index_granularity列才建立一个。
　　（3）Marks与Marks number均被保存在内存中，利于查询的时候快速检索。

1.主键的选择

　　主键中的列数没有明确限制。根据数据结构，可以在主键中包含更多或更少的列。这可能：

提高索引的性能。

如果主键是，那么如果满足以下条件(a, b)，则添加另一列将提高性能：c
- 有关于 column 条件的查询 c。
- index_granularity具有相同值的长数据范围（比长几倍）(a, b)很常见。换句话说，当添加另一列时，可以跳过相当长的数据范围。
改进数据压缩。

ClickHouse 按主键对数据进行排序，一致性越高，压缩效果越好。
在CollapsingMergeTree和SummingMergeTree引擎中合并数据部分时提供额外的逻辑。

在这种情况下，指定不同于主键的排序键是有意义的。

　　1）长主键会对插入性能和内存消耗产生负面影响，但主键中的额外列不会影响SELECT查询期间的 ClickHouse 性能。

　　2）ORDER BY tuple()可以使用语法创建没有主键的表。在这种情况下，ClickHouse 按插入顺序存储数据。如果要在通过INSERT ... SELECT查询插入数据时保存数据顺序，请设置max_insert_threads = 1。

　　3）要按初始顺序选择数据，请使用单线程 SELECT查询。

　　操作更改排序键（但不影响主键）：

ALTER TABLE [db].name [ON CLUSTER cluster] MODIFY ORDER BY new_expression

　　该命令将表的排序键new_expression更改为（表达式或表达式元组）。主键保持不变。

　　从某种意义上说，该命令是轻量级的，它只更改元数据。要保持数据部分行按排序键表达式排序的属性，您不能将包含现有列的表达式添加到排序键（仅ADD COLUMN在同一ALTER查询中由命令添加的列，没有默认列值）。

　　仅适用于MergeTree族中的表（包括复制表）。

　　针对主键的延伸介绍

　　clickhouse会在每个分区目录下生成一个索引文件primary.idx，记录了主键排序后按照索引粒度采样的值，以二进制的方式存储，可以通过od命令进行查看:

[ad@data1 ~/clickhouse/data/isv_data_prod/dm_order_today/202203_210_210_0]$  od -l -j 0 -N 80 --width=8 primary.idx
0000000  3545230323819229728
0000010  4048794554679177271
0000020  3617006580806400307
0000030  4121693288097986611
0000040  3688503285954781237
0000050  3689911760500897585
0000060  3616481000926753077
0000070  3617858594440033330
0000100  3472893445236864053
0000110  3905520523080577331
0000120

　　因为是稀疏索引，所以显然只靠一级索引文件是无法精确定位到数据的，这时候就需要标记文件登场了。在分区目录下，你可以看到很多后缀为.bin和.mrk2的文件，其中.bin是真实的数据内容，.mrk2就是标记文件。因为clickhouse底层是按列进行存储的，因此每一列会对应一个.bin文件和.mrk2文件。

[ad@data1 ~/clickhouse/data/isv_data_prod/dm_order_today/202203_210_210_0]$ od -l -j 0 -N 240 --width=24 ./TRADE_TYPE.mrk2
0000000                    0                    0                 7024
0000030                    0                22801                 7024
0000060                    0                46070                 7024
0000110                15454                    0                 7024
0000140                15454                22406                 7024
0000170                15454                44990                 7024
0000220                29733                    0                 7024
0000250                29733                22901                 7024
0000300                29733                47127                 7024
0000330                47062                    0                 7024
0000360

　　一行标记数据使用一个元组表示，元组内包含数据压缩块位置（在.bin文件中数据是切分成若干个数据块压缩存储的），数据块内偏移和索引粒度的大小。

　　检索方式如图，首先索引文件和标记文件在行上是对齐的，从上面索引文件和标记文件的示例可以看出来，二者的行数是一样的

　　查询的时候，会先根据要索引的值或范围，在primary.idx文件中确定一个行号范围（递归交集的判断），然后按照相同的行号范围在每一列的.mrk中查询，得到要查询的值在数据文件.bin的哪一个压缩块，以及将该压缩块解压之后在什么位置，然后将查询到的数据结果返回。

　　通过partition + 一级索引 + 标记文件，层层缩小数据扫描范围，clickhouse达到了其快速检索的目的。

　　如果没有查询条件命中索引的话clickhouse是怎么处理的呢？
　　扫描每个partition，不过因为.bin文件分了若干个小的压缩块，clickhouse利用多线程读取压缩块的方式在一定程度上也可以加速查找过程。

2.选择与排序键不同的主键

　　可以指定与排序键（用于对数据部分中的行进行排序的表达式）不同的主键（具有写入索引文件中每个标记的值的表达式）。

　　在这种情况下，主键表达式元组必须是排序键表达式元组的前缀。

　　此功能在使用SummingMergeTree和 AggregatingMergeTree表引擎时很有帮助。在使用这些引擎的常见情况下，表有两种类型的列：维度和度量。GROUP BY典型的查询聚合具有任意和按维度过滤的度量列的值。因为 SummingMergeTree 和 AggregatingMergeTree 聚合了具有相同排序键值的行，所以很自然地将所有维度添加到其中。因此，键表达式由一长串列组成，并且必须经常使用新添加的维度更新此列表。

　　在这种情况下，在主键中只保留几列是有意义的，这将提供有效的范围扫描，并将剩余的维度列添加到排序键元组中。

　　排序键的ALTER是一种轻量级操作，因为当一个新列同时添加到表和排序键时，不需要更改现有数据部分。由于旧排序键是新排序键的前缀，并且新添加的列中没有数据，因此在修改表的那一刻，数据同时按新旧排序键排序。

3.在查询中使用索引和分区

　　对于SELECT查询，ClickHouse 会分析是否可以使用索引。WHERE/PREWHERE如果子句具有表示相等或不等比较操作的表达式（作为连接元素之一或完全），或者如果它在主键中的列或表达式上具有固定前缀IN或LIKE带有固定前缀，则可以使用索引或分区键，或这些列的某些部分重复的功能，或这些表达式的逻辑关系。

　　因此，可以在一个或多个主键范围上快速运行查询。在此示例中，针对特定跟踪标签、特定标签和日期范围、特定标签和日期、具有日期范围的多个标签等运行查询将很快。

　　看一下配置如下的引擎：

  ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate) SETTINGS index_granularity=8192

　　在这种情况下，在查询中：

SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34
SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)
SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

ClickHouse 将使用主键索引来修剪不正确的数据，并使用月分区键来修剪在不正确日期范围内的分区。

　　上面的查询表明索引甚至用于复杂的表达式。从表中读取是有组织的，因此使用索引不会比完全扫描慢。

　　在下面的示例中，不能使用索引。

SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'

　　要检查 ClickHouse 在运行查询时是否可以使用索引，请使用设置force_index_by_date和force_primary_key。

　　按月分区的键允许只读取那些包含适当范围内日期的数据块。在这种情况下，数据块可能包含许多日期（最多一整个月）的数据。在一个块中，数据按主键排序，主键可能不包含日期作为第一列。因此，使用仅包含未指定主键前缀的日期条件的查询将导致读取比单个日期更多的数据。

4.单调主键索引

　　例如，考虑一个月中的几天。它们形成一个月的单调序列，但在更长时间内不是单调的。这是一个部分单调的序列。如果用户使用部分单调的主键创建表，ClickHouse 会像往常一样创建稀疏索引。当用户从此类表中选择数据时，ClickHouse 会分析查询条件。如果用户想要获取索引的两个标记之间的数据，并且这两个标记都在一个月内，ClickHouse 可以在这种特殊情况下使用索引，因为它可以计算查询参数和索引标记之间的距离。

　　如果查询参数范围内的主键值不代表单调序列，ClickHouse 不能使用索引。在这种情况下，ClickHouse 使用全扫描方法。

　　ClickHouse 不仅对月份序列中的天数使用此逻辑，而且对表示部分单调序列的任何主键都使用此逻辑。

5.跳数索引

　　索引声明位于CREATE查询的列部分。

INDEX index_name expr TYPE type(...) GRANULARITY granularity_value

GRANULARITY:跳数数据根据指的表达式聚合数据块上的信息，聚合信息的粒度是由创建索引的时候指定GRANULARITY的值决定的。

　　对于*MergeTree族中的表，可以指定跳数索引。

　　这些索引聚合了有关块上指定表达式的一些信息，这些信息由granularity_value颗粒组成（颗粒的大小使用index_granularity表引擎中的设置指定）。然后在查询中使用这些聚合，通过跳过无法满足查询SELECT的大数据块来减少从磁盘读取的数据量。where

　　例子

CREATE TABLE table_name
(
    u64 UInt64,
    i32 Int32,
    s String,
    ...
    INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3,
    INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4
) ENGINE = MergeTree()
...

　　ClickHouse 可以使用示例中的索引来减少在以下查询中从磁盘读取的数据量：

SELECT count() FROM table WHERE s < 'z'
SELECT count() FROM table WHERE u64 * i32 == 10 AND u64 * length(s) >= 1234

1）可用的索引类型

minmax

存储指定表达式的极值（如果表达式是tuple，那么它存储的每个元素的极值tuple），使用存储的信息来跳过像主键这样的数据块。
set(max_rows)

存储指定表达式的唯一值（不超过max_rows行，max_rows=0表示“无限制”）。使用这些值来检查WHERE表达式是否在数据块上不可满足。
ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

存储一个包含数据块中所有 ngram的Bloom 过滤器。仅适用于数据类型：String、FixedString和Map。可用于优化EQUALS,LIKE和IN表达式。
- n— ngram 大小，
- size_of_bloom_filter_in_bytes— 布隆过滤器大小（以字节为单位）（可以在此处使用较大的值，例如 256 或 512，因为它可以很好地压缩）。
- number_of_hash_functions— 布隆过滤器中使用的哈希函数的数量。
- random_seed— 布隆过滤器哈希函数的种子。
tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

与相同ngrambf_v1，但存储标记而不是 ngram。标记是由非字母数字字符分隔的序列。
bloom_filter([false_positive])— 存储指定列的布隆过滤器。

可选false_positive参数是从过滤器接收到误报响应的概率。可能的值：(0, 1)。默认值：0.025。

支持的数据类型：Int*, UInt*, Float*, Enum, Date, DateTime, String, FixedString, Array, LowCardinality, Nullable, UUID, Map.

对于数据类型，客户端可以使用mapKeys或mapValues函数Map指定是否应为键或值创建索引。

以下函数可以使用过滤器：equals、notEquals、in、notIn、has、hasAny、hasAll。

Map数据类型的索引以及其他索引创建示例

INDEX map_key_index mapKeys(map_column) TYPE bloom_filter GRANULARITY 1
INDEX map_key_index mapValues(map_column) TYPE bloom_filter GRANULARITY 1


INDEX sample_index (u64 * length(s)) TYPE minmax GRANULARITY 4
INDEX sample_index2 (u64 * length(str), i32 + f64 * 100, date, str) TYPE set(100) GRANULARITY 4
INDEX sample_index3 (lower(str), str) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4

跳数索引应用示例：

　　①minmax

　　报错的最大最小值（极值)。如下例子是2个列相乘，记录其minmax值的跳数索引。

INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3,

　　适合场景

SELECT count() FROM table WHERE u64 * i32 == 10

　　②set

　　一组数量有限的(max_rows)的唯一数结果集。如下是一个字段和另一个字段长度的乘积。最多有1000个不重复值。

INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4

　　适合场景

SELECT count() FROM table WHERE u64 * length(s) >= 1234

　　根据ngram切分的布隆过滤器。可用于优化 equals ， like 和 in 表达式的性能。

　　③ngrambf_v1

　　样例

INDEX index_name (ID, Code) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 5;
-- 3: token 长度，把数据切割成长度为 3 的短语
-- 256: 布隆过滤器大小
-- 2: 哈希函数个数
-- 0: 哈希函数随机种子

　　适合场景

where code like  '%123%'

　　④tokenbf_v1

　　与ngrambf_v1一样，不同于 ngrams 存储字符串指定长度的所有片段。它只存储被非字母数字字符分割的片段。

INDEX d ID TYPE tokenbf_v1(256, 2, 0) GRANULARITY 5

　　⑤bloom_filter

INDEX t_idx ip TYPE bloom_flter(0.025) GRANULARITY 5

2）功能支持

　　子句中的条件WHERE包含对列操作的函数的调用。如果列是索引的一部分，ClickHouse 会在执行函数时尝试使用该索引。ClickHouse 支持使用索引的不同函数子集。

　　该set索引可用于所有功能。其他索引的函数子集如下表所示。

Function (operator) / Index	primary key	minmax	ngrambf_v1	tokenbf_v1	bloom_filter
equals (=, ==)	✔	✔	✔	✔	✔
notEquals(!=, <>)	✔	✔	✔	✔	✔
like	✔	✔	✔	✔	✗
notLike	✔	✔	✔	✔	✗
startsWith	✔	✔	✔	✔	✗
endsWith	✗	✗	✔	✔	✗
multiSearchAny	✗	✗	✔	✗	✗
in	✔	✔	✔	✔	✔
notIn	✔	✔	✔	✔	✔
less (<)	✔	✔	✗	✗	✗
greater (>)	✔	✔	✗	✗	✗
lessOrEquals (<=)	✔	✔	✗	✗	✗
greaterOrEquals (>=)	✔	✔	✗	✗	✗
empty	✔	✔	✗	✗	✗
notEmpty	✔	✔	✗	✗	✗
hasToken	✗	✗	✗	✔	✗

具有小于 ngram 大小的常量参数的函数不能ngrambf_v1用于查询优化。

！！！ "注意" 布隆过滤器可能有误报匹配，因此ngrambf_v1,tokenbf_v1和bloom_filter索引不能用于优化预期函数结果为假的查询，例如：

可优化：
- s LIKE '%test%'
- NOT s NOT LIKE '%test%'
- s = 1
- NOT s != 1
- startsWith(s, 'test')
无法优化：
- NOT s LIKE '%test%'
- s NOT LIKE '%test%'
- NOT s = 1
- s != 1
- NOT startsWith(s, 'test')

3）对历史数据重建索引

　　建表语句：

CREATE TABLE ip_test(found_time String,ip String,
INDEX ip_idx1(ip) TYPE minmax GRANULARITY 1,
INDEX ip_idx2 ip TYPE bloom flter(0.025)0 GRANULARITY 1,
INDEX ip_idx3 ip TYPE ngrambf_v1(3, 512, 5, 0) GRANULARITY 1,
INDEX ip_idx4 ip TYPE tokenbf_v1(512, 5, 0) GRANULARITY 1,
INDEX ip_idx5 ip TYPE set(0) GRANULARITY4
)ENGINE= MergeTree() order by found_time
partition by toYYYYMMDD(toDateTime(found_time)) SETTINGS index_granularity= 4;

　　删除索引：

ALTER TABLE ip_test DROP INDEX ip_idx1;

重建索引：重建索引(对历史数据add索引的时候，只是改变了表的schema,实际的索引文件并没有生成，需要再使用重建索引的语句对历史数据建立索引):

1）添加索引

ALTER TABLE ip_test add INDEX ip_jidx2 ip TYPE bloom_ filter(0.025) GRANULARITY 1;

2）重建索引

ALTER TABLE ip_test MATERIALIZEINDEX ip_jidx2 [ IN PARTITION partition name]

6.总结

　　（1）虽然是稀疏索引，但是如果索引中的列过多，则根据索引来划分数据会更稀疏，建立的索引也需要更多，影响写入性能，也会增加内存的使用。

　　（2）相比普通的B树索引，稀疏索引需要的内存更少，但是可能导致需要扫描的行数比实际的多。

　　（3）官网推荐是不需要去改"8192"这个值。除非要做为索引的这个列的值分布非常非常集中，可能几w行数据才可能变化一个取值，否则无需去做调大去建立更稀疏的索引，不过如果这个列这个集中的分布，也不大适合作为索引；如果要调小这个值，是会带来索引列增加，但是同样也会带来内存使用增加、写入性能受影响。

　　（4）有多个列组合做组合索引，选稀疏的值放在第一位。只能选择一个列做单索引，如果有2个备选的值，要选比较稀疏的。通常需要满足高级列在前、查询频率大的在前原则。基数特别大的不适合做索引列（可以对比上图索引创建规则），如用户表的userid字段

　　（5）通常筛选后的数据满足在百万以内为最佳。

　　（6）建表优化，创建字段的时候尽量不要使用nullable，日期尽量都使用date类型。

posted @ 2022-03-17 16:56 渐逝的星光阅读(4696) 评论(0) 编辑收藏举报

刷新页面返回顶部

渐逝的星光

云卷云舒风入怀，潮涨潮落月洗尘。

clickhouse的主键和索引分析

1.主键的选择

针对主键的延伸介绍

2.选择与排序键不同的主键

3.在查询中使用索引和分区

4.单调主键索引

5.跳数索引

1）可用的索引类型

①minmax

②set

③ngrambf_v1

④tokenbf_v1

⑤bloom_filter

2）功能支持

3）对历史数据重建索引

6.总结

公告