clickhouse

1 時(shí)間字段類型

• 建表時(shí)能用數(shù)值型或日期時(shí)間型表示的字段就不要用字符串，全String 類型在以Hive(hbase)為中心的數(shù)倉(cāng)建設(shè)中常見扁远，但 ClickHouse 環(huán)境不應(yīng)受此影響敬辣。雖然 ClickHouse 底層將DateTime 存儲(chǔ)為時(shí)間戳Long類型称勋，但不建議存儲(chǔ)Long 類型耀鸦，因?yàn)镈ateTime 不需要經(jīng)過函數(shù)轉(zhuǎn)換處理捶枢，執(zhí)行效率高稀拐、可讀性好火邓。

2 Nullable類型

• 官方已經(jīng)指出Nullable 類型幾乎總是會(huì)拖累性能，因?yàn)榇鎯?chǔ)Nullable 列時(shí)需要?jiǎng)?chuàng)建一個(gè)額外的文件來存儲(chǔ) NULL 的標(biāo)記德撬，并且 Nullable 列無法被索引铲咨。因此除非極特殊情況，應(yīng)直接使用字段默認(rèn)值表示空蜓洪，或者自行指定一個(gè)在業(yè)務(wù)中無意義的值（例如用-1 表示沒有商品ID）

3 分區(qū)與索引

• 一般選擇按天分區(qū)纤勒，也可以指定為 Tuple()，以單表一億數(shù)據(jù)為例隆檀，分區(qū)大小控制在 10-30 個(gè)為最佳摇天。

• 必須指定索引列粹湃，ClickHouse 中的索引列即排序列，通過 order by 指定泉坐，一般在查詢條件中經(jīng)常被用來充當(dāng)篩選條件的屬性被納入進(jìn)來为鳄；可以是單一維度，也可以是組合維度的索引腕让；通常需要滿足高級(jí)列在前济赎、查詢頻率大的在前原則；還有基數(shù)特別大的不適合做索引列记某，如用戶表的 userid 字段司训；通常篩選后的數(shù)據(jù)滿足在百萬以內(nèi)為最佳。

4 數(shù)據(jù)TTL

• 如果表中不是必須保留全量歷史數(shù)據(jù)液南，建議指定 TTL（生存時(shí)間值）壳猜，可以免去手動(dòng)過期歷史數(shù)據(jù)的麻煩，TTL 也可以通過 alter table 語句隨時(shí)修改滑凉。

5 寫入與刪除優(yōu)化

• 盡量不要執(zhí)行單條或小批量刪除和插入操作统扳，這樣會(huì)產(chǎn)生小分區(qū)文件，給后臺(tái)Merge 任務(wù)帶來巨大壓力

• 不要一次寫入太多分區(qū)畅姊，或數(shù)據(jù)寫入太快咒钟，數(shù)據(jù)寫入太快會(huì)導(dǎo)致 Merge 速度跟不上而報(bào)錯(cuò)，一般建議每秒鐘發(fā)起 2-3 次寫入操作若未，每次操作寫入 2w~5w 條數(shù)據(jù)（依服務(wù)器性能而定）

• Too many parts錯(cuò)誤：in_memory_parts_enable_wal 默認(rèn)為 true（開啟wal預(yù)寫日志）

• Memory limit錯(cuò)誤：增加內(nèi)存設(shè)置max_memory_usage 朱嘴，或者內(nèi)存不充裕建議將超出部分內(nèi)容分配到系統(tǒng)硬盤上max_bytes_before_external_group_by、max_bytes_before_external_sort

6 謂詞下推：(各個(gè)版本性能損失有差異)

• 當(dāng) group by 有 having 子句粗合，但是沒有 with cube萍嬉、with rollup 或者 with totals 修飾的時(shí)候，having 過濾會(huì)下推到 where 提前過濾隙疚。例如下面的查詢壤追，HAVING name 變成了 WHERE name，在 group by 之前過濾

7 Prewhere替代where

• 當(dāng)查詢列明顯多于篩選列時(shí)使用 Prewhere 可十倍提升查詢性能供屉，Prewhere 會(huì)自動(dòng)優(yōu)化執(zhí)行過濾階段的數(shù)據(jù)讀取方式行冰，降低 io 操作
• 默認(rèn)：Prewhere自動(dòng)打開，但是某些場(chǎng)景即使開啟優(yōu)化伶丐，也不會(huì)自動(dòng)轉(zhuǎn)換成 prewhere悼做，需要手動(dòng)指定 prewhere：
  1）使用常量表達(dá)式
  2）使用默認(rèn)值為 alias 類型的字段
  3）包含了 arrayJOIN，globalIn撵割，globalNotIn 或者 indexHint 的查詢
  4）select 查詢的列字段和 where 的謂詞相同
  5）使用了主鍵字段

8 類關(guān)系型數(shù)據(jù)庫(kù)要求

• 千萬以上數(shù)據(jù)集進(jìn)行 order by 查詢時(shí)需要搭配 where 條件和 limit 語句一起使用（mysql贿堰、oracle也有同樣的要求）

9 避免構(gòu)建虛擬列

• 其實(shí)就是mysql、oracle要求使用函數(shù)的列不會(huì)命中索引

SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;

10 uniqCombined 替代distinct

• 性能可提升10 倍以上啡彬，uniqCombined 底層采用類似HyperLogLog 算法實(shí)現(xiàn)羹与，能接收2%左右的數(shù)據(jù)誤差故硅，可直接使用這種去重方式提升查詢性能。Count(distinct )會(huì)使用uniqExact精確去重纵搁。不建議在千萬級(jí)不同數(shù)據(jù)上執(zhí)行 distinct 去重查詢吃衅，改為近似去重uniqCombined

反例：select count(distinct rand()) from hits_v1;

正例：SELECT uniqCombined(rand()) from datasets.hits_v1

11 用IN代替JOIN

• mysql、oracle exist輪訓(xùn)外表腾誉，子查詢是大表徘层；in用于子查詢是小表
• 當(dāng)多表聯(lián)查時(shí)，查詢的數(shù)據(jù)僅從其中一張表出時(shí)利职，可考慮用 IN 操作而不是 JOIN

12 大小表JOIN（mysql趣效、oracle通用要求）

• 多表 join 時(shí)要滿足小表在右的原則，右表關(guān)聯(lián)時(shí)被加載到內(nèi)存中與左表進(jìn)行比較猪贪，ClickHouse 中無論是 Left join 跷敬、Right join 還是 Inner join 永遠(yuǎn)都是拿著右表中的每一條記錄到左表中查找該記錄是否存在，所以右表必須是小表热押。

13 分布式表使用GLOBAL

• 兩張分布式表上的 IN 和 JOIN 之前必須加上 GLOBAL 關(guān)鍵字西傀，右表只會(huì)在接收查詢請(qǐng)求的那個(gè)節(jié)點(diǎn)查詢一次，并將其分發(fā)到其他節(jié)點(diǎn)上桶癣。如果不加 GLOBAL 關(guān)鍵字的話拥褂，每個(gè)節(jié)點(diǎn)都會(huì)單獨(dú)發(fā)起一次對(duì)右表的查詢，而右表又是分布式表牙寞，就導(dǎo)致右表一共會(huì)被查詢 N2次（N是該分布式表的分片數(shù)量）饺鹃，這就是查詢放大，會(huì)帶來很大開銷碎税。

14 數(shù)據(jù)一致性

• 即便對(duì)數(shù)據(jù)一致性支持最好的 Mergetree尤慰，也只是保證最終一致性，ReplacingMergeTree去重時(shí)機(jī)不確定性
  1）在查詢語句后增加 FINAL 修飾符雷蹂，這樣在查詢的過程中將會(huì)執(zhí)行Merge 的特殊邏輯（例如數(shù)據(jù)去重，預(yù)聚合等）
  2）在 v20.5.2.7-stable 版本及以后杯道，F(xiàn)INAL 查詢支持多線程執(zhí)行匪煌，并且可以通過max_final_threads參數(shù)控制單個(gè)查詢的線程數(shù)。

explain pipeline select * from visits_v1 final WHERE StartDate = '2014-03-17' limit 100 settings max_final_threads = 2;

(Expression)

ExpressionTransform × 2

(SettingQuotaAndLimits)

(Limit)

Limit 2 → 2

(ReadFromMergeTree)

ExpressionTransform × 2

CollapsingSortedTransform × 2

Copy 1 → 2

AddingSelector

ExpressionTransform

MergeTree 0 → 1

從 CollapsingSortedTransform 這一步開始已經(jīng)是多線程執(zhí)行党巾，但是讀取 part 部分的動(dòng)作還是串行

15 物化視圖：

1）定義：

• 普通視圖不保存數(shù)據(jù)萎庭，保存的僅僅是查詢語句，查詢的時(shí)候還是從原表讀取數(shù)據(jù)齿拂，可以將普通視圖理解為是個(gè)子查詢驳规。物化視圖則是把查詢的結(jié)果根據(jù)相應(yīng)的引擎存入到了磁盤或內(nèi)存中，對(duì)數(shù)據(jù)重新進(jìn)行了組織署海，你可以理解物化視圖是完全的一張新表吗购。

2）優(yōu)缺點(diǎn)

• 優(yōu)點(diǎn)：查詢速度快医男，要是把物化視圖這些規(guī)則全部寫好，它比原數(shù)據(jù)查詢快了很多捻勉，總的行數(shù)少了镀梭，因?yàn)槎碱A(yù)計(jì)算好了。

• 缺點(diǎn)：它的本質(zhì)是一個(gè)流式數(shù)據(jù)的使用場(chǎng)景踱启，是累加式的技術(shù)报账，所以要用歷史數(shù)據(jù)做去重、去核這樣的分析埠偿，在物化視圖里面是不太好用的透罢。在某些場(chǎng)景的使用也是有限的。而且如果一張表加了好多物化視圖冠蒋，在寫這張表的時(shí)候羽圃，就會(huì)消耗很多機(jī)器的資源，比如數(shù)據(jù)帶寬占滿浊服、存儲(chǔ)一下子增加了很多统屈。

3）物化視圖實(shí)戰(zhàn)

#建表語句

CREATE TABLE hits_test(

EventDate Date,

CounterID UInt32,

UserID UInt64,

URL String,

Income UInt8

)ENGINE = MergeTree()

PARTITION BY toYYYYMM(EventDate)

ORDER BY (CounterID, EventDate, intHash32(UserID))

SAMPLE BY intHash32(UserID)

SETTINGS index_granularity = 8192

#導(dǎo)入數(shù)據(jù)

INSERT INTO hits_test

SELECT EventDate,CounterID,UserID,URL,Income FROM hits_v1 limit 10000;

#創(chuàng)建物化視圖
CREATE MATERIALIZED VIEW hits_mv

ENGINE=SummingMergeTree

PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID))

AS SELECT UserID,EventDate,count(URL) as ClickCount,sum(Income) AS IncomeSum FROM hits_test

WHERE EventDate >= '2014-03-20' #設(shè)置更新點(diǎn),該時(shí)間點(diǎn)之前的數(shù)據(jù)可以另外通過insert into select …… 的方式進(jìn)行插入 

GROUP BY UserID,EventDate;

#或者可以用下列語法，表 A 可以是一張 mergetree 表

CREATE MATERIALIZED VIEW 物化視圖名 TO 表 A AS SELECT FROM 表 B;

#不建議添加 populate 關(guān)鍵字進(jìn)行全量更新

#導(dǎo)入增量數(shù)據(jù)

INSERT INTO hits_test

SELECT EventDate,CounterID,UserID,URL,Income FROM hits_v1 WHERE EventDate >= '2014-03-23' limit 10;

#查詢物化視圖

SELECT * FROM hits_mv;

#導(dǎo)入歷史數(shù)據(jù)

INSERT INTO hits_mv

SELECT UserID,EventDate,count(URL) as ClickCount, sum(Income) AS IncomeSum FROM hits_test

WHERE EventDate = '2014-03-19' GROUP BY UserID,EventDate