亚洲视频二区_亚洲欧洲日本天天堂在线观看_日韩一区二区在线观看_中文字幕不卡一区

本文對 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 的行稱之為取消行。每次需要新增狀態時，寫入一行狀態行；需要刪除狀態時，則寫入一行取消行。

使用場景：

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

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

注意：

1. 索引列不支持更新、刪除
2. 分布式表不支持更新、刪除

7 復雜查詢 JOIN

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

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

查詢優化：

1. A join B 的查詢，比 from A,B,C 多表的性能高很多
2. global join 會把書記發送給所有節點參與計算，針對較小的維度表性能較好
3. JOIN 會在背地節點操作，適合于相同分片字段的兩張表關聯（A 表與 B 表的分片字段都包含字段 M）
4. IN 的性能比 JOIN 好，優先使用 JOIN
5. 先過濾再 join 效率更好（減低每個分片關聯數據量級）
6. 在做多表 join 時，A 表的查詢過濾條件中如果能包含與 B 表的 ON expr 中字段過濾條件，性能更好
7. 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 場景，在報表庫中有極大性能優勢。如果想作為應用數據庫，可以靈活采用其表引擎特點，盡量避免數據修改。其實，沒有最好的，只有最合適的。

 

作者：京東物流 耿宏宇

來源：京東云開發者社區