【Clickhouse2022.02 查询优化】

一、现场场景概述

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

二、主要问题

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

三、数据结构优化

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

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` UInt16INDEX 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分钟的数据。代码里要进一步判断,若查询出的结果数量不满足分页条数，还是走之前的逻辑。该方法在实时大数据量的场景，页面初始化加载有明显的提升。

SELECTsession_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
FROMdsd.threat_alarm
WHEREsession_start_time >= toDateTime('2023-08-23 10:09:31')and session_start_time <= toDateTime('2023-08-24 10:09:31')and session_start_time >= (SELECTDATE_ADD(minute,-5, MAX(session_start_time)) sstfromdsd.threat_alarm )
order bysession_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
);