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場景，在報表庫中有極大效能優勢。如果想作為應用資料庫，可以靈活採用其表引擎特點，儘量避免資料修改。其實，沒有最好的，只有最合適的。

作者：京東物流耿宏宇

來源：京東雲開發者社群