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

2022-11-03 14:02:38

表引擎

表引擎作用：資料的儲存方式和位置

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

並行資料存取

索引的使用（如果存在）

是否可以執行多執行緒請求

資料複製引數

常見表引擎	家族	說明	索引	備註
TinyLog	Log Family	以列檔案的形式儲存在硬碟資料寫入時，追加到檔案末尾	不支援	可用於儲存小批次處理的中間資料
Memory	其它	資料以未壓縮的原始形式直接儲存在記憶體	不支援	適用於少量資料的高效能查詢
MergeTree	MergeTree Family	支援列式儲存、分割區、稀疏索引、二級索引…	支援	單節點ClickHouse範例的預設表引擎

合併樹家族

合併樹家族特點：

快速插入資料並進行後續的後臺資料處理

支援資料複製

支援分割區

支援稀疏索引

稀疏索引原理

稀疏索引佔用空間小，範圍批次查詢快，但單點查詢較慢

MergeTree

擅長插入極大量的資料到一張表
資料能以資料片段的形式一個接一個地快速寫入，資料片段在後臺按一定的規則進行合併

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

關鍵詞	簡述
ENGINE	引擎
ORDER BY	資料排序規則
PARTITION BY	分割區
PRIMARY KEY	索引規則
TTL	資料生命週期
SETTINGS	其它設定

排序鍵

ORDER BY（必選項）

規定了分割區內的資料按照哪些欄位進行按序儲存

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

此情況下，資料順序是根據插入順序

如果想要按INSERT ... SELECT的資料順序來儲存，就設定max_insert_threads=1

若想按資料儲存順序查出資料，可用單執行緒查詢

對於有序資料，資料一致性越高，壓縮效率越高

主鍵

PRIMARY KEY（可選項）

作用：為列資料提供稀疏索引（不是唯一約束），提升列查詢效率

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

要求：主鍵列必須是排序列的字首

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

sparse index

分割區

PARTITION BY分割區（可選項）

分割區作用：縮小掃描範圍，優化查詢速度

並行：分割區後，面對涉及跨分割區的查詢統計，會以分割區為單位並行處理

如果不填：只會使用一個分割區

資料寫入與分割區合併：

任何一個批次的資料寫入都會產生一個臨時分割區，不會納入任何一個已有的分割區。

寫入後，過一段時間（約10多分鐘），會自動執行合併操作，把臨時分割區的資料合併

可用OPTIMIZE TABLE 表名 [FINAL]主動執行合併

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

分割區過多=>(列式)查詢時掃描檔案過多=>效能低

-- 建表
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);
-- 插資料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');
-- 插完後立即插，會發現資料寫入臨時分割區，還未進行自動合併
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 │
└─────┴────────┴──────────────┴─────────────────────┘
-- 手動合併分割區
OPTIMIZE TABLE t1 FINAL;
-- 再次查詢，會看到分割區已經合併
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 │
└─────┴────────┴──────────────┴─────────────────────┘

資料生命週期

TTL：Time To Live

列TTL

當列中的值過期時，ClickHouse將用列資料型別的預設值替換它們

TTL子句不能用於鍵列

表TTL

當資料部分中的所有列值都過期，可以刪除資料

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;
-- 插資料
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;
-- 過期資料行被刪除

立即查，TTL列值為2，5秒後查值為0，1分鐘後查此資料被刪除

其它設定

常見設定	說明	預設值	備註
index_granularity	索引粒度。索引中相鄰的『標記』間的資料行數	8192	通常不用改
index_granularity_bytes	索引粒度，以位元組為單位	10Mb	資料量很大且資料一致性很高時可考慮調大索引粒度
min_index_granularity_bytes	允許的最小資料粒度	1024b	用於防止新增索引粒度很低的表

ReplacingMergeTree

ReplacingMergeTree具有去重功能：分割區內按排序鍵去重

資料的去重只會在資料合併期間進行

合併會在後臺一個不確定的時間進行

可用OPTIMIZE語句發起計劃外的合併，但會引發資料的大量讀寫

ReplacingMergeTree適用於在後臺清除重複的資料，但是不保證沒有重複資料出現

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是版本列，是可選引數，型別可為UInt、Date、DateTime在資料合併時，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條資料，去重了，查出來只有3條，不同分割區沒有去重
┌─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

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

原理：預聚合

優點：加快聚合求和查詢、節省空間

語法：SummingMergeTree([columns])

columns是可選引數，必須是數值型別，並且不可位於主鍵中

所選列將會被預聚合求和；若預設，則所有非維度數位列將會被聚合求和

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 維度來得到彙總值，因為有些臨時明細資料還沒來得及聚合

所以仍要SELECT SUM(amount1)