ethan

第 4 章數據定義#

數據分區的基本操作#

查詢分區信息#

clickhouse 內置了許多 system 系統表，用於查詢自身的狀態信息。其中 parts 系統表專門用於查詢數據表的分區信息。

select partition_id, name, table, database from system.parts where table = 'partition_v2'

刪除指定分區#

合理的設計分區鍵並利用分區的刪除功能，就能夠達到數據更新的目的。

alter table tb_anme drop partition partition_expr

複製分區數據#

clickhouse 支持將 A 表的分區數據複製到 B 表，這項特性可以用於快速數據寫入、多表之間數據同步和備份等場景。

alter table B replace partition partition_expr from A

注意：需要滿足以下兩個前提條件：

• 兩張表需要擁有相同的分區鍵
• 他們的表結構完全相同

重置分區數據#

如果數據表某一列的數據有誤，需要將其重置為初始值，此時可以使用下面的語句實現：

alter table tb_name clear column column_name in partition partition_expr

準則：如果聲明了默認值表達式，則以表達式為準；否則以相應的數據類型的默認值為準。

卸載和裝載分區#

表分區可以通過 DETACH 語句卸載，分區被卸載後，物理數據並未刪除，而是轉移至表目錄的 detached 子目錄下。裝載分區 (ATTACH) 為其反向操作。常用於分區數據的遷移和備份場景。

alter table tb_name detache partition partition_expr

分佈式 DDL 執行#

clickhouse 支持集群模式，一個集群擁有 1 到多個節點。CREATE, ALTER, DROP, RENAME 以及 TRUNCATE 這些 DDL 語句，都支持分佈式執行。如果在集群中任意一個節點上執行 DDL 語句，那麼集群中的每個節點都會以相同的順序執行相同的語句。只需加上 ON CLUSTER cluster_name 聲明即可。

數據的寫入#

INSERT 語句支持三種語法範式。

第一種：values 語法

INSERT INTO [db.]table [(c1,c2,c3...)] VALUES (v11,v12,c13),(v21,v22,v23),...

第二種：指定格式的語法

INSERT INTO [db.]table [(c1,c2,c3...)] FORMAT format_anme data_set

第三種：使用 select 子句形式的語法

INSERT INTO [db.]table [(c1,c2,c3...)] SELECT ...

VALUES 和 SELECT 子句的形式都支持表達式或函數，但是表達式和函數會帶來額外的性能開銷。

數據的刪除和修改#

clickhouse 提供了 DELETE 和 UPDATE 的能力，這類操作被稱為 Mutation 查詢，它可以看作 ALTER 語句的變種。

不同點：Mutation 語句是一種 “很重” 的操作，更適合於批量數據的修改和刪除，其次，它不支持事物，一旦語句被提交執行，就會立刻對現有數據產生影響，無法回滾；最後，該語句的執行是一個異步的後台過程，語句被提交後就會立刻返回。所以這並不代表具體邏輯已經執行完畢，它的具體執行進度需要通過 system.mutations 系統表查詢。

SELECT database, table, mutation_id, block_numbers as num, is_done FROM system.mutations

第 5 章數據字典#

數據字典是以鍵值和屬性映射的形式定義數據。數據會在 clickhouse 啟動時主動加載或者首次查詢時惰性被動加載 (由參數設置決定) 到內存，並支持動態更新。它非常適合保存常用的維度表數據，可以避免不必要的 JOIN 查詢。

數據字典有內置（默認自帶的字典）和擴展（通過自定義配置實現的）兩種。

內置字典#

第 6 章 MergeTree 原理解析#

MergeTree 的創建方式與存儲結構#

MergeTree 的創建方式#

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

(1) PARTITION BY [選填]: 分區鍵，用於指定表數據以各種標準進行分區。可以是單個列字段，也可以是元組形式的多列字段，還可以是列表達式。如果不聲明則會以 all 命名。合理使用數據分區，可以有效減少查詢時數據文件的掃描範圍

(2) ORDER BY [必填]：排序鍵，用於指定在一個數據片段內，數據以各種標準排序。默認為主鍵。可以是單個列也可以是元組形式的多個列。

(3) PRIMARY KEY [選填]: 主鍵，按照主鍵字段生成一級縮陰，用於加速表查詢。默認情況為主鍵和排序鍵相同.MergTree 主鍵允許存在重複數據。

(4) SAMPLE BY [選填]：抽樣表達式，用於聲明數據的採樣標準。主鍵的配置中也需要聲明同樣的表達式。

(5) SETTINGS：index_granularity [選填] 表示索引的力度，默認為 8192。每隔 8192 行數據才生成一條索引。

(6) SETTINGS: index_granularity_bytes [選填], 19.12 版本後支持。增加了自適應間隔大小的特性（根據每一批次寫入數據的體量大小，動態劃分間隔大小），默認為 10M, 設置為 0 表示不啟動自適應功能。

(7) SETTINGS: enable_mixed_granularity_parts [選填]：設置是否自適應索引間隔的功能，默認開啟。

(8) SETTINGS：merge_with_ttl_timeout [選填]: 19.6 之後提供的數據 TTL 的功能

(9) SETTINGS: storage_policy [選填]： 多路徑存儲策略

數據分區#

數據的分區規則#

MergeTree 數據分區的規則由分區 ID 決定。分區 ID 的生成邏輯有 4 中規則：

(1) 不指定分區鍵：分區的 ID 默認取名為 all，所有的數據都會被寫入 all 分區

(2) 使用整型：如果分區鍵取值屬於整型，無法轉換為日期類型 YYYMMDD 格式，則直接按照該整型的字符形式輸出，作為分區 ID 的取值。

(3) 使用日期類型：如果分區鍵取值屬於日期類型，或者是可以轉換為 YYYMMDD 格式的整型，則使用按照 YYYYMMDD 進行格式化後的字符形式輸出，並作為分區 ID 的取值。

(4) 使用其它類型：如果分區鍵取值既不屬於整形也不屬於日期類型，則通過 128 位 Hash 算法生成 ID。

分區目錄的命名規則#

一個完整分區目錄的命名公式如下所示

PartitionID_MinBlockNum_MaxBlockNum_Level

(1) PartitionID：分區 ID

(2) MinBlockNum_MaxBlockNum: 最小數據塊和最大數據塊編號。這裡的 BlockNum 是一個整型的自增編號。

(3) Level：合併的層級，可以理解為某個分區被合併過的次數，或者這個分區的年齡。

分區目錄的合併過程#

MergeTree 的分區目錄並不是在數據表被創建之後就存在的，而是在數據寫入過程中被創建的。他的分區目錄在建立之後也不是一成不變的。

新目錄名稱的合併方式遵循以下規則：

• MinBlockNum：取同一分區內所有目錄中最小的值
• MaxBlockNum: 取同一分區內所有目錄中的最大值
• Level：取同一分區內最大 Level 值並加 1

一級索引#

MergeTree 會依據 index_granularity 間隔，為數據表生成一級索引並保存至 primary.idx 文件內，索引數據按照 PRIMARY_KEY 排序。

稀疏索引#

在稠密索引中每一行索引標記都會對應一行具體的數據記錄，在稀疏索引中，每一行索引標記對應的是一段數據，而不是一行。

稀疏索引的優勢在於，僅需使用少量的索引標記就能夠記錄大量數據的區間位置信息，且數據量越大優勢越明顯。由於稀疏索引佔用空間小，所以 primary.idx 內的索引數據常駐內存。

索引粒度#

索引粒度會根據數據的長度對數據進行標註，最終將數據以 index_granualarity 為粒度標記成多個間隔的小段。MergeTree 使用 MarkRange 表示一個具體的區間，並通過 start 和 end 表示其具體的範圍。這個參數不僅作用於一級索引同時也會影響數據標記和數據文件。

僅靠一級索引無法完成查詢工作，需要借助數據標記才能定位數據。數據文件也會按照 index_granualarity 的間隔粒度生成壓縮數據塊。

索引數據的生成規則#

MergeTree 需要間隔 index_granularity 行數據才會生成一條索引記錄，其索引值會依據聲明的主鍵字段獲取。稀疏索引的存儲是非常緊湊的，索引值前後緊密排列在一起。

索引的查詢過程#

MarkRange 在 ClickHouse 中是用於定義標記區間的對象。MergeTree 按照 index_granularity 的間隔力度，將一段完整的數據劃分成多個小的間隔數據段，一個具體的數據段就是一個 MarkRange。

整個索引查詢過程可以大致分為 3 個步驟：

(1) 生成查詢條件區間：首先將查詢條件轉換為條件區間，即便是單個值得查詢條件，也會被轉換成區間的形式。

(2) 遞歸交集判斷：以遞歸的形式，依次對 MarkRange 的數值區間與條件區間做交集判斷。

• 如果不存在交集，則直接通過剪枝算法優化此整段 MarkRange
• 如果存在交集，且 MarkRange 步長大於 8 (end-start), 則此區間進一步拆分成 8 個自區間 (由 merge_tree_coarse_index_granularity 指定)，並重複此規則，繼續做遞歸交集判斷
• 如果存在交集，且 MarkRange 不可在分解 (步長小於 8), 則記錄 MarkRange 並返回

(3) 合併 MarkRange 區間：將最終匹配的 MarkRange 聚合在一起，合併他們的範圍。

MergeTree 通過遞歸的形式持續向下拆分區間，最終將 MarkRange 定位到最細的粒度，以幫助在後續讀取數據的時候，能夠最小化掃描數據的範圍。

二級索引#

二級索引又稱跳數索引，是由數據的聚合信息構建的。索引類型不同，聚合信息的內容也不同。目的也是幫助查詢時減少數據掃描的範圍。在默認的情況下是關閉的，需要設置 allow_experimental_data_skipping_indices 才能使用。

SET allow_experimental_data_skipping_indices = 1

二級索引需要在 CREATE 語句內定義，它支持使用元組和表達式的形式聲明，其完整的定義語法如下所示：

INDEX index_name expr TYPE index_type (...) GRANULARITY granularity

會額外生成相應的索引 (skp_idx_[column].idx) 和標記文件 (skp_idx_[column].mrk)

granularity 與 index_granularity 的關係#

對於跳數索引，index_granularity 定義了數據的粒度，granularity 定義了聚合信息匯總的粒度（一行跳數索引能夠跳過多少個 index_granularity 區間的數據）。

跳數索引的類型#

MergeTree 共支持 4 中跳數索引，分別是 minmax,set,ngrambf_v1 和 tokenbf_v1。

跳數索引的用法：

(1) minmax: 記錄一組數據內的最小和最大極值，其索引的作用類似分區目錄的 minmax 索引，能夠快速跳過無用的數據區間。

INDEX a ID TYPE minmax GRANULARITY 5

(2) set: 記錄聲明字段或表達式的取值 (唯一值，無重複), 完整形式為 set (max_rows)，表示在一個 index_granularity 內，索引最多記錄的數據行數，如果 max_rows=0 表示無限制。

INDEX b (length(ID) * 8) TYPE set(100) GRANULARITY 5

(3) ngrambf_v1: 記錄數據短語的布隆過濾器，支持 string 和 fixedstring 數據類型。且只對 in,notIn,like,equals 和 notEquals 有用。

INDEX c (ID, Code) TYPE ngrambf_v1(3,256,2,0) GRANULARITY 5

• n: token 長度，依據 n 的長度將數據切割為 token 短語
• size_of_bloom_filter_in_bytes: 布隆過濾器的大小
• number_of_hash_functions: 布隆過濾器中使用 Hash 函數的個數
• random_seed: Hash 函數的隨機種子

(4) tokenbf_v1: 屬於 ngrambf_v1 的變種。變更了 token 的處理方法，會自動按照非字符的，數字的字符串分割 token。

INDEX d ID TYPE tokenbf_v1(256,2,0) GRANULARITY 5

數據存儲#

各列獨立存儲#

每個列對應的字段都有對應的.bin 數據文件，以分區目錄的形式被組織存放。

按列獨立存儲的設計優勢：

1. 可以更好的進行數據壓縮（相同類型的數據放在一起對壓縮友好）
2. 可以最小化數據掃描的範圍

數據是經過壓縮的，目前支持 LZ4, ZSTD, Multiple 和 Delta 幾種算法，默認使用 LZ4。其次數據會按照 ORDER BY 的聲明排序；最後，數據是以壓縮數據塊的形式被組織寫入.bin 文件中。

壓縮數據塊#

一個壓縮數據塊由頭部信息和壓縮數據兩部分組成。頭部信息固定使用 9 位字節表示，具體由 1 個 UInt8（1 字節）整形和 2 個 UInt32（4 字節）整型組成，分別代表使用的壓縮算法類型，壓縮後的數據大小和壓縮前的數據大小。

.bin 壓縮文件是由多個壓縮數據塊組成的，每個壓縮數據塊的頭信息是基於 CompressionMethod_CompressedSize_UncompressedSize 公式生成。

clickhouse-compressor 工具，可以查詢.bin 文件中壓縮數據的統計信息。

每個壓縮數據塊的體積，按照其壓縮前的數據字節大小，都被嚴格控制在 64KB~1MB, 上下限有 min_compress_block_size（65536）與 max_compress_block_size（1048576）指定。壓縮數據快最終的大小，和一個間隔內 (index_granularity) 數據的實際大小相關。

MergeTree 的數據具體寫入過程，會依照索引粒度，按批次獲取數據並進行處理。如果把未壓縮大小設為 size, 則整個寫入過程遵循以下原則：

（1）單個批次數據 size<64KB: 則繼續獲取下一批次數據，直到累積到 size>64KB, 生成下個壓縮數據快。

（2）單個批次數據 64KB <=size<=1MB: 直接生成下個壓縮數據塊

（3）單個批次數據 size>=1MB: 則首先按照 1MB 大小截斷並生成下個壓縮數據塊。剩餘數據繼續依照上述規則執行，此時會出現一個批次數據生成多個壓縮數據塊的情況

在.bin 文件中引入壓縮數據塊的目的至少有以下兩個：

一、雖然數據被壓縮後能過有效減少數據大小，降低存儲空間並加速數據傳輸效率，但數據的壓縮和解壓動作，也會帶來額外的性能損耗，所以需要控制被壓縮數據的大小，以求在性能損耗和壓縮率之間尋求一種平衡。

二、在具體讀取某一列數據時，首先需要將壓縮數據加載到內存並解壓。通過壓縮數據塊，可以在不讀整個.bin 的情況下將讀取粒度降低到壓縮數據塊界別，從而進一步縮小數據讀取的範圍

數據標記#

數據標記的生成原則#

數據標記和索引區間是對齊的，均按照 index_granularity 的粒度間隔。因此只需要通過索引區間的下標編號就可以直接找到對應的數據標記

數據標記文件和 .bin 文件一一對應，用於記錄數據在.bin 文件中的偏移量信息。

一行標記數據使用一個元組表示，元組內包含兩個整形數值的偏移量信息。分別表示此段數據區間內，在對應的 .bin 壓縮文件中，壓縮數據快的起始偏移量；以及將該數據壓縮塊解壓後，其未壓縮數據的起始偏移量。

標記數據與一級索引數據不同，它不能常駐內存，而是使用 LRU 緩存策略加快其去用速度。

數據標記的工作方式#

在讀取數據時，必須通過標記數據的位置信息才能夠找到所需要的數據。整個查找過程大致分為讀取壓縮數據快和讀取數據兩個步驟。

MergeTree 是如何定位壓縮數據塊，並讀取數據的：

（1）讀取壓縮數據快：在查詢某一列數據時， MergeTree 無須一次性加載整個.bin 文件，而是可以根據需要，只加載特定的壓縮數據快，而這項特性需要借助標記文件中所保存的壓縮文件中的偏移量。

（2）讀取數據：在讀取解壓後的數據時，MergeTree 並不需要一次性掃描整段解壓數據，它可以根據需要，從 index_granularity 的粒度加載特定的一小段。

對於分區、索引、標記和壓縮數據的協同總結#

寫入過程#

數據寫入的第一步是生成分區目錄，伴隨著每一批數據的寫入，都会生成一個新的分區目錄。在後續的某一時刻，屬於相同分區的目錄會依照規則合併到一起；接著，按照 index_granularity 索引粒度，會分成生成 primary.idx 一級索引或二級索引，每一個列字段的.mrk 數據標記和.bin 壓縮數據文件。

查詢過程#

數據查詢的本質，可以看作一個不斷減小數據範圍的過程。在最理想的情況下，MergeTree 首先可以依次借助分區索引，一級索引和二級索引，將數據掃描範圍縮至最小。然後在借助數據標記，將需要解壓與計算的數據範圍縮至最小。

如果一條查詢語句沒有指定任何 where 條件，或是指定了 where 條件，但條件沒有匹配到任何索引（分區索引，一級索引和二級索引），那麼 MergeTree 就不能預先減小數據範圍。在後續進行數據查詢時，它會掃描所有分區目錄，以及目錄內索引段的最大區間。雖然不能減少數據範圍，但是 MergeTree 仍然能夠借助數據標記，以多線程的形式同時讀取多個壓縮數據塊，以提升性能。

數據標記與壓縮數據塊的對應關係#

由於壓縮數據塊的劃分，與一個間隔（index_granularity）內的數據大小相關，每個壓縮數據塊的體積都被嚴格控制在 64KB-1MB. 而一個間隔的數據，又只會產生一行數據標記。那么根據一個間隔內數據的實際字節大小，數據標記和壓縮數據塊之間會產生三種不同的對應關係。

多對一#

多個數據標記對應一個壓縮數據塊，當一個間隔內的數據未壓縮大小 size 小於 64 KB 時，會出現這種對應關係。

一對一#

一個數據標記對應一個壓縮數據塊，當一個間隔內的數據未壓縮大小 size 大於等於 64 KB 且小於等於 1 MB 時，會出現這種關係。

一對多#

一個數據標記對應多個壓縮數據快，當一個間隔內的壓縮數據為壓縮大小 size 直接大於 1 MB 時，會出現這種關係。

第 7 章 MergeTree 系列表引擎#

目前在 ClickHouse 中，按照特點可以將表引擎大致分成 6 個系列，分別是合併樹，外部存儲，內存，文件，接口和其它，每一個系列的表引擎都有著獨自的特點和使用場景。

MergeTree#

MergeTree 作為家族系列最基礎的表引擎，提供了數據分區，一級索引和二級索引等功能。

數據 TTL#

TTL 表示數據的存活時間。在 MergeTree 中，可以為某個列字段或整張表設置 TTL。當時間達到時，如果是列字段級別的 TTL，則會刪除這一列的數據；如果是表級的 TTL，則會刪除整張表的數據；如果同時設置了列級別和表級別的 TTL，則會以先到期的那個為主。

TTL 都需要依賴某個 DateTime 或 Date 類型的字段，通過對這個時間字段的 INTERVAL 操作，來表示 TTL 的過期時間。

列級別 TTL#

如果想要設置列級別的 TTL，則需要在定義表字段的時候，為他們聲明 TTL 表達式，主鍵字段不能被聲明 TTL。

create table ttl_table_v1 (
	id String,
	create_time DateTime,
	code String TTL create_time + INTERVAL 10 SECOND,
	type UInt8 TTL create_time + INTERVAL 10 SECOND
)
ENGINE = MergeTree
PARTITION BY toYYYMM(create_time)
ORDER BY id

目前 ClickHouse 沒有提供取消列級別 TTL 的方法

表級別 TTL#

如果想要為整張表設置 TTL，需要在 MergeTree 的表參數中增加 TTL 表達式。

create table ttl_table_v2 (
	id String,
	create_time DateTime,
	code String TTL create_time + INTERVAL 10 SECOND,
	type UInt8 TTL create_time + INTERVAL 10 SECOND
)
ENGINE = MergeTree
PARTITION BY toYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY

表級別 TTL 目前也沒有取消的辦法

TTL 的運行機理#

如果設置了 TTL 表達式，那麼在寫入數據時，會以數據分區為單位，在每個分區目錄生成一個名為 ttl.txt 的文件，通過 JSON 配置保存了 TTL 的相關信息：

• columns 用於保存列級別的 TTL 信息
• table 用於保存表級別的 TTL 信息
• min 和 max 則保存了當前數據分區內，TTL 指定日期字段的最小值，最大值分別與 INTERVAL 表達式計算後的時間戳。
  大致的處理邏輯為：

1. MergeTree 以分區目錄為單位，通過 ttl.txt 文件記錄過期時間，並將作為後續的判斷依據。
2. 每當寫入一批數據時，都会基於 INTERVAL 表達式的計算結果為這個分區生成 ttl.txt 文件
3. 只有在 MergeTree 合併分區時，才會觸發刪除 TTL 過期數據的邏輯
4. 在選擇刪除的分區時，會使用貪婪算法，它的算法規則是盡可能找到會最早過期的，同時年紀又是最老的分區（合併次數更多，MaxBlockNum 更大的）
5. 如果一個分區內某一列數據因為 TTL 到期全部被刪除了，那麼在合併之後生成的新分區目錄中，將不會包含這個列字段的數據文件（.bin 和.mrk).
   注意：
6. TTL 默認的合併頻率由 MergeTree 的 merge_with_ttl_timeout 參數控制，默認 86400 秒，即一天。它維護的是一個專有的 TTL 任務隊列。有別於 MergeTree 的常規合併任務，如果這個值被設置的過小，可能會帶來性能損耗。
7. 除了被動觸發 TTL 合併外，也可以使用 optimize 命令強制觸發合併。

optimize table table_name
optimize table table_name FINAL   # 觸發所有分區合併

3. clickhouse 目前沒有提供刪除 TTL 聲明的方法，但是提供了控制全局 TTL 合併任務的啟停方法。

SYSTEM STOP/START TTL MERGES

多路徑存儲策略#

MergeTree 實現了自定義存儲策略的功能，支持以數據分區為最小移動單位，將分區目錄寫入多塊磁碟目錄。
目前有三類存儲策略：

• 默認策略： MergeTree 原本的存儲策略，無須任何配置，所有分區會自動保存到 config.xml 配置中 path 指定的路徑下
• JBOD 策略：這種策略適合伺服器掛載了多塊磁碟，但沒有做 RAID 的場景。JBOD 是一種輪詢策略，每次執行一次 INSERT 或者 MERGE，所產生的新分區會輪詢寫入各個磁碟。這種策略的效果類似 RAID 0, 可以降低單塊磁碟的負載，在一定條件下能夠增加數據並行讀寫的性能。如果單塊磁碟發生故障，則會丟掉應用 JBOD 策略寫入的這部分數據。
• HOT/COLD 策略：這種策略適合伺服器掛載了不同類型磁碟的場景。將存儲磁碟分為 HOT 與 COLD 兩類區域。HOT 區域使用 SSD 這類高性能存儲媒介，注重存取性能；COLD 區域則使用 HDD 這類高容量存儲媒介，注重存取經濟性。數據在寫入累積到閾值時，數據會自行移動到 COLD 區域。而在每個區域的內部，也支持定義多個磁碟，所以在單個區域的寫入過程中，也能應用 JBOD 策略。

ReplacingMergeTree#

擁有主鍵，但是它的主鍵沒有唯一鍵的約束。意味著即便多行數據的主鍵相同，他們還是能夠被正常寫入。ReplacingMergeTree 是為了數據去重而設計的，能夠在合併分區時刪除重複的數據。

排序鍵 ORDER BY 所聲明的表達式是後續作為判斷數據是否重複的依據。
ReplacingMergeTree 是以分區為單位刪除重複數據的。只有在相同的數據分區內重複的數據才可以被刪除，而不同數據分區之間的重複數據依然不能被剔除。
處理邏輯：

• 使用 ORDER BY 排序鍵作為判斷重複數據的唯一鍵
• 只有在合併分區的時候才會觸發刪除重複數據的邏輯
• 以數據分區為單位刪除重複數據。當分區合併時，同一分區內的重複數據會被刪除；不同分區之間的重複數據不會被刪除。
• 在進行數據去重時，因為分區內的數據已經基於 ORDER BY 進行了排序，所以能夠找到那些相鄰的重複數據
• 數據去重邏輯策略有兩種：
  • 如果沒有設置 ver 版本號，則保留同一組重複數據中的最後一行
  • 如果設置了 ver 版本號，則保留同一組重複數據中 ver 字段取值最大的那一行。

SummingMergeTree#

它能在合併分區的時候按照預先定義的條件聚合匯總數據，將同一分組下的多行數據匯總合併成一行，這既減少了數據行，又降低了後續匯總查詢的開銷。
處理邏輯：

• 用 ORDER BY 排序鍵作為聚合數據的條件 Key
• 只有在合併分區的時候才會觸發匯總的邏輯
• 以數據分區為單位來聚合數據。當分區合併時，同一數據分區內聚合 Key 相同的數據會被合併匯總，而不同分區之間的數據則不會被匯總。
• 如果在定義引擎時制定了 columns 匯總列（非主鍵的數值類型字段), 則 SUM 匯總這些列字段，如果未指定，則聚合所有非主鍵的數值類型字段
• 在進行數據匯總時，因為分區內的數據已經基於 ORDER BY 排序，所以能夠找到相鄰且擁有相同聚合 Key 的數據。
• 在匯總數據時，同一分區內，相同聚合 Key 的多行數據會合併成一行。其中匯總字段會進行 SUM 計算；對於那些非匯總字段，則會使用第一行數據的取值。
• 支持嵌套結構，但列字段名稱必須以 Map 後綴結尾。嵌套類型中，默認以第一個字段作為聚合 Key。除第一個字段以外，任何名稱以 Key,Id 或 Type 為後綴結尾的字段，都將和第一個字段一起組合成符合 Key。

AggregatingMergeTree#

處理邏輯：

• 用 ORDER BY 排序鍵作為聚合數據的條件 Key
• 使用 AggregateFunction 字段類型定義聚合函數的類型以及聚合的字段
• 只有在合併分區的時候才能觸發聚合計算的邏輯
• 以數據分區為單位來聚合數據。當分區合併時，同一數據分區內聚合 key 相同的數據會被合併計算，而不同分區之間的數據則不會被計算。
• 在進行數據計算時，因為分區內的數據已經基於 ORDER BY 排序，所以能夠找到那些相鄰擁有相同聚合 Key 的數據
• 在聚合數據時，同一分區內，相同聚合 Key 的多行數據會合併成一行。對於那些非主鍵，非 AggregateFunction 類型字段，則會使用第一行數據的取值。
• AggregateFunction 類型的字段使用二進制存儲，在寫入數據時，需要調用*State函數；而在查詢數據時，則需要調用相應的*Merge函數。其中，*表示定義時使用的聚合函數
• AggregatingMergeTree 通常作為物化視圖的表引擎，與普通的 MergeTree 搭配使用。

第 9 章 數據查詢#

ClickHouse 對於 SQL 語句的解析是大小寫敏感的，這意味著 SELECT a 和 SELECT A 表示的語義是不相同的。

WITH 子句#

ClickHouse 支持 CTE（Common Table Expression，公共表表達式），以增強查詢語句的表達。此形式，可以極大的提高語句的可讀性和可維護型。
CTE 通過 WITH 子句表示，目前支持四中用法

1. 定義變量
   可以定義變量，這些變量能夠在後續的查詢子句中被直接訪問。

WITH 10 AS start
SELECT number FROM system.numbers
WHERE number > start
LIMIT 5

2. 調用函數

WITH SUM（data_uncompressed_bytes) AS bytes
SELECT database, formatReadableSize(bytes) AS format FROM system.columns
GROUP BY database
ORDER BY bytes DESC

3. 定義子查詢

WITH(
	SELECT SUM(data_uncompressed_bytes) FROM system.columns
) AS total_bytes
SELECT database, (SUM(data_uncompressed_bytes)/total_bytes) * 100 AS database_disk_usage
FROM system.columns
GROUP BY database
ORDER BY database_disk_usage DESC

在 WITH 中使用子查詢時，需要注意：該查詢語句只能返回一行數據，如果結果集的數據大於一行則會拋出異常。
4. 在子查詢中重複使用 WITH

WITH（round(database_disk_usage))AS database_disk_usage_v1 SELECT database, database_disk_usage, database_disk_usage_v1 FROM (WITH (SELECT SUM(data_uncompressed_bytes) FROM system.columns) AS total_bytes SELECT database, (SUM(data_uncompressed_bytes)/total_bytes)*100 AS database_disk_usage FROM system.columns GROUP BY database ORDER BY database_disk_usage DESC)

FROM 子句#

FROM 子句表示從何處讀取數據，目前支持 3 中形式

1. 從數據表中取數

SELECT watch_id FROM hits_v1

2. 從子查詢中取數

SELECT max_watch_id from (SELECT MAX(watch_id) from hits_v1)

3. 從表函數中取數

SELECT number FROM numbers(5)

在 FROM 子句後，可以使用 Final 修飾符，它可以配合 CollapsingMergeTree 和 VersionedCollapsingMergeTree 等表引擎進行查詢操作，以強制在查詢過程中合併，但由於 Final 修飾符會降低查詢性能，所以應該儘可能避免使用它。

SAMPLE 子句#

SAMPLE 子句能夠實現數據採樣的功能，使查詢僅返回採樣數據而不是全部數據，從而有效減少負載。SAMPLE 子句的採樣機制是一種幂等設計，也就是說在數據不發生變化的情況下，使用相同的採樣規則總是能夠返回相同的數據，所以這項特性非常適合在那些可以接受近似查詢結果的場景使用。
SAMPLE 子句只能用於 MergeTree 系列引擎的數據表，並且要求在 create table 時聲明 sample by 抽樣表達式。

CREATE TABLE hits_v1 (
	counterid UInt64,
	EventDate DATE,
	UserID UInt64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM（EventDate）
ORDER BY (counterid, intHash32(UserID))
SAMPLE BY intHash32(UserID)

聲明 sample key 的時候需要注意：

• sample by 所聲明的表達式必須同時包含在主鍵的聲明內；
• sample key 必須是 Int 類型，如果不是，clickhouse 在進行 create table 操作時不會報錯，但是在查詢時會出現異常。
  子句的三種用法：

1. SAMPLE factor
   SAMPLE factor 表示按因子系數採樣，其中 factor 表示採樣因子，它的取值支持 0～1 之間的小數。如果 factor 設置為 0 或者 1, 則效果等同於不進行數據採樣。
2. SAMPLE rows
   SAMPLE rows 表示按樣本數量採樣，其中 rows 表示至少採樣多少行數據，它的取值必須是大於 1 的整數。如果 rows 的取值大於表內數據的總行數，則效果等於 rows=1 (即不使用採樣)
3. SAMPLE factor OFFSET n
   表示按因子系數和偏移量採樣，其中 factor 表示採樣因子，n 表示偏移多少數據後才開始採樣，他們兩個的取值都是 0~1 之間的小數。

ARRAY JOIN 子句#

ARRAY JOIN 子句允許在數據表的內部，與數組或嵌套類型的字段進行 JOIN 操作，從而將一行數組展開為多行。目前支持 inner 和 left 兩種 join 策略：

1. inner array join
   ARRAY JOIN 在默認情況下使用的是 INNER JOIN 策略。
   數據基於 value 數組被展開成了多行，並且排除掉了空數組。

2. LEFT ARRAY JOIN
   不會排除掉空數組，對多個數組字段進行 array join 操作時，查詢的計算邏輯是按行合併而不是產生笛卡兒積。

JOIN 子句#

語法包含連接精度和連接類型兩部分。
連接精度分為 ALL, ANY 和 ASOF 三種，而連接類型也可分為外連接、內連接和交叉連接三種。

連接精度#

連接精度決定了 JOIN 查詢在連接數據時所使用的策略，目前支持 ALL，ANY 和 ASOF 三種類型。默認是 ALL，可以通過 join_default_strictness 配置參數修改默認的連接精度類型。

ALL#

如果左表內的一行數據，在右表中有多行數據與之連接匹配，則返回右表中全部連接的數據，而判斷連接匹配的依據是左表與右表內的數據，基於連接鍵的取值完全相等，等同於 left.key=right.key。

ANY#

如果左表內的一行數據，在右表中有多行數據與之連接匹配，則返回右表中第一行連接的數據。ANY 和 ALL 判斷連接匹配的依據相同。

ASOF#

ASOF 是一種模糊連接，它允許在連接鍵之後追加定義一個模糊連接的匹配條件 asof_column。

SELECT a.id, a.name, b.rate, a.time, b.time FROM join_tab1 AS a ASOF INNER JOIN join_tb2 AS B ON a.id=b.id AND a.time = b.time

其中 a.id = b.id 是尋常的連接鍵，a.time=b.time 則是 asof_column 模糊連接條件，語義等同於a.time>= b.time
注意：asof_column 必須是整型，浮點型和日期這類有序序列的數據類型。asof_column 不能是數據表內的唯一字段，換言之，連接鍵 (JOIN_KEY) 和 asof_column 不能是同一字段。

連接類型#

連接類型決定了 JOIN 查詢組合左右兩個數據集合要用的策略，他們所行程的結果是交集，並集，笛卡兒積或者其它形式。

INNER#

INNER JOIN 表示內連接，在查詢時會以左表為基礎逐行遍歷數據，然後從右表中找出與左邊連接的行，它只會返回左表與右表兩個數據集合中交集的部分，其餘部分都會被排除。

OUTER#

表示外連接，它可以進一步細分為左外連接（LEFT），右外連接（RIGHT）和全外連接（FULL）三種形式。根據連接形式不同，其返回數據集合的邏輯也不盡相同。

1. LEFT
   在進行左外連接查詢時，會以左表為基礎逐行遍歷數據，然後從右表中找出與左邊連接的行以補齊屬性。如果右表中沒有找到連接的行，則采用相應字段數據類型的默認值填充。換言之，對於左連接查詢而言，左表的數據總是能夠全部返回。
2. RIGHT
   右外連接查詢的效果與左連接恰好相反，右表的數據總是能夠全部返回，而左表不能連接的數據則使用默認值補全。
   在進行右外連接查詢時，內部的執行邏輯大致如下：
   (1) 在內部進行類似 INNER JOIN 的內連接查詢，在計算交集部分的同時，順帶記錄右表中那些未能被連接的數據行。
   (2) 將那些未能被連接的數據行追加到交集的尾部
   (3) 將追加數據中那些屬於左表的列字段用默認值補全。
3. FULL
   全外連接內部的執行邏輯大致如下：
   (1) 會在內部進行類似 LEFT JOIN 的查詢，在左外連接的過程中，順帶記錄右表中已經被連接的數據行。
   (2) 通過在右表中記錄已被連接的數據行，得到未被連接的數據行
   (3) 將右表中未被連接的數據追加至結果集，並將那些屬於左表中的列字段以默認值補全。

CROSS#

表示交叉連接，它會返回左表與右表兩個數據集合的笛卡兒積。

多表連接#

在進行多張數據表的連接查詢時，ClickHouse 會將它們轉為兩兩連接的形式。

注意事項#

關於 JOIN 查詢的注意事項

1. 關於性能
   為了能夠優化 JOIN 查詢性能，首先應該遵循左大右小的原則。其次，JOIN 查詢目前沒有緩存的支持。最後，如果是在大量維度屬性補全的查詢場景中，則建議使用字典代替 JOIN 查詢。
2. 關於空值策略與簡寫形式

連接查詢的空值是由默認值填充的。連接查詢的空值策略是通過 join_use_nulls 參數指定的，默認為 0。當參數值為 0 時，空值由數據類型的默認值填充；而當參數值為 1 時，空值由 Null 填充。

WHERE 與 PREWHERE 子句#

WHERE 子句基於條件表達式來實現數據過濾。如果過濾條件恰好是主鍵字段，則能夠進一步借助索引加速查詢，所以 WHERE 子句是一條查詢語句是否啟用索引的判斷依據。
PREWHERE 目前只能用於 MergeTree 系列的表引擎，它可以看作對 WHERE 的一種優化，其作用於 WHERE 相同，均是用來過濾數據。不同之處在於，使用 PREWHERE 時，首先只會讀取 SELECT 聲明的列字段以補全其餘屬性。在一些場合下，PREWHERE 相比 WHERE，處理數據量更少，性能更高。
在以下情形下 WHERE 不會自動優化：

• 使用了常量表達式
• 使用了默認值為 ALIAS 類型的字段
• 包含了 arrayJoin, globalIn, globalNotIn 或者 indexHint 的查詢
• SELECT 查詢的列字段與 WHERE 謂詞相同
• 使用了主鍵字段

當使用 prewhere 進行主鍵查詢時，首先會通過稀疏索引過濾數據區間，接著會讀取 prewhere 指定的條件列以進一步過濾，這樣依賴就有可能截掉數據區間的尾巴，從而返回低於 index_granularity 粒度的數據範圍，即便如此相比其它場合移動謂詞所帶來的性能提升，這類效果還是有限的。

GROUP BY 子句#

表示聚合查詢。是讓 clickhouse 最凸顯卓越性能的地方，在 GROUP BY 後聲明的表達式，成為聚合鍵或者 key, 數據會按照聚合鍵進行聚合。
在某些場合下，可以借助 any, max 和 min 等聚合函數訪問聚合鍵之外的列字段。

WITH ROLLUP#

ROLLUP 能夠按照聚合鍵從右向上卷數據，基於聚合函數依次生成分組小計和總計。

WITH CUBE#

CUBE 會像立方體模型一樣，基於聚合鍵之間所有的組合生成小計信息。

WITH TOTALS#

會基於聚合函數對所有數據進行總計。

HAVING 子句#

需要與 GROUP BY 同時出現，不能單獨使用。它能夠在聚合計算之後實現二次過濾數據。

ORDER BY 子句#

ORDER BY 子句通過聲明排序鍵來指定查詢數據返回時的順序。在 MergeTree 中指定 ORDER BY 後，數據在各個分區內會按照其定義的規則排序，這是一種分區內的局部排序。如果在查詢時數據跨越了多個分區，則他們的返回順序是無法預知的，每一次查詢返回的順序都可能不同。在這種情況下，如果需要數據總是能夠按照期望的順序返回，就需要 ORDER BY 子句來指定全局順序。

ORDER BY 在使用時可以定義多個排序鍵，每個排序鍵後需緊跟 ASC（升序）或 DESC（降序）來確定排序順序。默認 ASC

1. NULLS LAST NULL 值放在最後，默認行為
2. NULLS FIRST NULL 值放在最前。

LIMIT BY 子句#

LIMIT BY 運行於 ORDER BY 之後和 LIMIT 之前，能夠按照指定分區，最多返回前 n 行數據。常用於 TOP N 的查詢場景。

LIMIT 子句#

LIMIT 子句用於返回指定的前 N 行數據，常用於分頁場景。三種語法格式如下：

limit n
limit n offset m
limit m,n

在使用 limit 子句時注意，如果數據跨越了多個分區，在沒有使用 ORDER BY 指定全局順序的情況下，每次 LIMIT 查詢返回的數據有可能不同。

SELECT 子句#

決定了查詢語句最終返回哪些列字段或表達式。雖然 SELECT 位於 SQL 語句的起始位置，但它卻是在上述一眾子句之後執行的。在其它子句執行後，SELECT 會將選取的字段或表達式作用於每行數據上。如果使用*通配符，則會返回數據表的所有字段。

DISTINCT 子句#

用於去除重複數據。能夠和 GROUP BY 同時使用，它們是互補而不是互斥關係。

UNION ALL 子句#

UNION ALL 子句能夠聯合左右兩邊的兩組子查詢，將結果一並返回。一次查詢中，可以聲明多次 UNION ALL 以便聯合多組查詢，但 UNION ALL 不能直接使用其他子句。

查詢 SQL 執行計劃#

（1）通過將 ClickHouse 服務日誌設置到 DEBUG 或者 TRACE 級別，可以變相實現 EXPLAIN 查詢，以分析 SQL 的執行日誌
（2）需要真正執行了 SQL 查詢，CH 才能打印計劃日誌，所以如果表的數據量很大最好借助 LIMIT 子句減小查詢返回的數量
（3）不要使用 SELECT * 全字段查詢
（4）儘可能利用各種索引（分區索引，一級索引，二級索引），這樣可避免全表掃描。

clickhouse-client -h 127.0.0.1 -u uts --port 9100 --password ~Uts2020db --send_logs_level=trace <<< "select timestamp,sip4,sip6,sport,dip4,dip6,dport,protocol,app_proto,app_desc_cn,dmac,s_card_name,s_device_hash,tx_bytes,tx_pkts,rx_bytes,rx_pkts,isIPv4,direct,first_time,last_time,sid from uts.storage_session prewhere sid global in (select sid from uts.storage_session prewhere timestamp > 1698826475 and timestamp <= 1699337215 and msgtype = 12 order by timestamp desc limit 10 offset 2357) order by timestamp desc" > /dev/null

第 10 章 副本與分片#

集群是副本和分片的基礎，它將 clickhouse 的服務拓撲由單點延伸到多個節點，但它並不像 Hadoop 生態的某些系統那樣，要求所有節點組成一個單一的大集群。ClickHouse 的集群配置非常靈活，用戶既可以將所有節點組成一個單一集群，也可以按照業務的訴求，把節點劃分為多個小的集群。在每一個小的集群區域之間，他們的節點，分區和副本數量可以各不相同。

從作用來看，clickhouse 集群的工作更多是針對邏輯層面的。集群定義了多個節點的拓撲關係，這些節點在後續服務過程中可能會協同工作，而執行層面的具體工作則交給副本和分片來執行。
副本和分片的區分方法
一：從數據層面來分，加入 clickhouse 的 N 個節點組成了一個集群，在集群的各個節點上，都有一張結果相同的數據表 Y。如果 N1 的 Y 和 N2 的 Y 中的數據完全不同，則 N1 和 N2 互為分片。如果他們的數據完全相同，則他們互為副本。
二：從功能作用層面區分，使用副本的主要目的是防止數據丟失，增加數據存儲的冗餘；而使用分片的主要目的是實現數據的水平切分。

數據副本#

只有使用了 ReplicatedMergeTree 複製表系列引擎，才能應用副本的能力。或者使用 ReplicatedMergeTree 的數據表就是副本。

副本的特點#

作為數據副本的主要實現載體，ReplicatedMergeTree 在設計上有一些顯著特點。

• 依賴 ZooKeeper: 在執行 INSET 和 ALTER 查詢的時候，ReplicatedMergeTree 需要借助 ZooKeeper 的分佈式協同能力，以實現多個副本之間的同步，但是在查詢副本的時候，並不需要使用 ZooKeeper.
• 表級別的副本：副本是在表級別定義的，所以每張表的副本配置都可以按照它的實際需求進行個性化定義，包括副本的數量，以及副本在集群內的分佈位置等。
• 多主架構：可以在任意一個副本上執行 INSERT 和 ALTER 查詢，他們的效果是相同的。這些操作會借助 ZooKeeper 的協同能力被分發到每個副本以本地形式執行。
• Block 數據快：在執行 INSERT 命令寫入數據時，會依據 max_insert_block_size 的大小（默認 1048576 行）將數據切分成若干個 Block 數據塊，所以 Block 數據塊是數據寫入的基本單元，並且具有寫入的原子性和唯一性
• 原子性：在數據寫入時，一個 block 塊內的數據要麼全部寫入成功，要麼全部失敗
• 唯一性：在寫入一個 Block 數據塊的時候，會按照當前 Block 數據快的數據順序，數據行和數據大小等制表，計算 Hash 信息摘要並記錄在案。在此之後，如果某個寫入的 Block 數據快與之前寫入的 Block 塊擁有相同的 Hash 摘要（Block 數據塊內數據順序，數據大小和數據行均相同），則該 Block 數據塊會被忽略。

副本的定義形式#

使用副本的好處：

• 增加了數據的冗餘存儲，所以降低了數據丟失的風險
• 副本採用了多主架構，所以每個副本實例都可以作為數據讀，寫的入口，分攤了節點的負載

ReplicatedMergeTree 原理解析#

數據結構#

核心邏輯中，大量的使用了 ZooKeeper 的能力，以實現多個 ReplicatedMergeTree 副本實例之間的協同，包括主副本選舉，副本狀態感知，操作日誌分發，任務隊列和 BlockID 去重判斷等。在執行 INSERT 數據寫入，MERGE 分區和 MUTATION 操作的時候，都会涉及與 ZooKeeper 的通信。但是在通信過程中並不會設計任何表數據的傳輸，在查數據的時候也不會訪問 ZooKeeper。