Clickhouse使用优化

Clickhouse使用规范

建表规范

先看下clickhouse的建表语法：

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()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

基本结构跟Mysql类似，这里主要说下以下几点，表引擎、分区和索引

表引擎

表引擎（即表的类型）决定了：

• 数据的存储方式和位置，写到哪里以及从哪里读取数据
• 支持哪些查询以及如何支持。
• 并发数据访问。
• 索引的使用（如果存在）。
• 是否可以执行多线程请求。
• 数据复制参数。

大部分场景下，我们使用MergeTree即可满足业务需求，MergeTree是clickhouse里面适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。 MergeTree系列引擎支持数据复制（使用Replicated* 的引擎版本），分区和一些其他引擎不支持的其他功能。

在建表的时候指定表引擎，ENGINE = MergeTree()

分区

分区是在一个表中通过指定的规则划分而成的逻辑数据集。可以按任意标准进行分区，如按月，按日或按事件类型。为了减少需要操作的数据，每个分区都是分开存储的。访问数据时，ClickHouse 尽量使用这些分区的最小子集。

分区是在 建表 时通过 PARTITION BY expr 子句指定的。分区键可以是表中列的任意表达式。例如，指定按月分区，表达式为 toYYYYMM(date_column)：

CREATE TABLE visits
(
    VisitDate Date,
    Hour UInt8,
    ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;

分区键也可以是表达式元组（类似 主键 ）。例如：

ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/name', 'replica1', Sign)
PARTITION BY (toMonday(StartDate), EventType)
ORDER BY (CounterID, StartDate, intHash32(UserID));

上例中，我们设置按一周内的事件类型分区。注意，分区的选择需要根据数据量和业务场景进行选择，不宜过大或者过精细，过大会导致一个分区内的数据太多，过精细导致分区文件太多，文件系统中的文件数量过多和需要打开的文件描述符过多，导致 SELECT 查询效率不佳。

可以通过 system.parts 表查看表片段和分区信息。例如，假设我们有一个 visits 表，按月分区。对 system.parts 表执行 SELECT：

SELECT
    partition,
    name,
    active
FROM system.parts
WHERE table = 'visits'

分区备份

某些场景下，当数据量很大，并且我们对于历史数据不需要查询，但是又不便删除，需要的时候又要查询使用，这个时候可以使用clickhouse的分区卸载/装载来实现业务需求。

比如我们clickhouse数据按周进行分区，业务上面只查询最近一年的数据，那么对于去年的数据，可以将对应的分区卸载，然后放到备份磁盘进行备份保存。

分区卸载DETACH

单机表：

ALTER TABLE beluga.src_soc_alarm_log_old DETACH PARTITION '20221121';

分布式表：

ALTER TABLE beluga.src_soc_alarm_log_old ON CLUSTER totems_distributed DETACH PARTITION '20221121';

这样数据会移动到 detached 目录下。

分区装载ATTACH

ALTER TABLE beluga.src_soc_alarm_log_old ATTACH PARTITION '20221121';

索引规范

主键、排序键索引

主键索引和排序键索引在建表的时候指定，属于一级索引，用于加速表的查询。

创建如下：

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;

• 如果我们只指定了排序键，那么主键将隐式定义为排序键。
• 为了提高内存效率，我们显式地指定了一个主键，只包含查询过滤的列。基于主键的主索引被完全加载到主内存中。
• 为了上下文的一致性和最大的压缩比例，我们单独定义了排序键，排序键包含当前表所有的列（和压缩算法有关，一般排序之后又更好的压缩率）。
• 如果同时指定了主键和排序键，则主键必须是排序键的前缀。

主键和排序键的选取也要根据业务场景来，通常是查询用的比较多的字段。主键索引可以显著的加快查询速度。

跳数索引

跳数索引属于clickhouse的二级索引，顾名思义跳数索引帮助Clickhouse快速跳过不匹配的数据块，从而减少查询匹配的数据，提升查询性能。

首先用户只能在MergeTree表引擎上使用数据跳数索引，所以建表的时候注意引擎类型。跳数索引往往需要根据业务的查询场景，数据类型及特点来选择。在介绍跳数索引的类型之前，先介绍granule(颗粒)这个概念，这个是使用clickhouse时最常遇到的底层概念。ClickHouse的存储数据的方式是列式存储，对于列存格式来说，为了避免每次必须读取整个文件的尴尬，对数据会做一次水平切分。在ClickHouse的语境下，切分的标准则是数据条数，每8192条数据会做一次切分，每个分片被称为Granule。这个在建表的时候我们可以指定，如下：

CREATE TABLE beluga.src_soc_alarm_log_index
(

    `uuid` String,

    `table_name` String,
    
    ......

    `flow_down_bit` Nullable(UInt64)
)
ENGINE = MergeTree
PARTITION BY toMonday(received_date)
ORDER BY (received_date, received_time)
SETTINGS index_granularity = 8192;

SETTINGS index_granularity = 8192 就是指定Granule切分的大小。

跳数索引类型

minmax

是最轻量的一种索引类型，就是在索引文件里保存了每个granule的最大值和最小值（针对索引列），然后在查询时，根据查询条件是否在最大值和最小值框定的范围内来确定是否排除granule。说它最轻量是因为索引需要存储的数据量最小的。

set

是在索引文件里保存每个granule的所有唯一值。它的优势是不存在假阳性，有就是有，没有就是没有，查询效率很高。但有个缺点是索引的大小不可控，如果数据的唯一值比较多，索引就会变得很大，反而影响查询性能。所以ClickHouse允许用户建索引时，设置索引的max_size
，当一个granule的唯一值数量超过max_size时，就不保存这个granule的set。例如set(100)设置max_size是100时，如果一个granule的唯一值超过了100，那么对这个granule就不会保存对应set，则每次查询时都会读取这个granule。set索引适合重复数据比较多，总的值不多的情况

Bloom Filter Types

Bloom filter是一种数据结构，它允许对集合成员进行高效的是否存在测试，但代价是有轻微的误报。在跳数索引的使用场景，假阳性不是一个大问题，因为惟一的问题只是读取一些不必要的块。

跳数索引的创建和删除，下面是建表创建：

CREATE TABLE beluga.src_soc_alarm_log_index
(

    `uuid` String,

    `table_name` String,
    
    ......

    `flow_down_bit` Nullable(UInt64),
    
    INDEX index_protocol_name protocol_name TYPE set(0) GRANULARITY 5,

    INDEX set_filter_index_src_port src_port TYPE set(0) GRANULARITY 5,

    INDEX set_filter_index_dst_port dst_port TYPE set(0) GRANULARITY 5,

    INDEX bloom_filter_index_src_ip src_ip TYPE bloom_filter(0.025) GRANULARITY 5,

    INDEX bloom_filter_index_dst_ip dst_ip TYPE bloom_filter(0.025) GRANULARITY 5
)
ENGINE = MergeTree
PARTITION BY toMonday(received_date)
ORDER BY (received_date, received_time)
SETTINGS index_granularity = 8192;

也可以后期根据业务需要再单独添加

添加：

ALTER TABLE beluga.src_soc_alarm_log add INDEX bloom_filter_index_src_ip src_ip TYPE bloom_filter(0.025) GRANULARITY 5;

删除:

ALTER TABLE beluga.src_soc_alarm_log DROP INDEX bloom_filter_index_src_ip;

注意：添加跳数索引对于历史的数据是不生效的，如果需要生效，执行下面命令：

ALTER TABLE beluga.src_soc_alarm_log MATERIALIZE INDEX bloom_filter_index_src_ip;

跳数索引的选择，需要结合查询业务和数据特点来确定，举例如下：

比如针对五元组的查询优化，一开始源端口src_port使用minmax跳数索引，查询的时候，发现跳过的数据块不多，minmax跳数索引保存的是数据块内所有数据的最大值和最小值，当我们查询src_port = 3306，某个数据块minmax 80到8080是符合的不被跳过，但实际上这个数据块并没有3306的源端口，考虑到实际业务场景端口最多0-65535，于是这里使用set跳数索引更加适合，set跳数索引存的是唯一值，还是刚刚的数据块，由于确实没有3306这条数据，那么匹配的时候就不满足而被跳过，clickhouse查询更快。

查询规范

1.Prewhere代替where

Prewhere 和 where 语句的作用相同，用来过滤数据。

不同之处在于 prewhere 只支持 MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤 之后再读取 select 声明的列字段来补全其余属性。

当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化 执行过滤阶段的数据读取方式，降低 io 操作。

在某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高。

2.指定select字段查询

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

反例：

SELECT * FROM src_soc_alarm_log_all

正例：

SELECT
    uuid,
    collector_type,
    policy_name,
    log_type_name,
    src_ip,
    dst_ip,
    device_type_name,
    log_time,
    origin_event_name,
    received_time
FROM src_soc_alarm_log_all

3.使用分区裁剪

分区裁剪就是只读取需要的分区，在过滤条件中指定，分区字段在建表的时候指定的，比如：

SELECT
    uuid,
    collector_type,
    policy_name,
    log_type_name,
    src_ip,
    dst_ip,
    device_type_name,
    log_time,
    origin_event_name,
    received_time
FROM src_soc_alarm_log_all
prewhere (received_date >= '2022-11-24' AND received_date <= '2022-11-24')

4.order by结合where limit

千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用，避免返回所有数据，这样可以有效减少扫描行数。例如：

SELECT
    uuid,
    collector_type,
    policy_name,
    log_type_name,
    src_ip,
    dst_ip,
    device_type_name,
    log_time,
    origin_event_name,
    received_time
FROM src_soc_alarm_log_all
prewhere (received_date >= '2022-11-24' AND received_date <= '2022-11-24') AND src_port = 3306
ORDER BY received_date DESC
LIMIT 0, 20;

5.避免构建虚拟列

虚拟列：原始表不存在的字段，查询语句虚拟出来的字段。如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。比如下面反例这样：

SELECT Income,Age,**Income/Age as IncRate** FROM datasets.hits_v1;

正例:

拿到 Income 和 Age 后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储

SELECT Income,Age FROM datasets.hits_v1;

6.使用建表排序键排序，避免查询再order by一次

建表的时候，可以指定多个排序字段，当数据入库的时候就会排好序，查询的时候无须再扫描数据进行排序，减少扫描行数。

反例：

SELECT
    uuid,
    collector_type,
    policy_name,
    log_type_name,
    src_ip,
    dst_ip,
    device_type_name,
    log_time,
    origin_event_name,
    received_time
FROM src_soc_alarm_log_all
prewhere (received_date >= '2022-11-24' AND received_date <= '2022-11-24')
ORDER BY received_time DESC
LIMIT 0, 20;

这里使用了received_time进行倒叙，CK会为了received_time的排序，扫描整个筛选出来的数据，查询速度就会很慢，扫描行数357.00 million rows

20 rows in set. Elapsed: 140.238 sec. Processed 357.00 million rows, 76.87 GB (2.55 million rows/s., 548.12 MB/s.)

11月24日的数据：

┌───count()─┐
│ 358512696 │
└───────────┘

可以看到CK全部扫描了一遍

正例：

SELECT
    uuid,
    collector_type,
    policy_name,
    log_type_name,
    src_ip,
    dst_ip,
    device_type_name,
    log_time,
    origin_event_name,
    received_time
FROM src_soc_alarm_log_all
prewhere (received_date >= '2022-11-24' AND received_date <= '2022-11-24')
ORDER BY received_date DESC
LIMIT 0, 20;

根据received_date排序即可，同一天下面的数据在入库的时候就已经排好序了，CK只需要读取部分即可。

20 rows in set. Elapsed: 0.535 sec. Processed 56.16 thousand rows, 12.09 MB (104.87 thousand rows/s., 22.58 MB/s.)

可以看到扫描行数只有56.16 thousand rows

这里需要配合建表的排序键，建表如下：

CREATE TABLE beluga.src_soc_alarm_log_index
(

    `uuid` String,

    `table_name` String,
    
    ......

    `flow_down_bit` Nullable(UInt64)
)
ENGINE = MergeTree
PARTITION BY toMonday(received_date)
ORDER BY (received_date, received_time)
SETTINGS index_granularity = 8192;

最优实践

Soc检索原始日志，当日志查出来之后默认返回uuid，采集类型、源IP、目的IP、事件名称等字段，然后用户点击某一条数据，再根据uuid进一步查询其他展示字段。

一开始的查询语句：

SELECT
    uuid,
    table_name,
    collector_type,
    policy_uuid,
    policy_name,
    log_type_uuid,
    log_type_name,
    manufacturer_name,
    dict_result_id,
    dict_result_name,
    dict_certainty_level_id,
    dict_certainty_level_name,
    dict_attack_stage_id,
    dict_attack_stage_name,
    dict_threat_level_id,
    dict_threat_level_name,
    protocol_num,
    protocol_name,
    app_protocol_name,
    src_ip,
    src_ip6,
    src_inet_type,
    src_tran_ip,
    src_tran_ip6,
    src_mac,
    src_asset_id,
    src_port,
    src_tran_port,
    src_country,
    src_region,
    src_city,
    src_network_type_id,
    src_network_type,
    ingress_interface,
    src_zone,
    dst_ip,
    dst_ip6,
    dst_inet_type,
    dst_tran_ip,
    dst_tran_ip6,
    dst_mac,
    dst_asset_id,
    dst_port,
    dst_tran_port,
    dst_country,
    dst_region,
    dst_city,
    dst_network_type_id,
    dst_network_type,
    egress_interface,
    dst_zone,
    log_level,
    device_id,
    device_name,
    device_ip,
    device_type_uuid,
    device_type_name,
    log_time,
    device_response,
    received_time,
    received_date,
    collector_ip,
    origin_event_name,
    origin_event_digest,
    origin_event_level,
    origin_event_type,
    origin_event_time,
    user_name,
    user_program,
    operation,
    user_object,
    user_zone,
    attack_result,
    cves,
    cvss_score,
    duration,
    object_name,
    object_type,
    object_status,
    domain_name,
    command,
    path,
    reference_link,
    origin_attack_method,
    origin_attack_phase,
    detail,
    origin_decrypt_msg,
    origin_msg,
    agent_ip,
    agent_id,
    host_name,
    internal_ip,
    external_ip,
    host_tag,
    host_memo,
    host_group_name,
    os,
    http_request_method,
    http_request_host,
    http_request_url,
    http_request_headers,
    http_request_referer,
    http_request_version,
    http_request_user_agent,
    http_request_cookie,
    http_request_content_type,
    http_request_body,
    http_response_code,
    http_response_content_type,
    http_response_body,
    http_response_headers,
    dns_request_content,
    dns_request_domain,
    dns_request_domain_type,
    dns_response_address,
    flow_up_packets,
    flow_down_packets,
    flow_up_bit,
    flow_down_bit
FROM src_soc_alarm_log_all
WHERE uuid = 'cf42182f9bdc444a83b8338dec542757'
ORDER BY received_date DESC
LIMIT 0, 1;

查询时间直接超时，因为这样基本上要全表扫描一遍直到找到那条数据。

第一版优化

根据主键筛选数据，缩小查询范围，因为页面上面是有时间范围筛选的：

SELECT
    uuid,
    ......,
    flow_up_bit,
    flow_down_bit
FROM src_soc_alarm_log_all
WHERE ( received_date >= '2022-11-18'  and received_date <= '2022-11-18'  ) and uuid = 'cf42182f9bdc444a83b8338dec542757'
ORDER BY received_date DESC
LIMIT 0, 1;

查询时间还是很久，因为这个日期的数据量在3亿，扫描3亿数据还是比较慢

第二版优化

单独对uuid增加跳数索引，这里minmax和set都不太适合，使用bloom_filter

ALTER TABLE beluga.src_soc_alarm_log add INDEX bloom_filter_index_uuid uuid TYPE bloom_filter(0.025) GRANULARITY 5;

对历史数据重构索引

ALTER TABLE beluga.src_soc_alarm_log MATERIALIZE INDEX bloom_filter_index_uuid;

再次查询

SELECT
    uuid,
    ......,
    flow_up_bit,
    flow_down_bit
FROM src_soc_alarm_log_all
WHERE ( received_date >= '2022-11-18'  and received_date <= '2022-11-18'  ) and uuid = 'cf42182f9bdc444a83b8338dec542757'
ORDER BY received_date DESC
LIMIT 0, 1;

查询结果：

1 rows in set. Elapsed: 4.875 sec. Processed 6.28 million rows, 268.47 MB (1.29 million rows/s., 55.07 MB/s.)

这个时候扫描是数据量就只有600多万，查询时间将近5秒

第三版优化

上面的查询我们使用主键received_date定位到了天的数据维度，根据跳数索引再过滤掉了一部分数据，但还是有一部分数据没有过滤掉，那怎么样才能进一步缩小数据范围，定位到数据呢，使用联合主键received_time，

SELECT
    uuid,
    ......,
    flow_up_bit,
    flow_down_bit
FROM src_soc_alarm_log_all
WHERE ( received_date >= '2022-11-18'  and received_date <= '2022-11-18'  ) and received_time = 1669792569835 and uuid = 'cf42182f9bdc444a83b8338dec542757'
LIMIT 0, 1;

查询结果：

1 rows in set. Elapsed: 0.278 sec. Processed 4.10 thousand rows, 11.14 MB (77.66 thousand rows/s., 211.29 MB/s.)

可以看到这个时候扫描数据量只有4000多行，速度秒级返回

通过以上实践案例，我们可以看到要想快速查询，就需要快速找到数据并缩小数据范围，这里充分发挥了索引的功能

常见问题

1.如何查看跳数索引使用详情情况

可以在clickhouse命令终端，输入日志级别

SET send_logs_level='trace';

这样当我们查询的时候，可以通过输出日志查找跳数索引过滤的数据块，如下：

<Debug> default.skip_table (933d4b2c-8cea-4bf9-8c93-c56e900eefd1) (SelectExecutor): Index `vix` has dropped 6102/6104 granules.

可以看到索引名称vix总共6104块，跳过6102数据块

2.服务器上面如何进入到clickhouse终端

clickhouse-client -mn

3.Clickhouse如何开启远程访问

vi /etc/clickhouse-server/config.xml
# 修改listen_host
<listen_host>0.0.0.0</listen_host>