資料分析資料庫ClickHouse在巨量資料領域應用實踐

2022-04-02 19:00:18

一、序言

面向巨量資料量查詢資料庫，優點是在較巨量資料量（千萬級）的前提下具有較好的查詢效能。

1、應用場景

ClickHouse應用於OLAP（線上分析處理）領域，具體來說滿足如下特點使用此技術比較合適：

事務型資料庫表通過連表查詢轉換成寬表
聚合（統計）計算使用較多
對查詢效率要求較高，有限時間範圍內能夠容忍非冪等性查詢（最終一致性）

2、學習姿勢

大多數學習ClickHouse是從OLTP資料庫開始的，比如Mysql資料庫。對於千萬級別的資料，以InnoDB為儲存引擎的表，僅僅是統計表行數這一需求，執行效率很低，對於一些聚合函數，相應延遲同樣無法接受。

提高資料庫硬體水平，一定程度上能夠改善查詢效率問題，但仍然不能徹底解決查詢效率問題。ClickHouse一推出就大火更加印證開發者在較巨量資料量的前提下希望有個合理查詢效率的需求是多麼的急切。

以典型的Mysql資料庫讀寫分離為例，橫向對比ClickHouse，對比Mysql為何查詢慢以及ClickHouse為何查詢要快，在此基礎上綜合考慮OLTP如何與OLAP協同工作。

二、知識儲備

（一）磁碟IO

1、資料量與查詢效率

資料量在超過一定邊界後，查詢效率急劇下降，造成查詢效率低下的主要原因是磁碟IO。比如Mysql資料庫，通過伺服器優化（增加硬體資源消耗），能夠提高一定的效能，並不能從軟體層次有效提高查詢效率。

千萬級別的大表，查詢效能較低，主要涉及磁碟這塊，影響因素有兩條：一是資料索引定位；二是磁碟IO。

（二）效能對比

1、磁碟工作機制

作業系統從磁碟讀取資料到記憶體中，大體經過如下過程：索引到資料儲存位置；以頁為單位IO資料。其中資料索引完畢，IO過程相對較快（速度與記憶體IO不是一個數量級）。

磁碟頁IO表示在磁碟頁上命中一條記錄與全部命中，IO時間相同。實際使用過程中，查詢一條記錄與多條連續記錄有時候時間相似（底層邏輯都是從磁碟IO一個磁碟頁的資料）。

2、按行（列）儲存

通過簡單範例比較按行儲存與按列儲存對查詢的影響，主要以磁碟IO最為技術指標。測試資料量為千萬級別。

CREATE TABLE `human_name` (
  `id` bigint(20) NOT NULL COMMENT 'ID',
  `name` varchar(32) DEFAULT NULL COMMENT '名稱',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '邏輯刪除',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `delete_time` datetime DEFAULT NULL COMMENT '刪除時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='人名資訊表';

通過不同的場景，對比不同儲存方式在磁碟IO上的消耗，進而比較查詢效率。

（1）通過id查詢name

儲存方式	索引方式	磁碟IO	執行過程
行儲存	雜湊索引O(1) BTree索引O(logN)	整行資料	磁碟上執行選擇操作，記憶體執行投影操作
列儲存	主鍵稀疏索引+二級索引	單行name列資料	在磁碟上執行選擇操作同時完成了投影操作

行儲存在索引上節約時間；列儲存在磁碟IO上節約時間，資料量較小可以忽略差異，本回合二者持平。

（2）通過批id查詢name

批查詢是指有限區間查詢或者有限集合查詢，資料量百條以內。有限區間查詢與有限集合查詢，對應的資料量較小，效能表現差別不大。仔細分析過程，二者仍然存在明顯的差異。

區間查詢的效率比有限集合查詢效率要高，原因如下：區間查詢資料儲存是連續的，單次資料索引，單頁磁碟IO（資料量較小），緊湊的資料查詢，按行儲存略佔優勢，考慮到是查詢單個欄位，因此磁碟資料索引次數均為一次（按列查詢多少列即索引多少次）。

集合查詢由於查詢條件非連續，需要單獨索引並完成磁碟IO，集合中有N個元素（隨機）需要索引N次，以頁為單位的磁碟IO

（3）通過id查詢整行資料

按列儲存通常比按行儲存的查詢效率要高，對於寬表（幾十列以上的聚合表），效果更加明顯。對於查詢，更多的需求是查詢某列資料或者某幾列資料，按列儲存的資料庫能夠大大減少磁碟資料的掃描範圍以及磁碟與記憶體之間的IO，從IO層面提高了查詢效率。

極端情況

資料庫儲存id和name資料，兩者都是非空的必選資料，這種情況下按行（列）儲存從IO層面來講是相似的，資料在磁碟上掃描範圍和讀寫IO差不多。通過id查詢name或者批次id查詢name，藉助於雜湊索引，按行儲存可能具有O(1)的時間複雜度。

實際資料不可能這麼純粹，行記錄通常會有儲存時間、修改時間、刪除時間、部分核心欄位的修改時間，資料量較少時，附屬欄位對查詢的影響較小，一旦資料量超過一定閥值，對查詢的影響逐步凸顯。按列儲存能夠忽略附屬欄位的磁碟掃描與IO。

綜合來講，從查詢的角度來講，按列儲存要優於按行儲存。

三、基礎知識

（一）表結構

clickhouse使用的表結構與常見的關聯式資料庫有一定的區別。

1、排序

在合併樹家族引擎中，表排序屬性是必選項。通過ORDER BY關鍵字設定分割區內資料的排序策略，資料在匯入或者儲存時按照排序策略有序儲存，有序資料直接儲存在磁碟中，查詢時具有較高的效率。

排序列也是索引列，高頻用作查詢條件的欄位新增到排序列有利於提高查詢效率。

2、主鍵

主鍵的定義比較奇怪，僅僅是起到過濾查詢索引的作用，沒有唯一約束的效果。

當設定有主鍵時，主鍵欄位必需包含在排序屬性中，且從左到右依次展開。

3、預設值

Null型別幾乎總是會拖累效能，原因如下：空值無法被索引；需要使用額外的特殊預留位置單獨處理。按列儲存每列資料個數一致有利於資料查詢。

資料在匯入之前需要做空值處理，將空值替換成與業務無關的資料。

（二）表引擎

clickhouse表引擎非常豐富，其中最常用的是合併樹家族引擎。

1、MergeTree

MergeTree引擎能夠實現較巨量資料量的查詢需求，由於主鍵沒有唯一索引約束，存在重複行的情況。在資料遷移的過程中，不可避免會出現重複資料匯入的情況，業務上能夠容忍部分重複資料，或者從應用端處理重複資料，可以選擇此引擎。

CREATE TABLE test_tbl (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = MergeTree()
	 PARTITION BY toYYYYMMDD(create_time)
     ORDER BY  (create_time)
     PRIMARY KEY (id)
     TTL create_time + INTERVAL 1 MONTH
     SETTINGS index_granularity=8192;

MergeTree引擎必需指定排序欄位。

屬性	含義	備註
ORDER BY	指定排序欄位（必選）	指定一個或者多個欄位作為排序欄位（分割區內排序）
PARTITION BY	指定分割區規則	一般而言以日期作為表分割區的策略
PRIMARY KEY	主鍵欄位	主鍵元素可以重複並且能夠指定多個欄位
TTL	記錄過期時間	可以指定記錄的過期時間
SETTINGS	稀疏索引間隔	無特別需求使用預設值即可

MergeTree的主鍵的作用是加速查詢，不是類似MySQL保持記錄唯一。

2、ReplacingMergeTree

ReplacingMergeTree引擎用來去除重複行，此處的去重有三個層次的含義：在分割區內去重；以主鍵欄位為比較物件；資料去重實踐只會在合併時發生。

-- 強制後臺合併，去重時所在錶停止服務
optimize table test_tbl_replacing final;

ReplacingMergeTree提供了主鍵去重的能力，但是仍舊有以下限制：

optimize是後臺動作，無法預測具體執行時間點；
在沒有徹底optimize之前，不能確定是否仍有重複資料；
手動執行optimize在海量資料場景下要消耗大量時間，無法滿足業務即時查詢的需求；
在分散式場景下，相同primary key的資料可能被sharding到不同節點上，不同shard間可能無法去重；

ReplacingMergeTree更多用於確保資料最終被去重，無法保證查詢過程中主鍵不重複。

ReplacingMergeTree(create_time)填入引數為版本欄位，重複記錄保留版本號最大最在行；允許為空，預設保留重複行最後插入的記錄。

去重深刻理解

這裡的去重並不能達到關係型資料庫嚴格意義去重的目的，使用時需要注意這個現象。另外不能以非黑即白的想法考慮這個問題，ClickHouse在提高查詢速度時做了一定的妥協。

3、SummingMergeTree

SummingMergeTree提供的是一種預聚合引擎，等效為以order by欄位為單位分組，然後執行聚合求和操作，不過這些結果是提前計算好了的，查詢時不需要實時計算。

如果聚合的值不滿足要求，可以在查詢結果集上通過聚合函數再次聚合，此時屬於實時計算。

（三）內建函數

常見的內建函數需要特別指出，新建表模式、資料匯入等方面會有應用。

1、格式化日期

格式化分割區函數常用於表的分割區設定，以天為單位的分割區是常見的分割區設定。

select toYYYYMMDD(now())

2、雜湊函數

以name欄位的雜湊字串作為分割區策略。

CREATE TABLE default.test02 (
`id` UInt16,
`name` String,
`create_time` Date) ENGINE = MergeTree() 
PARTITION BY LOWER(hex(MD5(name))) 
PRIMARY KEY id
ORDER BY (id,create_time);

表可以不設定主鍵，一旦設定主鍵，那麼表必選排序屬性必需以主鍵的順序依次展開。

直接用原始字串欄位值作為分割區策略也是可行的，考慮到字串的值域範圍比較廣，用雜湊函數處理會比較安全。

3、日期函數

獲取各種日期函數，如果不指定時區，預設讀取宿主機的時區資訊。

SELECT toDateTime(now()) AS t1,
       toDate(now()) AS t2,
       toDate(now(), 'Asia/Shanghai') AS t3,
       toString(now()) AS t4

四、安裝與設定

版本選擇長期支援版本20.8，採用手動安裝的方式進行。

（一）安裝

rpm -ivh clickhouse-server-20.8.19.4-2.noarch.rpm
rpm -ivh clickhouse-client-20.8.19.4-2.noarch.rpm
rpm -ivh clickhouse-common-static-20.8.19.4-2.x86_64.rpm

（二）設定

1、正則替換註釋

使用模式<!-[sS]*?->$查詢設定XML組態檔中所有註釋。

# 格式化XML檔案
xmllint --format config.xml

2、伺服器端組態檔

伺服器端組態檔有兩個config.xml和users.xml，前者是唯讀設定，後者可以在執行時動態修改。

五、小結

ClickHouse生態在快速迭代，很多亮眼的功能尚處於開發中或者測試中，這裡選取部分特性與大家分享。

以上就是資料分析資料庫ClickHouse在巨量資料領域應用實踐的詳細內容，更多關於ClickHouse巨量資料應用的資料請關注it145.com其它相關文章！