在引入ClickHouse過程中經歷各種困難，耗費大量精力去探索並一一解決，在這裡記錄一下希望能夠給沒有接觸過ClickHouse的同學提供一些方向上的指引避免多走彎路，如果文中有錯誤也希望多包含給出指點，歡迎大家一起討論ClickHouse相關的話題。本文偏長但全是乾貨，請預留40~60分鐘進行閱... ...
1 前言
京喜達技術部在社區團購場景下採用JDQ+Flink+Elasticsearch架構來打造實時數據報表。隨著業務的發展 Elasticsearch開始暴露出一些弊端，不適合大批量的數據查詢，高頻次深度分頁導出導致ES宕機、不能精確去重統計，多個欄位聚合計算時性能下降明顯。所以引入ClickHouse來處理這些弊端。
數據寫入鏈路是業務數據(binlog)經過處理轉換成固定格式的MQ消息，Flink訂閱不同Topic來接收不同生產系統的表數據，進行關聯、計算、過濾、補充基礎數據等加工關聯彙總成寬表，最後將加工後的DataStream數據流雙寫入ES和ClickHouse。查詢服務通過JSF和物流網關對外暴露提供給外部進行展示，由於ClickHouse將所有計算能力都用在一次查詢上，所以不擅長高併發查詢。我們通過對部分實時聚合指標介面增加緩存，或者定時任務查詢ClickHosue計算指標存儲到ES，部分指標不再實時查ClickHouse而是查ES中計算好的指標來抗住併發，並且這種方式能夠極大提高開發效率，易維護，能夠統一指標口徑。
在引入ClickHouse過程中經歷各種困難，耗費大量精力去探索並一一解決，在這裡記錄一下希望能夠給沒有接觸過ClickHouse的同學提供一些方向上的指引避免多走彎路，如果文中有錯誤也希望多包含給出指點，歡迎大家一起討論ClickHouse相關的話題。本文偏長但全是乾貨，請預留40~60分鐘進行閱讀。
2 遇到的問題
前文說到遇到了很多困難，下麵這些遇到的問題是本文講述的重點內容。
- 我們該使用什麼表引擎
- Flink如何寫入到ClickHouse
- 查詢ClickHouse為什麼要比查詢ES慢1~2分鐘
- 寫入分散式表還是本地表
- 為什麼只有某個分片CPU使用率高
- 如何定位是哪些SQL在消耗CPU，這麼多慢SQL，我怎麼知道是哪個SQL引起的
- 找到了慢SQL，如何進行優化
- 如何抗住高併發、保證ClickHouse可用性
3 表引擎選擇與查詢方案
在選擇表引擎以及查詢方案之前，先把需求捋清楚。前言中說到我們是在Flink中構造寬表，在業務上會涉及到數據的更新的操作，會出現同一個業務單號多次寫入資料庫。ES的upsert支持這種需要覆蓋之前數據的操作，ClickHouse中沒有upsert，所以需要探索出能夠支持upsert的方案。帶著這個需求來看一下ClickHouse的表引擎以及查詢方案。
ClickHouse有很多表引擎，表引擎決定了數據以什麼方式存儲，以什麼方式載入，以及數據表擁有什麼樣的特性。目前ClickHouse表引擎一共分為四個系列，分別是Log、MergeTree、Integration、Special。
- Log系列：適用於少量數據(小於一百萬行)的場景，不支持索引，所以對於範圍查詢效率不高。
- Integration系列：主要用於導入外部數據到ClickHouse，或者在ClickHouse中直接操作外部數據，支持Kafka、HDFS、JDBC、Mysql等。
- Special系列：比如Memory將數據存儲在記憶體，重啟後會丟失數據，查詢性能極好，File直接將本地文件作為數據存儲等大多是為了特定場景而定製的。
- MergeTree系列：MergeTree家族自身擁有多種引擎的變種，其中MergeTree作為家族中最基礎的引擎提供主鍵索引、數據分區、數據副本和數據採樣等能力並且支持極大量的數據寫入，家族中其他引擎在MergeTree引擎的基礎上各有所長。
Log、Special、Integration主要用於特殊用途，場景相對有限。其中最能體現ClickHouse性能特點的是MergeTree及其家族表引擎，也是官方主推的存儲引擎，幾乎支持所有ClickHouse核心功能，在生產環境的大部分場景中都會使用此系列的表引擎。我們的業務也不例外需要使用主鍵索引，日數據增量在2500多萬的增量，所以MergeTree系列是我們需要探索的目標。
MergeTree系列的表引擎是為插入大量數據而生，數據是以數據片段的形式一個接一個的快速寫入，ClickHouse為了避免數據片段過多會在後臺按照一定的規則進行合併形成新的段，相比在插入時不斷的修改已經存儲在磁碟的數據，這種插入後合併再合併的策略效率要高很多。這種數據片段反覆合併的特點，也正是MergeTree系列(合併樹家族)名稱的由來。為了避免形成過多的數據片段，需要進行批量寫入。MergeTree系列包含MergeTree、ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree、SummingMergeTree、AggregatingMergeTree引擎，下麵就介紹下這幾種引擎。
3.1 MergeTree：合併樹
MergeTree支持所有ClickHouse SQL語法。大部分功能點和我們熟悉的MySQL是類似的，但是有些功能差異比較大，比如主鍵，MergeTree系列的主鍵並不用於去重，MySQL中一個表中不能存在兩條相同主鍵的數據，但是ClickHouse中是可以的。
下麵建表語句中，定義了訂單號，商品數量，創建時間，更新時間。按照創建時間進行數據分區，orderNo作為主鍵(primary key)，orderNo也作為排序鍵(order by)，預設情況下主鍵和排序鍵相同，大部分情況不需要再專門指定primary key，這個例子中指定只是為了說明下主鍵和排序鍵的關係。當然排序鍵可以與的主鍵欄位不同，但是主鍵必須為排序鍵的子集，例如主鍵(a,b), 排序鍵必須為(a,b, , )，並且組成主鍵的欄位必須在排序鍵欄位中的最左側。
CREATE TABLE test_MergeTree ( orderNo String, number Int16, createTime DateTime, updateTime DateTime) ENGINE = MergeTree()PARTITION BY createTimeORDER BY (orderNo)PRIMARY KEY (orderNo);insert into test_MergeTree values('1', '20', '2021-01-01 00:00:00', '2021-01-01 00:00:00');insert into test_MergeTree values('1', '30', '2021-01-01 00:00:00', '2021-01-01 01:00:00');
註意這裡寫入的兩條數據主鍵orderNo都是1的兩條數據，這個場景是我們先創建訂單，再更新了訂單的商品數量為30和更新時間，此時業務實際訂單量為1，商品件量是30。
插入主鍵相同的數據不會產生衝突，並且查詢數據兩條相同主鍵的數據都存在。下圖是查詢結果，由於每次插入都會形成一個part，第一次insert生成了1609430400_1_1_0 數據分區文件，第二次insert生成了1609430400_2_2_0 數據分區文件，後臺還沒觸發合併，所以在clickhouse-client上的展示結果是分開兩個表格的(圖形化查詢工具DBeaver、DataGrip不能看出是兩個表格，可以通過docker搭建ClickHouse環境通過client方式執行語句，文末有搭建CK環境文檔)。
預期結果應該是number從20更新成30，updateTime也會更新成相應的值，同一個業務主鍵只存在一行數據，可是最終是保留了兩條。Clickhouse中的這種處理邏輯會導致我們查詢出來的數據是不正確的。比如去重統計訂單數量，count(orderNo)，統計下單件數sum(number)。
下麵嘗試將兩行數據進行合併。
進行強制的分段合併後，還是有兩條數據，並不是我們預期的保留最後一條商品數量為30的數據。但是兩行數據合併到了一個表格中，其中的原因是1609430400_1_1_0，1609430400_2_2_0 的partitionID相同合併成了1609430400_1_2_1這一個文件。合併完成後其中1609430400_1_1_0，1609430400_2_2_0會在一定時間(預設8min)後被後臺刪除。下圖是分區文件的命名規則，partitionID：1609430400 = 2021-01-01 00:00:00，MinBolckNum、MaxBolckNum：是最小數據塊最大數據塊，是一個整形自增的編號。Level：0可以理解為分區合併過的次數，預設值是0，每次合併過後生成的新的分區後會加1。
綜合上述，可以看出MergeTree雖然有主鍵，但並不是類似MySQL用來保持記錄唯一的去重作用，只是用來查詢加速，即使在手動合併之後，主鍵相同的數據行也仍舊存在，不能按業務單據去重導致count(orderNo)，sum(number)拿到的結果是不正確的，不適用我們的需求。
3.2 ReplacingMergeTree：替換合併樹
MergeTree雖然有主鍵，但是不能對相同主鍵的數據進行去重，我們的業務場景不能有重覆數據。ClickHouse提供了ReplacingMergeTree引擎用來去重，能夠在合併分區時刪除重覆的數據。我理解的去重分兩個方面，一個是物理去重，就是重覆的數據直接被刪除掉，另一個是查詢去重，不處理物理數據，但是查詢結果是已經將重覆數據過濾掉的。
示例如下，ReplacingMergeTree建表方法和MergeTree沒有特別大的差異，只是ENGINE 由MergeTree更改為ReplacingMergeTree([ver])，其中ver是版本列，是一個選填項，官網給出支持的類型是UInt ，Date或者DateTime，但是我試驗Int類型也是可以支持的(ClickHouse 20.8.11)。ReplacingMergeTree在數據合併時物理數據去重，去重策略如下。
- 如果ver版本列未指定，相同主鍵行中保留最後插入的一行。
- 如果ver版本列已經指定，下麵實例就指定了version列為版本列，去重是將會保留version值最大的一行，與數據插入順序無關。
CREATE TABLE test_ReplacingMergeTree ( orderNo String, version Int16, number Int16, createTime DateTime, updateTime DateTime) ENGINE = ReplacingMergeTree(version)PARTITION BY createTimeORDER BY (orderNo)PRIMARY KEY (orderNo);1) insert into test_ReplacingMergeTree values('1', 1, '20', '2021-01-01 00:00:00', '2021-01-01 00:00:00');2) insert into test_ReplacingMergeTree values('1', 2, '30', '2021-01-01 00:00:00', '2021-01-01 01:00:00');3) insert into test_ReplacingMergeTree values('1', 3, '30', '2021-01-02 00:00:00', '2021-01-01 01:00:00');-- final方式去重select * from test_ReplacingMergeTree final;-- argMax方式去重select argMax(orderNo,version) as orderNo, argMax(number,version) as number,argMax(createTime,version),argMax(updateTime,version) from test_ReplacingMergeTree;
下圖是在執行完前兩條insert語句後進行三次查詢的結果，三種方式查詢均未對物理存儲的數據產生影響，final、argMax方式只是查詢結果是去重的。
- 普通查詢：查詢結果未去重，物理數據未去重(未合併分區文件)
- final去重查詢：查詢結果已去重，物理數據未去重(未合併分區文件)
- argMax去重查詢：查詢結果已去重，物理數據未去重(未合併分區文件)
其中final和argMax查詢方式都過濾掉了重覆數據。我們的示例都是基於本地表做的操作，final和argMax在結果上沒有差異，但是如果基於分散式表進行試驗，兩條數據落在了不同數據分片(註意這裡不是數據分區)，那麼final和argMax的結果將會產生差異。final的結果將是未去重的，原因是final只能對本地表做去重查詢，不能對跨分片的數據進行去重查詢，但是argMax的結果是去重的。argMax是通過比較第二參數version的大小，來取出我們要查詢的最新數據來達到過濾掉重覆數據的目的，其原理是將每個Shard的數據摟到同一個Shard的記憶體中進行比較計算，所以支持跨分片的去重。
由於後臺的合併是在不確定時間執行的，執行合併命令，然後再使用普通查詢，發現結果已經是去重後的數據，version=2，number=30 是我們想保留的數據。
執行第三條insert語句，第三條的主鍵和前兩條一致，但是分區欄位createTime欄位不同，前兩條是2021-01-01 00:00:00，第三條是2021-01-02 00:00:00，如果按照上述的理解，在強制合併會後將會保留version = 3的這條數據。我們執行普通查詢之後發現，version = 1和2的數據做了合併去重，保留了2，但是version=3的還是存在的，這其中的原因ReplacingMergeTree是已分區為單位刪除重覆數據。前兩個insert的分區欄位createTime欄位相同，partitionID相同，所以都合併到了1609430400_1_2_1分區文件，而第三條insert與前兩條不一致，不能合併到一個分區文件，不能做到物理去重。最後通過final去重查詢發現可以支持查詢去重，argMax也是一樣的效果未作展示。
ReplacingMergeTree具有如下特點
- 使用主鍵作為判斷重覆數據的唯一鍵，支持插入相同主鍵數據。
- 在合併分區的時候會觸發刪除重覆數據的邏輯。但是合併的時機不確定，所以在查詢的時候可能會有重覆數據，但是最終會去重。可以手動調用optimize，但是會引發對數據大量的讀寫，不建議生產使用。
- 以數據分區為單位刪除重覆數據，當分區合併時，同一分區內的重覆數據會被刪除，不同分區的重覆數據不會被刪除。
- 可以通過final，argMax方式做查詢去重，這種方式無論有沒有做過數據合併，都可以得到正確的查詢結果。
ReplacingMergeTree最佳使用方案
- 普通select查詢：對時效不高的離線查詢可以採用ClickHouse自動合併配合，但是需要保證同一業務單據落在同一個數據分區，分散式表也需要保證在同一個分片(Shard)，這是一種最高效，最節省計算資源的查詢方式。
- final方式查詢：對於實時查詢可以使用final，final是本地去重，需要保證同一主鍵數據落在同一個分片(Shard)，但是不需要落在同一個數據分區，這種方式效率次之，但是與普通select相比會消耗一些性能，如果where條件對主鍵索引，二級索引，分區欄位命中的比較好的話效率也可以完全可以使用。
- argMax方式查詢：對於實時查詢可以使用argMax，argMax的使用要求最低，咋查都能去重，但是由於它的實現方式，效率會低很多，也很消耗性能，不建議使用。後面9.4.3會配合壓測數據與final進行對比。
上述的三種使用方案中其中ReplacingMergeTree配合final方式查詢，是符合我們需求的。
3.3 CollapsingMergeTree/VersionedCollapsingMergeTree：摺疊合併樹
摺疊合併樹不再通過示例來進行說明。可參考官網示例。
CollapsingMergeTree通過定義一個sign標記位欄位，記錄數據行的狀態。如果sign標記位1(《狀態》行)， 則表示這是一行有效的數據， 如果sign標記位為 -1(《取消》行)，則表示這行數據需要被刪除。需要註意的是數據主鍵相同才可能會被摺疊。
- 如果sign=1比sign=-1的數據多至少一行，則保留最後一行sign=1的數據。
- 如果sign=-1比sign=1多至少一行，則保留第一行sign=-1的行。
- 如果sign=1與sign=-1的行數一樣多，最後一行是sign=1，則保留第一行sign=-1和最後一行sign=1的數據。
- 如果sign=1與sign=-1的行數一樣多，最後一行是sign=-1，則什麼都不保留。
- 其他情況ClickHouse不會報錯但會列印告警日誌，這種情況下，查詢的結果是不確定不可預知的。
在使用CollapsingMergeTree時候需要註意
1）與ReplacingMergeTree一樣，摺疊數據不是實時觸發的，是在分區合併的時候才會體現，在合併之前還是會查詢到重覆數據。解決方式有兩種
- 使用optimize強制合併，同樣也不建議在生產環境中使用效率極低並且消耗資源的強制合併。
- 改寫查詢方式，通過group by 配合有符號的sign列來完成。這種方式增加了使用的編碼成本
2）在寫入方面通過《取消》行刪除或修改數據的方式需要寫入數據的程式記錄《狀態》行的數據，極大的增加存儲成本和編程的複雜性。Flink在上線或者某些情況下會重跑數據，會丟失程式中的記錄的數據行，可能會造成sign=1與sign=-1不對等不能進行合併，這一點是我們無法接受的問題。
CollapsingMergeTree還有一個弊端，對寫入的順序有嚴格的要求，如果按照正常順序寫入，先寫入sign=1的行再寫入sign=-1的行，能夠正常合併，如果順序反過來則不能正常合併。ClickHouse提供了VersionedCollapsingMergeTree，通過增加版本號來解決順序問題。但是其他的特性與CollapsingMergeTree完全一致，也不能滿足我們的需求
3.4 表引擎總結
我們詳細介紹了MergeTree系列中的MergeTree、ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree四種表引擎，還有SummingMergeTree、AggregatingMergeTree沒有介紹，SummingMergeTree是為不關心明細數據，只關心彙總數據設計的表引擎。MergeTree也能夠滿足這種只關註彙總數據的需求，通過group by配合sum，count聚合函數就可以滿足，但是每次查詢都進行實時聚合會增加很大的開銷。我們既有明細數據需求，又需要彙總指標需求，所以SummingMergeTree不能滿足我們的需求。AggregatingMergeTree是SummingMergeTree升級版，本質上還是相同的，區別在於：SummingMergeTree對非主鍵列進行sum聚合，而AggregatingMergeTree則可以指定各種聚合函數。同樣也滿足不了需求。
最終我們選用了ReplacingMergeTree引擎，分散式表通過業務主鍵sipHash64(docId)進行shard保證同一業務主鍵數據落在同一分片，同時使用業務單據創建時間按月/按天進行分區。配合final進行查詢去重。這種方案在雙十一期間數據日增3000W，業務高峰資料庫QPS93，32C 128G 6分片 2副本的集群CPU使用率最高在60%，系統整體穩定。下文的所有實踐優化也都是基於ReplacingMergeTree引擎。
4 Flink如何寫入ClickHouse
4.1 Flink版本問題
Flink支持通過JDBC Connector將數據寫入JDBC資料庫，但是Flink不同版本的JDBC connector寫入方式有很大區別。因為Flink在1.11版本對JDBC Connector進行了一次較大的重構：
- 1.11版本之前包名為flink-jdbc
- 1.11版本(包含)之後包名為flink-connector-jdbc
兩者對Flink中以不同方式寫入ClickHouse Sink的支持情況如下:
起初我們使用1.10.3版本的Flink，flink-jdbc不支持使用DataStream流寫入，需要升級Flink版本至1.11.x及以上版本來使用flink-connector-jdbc來寫入數據到ClickHouse。
4.2 構造ClickHouse Sink
/** * 構造Sink * @param clusterPrefix clickhouse 資料庫名稱 * @param sql insert 占位符 eq：insert into demo (id, name) values (?, ?) */public static SinkFunction getSink(String clusterPrefix, String sql) { String clusterUrl = LoadPropertiesUtil.appInfoProcessMap.get(clusterPrefix + CLUSTER_URL); String clusterUsername = LoadPropertiesUtil.appInfoProcessMap.get(clusterPrefix + CLUSTER_USER_NAME); String clusterPassword = LoadPropertiesUtil.appInfoProcessMap.get(clusterPrefix + CLUSTER_PASSWORD); return JdbcSink.sink(sql, new CkSinkBuilder<>(), new JdbcExecutionOptions.Builder().withBatchSize(200000).build(), new JdbcConnectionOptions.JdbcConnectionOptionsBuilder() .withDriverName("ru.yandex.clickhouse.ClickHouseDriver") .withUrl(clusterUrl) .withUsername(clusterUsername) .withPassword(clusterPassword) .build());}
使用flink-connector-jdbc的JdbcSink.sink() api來構造Flink sink。JdbcSink.sink()入參含義如下
- sql：占位符形式的sql語句，例如：insert into demo (id, name) values (?, ?)
- new CkSinkBuilder<>()：org.apache.flink.connector.jdbc.JdbcStatementBuilder介面的實現類，主要是將流中數據映射到java.sql.PreparedStatement 來構造PreparedStatement ，具體不再贅述。
- 第三個入參：flink sink的執行策略。
- 第四個入參：jdbc的驅動，連接，賬號與密碼。
- 使用時直接在DataStream流中addSink即可。
5 Flink寫入ClickHouse策略
Flink同時寫入ES和Clikhouse，但是在進行數據查詢的時候發現ClickHouse永遠要比ES慢一些，開始懷疑是ClickHouse合併等處理會耗費一些時間，但是ClickHouse這些合併操作不會影響查詢。後來查閱Flink寫入策略代碼發現是我們使用的策略有問題。
上段(4.2)代碼中new JdbcExecutionOptions.Builder().withBatchSize(200000).build()為寫入策略，ClickHouse為了提高寫入性能建議進行不少於1000行的批量寫入，或每秒不超過一個寫入請求。策略是20W行記錄進行寫入一次，Flink進行Checkpoint的時候也會進行寫入提交。所以當數據量積攢到20W或者Flink記性Checkpoint的時候ClickHouse裡面才會有數據。我們的ES sink策略是1000行或5s進行寫入提交，所以出現了寫入ClickHouse要比寫入ES慢的現象。
到達20W或者進行Checkpoint的時候進行提交有一個弊端，當數據量小達不到20W這個量級，Checkpoint時間間隔t1，一次checkpoint時間為t2，那麼從接收到JDQ消息到寫入到ClickHouse最長時間間隔為t1+t2，完全依賴Checkpoint時間，有時候有數據積壓最慢有1~2min。進而對ClickHouse的寫入策略進行優化，new JdbcExecutionOptions.Builder().withBatchIntervalMs(30 * 1000).build() 優化為沒30s進行提交一次。這樣如果Checkpoint慢的話可以觸發30s提交策略，否則Checkpoint的時候提交，這也是一種比較折中的策略，可以根據自己的業務特性進行調整，在調試提交時間的時候發現如果間隔過小，zookeeper的cpu使用率會提升，10s提交一次zk使用率會從5%以下提升到10%左右。
Flink中的org.apache.flink.connector.jdbc.internal.JdbcBatchingOutputFormat#open處理邏輯如下圖。
6 寫入分散式表還是本地表
先說結果，我們是寫入分散式表。
網上的資料和ClickHouse雲服務的同事都建議寫入本地表。分散式表實際上是一張邏輯表並不存儲真實的物理數據。如查詢分散式表，分散式表會把查詢請求發到每一個分片的本地表上進行查詢，然後再集合每個分片本地表的結果，彙總之後再返回。寫入分散式表，分散式表會根據一定規則，將寫入的數據按照規則存儲到不同的分片上。如果寫入分散式表也只是單純的網路轉發，影響也不大，但是寫入分散式表並非單純的轉發，實際情況見下圖。
有三個分片S1、S2、S3，客戶端連接到S1節點，進行寫入分散式表操作。
- 第一步：寫入分散式表1000條數據，分散式表會根據路由規則，假設按照規則300條分配到S1，200條到S2，500條到S3
- 第二步：client給過來1000條數據，屬於S1的300條數據直接寫入磁碟，數據S2,S3的數據也會寫入到S1的臨時目錄
- 第三步：S2,S3接收到zk的變更通知，生成拉取S1中當前分片對應的臨時目錄數據的任務，並且將任務放到一個隊列，等到某個時機會將數據拉到自身節點。
從分散式表的寫入方式可以看到，會將所有數據落到client連接分片的磁碟上。如果數據量大，磁碟的IO會造成瓶頸。並且MergeTree系列引擎存在合併行為，本身就有寫放大(一條數據合併多次)，占用一定磁碟性能。在網上看到寫入本地表的案例都是日增量百億，千億。我們選擇寫入分散式表主要有兩點，一是簡單，因為寫入本地表需要改造代碼，自己指定寫入哪個節點，另一個是開發過程中寫入本地表並未出現什麼嚴重的性能瓶頸。雙十一期間數據日增3000W(合併後)行並未造成寫入壓力。如果後續產生瓶頸，可能會放棄寫入分散式表。
7 為什麼只有某個分片CPU使用率高
7.1 數據分佈不均勻，導致部分節點CPU高
上圖是在接入ClickHouse過程中遇到的一個問題，其中7-1節點CPU使用率非常高，不同節點的差異非常大。後來通過SQL定位發現不同節點上的數據量差異也非常大，其中7-1節點數據量是最多的，導致7-1節點相比其他節點需要處理的數據行數非常多，所以CPU相對會高很多。因為我們使用網格站編碼，分揀倉編碼hash後做分散式表的數據分片策略，但是分揀倉編碼和網站編碼的基數比較小，導致hash後不夠分散造成這種數據傾斜的現象。後來改用業務主鍵做hash，解決了這種部分節點CPU高的問題。
7.2 某節點觸發合併，導致該節點CPU高
7-4節點(主節點和副本)，CPU毫無徵兆的比其他節點高很多，在排除新業務上線、大促等突發情況後進行慢SQL定位，通過query_log進行分析每個節點的慢查詢，具體語句見第8小節。
通過兩個節點的慢SQL進行對比，發現是如下SQL的查詢情況有較大差異。
SELECT ifNull(sum(t1.unTrackQty), 0) AS unTrackQtyFROM wms.wms_order_sku_local AS t1 FINAL PREWHERE t1.shipmentOrderCreateTime > '2021-11-17 11:00:00' AND t1.shipmentOrderCreateTime <= '2021-11-18 11:00:00' AND t1.gridStationNo = 'WG0000514' AND t1.warehouseNo NOT IN ('wms-6-979', 'wms-6-978', '6_979', '6_978') AND t1.orderType = '10'WHERE t1.ckDeliveryTaskStatus = '3'
但是我們有個疑惑，同樣的語句，同樣的執行次數，而且兩個節點的數據量，part數量都沒有差異，為什麼7-4節點掃描的行數是7-0上的5倍，把這個原因找到，應該就能定位到問題的根本原因了。
接下來我們使用clickhouse-client進行SQL查詢，開啟trace級別日誌，查看SQL的執行過程。具體執行方式以及查詢日誌分析參考下文9.1小節，這裡我們直接分析結果。
上面兩張圖可以分析出
- 7-0節點：掃描了4個part分區文件，共計94W行，耗時0.089s
- 7-4節點：掃描了2個part分區文件，其中有一個part491W行，共計502W行，耗時0.439s
很明顯7-4節點的202111_0_408188_322這個分區比較異常，因為我們是按月分區的，7-4節點不知道什麼原因發生了分區合併，導致我們檢索的11月17號的數據落到了這個大分區上，所以但是查詢會過濾11月初到18號的所有數據，和7-0節點產生了差異。上述的SQL通過 gridStationNo = ‘WG0000514’ 條件進行查詢，所以在對gridStationNo 欄位進行創建二級索引後解決了這個問題。
在增加加二級索引後7-4節點：掃描了2個part分區文件，共計38W行，耗時0.103s。
7.3 物理機故障
這種情況少見，但是也遇到過一次
8 如何定位是哪些SQL在消耗CPU
我認為可以通過兩個方向來排查問題，一個是SQL執行頻率是否過高，另一個方向是判斷是否有慢SQL在執行，高頻執行或者慢查詢都會大量消耗CPU的計算資源。下麵通過兩個案例來說明一下排查CPU偏高的兩種有效方法，下麵兩種雖然操作上是不同的，但是核心都是通過分析query_log來進行分析定位的。
8.1 grafana定位高頻執行SQL
在12月份上線了一些需求，最近發現CPU使用率對比來看使用率偏高，需要排查具體是哪些SQL導致的。
通過上圖自行搭建的grafana監控可以看出(搭建文檔)，有幾個查詢語句執行頻率非常高，通過SQL定位到查詢介面代碼邏輯，發現一次前端介面請求後端介面會執行多條相似條件的SQL語句，只是業務狀態不相同。這種需要統計不同類型、不同狀態的語句，可以進行條件聚合進行優化，9.4.1小節細講。優化後語句執行頻率極大的降低。
8.2 掃描行數高/使用記憶體高：query_log_all分析
上節說SQL執行頻率高，導致CPU使用率高。如果SQL頻率執行頻率很低很低，但是CPU還是很高該怎麼處理。SQL執行頻率低，可能存在掃描的數據行數很大的情況，消耗的磁碟IO，記憶體，CPU這些資源很大，這種情況下就需要換個手段來排查出來這個很壞很壞的SQL(T⌓T)。
ClickHouse自身有system.query_log表，用於記錄所有的語句的執行日誌，下圖是該表的一些關鍵欄位信息
-- 創建query_log分散式表CREATE TABLE IF NOT EXISTS system.query_log_allON CLUSTER defaultAS system.query_logENGINE = Distributed(sht_ck_cluster_pro,system,query_log,rand());-- 查詢語句select -- 執行次數 count(), -- 平均查詢時間 avg(query_duration_ms) avgTime, -- 平均每次讀取數據行數 floor(avg(read_rows)) avgRow, -- 平均每次讀取數據大小 floor(avg(read_rows) / 10000000) avgMB, -- 具體查詢語句 any(query), -- 去除掉where條件，用戶group by歸類 substring(query, positionCaseInsensitive(query, 'select'), positionCaseInsensitive(query, 'from')) as queryLimitfrom system.query_log_all/system.query_logwhere event_date = '2022-01-21' and type = 2group by queryLimitorder by avgRow desc;
query_log是本地表，需要創建分散式表，查詢所有節點的查詢日誌，然後再執行查詢分析語句，執行效果見下圖，圖中可以看出有幾個語句平均掃秒行數已經到了億級別，這種語句可能就存在問題。通過掃描行數可以分析出索引，查詢條件等不合理的語句。7.2中的某個節點CPU偏高就是通過這種方式定位到有問題的SQL語句，然後進一步排查從而解決的。
9 如何優化慢查詢
ClickHouse的SQL優化比較簡單，查詢的大部分耗時都在磁碟IO上，可以參考下這個小實驗來理解。核心優化方向就是降低ClickHouse單次查詢處理的數據量，也就是降低磁碟IO。下麵介紹下慢查詢分析手段、建表語句優化方式，還有一些查詢語句優化。
9.1 使用服務日誌進行慢查詢分析
雖然ClickHouse在20.6版本之後已經提供查看查詢計劃的原生EXPLAIN，但是提供的信息對我們進行慢SQL優化提供的幫助不是很大，在20.6版本前藉助後臺的服務日誌，可以拿到更多的信息供我們分析。與EXPLAIN相比我更傾向於使用查看服務日誌這種方式進行分析，這種方式需要使用clickhouse-client進行執行SQL語句，文末有通過docker搭建CK環境文檔。高版本的EXPLAIN提供了ESTIMATE可以查詢到SQL語句掃描的part數量、數據行數等細粒度信息，EXPLAIN使用方式可以參考官方文檔說明。
用一個慢查詢來進行分析，通過8.2中的query_log_all定位到下列慢SQL。
select ifNull(sum(interceptLackQty), 0) as interceptLackQtyfrom wms.wms_order_sku_local final prewhere productionEndTime = '2022-02-17 08:00:00' and orderType = '10'where shipmentOrderDetailDeleted = '0' and ckContainerDetailDeleted = '0'
使用clickhouse-client，send_logs_level參數指定日誌級別為trace。
clickhouse-client -h 地址 --port 埠 --user 用戶名 --password 密碼 --send_logs_level=trace
在client中執行上述慢SQL，服務端列印日誌如下，日誌量較大，省去部分部分行，不影響整體日誌的完整性。
[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.036317 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> executeQuery: (from 11.77.96.163:35988, user: bjwangjiangbo) select ifNull(sum(interceptLackQty), 0) as interceptLackQty from wms.wms_order_sku_local final prewhere productionEndTime = '2022-02-17 08:00:00' and orderType = '10' where shipmentOrderDetailDeleted = '0' and ckContainerDetailDeleted = '0'[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.037876 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> ContextAccess (bjwangjiangbo): Access granted: SELECT(orderType, interceptLackQty, productionEndTime, shipmentOrderDetailDeleted, ckContainerDetailDeleted) ON wms.wms_order_sku_local[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038239 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> wms.wms_order_sku_local (SelectExecutor): Key condition: unknown, unknown, and, unknown, unknown, and, and, unknown, unknown, and, and[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038271 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> wms.wms_order_sku_local (SelectExecutor): MinMax index condition: unknown, unknown, and, unknown, unknown, and, and, unknown, unknown, and, and[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038399 [ 1340 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202101_0_0_0_3[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038475 [ 1407 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202103_0_17_2_22[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038491 [ 111 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202103_18_20_1_22..................................省去若幹行(此塊含義為：在分區內檢索有沒有使用索引).................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039041 [ 1205 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202202_1723330_1723365_7[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039054 [ 159 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202202_1723367_1723367_0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038928 [ 248 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202201_3675258_3700711_1054[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039355 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> wms.wms_order_sku_local (SelectExecutor): Selected 47 parts by date, 47 parts by key, 9471 marks by primary key, 9471 marks to read from 47 ranges[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039495 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202101_0_0_0_3, approx. 65536 rows starting from 0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039583 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202101_1_1_0_3, approx. 16384 rows starting from 0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.040291 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202102_0_2_1_4, approx. 146850 rows starting from 0..................................省去若幹行（每個分區讀取的數據行數信息）.................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.043538 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202202_1723330_1723365_7, approx. 24576 rows starting from 0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.043604 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202202_1723366_1723366_0, approx. 8192 rows starting from 0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.043677 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202202_1723367_1723367_0, approx. 8192 rows starting from 0..................................完成數據讀取，開始進行聚合計算.................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.047880 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> InterpreterSelectQuery: FetchColumns -> Complete[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.263500 [ 1377 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> AggregatingTransform: Aggregating[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.263680 [ 1439 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> Aggregator: Aggregation method: without_key..................................省去若幹行（數據讀取完成後做聚合操作）.................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.263840 [ 156 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> AggregatingTransform: Aggregated. 12298 to 1 rows (from 36.03 KiB) in 0.215046273 sec. (57187.69187876137 rows/sec., 167.54 KiB/sec.)[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.264283 [ 377 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> AggregatingTransform: Aggregated. 12176 to 1 rows (from 35.67 KiB) in 0.215476999 sec. (56507.191284950095 rows/sec., 165.55 KiB/sec.)[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.264307 [ 377 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> Aggregator: Merging aggregated data..................................完成聚合計算，返回最終結果.................................................┌─interceptLackQty─┐│ 563 │└──────────────────┘...................................數據處理耗時，速度，信息展示................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.265490 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Information> executeQuery: Read 73645604 rows, 1.20 GiB in 0.229100749 sec., 321455099 rows/sec., 5.22 GiB/sec.[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.265551 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> MemoryTracker: Peak memory usage (for query): 60.37 MiB.1 rows in set. Elapsed: 0.267 sec. Processed 73.65 million rows, 1.28 GB (276.03 million rows/s., 4.81 GB/s.)
現在分析下，從上述日誌中能夠拿到什麼信息，首先該查詢語句沒有使用主鍵索引，具體信息如下
2022.02.17 21:21:54.038239 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} wms.wms_order_sku_local (SelectExecutor): Key condition: unknown, unknown, and, unknown, unknown, and, and, unknown, unknown, and, and
同樣也沒有使用分區索引，具體信息如下
2022.02.17 21:21:54.038271 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} wms.wms_order_sku_local (SelectExecutor): MinMax index condition: unknown, unknown, and, unknown, unknown, and, and, unknown, unknown, and, and
此次查詢一共掃描36個parts，9390個MarkRange，通過查詢system.parts系統分區信息表發現當前表一共擁有36個活躍的分區，相當於全表掃描。
2022.02.17 21:44:58.012832 [ 1138 ] {f1561330-4988-4598-a95d-bd12b15bc750} wms.wms_order_sku_local (SelectExecutor): Selected 36 parts by date, 36 parts by key, 9390 marks by primary key, 9390 marks to read from 36 ranges
此次查詢總共讀取了73645604 行數據，這個行數也是這個表的總數據行數，讀取耗時0.229100749s，共讀取1.20GB的數據。
2022.02.17 21:21:54.265490 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} executeQuery: Read 73645604 rows, 1.20 GiB in 0.229100749 sec., 321455099 rows/sec., 5.22 GiB/sec.
此次查詢語句消耗的記憶體最大為60.37MB
2022.02.17 21:21:54.265551 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} MemoryTracker: Peak memory usage (for query): 60.37 MiB.
最後彙總了下信息，此次查詢總共耗費了0.267s，處理了7365W數據，共1.28GB，並且給出了數據處理速度。
1 rows in set. Elapsed: 0.267 sec. Processed 73.65 million rows, 1.28 GB (276.03 million rows/s., 4.81 GB/s.)
通過上述可以發現兩點嚴重問題
- 沒有使用主鍵索引：導致全表掃描
- 沒有使用分區索引：導致全表掃描
所以需要再查詢條件上添加主鍵欄位或者分區索引來進行優化。
shipmentOrderCreateTime為分區鍵，在添加這個條件後再看下效果。
通過分析日誌可以看到沒有使用主鍵索引，但是使用了分區索引，掃描分片數為6，MarkRange 186，共掃描1409001行數據，使用記憶體40.76MB，掃描數據大小等大幅度降低節省大量伺服器資源，並且提升了查詢速度，0.267s降低到0.18s。
9.2 建表優化
9.2.1 儘量不使用Nullable類型
從實踐上看，設置成Nullable對性能影響也沒有多大，可能是因為我們數據量比較小。不過官方已經明確指出儘量不要使用Nullable類型，因為Nullable欄位不能被索引，而且Nullable列除了有一個存儲正常值的文件，還會有一個額外的文件來存儲Null標記。
Using Nullable almost always negatively affects performance, keep this in mind when designing your databases.
CREATE TABLE test_Nullable( orderNo String, number Nullable(Int16), createTime DateTime) ENGINE = MergeTree()PARTITION BY createTimeORDER BY (orderNo)PRIMARY KEY (orderNo);
上述建表語句為例，number 列會生成number.null.*兩個額外文件，占用額外存儲空間，而orderNo列則沒有額外的null標識的存儲文件。
我們實際應用中建表，難免會遇到這種可能為null的欄位，這種情況下可以使用不可能出現的一個值作為預設值，例如將狀態欄位都是0及以上的值，那麼可以設置為-1為預設值，而不是使用nullable。
9.2.2 分區粒度
分區粒度根據業務場景特性來設置，不宜過粗也不宜過細。我們的數據一般都是按照時間來嚴格劃分，所以都是按天、按月來劃分分區。如果索引粒度過細按分鐘、按小時等劃分會產生大量的分區目錄，更不能直接PARTITION BY create_time ，會導致分區數量驚人的多，幾乎每條數據都有一個分區會嚴重的影響性能。如果索引粒度過粗，會導致單個分區的數據量級比較大，上面7.2節的問題和索引粒度也有關係，按月分區，單個分區數據量到達500W級，數據範圍1號到18號，只查詢17號，18號兩天的數據量，但是優化按月分區，分區合併之後不得不處理不相關的1號到16號的額外數據，如果按天分區就不會產生CPU飆升的現象。所以要根據自己業務特性來創建，保持一個原則就是查詢只處理本次查詢條件範圍內的數據，不額外處理不相關的數據。
9.2.3 分散式表選擇合適的分片規則
以上文7.1中為例，分散式表選擇的分片規則不合理，導致數據傾斜嚴重落到了少數幾個分片中。沒有發揮出分散式資料庫整個集群的計算能力，而是把壓力全壓在了少部分機器上。這樣整體集群的性能肯定是上不來的，所以根據業務場景選擇合適的分片規則，比如我們將sipHash64(warehouseNo)優化為sipHash64(docId)，其中docId是業務上唯一的一個標識。
9.3 性能測試，對比優化效果
在聊查詢優化之前先說一個小工具，clickhouse提供的一個clickhouse-benchmark性能測試工具，環境和前文提到的一樣通過docker搭建CK環境，壓測參數可參考官方文檔，這裡我舉一個簡單的單併發測試示例。
clickhouse-benchmark -c 1 -h 鏈接地址 --port 埠號 --user 賬號 --password 密碼 <<< "具體SQL語句"
通過這種方式可以瞭解SQL級別的QPS和TP99等信息，這樣就可以測試語句優化前後的性能差異。
9.4 查詢優化
9.4.1 條件聚合函數降低掃描數據行數
假設一個介面要統計某天的”入庫件量”，”有效出庫單量”，”覆核件量”。
-- 入庫件量select sum(qty) from table_1 final prewhere type = 'inbound' and dt = '2021-01-01';-- 有效出庫單量select count(distinct orderNo) final from table_1 prewhere type = 'outbound' and dt = '2021-01-01' where and status = '1' ;-- 覆核件量select sum(qty) from table_1 final prewhere type = 'check' and dt = '2021-01-01';
一個介面出三個指標需要上述三個SQL語句查詢table_1 來完成，但是我們不難發現dt是一致的，區別在於type和status兩個條件。假設dt = ‘2021-01-1’ 每次查詢需要掃描100W行數據，那麼一次介面請求將會掃描300W行數據。通過條件聚合函數優化後將三次查詢改成一次，那麼掃描行數將降低為100W行，所以能極大的節省集群的計算資源。
select sumIf(qty, type = 'inbound'), -- 入庫件量countIf(distinct orderNo, type = 'outbound' and status = '1'), -- 有效出庫單量sumIf(qty, type = 'check') -- 覆核件量prewhere dt = '2021-01-01';
條件聚合函數是比較靈活的，可根據自己業務情況自由發揮，記住一個宗旨就是減少整體的掃描量，就能到達提升查詢性能的目的。
9.4.2 二級索引
MergeTree 系列的表引擎可以指定跳數索引。
跳數索引是指數據片段按照粒度(建表時指定的index_granularity)分割成小塊後，將granularity_value數量的小塊組合成一個大的塊，對這些大塊寫入索引信息，這樣有助於使用where篩選時跳過大量不必要的數據，減少SELECT需要讀取的數據量。
CREATE TABLE table_name( u64 UInt64, i32 Int32, s String, ... INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3, INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4) ENGINE = MergeTree()...
上例中的索引能讓 ClickHouse 執行下麵這些查詢時減少讀取數據量。
SELECT count() FROM table WHERE s < 'z'SELECT count() FROM table WHERE u64 * i32 == 10 AND u64 * length(s) >= 1234
支持的索引類型
- minmax：以index granularity為單位，存儲指定表達式計算後的min、max值；在等值和範圍查詢中能夠幫助快速跳過不滿足要求的塊，減少IO。
- set(max_rows)：以index granularity為單位，存儲指定表達式的distinct value集合，用於快速判斷等值查詢是否命中該塊，減少IO。
- ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)：將string進行ngram分詞後，構建bloom filter，能夠優化等值、like、in等查詢條件。
- tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)： 與ngrambf_v1類似，區別是不使用ngram進行分詞，而是通過標點符號進行詞語分割。
- bloom_filter([false_positive])：對指定列構建bloom filter，用於加速等值、like、in等查詢條件的執行。
創建二級索引示例
Alter table wms.wms_order_sku_local ON cluster default ADD INDEX belongProvinceCode_idx belongProvinceCode TYPE set(0) GRANULARITY 5;Alter table wms.wms_order_sku_local ON cluster default ADD INDEX productionEndTime_idx productionEndTime TYPE minmax GRANULARITY 5;
重建分區索引數據：在創建二級索引前插入的數據，不能走二級索引，需要重建每個分區的索引數據後才能生效
-- 拼接出所有數據分區的MATERIALIZE語句select concat('alter table wms.wms_order_sku_local on cluster default ', 'MATERIALIZE INDEX productionEndTime_idx in PARTITION '||partition_id||',')from system.partswhere database = 'wms' and table = 'wms_order_sku_local'group by partition_id-- 執行上述SQL查詢出的所有MATERIALIZE語句進行重建分區索引數據
9.4.3 final替換argMax進行去重
對比下final和argMax兩種方式的性能差距，如下SQL
-- final方式select count(distinct groupOrderCode), sum(arriveNum), count(distinct sku) from tms.group_order final prewhere siteCode = 'WG0001544' and createTime >= '2022-03-14 22:00:00' and createTime <= '2022-03-15 22:00:00' where arriveNum > 0 and test <> '1'-- argMax方式select count(distinct groupOrderCode), sum(arriveNumTemp), count(distinct sku) from (select argMax(groupOrderCode,version) as groupOrderCode, argMax(arriveNum,version) as arriveNumTemp, argMax(sku,version) as sku from tms.group_order prewhere siteCode = 'WG0001544' and createTime >= '2022-03-14 22:00:00' and createTime <= '2022-03-15 22:00:00' where arriveNum > 0 and test <> '1' group by docId)
final方式的TP99明顯要比argMax方式優秀很多
9.4.4 prewhere替代where
ClickHouse的語法支持了額外的prewhere過濾條件，它會先於where條件進行判斷，可以看做是更高效率的where，作用都是過濾數據。當在sql的filter條件中加上prewhere過濾條件時，存儲掃描會分兩階段進行，先讀取prewhere表達式中依賴的列值存儲塊，檢查是否有記錄滿足條件，在把滿足條件的其他列讀出來，以下述的SQL為例，其中prewhere方式會優先掃描type，dt欄位，將符合條件的列取出來，當沒有任何記錄滿足條件時，其他列的數據就可以跳過不讀了。相當於在Mark Range的基礎上進一步縮小掃描範圍。prewhere相比where而言，處理的數據量會更少，性能會更高。看這段話可能不太容易理解，
-- 常規方式select count(distinct orderNo) final from table_1 where type = 'outbound' and status = '1' and dt = '2021-01-01';-- prewhere方式select count(distinct orderNo) final from table_1 prewhere type = 'outbound' and dt = '2021-01-01' where and status = '1' ;
上節我們說了使用final進行去重優化。通過final去重，並且使用prewhere進行查詢條件優化時有個坑需要註意，prewhere會優先於final進行執行，所以對於status這種值可變的欄位處理過程中，能夠查詢到中間狀態的數據行，導致最終數據不一致。
如上圖所示，docId:123_1的業務數據，進行三次寫入，到version=103的數據是最新版本數據，當我們使用where過濾status這個可變值欄位時，語句1，語句2結果如下。
--語句1：使用where + status=1 查詢，無法命中docId:123_1這行數據select count(distinct orderNo) final from table_1 where type = 'outbound' and dt = '2021-01-01' and status = '1';--語句2：使用where + status=2 查詢，可以查詢到docId:123_1這行數據select count(distinct orderNo) final from table_1 where type = 'outbound' and dt = '2021-01-01' and status = '2';
當我們引入prewhere後，語句3寫法：prewhere過濾status欄位時將status=1，version=102的數據會過濾出來，導致我們查詢結果不正確。正確的寫法是語句2，將不可變欄位使用prewhere進行優化。
-- 語句3：錯誤方式，將status放到prewhereselect count(distinct orderNo) final from table_1 prewhere type = 'outbound' and dt = '2021-01-01' and status = '1';-- 語句4：正確prewhere方式，status可變欄位放到where上select count(distinct orderNo) final from table_1 prewhere type = 'outbound' and dt = '2021-01-01' where and status = '1' ;
其他限制：prewhere目前只能用於MergeTree系列的表引擎
9.4.5 列裁剪，分區裁剪
ClickHouse 非常適合存儲大數據量的寬表，因此我們應該避免使用 SELECT * 操作，這是一個非常影響的操作。應當對列進行裁剪，只選擇你需要的列，因為欄位越少，消耗的 IO 資源就越少，從而性能就越高。
而分區裁剪就是只讀取需要分區，控制好分區欄位查詢範圍。
9.4.6 where、group by 順序
where和group by中的列順序，要和建表語句中order by的列順序統一，並且放在最前面使得它們有連續不間斷的公共首碼，否則會影響查詢性能。
-- 建表語句create table group_order_local( docId String, version UInt64, siteCode String, groupOrderCode String, sku String, ... 省略非關鍵欄位 ... createTime DateTime) engine = ReplicatedReplacingMergeTree('/clickhouse/tms/group_order/{shard}', '{replica}', version)PARTITION BY toYYYYMM(createTime)ORDER BY (siteCode, groupOrderCode, sku);--查詢語句1select count(distinct groupOrderCode) groupOrderQty, ifNull(sum(arriveNum),0) arriveNumSum,count(distinct sku) skuQtyfrom tms.group_order finalprewhere createTime >= '2021-09-14 22:00:00' and createTime <= '2021-09-15 22:00:00'and siteCode = 'WG0000709'where arriveNum > 0 and test <> '1'--查詢語句2 （where/prewhere中欄位）select count(distinct groupOrderCode) groupOrderQty, ifNull(sum(arriveNum),0) arriveNumSum,count(distinct sku) skuQtyfrom tms.group_order finalprewhere siteCode = 'WG0000709' and createTime >= '2021-09-14 22:00:00' and createTime <= '2021-09-15 22:00:00'where arriveNum > 0 and test <> '1'
建表語句 ORDER BY (siteCode, groupOrderCode, sku)，語句1沒有符合要求經過壓測QPS6.4，TP99 0.56s，語句2符合要求經過壓測QPS 14.9，TP99 0.12s
10 如何抗住高併發、保證ClickHouse可用性
1）降低查詢速度，提高吞吐量
max_threads：位於 users.xml 中，表示單個查詢所能使用的最大 CPU 個數，預設是 CPU 核數，假如機器是32C，則會起32個線程來處理當前請求。可以把max_threads調低，犧牲單次查詢速度來保證ClickHouse的可用性，提升併發能力。可通過jdbc的url來配置
下圖是基於32C128G配置，在保證CK集群能夠提供穩定服務CPU使用率在50%的情況下針對max_threads做的一個壓測，介面級別壓測，一次請求執行5次SQL，處理數據量508W行。可以看出max_threads越小，QPS越優秀TP99越差。可根據自身業務情況來進行調整一個合適的配置值。
2）介面增加一定時間的緩存
3）非同步任務執行查詢語句，將聚合指標結果落到ES中，應用查詢ES中的聚合結果
4）物化視圖，通過預聚合方式解決這種問題，但是我們這種業務場景不適用
11 資料集合
•更改ORDER BY欄位，PARTITION BY，備份數據，單表遷移數據等操作
•基於docker搭建clickhouse-client鏈接ck集群
作者：京東物流 馬紅岩
內容來源：京東雲開發者社區
