跳转至

UTS clickhouse数据查询-深度分页问题优化思路

  1. 新建一个django项目
  2. 让django项目跑起来
  3. 使用程序自动迁移文件以达到自适应
  4. 生成完自动迁移规则

clickhouse-client -h 127.0.0.1 -u uts --port 9100 --password ~Uts2020db

select timestamp, sip4,sip6,sport,dip4,dip6,dport,protocol,app_proto,app_desc_cn,dmac,s_card_name,s_device_hash,tx_bytes,tx_pkts,rx_bytes,rx_pkts,isIPv4,direct,first_time,last_time from storage_session prewhere timestamp >= 1700460108 and timestamp <= 1700546508 and msgtype = 12 order by timestamp desc limit 10 offset 37854680;

select timestamp,sip4,sip6,sport,dip4,dip6,dport,protocol,app_proto,app_desc_cn,dmac,s_card_name,s_device_hash,tx_bytes,tx_pkts,rx_bytes,rx_pkts,isIPv4,direct,first_time,last_time from storage_session where sid in (select sid from storage_session prewhere timestamp >= 1700460108 and timestamp <= 1700546508 and msgtype = 12 order by timestamp desc limit 10 offset 37854680);

select * from storage_session where sid in (select sid from storage_session prewhere timestamp >= 1672548505 and timestamp <= 1700628511 and msgtype = 12 order by timestamp desc limit 10 offset 0)

select * from storage_session where sid global in (select sid from storage_session prewhere timestamp >= 1700460108 and timestamp <= 1700610748 and msgtype = 12 order by timestamp desc limit 10 offset 37854680);

select timestamp,sip4,sip6,sport,dip4,dip6,dport,protocol,app_proto,app_desc_cn,dmac,s_card_name,s_device_hash,tx_bytes,tx_pkts,rx_bytes,rx_pkts,isIPv4,direct,first_time,last_time,sid from storage_session prewhere sid global in (select sid from storage_session prewhere timestamp >= 1672551451 and timestamp <= 1700631446 and msgtype = 12 and 1 = 1 order by timestamp desc limit 10 offset 1654281490) order by timestamp desc

select * from storage_http prewhere sid in (select sid from storage_http prewhere timestamp >= 1700533898 and timestamp <= 1700620298 and msgtype = 1 order by timestamp desc limit 10 offset 4075190) order by timestamp desc

删除索引数据

ALTER TABLE storage_session DROP INDEX msgtype;

创建索引

ALTER TABLE storage_http ADD INDEX msgtype(timestamp, msgtype, sid, sip4, sip6, sport, dip4, dip6, dport) TYPE minmax GRANULARITY 3;

对索引进行物化

ALTER TABLE storage_http MATERIALIZE INDEX msgtype;

对以下表均使用创建索引

storage_session storage_http storage_others storage_file storage_iot_coap storage_iot_mqtt storage_iot_xmpp storage_iot_other storage_api_meta storage_dns storage_ssl storage_unfiled storage_ics storage_sensitive storage_threat

查询物化的进度

select * from system.mutations where is_done='0';

time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t))

select * from storage_session prewhere timestamp > 1700638083 and timestamp <= 1700648883 and msgtype = 12 order by timestamp desc limit 10 offset 490270

select * from storage_session prewhere sid global in (select sid from storage_session prewhere timestamp > 1700634513 and timestamp <= 1700731293 and msgtype = 12 order by timestamp desc limit 10 offset 490270) order by timestamp desc

timestamp>=1700710392 and end_time<=1700796792

1700731368

1700645643

select 1 from system.tables where database = 'uts' and name = 'storage_sensitive_view';

建立count的物化试图

create MATERIALIZED view if not exists storage_unfiled_view ENGINE=SummingMergeTree partition by toYYYYMMDD(timestamp) order by (timestamp, msgtype) as select msgtype, timestamp, count(msgtype) as cnt from uts.storage_unfiled group by msgtype, timestamp;

insert into storage_unfiled_view select msgtype, timestamp, count(msgtype) as cnt from uts.storage_unfiled group by msgtype, timestamp;

对一下表执行上面两个语句

storage_session storage_http storage_others storage_file storage_api_meta storage_dns storage_ssl storage_unfiled storage_sensitive storage_threat

未增加索引

image-20231123143526792

增加索引后

image-20231123144457677