基於ClickHouse解決活動海量資料問題

1、背景

魔笛活動平臺要記錄每個活動的使用者行為資料，幫助客服、運營、產品、研發等快速處理客訴、解決線上問題並進行相關資料分析和報警。可以預見到需要儲存和分析海量資料，預估至少幾十億甚至上百億的資料量，所以需要選擇一款能儲存海量資料的資料庫。由於是通過接收MQ儲存或者API方式儲存，所以對實時寫入效能也有一定要求。同時可能後續還需要一些實時資料分析等。這裡總結一下需求點：

1.可以儲存海量資料；

2.寫入效能好；

3.可以進行實時計算分析；

4.查詢效能最好不要太差。

2、技術選型

2.1 MySQL單表

MySQL資料庫我們是算用得最多了。但眾所周知，MySQL是單機的。MySQL能儲存多少資料，取決於那臺伺服器的硬碟大小。很多時候MySQL是沒法儲存那麼多資料的，根據行記錄頭資訊、可變欄位列表、事務ID、指標欄位、欄位內容資訊等不同儲存量極限也會不同，資料儲存量範圍為一百多萬條到將近5億條資料，業界公認MySQL單表容量在1KW量級是最佳狀態，這個感興趣的可以自己去看看，這裡就不再贅述，肯定不能儲存幾十億條資料，所以MySQL單表不適合。

2.2 MySQL分庫分表

分庫分表確實可以儲存更多的資料量，分散式事務和非同步複製等技術也進一步提高了寫入效能和資料的可靠性，將資料分散到多個物理伺服器或表中，減少單個伺服器或表的負擔，查詢效能也還可以，支援線上事務處理。但是有以下不足：

1.MySQL儲存資料耗費的資源更多，當資料量達到幾十億時候，如果每列欄位都加上索引，索引佔用空間的比例甚至超過資料本身的儲存空間；；

2.線上分析處理能力一般。因為線上分析處理（OLAP）則需要進行復雜的查詢和分析，通常需要使用聚合函數等操作，這些操作會涉及到大量的資料讀取和計算，因此需要大量的計算資源和記憶體空間，在MySQL分庫分表中，資料被分散儲存在多個節點上，查詢資料需要通過網路進行資料的傳輸和計算，這會導致查詢速度的降低和延遲的增加；

3.由於資料的分散儲存，需要進行多個節點的資料聚合和計算，這也會增加系統的負擔和延遲；

4.同時，這也導致當資料量達到幾十億時候，查詢效能也會受到很大影響。

所以MySQL分庫分表不太適合。

2.3 Elasticsearch

Elasticsearch是一個分散式的搜尋引擎，並採用資料分片和高可用性等技術，可以儲存海量資料。採用倒排索引的方式儲存資料，可以快速檢索資料。也可以進行實時資料分析。但是有以下不足：

1.由於分詞等特性，寫吞吐量上有著明顯的瓶頸，;

2.分詞會增加寫入操作的延遲和負載;

3.熱點問題比較難解決。如果資源冗餘不足，就會導致穩定性下降，資料寫入會發生延遲;

4.當資料量增加時，Elasticsearch 的查詢速度會變慢，因為它必須掃描整個索引才能找到符合查詢條件的資料;

5.壓縮率不高，儲存成本也比較高；

2.4 Hbase

HBase是基於HDFS分散式檔案系統去構建的，叢集的管理基於 ZooKeeper 實現，設計是為了海量資料的快速儲存和隨機存取，列式儲存也減少資料的讀取量。列族設計、MemStore 快取、批次寫入、資料壓縮等使其寫入效能也非常優秀。但是有以下不足：

1.它並不是一個實時計算和資料分析的框架；

1. 資料儲存方式問題：HBase的資料儲存方式是以列族和列的方式儲存資料，這種方式適合儲存結構化資料，但是在儲存非結構化資料時效率較低。在實時資料分析場景下，資料可能是半結構化或者非結構化的，這種資料儲存在HBase中需要進行額外的處理，導致效率下降；

2. RowKey的設計對查詢也有一定限制，所以Hbase不太適合；

3. 資料讀取效率問題：HBase的資料讀取方式是通過掃描整個表或者通過索引查詢特定行來實現的，這種方式在處理大量資料時效率較低，尤其是在實時資料分析場景下，需要快速響應使用者的查詢請求，但是HBase的讀取速度無法滿足這個需求。

2.5 ClickHouse

ClickHouse的特點是高速、可延伸、高效、低成本，它可以適應各種資料儲存和處理需求，包括線上分析處理（OLAP）、實時資料分析、資料倉儲、紀錄檔分析等場景。它支援SQL語言和多種資料格式，包括CSV（逗號分隔值）、JSON、XML等，並且可以通過JDBC、ODBC和HTTP等協定進行存取。ClickHouse的效能非常出色，可以在秒級別內處理數十億條資料，而且它支援資料壓縮和分割區等功能，可以大大降低儲存和查詢成本。基於以上特性，選擇ClickHouse來儲存、查詢和分析資料。

3、ClickHouse詳細介紹

3.1 來源

ClickHouse是俄羅斯的搜尋巨頭Yandex公司開發的面向列式儲存的關係型資料庫（DBMS），於2016年開源,使用C++編寫的，主要用於線上分析處理查詢（OLAP）,能夠使用SQL查詢實時生成分析資料包告。ClickHouse 是過去兩年中 OLAP 領域中最熱門的。

ClickHouse的初始設計目的是為了服務於自己公司的一款名叫Yandex.Metrica的產品。Metrica是一款Web流量分析工具，基於前方探針採集行為資料，然後進行一系列的資料分析，類似資料倉儲的OLAP分析。而在採集資料的過程中，一次頁面click(點選)，會產生一個event（事件）。所以，整個系統的邏輯就是基於頁面的點選事件流，面向資料倉儲進行OLAP分析。所以ClickHouse的全稱是Click Stream（點選流），Data WareHouse（資料倉儲），簡稱ClickHouse。

3.2 架構

ClickHouse則採用Multi-Master多主架構，叢集中每個角色對等，使用者端存取任意一個節點都能得到相同的效果。

Single-Master架構對於查詢場景，部分查詢的最後階段會在Master節點上進行最終的資料處理，需要消耗一定的CPU以及記憶體資源。對於寫入場景，大量的實時插入、更新、刪除的需要高效能保證。同時並行連線數很大的情況Single-Master結構也較難處理。

Multi-Master通過水平擴充套件Master節點突破了原架構單Master的限制，配合Segment節點（計算節點）的彈性，系統整體能力尤其是連線數及讀寫效能得到進一步提升，更好地滿足實時數倉及HTAP等業務場景的需求。

叢集部署架構：

在每個節點建立一個資料表，作為一個資料分片，使用ReplicatedMergeTree表引擎實現資料副本，而分佈表作為資料寫入和查詢的入口。

3.3 特點

3.3.1 列式儲存

首先我們先看行儲存：

按行儲存的時候，一行記錄的屬性值儲存在臨近的空間，然後接著是下一條記錄的屬性值。好處是想查某個人所有的屬性時，可以通過一次磁碟查詢加順序讀取就可以。但是當想查所有人的年齡時，需要不停的查詢，或者全表掃描才行，遍歷的很多資料都是不需要的，大量磁碟轉動定址的操作使得讀取效率大大降低。

id	name	dept
1	張三	A
2	李四	B
3	王五	A

資料在磁碟上是以行的形式儲存在磁碟上，同一行的資料緊挨著存放在一起。由於 dept 的值是離散地儲存在磁碟中，在查詢過程中，需要磁碟轉動多次，才能完成資料的定位和返回結果。

列儲存：

對於 OLAP 場景，一個典型的查詢需要遍歷整個表，進行分組、排序、聚合等操作，這樣一來行式儲存中把一整行記錄存放在一起的優勢就不復存在了。而且，分析型 SQL 常常不會用到所有的列，而僅僅對其中某些需要的的列做運算，那一行中無關的列也不得不參與掃描。

id	1	2	3
name	張三	李四	王五
dept	A	B	A

然而在列式儲存中，而按列儲存的時候，單個屬性所有的值儲存在臨近的的空間，即一列的所有資料連續儲存的，每個屬性有不同的空間。由於同一列的資料被緊挨著存放在了一起，那麼基於需求欄位查詢和返回結果時，就不許對每一行資料進行掃描，按照列找到需要的資料，磁碟的轉動次數少，效能也會提高。

列的組成都是靈活的，行與行之間的列不需要相同。三行資料實際在CK中數一行資料。

列名	Columns
row1
row2

列式儲存優點：

1.對於列的聚合，計數，求和等統計操作要優於行式儲存。

2.由於某一列的資料型別都是相同的，針對於資料儲存更容易進行資料壓縮，每一列選擇更優的資料壓縮演演算法，大大提高了資料的壓縮比重。

3.由於資料壓縮比更好，一方面節省了磁碟空間，另一方面對於cache也有了更大的發揮空間。

3.3.2 完備的DBMS功能

ClickHouse擁有完備的管理功能，所以它是真正的列式資料庫管理系統，而不僅是一個資料庫。作為一個DBMS，它具備了一些基本功能。

1.DDL ( 資料定義語言 )：可以動態地建立、修改或刪除資料庫、表和檢視，而無須重啟服務。

2.DML ( 資料操作語言 )：可以動態查詢、插入、修改或刪除資料。

3.許可權控制：可以按照使用者粒度設定資料庫或者表的操作許可權，保障資料的安全性。

4.資料備份與恢復：提供了資料備份匯出與匯入恢復機制，滿足生產環境的要求。

5.分散式管理：提供叢集模式，能夠自動管理多個資料庫節點。

3.3.3 資料壓縮

ClickHouse資料預設使用LZ4演演算法壓縮，它使用374臺伺服器的叢集，儲存了20.3萬億行的資料。在去除重複與副本資料的情況下，壓縮後的資料達到了2PB，未壓縮前（TSV格式）大概有17PB，資料總體的壓縮比可以達到8:1。

ClickHouse採用列式儲存，列式儲存相對於行式儲存另一個優勢就是對資料壓縮的友好性。例如：有兩個字串「ABCDE」，「BCD」，現在對它們進行壓縮：

壓縮前：ABCDE_BCD 壓縮後：ABCDE_(5,3)

通過上面例子可以看到，壓縮的本質是按照一定步長對資料進行匹配掃描，當發現重複部分的時候就進行編碼轉換。例如：(5,3)代表從下劃線往前數5個位元組，會匹配上3個位元組長度的重複項，即:「BCD」。當然，真實的壓縮演演算法比以上舉例更復雜，但壓縮的本質就是如此，資料中重複性項越多，則壓縮率越高，壓縮率越高，則資料體量越小，而資料體量越小，則資料在網路中的傳輸越快，對網路頻寬和磁碟IO的壓力也就越小。 列式儲存中同一個列的資料由於它們擁有相同的資料型別和現實語意，可能具備重複項的可能性更高，更利於資料的壓縮。所以ClickHouse在資料壓縮上比例很大。

壓縮必然帶來壓縮和解壓縮的CPU消耗，這是一個利用CPU時間換I/O時間的手段。事務資料庫由於大部分情況下是針對行的操作，因此如果對每一行都進行一次壓縮解壓縮，帶來的時間消耗是遠大於磁碟I/O時間的。這就是事務資料庫沒有使用壓縮技術的原因。而ClickHouse則不同，ClickHouse的最小處理單元是塊，塊一般由8192行資料組成，ClickHouse的一次壓縮針對的是8192行資料，這就極大降低CPU的壓縮和解壓縮時間。同時，ClickHouse是列存資料庫，同一列的資料相對更有規律，因此能夠帶來比較大的壓縮比。因此，塊+壓縮在ClickHouse中成為一個非常關鍵的優化手段。

3.3.4 向量化執行引擎

向量化執行，可以簡單地看作一項消除程式中迴圈的優化。

我們看下面一個簡單的程式碼：

for (size_t i = 0; i < 100; ++i) 
    c[i] = a[i] + b[i];

這個程式碼會迴圈100次，將a和b陣列對應下標的數位相加如何賦值給c，那麼如何加速這樣的計算呢，一個樸素的想法就是寫出如下的程式碼，這是非向量化執行的方式：

c[0] = a[0] + b[0]; 
c[1] = a[1] + b[1];
... ... 

向量化執行的方式就是並行執行一次。

為了實現向量化執行，需要利用CPU的SIMD指令。SIMD的全稱是Single Instruction Multiple Data，即用單條指令操作多條資料。現代計算機系統概念中，它是通過資料並行以提高效能的一種實現方式（其他的還有指令級並行和執行緒級並行），它的原理是在CPU暫存器層面實現資料的並行操作。

如果這時候CPU也可以並行的計算我們寫的程式碼，那麼理論上我們的處理速度就會是之前程式碼的100倍，幸運的是SIMD指令就是完成這樣的工作的，用SIMD指令去完成這樣程式碼設計和執行就叫做向量化。

從上圖中可以看到，CPU、CPU三級快取、記憶體、磁碟資料容量與資料讀取速度對比，從左向右，距離CPU越遠，則資料的存取速度越慢。從暫存器中存取資料的速度，是從記憶體存取資料速度的300倍，是從磁碟中存取資料速度的3000萬倍。所以利用CPU向量化執行的特性，對於程式的效能提升意義非凡。

ClickHouse提供了很多內建函數，在使用這些內建函數時，ClickHouse會自動進行向量化優化。因此儘可能使用提供的內建函數進行計算，而不是自己寫SQL語句。下面展示錯誤的SQL寫法以及正確的寫法。

SELECT (2/(1.0 + exp(-2 * x))-1) as tanh_x …… // 錯誤的寫法
SELECT tanh(x) as tanh_x …… // 正確的寫法，直接使用ClickHouse的內建函數

3.3.5 資料分片與分散式查詢

資料分片是將資料進行橫向切分，這是一種在面對海量資料的場景下，解決儲存和查詢瓶頸的有效手段，是一種分治思想的體現。ClickHouse支援分片，而分片則依賴叢集。每個叢集由1到多個分片組成，而每個分片則對應了ClickHouse的1個服務節點。分片的數量上限取決於節點數量（1個分片只能對應1個服務節點）。

ClickHouse 分片可以理解為就是 ClickHouse 一個單機資料庫範例（副本節點也算），多個這種單機資料庫範例構成一個 ClickHouse 叢集。分片是指包含資料不同部分的伺服器（要讀取所有資料，必須存取所有分片）。ClickHouse 通過分片，將一張表的資料水平分割在不同的節點上，隨著業務的發展，當表資料的大小增加到很大時，也能夠通過水平擴容， 保證資料的儲存。

ClickHouse擁有高度自動化的分片功能。ClickHouse提供了本地表 ( Local Table ) 與分散式表 ( Distributed Table ) 的概念。一張本地表等同於一份資料的分片。而分散式表本身不儲存任何資料，它是本地表的存取代理，其作用類似分庫中介軟體。藉助分散式表，能夠代理存取多個資料分片，從而實現分散式查詢。簡單理解，Distributed 表引擎只是你真實資料表（本地表）的代理，在進行資料查詢時，它會將查詢請求傳送到各個分片上，結合索引（如果有），並行進行查詢計算，最終將結果進行合併，返回到 Client。

這種設計類似資料庫的分庫和分表，十分靈活。例如在業務系統上線的初期，資料體量並不高，此時資料表並不需要多個分片。所以使用單個節點的本地表（單個資料分片）即可滿足業務需求，待到業務增長、資料量增大的時候，再通過新增資料分片的方式分流資料，並通過分散式表實現分散式查詢。

3.3.6 多執行緒

向量化執行是通過資料級並行的方式提升了效能，多執行緒處理是通過執行緒級並行的方式實現了效能的提升。相比基於底層硬體實現的向量化執行SIMD，執行緒級並行通常由更高層次的軟體層面控制，目前市面上的伺服器都支援多核心多執行緒處理能力。由於SIMD不適合用於帶有較多分支判斷的場景，ClickHouse也大量使用了多執行緒技術以實現提速，以此和向量化執行形成互補。

ClickHouse在資料存取方面，既支援分割區(縱向擴充套件，利用多執行緒原理 )，也支援分片(橫向擴充套件，利用分散式原理)，可以說是將多執行緒和分散式的技術應用到了極致。

3.3.7 關係模型與標準SQL查詢

ClickHouse 和 MySQL 類似，把表級的儲存引擎外掛化，根據表的不同需求可以設定不同的儲存引擎。目前包括合併樹、紀錄檔、介面和其他四大類 20 多種引擎。

相比HBase、Redis、MongoDB這類NoSQL資料庫，ClickHouse使用關係模型描述資料並提供了傳統資料庫的概念(資料庫、表、檢視和函數等)。ClickHouse完全使用SQL作為查詢語言(支援GROUP BY、ORDER BY、JOIN、IN等大部分標準SQL)，ClickHouse提供了標準協定的SQL查詢介面，可以與第三方分析視覺化系統無縫整合對接。支援mybatis和mybatis-plus，但是mybatis-plus分頁支援還不是很友好，但是通過一定方式也可以實現。在SQL解析方面，ClickHouse是大小寫敏感，SELECT a 和 SELECT A所代表的語意不同。

3.3.8 多主架構

Spark、HBase和Elasticsearch這類分散式系統，都採用了Master-Slave主從架構，由一個管控節點作為Leader統籌全域性。而ClickHouse則採用Multi-Master多主架構，叢集中的每個節點角色對等，使用者端存取任意一個節點都能得到相同的效果。這種多主的架構有許多優勢，例如對等的角色使系統架構變得更加簡單，不用再區分主控節點、資料節點和計算節點，叢集中的所有節點功能相同。所以它天然規避了單點故障的問題。

3.4 資料型別

3.4.1 數值

Int Ranges

Int8 — [-128 : 127]

Int16 — [-32768 : 32767]

Int32 — [-2147483648 : 2147483647]

Int64 — [-9223372036854775808 : 9223372036854775807]

Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]

Int256 — [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]

推薦使用： UInt

Uint Ranges

UInt8 — [0 : 255]

UInt16 — [0 : 65535]

UInt32 — [0 : 4294967295]

UInt64 — [0 : 18446744073709551615]

UInt256 — [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]

3.4.2 浮點數

單精度浮點： Float32 4個位元組， 有效精度7

雙精度浮點： Float64 8個位元組， 有效精度16

位數超過會發生溢位；

注意： 浮點數支援正無窮(inf)、負無窮(-inf)及非數位(nan)的表示式

3.4.3 Decimal

如果需要使用更高精度運算，需要使用Decimal

Decimal32(S),Decimal64(S),Decimal128(S)

或者Decimal(P,S)

3.4.4 String 字串

可以儲存任意長度字串，包括Null

FixedString 定長字串

FixedString 與Char都是定長字串，對於字元有明確長度的場合建議使用固定長度字串；

定長申明FixedString(N)， X表示字元長度；

FixedString與Char不同的是 FixedString 使用null填充末尾字元，Char使用空格填充；

3.4.5 UUID

使用 generateUUIDv4()生成

3.4.6 時間型別

1, DateTime 日期時間，精確到秒， 如： 2021-03-01 00:00:00

2, DateTime64 日期時間，精確到亞秒， 如： 2021-03-01 00:00:00:00

3, Date 日期， 精確到天； 如： 2021-03-01

3.4.7 陣列Array

陣列裡面可以有不同型別的元素，但是型別必須相容；

型別： c1 Array(Int8) Comment '陣列example';

array(1，2.0 ，3.1)

3.4.8 列舉Enum

Enum8： 底層實際儲存： (String:Int8) Key/Value

Enum16: 底層儲存： (String:Int16) Key/Value

1， Key和Value需要保證唯一性；

2， Key可以為空，但Key和Value不可以同時為空；

欄位定義： c1 Enum('ready' = 1,'start' = 2,'success' = 3,'error' = 4) comment '列舉值舉例';

eg： INSERT INTO Enum_TB VALUES('ready')

3.4.9 CK與其他關係型資料庫型別對比

Int8 — TINYINT, BOOL, BOOLEAN, INT1

Int16 — SMALLINT, INT2.

Int32 — INT, INT4, INTEGER.

Int64 — BIGINT.

Float32 — float.

Float64 — double.

String - VARCHAR, BLOB, TEXT

FixedString - Char

DateTime - datetime

3.5 表引擎 - MergeTree引擎

在這眾多的表引擎中，最常用的是合併樹（MergeTree）表引擎及其家族系列（*MergeTree），因為只有合併樹系列的表引擎才支援主鍵索引、資料分割區、資料副本和資料取樣這些特性，同時也只有此係列的表引擎支援ALTER相關操作。

MergeTree 的主要特點為：

1.儲存的資料按主鍵排序：這樣可以建立一個小型的稀疏索引來加快資料檢索

2.支援資料分割區：資料分割區可以僅掃描指定分割區資料，提高效能

3.支援資料副本

4.支援資料取樣

3.5.1 MergeTree的建立

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    省略...
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, 省略...]

PARTITION BY [選填] ：分割區鍵，用於指定表資料以何種標準進行分割區。分割區鍵既可以是單個列欄位，也可以通過元組的形式使用多個列欄位，同時它也支援使用列表示式。如果不宣告分割區鍵，則ClickHouse會生成一個名為all的分割區。合理使用資料分割區，可以有效減少查詢時資料檔案的掃描範圍

ORDER BY [必填] ：排序鍵，用於指定在一個資料片段內，資料以何種標準排序。預設情況下主鍵（PRIMARY KEY）與排序鍵相同。排序鍵既可以是單個列欄位，例如ORDER BY CounterID，也可以通過元組的形式使用多個列欄位，例如ORDER BY（CounterID,EventDate）。當使用多個列欄位排序時，以ORDER BY（CounterID,EventDate）為例，在單個資料片段內，資料首先會以CounterID排序，相同CounterID的資料再按EventDate排序

PRIMARY KEY [選填] ：主鍵，顧名思義，宣告後會依照主鍵欄位生成一級索引，用於加速表查詢。預設情況下，主鍵與排序鍵(ORDER BY)相同，所以通常直接使用ORDER BY代為指定主鍵，無須刻意通過PRIMARY KEY宣告。所以在一般情況下，在單個資料片段內，資料與一級索引以相同的規則升序排列。與其他資料庫不同，MergeTree主鍵允許存在重複資料（ReplacingMergeTree引擎可以去重）

SAMPLE BY [選填] ：用於抽樣的表示式，可選項。如果要用抽樣表示式，主鍵中必須包含這個表示式。例如：SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。

SETTINGS：index_granularity [選填] ：index_granularity對於MergeTree而言是一項非常重要的引數，它表示索引的粒度，預設值為8192。也就是說，MergeTree的索引在預設情況下，每間隔8192行資料才生成一條索引

SETTINGS：index_granularity_bytes [選填] ：在19.11版本之前，ClickHouse只支援固定大小的索引間隔，由index_granularity控制，預設為8192。在新版本中，它增加了自適應間隔大小的特性，即根據每一批次寫入資料的體量大小，動態劃分間隔大小。而資料的體量大小，正是由index_granularity_bytes引數控制的，預設為10M(10×1024×1024)，設定為0表示不啟動自適應功能。

資料 TTL： 支援整個表資料的有效期設定和單欄位有效期設定。

3.6 索引

稠密索引：一條資料建立一條索引

稀疏索引：一段資料建立一條索引

3.6.1 一級索引

一級索引是稀疏索引，意思就是說：每一段資料生成一條索引記錄，而不是每一條資料都生成索引， 如果是每一條資料都生成索引，則是稠密索引。用一個形象的例子來說明：如果把MergeTree比作一本書，那麼稀疏索引就好比是這本書的一級章節目錄。一級章節目錄不會具體對應到每個字的位置，只會記錄每個章節的起始頁碼。

MergeTree 的主鍵使用 PRIMARY KEY 定義，待主鍵定義之後，MergeTree 會依據 index_granularity 間隔（預設 8192 行），為資料表生成一級索引並儲存至 primary.idx 檔案內。

稀疏索引的優勢是顯而易見的，它僅需使用少量的索引標記就能夠記錄大量資料的區間位置資訊，且資料量越大優勢越為明顯。以預設的索引粒度（8192）為例，MergeTree只需要12208行索引標記就能為1億行資料記錄提供索引。由於稀疏索引佔用空間小，所以primary.idx內的索引資料常駐記憶體，取用速度自然極快。

在 ClickHouse 中，一級索引常駐記憶體。總的來說：一級索引和標記檔案一一對齊，兩個 索引標記之間的資料，就是一個資料區間，在資料檔案中，這個資料區間的所有資料，生成一個壓縮資料塊。每列壓縮資料檔案，儲存每一列的資料，每一列欄位都有獨立的資料檔案，每一列都有對應的標記檔案，儲存了列壓縮檔案中資料的偏移量資訊，與稀疏索引對齊，又與壓縮檔案對應，建立了稀疏索引與資料檔案的對映關係。不能常駐記憶體，使用LRU快取策略加快其取用速度。

需要注意的是：ClickHouse 的主鍵索引與 MySQL 等資料庫不同，它並不用於去重，即便 primary key 相同的行，也可以同時存在於資料庫中。 要想實現去重效果，需要結合具體的表引擎 ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree 實現。

3.6.2 二級索引

二級索引：又稱之為跳數索引。目的和一級索引一樣，是為了減少待搜尋的資料的範圍。

跳數索引的預設是關閉的，需要通過引數來開啟，索引生成粒度由 granularity 控制，如果生成了二級索引，則會在分割區目錄下生成額外的：skp_idx_[Column].idx 與 skp_idx_[Column].mrk 檔案。

跳數索引的生成規則：按照特定規則每隔 granularity 個 index_granularity 條資料，就會生成一條跳數索引。

比如 minmax 跳數索引，生成的是：granularity 個 index_granularity 條資料內的最大值最小值生成一條索引，如果將來需要針對構建二級索引的這個欄位求最大值最小值，則可以幫助提高效率。

跳數索引一共支援四種型別：minmax(最大最小)、set(去重集合)、 ngrambf_v1（ngram 分詞布隆索引）和 tokenbf_v1（標點符號分詞布隆索引），一張資料表支援同時宣告多個跳數索引。

3.6.3 索引粒度

資料以index_granularity(8192)被標記為多個小的區間，其中每個區間做多8192行資料。MergeTree使用MarkRange表示一個具體的區間，並通過start和end表示其具體的範圍。index_granularity不但作用於一級索引還會影響標記檔案和資料檔案。因為僅有一級索引檔案是無法完成查詢工作的，需要藉助於標記來定位資料，所以一級索引和和資料標記的間隔粒度相同，彼此對齊，而資料檔案也會按照index_granularity的間隔粒度生成壓縮資料塊。

3.6.4 查詢和寫入

查詢過程：

資料查詢的本質，可以看作一個不斷減小資料範圍的過程。我們可以總結出資料查詢流程：MergeTree 首先可以依次藉助分割區索引、一級索引和二級索引，將資料 掃描範圍縮至最小。然後再借助資料標記，將需要解壓與計算的資料範圍縮至最小。

寫入過程：

第一步是生產分割區目錄，伴隨著每一批資料的寫入，都會生成一個新的分割區目錄。在後續的某一時刻，屬於相同分割區的目錄會按照規則合併到一起；接著按照index_granularity索引粒度，會分別生成primary.idx一級索引（若宣告了二級索引則會穿建立二級索引檔案）。每批資料的寫入，都會生成一個新的分割區目錄，後續會非同步的將相同分割區的目錄進行合併。按照索引粒度，會分別生成一級索引檔案、每個欄位的標記和壓縮資料檔案

4、ClickHouse應用場景

4.1 使用場景

1.絕大多數請求都是用於讀存取的，資料一次寫入，多次查詢

2.資料需要以大批次（大於1000行）進行更新，而不是單行更新；或者根本沒有更新操作

3.資料只是新增到資料庫，沒有必要修改

4.讀取資料時，會從資料庫中提取出大量的行，但只用到一小部分列

5.表很「寬」，即表中包含大量的列

6.查詢頻率相對較低（通常每臺伺服器每秒查詢數百次或更少）

7.對於簡單查詢，允許大約50毫秒的延遲

8.列的值是比較小的數值和短字串（例如，每個URL只有60個位元組）

9.在處理單個查詢時需要高吞吐量（每臺伺服器每秒高達數十億行）

10.不需要事務

11.資料一致性要求較低

4.2 業務場景

流量分析、精準行銷、廣告實時競價、BI 報表分析、使用者行為分析、紀錄檔分析、實時大屏等。

1.資料倉儲：ClickHouse 可以快速地處理大量的資料，支援高並行查詢和複雜的聚合分析，是資料倉儲和資料分析的首選工具。

2.實時資料分析：ClickHouse 支援實時資料分析和實時查詢，可以快速地處理實時資料流，是實時資料分析和實時監控的首選工具。

3.時序資料儲存：ClickHouse 支援時序資料儲存和時序資料分析，可以快速地處理時間序列資料，是時序資料儲存和時序資料分析的首選工具。

4.資料視覺化：ClickHouse 支援資料視覺化和報表生成，可以快速地生成各種型別的報表和圖表，是資料視覺化和報表生成的首選工具。

5、總結

5.1 缺點

1.沒有完整的事務支援；

2.不支援分詞查詢；

3.缺少高頻率，低延遲的修改或刪除已存在資料的能力。僅能用於批次刪除或修改資料；

4.不擅長 join 操作；

5.不支援高並行，官方建議qps為100。原因： ClickHouse 將資料劃分為多個 partition，每個 partition 再進一步劃分為多個 index_granularity(索引粒度)，然後通過多個 CPU核心分別處理其中的一部分來實現並行資料處理。在這種設計下， 單條 Query 就能利用整機所有 CPU。 極致的並行處理能力，極大的降低了查詢延時。所以，ClickHouse 即使對於大量資料的查詢也能夠化整為零平行處理。但是有一個弊端就是對於單條查詢使用多 cpu，就不利於同時並行多條查詢。所以對於高 qps 的查詢業務， ClickHouse 並不是強項。

5.2 為什麼查詢這麼快

利用儲存引擎的特殊設計充分減少磁碟I/O對查詢速度的影響。從使用者提交一條SQL語句進行查詢到最終輸出結果的過程中，大量的時間是消耗在了磁碟I/O上，在很多情況下，I/O所佔用的時間可以達到整個時間的90%以上。對儲存引擎磁碟I/O的優化可以獲得非常大的收益。ClickHouse的儲存引擎設計中大量優化的目的也是為了減少磁碟I/O。

1.列存

2.預排序：在實現範圍查詢時，可以將大量的隨機讀轉換為順序讀，從而有效提高I/O效率，降低範圍查詢時的I/O時間；

3.資料壓縮：可以減少讀取和寫入的資料量，從而減少I/O時間。

4.向量化引擎，儘可能多地使用內建函數

5.儘可能避免Join操作，可以用Spark替代

6.ClickHouse 會在記憶體中進行 GROUP BY，並且使用 HashTable 裝載資料。

7.索引

8.多執行緒和分散式

9.演演算法：ClickHouse針對不同的應用場景，選擇不同的演演算法：

10.對於常數字串查詢，使用volnitsky演演算法

11.對於非常數字串，使用CPU的向量化執行SIMD，進行暴力優化

12.對於字串正則匹配，使用re2和hyperscan演演算法

5.3 為什麼寫入效能這麼好

1.列存

2.資料壓縮：能夠有效地減少資料的儲存空間，從而提高寫入效能

3.分散式架構

4.多執行緒寫入：ClickHouse採用多執行緒寫入機制，能夠同時處理多個寫入請求，從而提高寫入效能。

LSM-Tree儲存結構。

先明白一個測試資料：磁碟順序讀寫和隨機讀寫的效能差距大概是1千到5千倍之間

連續 I/O 順序讀寫，磁頭幾乎不用換道，或者換道的時間很短，效能很高，比如0.03 * 2000 MB /s
隨機 I/O 隨機讀寫，會導致磁頭不停地換道，造成效率的極大降低，0.03MB/s

ClickHouse中的MergeTree也是類LSM樹的思想，紀錄檔結構合併樹，但不是樹，而是利用磁碟順序讀寫能力，實現一個多層讀寫的儲存結構 是一種分層，有序，面向磁碟的資料結構，核心思想是利用了磁碟批次的順序寫要遠比隨機寫效能高出很多 大大提升了資料的寫入能力。

充分利用了磁碟順序寫的特性，實現高吞吐寫能力，資料寫入後定期在後臺Compaction。在資料匯入時全部是順序append寫，在後臺合併時也是多個段merge sort後順序寫回磁碟。官方公開benchmark測試顯示能夠達到50MB-200MB/s的寫入吞吐能力，按照每行100Byte估算，大約相當於50W-200W條/s的寫入速度。

作者：京東科技 苗元

來源：京東雲開發者社群