編者按
ClickHouse自從2016年開源以來便備受關注，主要應用於資料分析（OLAP）領域，各個大廠紛紛跟進大規模使用。百分點科技在某國家級專案建設中完成了多資料中心的ClickHouse叢集建設，日增千億資料量，在此基礎上進行最佳化與效能調優，能夠更好地解決部署規模擴大和資料量擴容等問題。本文結合專案的資料規模及業務場景，重點介紹了百分點大資料技術團隊在ClickHouse國家級專案建設中的效能最佳化實踐。
一、概覽
2020年4月，百分點大資料技術團隊結合某國家級多資料中心的Clickhouse叢集建設，發表了“ClickHouse國家級專案最佳實踐”，該文介紹了Clickhouse的特點、適用場景與核心概念，以及百分點科技在大規模Clickhouse叢集建設與運營方面的實踐經驗。為部署到更多的城市，解決當前城市資料量擴容問題，Clickhouse叢集的設計最佳化與效能調優成為重點工作：
資料量增加了5倍多：資料量達到每天千億的級別；
查詢週期更長：即時查詢週期從7天增加到30天；
資料種類更多：業務資料型別增加了1倍；
叢集規模增加了400多臺，聚合節點壓力變大；
聚合查詢值列舉空間大：部分欄位的值列舉空間在幾百萬和幾十億之間，聚合可能記憶體溢位。
在做了大規模的測試後，我們發現沿用叢集版本和規劃不能滿足需求。Clickhouse的很多新特性V1。1。5版本都不支援。經過一系列的版本對比測試，叢集規劃變更及引數調優，成功將版本升級到V20。1。16，同時也升級了Clickhouse指標採集與監控系統，滿足了資料規模的增長和業務需求的變化。整個升級涉及很多內容，本文僅就Clickhouse效能調優實踐進行重點介紹。
二、
調優思路
Clickhouse效能主要由CPU、IO、Memory驅動，無論是索引最佳化、列編碼與壓縮演算法，還是配置的調優，主要是圍繞著這三個方面進行。索引改變了資料儲存和讀取的順序，編碼和壓縮改變資料儲存與傳輸的大小，服務配置影響著ClickHouse的資源分配和執行行為。
首先透過版本升級，利用ClickHouse的新特性來提升單條SQL的查詢速度，如採用跳數索引、列編碼及新的壓縮演算法，這些都是在新版本中出的新特性。
其次，改變了整個叢集的寫入方式，在資料寫入的時候對叢集按照業務概念進入邏輯劃分，減少了叢集寫入壓力。
三、實踐經驗
1.
主鍵的選擇
1.1
生產中如何建主鍵索引
實踐中，時間對於業務是必查欄位，因此選用時間欄位作為主鍵，同時將幾個重要欄位也加入了主鍵。總體來說，ClickHouse索引的長度沒有明確的限制，需要根據實際業務和資料的結構來綜合考慮。
提升查詢效能
加在索引中的列如果能跳過比較長的一段資料，則能很好的提升查詢效能。
提高資料壓縮率
根據主鍵排序的時候，資料一致性越高，壓縮比越高，這兩點在下面的測試用例中會很明顯的看到。另外，較長的索引會影響索引的記憶體使用量，不過對於目前普遍的大資料機器配置來說，以及ClickHouse稀疏索引的特性，四五個欄位的長索引，對記憶體的佔用還是很有限的。
1.2
測試表結構
在專案建設前期，表的主鍵基本上只有業務時間一個欄位，於是我們做了下面的測試，使用其他比較重要的欄位作為主鍵開頭。
下面四張測試表欄位一致，只有主鍵不同。其中表2和表3資料一致，各有70億資料；表28和表29資料一致，各有643575000資料，為了避免分割槽part數量的影響，所有表的分割槽都merge比較徹底，測試表的分割槽數也一致。
1.6
看上去log_local29的壓縮效果更好，但是為什麼不用這種組合索引呢？
實際業務中是多欄位開放式查詢，found_time是必查欄位，無論查什麼欄位，都要帶上found_time欄位，使用s_city開頭的主鍵看上去這幾個欄位得到極大的最佳化效果，但是在不查詢這幾個欄位的時候，會因為found_time沒有得到較好的最佳化而查詢效果變差。
如果是要對每天的資料做類似於數倉的ETL，原始資料層使用這種索引組合則會因為降低比較多的儲存而好很多。
2.
跳數索引
2.1 ClickHouse
跳數索引型別
生產中，主要是使用MergeTree系列的引擎，目前，MergeTree系列引擎共支援5種跳數索引，分別是minmax、set和ngrambf_v1和tokenbf_v1、bloom_filter。實際生產中我們只選用了bloom_filter跳數索引，這個是由百分點科技實際業務的資料分佈所決定的，後面會分析為什麼只用了bloom_filter索引。
在生產中只對列舉值比較多的欄位用了bloom_filter跳數索引，其他索引沒有使用，因為bloom_filter的索引檔案不至於太大，同時對於值比較多的列又能起到比較好的過濾效果。因為經過測試發現對於列舉值較少的欄位，不建索引查詢速度就已經很快，因為列舉少，本身在壓縮的時候壓縮比很高，讀取速度很快，但是建了索引之後，要麼不能過濾掉資料，要麼確實過濾掉一部分資料，但是因為讀取的資料塊不一致又會導致從原先的大塊讀取的順序IO退化為隨機IO，反而得不償失。而列舉值較多的情況比如ip欄位，在使用ngrambf_v1索引的時候，ngram size和bloom filter大小的選擇對索引的大小和效果影響很大。
另外，透過上面的表格也能看出來，有些欄位的bloom_filter跳數索引還是非常佔用儲存的，查詢的時候跳數索引的IO也需要考慮。因此，實際中才會出現日誌中確實過濾掉了資料，但是查詢速度反而慢了的情況。最後，選擇什麼索引以及索引的引數怎麼設定，都要結合業務資料的分佈特點也進行，資料的排序方式、雜湊程度都會影響索引效果。
在生產中，我們調整了一些欄位的型別，有的從String變為Int，有的從較大取值範圍的型別調整為較小範圍的型別；還有一些不常用和壓縮後size非常大的欄位採取了較高的壓縮演算法；對於一些列舉值較少的String使用了LowCardinality（String），但由於這個特性在所用版本中有bug，因此在生產中改成了String。
4.
資料分割槽
4.1
資料分割槽規則
不指定分割槽鍵
如果建表時不指定分割槽鍵，則資料預設不分割槽，所有資料寫到一個預設分割槽all裡面。
ClickHouse的聚合過程大致如下圖（這裡只畫了資料返回流程）。在ClickHouse的聚合查詢中，每個機器都會把自己的聚合的中間狀態返回給分散式節點，也就是說，即使你只是想要Top100，每臺機器也會把自己所擁有的所有列舉值都返回給分散式節點進行進一步的聚合。
返回給分散式節點22條資料，分散式節點對著22條資料進行進一步的聚合，最後得出想要的結果。
再看兩個查詢的不同之處，能明顯看出查詢處理的資料量不一樣，原查詢直接查詢原表15。5億的資料，使用物化檢視後，物化檢視查詢的是存在自己視圖表裡的資料，資料重組後的資料量差異是兩者查詢速度差異的根本原因。
6.4
物化檢視在實踐中的使用
因為業務查詢條件的不固定，所以在實際業務中沒有使用物化檢視，不過在對ClickHouse的監控中使用到了，比如每天新增、每小時新增資料量，每天的流量情況以及慢SQL的查詢等，這裡就不詳細展開了。
總結與展望
透過調優，在生產環境中，95%的查詢小於1S響應、98%查詢小於3秒響應、99%查詢小於5秒響應。
文中的最佳化思路和方法總體上非常的通用，但具體怎麼設計和使用需要結合實際的業務場景來進行。比如，在百分點科技專案中資料的儲存是隨機寫到一定數量機器上的，在瞭解的不少公司實踐中，比較推薦的都是對資料按照一定規則進行hash進行讓相同的資料落到同一臺機器上，我們沒有這樣做，主要有三點原因：一是資料從源頭進來的時候就沒有進行hash，二是如果在資料流中進行hash會大大的降低資料流的處理速度，三是資料傾斜問題。
另外，對於專案中存在的一些問題我們也進行了反思，比如選用的版本，還不是很穩定和比較優的版本，選擇一個版本需要花費時間和精力進行各種測試，這個版本也只是在當時匆忙的時間與多個版本中綜合妥協的選擇。現在，ClickHouse的大版本已經更新到21。X，在此版本後，又出現了很多新功能，比如對於小表的wide part功能、SQL的explain功能、mysql同步資料到ClickHouse、開窗函式，以及使用S3等作為ClickHouse底層儲存進行存算分離等，需要學習和實踐的還有很多。
文章的最後，就以開窗函式作為彩蛋吧！
開窗函式
開窗函式這個功能在21。3中出現實驗性功能，使用這個功能需要設定allow_experimental_window_functions
= 1。
例項：求過去10分鐘每分鐘都出現並連續出現3次的活躍ip。
可以看出，使用開窗函式後不但SQL簡潔不少，執行速度也快很多。開窗函式對於數倉的朋友來說，簡直就是福音。
說明：
文中所做測試用的均是測試資料，與生產會有一定偏差，但總體不影響本文中的相關結論；測試機器使用的RAID5，具體IO行為與SSD會有差別。