ClickHouse高級 1. 執行計劃 在 ClickHouse 20.6 版本之前要查看 SQL 語句的執行計劃需要設置日誌級別為 TRACE 才可以看到，並且只能真正執行 SQL，在執行日誌裡面查看。在 20.6 版本引入了原生的執行計劃語法，併在 20.6.3.28 版本成為正式功能。 1. ...
ClickHouse高級
目錄1. 執行計劃
在 ClickHouse 20.6 版本之前要查看 SQL 語句的執行計劃需要設置日誌級別為 TRACE 才可以看到，並且只能真正執行 SQL，在執行日誌裡面查看。在 20.6 版本引入了原生的執行計劃語法，併在 20.6.3.28 版本成為正式功能。
1.1 基本語法
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]
- PLAN：用於查看執行計劃，預設值
- header：列印計劃中各個步驟的 head 說明，預設關閉，預設值 0
- description：列印計劃中各個步驟的描述，預設開啟，預設值 1
- actions：列印計劃中各個步驟的詳細信息，預設關閉，預設值 0
- AST：用於查看語法樹
- SYNTAX：用於優化語法
- PIPELINE：用於查看 PIPELINE 計劃
- header：列印計劃中各個步驟的 head 說明，預設關閉
- graph：用 DOT 圖像語言描述管道圖，預設關閉，查看相關的圖形需要 graphviz 查看
- actions：如果開啟了 graph，緊湊列印，預設開啟
註：PLAN 和 PIPELINE 可以進行額外的顯示設置，如上參數所示。
1.2 案例實操
1.2.1 新版本
-
PLAN
-- 簡單查詢 :) EXPLAIN PLAN SELECT arrayJoin([1,2,3,NULL,NULL]); EXPLAIN SELECT arrayJoin([1, 2, 3, NULL, NULL]) Query id: dbecbf4d-ccd9-4064-900f-8fc95e04f36b ┌─explain───────────────────────────────────────────────────────────────────┐ │ Expression ((Projection + Before ORDER BY)) │ │ SettingQuotaAndLimits (Set limits and quota after reading from storage) │ │ ReadFromStorage (SystemOne) │ └───────────────────────────────────────────────────────────────────────────┘ 3 rows in set. Elapsed: 0.001 sec. -- 複雜 SQL 的執行計劃 :) EXPLAIN :-] SELECT database, table, count(1) cnt :-] FROM system.parts :-] WHERE database IN ('datasets', 'system') :-] GROUP BY database, table :-] ORDER BY database, cnt DESC :-] LIMIT 2 BY database; EXPLAIN SELECT database, table, count(1) AS cnt FROM system.parts WHERE database IN ('datasets', 'system') GROUP BY database, table ORDER BY database ASC, cnt DESC LIMIT 2 BY database Query id: 500e3a0e-d29e-4316-a863-01de50d811f6 ┌─explain─────────────────────────────────────────────────────────────────────────────────────┐ │ Expression (Projection) │ │ LimitBy │ │ Expression (Before LIMIT BY) │ │ MergingSorted (Merge sorted streams for ORDER BY) │ │ MergeSorting (Merge sorted blocks for ORDER BY) │ │ PartialSorting (Sort each block for ORDER BY) │ │ Expression (Before ORDER BY) │ │ Aggregating │ │ Expression (Before GROUP BY) │ │ Filter (WHERE) │ │ SettingQuotaAndLimits (Set limits and quota after reading from storage) │ │ ReadFromStorage (SystemParts) │ └─────────────────────────────────────────────────────────────────────────────────────────────┘ 12 rows in set. Elapsed: 0.004 sec. -- 打開全部的參數的執行計劃 :) EXPLAIN header = 1, actions = 1, description = 1 :-] SELECT number :-] FROM system.numbers :-] LIMIT 10; EXPLAIN header = 1, actions = 1, description = 1 SELECT number FROM system.numbers LIMIT 10 Query id: 7495adae-1de1-4dd6-a206-3915b2174a9a ┌─explain───────────────────────────────────────────────────────────────────┐ │ Expression ((Projection + Before ORDER BY)) │ │ Header: number UInt64 │ │ Actions: INPUT :: 0 -> number UInt64 : 0 │ │ Positions: 0 │ │ SettingQuotaAndLimits (Set limits and quota after reading from storage) │ │ Header: number UInt64 │ │ Limit (preliminary LIMIT (without OFFSET)) │ │ Header: number UInt64 │ │ Limit 10 │ │ Offset 0 │ │ ReadFromStorage (SystemNumbers) │ │ Header: number UInt64 │ └───────────────────────────────────────────────────────────────────────────┘ 12 rows in set. Elapsed: 0.002 sec. -
AST 語法樹
:) EXPLAIN AST :-] SELECT number :-] FROM system.numbers :-] LIMIT 10; EXPLAIN AST SELECT number FROM system.numbers LIMIT 10 Query id: 5fa352a1-4e72-4e3d-953f-fd249eddf144 ┌─explain─────────────────────────────────────┐ │ SelectWithUnionQuery (children 1) │ │ ExpressionList (children 1) │ │ SelectQuery (children 3) │ │ ExpressionList (children 1) │ │ Identifier number │ │ TablesInSelectQuery (children 1) │ │ TablesInSelectQueryElement (children 1) │ │ TableExpression (children 1) │ │ TableIdentifier system.numbers │ │ Literal UInt64_10 │ └─────────────────────────────────────────────┘ 10 rows in set. Elapsed: 0.002 sec. -
SYNTAX 語法優化
-- 執行查詢 :) SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') :-] FROM numbers(10); SELECT if(number = 1, 'hello', if(number = 2, 'world', 'atguigu')) FROM numbers(10) Query id: 16e12575-e91c-46ff-b82e-3d84df5e9fd9 ┌─multiIf(equals(number, 1), 'hello', equals(number, 2), 'world', 'atguigu')─┐ │ atguigu │ │ hello │ │ world │ │ atguigu │ │ atguigu │ │ atguigu │ │ atguigu │ │ atguigu │ │ atguigu │ │ atguigu │ └────────────────────────────────────────────────────────────────────────────┘ 10 rows in set. Elapsed: 0.003 sec. -- 查看語法優化 :) EXPLAIN SYNTAX :-] SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') :-] FROM numbers(10); EXPLAIN SYNTAX SELECT if(number = 1, 'hello', if(number = 2, 'world', 'atguigu')) FROM numbers(10) Query id: 5f2be03d-d378-4490-b803-648e0a2a64ef ┌─explain────────────────────────────────────────────────────────────┐ │ SELECT if(number = 1, 'hello', if(number = 2, 'world', 'atguigu')) │ │ FROM numbers(10) │ └────────────────────────────────────────────────────────────────────┘ 2 rows in set. Elapsed: 0.001 sec. -- 開啟三元運算符優化 SET optimize_if_chain_to_multiif = 1; -- 再次查看語法優化 :) EXPLAIN SYNTAX :-] SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') :-] FROM numbers(10); EXPLAIN SYNTAX SELECT if(number = 1, 'hello', if(number = 2, 'world', 'atguigu')) FROM numbers(10) Query id: a41d06bf-31a2-475a-bfcc-e96496fada63 ┌─explain─────────────────────────────────────────────────────────────┐ │ SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'atguigu') │ │ FROM numbers(10) │ └─────────────────────────────────────────────────────────────────────┘ 2 rows in set. Elapsed: 0.002 sec. -
PIPELINE
:) EXPLAIN PIPELINE :-] SELECT sum(number) :-] FROM numbers_mt(100000) :-] GROUP BY number % 20; EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20 Query id: b7eb588e-4987-4489-86ab-ed0442061d66 ┌─explain─────────────────────────┐ │ (Expression) │ │ ExpressionTransform │ │ (Aggregating) │ │ Resize 4 → 1 │ │ AggregatingTransform × 4 │ │ (Expression) │ │ ExpressionTransform × 4 │ │ (SettingQuotaAndLimits) │ │ (ReadFromStorage) │ │ NumbersMt × 4 0 → 1 │ └─────────────────────────────────┘ 10 rows in set. Elapsed: 0.003 sec. -- 更多參數 EXPLAIN PIPELINE header = 1,graph = 1 SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;
1.2.2 老版本
$ clickhouse-client -h 主機名 --send_logs_level=trace <<< "sql" > /dev/null
其中，send_logs_level參數指定日誌等級為 trace，<<<將 SQL 語句重定向至 clickhouse-client 進行查詢，> /dev/null將查詢結果重定向到空設備吞掉，以便觀察日誌。
註意：通過將 ClickHouse 的服務日誌，設置到 DEBUG 或者 TRACE 級別，才可以變相實現 EXPLAIN 查詢的作用。
2. 建表優化
2.1 數據類型
2.1.1 時間欄位類型
建表時能用數值型或日期時間型表示的欄位就不要用字元串，全 String 類型在以 Hive 為中心的數倉建設中常見，但 ClickHouse 環境不應受此影響。
雖然 ClickHouse 底層將 DateTime 存儲為時間戳 Long 類型，但不建議存儲 Long 類型，因為 DateTime 不需要經過函數轉換處理，執行效率高、可讀性好。
-- create_time 不為 DateTime 類型時，需要經過 toDate 函數轉換
CREATE TABLE t_type2
(
id UInt32,
sku_id String,
total_amount Decimal(16, 2),
create_time Int32
) ENGINE = ReplacingMergeTree(create_time)
PARTITION BY toYYYYMMDD(toDate(create_time))
PRIMARY KEY (id)
ORDER BY (id, sku_id);
2.1.2 空值存儲類型
官方已經指出 Nullable 類型幾乎總還是會拖累性能，因為存儲 Nullable 列時需要創建一個額外的文件來存儲 NULL 標記，並且 Nullable 列無法被索引。因此除非極特殊情況，應直接使用欄位預設值表示空，或者自行指定一個在業務中無意義的值（例如用 -1 表示沒有商品 ID）。
-- 創建帶空值的表
CREATE TABLE t_null
(
x Int8,
y Nullable(Int8)
) ENGINE = TinyLog;
-- 插入數據
INSERT INTO t_null
VALUES (1, NULL),
(2, 3);
-- 查詢
SELECT x + y
FROM t_null;
官方說明：Nullable | ClickHouse Documentation
2.2 分區和索引
分區粒度根據業務特點決定，不宜過粗或過細。一般選擇按天分區，也可以指定為 Tuple()，以單表一億數據為例，分區大小控制在 10-30 個最佳。
必須指定索引列，ClickHouse 中的索引列即排序列，通過 order by 指定，一般在查詢條件中經常被用來充當篩選條件的屬性被納入進來；可以是單一維度，也可以是組合維度的索引；通常需要滿足高級列在前、查詢頻率大的在前原則；還有基數特別大的不適合做索引列，如用戶表的 userid 欄位；通常篩選後的數據滿足在百萬以內為最佳。
2.3 表參數
index_granularity 是用來控制索引粒度的，預設是 8192，如非必須不建議調整。
如果表中不是必須保留全量歷史數據，建議指定 TTL（生存時間值），可以免去手動清理過期歷史數據的麻煩，TTL 也可以通過alter table語句隨時修改。
2.4 寫入和刪除優化
儘量不要執行單條或小批量刪除和插入操作，這樣會產生小分區文件，給後臺 Merge 任務帶來巨大壓力。
不要一次寫入太多分區，或數據寫入太快，數據寫入太快會導致 Merge 速度跟不上而報錯，一般建議每秒鐘發起 2-3 次寫入操作，每次操作寫入 2w~5w 條數據（依伺服器性能而定）。
常見錯誤：
Too many parts：使用 WAL 預寫日誌（in_memory_parts_enable_wal預設為 true），提高寫入性能；Memory limit：在伺服器記憶體充裕的情況下增加記憶體配額，一般通過max_memory_usage參數來實現；在伺服器記憶體不充裕的情況下，建議將超出部分內容分配到系統硬碟上，但會降低執行速度，一般通過max_bytes_before_external_group_by、max_bytes_before_external_sort參數來實現。
2.5 常見配置
配置項主要在 config.xml 或 users.xml 中，基本上都在 users.xml 里。
config.xml 配置項：Server Settings | ClickHouse Documentation
users.xml 配置項：Settings | ClickHouse Documentation
2.5.1 CPU資源
| 配置 | 描述 |
|---|---|
| background_pool_size | 後臺線程池的大小，merge 線程就是在該線程池中執行，該線程池不僅僅是給 merge 線程用的，預設值 16，建議改成 CPU 個數的 2 倍（線程數） |
| background_schedule_pool_size | 執行後臺任務的線程數，預設 128，建議改成CPU 個數的 2 倍（線程數） |
| background_distributed_schedule_pool_size | 分散式發送執行後臺任務的線程數，預設 16，建議改成CPU 個數的 2 倍（線程數） |
| max_concurrent_queries | 最大併發處理的請求數（包含 select、insert 等），預設值 100，建議150~300（不夠再加） |
| max_threads | 單個查詢所能使用的最大 CPU 個數，預設是 CPU 核數 |
2.5.2 記憶體資源
| 配置 | 描述 |
|---|---|
| max_memory_usage | 此參數在 users.xml 中，表示單詞 Query 占用記憶體最大值，該值可以設置的比較大，這樣可以提升集群查詢的上限；保留部分給 OS，例如：128GB 記憶體的機器設置為 100GB |
| max_bytes_before_external_group_by | 一般按照 max_memory_usage 的一半設置記憶體，當 group 使用記憶體超過閾值後會刷新到磁碟進行 |
| max_bytes_before_external_sort | 當 order by 已超過 max_bytes_before_external_sort 記憶體就進行溢寫磁碟（基於磁碟排序），如果不設置該值，那麼當記憶體不夠是直接拋錯，設置了該值 order by 可以正常完成，但是速度相對存記憶體來說較慢（實測非常慢） |
| max_table_size_to_drop | 此參數在 config.xml 中，應用於需要刪除表或分區的情況，預設 50GB，意思是如果刪除 50GB 以上的分區表會失敗，建議改為 0（任何分區表都可刪除） |
2.5.3 存儲
ClickHouse 不支持設置多數據目錄，為了提升數據 IO 性能，可以掛載虛擬捲組，一個捲組綁定多塊物理磁碟提升讀寫性能，多數據查詢場景 SSD 迴避普通機械硬碟快 2-3 倍。
3. 語法優化
# 1. 準備測試數據，下載官方數據集
$ curl -O https://datasets.clickhouse.com/hits/partitions/hits_v1.tar
$ curl -O https://datasets.clickhouse.com/visits/partitions/visits_v1.tar
# 2. 解壓到 ClickHouse 數據目錄
$ tar xvf hits_v1.tar -C /var/lib/clickhouse
$ tar xvf visits_v1.tar -C /var/lib/clickhouse
# 3. 修改數據目錄所屬用戶
$ sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/data/datasets
$ sudo chown -R clickhouse:clickhouse /var/lib/clickhouse/metadata/datasets
# 4. 重啟 ClickHouse
$ sudo systemctl restart clickhouse-server
# 5. 執行查詢，hits_v1 表 130+ 欄位 880w+ 數據，visits_v1 表 180+ 欄位 160w+ 數據
$ clickhouse-client --query "SELECT count(*) FROM datasets.hits_v1"
$ clickhouse-client --query "SELECT count(*) FROM datasets.visits_v1"
ClickHouse 的 SQL 優化規則是基於 RBO(Rule Based Optimization)，下麵為部分優化規則。
3.1 COUNT優化
在調用 count 函數時，如果沒有指定具體欄位且沒有 where 條件，則會直接使用 system.tables 的 total_rows，例如：
-- Optimized trivial count 是對 count 的優化
:) explain plan select count(*) from hits_v1;
EXPLAIN
SELECT count(*)
FROM hits_v1
Query id: 2c1bacf8-187c-430a-95b5-3a3ebdb747af
┌─explain──────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│ MergingAggregated │
│ ReadFromPreparedSource (Optimized trivial count) │
└──────────────────────────────────────────────────────┘
3 rows in set. Elapsed: 0.004 sec.
-- 如果 count 具體的列欄位，則不會優化
:) explain plan select count(UserID) from hits_v1;
EXPLAIN
SELECT count(UserID)
FROM hits_v1
Query id: f6f060aa-09de-4fa2-a0e9-2909f0800e42
┌─explain───────────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│ ReadFromMergeTree │
└───────────────────────────────────────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.007 sec.
3.2 消除子查詢重覆欄位
下麵語句子查詢中有兩個重覆的 id 欄位，會被去重：
:) EXPLAIN SYNTAX
:-] SELECT a.UserID, b.VisitID, a.URL, b.UserID
:-] FROM datasets.hits_v1 AS a
:-] LEFT JOIN (SELECT UserID, UserID, VisitID FROM datasets.visits_v1) AS b USING (UserID)
:-] LIMIT 3;
EXPLAIN SYNTAX
SELECT
a.UserID,
b.VisitID,
a.URL,
b.UserID
FROM datasets.hits_v1 AS a
LEFT JOIN
(
SELECT
UserID,
UserID,
VisitID
FROM datasets.visits_v1
) AS b USING (UserID)
LIMIT 3
Query id: 119f7b30-39b0-4cc8-a6f4-81f302e35867
┌─explain─────────────────────┐
│ SELECT │
│ UserID, │
│ VisitID, │
│ URL, │
│ b.UserID │
│ FROM datasets.hits_v1 AS a │
│ ALL LEFT JOIN │
│ ( │
│ SELECT │
│ UserID, │
│ VisitID │
│ FROM datasets.visits_v1 │
│ ) AS b USING (UserID) │
│ LIMIT 3 │
└─────────────────────────────┘
14 rows in set. Elapsed: 0.008 sec.
3.3 謂詞下推
當 group by 有 having 子句，但是沒有 with cube、with rollup 或者 with totals 修飾的時候，having 過濾會下推到 where 提前過濾。例如：
-- having 例子
:) EXPLAIN SYNTAX
:-] SELECT UserID
:-] FROM datasets.hits_v1
:-] GROUP BY UserID
:-] HAVING UserID = '8585742290196126178';
EXPLAIN SYNTAX
SELECT UserID
FROM datasets.hits_v1
GROUP BY UserID
HAVING UserID = '8585742290196126178'
Query id: 761000a4-0043-4f7b-8329-89b2bfbbe055
┌─explain──────────────────────────────┐
│ SELECT UserID │
│ FROM datasets.hits_v1 │
│ WHERE UserID = '8585742290196126178' │
│ GROUP BY UserID │
└──────────────────────────────────────┘
4 rows in set. Elapsed: 0.003 sec.
-- 子查詢也支持謂詞下推
:) EXPLAIN SYNTAX
:-] SELECT *
:-] FROM (SELECT UserID FROM datasets.visits_v1)
:-] WHERE UserID = '8585742290196126178';
EXPLAIN SYNTAX
SELECT *
FROM
(
SELECT UserID
FROM datasets.visits_v1
)
WHERE UserID = '8585742290196126178'
Query id: a70da9af-c8c1-4787-a536-e1794fcef33d
┌─explain──────────────────────────────────┐
│ SELECT UserID │
│ FROM │
│ ( │
│ SELECT UserID │
│ FROM datasets.visits_v1 │
│ WHERE UserID = '8585742290196126178' │
│ ) │
│ WHERE UserID = '8585742290196126178' │
└──────────────────────────────────────────┘
8 rows in set. Elapsed: 0.006 sec.
-- 複雜例子
:) EXPLAIN SYNTAX
:-] SELECT *
:-] FROM (SELECT *
:-] FROM (SELECT UserID FROM datasets.visits_v1)
:-] UNION ALL
:-] SELECT *
:-] FROM (SELECT UserID FROM datasets.visits_v1))
:-] WHERE UserID = '8585742290196126178';
EXPLAIN SYNTAX
SELECT *
FROM
(
SELECT *
FROM
(
SELECT UserID
FROM datasets.visits_v1
)
UNION ALL
SELECT *
FROM
(
SELECT UserID
FROM datasets.visits_v1
)
)
WHERE UserID = '8585742290196126178'
Query id: 7b7ddf53-51b5-40d9-b079-ac65989479f2
┌─explain──────────────────────────────────────┐
│ SELECT UserID │
│ FROM │
│ ( │
│ SELECT UserID │
│ FROM │
│ ( │
│ SELECT UserID │
│ FROM datasets.visits_v1 │
│ WHERE UserID = '8585742290196126178' │
│ ) │
│ WHERE UserID = '8585742290196126178' │
│ UNION ALL │
│ SELECT UserID │
│ FROM │
│ ( │
│ SELECT UserID │
│ FROM datasets.visits_v1 │
│ WHERE UserID = '8585742290196126178' │
│ ) │
│ WHERE UserID = '8585742290196126178' │
│ ) │
│ WHERE UserID = '8585742290196126178' │
└──────────────────────────────────────────────┘
22 rows in set. Elapsed: 0.011 sec.
3.4 聚合計算外推
聚合函數內的計算會外推，例如：
:) EXPLAIN SYNTAX
:-] SELECT sum(UserID * 2)
:-] FROM datasets.visits_v1;
EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM datasets.visits_v1
Query id: 205e2c27-8f38-41c3-8fe0-566ca9e0168d
┌─explain─────────────────┐
│ SELECT sum(UserID) * 2 │
│ FROM datasets.visits_v1 │
└─────────────────────────┘
2 rows in set. Elapsed: 0.004 sec.
3.5 聚合函數消除
如果對聚合鍵，即 group by key 使用 min、max、any 聚合函數，則將函數去除，例如：
:) EXPLAIN SYNTAX
:-] SELECT sum(UserID * 2), max(VisitID), max(UserID)
:-] FROM datasets.visits_v1
:-] GROUP BY UserID;
EXPLAIN SYNTAX
SELECT
sum(UserID * 2),
max(VisitID),
max(UserID)
FROM datasets.visits_v1
GROUP BY UserID
Query id: f8931130-1bde-4086-a947-4921b76a6ec4
┌─explain─────────────────┐
│ SELECT │
│ sum(UserID) * 2, │
│ max(VisitID), │
│ UserID │
│ FROM datasets.visits_v1 │
│ GROUP BY UserID │
└─────────────────────────┘
6 rows in set. Elapsed: 0.003 sec.
3.6 刪除重覆的 order by key
重覆的排序鍵欄位會被去重，例如：
:) EXPLAIN SYNTAX
:-] SELECT UserID, VisitID
:-] FROM datasets.visits_v1
:-] ORDER BY UserID ASC, UserID ASC, VisitID ASC, VisitID ASC;
EXPLAIN SYNTAX
SELECT
UserID,
VisitID
FROM datasets.visits_v1
ORDER BY
UserID ASC,
UserID ASC,
VisitID ASC,
VisitID ASC
Query id: 82b72116-610f-410e-a64a-3607ff784d0b
┌─explain─────────────────┐
│ SELECT │
│ UserID, │
│ VisitID │
│ FROM datasets.visits_v1 │
│ ORDER BY │
│ UserID ASC, │
│ VisitID ASC │
└─────────────────────────┘
7 rows in set. Elapsed: 0.003 sec.
註：重覆的 group by key 不會被刪除。
3.7 刪除重覆的 limit by key
重覆聲明的 limit by key 會被去重，例如：
:) EXPLAIN SYNTAX
:-] SELECT UserID, VisitID
:-] FROM datasets.visits_v1
:-] LIMIT 3 BY VisitID, VisitID
:-] LIMIT 10;
EXPLAIN SYNTAX
SELECT
UserID,
VisitID
FROM datasets.visits_v1
LIMIT 3 BY
VisitID,
VisitID
LIMIT 10
Query id: 23883608-f407-4d24-bff1-b1009567d262
┌─explain─────────────────┐
│ SELECT │
│ UserID, │
│ VisitID │
│ FROM datasets.visits_v1 │
│ LIMIT 3 BY VisitID │
│ LIMIT 10 │
└─────────────────────────┘
6 rows in set. Elapsed: 0.003 sec.
3.8 刪除重覆的 using key
重覆的關聯鍵欄位會被去重，例如：
:) EXPLAIN SYNTAX
:-] SELECT a.UserID, a.UserID, b.VisitID, a.URL, b.UserID
:-] FROM datasets.hits_v1 AS a
:-] LEFT JOIN datasets.visits_v1 AS b USING (UserID, UserID);
EXPLAIN SYNTAX
SELECT
a.UserID,
a.UserID,
b.VisitID,
a.URL,
b.UserID
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v1 AS b USING (UserID, UserID)
Query id: 1f1519f0-fa32-4520-ad6b-c762b310879e
┌─explain──────────────────────────────────────────────┐
│ SELECT │
│ UserID, │
│ UserID, │
│ VisitID, │
│ URL, │
│ b.UserID │
│ FROM datasets.hits_v1 AS a │
│ ALL LEFT JOIN datasets.visits_v1 AS b USING (UserID) │
└──────────────────────────────────────────────────────┘
8 rows in set. Elapsed: 0.008 sec.
3.9 標量替換
如果子查詢只返回一行數據，在被引用的時候用標量替換，例如下麵語句中的 total_disk_usage：
:) EXPLAIN SYNTAX
:-] WITH (SELECT sum(bytes) FROM system.parts WHERE active) AS total_disk_usage
:-] SELECT (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage, table
:-] FROM system.parts
:-] GROUP BY table
:-] ORDER BY table_disk_usage DESC
:-] LIMIT 10;
EXPLAIN SYNTAX
WITH (
SELECT sum(bytes)
FROM system.parts
WHERE active
) AS total_disk_usage
SELECT
(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10
Query id: b1f006ec-8aaf-4c68-8843-41cbe7e9cc5e
┌─explain─────────────────────────────────────────────────────────────────────────┐
│ WITH identity(_CAST(0, 'Nullable(UInt64)')) AS total_disk_usage │
│ SELECT │
│ (sum(bytes_on_disk AS bytes) / total_disk_usage) * 100 AS table_disk_usage, │
│ table │
│ FROM system.parts │
│ GROUP BY table │
│ ORDER BY table_disk_usage DESC │
│ LIMIT 10 │
└─────────────────────────────────────────────────────────────────────────────────┘
8 rows in set. Elapsed: 0.004 sec.
3.10 三元運算優化
如果開啟了optimize_if_chain_to_multiif參數，三元運算符會被替換成 multiIf 函數，例如：
:) EXPLAIN SYNTAX
:-] SELECT if(number = 1, 'hello', if(number = 2, 'world', 'atguigu'))
:-] FROM numbers(10) SETTINGS optimize_if_chain_to_multiif = 1;
EXPLAIN SYNTAX
SELECT if(number = 1, 'hello', if(number = 2, 'world', 'atguigu'))
FROM numbers(10)
SETTINGS optimize_if_chain_to_multiif = 1
Query id: 068adf60-9b81-4af8-9683-a880769ac49d
┌─explain─────────────────────────────────────────────────────────────┐
│ SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'atguigu') │
│ FROM numbers(10) │
│ SETTINGS optimize_if_chain_to_multiif = 1 │
└─────────────────────────────────────────────────────────────────────┘
3 rows in set. Elapsed: 0.002 sec.
4. 查詢優化
4.1 單表查詢
4.1.1 PREWHERE 替代 WHERE
PREWHERE 和 WHERE 語句的作用相同，用來過濾數據，不同之處在於 PREWHERE 只支持 *MergeTree 族系列引擎的表，首先會讀取指定的列數據，來判斷數據過濾，等待數據過濾之後再讀取 SELECT 聲明的列欄位來補全其餘屬性。
當查詢列明顯多於篩選列時使用 PREWHERE 可十倍提升查詢性能，PREWHERE 會自動優化執行過濾階段的數據讀取方式，降低 IO 操作。
在某些場合下，PREWHERE 語句比 WHERE 語句處理的數據量更少且性能更高。
-- 使用 where，預設自動優化 prewhere，需要關閉自動轉 prewhere
SELECT WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate,
CounterID,
ClientIP,
ClientIP6,
RegionID,
UserID,
CounterClass,
OS,
UserAgent,
URL,
Referer,
URLDomain,
RefererDomain,
Refresh,
IsRobot,
RefererCategories,
URLCategories,
URLRegions,
RefererRegions,
ResolutionWidth,
ResolutionHeight,
ResolutionDepth,
FlashMajor,
FlashMinor,
FlashMinor2
FROM datasets.hits_v1
WHERE UserID = '3198390223272470366' SETTINGS optimize_move_to_prewhere = 0;
-- 使用 prewhere
SELECT WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate,
CounterID,
ClientIP,
ClientIP6,
RegionID,
UserID,
CounterClass,
OS,
UserAgent,
URL,
Referer,
URLDomain,
RefererDomain,
Refresh,
IsRobot,
RefererCategories,
URLCategories,
URLRegions,
RefererRegions,
ResolutionWidth,
ResolutionHeight,
ResolutionDepth,
FlashMajor,
FlashMinor,
FlashMinor2
FROM datasets.hits_v1
PREWHERE UserID = '3198390223272470366';
預設情況下，一般不會關閉 WHERE 自動優化 PREWHERE，但是某些場景即使開啟自動優化也不會自動自動轉換成 PREWHERE，需要手動指定 PREWHERE：
- 使用常量表達式；
- 使用預設值為 alias 類型的欄位；
- 包含了 arrayJOIN、globalIn、globalNotIn 或者 indexHInt 的查詢；
- select 查詢的列欄位和 where 的謂詞相同；
- 使用了主鍵欄位；
4.1.2 數據採樣
通過採樣運算可極大提升數據分析的性能。
-- SAMPLE 採樣，先採樣再統計
SELECT Title, count(*) AS PageViews
FROM datasets.hits_v1 SAMPLE 0.1
WHERE CounterID = 57
GROUP BY Title
ORDER BY PageViews DESC
LIMIT 1000;
註：採樣修飾符只有在 MergeTree engine 表中才有效，且在創建表時需要指定採樣策略。
4.1.3 列裁剪與分區裁剪
數據量太大時避免使用 select * 操作，查詢的性能會與查詢的欄位大小和數量成反比，查詢的欄位越少，消耗的 IO 資源越少，性能就會越高。
-- 反例
SELECT * FROM datasets.hits_v1;
-- 正例
SELECT WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate,
CounterID,
ClientIP,
ClientIP6,
RegionID,
UserID,
CounterClass,
OS,
UserAgent,
URL,
Referer,
URLDomain,
RefererDomain,
Refresh,
IsRobot,
RefererCategories,
URLCategories,
URLRegions,
RefererRegions,
ResolutionWidth,
ResolutionHeight,
ResolutionDepth,
FlashMajor,
FlashMinor,
FlashMinor2
FROM datasets.hits_v1;
分區裁剪就是只讀取需要的分區，在過濾條件中指定。
SELECT WatchID,
JavaEnable,
Title,
GoodEvent,
EventTime,
EventDate,
CounterID,
ClientIP,
ClientIP6,
RegionID,
UserID
FROM datasets.hits_v1
WHERE EventDate = '2014-03-23';
4.1.4 order by 結合 where、limit
千萬以上數據集進行 order by 查詢時需要搭配 where 條件和 limit 語句一起使用。
-- 正例
SELECT UserID, Age
FROM datasets.hits_v1
WHERE CounterID = 57
ORDER BY Age DESC
LIMIT 1000;
-- 反例
SELECT UserID, Age
FROM datasets.hits_v1
ORDER BY Age DESC;
4.1.5 避免構建虛擬列
如非必須，不要在結果集上構建虛擬列，虛擬列非常消耗資源浪費性能，可以考慮客戶端進行處理，或者在表中構造實際欄位進行額外存儲。
-- 反例
SELECT Income, Age, Income / Age AS IncRate
FROM datasets.hits_v1;
-- 正例
SELECT Income, Age
FROM datasets.hits_v1;
4.1.6 uniqCombined 替代 distinct
性能可提升 10 倍以上，uniqCombined 底層採用類似 HyperLogLog 演算法實現，為近似去重，可直接使用這種去重方式提升查詢性能。Count(distinct) 會使用 uniqExact 精確去重。
不建議在千萬級不同數據上執行 distinct 去重查詢，改為近似去重 uniqCombined。
-- 反例
SELECT count(DISTINCT rand())
FROM datasets.hits_v1;
-- 正例
SELECT uniqCombined(rand())
FROM datasets.hits_v1;
4.1.7 使用物化視圖
參考第 6 節。
4.1.8 其它註意事項
-
查詢熔斷
為了避免因個別慢查詢引起的服務雪崩問題，除了可以為單個查詢設置超時以外，還可以配置周期熔斷，在一個查詢周期內，如果用戶頻繁進行慢查詢操作超出規定閾值後將無法繼續進行查詢操作。
-
關閉虛擬記憶體
物理記憶體和虛擬記憶體的數據交換，會導致查詢變慢，資源允許的情況下關閉虛擬記憶體。
-
配置 join_use_nulls
為每一個賬戶添加 join_use_nulls 配置，左表中的一條記錄在右表中不存在，右表的相應欄位會返回該欄位相應數據類型的預設值，而不是標準 SQL 中的 NULL 值。
-
批量寫入先排序
批量寫入數據時，必須控制每個批次的數據中涉及到的分區的數量，在寫入之前最好對需要導入的數據進行排序。無序的數據或者涉及的分區太多，會導致 ClickHouse 無法及時對新導入的數據進行合併，從而影響查詢性能。
-
關註 CPU
CPU 一般在 50% 左右會出現查詢波動，達到 70% 會出現大範圍的查詢超時，CPU 是最關鍵的指標，要非常關註。
4.2 多表關聯
4.2.1 測試數據
-- 創建小表
CREATE TABLE IF NOT EXISTS datasets.visits_v2
ENGINE = CollapsingMergeTree(Sign) PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192 AS
SELECT *
FROM datasets.visits_v1
LIMIT 10000;
-- 創建空表用來保存 join 結果，避免控制台列印過慢
CREATE TABLE IF NOT EXISTS datasets.hits_v2
ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192 AS
SELECT *
FROM datasets.hits_v1
WHERE 1 = 0;
4.2.2 用 IN 代替 JOIN
當多表聯查時，查詢的數據僅從其中一張表出時，可考慮用 IN 操作而不是 JOIN。
-- 正例
INSERT INTO datasets.hits_v2
SELECT a.*
FROM datasets.hits_v1 a
WHERE a.CounterID IN (SELECT CounterID FROM datasets.visits_v1);
-- 反例
INSERT INTO datasets.hits_v2
SELECT a.*
FROM datasets.hits_v1 a
LEFT JOIN datasets.visits_v1 b ON a.CounterID = b.CounterID;
4.2.3 大小表 JOIN
多表 JOIN 時要滿足小表在右的原則，右表關聯時被載入到記憶體中與左表進行比較，ClickHouse 中無論是 LEFT JOIN、RIGHT JOIN 還是 INNER JOIN 永遠都是拿著右表中的每一條記錄到左表中查找該記錄是否存在，所以右表必須是小表。
-
小表在右
INSERT INTO datasets.hits_v2 SELECT a.* FROM datasets.hits_v1 a LEFT JOIN datasets.visits_v2 b ON a.CounterID = b.CounterID; -
大表在右
INSERT INTO datasets.hits_v2 SELECT a.* FROM datasets.visits_v2 b LEFT JOIN datasets.hits_v1 a ON a.CounterID = b.CounterID;
4.2.4 註意謂詞下推（版本差異）
ClickHouse 在 JOIN 查詢時不會主動發起謂詞下推的操作，需要每個子查詢提前完成過濾操作，需要註意的是，是否執行謂詞下推，對性能影響差別很大（新版本已經不存在此問題，但是需要註意謂詞位置的不同依然有性能的差異）。
-- having 左表
:) EXPLAIN SYNTAX
:-] SELECT a.UserID, b.VisitID
:-] FROM datasets.hits_v1 a
:-] LEFT JOIN datasets.visits_v2 b ON a.CounterID = b.CounterID
:-] HAVING a.EventDate = '2014-03-27';
EXPLAIN SYNTAX
SELECT
a.UserID,
b.VisitID
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID
HAVING a.EventDate = '2014-03-27'
Query id: decc4c92-43cd-40fa-b741-6430e0f8a566
┌─explain──────────────────────────────────────────────────────────┐
│ SELECT │
│ UserID, │
│ VisitID │
│ FROM datasets.hits_v1 AS a │
│ ALL LEFT JOIN datasets.visits_v2 AS b ON CounterID = b.CounterID │
│ PREWHERE EventDate = '2014-03-27' │
└──────────────────────────────────────────────────────────────────┘
6 rows in set. Elapsed: 0.015 sec.
-- having 右表
:) EXPLAIN SYNTAX
:-] SELECT a.UserID, b.VisitID
:-] FROM datasets.hits_v1 a
:-] LEFT JOIN datasets.visits_v2 b ON a.CounterID = b.CounterID
:-] HAVING b.StartDate = '2014-03-27';
EXPLAIN SYNTAX
SELECT
a.UserID,
b.VisitID
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID
HAVING b.StartDate = '2014-03-27'
Query id: 830fbbe2-b6ef-4036-bc49-6bc13af5b32d
┌─explain──────────────────────────────────────────────────────────┐
│ SELECT │
│ UserID, │
│ VisitID │
│ FROM datasets.hits_v1 AS a │
│ ALL LEFT JOIN datasets.visits_v2 AS b ON CounterID = b.CounterID │
│ WHERE StartDate = '2014-03-27' │
└──────────────────────────────────────────────────────────────────┘
6 rows in set. Elapsed: 0.005 sec.
-- 子查詢 JOIN WHERE 不會自動優化
-- 正例
:) EXPLAIN SYNTAX
:-] SELECT a.UserID, b.VisitID
:-] FROM (SELECT UserID, CounterID FROM datasets.hits_v1 WHERE EventDate = '2014-03-27') AS a
:-] LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID;
EXPLAIN SYNTAX
SELECT
a.UserID,
b.VisitID
FROM
(
SELECT
UserID,
CounterID
FROM datasets.hits_v1
WHERE EventDate = '2014-03-27'
) AS a
LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID
Query id: a8642ba3-a680-42aa-832b-8f8c30d4b808
┌─explain──────────────────────────────────────────────────────────┐
│ SELECT │
│ UserID, │
│ VisitID │
│ FROM │
│ ( │
│ SELECT │
│ UserID, │
│ CounterID │
│ FROM datasets.hits_v1 │
│ PREWHERE EventDate = '2014-03-27' │
│ ) AS a │
│ ALL LEFT JOIN datasets.visits_v2 AS b ON CounterID = b.CounterID │
└──────────────────────────────────────────────────────────────────┘
12 rows in set. Elapsed: 0.013 sec.
-- 反例
:) EXPLAIN SYNTAX
:-] SELECT a.UserID, b.VisitID
:-] FROM datasets.hits_v1 AS a
:-] LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID
:-] WHERE a.EventDate = '2014-03-27';
EXPLAIN SYNTAX
SELECT
a.UserID,
b.VisitID
FROM datasets.hits_v1 AS a
LEFT JOIN datasets.visits_v2 AS b ON a.CounterID = b.CounterID
WHERE a.EventDate = '2014-03-27'
Query id: 17a68a1e-19eb-443d-bbf4-66f9442e00d5
┌─explain──────────────────────────────────────────────────────────┐
│ SELECT │
│ UserID, │
│ VisitID │
│ FROM datasets.hits_v1 AS a │
│ ALL LEFT JOIN datasets.visits_v2 AS b ON CounterID = b.CounterID │
│ PREWHERE EventDate = '2014-03-27' │
└──────────────────────────────────────────────────────────────────┘
6 rows in set. Elapsed: 0.015 sec.
4.2.5 分散式表使用 GLOBAL
兩張分散式表上的 IN 和 JOIN 之前必須加上GLOBAL關鍵字，右表只會在接收查詢請求的那個節點查詢一次，並將其分發到其它節點上。如果不加 GLOBAL 關鍵字的話，每個節點都會單獨發起一次對右表的查詢，而右表又是分散式表，就導致右表一共會被查詢 N2 次（N 是該分散式表的分片數量），這就是查詢放大，會帶來很大的開銷。
4.2.6 使用字典表
將一些需要關聯分析的業務創建成字典表進行 JOIN 操作，前提是字典表不宜太大，因為字典表會常駐記憶體。
官方文檔：Dictionaries | ClickHouse Documentation
4.2.7 提前過濾
通過增加邏輯過濾可以減少數據掃描，達到提高執行速度及降低記憶體消耗的目的。
5. 數據一致性（重點）
通過查詢 ClickHouse 手冊發現，即使對數據一致性支持最好的 MergeTree，也只是保證最終一致性。
ReplacingMergeTree 該引擎和 MergeTree 的不同之處在於它會刪除排序鍵值相同的重覆項。
數據的去重只會在數據合併期間進行，合併會在後臺不定期執行，因此無法預先做出計劃，有一些數據可能仍未被處理。儘管可以用OPTIMIZE語句發起計劃外的合併，但不要依靠它，因為OPTIMIZE語句會引起對數據的大量讀寫。
因此，ReplacingMergeTree適用於在後臺清除重覆的數據以節省空間，但是它不保證沒有重覆的數據出現。
在使用ReplacingMergeTree、SummingMergeTree這類表引擎的時候，會出現短暫數據不一致的情況。
在某些對一致性非常敏感的場景，通常有以下幾種方案。
-- 準備測試數據，創建表
CREATE TABLE IF NOT EXISTS datasets.test_a
(
user_id UInt64 COMMENT '數據去重更新標識',
score String,
deleted UInt8 DEFAULT 0 COMMENT '自定義標記位',
create_time DateTime DEFAULT toDateTime(0) COMMENT '版本號欄位'
) ENGINE = ReplacingMergeTree(create_time)
ORDER BY user_id;
-- 寫入 1000w 測試數據
INSERT INTO datasets.test_a (user_id, score)
WITH (SELECT ['A', 'B', 'C', 'D', 'E', 'F', 'G']) AS dict
SELECT number AS user_id, dict[number % 7 + 1] AS score
FROM numbers(10000000);
-- 修改前 50w 數據，修改 score 欄位與 create_time 欄位
INSERT INTO datasets.test_a (user_id, score, create_time)
WITH (SELECT ['AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG']) AS dict
SELECT number AS user_id, dict[number % 7 + 1] AS score, now() AS create_time
FROM numbers(500000);
-- 查詢總數（1050w）
SELECT count()
FROM datasets.test_a;
5.1 手動 OPTIMIZE
在寫入數據後，立刻執行OPTIMIZE強制觸發新寫入分區的合併動作。
OPTIMIZE TABLE datasets.test_a FINAL;
-- 語法
OPTIMIZE TABLE [db.]name [ON CLUSTER cluster] [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE [BY expression]]
5.2 通過 GROUP BY 去重
-
執行去重的查詢
SELECT user_id, argMax(score, create_time) AS score, argMax(deleted, create_time) AS deleted, max(create_time) AS ctime FROM datasets.test_a GROUP BY user_id HAVING deleted = 0;函數說明：
- argMax(field1, field2)：按照 field2 的最大值取 field1 的值。
當更新數據時，會寫入一行新的數據，例如上面的語句中，通過查詢最大的 create_time 得到修改後的 score 欄位值。
-
創建視圖
CREATE VIEW view_test_a AS SELECT user_id, argMax(score, create_time) AS score, argMax(deleted, create_time) AS deleted, max(create_time) AS ctime FROM datasets.test_a GROUP BY user_id HAVING deleted = 0; -
插入重覆數據
-- 插入 user_id 重覆數據 INSERT INTO datasets.test_a (user_id, score, create_time) VALUES (0, 'AAAA', now()); -- 通過視圖查詢 SELECT * FROM view_test_a WHERE user_id = 0; -
刪除數據
-- 刪除數據 INSERT INTO datasets.test_a (user_id, score, deleted, create_time) VALUES (0, 'AAAA', 1, now()); -- 通過視圖查詢 SELECT * FROM datasets.view_test_a WHERE user_id = 0;這行數據並沒有被真正的刪除，而是被過濾掉了。在一些合適的場景下，可以結合表級別的 TTL 最終將物理數據刪除。
5.3 通過 FINAL 查詢
在查詢語句後增加 FINAL 修飾符，這樣在查詢的過程中將會執行 Merge 的特殊邏輯（例如數據去重，預聚合等）。
但是該方法在早期版本基本沒有人使用，因為在增加 FINAL 之後，我們的查詢將會變成一個單線程的執行過程，查詢速度非常慢。
在 20.5.2.7 版本中，FINAL 查詢支持多線程執行，並且可以通過max_final_threads 參數控制單個查詢的線程數。但是目前讀取部分的動作依然是串列的。
FINAL 查詢最終的性能和很多因素相關，列欄位的大小、分區的數量等等都會影響到最終的查詢時間，所以還要結合實際場景取捨。
參考鏈接：Parallel final by KochetovNicolai · Pull Request #10463 · ClickHouse/ClickHouse (github.com)
-- 普通語句執行計劃
:) EXPLAIN PIPELINE
:-] SELECT *
:-] FROM datasets.visits_v1
:-] WHERE StartDate = '2014-03-17'
:-] LIMIT 100;
EXPLAIN PIPELINE
SELECT *
FROM datasets.visits_v1
WHERE StartDate = '2014-03-17'
LIMIT 100
Query id: 730be1e8-f678-4e30-9de2-3dc3d5c7f228
Connecting to database datasets at localhost:9000 as user default.
Connected to ClickHouse server version 21.9.2 revision 54449.
┌─explain──────────
