關(guān)于ClickHouse的基礎(chǔ)概念這里就不做太多的贅述了，ClickHouse官網(wǎng)都有很詳細(xì)說明。結(jié)合供應(yīng)鏈數(shù)字化團隊在使用ClickHouse時總結(jié)出的一些注意事項，尤其在命名方面要求研發(fā)嚴(yán)格遵守約定，對日常運維有很大的幫助，也希望對讀者有啟發(fā)。

目前供應(yīng)鏈數(shù)字化ck集群用來存儲實時數(shù)據(jù)，先通過下面這張圖表了解下ClickHouse數(shù)據(jù)來源。

圖中標(biāo)注1和2的位置是供應(yīng)鏈數(shù)字化研發(fā)在開發(fā)業(yè)務(wù)功能時改動量比較多的部分，隨著需求變多，DTS任務(wù)和數(shù)據(jù)庫表也越來越多。 通過定義研發(fā)使用約定，使我們的DTS任務(wù)、表、表字段看起來很整潔。

有哪些好處呢？

1、根據(jù)ck表名快速找到對應(yīng)的DTS任務(wù)及消費jdq topic / 當(dāng)然通過jdq也可快速找到對應(yīng)的ck表（對不了解業(yè)務(wù)的人幫助很大）。

2、通過ck表字段即可知道該字段來自哪個業(yè)務(wù)表哪個字段（字段數(shù)據(jù)不對，聯(lián)系業(yè)務(wù)值班先排查業(yè)務(wù)庫的字段是否正確）。

3、快速統(tǒng)計到團隊 或 某個ck集群有多少DTS任務(wù)（運維時不會遺漏，一目了然）

一 建表約定

1.1 表命名約定

表命名要求： 1、本地表命名必須_local結(jié)尾 2、分布式表命名必須以_all結(jié)尾；

--創(chuàng)建本地表， 使用on cluster default 在每個節(jié)點上都創(chuàng)建一張本地表
CREATETABLE本地表名 on cluster 集群名稱
(
...
    ts                   DateTime Default now() COMMENT '時間搓',
    version              UInt64 COMMENT '版本號'
) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/test_local', 
    '{replica}',version)
[PARTITIONBYexpr]-- 數(shù)據(jù)分區(qū)規(guī)則
[ORDERBYexpr]-- 排序鍵
[SAMPLEBYexpr]-- 采樣鍵
[SETTINGS index_granularity = 8192, ...]-- 額外參數(shù)

參數(shù)解釋說明：
PARTITION BY toYYYYMM(tmsCreateTime) 按照月份分
ReplicatedReplacingMergeTree(參數(shù)1，參數(shù)2，參數(shù)3)
ORDER BY (參數(shù)1,參數(shù)2, ....); 參數(shù)1,參數(shù)2，參數(shù)3....等組成業(yè)務(wù)主鍵 

----創(chuàng)建分布式表
CREATE TABLE IF NOT EXISTS 庫名.分布式表名 on cluster default AS 本地表名 
ENGINE = Distributed (default,庫名,本地表名,sipHash64(分片鍵));

建表示例腳本：

CREATE TABLE reports_prestore_outbound_fulltrace_local on cluster `default`
(
    `sm_so_no` String COMMENT '訂單號',
    `sm_waybill_code` String COMMENT '青龍運單號',
    `sm_so_type` Int64 DEFAULT 0 COMMENT '訂單類型',
    `st_so_status` Int64 DEFAULT 0 COMMENT '訂單狀態(tài)(1初始、2定位完成、3定位失敗、5揀貨中、6出庫完成、7取消、8轉(zhuǎn)病單、9站點已收貨、10已妥投、11再投中、12已拒收)',
        ?
  	`st_delivery_time` DateTime COMMENT '妥投時間',
   	`st_redeliver_time` DateTime COMMENT '再投時間',
   	`st_reject_time` DateTime COMMENT '拒收時間',
   	`version` UInt64 COMMENT '更新版本號',
    `ts` DateTime DEFAULT now() COMMENT '時間戳'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/waybill_data_center/reports_prestore_outbound_fulltrace_local',
 '{replica}', version)
PARTITION BY toYYYYMM(so_create_time)
ORDER BY (sm_so_no,sm_waybill_code)
TTL so_create_time + toIntervalMonth(1)
SETTINGS index_granularity = 8192;

CREATE     TABLE     IF     NOT     EXISTS   `reports_prestore_outbound_fulltrace_all`     on     cluster     default     AS     `reports_prestore_outbound_fulltrace_local`
ENGINE     =     Distributed     (default,waybill_data_center,reports_prestore_outbound_fulltrace_local,sipHash64(sm_so_no));	

1.2 分區(qū)鍵設(shè)置

視情況大表按天分區(qū)，小表按月分區(qū)。分區(qū)鍵盡量使用date和datetime字段，避免string類型的分區(qū)鍵

分區(qū)粒度根據(jù)業(yè)務(wù)特點決定，不宜過粗或過細(xì)。 建議使用toYYYYMMDD()按天分區(qū)，如果數(shù)據(jù)量很少，100w左右，建議使用toYYYYMM()按月分區(qū)，過多的分區(qū)會占用大量的資源，會因為文件系統(tǒng)中的文件數(shù)量過多和需要打開的文件描述符過多，導(dǎo)致 SELECT 查詢效率不佳。

1.3 分片鍵設(shè)置

分布式表分片鍵需要采用hash函數(shù)，應(yīng)避免數(shù)據(jù)熱點，集中寫入某個分片；

分片鍵盡量使用表中區(qū)分粒度較細(xì)的字段,可以時多個字段的組合，如：id / order_no

1.4 排序鍵/主鍵設(shè)置

1、有序可以保證很高的壓縮比及加速查詢，寫入數(shù)據(jù)建議提前排序再寫入數(shù)據(jù)；

2、若未指定默認(rèn)為排序建，主鍵不保證唯一性。主鍵過長會拖慢寫入性能，并且會造成過多的內(nèi)存占用（主鍵常駐內(nèi)存）。

1.5 字段使用約定

1、字段類型能用數(shù)字型的字段盡量用數(shù)字型，避免使用string

2、表字段命名：${業(yè)務(wù)表縮寫}_${業(yè)務(wù)表字段}，如: 業(yè)務(wù)表ob_shipment_m 縮寫：osm， ck表字段則以osm_開頭。 注意：業(yè)務(wù)表的縮寫需要使用字典方式管理

3、日期字段建議默認(rèn)值為1970-01-01，時間字段默認(rèn)值為1970-01-01 08:00:00，使用到的地方排除掉默認(rèn)值即可

4、表中必須包含：ts（時間搓）、version（flink寫入jdq的時間，單位：）字段。

version— 版本列。類型為 UInt*, Date 或 DateTime。可選參數(shù)。 在數(shù)據(jù)合并的時候，ReplacingMergeTree 從所有具有相同排序鍵的行中選擇一行留下： 如果 version 列未指定，保留最后一條。 如果 version 列已指定，保留 version 值最大的版本

5、盡量不使用Nullable類型

可以非NUll的盡量非NUll并在代碼中賦予默認(rèn)值,數(shù)量字段默認(rèn)值為0，狀態(tài)字段默認(rèn)值建議使用有符號int時為-127、無符號int時為0，字符串建議默認(rèn)值為空字符串。

設(shè)置成Nullable對性能影響也沒有多大，可能是因為我們數(shù)據(jù)量比較小。不過官方已經(jīng)明確指出盡量不要使用Nullable類型，因為Nullable字段不能被索引，而且Nullable列除了有一個存儲正常值的文件，還會有一個額外的文件來存儲Null標(biāo)記。

1.6 新增列操作方式

本地表的修改直接執(zhí)行即可。如果要對分布式表進行修改，需分如下情況進行：

?如果沒有數(shù)據(jù)寫入，您可以先修改本地表，然后修改分布式表。

?如果數(shù)據(jù)正在寫入，您需要區(qū)分不同的類型進行操作。

添加表字段SQL示例參考：

ALTER TABLE reports_prestore_outbound_fulltrace_local on  cluster default ADD COLUMN  st_redeliver_time DateTime COMMENT '再投時間';
ALTER TABLE reports_prestore_outbound_fulltrace_all on  cluster default ADD COLUMN  st_redeliver_time DateTime COMMENT '再投時間';

1.7 DDL執(zhí)行注意事項

1.mutation（delete，update)操作比較重，盡量避免執(zhí)行此類操作；

2.清理過期數(shù)據(jù)，應(yīng)使用TTL，或者drop partition；

3.分布式DDL，分片副本節(jié)點串行執(zhí)行，出現(xiàn)阻塞會導(dǎo)致后面所有DDL無法正常執(zhí)行，建議輪詢各分片執(zhí)行DDL，尤其是變更字段類型，不建議直接on cluster default進行變更；

4.optimize table table_name final手動觸發(fā)合并慎用，盡量按分區(qū)操作。

二 數(shù)據(jù)寫入約定

結(jié)合供應(yīng)鏈的使用場景，這里在flink層加工完數(shù)據(jù)后沒有直接將數(shù)據(jù)寫入ClickHouse集群，而是發(fā)送到JDQ隊列中。這樣做的優(yōu)勢 1、加工和存儲解藕 2、JDQ消息共享；

若數(shù)據(jù)需要做主備存儲，我們只需要創(chuàng)建新的DTS任務(wù)訂閱JDQ消息，將消息寫入到備用的ClickHouse集群即可。

2.1 DTS任務(wù)命名約定

任務(wù)名規(guī)則：sc_digital_${集群ID}_${分布式表名}

使用"sc_digital_"前綴加分布式模型名稱，如：sc_digital_c4omjd8fl7_reports_prestore_outbound_fulltrace_all

2.2 DTS所屬項目空間

創(chuàng)建DTS任務(wù)時，任務(wù)需要放在《數(shù)字化-DTS任務(wù)空間》下。

2.3 DTS寫入批次設(shè)置

DTS任務(wù)批次寫入默認(rèn)值“40W/1分鐘”。這里需要根據(jù)實際情況適當(dāng)調(diào)下。

建議：每次插入50W行左右數(shù)據(jù), 最多不可超過100W行. 總之CK不像MySQL要小事務(wù). 
比如1000W行數(shù)據(jù), MySQL建議一次插入1W左右, 使用小事務(wù), 執(zhí)行1000次. CK建議20次,每次50W. 
這是MergeTree引擎原理決定的, 頻繁少量插入會導(dǎo)致data part過多, 合并不過來.

2.4 DTS消費JDQ的等級

默認(rèn)消費JDQ的等級為L3。消費等級要根據(jù)業(yè)務(wù)實際使用場景做相應(yīng)調(diào)整。以下等級劃分標(biāo)準(zhǔn)（來源于JDQ等級調(diào)整說明）：

三 數(shù)據(jù)查詢約定

針對易出問題的flink-CK鏈路進行雙流，物理隔離，遇到問題可將查詢請求一鍵切換至備用CK集群。

3.1 盡量prewhere替代where

值不變得字段必須使用prewhere特性提升查詢性能

注意：prewhere目前只能用于MergeTree系列的表引擎

3.2 where條件，盡量包含分區(qū)鍵，和主鍵索引前綴字段

盡量遵循最左原則，如果跳過最左前綴字段，使用其他字段查詢，也會走索引過濾一些數(shù)據(jù)，但是效果不好；

3.3 避免使用Select *

避免使用 SELECT * 操作，這是一個非常影響的操作。應(yīng)當(dāng)對列進行裁剪，只選擇你需要的列，因為字段越少，消耗的 IO 資源就越少，從而性能就越高。

3.4 where、group by 順序

where和group by中的列順序，要和建表語句中order by的列順序統(tǒng)一，并且放在最前面使得它們有連續(xù)不間斷的公共前綴，否則會影響查詢性能。

3.5 JOIN 性能不是很好，應(yīng)避免使用

替代方案：業(yè)務(wù)設(shè)計使用大寬表，或使用in替代多變關(guān)聯(lián)，或使用字典，但需注意內(nèi)存占用；如必須使用join，右表選小表（hash join 右表會全部加載到內(nèi)存）;

3.6 使用final去重

使用final去重查詢，盡量不要用argMax

3.7 二級索引

1、可變值字段不能添加二級索引。按此字段做條件查詢會先走索引在合并數(shù)據(jù)，查出而外的中間態(tài)數(shù)據(jù)。

2、 增加二級索引只對后續(xù)新增數(shù)據(jù)生效。如需對歷史數(shù)據(jù)也走索引，需要按分區(qū)刷新數(shù)據(jù)

創(chuàng)建二級索引示例

Alter table reports_prestore_outbound_fulltrace_local ON cluster default 
    ADD INDEX idx_belong_province_code belong_province_code TYPE set(0) GRANULARITY 5;
Alter table reports_prestore_outbound_fulltrace_local ON cluster default 
    ADD INDEX idx_st_delivery_time st_delivery_time TYPE minmax GRANULARITY 5;

審核編輯 黃宇