大佬教程收集整理的这篇文章主要介绍了ClickHouse 高级(四)查询优化,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
#关闭 where 自动转 prewhere(默认情况下, where 条件会自动优化成 prewhere) set optimize_move_to_prewhere=0; # 使用 where SELEct WatchID, JavaEnable, title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID, CounterClass, OS, UserAgent, URL, Referer, URLDomain, RefererDomain, refresh, IsRobot, RefererCategories, URLCategories, URLRegions, RefererRegions, ResolutionWidth, ResolutionHeight, ResolutionDepth, FlashMajor, FlashMinor, FlashMinor2 from datasets.hits_v1 where UserID='3198390223272470366'; # 使用 prewhere 关键字 SELEct WatchID, JavaEnable, title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID, CounterClass, OS, UserAgent, URL, Referer, URLDomain, RefererDomain, refresh, IsRobot, RefererCategories, URLCategories, URLRegions, RefererRegions, ResolutionWidth, ResolutionHeight, ResolutionDepth, FlashMajor, FlashMinor, FlashMinor2 from datasets.hits_v1 prewhere UserID='3198390223272470366';
SELECT title,count(*) AS PageViews FROM hits_v1 SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数 WHERE CounterID =57 GROUP BY title ORDER BY PageViews DESC LIMIT 1000
反例: SELEct * from datasets.hits_v1; 正例: SELEct WatchID, JavaEnable, title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID from datasets.hits_v1;
SELEct WatchID, JavaEnable, title, GoodEvent, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID from datasets.hits_v1 where EventDate='2014-03-23';
#正例: SELECT UserID,Age FROM hits_v1 WHERE CounterID=57 ORDER BY Age DESC LIMIT 1000 #反例: SELECT UserID,Age FROM hits_v1 ORDER BY Age DESC
反例: SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1; 正例:拿到 Income 和 Age 后,考虑在前端进行处理,或者在表中构造实际字段进行额外存储 SELECT Income,Age FROM datasets.hits_v1;
反例: SELEct count(disTinct rand()) from hits_v1; 正例: SELECT uniqCombined(rand()) from datasets.hits_v1
#创建小表 CREATE TABLE visits_v2 ENGINE = CollapsingMergeTree(Sign) PARTITION BY toYYYymM(StartDatE) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) SETTinGS index_granularity = 8192 as SELEct * from visits_v1 limit 10000; #创建 join 结果表:避免控制台疯狂打印数据 CREATE TABLE hits_v2 ENGINE = MergeTree() PARTITION BY toYYYymM(EventDatE) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTinGS index_granularity = 8192 as SELEct * from hits_v1 where 1=0;
insert into hits_v2 SELEct a.* from hits_v1 a where a. CounterID in (SELEct CounterID from visits_v1); #反例:使用 join insert into table hits_v2 SELEct a.* from hits_v1 a left join visits_v1 b on a. CounterID=b. CounterID;
insert into table hits_v2 SELEct a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. CounterID;
insert into table hits_v2 SELEct a.* from visits_v2 b left join hits_v1 a on a. CounterID=b. CounterID;
Explain syntax SELEct a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. CounterID having a.EventDate = '2014-03-17'; Explain syntax SELEct a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. CounterID having b.StartDate = '2014-03-17'; insert into hits_v2 SELEct a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. CounterID where a.EventDate = '2014-03-17'; insert into hits_v2 SELEct a.* from ( SELEct * from hits_v1 where EventDate = '2014-03-17' ) a left join visits_v2 b on a. CounterID=b. CounterID;
以上是大佬教程为你收集整理的ClickHouse 高级(四)查询优化全部内容,希望文章能够帮你解决ClickHouse 高级(四)查询优化所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。