clickhouse 查询技巧

1. Clickhouse 只有order by的第一个字段才走索引;
2. Partition字段也是索引, 查Partition字段走索引;
3. 即使查询的字段都非索引, 字段类型不同查询速度也不同;
4. 模糊查询的like '%xxx%’不走索引也非常慢, 这种查询使用match会快;
5. 物化视图使用索引同等量数据下, 比原数据更快. 但是不使用索引会非常慢(除非建表条件过滤了非常多数据). 
6. 按第一个order by 字段排序, 查询结果不用重新排序, 查询速度快. (如果非第一个order by 字段会重新排序就很慢了) 即:第一个order by 字段对查询和排序都有很大作用.

 

 

下面是clickhous 造数据

 

---- 1. 打入trace_curr_local

INSERT INTO ff.trace_curr_local (identitycode, account, accounttype, imsi, imei, stationid, mcc, mnc, lactac, cieci, lon, lat, starttime, endtime, roomid, ispid, gathertime, `hour`, nearstation, pdate, sourcetype, mac, accountname, uid, hardwaresignature) select concat(toString(toInt64(rand() * 99)), '@5'), toInt64(rand() * 99), 5, toInt64(rand()), toInt64(rand()), hex(rand64()), toInt64(rand()), toInt64(rand()), substring(toString(rand64()), 1, 6) as lactac, substring(toString(rand64()), 1, 5) as cieci, 0, 0, (1717836127 + toInt32(toInt32(rand()) /1000)) as starttime, (starttime + 100) as endtime, 1001,1 , (endtime + 100) gathertime, substring(toString(rand64()), 1, 1) as hour, [], '2024-07-07', 5, hex(rand64()) as mac, hex(rand64()) as accountname, '', '' from numbers(422612048);

INSERT INTO ff.trace_curr_local (identitycode, account, accounttype, imsi, imei, stationid, mcc, mnc, lactac, cieci, lon, lat, starttime, endtime, roomid, ispid, gathertime, `hour`, nearstation, pdate, sourcetype, mac, accountname, uid, hardwaresignature)
select concat(toString(toInt64(rand() * 99)), '@4'), toInt64(rand() * 99), 4, toInt64(rand()), toInt64(rand()), select hex(rand64()), toInt64(rand()), toInt64(rand()), substring(toString(rand64()), 1, 6) as lactac, substring(toString(rand64()), 1, 5) as cieci, 0, 0, (1717836127 + toInt32(toInt32(rand()) /1000)) as starttime, (firsttime + 100) as endtime, 1001,1 , (endtime + 100) gathertime, substring(toString(rand64()), 1, 1) as as hour, [2], pdate, 4, mac, accountname, uid, hardwaresignature from ff.trace_curr_local;

INSERT INTO ff.trace_curr_local (identitycode, account, accounttype, imsi, imei, stationid, mcc, mnc, lactac, cieci, lon, lat, starttime, endtime, roomid, ispid, gathertime, `hour`, nearstation, pdate, sourcetype, mac, accountname, uid, hardwaresignature) select concat(toString(toInt64(rand() * 99)), '@5'), toInt64(rand() * 99), 5, toInt64(rand()), toInt64(rand()), stationid, toInt64(rand()), toInt64(rand()), lactac, cieci, lon, lat, starttime, endtime, roomid, ispid, gathertime, `hour`, nearstation, pdate, 5, mac, accountname, uid, hardwaresignature from ff.trace_curr_local

 

--2. 打入identity_base_info_local
INSERT INTO ff.identity_base_info_local (id, account, accounttype, `domain`, firsttime, identitycode, isipv4, lastip4, lastip6, lasttime, password, server, sourcetype) select toInt64(rand()), toInt64(rand()), [1], hex(rand64()), (1717836127 + toInt32(toInt32(rand()) /1000)) as firsttime, hex(rand64()), 1, '2.2.2.2', '', (firsttime + 100), hex(rand64()), '', [3] from numbers(190413375);

 

---3. 打入relationship_agg_result
INSERT INTO ff.relationship_agg_result_local (relacode, accountfrom, fromtype, accountto, totype, relatype, endtime, count) select concat(toString(accountfrom), '&@1005', '&@',toString(accountto), '&@1&@1' ), toInt64(rand()) as accountfrom, 1005, toInt64(rand()) as accountto, 1, 1, (1717836127 + toInt32(toInt32(rand()) /1000)) as endtime, substring(toString(rand64()), 1, 1) as count from numbers(162525981);

 

---4 打入url_fullaudit_monitor_local
INSERT INTO ff.url_fullaudit_monitor_local(logid, srcip4, srcip6, srcport, destip4, destip6, destport, isipv4, gathertime, taskid, probeid, ispid, roomid, protocolid, reftaskid, url, referer, cookie, useragent, requestheaders, responseheaders, isgtppack, username, usertype, imsi, imei, nesrcip4, nesrcip6, nedestip4, nedestip6, publicsrcip4, publicsrcip6, publicsrcport, nettype, lactac, cieci, pdate, srcip, destip, nesrcip, nedestip, publicsrcip, mcc, mnc, terminalmac, terminalequipmentid, terminalimsi, terminaltype, terminalmodel, terminalostype, terminalosversion, browsetype, browseversion, hardwaresignaturetype, hardwaresignature, longitude, latitude, `action`, accountname, accountpwd, nickname, realname, address, phonenumber, uid) select substring(toString(rand64()), 1, 20) , (168587139 + toInt32(toInt32(rand()) /10000)), '', substring(toString(rand64()), 1, 6) , (168587139 + toInt32(toInt32(rand()) /10000)), '', substring(toString(rand64()), 1, 5), 1, 1718899196, substring(toString(rand64()), 1, 4) , '001-001-IAS-011', 1, substring(toString(rand64()), 1, 2) , substring(toString(rand64()), 1, 2) , 0, 'bbaad.life', '', '', '', '', '', 0, hex(rand64()) as username , 0, '', '', 0, '', 0, '', 0, '', 0, 0, 0, 0, '2024-07-04', '103.112.111.121', '54.238.149.135', '0.0.0.0', '0.0.0.0', '0.0.0.0', 0, 0, '', '', '', 0, '', 0, '', 0, '', 0, '', '', '', 0, '', '', '', '', '', toString(toInt64(rand() * 99)), '' from numbers(700000000);

 

磁盘使用情况
SELECT formatReadableSize(sum(bytes_on_disk)) AS total_disk_usage FROM system.parts;

SELECT round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) AS compression_ratio FROM system.parts;