关于clickhouse:Clickhouse查询性能优化

尽管 clickhouse 在大数据量查问速度会比关系型数据库如 mysql 或者 postrges 快很多，但还是有很多中央须要去理解和配置，达到提供最低资源获取最大产出

以下内容次要来源于 clickhouse 官网中文文档

主索引采纳 B+Tree 的数据结构进行疾速定位所在行，搜寻一个条目标均匀工夫复杂度为 O(log2n)，对于一个有1000 万行的表，这意味着须要 23 步来定位任何索引条目
额定的磁盘和内存开销
向表中增加新行和向索引中增加条目时更高的插入老本(有时还须要从新均衡B-Tree)

`clickhouse`索引

依照主键列的程序将一组行存储在磁盘，一组数据行（称为颗粒（granule），大小是 index_granularity 定义配置的，默认8192）构建一个索引条目，即稠密索引
稠密主索引容许它疾速 (通过对索引项进行二分查找) 辨认可能匹配查问的行组，而后潜在的匹配行组 (颗粒) 以并行的形式被加载到 ClickHouse 引擎中，以便找到匹配的行

`Clickhouse`的颗粒与查问

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

下表的建表语句 copy from ClickHouse 主键索引最佳实际

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;

如下图所示，每 8192 行数属于一个颗粒（图片来源于 clickhouse 官网文档）

其中主索引是UserID

查问第一阶段：颗粒抉择

Clickhouse通过稠密主索引来疾速 (二分查找算法) 抉择可能蕴含匹配查问的行的颗粒。

查问 第二阶段：数据读取

ClickHouse定位所选的颗粒，以便将它们的所有行流到 ClickHouse 引擎中，以便找到理论匹配查问的行

数据库中某个表的某个列中不反复行的总个数
对于 mysql 等关系型数据库，对于索引列，基数越大，查问成果越好，基数越小，查问成果越差，现实的索引列满足：基数 / 理论行 =1，比方 user 表当中如果 username 是全表惟一的，那么在 username 下面利用的索引在命中索引前提下查问成果最好

`clickhouse`表

上表中指定的主键是(UserID, URL)，排序键是(UserID, URL, EventTime)，留神，排序键指定之后不能更改排序键的值，排序键也不能是空，主键必须是排序键的前缀

字段基数的排列程序是 UserID,URL，EventTime，即UserID 去重数量不多（低基数），URL去重之后数量较多，EventTime去重之后数量最多(高基数)

所以最佳的主键设计是 (UserID, URL)，在Clickhouse 的索引文件当中就是先依照雷同 UserID 排序，具备雷同的 UserID 状况下再依照 URL 排序

主键或者排序键的最佳设计就是放弃 前缀主键低基数 ，在这样的状况下会有起码的颗粒流入Clickhouse 引擎

业务场景中会常常要求依照工夫倒序排列的需要

计划一

放弃 前缀主键低基数 的数据库设计，在数据库查问的时候指定ORDER BY EventTime DESC

该计划在理论测试中发现该查问会常常导致全表扫描以至于速度十分慢，在千万级数据表下面查问常常大于 5 秒

计划二

排序键就定义为 ORDER BY -EventTime，这样就违反了 前缀主键低基数 的优化设计了，然而查问的时候不必在 sql 语句当中显式指定工夫倒排了，所以在这种状况下就须要应用到 Clickhouse 的跳数索引

`Clickhouse`跳数索引类型

以下文档 copy from clickhouse 的章节 深刻了解 ClickHouse 跳数索引

minmax

这种轻量级索引类型不须要参数。它存储每个块的索引表达式的最小值和最大值(如果表达式是一个元组，它别离存储元组元素的每个成员的值)。对于偏向于按值涣散排序的列，这种类型十分现实。在查询处理期间，这种索引类型的开销通常是最小的。

这种类型的索引只实用于标量或元组表达式——索引永远不适用于返回数组或 map 数据类型的表达式。
set

这种轻量级索引类型承受单个参数 max_size，即每个块的值集(0 容许有限数量的离散值)。这个汇合蕴含块中的所有值(如果值的数量超过max_size 则为空)。这种索引类型实用于每组颗粒中基数较低 (实质上是“汇集在一起”) 但总体基数较高的列。

该索引的老本、性能和有效性取决于块中的基数。如果每个块蕴含大量惟一值，那么针对大型索引集计算查问条件将十分低廉，或者因为索引超过 max_size 而为空，因而索引将不利用。
Bloom Filter Types

Bloom filter是一种数据结构，它容许对汇合成员进行高效的是否存在测试，但代价是有轻微的误报。在跳数索引的应用场景，假阳性不是一个大问题，因为惟一的问题只是读取一些不必要的块。潜在的假阳性意味着索引表达式应该为真，否则无效的数据可能会被跳过。

因为 Bloom filter 能够更无效地解决大量离散值的测试，所以它们能够实用于大量条件表达式判断的场景。特地的是 Bloom filter 索引能够利用于数组，数组中的每个值都被测试，也能够利用于 map，通过应用mapKeys 或mapValues函数将键或值转换为数组。

有三种基于 Bloom 过滤器的数据跳数索引类型：
- 根本的 bloom_filter 承受一个可选参数，该参数示意在 0 到 1 之间容许的“假阳性”率(如果未指定，则应用0.025)。
- 更业余的 tokenbf_v1。须要三个参数，用来优化布隆过滤器：（1）过滤器的大小字节(大过滤器有更少的假阳性，有更高的存储老本)，（2）哈希函数的个数(更多的散列函数能够缩小假阳性)。（3）布隆过滤器哈希函数的种子。此索引仅实用于String、FixedString 和Map类型的数据。输出表达式被宰割为由非字母数字字符分隔的字符序列。例如，列值 This is a candidate for a "full text" search 将被宰割为 This is a candidate for full text search。它用于LIKE、EQUALS、in、hasToken() 和相似的长字符串中单词和其余值的搜寻。例如，一种可能的用处是在非构造的应用程序日志行列中搜寻大量的类名或行号。
- 更业余的 ngrambf_v1。该索引的性能与tokenbf_v1 雷同。在 Bloom filter 设置之前须要一个额定的参数，即要索引的 ngram 的大小。一个 ngram 是长度为 n 的任何字符串，比方如果 n 是 4，A short string会被宰割为A sh` sho, shor, hort, ort s, or st, r str, stri, trin, ring`。这个索引对于文本搜寻也很有用，特地是没有单词间断的语言，比方中文。

高基数字段适宜采纳 bloom filter 跳数索引放慢查问速度

Bloom filter解释，由一个超长的二进制位数组和一系列的哈希函数组成，二进制位数组初始全副为 0，当给定一个待查问的元素时，这个元素会被一系列哈希函数计算映射出一系列的值，所有的值在位数组的偏移量处理为 1，同样是这个元素通过哈希函数计算后失去所有的偏移地位，若这些地位全都为 1，则判断这个元素在这个汇合中，若有一个不为 1，则判断这个元素不在这个汇合中。

更具体的解释参考文档深刻了解布隆过滤器以及布隆过滤器 Bloom Filter 详解

如果想自定义过滤器的输出参数和假阳性概率，能够拜访如下地址获取对应的参数布隆过滤器计算网址

调整建表语句如下，

CREATE TABLE hits_UserID_URL
(    
--  退出 `Id` 字段，采纳雪花算法生成
    `Id` UInt64,
    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime,

--  退出布隆过滤器跳数索引，采纳 clickhouse 提供的默认 bloom_filter 函数
--  配置容许假阳性概率为 0.001，官网默认值是 0.025
--  概率越小，假阳性概率越低，查问时候会有越少的颗粒发送给 clickhouse 引擎，所以查问速度更快，然而索引占用的磁盘空间也越多
--  每个索引块由颗粒（granule）组成, 例如，如果主表索引粒度为 8192 行，GRANULARITY 为 4，则每个索引“块”将为 32768 行
--    GRANULARITY 参数配置为 1 就能够
    INDEX skip_index_url URL TYPE bloom_filter(0.001) GRANULARITY 1
)
ENGINE = MergeTree
-- 依照倒序 Id 排列，Id 和工夫戳强相干，所以查问的时候不必指定 Order by，数据获取到的时候默认就是 order by id desc
ORDER BY -Id
SETTINGS index_granularity = 8192, index_granularity_bytes = 0;

该段形容摘抄自 clickhouse 官网中文文档低基数类型

LowCardinality 是一种扭转数据存储和数据处理办法的概念，ClickHouse会把 LowCardinality 所在的列进行字典编码，对很多利用来说，解决字典编码的数据能够显著的减少查问速度。字典编码能够参考详解 LZ77 字典编码压缩和解压缩流程

应用 LowCarditality 数据类型的效率依赖于数据的多样性，如果一个字典蕴含少于 10000 个不同的值，那么 ClickHouse 能够进行更高效的数据存储和解决，反之如果字典多于 10000，效率会体现的更差。

当应用字符类型的时候，能够思考应用 LowCardinality 代替 Enum 字段，LowCardinality 通常更加灵便和高效。

当初假如须要在表外面退出 Country 字段，全世界总共国家总数的基数不大，如果能够的话存储为 UInt8，其余低基数枚举也尽量存储为Int 类型

如果 Country 字段是 String 类型，那么须要调整建表语句如下

CREATE TABLE hits_UserID_URL
(    
    `Id` UInt64,
--  没有优化的时候, 类型是 String，字段定义为 `Country` String,
--  优化的时候间接加上一个 LowCardinality 函数
--  在曾经有的表下面批改的时候不须要改变其余代码，对 python, rust，golang 或者其余语言的客户端代码来说，`Country` 字段就是 String
--  加上 LowCardinality 之后，查问如果用到 Country，减速成果非常明显
    `Country` LowCardinality(String),

    `UserID` UInt32,
    `URL` String,
    `EventTime` DateTime,

    INDEX skip_index_url URL TYPE bloom_filter(0.001) GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY -Id
SETTINGS index_granularity = 8192, index_granularity_bytes = 0;

LowCardinality所蕴含的字段不能是Int，然而配置文件中有个参数能够扭转这个状况

对应配置文档如下

配置参数是allow_suspicious_low_cardinality_types

容许或限度将与固定大小为 8 字节或更少的数据类型一起应用：数字数据类型和FixedString(8_bytes_or_less).

对于小的固定值，应用 ofLowCardinality通常是低效的，因为 ClickHouse 为每一行存储一个数字索引。因而：

磁盘空间使用率可能会回升。
RAM 耗费可能更高，具体取决于字典大小。
因为额定的编码 / 编码操作，某些性能可能会运行得更慢。

因为上述所有起因，MergeTree -engine表中的合并工夫可能会减少。

所以恪守 Clickhouse 的倡议，数字类型的数据就不应用 LowCardinality 参数了

`CPU`参数优化

clickhouse极大的依赖 CPU 运算和程序磁盘 IO 的速度，所以为 clickhouse 配置 CPU 高性能模式是失常的

linux的 cpu 共有以下几种模式

performance: 固定工作在其反对的最高运行频率上
powersave: 省电模式，固定工作在其反对的最低运行频率上
Userspace: 零碎将变频策略的决策权交给了用户态应用程序
ondemand: 齐全在内核态下工作并且可能以更加细粒度的工夫距离对系统负载状况进行采样剖析并管制频率
conservative: 在不影响零碎性能的前提下做到更高效的节能，降频比拟迟缓激进

查看以后 cpu 运行模式

$ sudo cat /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor

设置为性能模式

$ sudo cpupower frequency-set -g performance

深刻了解 clickhouse 跳数索引

clickhouse主键索引最佳实际

clickhouse官网中文文档低基数类型

深刻了解布隆过滤器

布隆过滤器 Bloom Filter 详解

布隆过滤器计算网址

详解 LZ77 字典编码压缩和解压缩流程

关于clickhouse:Clickhouse查询性能优化

索引设计

关系型数据库设计

`clickhouse`索引

`Clickhouse`的颗粒与查问

数据表主键以及排序字段优化

基数概念

`clickhouse`表

理论利用的限度

`Clickhouse`跳数索引类型

高基数字段采纳跳数索引优化

低基数字段优化

形容

具体操作

留神点

`CPU`参数优化

参考浏览

索引设计

关系型数据库设计

clickhouse索引

Clickhouse的颗粒与查问

数据表主键以及排序字段优化

基数概念

clickhouse表

理论利用的限度

Clickhouse跳数索引类型

高基数字段采纳跳数索引优化

低基数字段优化

形容

具体操作

留神点

CPU参数优化

参考浏览

`clickhouse`索引

`Clickhouse`的颗粒与查问

`clickhouse`表

`Clickhouse`跳数索引类型

`CPU`参数优化