网站三站合一,朋友做网站网站挣了好多钱,开发手机应用网站,三亚中国检科院生物安全中心门户网站建设一、现场场景概述
现场每天每张表入库数据量大约2-4亿条,页面涉及到自定义时间段查询(白天08:00-15:00,夜晚23:00-06:00)与不同时间段(最近一天、一周、一个月和全部)的统计指标查询。
二、主要问题
时间跨度大无查询或查询条件命中数据过多的分页查询场景速度慢 (主要是数据…一、现场场景概述
现场每天每张表入库数据量大约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 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.. --databasedsdbak --queryselect * from dns_log SETTINGS max_execution_time60000000 FORMAT CSV dns_log_local2.csv
#导入
nohup cat dns_log_local2.csv | clickhouse-client -h localhost --port 9000 -u default --password Az123456.. --databasedsd --queryINSERT INTO dns_log FORMAT CSV SETTINGS max_execution_time60000 五、查询优化
近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
);
