ClickHouse 效能優化？試試物化檢視

一、前言

ClickHouse 是一個用於聯機分析 (OLAP) 的列式資料庫管理系統 (DBMS)；目前我們使用 CH 作為實時數倉用於統計分析，在做效能優化的時候使用了  物化檢視 這一特性作為優化手段，本文主要分享物化檢視的特性與如何使用它來優化 ClickHouse 的查詢效能。

 

二、概念

資料庫中的  檢視(View) 指的是通過一張或多張表查詢出來的  邏輯表 ，本身只是一段  SQL 的封裝並  不儲存資料。

而  物化檢視(Materialized View) 與普通檢視不同的地方在於它是一個查詢結果的資料庫物件 (持久化儲存)，非常趨近於表；物化檢視是資料庫中的預計算邏輯 + 顯式快取，典型的空間換時間思路，所以用得好的話，它可以避免對基礎表的頻繁查詢並複用結果，從而顯著提升查詢的效能。

在傳統關係型資料庫中，Oracle、PostgreSQL、SQL Server 等都支援物化檢視，而作為 MPP 資料庫的 ClickHouse 也支援該特性。

 

三、ClickHouse 物化檢視

ClickHouse 中的物化檢視可以掛接在任意引擎的基礎表上，而且會自動更新資料，它可以藉助 MergeTree 家族引擎 (SummingMergeTree、Aggregatingmergetree 等)，得到一個實時的預聚合，滿足快速查詢；但是對  更新 與  刪除 操作支援並不好，更像是個插入觸發器。

建立語法：

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

POPULATE 關鍵字決定了物化檢視的更新策略：

• 若有 POPULATE 則在建立檢視的過程會將源表已經存在的資料一併匯入，類似於 create table ... as
• 若無 POPULATE 則物化檢視在建立之後沒有資料

ClickHouse 官方並不推薦使用 populated，因為在建立檢視過程中插入表中的資料並不會寫入檢視，會造成資料的丟失。

 

四、案例

4.1. 場景

假設有一個日誌表  login_user_log 來記錄每次登入的使用者資訊，現在需要按使用者所屬地為維度來統計每天的登入次數。

PS：這種  只有新增記錄，沒有更新刪除的記錄表就非常適合使用  物化檢視 來優化統計效能

 

正常的聚合 SQL 如下：city 為使用者所屬地，login_date 為登入時間

select city, login_date, count(1) login_cntfrom login_user_loggroup by city, login_date

增加  物化檢視 後的架構如下圖所示：

 

4.2. 建表

建立基礎表：基礎表使用  SummingMergeTree 引擎，進行預聚合處理

CREATE TABLE login_user_log_base
(
    city String,
		login_date Date,
    login_cnt UInt32
)ENGINE = SummingMergeTree()ORDER BY (city, login_date)

SummingMergeTree 表引擎主要用於只關心聚合後的資料，而不關心明細資料的場景，它能夠在合併分割槽的時候按照預先定義的條件聚合彙總資料，將同一分組下的多行資料彙總到一行，可以顯著的  減少儲存空間並加快資料查詢的速度。

 

建立物化檢視：使用者在建立物化檢視時，通過  AS SELECT ... 子句從源表中查詢需要的列，十分靈活

CREATE MATERIALIZED VIEW if not exists login_user_log_mv 
TO login_user_log_base 
AS SELECT city, login_date, count(1) login_cntfrom login_user_loggroup by city, login_date

使用  TO 關鍵字關聯  物化檢視 與  基礎表，需要自己初始化歷史資料。

 

4.3. 查詢統計結果

使用物化檢視查詢

SELECT city, login_date, sum(login_cnt) cntfrom login_user_log_mvgroup by city, login_date

注意：在使用物化檢視（SummingMergeTree 引擎）的時候，也需要按照聚合查詢來寫 sql，因為雖然  SummingMergeTree 會自己預聚合，但是並不是實時的，具體執行聚合的時機並  不可控。

 

總結

1. 在建立 MV 表時，一定要使用 TO 關鍵字為 MV 表指定儲存位置，否則不支援  巢狀檢視 (多個物化檢視繼續聚合一個新的檢視)
2. 在建立 MV 表時如果用到了多表聯查，不能為連線表指定別名，如果多個連線表中存在同名欄位，在連線表的查詢語句中使用 AS 將欄位名區分開
3. 在建立 MV 表時如果用到了多表聯查，只有當第一個查詢的表有資料插入時，這個 MV 才會被觸發
4. 在建立 MV 表時不要使用 POPULATE 關鍵字，而是在 MV 表建好之後將資料手動匯入 MV 表
5. 在使用 MV 的聚合引擎時，也需要按照聚合查詢來寫 sql，因為聚合時機不可控