一、序言

面向大數(shù)據(jù)量查詢(xún)數(shù)據(jù)庫(kù)，優(yōu)點(diǎn)是在較大數(shù)據(jù)量（千萬(wàn)級(jí)）的前提下具有較好的查詢(xún)性能。

1、應(yīng)用場(chǎng)景

ClickHouse應(yīng)用于OLAP（在線分析處理）領(lǐng)域，具體來(lái)說(shuō)滿足如下特點(diǎn)使用此技術(shù)比較合適：

• 事務(wù)型數(shù)據(jù)庫(kù)表通過(guò)連表查詢(xún)轉(zhuǎn)換成寬表
• 聚合（統(tǒng)計(jì)）計(jì)算使用較多
• 對(duì)查詢(xún)效率要求較高，有限時(shí)間范圍內(nèi)能夠容忍非冪等性查詢(xún)（最終一致性）

2、學(xué)習(xí)姿勢(shì)

大多數(shù)學(xué)習(xí)ClickHouse是從OLTP數(shù)據(jù)庫(kù)開(kāi)始的，比如Mysql數(shù)據(jù)庫(kù)。對(duì)于千萬(wàn)級(jí)別的數(shù)據(jù)，以InnoDB為存儲(chǔ)引擎的表，僅僅是統(tǒng)計(jì)表行數(shù)這一需求，執(zhí)行效率很低，對(duì)于一些聚合函數(shù)，相應(yīng)延遲同樣無(wú)法接受。

提高數(shù)據(jù)庫(kù)硬件水平，一定程度上能夠改善查詢(xún)效率問(wèn)題，但仍然不能徹底解決查詢(xún)效率問(wèn)題。ClickHouse一推出就大火更加印證開(kāi)發(fā)者在較大數(shù)據(jù)量的前提下希望有個(gè)合理查詢(xún)效率的需求是多么的急切。

以典型的Mysql數(shù)據(jù)庫(kù)讀寫(xiě)分離為例，橫向?qū)Ρ菴lickHouse，對(duì)比Mysql為何查詢(xún)慢以及ClickHouse為何查詢(xún)要快，在此基礎(chǔ)上綜合考慮OLTP如何與OLAP協(xié)同工作。

二、知識(shí)儲(chǔ)備

（一）磁盤(pán)IO

1、數(shù)據(jù)量與查詢(xún)效率

數(shù)據(jù)量在超過(guò)一定邊界后，查詢(xún)效率急劇下降，造成查詢(xún)效率低下的主要原因是磁盤(pán)IO。比如Mysql數(shù)據(jù)庫(kù)，通過(guò)服務(wù)器優(yōu)化（增加硬件資源消耗），能夠提高一定的性能，并不能從軟件層次有效提高查詢(xún)效率。

千萬(wàn)級(jí)別的大表，查詢(xún)性能較低，主要涉及磁盤(pán)這塊，影響因素有兩條：一是數(shù)據(jù)索引定位；二是磁盤(pán)IO。

（二）性能對(duì)比

1、磁盤(pán)工作機(jī)制

操作系統(tǒng)從磁盤(pán)讀取數(shù)據(jù)到內(nèi)存中，大體經(jīng)過(guò)如下過(guò)程：索引到數(shù)據(jù)存儲(chǔ)位置；以頁(yè)為單位IO數(shù)據(jù)。其中數(shù)據(jù)索引完畢，IO過(guò)程相對(duì)較快（速度與內(nèi)存IO不是一個(gè)數(shù)量級(jí)）。

磁盤(pán)頁(yè)IO表示在磁盤(pán)頁(yè)上命中一條記錄與全部命中，IO時(shí)間相同。實(shí)際使用過(guò)程中，查詢(xún)一條記錄與多條連續(xù)記錄有時(shí)候時(shí)間相似（底層邏輯都是從磁盤(pán)IO一個(gè)磁盤(pán)頁(yè)的數(shù)據(jù)）。

2、按行（列）存儲(chǔ)

通過(guò)簡(jiǎn)單示例比較按行存儲(chǔ)與按列存儲(chǔ)對(duì)查詢(xún)的影響，主要以磁盤(pán)IO最為技術(shù)指標(biāo)。測(cè)試數(shù)據(jù)量為千萬(wàn)級(jí)別。

CREATE TABLE `human_name` (
  `id` bigint(20) NOT NULL COMMENT 'ID',
  `name` varchar(32) DEFAULT NULL COMMENT '名稱(chēng)',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '邏輯刪除',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
  `delete_time` datetime DEFAULT NULL COMMENT '刪除時(shí)間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='人名信息表';

通過(guò)不同的場(chǎng)景，對(duì)比不同存儲(chǔ)方式在磁盤(pán)IO上的消耗，進(jìn)而比較查詢(xún)效率。

（1）通過(guò)id查詢(xún)name

行存儲(chǔ)在索引上節(jié)約時(shí)間；列存儲(chǔ)在磁盤(pán)IO上節(jié)約時(shí)間，數(shù)據(jù)量較小可以忽略差異，本回合二者持平。

（2）通過(guò)批id查詢(xún)name

批查詢(xún)是指有限區(qū)間查詢(xún)或者有限集合查詢(xún)，數(shù)據(jù)量百條以?xún)?nèi)。有限區(qū)間查詢(xún)與有限集合查詢(xún)，對(duì)應(yīng)的數(shù)據(jù)量較小，性能表現(xiàn)差別不大。仔細(xì)分析過(guò)程，二者仍然存在明顯的差異。

區(qū)間查詢(xún)的效率比有限集合查詢(xún)效率要高，原因如下：區(qū)間查詢(xún)數(shù)據(jù)存儲(chǔ)是連續(xù)的，單次數(shù)據(jù)索引，單頁(yè)磁盤(pán)IO（數(shù)據(jù)量較?。?，緊湊的數(shù)據(jù)查詢(xún)，按行存儲(chǔ)略占優(yōu)勢(shì)，考慮到是查詢(xún)單個(gè)字段，因此磁盤(pán)數(shù)據(jù)索引次數(shù)均為一次（按列查詢(xún)多少列即索引多少次）。

集合查詢(xún)由于查詢(xún)條件非連續(xù)，需要單獨(dú)索引并完成磁盤(pán)IO，集合中有N個(gè)元素（隨機(jī)）需要索引N次，以頁(yè)為單位的磁盤(pán)IO

（3）通過(guò)id查詢(xún)整行數(shù)據(jù)

按列存儲(chǔ)通常比按行存儲(chǔ)的查詢(xún)效率要高，對(duì)于寬表（幾十列以上的聚合表），效果更加明顯。對(duì)于查詢(xún)，更多的需求是查詢(xún)某列數(shù)據(jù)或者某幾列數(shù)據(jù)，按列存儲(chǔ)的數(shù)據(jù)庫(kù)能夠大大減少磁盤(pán)數(shù)據(jù)的掃描范圍以及磁盤(pán)與內(nèi)存之間的IO，從IO層面提高了查詢(xún)效率。

極端情況

數(shù)據(jù)庫(kù)存儲(chǔ)id和name數(shù)據(jù)，兩者都是非空的必選數(shù)據(jù)，這種情況下按行（列）存儲(chǔ)從IO層面來(lái)講是相似的，數(shù)據(jù)在磁盤(pán)上掃描范圍和讀寫(xiě)IO差不多。通過(guò)id查詢(xún)name或者批量id查詢(xún)name，借助于哈希索引，按行存儲(chǔ)可能具有O(1)的時(shí)間復(fù)雜度。

實(shí)際數(shù)據(jù)不可能這么純粹，行記錄通常會(huì)有保存時(shí)間、修改時(shí)間、刪除時(shí)間、部分核心字段的修改時(shí)間，數(shù)據(jù)量較少時(shí)，附屬字段對(duì)查詢(xún)的影響較小，一旦數(shù)據(jù)量超過(guò)一定閥值，對(duì)查詢(xún)的影響逐步凸顯。按列存儲(chǔ)能夠忽略附屬字段的磁盤(pán)掃描與IO。

綜合來(lái)講，從查詢(xún)的角度來(lái)講，按列存儲(chǔ)要優(yōu)于按行存儲(chǔ)。

三、基礎(chǔ)知識(shí)

（一）表結(jié)構(gòu)

clickhouse使用的表結(jié)構(gòu)與常見(jiàn)的關(guān)系數(shù)據(jù)庫(kù)有一定的區(qū)別。

1、排序

在合并樹(shù)家族引擎中，表排序?qū)傩允潜剡x項(xiàng)。通過(guò)ORDER BY關(guān)鍵字設(shè)置分區(qū)內(nèi)數(shù)據(jù)的排序策略，數(shù)據(jù)在導(dǎo)入或者保存時(shí)按照排序策略有序存儲(chǔ)，有序數(shù)據(jù)直接存儲(chǔ)在磁盤(pán)中，查詢(xún)時(shí)具有較高的效率。

排序列也是索引列，高頻用作查詢(xún)條件的字段添加到排序列有利于提高查詢(xún)效率。

2、主鍵

主鍵的定義比較奇怪，僅僅是起到過(guò)濾查詢(xún)索引的作用，沒(méi)有唯一約束的效果。

當(dāng)設(shè)置有主鍵時(shí)，主鍵字段必需包含在排序?qū)傩灾?，且從左到右依次展開(kāi)。

3、默認(rèn)值

Null類(lèi)型幾乎總是會(huì)拖累性能，原因如下：空值無(wú)法被索引；需要使用額外的特殊占位符單獨(dú)處理。按列存儲(chǔ)每列數(shù)據(jù)個(gè)數(shù)一致有利于數(shù)據(jù)查詢(xún)。

數(shù)據(jù)在導(dǎo)入之前需要做空值處理，將空值替換成與業(yè)務(wù)無(wú)關(guān)的數(shù)據(jù)。

（二）表引擎

clickhouse表引擎非常豐富，其中最常用的是合并樹(shù)家族引擎。

1、MergeTree

MergeTree引擎能夠?qū)崿F(xiàn)較大數(shù)據(jù)量的查詢(xún)需求，由于主鍵沒(méi)有唯一索引約束，存在重復(fù)行的情況。在數(shù)據(jù)遷移的過(guò)程中，不可避免會(huì)出現(xiàn)重復(fù)數(shù)據(jù)導(dǎo)入的情況，業(yè)務(wù)上能夠容忍部分重復(fù)數(shù)據(jù)，或者從應(yīng)用端處理重復(fù)數(shù)據(jù)，可以選擇此引擎。

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引擎必需指定排序字段。

MergeTree的主鍵的作用是加速查詢(xún)，不是類(lèi)似MySQL保持記錄唯一。

2、ReplacingMergeTree

ReplacingMergeTree引擎用來(lái)去除重復(fù)行，此處的去重有三個(gè)層次的含義：在分區(qū)內(nèi)去重；以主鍵字段為比較對(duì)象；數(shù)據(jù)去重實(shí)踐只會(huì)在合并時(shí)發(fā)生。

-- 強(qiáng)制后臺(tái)合并，去重時(shí)所在表停止服務(wù)
optimize table test_tbl_replacing final;

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

• optimize是后臺(tái)動(dòng)作，無(wú)法預(yù)測(cè)具體執(zhí)行時(shí)間點(diǎn)；
• 在沒(méi)有徹底o(hù)ptimize之前，不能確定是否仍有重復(fù)數(shù)據(jù)；
• 手動(dòng)執(zhí)行optimize在海量數(shù)據(jù)場(chǎng)景下要消耗大量時(shí)間，無(wú)法滿足業(yè)務(wù)即時(shí)查詢(xún)的需求；
• 在分布式場(chǎng)景下，相同primary key的數(shù)據(jù)可能被sharding到不同節(jié)點(diǎn)上，不同shard間可能無(wú)法去重；

ReplacingMergeTree更多用于確保數(shù)據(jù)最終被去重，無(wú)法保證查詢(xún)過(guò)程中主鍵不重復(fù)。

ReplacingMergeTree(create_time)填入?yún)?shù)為版本字段，重復(fù)記錄保留版本號(hào)最大最在行；允許為空，默認(rèn)保留重復(fù)行最后插入的記錄。

去重深刻理解

這里的去重并不能達(dá)到關(guān)系型數(shù)據(jù)庫(kù)嚴(yán)格意義去重的目的，使用時(shí)需要注意這個(gè)現(xiàn)象。另外不能以非黑即白的想法考慮這個(gè)問(wèn)題，ClickHouse在提高查詢(xún)速度時(shí)做了一定的妥協(xié)。

3、SummingMergeTree

SummingMergeTree提供的是一種預(yù)聚合引擎，等效為以order by字段為單位分組，然后執(zhí)行聚合求和操作，不過(guò)這些結(jié)果是提前計(jì)算好了的，查詢(xún)時(shí)不需要實(shí)時(shí)計(jì)算。

如果聚合的值不滿足要求，可以在查詢(xún)結(jié)果集上通過(guò)聚合函數(shù)再次聚合，此時(shí)屬于實(shí)時(shí)計(jì)算。

（三）內(nèi)置函數(shù)

常見(jiàn)的內(nèi)置函數(shù)需要特別指出，新建表模式、數(shù)據(jù)導(dǎo)入等方面會(huì)有應(yīng)用。

1、格式化日期

格式化分區(qū)函數(shù)常用于表的分區(qū)設(shè)置，以天為單位的分區(qū)是常見(jiàn)的分區(qū)設(shè)置。

select toYYYYMMDD(now())

2、哈希函數(shù)

以name字段的哈希字符串作為分區(qū)策略。

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);

表可以不設(shè)置主鍵，一旦設(shè)置主鍵，那么表必選排序?qū)傩员匦枰灾麈I的順序依次展開(kāi)。

直接用原始字符串字段值作為分區(qū)策略也是可行的，考慮到字符串的值域范圍比較廣，用哈希函數(shù)處理會(huì)比較安全。

3、日期函數(shù)

獲取各種日期函數(shù)，如果不指定時(shí)區(qū)，默認(rèn)讀取宿主機(jī)的時(shí)區(qū)信息。

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

四、安裝與配置

版本選擇長(zhǎng)期支持版本20.8，采用手動(dòng)安裝的方式進(jìn)行。

（一）安裝

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、正則替換注釋

使用模式<!-[\s\S]*?->$查詢(xún)配置XML配置文件中所有注釋。

# 格式化XML文件
xmllint --format config.xml 

2、服務(wù)端配置文件

服務(wù)端配置文件有兩個(gè)config.xml和users.xml，前者是只讀配置，后者可以在運(yùn)行時(shí)動(dòng)態(tài)修改。

五、小結(jié)

ClickHouse生態(tài)在快速迭代，很多亮眼的功能尚處于開(kāi)發(fā)中或者測(cè)試中，這里選取部分特性與大家分享。

以上就是數(shù)據(jù)分析數(shù)據(jù)庫(kù)ClickHouse在大數(shù)據(jù)領(lǐng)域應(yīng)用實(shí)踐的詳細(xì)內(nèi)容，更多關(guān)于ClickHouse大數(shù)據(jù)應(yīng)用的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論