clickhouse在易企秀數(shù)據(jù)倉庫項目中已投入使用兩年汽纤，主要為內(nèi)部用戶提供快速查詢和多維分析的能力动猬；希望你在業(yè)務(wù)當(dāng)中遇到的性能問題，在這里都能得到解決

Clickhouse堪稱OLAP領(lǐng)域的黑馬，最近發(fā)布的幾個版本在多表關(guān)聯(lián)分析上也有了極大的性能提升绅项，尤其是還引入了MaterializeMySQL Database Engine做到了實時對齊業(yè)務(wù)線mysql中的數(shù)據(jù)。

表優(yōu)化

數(shù)據(jù)類型

• 建表時能用數(shù)值型或日期時間型表示的字段比肄，就不要用字符串——全String類型在以Hive為中心的數(shù)倉建設(shè)中常見快耿，但CK環(huán)境不應(yīng)受此影響。

• 雖然clickhouse底層將DateTime存儲為時間戳Long類型芳绩，但不建議直接存儲Long類型掀亥，因為DateTime不需要經(jīng)過函數(shù)轉(zhuǎn)換處理，執(zhí)行效率高妥色、可讀性好搪花。

• 官方已經(jīng)指出Nullable類型幾乎總是會拖累性能，因為存儲Nullable列時需要創(chuàng)建一個額外的文件來存儲NULL的標(biāo)記嘹害，并且Nullable列無法被索引撮竿。因此除非極特殊情況，應(yīng)直接使用字段默認(rèn)值表示空笔呀，或者自行指定一個在業(yè)務(wù)中無意義的值（例如用-1表示沒有商品ID）幢踏。

• 數(shù)值類型分組最快，在新版本中ck會對string類型進(jìn)行一次hash映射再分組

分區(qū)和索引

• 分區(qū)粒度根據(jù)業(yè)務(wù)特點決定许师，不宜過粗或過細(xì)房蝉。一般選擇按天分區(qū)，也可指定為tuple()枯跑；以單表1億數(shù)據(jù)為例惨驶，分區(qū)大小控制在10-30個為最佳。

PARTITION BY tuple() 

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

表參數(shù)

• index_granularity 是用來控制索引粒度的 默認(rèn)是8192，如非必須不建議調(diào)整。

• 如果表中不是必須保留全量歷史數(shù)據(jù)灼捂，建議指定TTL离例，可以免去手動過期歷史數(shù)據(jù)的麻煩。TTL也可以通過ALTER TABLE語句隨時修改悉稠。

查詢優(yōu)化

單表查詢

• 使用prewhere替代where關(guān)鍵字宫蛆；當(dāng)查詢列明顯多于篩選列時使用prewhere可十倍提升查詢性能

# prewhere 會自動優(yōu)化執(zhí)行過濾階段的數(shù)據(jù)讀取方式，降低io操作
select * from work_basic_model  where product='tracker_view' and ( id='eDf8fZky' or code='eDf8fZky' ) 
#替換where關(guān)鍵字
select * from work_basic_model  prewhere product='tracker_view' and ( id='eDf8fZky' or code='eDf8fZky' ) 

• 數(shù)據(jù)采樣的猛，通過采用運(yùn)算可極大提升數(shù)據(jù)分析的性能

SELECT
    Title,
    count() * 10 AS PageViews
FROM hits_distributed
SAMPLE 0.1   #代表采樣10%的數(shù)據(jù)耀盗，也可以是具體的條數(shù)
WHERE
    CounterID = 34
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

采樣修飾符只有在mergetree engine表中才有效，且在創(chuàng)建表時需要指定采樣策略卦尊；

• 數(shù)據(jù)量太大時應(yīng)避免使用select * 操作叛拷，查詢的性能會與查詢的字段大小和數(shù)量成線性變換；字段越少猫牡，消耗的io資源就越少胡诗，性能就會越高。

• 千萬以上數(shù)據(jù)集進(jìn)行order by查詢時需要搭配where條件和limit語句一起使用

• 如非必須不要在結(jié)果集上構(gòu)建虛擬列淌友，虛擬列非常消耗資源浪費(fèi)性能，可以考慮在前端進(jìn)行處理骇陈，或者在表中構(gòu)造實際字段進(jìn)行額外存儲震庭。

select id ,pv, uv , pv/uv rate 

• 使用 uniqCombined 替代 distinct 性能可提升10倍以上，uniqCombined 底層采用類似HyperLogLog算法實現(xiàn)你雌，如能接收2%左右的數(shù)據(jù)誤差器联，可直接使用這種去重方式提升查詢性能。

• 對于一些確定的數(shù)據(jù)模型婿崭，可將統(tǒng)計指標(biāo)通過物化視圖的方式進(jìn)行構(gòu)建拨拓，這樣可避免數(shù)據(jù)查詢時重復(fù)計算的過程；物化視圖會在有新數(shù)據(jù)插入時進(jìn)行更新氓栈。

# 通過物化視圖提前預(yù)計算用戶下載量
CREATE MATERIALIZED VIEW download_hour_mv
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(hour) ORDER BY (userid, hour)
AS SELECT
  toStartOfHour(when) AS hour,
  userid,
  count() as downloads,
  sum(bytes) AS bytes
FROM download WHERE when >= toDateTime('2020-10-01 00:00:00')  #設(shè)置更新點渣磷，該時間點之前的數(shù)據(jù)可以通過insert into select的方式進(jìn)行插入
GROUP BY userid, hour

## 或者
CREATE MATERIALIZED VIEW db.table_MV TO db.table_new  ## table_new 可以是一張mergetree表
AS SELECT * FROM db.table_old; 

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

多表關(guān)聯(lián)

• 當(dāng)多表聯(lián)查時，查詢的數(shù)據(jù)僅從其中一張表出時授瘦，可考慮使用IN操作而不是JOIN醋界。

select a.* from a where a.uid in (select uid from b)
# 不要寫成
select a.* from a left join b on a.uid=b.uid

• 多表Join時要滿足小表在右的原則，右表關(guān)聯(lián)時被加載到內(nèi)存中與左表進(jìn)行比較提完。

• clickhouse在join查詢時不會主動發(fā)起謂詞下推的操作形纺，需要每個子查詢提前完成過濾操作；需要注意的是徒欣，是否主動執(zhí)行謂詞下推逐样，對性能影響差別很大【新版本中已不再存在此問題，但是需要注意的是謂詞位置的不同依然有性能的差異】。

• 將一些需要關(guān)聯(lián)分析的業(yè)務(wù)創(chuàng)建成字典表進(jìn)行join操作脂新，前提是字典表不易太大秽澳，因為字典表會常駐內(nèi)存。

ENGINE = Dictionary(dict_name)
或者
create database db_dic ENGINE = Dictionary

寫入和刪除優(yōu)化

• 盡量不要執(zhí)行單條或小批量刪除和插入操作戏羽，這樣會產(chǎn)生大量小分區(qū)文件担神，給后臺merge任務(wù)帶來巨大壓力。
• 不要一次寫入太多分區(qū)始花，或數(shù)據(jù)寫入太快妄讯，數(shù)據(jù)寫入太快會導(dǎo)致merge速度跟不上而報錯；一般建議每秒中發(fā)起2-3次寫入操作酷宵，每次操作寫入2w-5w條數(shù)據(jù)亥贸。

運(yùn)維相關(guān)

配置

存儲

clickhouse不支持設(shè)置多數(shù)據(jù)目錄朦前，為了提升數(shù)據(jù)io性能介杆，可以掛載虛擬券組，一個券組綁定多塊物理磁盤提升讀寫性能韭寸；多數(shù)查詢場景SSD盤會比普通機(jī)械硬盤快2-3倍春哨。

數(shù)據(jù)同步

新版clickhouse提供了一個實驗性的功能，那就是我們可以將clickhouse偽裝成mysql的一個備庫去實時對齊mysql中的數(shù)據(jù)恩伺，當(dāng)mysql庫表數(shù)據(jù)發(fā)生變化時會實時同步到clickhouse中赴背；這樣就省掉了單獨(dú)維護(hù)實時spark/flink任務(wù)讀取kafka數(shù)據(jù)再存入clickhouse的環(huán)節(jié)，大大降低了運(yùn)維成本提升了效率莫其。

CREATE DATABASE ckdb ENGINE = MaterializeMySQL('172.17.0.2:3306', 'ckdb', 'root', '123');

查詢?nèi)蹟?/h3>

為了避免因個別慢查詢引起的服務(wù)雪崩問題癞尚，除了可以為單個查詢設(shè)置超時以外，還可以配置周期熔斷乱陡；在一個查詢周期內(nèi)浇揩，如果用戶頻繁進(jìn)行慢查詢操作超出規(guī)定閾值后將無法繼續(xù)進(jìn)行查詢操作：

熔斷策略

綁定用戶

clickhouse權(quán)限管理與資源隔離
clickhouse高級功能上線之mysql實時數(shù)據(jù)同步

clickhouse如何構(gòu)建復(fù)雜數(shù)據(jù)模型
clickhouse sql規(guī)范