ClickHouse 進階指南:最佳化、具體化檢視和真實技巧

如何把 ClickHouse 發揮到極致:ORDER BY、具體化檢視、projection、編碼、TTL、S3 分層和慢查詢診斷。帶真實 SQL。

根本原則:讀更少的資料
1. 排序鍵:決定一切的那個決定
2. 具體化檢視:把工作挪到插入時
3. Projection:同一張表按兩種方式排序
4. 壓縮編碼:最被低估的調校
- 前後都要測
- 資料型別本身也是壓縮
5. 跳過索引:謹慎使用
- 重要的警告
6. TTL 和分層:自己清理自己的資料
7. 插入:把所有人都拖垮的錯誤
8. 診斷:找出哪裡出了問題
9. 更聰明的查詢
我的最佳化清單
關於最佳化的最後一點反思
常見問題

根本原則:讀更少的資料

下面所有內容都是同一個思路的變體。ClickHouse 之所以快,不是因為它處理資料快——它確實也快——而是因為它處理的資料比你想的少得多。

你發的每條查詢都會告訴你它讀了多少:

6 rows in set. Elapsed: 0.021 sec. Processed 1.14 million rows, 8.32 MB

這一行才是你的主要指標。如果你的表有 5 億筆,而一條查詢處理了 5 億筆,你根本沒最佳化任何東西:你是在做一次很快的全表掃描。目標永遠是把這個數字壓下去。

壓它的工具,按影響力排序:排序鍵、具體化檢視、projection、跳過索引和分區。這個順序。

1. 排序鍵:決定一切的那個決定

如果這篇文章你只帶走一件事,請帶走這一節。

稀疏索引怎麼運作

ClickHouse 沒有按列的 B 樹索引。它按你的 ORDER BY 在磁碟上實體排序資料,每 8,192 列(一個顆粒)存一個 mark,記錄那個位置上的鍵值。整個索引每 TB 只占幾 MB。

當你用 ORDER BY 裡的欄位過濾時,ClickHouse 看這個索引,找出可能包含匹配值的顆粒,只讀那些。其他一律不碰。

結論很直接:只有當一欄在 ORDER BY 的前綴裡,它才能加速你的查詢。如果你的鍵是 (evento, fecha, usuario_id),按 evento 過濾極快,按 evento AND fecha 也快,但只按 usuario_id 過濾會逼成全表掃描。順序的重要性跟 Postgres 複合索引一樣,但後果要大得多。

選 ORDER BY 的三條規則

規則 1:總是用來過濾的欄位放前面。 看你的真實查詢,不是假想的。如果 95% 帶 WHERE tenant_id = ?,tenant_id 放第一。

規則 2:使用頻率一樣時,低基數放前面。 一個有 6 個不同值的欄位(國家)比一個有 50,000 個值的欄位(usuario_id)更能聚攏資料。把高基數放前面會讓顆粒碎片化,既毀壓縮又毀跳區塊。

規則 3:時間欄位幾乎總會進鍵裡,但很少放第一。 把 timestamp 放最前很誘人,因為「所有東西都按日期過濾」。但 (tenant_id, evento, fecha) 通常比 (fecha, tenant_id, evento) 好,因為日期已經和自然插入順序高度相關。

一個設計良好的表的例子:

CREATE TABLE eventos
(
    tenant_id   UInt32,
    fecha       Date,
    timestamp   DateTime CODEC(Delta, ZSTD(1)),
    evento      LowCardinality(String),
    pais        LowCardinality(String),
    usuario_id  UInt32 CODEC(Delta, ZSTD(1)),
    url         String CODEC(ZSTD(3)),
    duracion_ms UInt32 CODEC(T64, ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(fecha)
ORDER BY (tenant_id, evento, fecha, usuario_id);

把主鍵和排序鍵分開

一個很少人用的細節:你可以有一個長 ORDER BY 和一個更短的 PRIMARY KEY。

ENGINE = MergeTree
ORDER BY (tenant_id, evento, fecha, usuario_id)
PRIMARY KEY (tenant_id, evento)

ORDER BY 控制實體順序(從而控制壓縮),而 PRIMARY KEY 控制記憶體裡索引存什麼。如果你的排序鍵有五欄,而你只用前兩欄過濾,這能減少索引的記憶體占用,且不損失任何東西。

怎麼驗證你的鍵有效

打開 trace,看丟棄了多少顆粒:

SET send_logs_level = 'trace';

SELECT count() FROM eventos WHERE tenant_id = 42 AND evento = 'purchase';

日誌裡你會看到這樣的列:

Key condition: (column 0 in [42, 42]), (column 1 in ['purchase', 'purchase'])
Selected 3/48 parts by partition key, 3 parts by primary key, 14/61035 marks by primary key

14/61035 marks 意思是你讀了表的 0.02%。這是一個選得好的鍵。如果你看到 61035/61035,說明你的過濾沒用上索引。

2. 具體化檢視:把工作挪到插入時

這是影響力最大、又最被誤解的特性。

ClickHouse 的具體化檢視不是快取、也不是會定期刷新的表。它是一個插入觸發器:每當一個資料區塊到達源表,查詢就在這個區塊上跑,結果寫到目標表。原始資料再也不會被讀。

這有一個關鍵含義:具體化檢視只看得到它建立之後插入的資料。 歷史資料必須手動回填。

標準模式

三塊:源表、彙總目標表、連接它們的檢視。

-- 1. 帶彙總狀態的目標表
CREATE TABLE eventos_diarios
(
    tenant_id       UInt32,
    fecha           Date,
    evento          LowCardinality(String),
    pais            LowCardinality(String),
    total           UInt64,
    usuarios_unicos AggregateFunction(uniq, UInt32),
    duracion_media  AggregateFunction(avg, UInt32)
)
ENGINE = SummingMergeTree
ORDER BY (tenant_id, fecha, evento, pais);

-- 2. 餵它的具體化檢視
CREATE MATERIALIZED VIEW mv_eventos_diarios
TO eventos_diarios
AS
SELECT
    tenant_id,
    fecha,
    evento,
    pais,
    count()                 AS total,
    uniqState(usuario_id)   AS usuarios_unicos,
    avgState(duracion_ms)   AS duracion_media
FROM eventos
GROUP BY tenant_id, fecha, evento, pais;

注意 uniqState 和 avgState。-State 後綴存的是彙總的中間狀態,而不是最終結果。這正是讓你能正確合併來自不同區塊的彙總的東西。

查詢時,你用 -Merge 後綴:

SELECT
    fecha,
    evento,
    sum(total)                    AS eventos,
    uniqMerge(usuarios_unicos)    AS usuarios,
    avgMerge(duracion_media)      AS duracion
FROM eventos_diarios
WHERE tenant_id = 42
  AND fecha >= today() - 30
GROUP BY fecha, evento
ORDER BY fecha;

為什麼這如此重要: 這條查詢讀的是幾千筆預彙總資料,而不是幾億筆原始資料。差別不是 2 倍,是兩到三個數量級。而且因為計算在插入時做,成本被分散到時間上,而不是集中在你使用者看 dashboard 的那一刻。

常見錯誤:用 avg() 而不是 avgState(),查詢時又對平均值取平均。這會給出錯誤結果,因為平均的平均不是平均。彙總狀態正是為避免這個而存在。

可刷新的具體化檢視

最近幾個版本也有可刷新的變體,它確實會週期性重跑完整查詢:

CREATE MATERIALIZED VIEW mv_resumen
REFRESH EVERY 1 HOUR
ENGINE = MergeTree ORDER BY fecha
AS SELECT ... ;

在需要和會變的維度表做 JOIN 時有用——增量檢視處理不好這種情況。更貴,但更容易推理。

3. Projection:同一張表按兩種方式排序

這裡是經典問題:你的 ORDER BY 為按 tenant_id 過濾做了最佳化,但你也需要按 url 過濾的快查詢。你只能有一個實體順序。

Projection 透過在同一個表裡存一份按另一種方式排序的額外副本來解決這個問題。ClickHouse 自動選擇用哪個。

ALTER TABLE eventos ADD PROJECTION proj_por_url
(
    SELECT
        url,
        fecha,
        count(),
        avg(duracion_ms)
    GROUP BY url, fecha
);

-- 對既有資料具體化
ALTER TABLE eventos MATERIALIZE PROJECTION proj_por_url;

現在按 url 分組的查詢會用 projection,你不用改 SQL。用 EXPLAIN 驗證:

EXPLAIN indexes = 1
SELECT url, count() FROM eventos WHERE fecha >= today() - 7 GROUP BY url;

Projection vs. 具體化檢視。 顯而易見的問題。我的標準:

用 projection:當你想要同一份資料以另一種順序、或同一張表的一個派生彙總,並且你看重它的透明性(不改查詢、一致性自動)。

用 具體化檢視:當你需要變換資料、寫到帶不同 TTL 的表、鏈多個階段、或合併多個來源。以及目標表需要獨立存活的時候。

projection 的代價是儲存(資料存兩份)和插入速度。別給一張持續接收插入的表加五個 projection。

4. 壓縮編碼:最被低估的調校

磁碟上位元組更少 = I/O 更少 = 查詢更快。壓縮不是省儲存的話題,是效能的話題。

ClickHouse 預設用 LZ4,效果不錯,但按欄調會有真實收益。官方文件按重要性推薦:

ZSTD 作底。 它提供最好的壓縮比,ZSTD(1) 對大多數型別是個好預設。超過 ZSTD(3) 很少值得那一點插入成本。

Delta 用於日期和整數序列。 對單調序列或相鄰值差異小的情境非常有效。時間戳和自增 ID 是經典案例。如果一階導數的結果還不夠小,試 DoubleDelta。

Delta 改進 ZSTD。 它們組合得好:CODEC(Delta, ZSTD(1)) 通常勝過單獨用任何一個。

LZ4 如果和 ZSTD 打平。 如果你拿到接近的壓縮比,優先 LZ4,因為它解壓更快、CPU 占用更低。實際中 ZSTD 在大多數情況下以明顯優勢勝出。

T64 用於小範圍或稀疏資料。 當區塊內值範圍窄時有效。避免用於亂數。

Gorilla 用於感測器類浮點。 為變化小的儀表讀數設計。

實務中:

CREATE TABLE metricas
(
    timestamp  DateTime      CODEC(Delta, ZSTD(1)),
    sensor_id  UInt32        CODEC(Delta, ZSTD(1)),
    temperatura Float32      CODEC(Gorilla, ZSTD(1)),
    estado     LowCardinality(String),
    payload    String        CODEC(ZSTD(3))
)
ENGINE = MergeTree
ORDER BY (sensor_id, timestamp);

前後都要測

SELECT
    name,
    formatReadableSize(sum(data_compressed_bytes))   AS comprimido,
    formatReadableSize(sum(data_uncompressed_bytes)) AS sin_comprimir,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table = 'metricas'
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC;

按壓縮大小排序,只攻最大的兩三欄。給一欄只占 3 MB 的欄調編碼是浪費時間。

關於 compact part 的提示: 如果你在 compressed_size 裡看到零,那是因為你的 part 是 compact 而不是 wide 型別(小插入時發生)。由 min_bytes_for_wide_part 和 min_rows_for_wide_part 控制。

資料型別本身也是壓縮

在動編碼之前,先複查型別。一個 0 到 100 的值用 UInt8 而不是 Int64 是免费的 8 倍縮減。不同值少於約 10,000 的欄用 LowCardinality(String)。日期用 DateTime 而不是 String。ClickHouse 文件顯示,僅靠最佳化型別和排序鍵,同一個資料集壓縮後從 50 GB 降到了 25 GB。

5. 跳過索引:謹慎使用

data skipping indexes 讓你能按不在 ORDER BY 裡的欄位過濾、跳過區塊。聽起來像萬能解,幾乎從來都不是。

ALTER TABLE eventos ADD INDEX idx_pais pais TYPE set(100) GRANULARITY 4;
ALTER TABLE eventos MATERIALIZE INDEX idx_pais;

主要型別:

minmax 存每個區塊的最小和最大值。套用最便宜。對近似有序的欄位和範圍過濾理想。

set(N) 每個區塊存最多 N 個不同值。全域高基數但每個區塊內低基數時好用。

bloom_filter 用於在大值集合裡測成員關係。對陣列和 map 有效。

text 是真正用於全文搜尋的倒排索引。這是今天推薦的;舊的 tokenbf_v1 和 ngrambf_v1 已標記為棄用。

重要的警告

官方文件對此很明確,值得重複:「給一個你經常查的欄加索引」這種自然衝動,在 ClickHouse 裡通常是錯的。

跳過索引只有在主鍵和被索引欄位之間有強相關時才有用。如果那一欄的值在整個表裡隨機散布,每個區塊都會包含一些,什麼也跳不掉。你為索引付出了代價(插入和查詢上),換來的收益是零。

在加跳過索引之前,按這個順序試:改 ORDER BY、加一個 projection、或建一個具體化檢視。跳過索引是最後手段,不是第一手段。

確實發光的情境:稀有但重要的值。在日誌表上對 error_code 加一個 set 索引,能跳過絕大多數沒有錯誤的區塊。

注意這個細節,因為每個人第一次都在這裡栽跟頭:ClickHouse 要求 TTL 的 GROUP BY 欄位是主鍵的前綴。如果你的表按 (sensor_id, timestamp) 排序,你卻按 toStartOfHour(timestamp) 分組,你會得到 TTL Expression GROUP BY key should be a prefix of primary key 錯誤。解決辦法是把取整後的運算式放進 ORDER BY 本身,就像上面例子那樣。

S3 分層

讓長期保留在低成本下可行的技巧。設定一個 S3 磁碟和儲存策略:

<!-- /etc/clickhouse-server/config.d/storage.xml -->
<clickhouse>
  <storage_configuration>
    <disks>
      <s3_frio>
        <type>s3</type>
        <endpoint>https://mi-bucket.s3.eu-west-1.amazonaws.com/clickhouse/</endpoint>
        <access_key_id>TU_ACCESS_KEY</access_key_id>
        <secret_access_key>TU_SECRET_KEY</secret_access_key>
      </s3_frio>
    </disks>
    <policies>
      <caliente_frio>
        <volumes>
          <caliente><disk>default</disk></caliente>
          <frio><disk>s3_frio</disk></frio>
        </volumes>
      </caliente_frio>
    </policies>
  </storage_configuration>
</clickhouse>

然後套用它:

ALTER TABLE eventos MODIFY SETTING storage_policy = 'caliente_frio';

ALTER TABLE eventos MODIFY TTL
    fecha + INTERVAL 30 DAY TO VOLUME 'frio',
    fecha + INTERVAL 365 DAY DELETE;

最近 30 天在本地 SSD(快),其餘在 S3(便宜),全部一年後刪除。對小專案,這正是把「我只存 30 天,因為付不起更多」變成「我花幾分錢存兩年」的東西。 對冷資料的查詢當然更慢,但它們仍然能跑,而且是最少被跑的那些。

7. 插入:把所有人都拖垮的錯誤

生產環境的頭號問題不是慢查詢,是 Too many parts。

每次 INSERT 都在磁碟上建立一個新 part。part 在背景合併,但如果你插得比合併快,它們就堆積,直到伺服器拒絕寫入。

規則:批次插入

每次至少 1,000 筆;理想是 10,000 到 100,000。在你的應用裡緩衝,按大小或按時間(哪個先到)刷出。

替代:非同步插入

如果你的架構不容易緩衝——比如很多行程各自寫單一事件——那就讓 ClickHouse 替你做:

SET async_insert = 1;
SET wait_for_async_insert = 1;

ClickHouse 在伺服器側緩衝裡累積,按批次刷出。wait_for_async_insert = 1 時用戶端等真正的寫入確認(更安全);為 0 時立即得到確認(更快,但如果伺服器在 flush 前掛了,你可能丟資料)。

用 async_insert_max_data_size 和 async_insert_busy_timeout_ms 調緩衝行為。

診斷

SELECT
    table,
    count()                                  AS num_parts,
    sum(rows)                                AS filas,
    formatReadableSize(sum(bytes_on_disk))   AS tamano
FROM system.parts
WHERE active AND database = 'creacosas'
GROUP BY table
ORDER BY num_parts DESC;

如果一張表有幾百個活躍 part,你的插入模式就是問題。

而且,OPTIMIZE TABLE ... FINAL 不是解決方案。它強制做一次在大表上非常昂貴的完整合併,還不修根因。官方文件明確建議避免它。

8. 診斷:找出哪裡出了問題

查詢日誌是你最好的工具

ClickHouse 把每條查詢連同指標記錄下來:

SELECT
    query_duration_ms,
    formatReadableQuantity(read_rows)    AS filas_leidas,
    formatReadableSize(read_bytes)       AS bytes_leidos,
    formatReadableSize(memory_usage)     AS memoria,
    substring(query, 1, 120)             AS consulta
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 20;

按耗時排序,攻最差的那批。把 read_rows 和pin表的總筆數對比:如果接近,說明你的過濾沒用上索引。

EXPLAIN

EXPLAIN indexes = 1
SELECT ... ;

它顯示用了哪些索引和 projection,每步丟棄了多少顆粒。

PREWHERE

ClickHouse 通常會自動套用這個最佳化,但你可以強制。PREWHERE 先讀過濾欄位,丟棄列,然後再讀其餘欄位:

SELECT url, payload_grande
FROM eventos
PREWHERE evento = 'error'      -- 先只讀這一欄
WHERE duracion_ms > 5000;

在你按小欄位過濾、又選大欄位的時候特別有用。

查詢快取

對帶相同重複查詢的 dashboard:

SELECT ... SETTINGS use_query_cache = 1;

全域設 TTL。它不是銀彈——只在查詢逐字重複時有用——但在一個幾個人同時看的 dashboard 上,它能省不少。

9. 更聰明的查詢

精度不重要時用近似彙總。 uniq() 用 HyperLogLog,比 uniqExact() 快得多、記憶體也輕得多。對一個「不重複使用者」的 dashboard,0.5% 的誤差無傷大雅。

用字典代替 JOIN。 如果你反覆和一張小維度表 JOIN,把它作為記憶體字典載入:

CREATE DICTIONARY dic_paises (
    codigo String,
    nombre String
)
PRIMARY KEY codigo
SOURCE(CLICKHOUSE(TABLE 'paises'))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(3600);

SELECT dictGet('dic_paises', 'nombre', pais) AS pais_nombre, count()
FROM eventos GROUP BY pais;

一個 dictGet 是一次記憶體雜湊查詢:比 JOIN 快幾個數量級。

組合子函式。 -If、-Array、-Merge 能省掉整個子查詢:

SELECT
    fecha,
    countIf(evento = 'purchase')                      AS compras,
    countIf(evento = 'signup')                        AS registros,
    avgIf(duracion_ms, evento = 'pageview')           AS duracion_pageview
FROM eventos
GROUP BY fecha;

一次掃資料,而不是三次。

用 windowFunnel 做轉換漏斗。 在標準 SQL 裡算漏斗很痛苦。在這裡它是一個函式:

SELECT
    nivel,
    count() AS usuarios
FROM (
    SELECT
        usuario_id,
        windowFunnel(3600)(
            timestamp,
            evento = 'pageview',
            evento = 'signup',
            evento = 'purchase'
        ) AS nivel
    FROM eventos
    WHERE fecha >= today() - 30
    GROUP BY usuario_id
)
GROUP BY nivel
ORDER BY nivel;