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

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

什麼是 system.query_log？

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

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

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

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

SELECT
    query_start_time,
    query_duration_ms,
    read_rows,
    result_rows,
    query
FROM system.query_log
WHERE event_time > now() - INTERVAL 10 MINUTE
AND type = 'QueryFinish'
AND query LIKE 'SELECT%'
ORDER BY query_start_time DESC
LIMIT 100;

常見欄位解讀

如何找出「慢查詢」？

SELECT
    query_start_time,
    query_duration_ms,
    read_rows,
    memory_usage,
    query
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
AND type = 'QueryFinish'
AND query_duration_ms > 500  -- 大於 500ms
ORDER BY query_duration_ms DESC;

什麼是 EXPLAIN ？

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

EXPLAIN SYNTAX：

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

EXPLAIN 範例

SYNTAX - 經語法優化後的查詢

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

SELECT *
FROM system.numbers
WHERE number < 10

QUERY TREE — 最終查詢邏輯結構

EXPLAIN QUERY TREE SELECT id, value FROM test_table;

QUERY id: 0
  PROJECTION COLUMNS
    id UInt64
    value String
  JOIN TREE
    TABLE id: 3, table_name: default.test_table

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

PLAN - 執行計畫步驟

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

Union
 Expression (Projection)
  Aggregating
   ReadFromStorage (SystemNumbers)

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

ESTIMATE — 查詢預估讀取量

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

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

進階：優化一個慢查詢

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

SELECT
    query_start_time,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage,
    query
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
AND type = 'QueryFinish'
AND query LIKE '%order_summary%'
ORDER BY query_duration_ms DESC
LIMIT 5;

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

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

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

Expression (Projection)
 Aggregating
  ReadFromMergeTree (order_summary)

全表掃描！

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

• 查詢條件沒有加上 Partition Key (date)。
• order_summary 按 (date, region) 分區，但查詢沒帶 date 範圍 → 全表掃描。
• 可考慮將 region 聚合寫入 Materialized View 預先計算。

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

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

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

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

query_duration_ms: 300ms

4500ms -> 300ms (Nice Try Diddy)

進階：優化一個全局掃描

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

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

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

Expression (Projection)
 Aggregating
  ReadFromMergeTree (user_events)

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

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

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

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

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

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

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

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

4. 檢查 PIPELINE 是否有並行處理。

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

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

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

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

query_duration_ms: 600ms

結語

EXPLAIN 是 ClickHouse 優化查詢性能的核心工具，透過不同模式，你可以：

• 了解查詢的執行結構
• 預測查詢的資源消耗
• 找出瓶頸進行針對性優化