在前幾篇文章中，我們已經介紹過 ClickHouse 的基礎架構、MergeTree 儲存引擎，以及各種索引與資料壓縮機制。這些特性讓 ClickHouse 成為一個極具效能的 OLAP（線上分析處理）資料庫，特別適合處理大規模資料查詢。

然而，在實際專案中，我們會發現「效能並非理所當然」。同樣一張表、同樣一筆數據，如果查詢設計不當，效能可能差到百倍以上。

為什麼需要查詢優化？

ClickHouse 的確擅長處理數十億筆資料，但這並不代表我們可以「無腦查詢」。隨著資料量從 100 萬 → 1000 萬 → 1 億 成長，若沒有妥善設計，查詢速度可能會從毫秒級惡化成數秒甚至數十秒，直接影響使用者體驗。

舉例來說：

• Dashboard 無法即時更新，導致決策延遲
• API 響應超過數秒，造成前端操作卡頓
• Backend, Data Scientist 無法在合適時間內完成查詢分析

因此，查詢優化不只是為了「快」，更是為了系統的穩定性與可擴展性。

OFFSET 分頁效能差

在許多系統中，最常見的需求就是「分頁查詢」。假設我們有一個 events 表，用來紀錄使用者的行為事件：

SELECT * FROM events 
ORDER BY created_at DESC 
LIMIT 50 OFFSET 1000000;

這個查詢看似正常，但隨著 OFFSET 變大，效能會急速下降。原因在於 ClickHouse 需要掃描並丟棄前面一百萬筆資料，才能回傳第 1000001 筆到 1000050 筆。

優化方案：Keyset Pagination（游標分頁）

改用「基於主鍵或排序欄位的分頁」：

SELECT * FROM events 
WHERE created_at < '2025-01-01 00:00:00'
ORDER BY created_at DESC
LIMIT 50;

這種方式直接從指定時間點往後查詢，不需要丟棄前面的資料，效能大幅提升。

• 優化前：數秒到數十秒
• 優化後：數百毫秒甚至更快

這種方法在時間序列資料中特別有效，也符合 ClickHouse 的設計哲學：盡量掃描少量資料，而非掃描所有資料再過濾。

WHERE 條件未使用索引

另一個常見問題是「查詢條件沒有命中索引」。假設我們要查詢某個使用者最近 7 天的紀錄：

SELECT COUNT(*) 
FROM logs 
WHERE user_id = 123 
AND created_at >= today() - 7;

如果表的排序鍵不是 (user_id, created_at)，這個查詢就會全表掃描，效能極差。

優化方案：Primary Key + Partition

在建表時，我們應該考慮查詢模式，將常用的過濾條件設定為排序鍵或分區：

CREATE TABLE logs
(
    user_id UInt64,
    created_at DateTime,
    event_type String,
    ...
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (user_id, created_at);

這樣，當我們查詢某個 user_id 並限制時間區間時，ClickHouse 會自動進行 Partition Pruning，只掃描必要的資料。

• 優化前：掃描數億筆資料
• 優化後：僅掃描數百萬筆資料，速度提升 10 倍以上

重複資料導致彙總變慢

在實務上，我們常會遇到「批次匯入導致重複資料」的情境。例如，某些 ETL 程序每天匯入一份全量資料，但會包含重複紀錄。

原始查詢需要先 GROUP BY 去重，這對數億筆資料來說效能極差。

優化方案 1：ReplacingMergeTree 去重

使用 ReplacingMergeTree，在合併階段自動去除重複紀錄：

CREATE TABLE events
(
    id UInt64,
    user_id UInt64,
    event_type String,
    version UInt32
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (id);

這樣，查詢時就不需要再額外去重，效能大幅提升，這是我在實習過程中親身經歷的QQ，直接重建一張 table，把資料轉移到 ReplacingMergeTree的 new table 上。

優化方案 2：Materialized View 預聚合

另一個作法是建立 MV (Materialized View)，將原始資料預先彙總存入新表：

CREATE MATERIALIZED VIEW events_mv 
ENGINE = SummingMergeTree()
ORDER BY (user_id, event_type)
AS
SELECT user_id, event_type, count() AS cnt
FROM events
GROUP BY user_id, event_type;

查詢時只需要對 events_mv 做 SELECT，效能幾乎是秒殺級。

JOIN 效能不佳

ClickHouse 的 JOIN 並不像傳統關聯式資料庫那樣靈活，若不小心，效能會很差。

假設我們要將 events 表與 users 表做關聯：

SELECT e.*, u.name 
FROM events e
JOIN users u ON e.user_id = u.id;

若 users 是一張大表，JOIN 效能會急速下降。

優化方案：Dictionary 加速

如果 users 是一張小表，可以轉成 Dictionary，放到記憶體中供查詢使用：

CREATE DICTIONARY users_dict
(
    id UInt64,
    name String
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE users))
LAYOUT(HASHED());

查詢時就可以改寫成：

SELECT e.*, dictGet('users_dict', 'name', toUInt64(e.user_id)) AS user_name
FROM events e;

這種方式等於把 users 變成一個高效快取，避免大表 JOIN。

• 優化前：JOIN 查詢數秒甚至數十秒
• 優化後：查詢僅需數百毫秒

想學習 Dictionary 可參考 官方文件， Dictionary 是一個專門給小表作為 cache 的特殊型別，讓你在高頻查詢中，避免每次都去 JOIN 大表，改用 快取好的 Key-Value 對應 來加速查詢時間。

總結

從這些案例可以看到，ClickHouse 的查詢效能優化大致遵循以下原則：

1. 避免 OFFSET，改用 Keyset 分頁
2. 設計良好的排序鍵與分區，讓查詢能命中索引
3. 使用 MergeTree 變種 (Replacing / Summing) 來處理去重與聚合
4. 善用 Materialized View 預先計算，避免重複運算
5. JOIN 最小化：小表 JOIN 可以轉成 Dictionary，大表 JOIN 需慎用
6. 盡量減少需要掃描的資料量，而不是「事後再過濾」

透過查詢優化，我們能將原本數秒甚至數十秒的查詢縮短到毫秒級，這對即時分析、線上系統效能都有關鍵意義。

希望這些案例能幫助大家在實務中更好地駕馭 ClickHouse！ 🚀