【Clickhouse2022.02 查询优化】

一、现场场景概述

现场每天每张表入库数据量大约2-4亿条,页面涉及到自定义时间段查询(白天08:00-15:00,夜晚23:00-06:00)与不同时间段(最近一天、一周、一个月和全部)的统计指标查询。

二、主要问题

时间跨度大无查询或查询条件命中数据过多的分页查询场景速度慢
(主要是数据量过大orderby慢造成的，需要减少数据量)
需要针对不同时间段的指标进行查询,统计慢
(不同时间段导致缓存无法复用，需要创建投影以便于统计指标的快速查询)
时间跨度选择全部时进行单条件查询慢
(涉及到索引问题,跳数索引值过小，表结构的优化)

三、数据结构优化

表结构修改
主键、排序键的选择，优先将初始化默认的排序字段(一般为时间字段)设置为主键或排序键(会默认创建索引)，这样初始化页面时正序取数据很方便，并且该字段一般作为热点查询字段。后面的排序键根据其和第一排序键组合查询的顺序进行添加，例如:

CREATE TABLE dns_log ON cluster cluste (
	`id` UInt64,
	`session_start_time` DateTime64 (3),
	`src_ip` String,
	`src_port` UInt16,
	`src_area` String,
	`dst_ip` String,
	`dst_port` UInt16,
	`dst_area` String,
	`answer_ip` String,
	`answer_area` String,
	`req_domain` String,
	`req_type` UInt8,
	`domain_len` UInt16
	INDEX idx_rd `req_domain` type bloom_filter() GRANULARITY 4,
	INDEX idx_si src_ip type bloom_filter() GRANULARITY 4,
	INDEX idx_di dst_ip type bloom_filter() GRANULARITY 4,
	INDEX idx_sa src_area type set(3000) GRANULARITY 4,
	INDEX idx_da dst_area type set(3000) GRANULARITY 4 GRANULARITY 4,
	INDEX idx_sp src_port type bloom_filter() GRANULARITY 4,
	INDEX idx_dp dst_port type bloom_filter() GRANULARITY 4,
	INDEX idx_id `id` type minmax() GRANULARITY 4
	) ENGINE = ReplicatedMergeTree ( '/clickhouse/tables/{shard}/dns_log', '{replica}' )
PARTITION BY (toYYYYMMDD (session_start_time),toHour (session_start_time))
ORDER BY(session_start_time, domain_len, answer_count,id )
SETTINGS index_granularity = 8192;

2.索引类型修改
之前的索引类型全部是ngrambf_v1,个人对其不了解,之前的同事创建的，参数为官网的默认值,效果比较差。个人感觉这个索引需要跟家了解业务数据的特性，针对性的调整参数。我这边时间紧任务重，没有时间深究，留到以后在深入。

Minmax:对于数字有序字段(区间范围)很有效果，我这里的id是根据时间戳和其他一些字段雪花生成的，具备顺序性，故这里修改为Minmax索引。
set:我们这边数据90%本都是本省数据，像src_area和dst_area地理位置(城市名称)，基数很小，一般都不过1000,为了保险设置的3倍。
bloom_filter:针对高基数的字段设置布隆索引,数据的ip、域名等与主键(第一排序键)之间没有什么关联,这里使用的默认参数。

3.跳数索引值的选择
我这里都是GRANULARITY 4 ,这个跳数值其实很依赖于主键的选择，主键和索引字段的关联性越强，就能更好选择合适的值进行设置。我这边主键排序是时间，除了id有点关联，其他的索引字段关联性其实都不强。我这边一个块8192行，每次跳4个块(参考你的数据量大小设置)，我这边验证效果还不错。

四、数据导入与导出

修改表结构需要将数据导出备份,重新建表后将数据重新导入。

#数据导出，并且设置执行时长(导出大数据情况下)
nohup  clickhouse-client -h localhost --port 9000 -u default --password Az123456.. --database="dsdbak" --query="select * from dns_log SETTINGS max_execution_time=60000000 FORMAT CSV" > dns_log_local2.csv &
#导入
nohup cat dns_log_local2.csv | clickhouse-client -h localhost --port 9000 -u default --password Az123456.. --database="dsd" --query="INSERT INTO dns_log FORMAT CSV SETTINGS max_execution_time=60000" &

五、查询优化

近5分钟数据查询(大大减少了数据量)
一般应用于近一天、一周、一个月的查询。通过查DATE_ADD函数与MAX查询最新一条数据的方式,找到最新(有数据的时间,不等于now。保证数据查询出的数据不为空)5分钟的数据。代码里要进一步判断,若查询出的结果数量不满足分页条数，还是走之前的逻辑。该方法在实时大数据量的场景，页面初始化加载有明显的提升。

SELECT
	session_start_time,
	src_ip AS srcIp,
	src_port AS srcPort,
	src_area AS srcArea,
	dst_ip AS dstIp,
	dst_port AS dstPort,
	dst_area AS dstArea,
	answer_ip AS answerIp,
	answer_area AS answerArea,
	`req_domain` AS reqDomain,
	req_type AS reqType
FROM
	dsd.threat_alarm
WHERE
	session_start_time >= toDateTime('2023-08-23 10:09:31')
	and session_start_time <= toDateTime('2023-08-24 10:09:31')
	and session_start_time >= (
	SELECT
		DATE_ADD(minute,-5, MAX(session_start_time)) sst
	from
		dsd.threat_alarm )
order by
	session_start_time desc
limit 0,
30

六、创建投影:预聚合|排序

首先投影数据适量，过多的投影会影响集群性能。

--域名分组统计投影
ALTER TABLE dns_log on cluster cluster_3shards_2replicas  ADD PROJECTION dns_log_domain(
SELECT req_domain,count(),max(session_start_time) group by req_domain
);