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

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

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

有哪些好處呢？

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

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

3、快速統(tǒng)計(jì)到團(tuán)隊(duì) 或 某個(gè)ck集群有多少DTS任務(wù)（運(yùn)維時(shí)不會(huì)遺漏，一目了然）

一 建表約定

1.1 表命名約定

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

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

參數(shù)解釋說(shuō)明：
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 庫(kù)名.分布式表名 on cluster default AS 本地表名 
ENGINE = Distributed (default,庫(kù)名,本地表名,sipHash64(分片鍵));

建表示例腳本：

CREATE TABLE reports_prestore_outbound_fulltrace_local on cluster `default`
(
    `sm_so_no` String COMMENT '訂單號(hào)',
    `sm_waybill_code` String COMMENT '青龍運(yùn)單號(hào)',
    `sm_so_type` Int64 DEFAULT 0 COMMENT '訂單類型',
    `st_so_status` Int64 DEFAULT 0 COMMENT '訂單狀態(tài)(1初始、2定位完成、3定位失敗、5揀貨中、6出庫(kù)完成、7取消、8轉(zhuǎn)病單、9站點(diǎn)已收貨、10已妥投、11再投中、12已拒收)',
        ?
  	`st_delivery_time` DateTime COMMENT '妥投時(shí)間',
   	`st_redeliver_time` DateTime COMMENT '再投時(shí)間',
   	`st_reject_time` DateTime COMMENT '拒收時(shí)間',
   	`version` UInt64 COMMENT '更新版本號(hào)',
    `ts` DateTime DEFAULT now() COMMENT '時(shí)間戳'
)
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ù)特點(diǎn)決定，不宜過(guò)粗或過(guò)細(xì)。 建議使用toYYYYMMDD()按天分區(qū)，如果數(shù)據(jù)量很少，100w左右，建議使用toYYYYMM()按月分區(qū)，過(guò)多的分區(qū)會(huì)占用大量的資源，會(huì)因?yàn)槲募到y(tǒng)中的文件數(shù)量過(guò)多和需要打開的文件描述符過(guò)多，導(dǎo)致 SELECT 查詢效率不佳。

1.3 分片鍵設(shè)置

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

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

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

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

2、若未指定默認(rèn)為排序建，主鍵不保證唯一性。主鍵過(guò)長(zhǎng)會(huì)拖慢寫入性能，并且會(huì)造成過(guò)多的內(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，時(shí)間字段默認(rèn)值為1970-01-01 08:00:00，使用到的地方排除掉默認(rèn)值即可

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

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

5、盡量不使用Nullable類型

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

設(shè)置成Nullable對(duì)性能影響也沒(méi)有多大，可能是因?yàn)槲覀償?shù)據(jù)量比較小。不過(guò)官方已經(jīng)明確指出盡量不要使用Nullable類型，因?yàn)镹ullable字段不能被索引，而且Nullable列除了有一個(gè)存儲(chǔ)正常值的文件，還會(huì)有一個(gè)額外的文件來(lái)存儲(chǔ)Null標(biāo)記。

1.6 新增列操作方式

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

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

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

添加表字段SQL示例參考：

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

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

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

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

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

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

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

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

若數(shù)據(jù)需要做主備存儲(chǔ)，我們只需要?jiǎng)?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所屬項(xiàng)目空間

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

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

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

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

2.4 DTS消費(fèi)JDQ的等級(jí)

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

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

針對(duì)易出問(wèn)題的flink-CK鏈路進(jìn)行雙流，物理隔離，遇到問(wèn)題可將查詢請(qǐng)求一鍵切換至備用CK集群。

3.1 盡量prewhere替代where

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

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

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

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

3.3 避免使用Select *

避免使用 SELECT * 操作，這是一個(gè)非常影響的操作。應(yīng)當(dāng)對(duì)列進(jìn)行裁剪，只選擇你需要的列，因?yàn)樽侄卧缴?，消耗?IO 資源就越少，從而性能就越高。

3.4 where、group by 順序

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

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

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

3.6 使用final去重

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

3.7 二級(jí)索引

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

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

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

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;

審核編輯 黃宇