ClickHouse技術研究及語法簡介_ZenDei技術網路在線

本文對Clickhouse架構原理、語法、性能特點做一定研究，同時將其與mysql、elasticsearch、tidb做橫向對比，並重點分析與mysql的語法差異，為有mysql遷移clickhouse場景需求的技術預研及參考。 ...

本文對Clickhouse架構原理、語法、性能特點做一定研究，同時將其與mysql、elasticsearch、tidb做橫向對比，並重點分析與mysql的語法差異，為有mysql遷移clickhouse場景需求的技術預研及參考。

1 基礎概念

Clickhouse是一個用於聯機分析（OLAP）的列式資料庫管理系統（DBMS)。

1.1 集群架構

ClickHouse 採用典型的分組式的分散式架構，具體集群架構如下圖所示：

Shard：集群內劃分為多個分片或分組（Shard 0 … Shard N），通過 Shard 的線性擴展能力，支持海量數據的分散式存儲計算。
Node：每個 Shard 內包含一定數量的節點（Node，即進程），同一 Shard 內的節點互為副本，保障數據可靠。ClickHouse 中副本數可按需建設，且邏輯上不同 Shard 內的副本數可不同。
ZooKeeper Service：集群所有節點對等，節點間通過 ZooKeeper 服務進行分散式協調。

1.2 數據分區

Clickhouse是分散式系統，其數據表的創建，與mysql是有差異的，可以類比的是在mysql上實現分庫分表的方式。

Clichhouse先在每個 Shard 每個節點上創建本地表（即 Shard 的副本），本地表只在對應節點內可見；然後再創建分散式表[Distributed]，映射到前面創建的本地表。

用戶在訪問分散式表時，ClickHouse 會自動根據集群架構信息，把請求轉發給對應的本地表。

1.3 列式存儲

相對於關係型資料庫（RDBMS），是按行存儲的。以mysql中innodb的主鍵索引為例，構建主鍵索引的B+樹中，每個葉子節點存儲的就是一行記錄。

而列式資料庫，是將一個表，按column的維護進行存儲，“單次磁碟I/O拿到的是一列的數據”。

列式存儲的優點
在查詢時，只會讀取涉及到的列，會大大減少IO次數/開銷。並且clickhouse在存儲時會按指定順序排列數據，因此只需要按where條件指定列進行順序掃描、多個列的掃描結果合併，即可找到滿足條件的數據。
但由於insert數據時，是按行寫入的，因此存儲的過程會麻煩一些。

查詢時的區別：

列存儲：僅從存儲系統中讀取必要的列數據（select + where 涉及到的），無用列不讀取，速度非常快。
行存儲：從存儲系統讀取所有滿足條件的行數據，然後在記憶體中過濾出需要的欄位，速度較慢。

1.4 數據排序

每個數據分區內部，所有列的數據是按照排序鍵（ORDER BY 列）進行排序的。
可以理解為：對於生成這個分區的原始記錄行，先按排序鍵進行排序，然後再按列拆分存儲。

1.5 數據分塊

每個列的數據文件中，實際是分塊存儲的，方便數據壓縮及查詢裁剪，每個塊中的記錄數不超過 index_granularity，預設 8192，當達到index_granularity的值，數據會分文件。

1.6 向量化執行

在支持列存的基礎上，ClickHouse 實現了一套面向向量化處理的計算引擎，大量的處理操作都是向量化執行的。

向量化處理的計算引擎：
基於數據存儲模型，疊加批量處理模式，利用SIMD指令集，降低函數調用次數，降低硬體開銷（比如各級硬體緩存），提升多核CPU利用率。
再加上分散式架構，多機器、多節點、多線程、批量操作數據的指令，最大限度利用硬體資源，提高效率。

註：SIMD指令，單指令多數據流，也就是說在同一個指令周期可以同時處理多個數據。(例如：在一個指令周期內就可以完成多個數據單元的比較).

1.7 編碼壓縮

由於 ClickHouse 採用列存儲，相同列的數據連續存儲，且底層數據在存儲時是經過排序的，這樣數據的局部規律性非常強，有利於獲得更高的數據壓縮比。
同時，超高的壓縮比又可以降低存儲讀取開銷、提升系統緩存能力，從而提高查詢性能。

1.8 索引

前面提到的列式存儲，用於裁剪不必要的欄位讀取；
而索引，則用於裁剪不必要的記錄讀取（減少未命中數據的IO)。

簡單解釋：
以主鍵索引為例，Clickhouse存儲數據時，會按排序鍵（ORDER BY)指定的列進行排序，並按Index_granularity參數切分成塊，然後會抽取每個數據塊的首行，組織為一份稀疏的排序索引。
類比B+樹的查找過程，如果where條件中包含主鍵列，就可以通過稀疏索引快速的過濾。稀疏索引對於範圍查找比較高效。

二級索引，則是採用bloom filter來實現的：minmax，set，ngrambf/tokenbf。

1.9 適用場景

OLAP 分析領域有兩個典型的方向：

ROLAP，通過列存、索引等各類技術手段，提升查詢時性能。
寬表、大表場景，where條件過多且動態，mysql無法每列都建索引。
MOLAP，通過預計算提前生成聚合後的結果數據，降低查詢讀取的數據量，屬於計算換性能方式。
複雜的報表查詢，聚合、篩選很複雜的場景。

既然是OLAP分析，對數據的使用有些基本要求：

絕大多數都是用於讀訪問
無更新、大批量的更新（大於1000行）。（ck沒有高速、低延遲的更新和刪除方法）
查詢的列儘量少，但行數很多。
不需要事務、可以避免事務（clickhouse不支持事務）
數據一致性要求較低
多表join時，只有一個是大表、大表關聯小表
單表的查詢、聚合效率最高，建議數據做寬表處理

2 橫向對比

搬倉系統面臨的是從十幾億數據中進行查詢、聚合分析，從世面上可選的支持海量數據讀寫的中間件中搜集到，能夠有支持類似場景、有比較輕量級的產品大概有Clickhouse、ElasticSearch、TiDB。

2.1 clickhouse與ElasticSearch對比

elastic生態很豐富，es作為其中的存儲產品，從首個版本算起，已經有10年發展歷史，主要解決的是搜索問題。es的底層存儲採用lucene，主要包含行存儲、列存儲和倒排索引，利用分片與副本機制，解決了集群下搜索性能與高可用的問題。

es的優勢：

支持實時更新，對update、delete操作支持更完整。
數據分片更均勻，集群擴展更加方便

es的局限性：

數據量超過千萬或者億級時，若聚合的列數太多，性能也到達瓶頸；
不支持深度二次聚合，導致一些複雜的聚合需求，需要人工編寫代碼在外部實現，這又增加很多開發工作量。

ClickHouse 與 Elasticsearch（排序與聚合查詢）一樣，都採用列式存儲結構，都支持副本分片，不同的是 ClickHouse 底層有一些獨特的實現，如下：

合併樹表引擎系列（MergeTree ），提供了數據分區、一級索引、二級索引。
向量引擎（Vector Engine），數據不僅僅按列存儲，同時還按向量(列的一部分)進行處理，這樣可以更加高效地使用 CPU

網上資料：聚合查詢的性能對比

es對於在處理大查詢，可能導致OOM問題，集群雖然能夠對異常節點有自動恢復機制，但其查詢數據量級不滿足搬倉系統需求。

2.2 clickhouse與TiDB對比

TiDB 是一個分散式 NewSQL 資料庫。它支持水平彈性擴展、ACID 事務、標準 SQL、MySQL 語法和 MySQL 協議,具有數據強一致的高可用特性,是一個不僅適合OLTP場景還適OLAP場景的混合資料庫。

TiDB的優勢：

相容Mysql協議和絕大多數Mysql語法，在大多數情況下，用戶無需修改一行代碼就可以從Mysql無縫遷移到TiDB
高可用、強制一致性（Raft）
支持ACID事務（依賴事務列表），支持二級索引
適合快速的點插入，點更新和點刪除

TiDB的局限性：

更擅長OLTP
性能依賴硬體和集群規模，單機的讀寫性能不夠出色

TiDB更加適合作為MySql的替代，其對MySQL的相容可以使得我們的應用切換成本較低，並且TiDB提供的數據自動分片無需人工維護。

3 為什麼是clickhouse

我們的項目場景是每天要同步十幾億單表數據，基本業務的查詢在百萬，還包含複雜的聚合分析。而Clickhouse在處理單表海量數據的查詢分析方面，是十分優秀的，因此選用clickhouse。

3.1 clickhouse讀寫性能驗證

官方公開benchmark測試顯示能夠達到50MB-200MB/s的寫入吞吐能力，按照每行100Byte估算，大約相當於50W-200W條/s的寫入速度。

下麵是對Clickhouse的讀寫性能的簡單測試，數據量越大差距越明顯。
1）JDBC方式單表、單次寫入性能測試（性能更好）：

2）Mybatis方式單表、單次寫入性能測試：

聚合查詢性能舉例：下圖是搬倉系統一個聚合查詢，在clickhouse中不同數據量級情況下的表現。這個查詢在mysql中執行，一百萬左右的數據量時，耗時已經是分鐘級別。

1）count+distinct方式聚合：

2）group by方式聚合：

3.2 不足之處

作為分散式系統，通常包含三個重要組成：1、存儲引擎。 2、計算引擎。 3、分散式管控層。
在分散式管控層，CK顯得較為薄弱，導致運營、使用成本較高。

分散式表、本地表、副本的維護，這些都是需要用戶自己來定義的，在使用時需要提前學習大量相關內容。
彈性伸縮：ck雖然可以做到水平增加節點，但不支持自動的數據均衡。也就是說當集群擴容後，需要手動將數據重寫分片，或者依賴數據過期，才能保持存儲壓力的均衡。
故障恢復：在節點故障的情況下，ck不能利用其他機器補齊缺失的副本數據，需要用戶ian補齊節點後，才能自動在副本件進行數據同步。

這方面，由於我們直接採用京東雲實例，可以省很多事情。

計算引擎，CK在處理多表關聯查詢、複雜嵌套子查詢等場景，需要人工優化，才能做到明顯的性能提升；
實時寫入，CK使用場景並不適合比較分散的插入，因為其沒有實現記憶體表（Memory Table）結構，每批次寫入直接落盤，單條記錄實時寫入會導致底層大量的小文件，影響查詢性能。

建議單次大批量寫入方式、報表庫場景降低小文件產生概率。

集群模式下本地表的寫入，需要自定義分片規則，否則隨機寫入會造成數據不均勻。
依賴分散式表的寫入，對網路、資源的占用較高。

從數據量增長情況來看，使用場景：

如果預估自己的業務數據量不大(日增不到百萬行), 那麼寫分散式表和本地表都可以, 但要註意如果選擇寫本地表, 請保證每次寫入數據都建立新的連接, 且每個連接寫入的數據量基本相同，手動保持數據均勻
如果預估自己的業務數據量大(日增百萬以上, 併發插入大於10), 那麼請寫本地表
建議每次插入50W行左右數據, 最多不可超過100W行. 總之CH不像MySQL要小事務. 比如1000W行數據, MySQL建議一次插入1W左右, 使用小事務, 執行1000次. CH建議20次,每次50W. 這是MergeTree引擎原理決定的, 頻繁少量插入會導致data part過多, 合併不過來.
MergeTree系列：被設計用於插入極大量的數據到一張表當中。數據可以以數據片段的形式一個接著一個的快速寫入，數據片段在後臺按照一定的規則進行合併。相比在插入時不斷修改（重寫）已存儲的數據，這種策略會高效很多。
Log系列：功能相對簡單，主要用於快速寫入小表（1百萬行左右的表），然後全部讀出的場景。
Integration系列：主要用於將外部數據導入到ClickHouse中，或者在ClickHouse中直接操作外部數據源。
Special系列：大多是為了特定場景而定製的。上面提到的Distributed就屬於該系列。

4.1 MergeTree表引擎

主要用於海量數據分析，支持數據分區、存儲有序、主鍵索引、稀疏索引、數據TTL等。MergeTree支持所有ClickHouse SQL語法，但是有些功能與MySQL並不一致，比如在MergeTree中主鍵並不用於去重。

先看一個創建表的簡單語法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    ...
) ENGINE = ReplacingMergeTree([ver]) 
[PARTITION BY expr]  -- 數據分區規則
[ORDER BY expr] -- 排序鍵
[SAMPLE BY expr] -- 採樣鍵
[SETTINGS index_granularity = 8192, ...] -- 額外參數

先忽略表結構的定義，先看看相比於mysql建表的差異項。（指定集群、分區規則、排序鍵、採樣0-1數字）

數據分區：每個分片副本的內部，數據按照 PARTITION BY 列進行分區，分區以目錄的方式管理，本文樣例中表按照時間進行分區。

基於MergeTree表引擎，CK擴展很多解決特殊場景的表引擎，下麵介紹幾種常用的。

4.1.1 ReplacingMergeTree引擎

該引擎和 MergeTree 的不同之處在於它會刪除排序鍵值(ORDER BY)相同的重覆項。
官方建表語句：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver]) 
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

註意：在設置表引擎時，比MergeTree多了一個參數：ver-版本列，ENGINE = ReplacingMergeTree([ver]) 。
在數據合併的時候，ReplacingMergeTree 從所有具有相同排序鍵的行中選擇一行留下：

如果 ver 列未指定，保留最後一條。
如果 ver 列已指定，保留 ver 值最大的版本。

ReplacingMergeTree引擎，在數據寫入後，不一定立即進行去重操作，或者不一定去重完畢（官方描述在10到15分鐘內會進行合併）。
由於去重依賴的是排序鍵，ReplacingMergeTree引擎是會按照分區鍵進行分區的，因此相同排序鍵的數據有可能被分到不同的分區，不同shard間可能無法去重。

在圖上，分區1的文件塊，會進行數據合併去重，但是分區1與分區2之間的數據是不會進行去重的。因此，如果要保證數據最終能夠去重，要保證相同排序鍵的數據，會寫入相同分區。

數據驗證
下圖為ReplacingMergeTree引擎，以日期作為分區鍵，對於重覆主鍵數據的去重測試：

4.1.2 CollapsingMergeTree引擎

該引擎要求在建表語句中指定一個標記列Sign，按照Sign的值將行分為兩類：Sign=1的行稱之為狀態行，Sign=-1的行稱之為取消行。每次需要新增狀態時，寫入一行狀態行；需要刪除狀態時，則寫入一行取消行。
使用場景：

按clickhouse的架構，期合併、摺疊操作，都是後臺獨立現場執行的，因此時間上並不能控制，何時摺疊完成也無法預知。
如果插入的狀態行與取消行是亂序的，會導致無法正常摺疊

4.1.3 VersionedCollapsingMergeTree表引擎

為瞭解決CollapsingMergeTree亂序寫入情況下無法正常摺疊問題，VersionedCollapsingMergeTree表引擎在建表語句中新增了一列Version，用於在亂序情況下記錄狀態行與取消行的對應關係。
主鍵相同，且Version相同、Sign相反的行，在Compaction時會被刪除。

4.2 數據副本

數據副本放在表引擎這裡單獨講一下，是由於只有 MergeTree 系列里的表可支持副本：

ReplicatedMergeTree
ReplicatedSummingMergeTree
ReplicatedReplacingMergeTree
ReplicatedAggregatingMergeTree
ReplicatedCollapsingMergeTree
ReplicatedVersionedCollapsingMergetree
ReplicatedGraphiteMergeTree
副本是表級別的，不是整個伺服器級的。所以，伺服器里可以同時有複製表和非複製表。
副本不依賴分片。每個分片有它自己的獨立副本。
要使用副本，必須配置文件中設置 ZooKeeper 集群的地址。 （京東雲提供的clickhouse已經完成了配置，我們直接使用即可）

<zookeeper>
    <node index="1">
        <host>example1</host>
        <port>2181</port>
    </node>
    <node index="2">
        <host>example2</host>
        <port>2181</port>
    </node>
    <node index="3">
        <host>example3</host>
        <port>2181</port>
    </node>
</zookeeper>

創建數據副本，是通過設置表引擎位置的參數來控制的，語法示例：

CREATE TABLE table_name
(
    EventDate DateTime,
    CounterID UInt32,
    UserID UInt32
)ENGINE=ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}')  -- 這裡
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)

定義數據副本，只需要在以上表引擎名字的前面，帶上Replicated即可。
上方例子中，使用的表引擎為MergeTree，開啟數據副本，關鍵字Replicated，參數有2個且必填：

zoo_path — ZooKeeper 中該表的路徑。
replica_name — ZooKeeper 中的該表的副本名稱

示例中的取值，採用了變數{layer}、{shard}、{replica}，他們的值取得是配置文件中的值，影響的是生成的副本粒度。

<macros>
    <layer>05</layer>
    <shard>02</shard>
    <replica>example05-02-1.yandex.ru</replica>
</macros>

4.3 Special系列

Special系列的表引擎，大多是為了特定場景而定製的。

Memory：將數據存儲在記憶體中，重啟後會導致數據丟失。查詢性能極好，適合於對於數據持久性沒有要求的1億一下的小表。在ClickHouse中，通常用來做臨時表；
Buffer：為目標表設置一個記憶體buffer，當buffer達到了一定條件之後會flush到磁碟；
File：直接將本地文件作為數據存儲；
Null：寫入數據被丟棄、讀取數據為空。
Distributed：分散式引擎，可以在多個伺服器上進行分散式查詢

4.3.1 Distributed引擎

分散式表引擎，本身不存儲數據，也不占用存儲空間，在定義時需要指定欄位，但必須與要映射的表的結構相同。可用於統一查詢*MergeTree的每個分片，類比sharding中的邏輯表。
比如搬倉系統，使用ReplicatedReplacingMergeTree與Distributed結合，實現通過分散式表實現對本地表的讀寫（寫入操作本地表，讀取操作分散式表）。

CREATE TABLE IF NOT EXISTS {distributed_table} as {local_table}
ENGINE = Distributed({cluster}, '{local_database}', '{local_table}', rand())

說明：

distributed_table：分散式表的表名
local_table：本地表名
as local_table：保持分散式表與本地表的表結構一致。此處也可以用（column dataType）這種定義表結構方式代替
cluster：集群名

註意事項：

分散式表本身並不存儲數據，只是提供了一個可以分散式訪問數據的框架，查詢分散式表的時候clickhouse會自動去查詢對應的每個本地表中的數據，聚合後再返回
註意AS {local_table}，它表明瞭分散式表所對應的本地表（本地表是存儲數據的）
可以配置Distributed表引擎中的最後一個參數 rand()來設置數據條目的分配方式
可以直接往分散式表中寫數據，clickhouse會自動按照上一點所說的方式來分配數據和自平衡，數據實際會寫到本地表
也可以自己寫分片演算法，然後往本地表中寫數據【網上資料的場景是每天上千億寫入，性能考慮要直接寫本地表】

4.4 Log系列

Log系列表引擎功能相對簡單，主要用於快速寫入小表（1百萬行左右的表），然後全部讀出的場景。
幾種Log表引擎的共性是：

數據被順序append寫到磁碟上；
不支持delete、update；
不支持index；
不支持原子性寫；
insert會阻塞select操作。

它們彼此之間的區別是：

TinyLog：不支持併發讀取數據文件，查詢性能較差；格式簡單，適合用來暫存中間數據；
StripLog：支持併發讀取數據文件，查詢性能比TinyLog好；將所有列存儲在同一個大文件中，減少了文件個數；
Log：支持併發讀取數據文件，查詢性能比TinyLog好；每個列會單獨存儲在一個獨立文件中。

4.5 Integration系列

該系統表引擎主要用於將外部數據導入到ClickHouse中，或者在ClickHouse中直接操作外部數據源。

Kafka：將Kafka Topic中的數據直接導入到ClickHouse；
MySQL：將Mysql作為存儲引擎，直接在ClickHouse中對MySQL表進行select等操作；猜測：如果有join需求，又不想將mysql數據導入ck中
JDBC/ODBC：通過指定jdbc、odbc連接串讀取數據源；
HDFS：直接讀取HDFS上的特定格式的數據文件。

5 數據類型

clickhouse支持的數據類型如下圖，分為基礎類型、複合類型、特殊類型。

5.1 CK與Mysql數據類型對照

6 SQL語法-常用介紹

6.1 DDL

6.1.1 創建資料庫：

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster];

如果CREATE 語句中存在IF NOT EXISTS 關鍵字，則當資料庫已經存在時，該語句不會創建資料庫，且不會返回任何錯誤。
ON CLUSTER 關鍵字用於指定集群名稱，在集群環境下必須指定該參數，否則只會在鏈接的節點上創建。

6.1.2 創建本地表：

CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER cluster
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = engine_name()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...];

選項描述：

db：指定資料庫名稱，如果當前語句沒有包含‘db’，則預設使用當前選擇的資料庫為‘db’。
cluster：指定集群名稱，目前固定為default。ON CLUSTER 將在每一個節點上都創建一個本地表。
type：該列數據類型，例如 UInt32。
DEFAULT：該列預設值。如果INSERT中不包含指定的列，那麼將通過表達式計算它的預設值並填充它（與mysql一致）。
MATERIALIZED：物化列表達式，表示該列不能被INSERT，是被計算出來的；在INSERT語句中，不需要寫入該列；在SELECT * 查詢語句結果集不包含該列；需要指定列表來查詢（虛擬列）
ALIAS ：別名列。這樣的列不會存儲在表中。它的值不能夠通過INSERT寫入，同時SELECT查詢使用星號時，這些列也不會被用來替換星號。但是它們可以用於SELECT中，在這種情況下，在查詢分析中別名將被替換。
物化列與別名列的區別：物化列是會保存數據，查詢的時候不需要計算，而別名列不會保存數據，查詢的時候需要計算，查詢時候返回表達式的計算結果

以下選項與表引擎相關，只有MergeTree系列表引擎支持：

PARTITION BY：指定分區鍵。通常按照日期分區，也可以用其他欄位或欄位表達式。（定義分區鍵一定要考慮清楚，它影響數據分佈及查詢性能）
ORDER BY：指定排序鍵。可以是一組列的元組或任意的表達式。
PRIMARY KEY：指定主鍵，預設情況下主鍵跟排序鍵相同。因此，大部分情況下不需要再專門指定一個 PRIMARY KEY 子句。
SAMPLE BY ：抽樣表達式，如果要用抽樣表達式，主鍵中必須包含這個表達式。
SETTINGS：影響性能的額外參數。
GRANULARITY ：索引粒度參數。

示例，創建一個本地表：

CREATE TABLE ontime_local ON CLUSTER default -- 表名為 ontime_local
(
    Year UInt16,
    Quarter UInt8,
    Month UInt8,
    DayofMonth UInt8,
    DayOfWeek UInt8,
    FlightDate Date,
    FlightNum String,
    Div5WheelsOff String,
    Div5TailNum String
)ENGINE = ReplicatedMergeTree(--表引擎用ReplicatedMergeTree，開啟數據副本的合併樹表引擎）
    '/clickhouse/tables/ontime_local/{shard}', -- 指定存儲路徑
    '{replica}')           
 PARTITION BY toYYYYMM(FlightDate)  -- 指定分區鍵，按FlightDate日期轉年+月維度，每月做一個分區
 PRIMARY KEY (intHash32(FlightDate)) -- 指定主鍵，FlightDate日期轉hash值
 ORDER BY (intHash32(FlightDate),FlightNum) -- 指定排序鍵，包含兩列：FlightDate日期轉hash值、FlightNunm字元串。
 SAMPLE BY intHash32(FlightDate)  -- 抽樣表達式，採用FlightDate日期轉hash值
SETTINGS index_granularity= 8192 ;  -- 指定index_granularity指數，每個分區再次劃分的數量

6.1.3 創建分散式表

基於本地表創建一個分散式表。基本語法：

CREATE TABLE  [db.]table_name  ON CLUSTER default
 AS db.local_table_name
ENGINE = Distributed(<cluster>, <database>, <shard table> [, sharding_key])

參數說明：

db：資料庫名。
local_table_name：對應的已經創建的本地表表名。
shard table：同上，對應的已經創建的本地表表名。
sharding_key：分片表達式。可以是一個欄位，例如user_id（integer類型），通過對餘數值進行取餘分片；也可以是一個表達式，例如rand()，通過rand()函數返回值/shards總權重分片；為了分片更均勻，可以加上hash函數，如intHash64(user_id)。

示例，創建一個分散式表：

CREATE TABLE ontime_distributed ON CLUSTER default   -- 指定分散式表的表名，所在集群
 AS db_name.ontime_local                             -- 指定對應的 本地表的表名
ENGINE = Distributed(default, db_name, ontime_local, rand());  -- 指定表引擎為Distributed（固定）

6.1.4 其他建表

clickhouse還支持創建其他類型的表：

6.1.5 修改表

語法與mysql基本一致：
ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN …

支持下列動作：

ADD COLUMN — 添加列
DROP COLUMN — 刪除列
CLEAR COLUMN — 重置列的值
COMMENT COLUMN — 給列增加註釋說明
MODIFY COLUMN — 改變列的值類型，預設表達式以及TTL

舉例：ALTER TABLE bd01.table_1 ADD COLUMN browser String AFTER name; – 在name列後面追加一列

6.2 DML

註意：

索引列不支持更新、刪除
分散式表不支持更新、刪除

7 複雜查詢JOIN

所有標準 SQL JOIN 支持類型（INNER和OUTER可以省略）：

INNER JOIN，只返回匹配的行。
LEFT OUTER JOIN，除了匹配的行之外，還返回左表中的非匹配行。
RIGHT OUTER JOIN，除了匹配的行之外，還返回右表中的非匹配行。
FULL OUTER JOIN，除了匹配的行之外，還會返回兩個表中的非匹配行。
CROSS JOIN，產生整個表的笛卡爾積, “join keys” 是不指定。

查詢優化：

A join B 的查詢，比from A,B,C 多表的性能高很多
global join 會把書記發送給所有節點參與計算，針對較小的維度表性能較好
JOIN會在背地節點操作，適合於相同分片欄位的兩張表關聯（A表與B表的分片欄位都包含欄位M）
IN的性能比JOIN好，優先使用JOIN
先過濾再join效率更好（減低每個分片關聯數據量級）
在做多表join時，A表的查詢過濾條件中如果能包含與B表的ON expr中欄位過濾條件，性能更好
join的順序，大表在左，小表在右；ck查詢時會從右向左執行

對比JOIN與IN的查詢複雜度：
CK常用的表引擎會是分散式存儲，因此查詢過程一定是每個分片進行一次查詢，這就導致了sql的複雜度越高，查詢鎖掃描的分片數量越多，耗時也就越久。

假設AB兩個表，分別存儲在10個分片中，join則是查詢10次A表的同時，join10次B表，合計要10*10次。採用Global join則會先查詢10次並生成臨時表，再用臨時表取和B表join，合計要10+10次。

這算是分散式架構的查詢特點，如果能幹預數據分片規則，如果查詢條件中帶有分片列，則可以直接定位到包含數據的分片上，從而減小查詢次數。

CK對於join語法上雖然支持，但是性能並不高。當join的左邊是子查詢結果時，ck是無法進行分散式join的。

8 MySQL遷移到CK

數據同步成本：clickhouse可以做到與mysql的表結構一致，進而數據同步成本較低，不需要調整數據結構、不需要額外做寬表處理（當然轉為寬表效率更高）。
SQL遷移成本：支持jdbc、mybatis接入；支持標準SQL的語法；支持join、in、函數，SQL遷移成本較低。

當然如果花功夫對錶結構、SQL、索引等進行優化，能得到更好的查詢效率。

官方支持
在2020年下半年，Yandex 公司在 ClickHouse 社區發佈了MaterializeMySQL引擎，支持從MySQL全量及增量實時數據同步。MaterializeMySQL引擎目前支持 MySQL 5.6/5.7/8.0 版本，相容 Delete/Update 語句，及大部分常用的 DDL 操作。
也就是說，CK支持作為MySQL的從節點存在，依賴訂閱binlog方式實現。
https://bbs.huaweicloud.com/blogs/238417

9 總結

ClickHouse更加適合OLAP場景，在報表庫中有極大性能優勢。如果想作為應用資料庫，可以靈活採用其表引擎特點，儘量避免數據修改。其實，沒有最好的，只有最合適的。

作者：京東物流耿巨集宇

來源：京東雲開發者社區