MySQL ClickHouse常用表引擎超詳細(xì)講解

更新時(shí)間：2022年11月02日 09:14:55 作者：小基基o_O

這篇文章主要介紹了MySQL ClickHouse常用表引擎，ClickHouse表引擎中，CollapsingMergeTree和VersionedCollapsingMergeTree都能通過標(biāo)記位按規(guī)則折疊數(shù)據(jù)，從而達(dá)到更新和刪除的效果

表引擎

表引擎作用：數(shù)據(jù)的存儲(chǔ)方式和位置

支持哪些查詢以及如何支持

并發(fā)數(shù)據(jù)訪問

索引的使用（如果存在）

是否可以執(zhí)行多線程請求

數(shù)據(jù)復(fù)制參數(shù)

常見表引擎	家族	說明	索引	備注
TinyLog	Log Family	以列文件的形式保存在硬盤數(shù)據(jù)寫入時(shí)，追加到文件末尾	不支持	可用于存儲(chǔ)小批量處理的中間數(shù)據(jù)
Memory	其它	數(shù)據(jù)以未壓縮的原始形式直接保存在內(nèi)存	不支持	適用于少量數(shù)據(jù)的高性能查詢
MergeTree	MergeTree Family	支持列式存儲(chǔ)、分區(qū)、稀疏索引、二級索引…	支持	單節(jié)點(diǎn)ClickHouse實(shí)例的默認(rèn)表引擎

合并樹家族

合并樹家族特點(diǎn)：

快速插入數(shù)據(jù)并進(jìn)行后續(xù)的后臺(tái)數(shù)據(jù)處理

支持?jǐn)?shù)據(jù)復(fù)制

支持分區(qū)

支持稀疏索引

稀疏索引原理

稀疏索引占用空間小，范圍批量查詢快，但單點(diǎn)查詢較慢

MergeTree

擅長插入極大量的數(shù)據(jù)到一張表
數(shù)據(jù) 能以數(shù)據(jù)片段的形式一個(gè)接一個(gè)地快速寫入，數(shù)據(jù)片段在后臺(tái) 按一定的規(guī)則進(jìn)行合并

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster](
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

關(guān)鍵詞	簡述
ENGINE	引擎
ORDER BY	數(shù)據(jù)排序規(guī)則
PARTITION BY	分區(qū)
PRIMARY KEY	索引規(guī)則
TTL	數(shù)據(jù)生命周期
SETTINGS	其它設(shè)置

排序鍵

ORDER BY（必選項(xiàng)）

規(guī)定了分區(qū)內(nèi)的數(shù)據(jù)按照哪些字段進(jìn)行按序存儲(chǔ)

如果不需要排序，就用ORDER BY tuple()

此情況下，數(shù)據(jù)順序是根據(jù)插入順序

如果想要按INSERT ... SELECT的數(shù)據(jù)順序來存儲(chǔ)，就設(shè)置max_insert_threads=1

若想按數(shù)據(jù)存儲(chǔ)順序查出數(shù)據(jù)，可用單線程查詢

對于有序數(shù)據(jù)，數(shù)據(jù)一致性越高，壓縮效率越高

主鍵

PRIMARY KEY（可選項(xiàng)）

作用：為列數(shù)據(jù)提供稀疏索引（不是唯一約束），提升列查詢效率

默認(rèn)情況下，主鍵與排序鍵相同；通常不需要顯式PRIMARY KEY子句，除非主鍵≠排序鍵

要求：主鍵列必須是排序列的前綴

例如ORDER BY (a,b)則PRIMARY KEY后可以是(a,b)或(a)

sparse index

分區(qū)

PARTITION BY分區(qū)（可選項(xiàng)）

分區(qū)作用：縮小掃描范圍，優(yōu)化查詢速度

并行：分區(qū)后，面對涉及跨分區(qū)的查詢統(tǒng)計(jì)，會(huì)以分區(qū)為單位并行處理

如果不填：只會(huì)使用一個(gè)分區(qū)

數(shù)據(jù)寫入與分區(qū)合并：

任何一個(gè)批次的數(shù)據(jù)寫入都會(huì)產(chǎn)生一個(gè)臨時(shí)分區(qū)，不會(huì)納入任何一個(gè)已有的分區(qū)。

寫入后，過一段時(shí)間（約10多分鐘），會(huì)自動(dòng)執(zhí)行合并操作，把臨時(shí)分區(qū)的數(shù)據(jù)合并

可用OPTIMIZE TABLE 表名 [FINAL]主動(dòng)執(zhí)行合并

通常不需要使用分區(qū)鍵。使用時(shí)，不建議使用比月更細(xì)粒度的分區(qū)鍵

分區(qū)過多=>(列式)查詢時(shí)掃描文件過多=>性能低

-- 建表
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  uid UInt32,
  sku_id String,
  total_amount Decimal(9,2),
  create_time Datetime
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY (uid)
ORDER BY (uid,sku_id);
-- 插數(shù)據(jù)2次
INSERT INTO t1 VALUES
(1,'sku1',1.00,'2020-06-01 12:00:00'),
(2,'sku1',9.00,'2020-06-02 13:00:00'),
(3,'sku2',6.00,'2020-06-02 12:00:00');
INSERT INTO t1 VALUES
(1,'sku1',1.00,'2020-06-01 12:00:00'),
(2,'sku1',9.00,'2020-06-02 13:00:00'),
(3,'sku2',6.00,'2020-06-02 12:00:00');
-- 插完后立即插，會(huì)發(fā)現(xiàn)數(shù)據(jù)寫入臨時(shí)分區(qū)，還未進(jìn)行自動(dòng)合并
SELECT * FROM t1;
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
│   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
│   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
-- 手動(dòng)合并分區(qū)
OPTIMIZE TABLE t1 FINAL;
-- 再次查詢，會(huì)看到分區(qū)已經(jīng)合并
SELECT * FROM t1;
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
│   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
│   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
│   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
│   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘

數(shù)據(jù)生命周期

TTL：Time To Live

列TTL

當(dāng)列中的值過期時(shí)，ClickHouse將用列數(shù)據(jù)類型的默認(rèn)值替換它們

TTL子句不能用于鍵列

表TTL

當(dāng)數(shù)據(jù)部分中的所有列值都過期，可以刪除數(shù)據(jù)

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    d DateTime,
    -- 列生命周期（5秒）
    a Int TTL d + INTERVAL 5 SECOND
)ENGINE = MergeTree()
ORDER BY d
-- 表生命周期（1分鐘）
TTL d + INTERVAL 1 MINUTE DELETE;
-- 插數(shù)據(jù)
INSERT INTO t1 VALUES (now(),2);
-- 立即查
SELECT * FROM t1;
┌───────────────────d─┬─a─┐
│ 2022-11-01 14:39:17 │ 2 │
└─────────────────────┴───┘
-- 5秒后刷新并查詢
OPTIMIZE TABLE t1 FINAL;
SELECT * FROM t1;
┌───────────────────d─┬─a─┐
│ 2022-11-01 14:39:17 │ 0 │
└─────────────────────┴───┘
-- 1分鐘后查
OPTIMIZE TABLE t1 FINAL;
SELECT * FROM t1;
-- 過期數(shù)據(jù)行被刪除

立即查，TTL列值為2，5秒后查值為0，1分鐘后查此數(shù)據(jù)被刪除

其它設(shè)置

常見設(shè)置	說明	默認(rèn)值	備注
index_granularity	索引粒度。索引中相鄰的『標(biāo)記』間的數(shù)據(jù)行數(shù)	8192	通常不用改
index_granularity_bytes	索引粒度，以字節(jié)為單位	10Mb	數(shù)據(jù)量很大且數(shù)據(jù)一致性很高時(shí) 可考慮調(diào)大索引粒度
min_index_granularity_bytes	允許的最小數(shù)據(jù)粒度	1024b	用于防止添加索引粒度很低的表

ReplacingMergeTree

ReplacingMergeTree具有去重功能：分區(qū)內(nèi)按排序鍵去重

數(shù)據(jù)的去重只會(huì)在數(shù)據(jù)合并期間進(jìn)行

合并會(huì)在后臺(tái)一個(gè)不確定的時(shí)間進(jìn)行

可用OPTIMIZE語句發(fā)起計(jì)劃外的合并，但會(huì)引發(fā)數(shù)據(jù)的大量讀寫

ReplacingMergeTree適用于在后臺(tái)清除重復(fù)的數(shù)據(jù)，但是不保證沒有重復(fù)數(shù)據(jù)出現(xiàn)

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, ...]

ver是版本列，是可選參數(shù)，類型可為UInt、Date、DateTime在數(shù)據(jù)合并時(shí)，ReplacingMergeTree從相同排序鍵的行中選擇一行留下：

如果ver列未指定，就保留最后一條

如果ver列已指定，就保留ver值最大的版本

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  uid UInt32,
  sku_id String,
  create_time Datetime
) ENGINE = ReplacingMergeTree(create_time)
PARTITION BY sku_id
ORDER BY (uid);
INSERT INTO t1 VALUES
(1,'s1','2022-06-01 00:00:00'),
(1,'s1','2022-06-02 11:11:11'),
(1,'s2','2022-06-02 13:00:00'),
(2,'s2','2022-06-02 12:12:12'),
(2,'s2','2022-06-02 00:00:00');
SELECT * FROM t1;
-- 插了5條數(shù)據(jù)，去重了，查出來只有3條，不同分區(qū)沒有去重
┌─uid─┬─sku_id─┬─────────create_time─┐
│   1 │ s1     │ 2022-06-02 11:11:11 │
└─────┴────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─────────create_time─┐
│   1 │ s2     │ 2022-06-02 13:00:00 │
│   2 │ s2     │ 2022-06-02 12:12:12 │
└─────┴────────┴─────────────────────┘

SummingMergeTree

適用場景：不需要查詢明細(xì)，只查詢按維度聚合求和的場景

原理：預(yù)聚合

優(yōu)點(diǎn)：加快聚合求和查詢、節(jié)省空間

語法：SummingMergeTree([columns])

columns是可選參數(shù)，必須是數(shù)值類型，并且不可位于主鍵中

所選列將會(huì)被預(yù)聚合求和；若缺省，則所有非維度數(shù)字列將會(huì)被聚合求和

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  uid UInt32,
  amount1 Decimal(9,2),
  amount2 Decimal(9,2)
) ENGINE = SummingMergeTree(amount1)
ORDER BY (uid);
INSERT INTO t1 VALUES (1,1.00,2.00),(1,9.00,8.00);
SELECT * FROM t1;
┌─uid─┬─amount1─┬─amount2─┐
│   1 │   10.00 │    2.00 │
└─────┴─────────┴─────────┘
INSERT INTO t1 VALUES (1,1.11,2.22),(2,5.00,5.00);
SELECT * FROM t1;
┌─uid─┬─amount1─┬─amount2─┐
│   1 │   10.00 │    2.00 │
└─────┴─────────┴─────────┘
┌─uid─┬─amount1─┬─amount2─┐
│   1 │    1.11 │    2.22 │
│   2 │    5.00 │    5.00 │
└─────┴─────────┴─────────┘
OPTIMIZE TABLE t1;
SELECT * FROM t1;
┌─uid─┬─amount1─┬─amount2─┐
│   1 │   11.11 │    2.00 │
│   2 │    5.00 │    5.00 │
└─────┴─────────┴─────────┘

圖示amount1會(huì)按照uid聚合求和，而amount2是第一條插入uid時(shí)的值

注意

不能直接SELECT amount1 FROM t1 WHERE 維度來得到匯總值，因?yàn)橛行┡R時(shí)明細(xì)數(shù)據(jù)還沒來得及聚合

所以仍要SELECT SUM(amount1)