文章目錄
ClickHouse優化
一、表優化
1、日期字段避免使用String存儲
2、Nullable值處理
3、分區和索引
4、建表指定TTL
二、寫入查詢優化
1、避免小批量資料寫入
2、count優化
3、避免使用select *
4、避免建構虛拟列
5、使用uniqCombined代替count(distinct)
6 、使用物化視圖
7、Join關聯相關
8、分布式表使用global
9、避免使用final
ClickHouse優化
一、表優化
1、日期字段避免使用String存儲
在Hive中對于日期資料我們經常使用String類型存儲，但是在ClickHouse中建表時針對日期類型資料存儲建議使用日期類型存儲，不使用String類型存儲，因為在使用到日期時日期類型可以直接處理，String類型的日期資料還需要使用函數進行處理，執行效率低。例如：
select toDateTime('2021-12-31 17:22:23'),toTypeName(toDateTime('2021-12-31 17:22:23')) 
2、Nullable值處理
在ClickHouse表中資料存儲時，對于一些列盡量不使用Nullable類型存儲，因為此類型需要單獨建立額外的檔案來存儲NULL的标記并且Nullable類型列無法被索引，會拖累性能，在資料存儲時如果有空值時，我們可以選擇在業務中沒有意義的值來替代NULL值。
3、分區和索引
ClickHouse中一般選擇按天分區，可以指定tuple()指定多個列為組合分區。如果不按天分區，每個分區資料量控制在800~1000萬為宜。
建表時通過order by 指定索引列，可以指定tuple(),指定多個列為索引列，指定索引列時最好滿足高基列在前、查詢頻率大的列在前的原則。基數過大的列不适合作為索引列，因為如果某列基數特别大，這種情況有索引和沒索引效果一樣。
4、建表指定TTL
如果表不是必須儲存全量曆史資料，建議指定TTL，以免去手動清除過期資料的麻煩。
二、寫入查詢優化
1、避免小批量資料寫入
盡量避免單條和小批量插入、删除操作，會産生大量小分區檔案，給背景Merge帶來壓力。
2、count優化
在ClickHouse中向查詢資料總條數時，使用count() 代替count(列)查詢，因為使用count()查詢會自動尋找資料目錄中的“count.txt”檔案讀取資料總條目，性能極高。如果使用count(列)相當于掃描全表讀取總資料量。
node1 :) explain plan select count() from person_info; node1 :) explain plan select count(name) from person_info; 3、避免使用select *
資料量太大時應避免使用select * 查詢，這種查詢會将表中所有字段都查詢出來，IO消耗大，查詢字段越少消耗的IO資源就越少，性能就會越高。
4、避免建構虛拟列
如果非必要盡量避免在查詢時建構虛拟列，虛拟列非常消耗資源，造成性能浪費，可以考慮在前端進行處理或者在表中建構實際的列進行額外存儲。
#避免使用虛拟列 ，以下count1/count2就是虛拟列
select id,name,count1,count2,count1/count2 as new_col from tbl; 5、使用uniqCombined代替count(distinct)
使用uniqCombined替代distinct性能可提升10倍以上，uniqCombined 底層采用類似HyperLogLog算法實作，如能接收2%左右的資料誤差，可直接使用這種去重方式提升查詢性能。
node1 :) select count(distinct WatchID) from datasets.hits_v1; node1 :) select uniqCombined(WatchID) from datasets.hits_v1; 6 、使用物化視圖
對于一些确定的資料模型，可以将統計名額通過物化視圖的方式進行建構，這樣可避免資料查詢時重複計算的過程，同樣在後期也可以建構Projection投影來替代物化視圖。
7、Join關聯相關
當多表關聯查詢時，查詢的資料僅來源于一張表時，可考慮用IN代替JOIN，速度會更快。
node1 :) select count(distinct a.CounterID) as cnt from hits_v1 as a join visits_v1 as b on a.CounterID = b.CounterID node1 :) select count(distinct CounterID) as cnt from hits_v1 where CounterID in (select CounterID from visits_v1); 此外，多表關聯時，将小表放在右側，因為右表自動會被加載到記憶體中與左表進行關聯。
8、分布式表使用global
對分布式表使用join 或者 in時，ClickHouse會将目前SQL分發到各個ClickHouse節點上執行，例如有如下SQL:
select a.id,a.name,b.score from a join b on a.id = b.id 如果以上a表和b表都是分布式表，ClickHouse叢集有3個節點，那麼上面SQL會分發到ClickHouse所有節點執行，b表會在每個節點上收集其他節點對應b表資料并放在記憶體，這樣的話，每個ClickHouse節點都會從對應的3台節點上将b表資料進行彙集。
如果使用global關鍵字，執行如下SQL：
select a.id,a.name,b.score from a global join b on a.id = b.id 這樣執行SQL的話，相當于在目前寫SQL節點會将查詢得到b表所有資料，然後統一分發到其他ClickHouse各個節點上，然後每個節點在執行與a表關聯。這樣使用global就減少了叢集之間查詢次數。假設b表有N個分片分布在N個ClickHouse節點上，不使用global時，每個節點擷取b表全量資料需要執行N的平方次查詢，使用global時隻需要執行N次查詢即可。
是以在使用分布式表進行join或者in時，可以優先考慮使用global，使用用法如下：
select a.id,a.name,b.score from a global join b on a.id = b.id
select a.id,a.name from a global where a.id global in (select id from b) 9、避免使用final
ClickHouse中我們可以使用ReplacintMergeTree來對資料進行去重，這個引擎可以在資料主鍵相同時根據指定的字段保留一條資料，ReplacingMergeTree隻是在一定程度上解決了資料重複問題，由于自動分區合并機制在背景定時執行，是以并不能完全保障資料不重複。我們需要在查詢時在最後執行final關鍵字，final執行會導緻背景資料合并，查詢時如果有final效率将會極低，我們應當避免使用final查詢，那麼不使用final我們可以通過自己寫SQL方式查詢出想要的資料，舉例如下：
#建立replacingMergeTree 表t_replacing_mt
create table t_replacing_mt(
id UInt8,
name String,
age UInt8
) engine = ReplacingMergeTree(age)
order by id;
#向表中插入以下資料
insert into t_replacing_mt values (1,'張三',18),(2,'李四',19),(3,'王五',20); #繼續向表中插入如下資料
insert into t_replacing_mt values (1,'張三',20),(2,'李四',15); #通過final查詢最終結果
node1 :) select * from t_replacing_mt final; 下面我們不使用final，通過自己寫SQL方式現在查詢最終合并資料，操作如下：
#重新删除表t_replacing_mt，重建、并加載如下資料
drop table t_replacing_mt;
create table t_replacing_mt(
id UInt8,
name String,
age UInt8
) engine = ReplacingMergeTree(age)
order by id;
insert into t_replacing_mt values (1,'張三',18),(2,'李四',19),(3,'王五',20);
#繼續向表中插入如下資料
insert into t_replacing_mt values (1,'張三',20),(2,'李四',15);
#自己寫SQL方式實作查詢去重後的資料，這樣避免使用final查詢，效率提高
SELECT
id,
argMax(name, age) AS namex,
max(age) AS agex
FROM t_replacing_mt
GROUP BY id - 📢歡迎點贊 👍 收藏 ⭐留言 📝 如有錯誤敬請指正！
- 📢本文由 Lansonli 原創
- 📢停下休息的時候不要忘了别人還在奔跑，希望大家抓緊時間學習，全力奔赴更美好的生活✨