clickhouse 21.x生產(chǎn)實踐優(yōu)化

clickhouse
1 時間字段類型
  • 建表時能用數(shù)值型或日期時間型表示的字段就不要用字符串，全String 類型在以Hive(hbase)為中心的數(shù)倉建設(shè)中常見，但 ClickHouse 環(huán)境不應(yīng)受此影響。雖然 ClickHouse 底層將DateTime 存儲為時間戳Long類型，但不建議存儲Long 類型，因為DateTime 不需要經(jīng)過函數(shù)轉(zhuǎn)換處理，執(zhí)行效率高、可讀性好。
2 Nullable類型
  • 官方已經(jīng)指出Nullable 類型幾乎總是會拖累性能，因為存儲Nullable 列時需要創(chuàng)建一個額外的文件來存儲 NULL 的標記，并且 Nullable 列無法被索引。因此除非極特殊情況，應(yīng)直接使用字段默認值表示空，或者自行指定一個在業(yè)務(wù)中無意義的值（例如用-1 表示沒有商品ID）
3 分區(qū)與索引

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

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

4 數(shù)據(jù)TTL
  • 如果表中不是必須保留全量歷史數(shù)據(jù)，建議指定 TTL（生存時間值），可以免去手動過期歷史數(shù)據(jù)的麻煩，TTL 也可以通過 alter table 語句隨時修改。
5 寫入與刪除優(yōu)化

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

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

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

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

6 謂詞下推：(各個版本性能損失有差異)
  • 當 group by 有 having 子句，但是沒有 with cube、with rollup 或者 with totals 修飾的時候，having 過濾會下推到 where 提前過濾。例如下面的查詢，HAVING name 變成了 WHERE name，在 group by 之前過濾
7 Prewhere替代where
  • 當查詢列明顯多于篩選列時使用 Prewhere 可十倍提升查詢性能，Prewhere 會自動優(yōu)化執(zhí)行過濾階段的數(shù)據(jù)讀取方式，降低 io 操作
  • 默認：Prewhere自動打開，但是某些場景即使開啟優(yōu)化，也不會自動轉(zhuǎn)換成 prewhere，需要手動指定 prewhere：
    1）使用常量表達式
    2）使用默認值為 alias 類型的字段
    3）包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查詢
    4）select 查詢的列字段和 where 的謂詞相同
    5）使用了主鍵字段
8 類關(guān)系型數(shù)據(jù)庫要求
  • 千萬以上數(shù)據(jù)集進行 order by 查詢時需要搭配 where 條件和 limit 語句一起使用（mysql、oracle也有同樣的要求）
9 避免構(gòu)建虛擬列
  • 其實就是mysql、oracle要求使用函數(shù)的列不會命中索引
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;
10 uniqCombined 替代distinct
  • 性能可提升10 倍以上，uniqCombined 底層采用類似HyperLogLog 算法實現(xiàn)，能接收2%左右的數(shù)據(jù)誤差，可直接使用這種去重方式提升查詢性能。Count(distinct )會使用uniqExact精確去重。不建議在千萬級不同數(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用于子查詢是小表
  • 當多表聯(lián)查時，查詢的數(shù)據(jù)僅從其中一張表出時，可考慮用 IN 操作而不是 JOIN
12 大小表JOIN（mysql、oracle通用要求）
  • 多表 join 時要滿足小表在右的原則，右表關(guān)聯(lián)時被加載到內(nèi)存中與左表進行比較，ClickHouse 中無論是 Left join 、Right join 還是 Inner join 永遠都是拿著右表中的每一條記錄到左表中查找該記錄是否存在，所以右表必須是小表。
13 分布式表使用GLOBAL
  • 兩張分布式表上的 IN 和 JOIN 之前必須加上 GLOBAL 關(guān)鍵字，右表只會在接收查詢請求的那個節(jié)點查詢一次，并將其分發(fā)到其他節(jié)點上。如果不加 GLOBAL 關(guān)鍵字的話，每個節(jié)點都會單獨發(fā)起一次對右表的查詢，而右表又是分布式表，就導(dǎo)致右表一共會被查詢 N2次（N是該分布式表的分片數(shù)量），這就是查詢放大，會帶來很大開銷。
14 數(shù)據(jù)一致性
  • 即便對數(shù)據(jù)一致性支持最好的 Mergetree，也只是保證最終一致性，ReplacingMergeTree去重時機不確定性
    1）在查詢語句后增加 FINAL 修飾符，這樣在查詢的過程中將會執(zhí)行Merge 的特殊邏輯（例如數(shù)據(jù)去重，預(yù)聚合等）
    2）在 v20.5.2.7-stable 版本及以后，F(xiàn)INAL 查詢支持多線程執(zhí)行，并且可以通過max_final_threads參數(shù)控制單個查詢的線程數(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 部分的動作還是串行
15 物化視圖：
1）定義：
  • 普通視圖不保存數(shù)據(jù)，保存的僅僅是查詢語句，查詢的時候還是從原表讀取數(shù)據(jù)，可以將普通視圖理解為是個子查詢。物化視圖則是把查詢的結(jié)果根據(jù)相應(yīng)的引擎存入到了磁盤或內(nèi)存中，對數(shù)據(jù)重新進行了組織，你可以理解物化視圖是完全的一張新表。
2）優(yōu)缺點

  • 優(yōu)點：查詢速度快，要是把物化視圖這些規(guī)則全部寫好，它比原數(shù)據(jù)查詢快了很多，總的行數(shù)少了，因為都預(yù)計算好了。

  • 缺點：它的本質(zhì)是一個流式數(shù)據(jù)的使用場景，是累加式的技術(shù)，所以要用歷史數(shù)據(jù)做去重、去核這樣的分析，在物化視圖里面是不太好用的。在某些場景的使用也是有限的。而且如果一張表加了好多物化視圖，在寫這張表的時候，就會消耗很多機器的資源，比如數(shù)據(jù)帶寬占滿、存儲一下子增加了很多。

3）物化視圖實戰(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è)置更新點,該時間點之前的數(shù)據(jù)可以另外通過insert into select …… 的方式進行插入 

GROUP BY UserID,EventDate;

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

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

#不建議添加 populate 關(guā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
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容