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

作者：京東物流耿宏宇

來源：京東云開發者社區

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/555798.html

標籤：MySQL

上一篇：MySQL一個關于derived table的bug描述與規避

下一篇：返回列表