ClickHouse 系列：如何提升查詢優化？system.query_log 與 EXPLAIN 用法

1730 字

9 分鐘

ClickHouse 系列：如何提升查詢優化？system.query_log 與 EXPLAIN 用法

2025-08-21

software development

ClickHouse

/

Database

/

鐵人賽

在資料量日益龐大的場景下，如何優化查詢效能 是每一位開發者必須具備的核心能力。本篇將帶你實戰演練 ClickHouse 中兩個查詢優化利器：

system.query_log → 查詢歷史執行效能
EXPLAIN → 預估查詢路徑與資源使用

什麼是 system.query_log？#

system.query_log 是 ClickHouse 內建的查詢歷史紀錄表，它會紀錄每一筆查詢的：

啟動時間、執行耗時
資源使用量 (讀取行數、記憶體用量)
查詢錯誤與異常
使用者、來源 IP、Client 資訊
查詢使用的 Storage、Functions、Events

WARNING
這邊只紀錄「查詢執行的資訊」，並不會紀錄查詢的結果資料。

查詢最近 100 筆 SELECT 查詢執行紀錄#

1
SELECT
2
    query_start_time,
3
    query_duration_ms,
4
    read_rows,
5
    result_rows,
6
    query
7
FROM system.query_log
8
WHERE event_time > now() - INTERVAL 10 MINUTE
9
AND type = 'QueryFinish'
10
AND query LIKE 'SELECT%'
11
ORDER BY query_start_time DESC
12
LIMIT 100;

常見欄位解讀#

欄位	說明
query_start_time	查詢開始時間
query_duration_ms	查詢耗時 (毫秒)
read_rows	查詢過程中讀取的 row 數量
result_rows	查詢結果輸出的 row 數量
memory_usage	查詢執行時的記憶體使用量 (Byte)

type 值	說明
`QueryStart` = 1	查詢開始執行時紀錄
`QueryFinish` = 2	查詢成功完成時紀錄
`ExceptionBeforeStart` = 3	查詢還沒執行就錯誤時紀錄
`ExceptionWhileProcessing`=4	查詢執行中發生錯誤時紀錄

如何找出「慢查詢」？#

1
SELECT
2
    query_start_time,
3
    query_duration_ms,
4
    read_rows,
5
    memory_usage,
6
    query
7
FROM system.query_log
8
WHERE event_time > now() - INTERVAL 1 HOUR
9
AND type = 'QueryFinish'
10
AND query_duration_ms > 500  -- 大於 500ms
11
ORDER BY query_duration_ms DESC;

什麼是 EXPLAIN ？#

ClickHouse 提供 EXPLAIN 語法，讓你在查詢前預測 查詢路徑、掃描資料量、JOIN 策略 等細節。

EXPLAIN SYNTAX：#

1
EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [settings]
2
SELECT ...

模式	說明
AST	顯示查詢的抽象語法樹 (Abstract Syntax Tree)。
SYNTAX	顯示經過語法優化後的查詢結構。
QUERY TREE	顯示查詢邏輯樹，反映優化器進行後的結構。
PLAN	查詢的執行計畫路徑（含掃描表、JOIN 策略等）。
PIPELINE	查詢的執行階段與並行度資訊 (執行緒、流水線處理器等)。
ESTIMATE	預估查詢將掃描的資料量（rows、marks、parts）。
TABLE OVERRIDE	驗證 table function 的 schema 覆寫是否正確。

EXPLAIN 範例#

SYNTAX - 經語法優化後的查詢#

1
EXPLAIN SYNTAX SELECT * FROM system.numbers WHERE number < 10;

1
SELECT *
2
FROM system.numbers
3
WHERE number < 10

QUERY TREE — 最終查詢邏輯結構#

1
EXPLAIN QUERY TREE SELECT id, value FROM test_table;

1
QUERY id: 0
2
  PROJECTION COLUMNS
3
    id UInt64
4
    value String
5
  JOIN TREE
6
    TABLE id: 3, table_name: default.test_table

這能讓你清楚知道查詢會如何去 Join Tables、哪些欄位會被投影出來。

PLAN - 執行計畫步驟#

1
EXPLAIN PLAN SELECT sum(number) FROM numbers(1000) GROUP BY number % 4;

1
Union
2
 Expression (Projection)
3
  Aggregating
4
   ReadFromStorage (SystemNumbers)

你可以看到從讀取資料到聚合的整個查詢執行路徑。

ESTIMATE — 查詢預估讀取量#

1
EXPLAIN ESTIMATE SELECT * FROM large_table WHERE date >= '2024-01-01';

1
┌─database─┬─table──────┬─parts─┬─rows───┬─marks─┐
2
│ default  │ large_table│     2 │ 500000 │    32 │
3
└──────────┴────────────┴───────┴────────┴───────┘

進階：優化一個慢查詢#

先用 system.query_log 找到最近慢查詢。

1
SELECT
2
    query_start_time,
3
    query_duration_ms,
4
    read_rows,
5
    read_bytes,
6
    memory_usage,
7
    query
8
FROM system.query_log
9
WHERE event_time > now() - INTERVAL 1 HOUR
10
AND type = 'QueryFinish'
11
AND query LIKE '%order_summary%'
12
ORDER BY query_duration_ms DESC
13
LIMIT 5;

1
query_duration_ms: 4500ms
2
read_rows: 100000000
3
query: SELECT region, SUM(amount) FROM order_summary GROUP BY region;

把該 SQL 用 EXPLAIN PLAN 預測路徑與資料量。

1
EXPLAIN PLAN SELECT region, SUM(amount) FROM order_summary GROUP BY region;

1
Expression (Projection)
2
 Aggregating
3
  ReadFromMergeTree (order_summary)

全表掃描！

檢查是否：
- 有全表掃描 (資料區塊過大)。
- 有不必要的 JOIN → 可否轉 Materialized View。
- 缺少 Partition Pruning、索引無法生效。

查詢條件沒有加上 Partition Key (date)。

order_summary 按 (date, region) 分區，但查詢沒帶 date 範圍 → 全表掃描。

可考慮將 region 聚合寫入 Materialized View 預先計算。

調整查詢條件（如加 Partition Key 範圍、Data Skipping Index）。

1
SELECT region, SUM(amount)
2
FROM order_summary
3
WHERE date = today() - 1
4
GROUP BY region;

再次觀察 query_log → 查詢耗時是否下降。

1
SELECT query_duration_ms FROM system.query_log
2
WHERE query LIKE '%order_summary%'
3
AND event_time > now() - INTERVAL 5 MINUTE
4
AND type = 'QueryFinish'
5
ORDER BY query_start_time DESC
6
LIMIT 1;

1
query_duration_ms: 300ms

4500ms -> 300ms (Nice Try Diddy)

進階：優化一個全局掃描#

1
SELECT user_id, COUNT(*) FROM user_events GROUP BY user_id;

執行 EXPLAIN PLAN → 確認是否使用了 Primary Key Index。

1
EXPLAIN PLAN SELECT user_id, COUNT(*) FROM user_events GROUP BY user_id;

1
Expression (Projection)
2
 Aggregating
3
  ReadFromMergeTree (user_events)

完全沒有 Index 篩選，直接全表掃描。

若未使用 → 加入 Partition Pruning 條件。

user_events 的 Partition Key 是 EventDate，所以我們加上日期範圍：

1
SELECT user_id, COUNT(*)
2
FROM user_events
3
WHERE EventDate >= today() - 7
4
GROUP BY user_id;

使用 EXPLAIN ESTIMATE 檢查掃描量是否下降。

1
EXPLAIN ESTIMATE
2
SELECT user_id, COUNT(*)
3
FROM user_events
4
WHERE EventDate >= today() - 7;

1
┌─database─┬─table────────┬─parts─┬─rows──────┬─marks─┐
2
│ default  │ user_events  │     3 │ 10000000  │   800 │
3
└──────────┴──────────────┴───────┴───────────┴───────┘

原本未加條件時掃描了 1 億筆 rows，現在僅掃描 1 千萬筆，資料量明顯下降。

檢查 PIPELINE 是否有並行處理。

1
EXPLAIN PIPELINE
2
SELECT user_id, COUNT(*)
3
FROM user_events
4
WHERE EventDate >= today() - 7
5
GROUP BY user_id;

確認查詢能夠使用多個 AggregatingTransform 節點平行處理。

再次查詢 system.query_log 驗證查詢耗時是否下降。

1
SELECT query_duration_ms FROM system.query_log
2
WHERE query LIKE '%user_events%'
3
AND event_time > now() - INTERVAL 5 MINUTE
4
AND type = 'QueryFinish'
5
ORDER BY query_start_time DESC
6
LIMIT 1;