MySQL ClickHouse常用表引擎超詳細講解

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

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

表引擎

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

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

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

索引的使用（如果存在）

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

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

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

合并樹家族

合并樹家族特點：

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

支持數(shù)據(jù)復制

支持分區(qū)

支持稀疏索引

稀疏索引原理

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

MergeTree

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

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（必選項）

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

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

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

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

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

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

主鍵

PRIMARY KEY（可選項）

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

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

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

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

sparse index

分區(qū)

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

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

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

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

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

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

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

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

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

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

-- 建表
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');
-- 插完后立即插，會發(fā)現(xiàn)數(shù)據(jù)寫入臨時分區(qū)，還未進行自動合并
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 │
└─────┴────────┴──────────────┴─────────────────────┘
-- 手動合并分區(qū)
OPTIMIZE TABLE t1 FINAL;
-- 再次查詢，會看到分區(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

當列中的值過期時，ClickHouse將用列數(shù)據(jù)類型的默認值替換它們

TTL子句不能用于鍵列

表TTL

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

ReplacingMergeTree

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

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

合并會在后臺一個不確定的時間進行

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

ReplacingMergeTree適用于在后臺清除重復的數(shù)據(jù)，但是不保證沒有重復數(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ù)合并時，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

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

原理：預聚合

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

語法：SummingMergeTree([columns])

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

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

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會按照uid聚合求和，而amount2是第一條插入uid時的值

注意

不能直接SELECT amount1 FROM t1 WHERE 維度來得到匯總值，因為有些臨時明細數(shù)據(jù)還沒來得及聚合

所以仍要SELECT SUM(amount1)