clickhouse数据库索引优化与说明（1）_clickhouse设置索引列

常量体系介绍

文件类型：
primary.idx #主索引文件
*.bin #列数据存储文件
*.mrk #列标记文件

创建表语法关键字：
PRIMARY KEY ( ) #定义主索引字段；
index_granularity #主索引分组情况；每组长度；
index_granularity_bytes # 设置为0表示禁止自适应分组长度；
ORDER BY #表中需要排序的字段；

数据示例

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;
1
2
3
4
5
6
7
8
9
10

注：
如果未指定 PRIMARY KEY 主键字段；会隐式将主键设置为同排序字段；
如果同时指定了主键和排序键，则主键必须是排序键的前缀;

hits_UserID_URL表有887万行数据；

数据库会分别生成以下文件：

列数据文件:

UserID.bin
URL.bin
EventTime.bin
1
2
3

数据将按照选定的排序字段进行字典排序；并且在有序数据下进行压缩处理；

数据分组
将上图数据按照 index_granularity 设定值8192进行逻辑分组；
得到一下数据结构；总共被分为1083个组（序号从0 开始）；每一组有8192行数据（最后一组只有3937行）；
图中每一个分组的主键列中有一个橙色标记值； 该标记为其所在分组中属于最小值（字典序）；

主索引文件：

primary.idx 
1

主键文件记录了分组数据中主键列最小值数据（被橙色标记数据）；生成以下数据结构；

标记文件

UserID.mrk
URL.mrk
EventTime.mrk
1
2
3

标记文件与主索引文件基本一致；区别在于该文件存储的不是最小列值； 而是存储该分组的物理地址；分别记录了缩后数据块地址；以及数据块解压后的真实地址；存储的位置值是以偏移量的方式记录；

block_offset ：压缩版本的数据位置
granule_offset： 解压后数据位置
1
2

执行流程

现在查询userID为749927693的用户点击次数最多的10个链接；

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

1、主索引文件完全加载到主内存中。如果文件大于可用的空闲内存空间，则ClickHouse将发生错误。

2、使用二分查找法对userID字段进行查找；识别出ID: 749927693可能存在于那些组里边；
分组为176的最小userID为747148242 ，大于749927693；
并且下一个分组为 177 的最小 UserID 值大于749927693。因此，只有分组176 才可能包含 UserID 列值为 749.927.693 的行。

3、为了进一步确认分组中是否存在749927693的值；需要将该分组的数据 ：8192 行流式传输到 ClickHouse 中

步骤：
通过分组标记：176 ；
在userID.mrk文件中block_offset 查找到分组为176的物理地址；
将176压缩块真实数据传入内存中并解压；
再通过userID.mrk文件中granule_offset 查找解压后的数据块；（压缩数据可能不止一个分组）；

通过上边几步操作后；处理只需要扫描8192 行就可以确定出userID为：749927693数据；

总结：
clickhouse 在以有序的列存储情况下；
对所有主索引进行逻辑分组处理生成主索引文件 primary.idx 并使用二分查找法检索该文件查找分组编号：mark;
然后通过mark 在userID标记文件UserID.mrk中查找出压缩数据数据与未压缩数据物理位置；从而在较小的数据量中检索出需要的信息；