ClickHouse性能優化？試試物化視圖

語言: CN / TW / HK
時間 2021-04-07 11:02:27

mark

一、前言

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

 

二、概念

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

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

在傳統關係型數據庫中，Oracle、PostgreSQL、SQL Server等都支持物化視圖，而作為MPP數據庫的ClickHouse也支持該特性。

file

 

三、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_cnt
from login_user_log
group by city, login_date

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

file

 

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_cnt
from login_user_log
group by city, login_date

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

 

4.3. 查詢統計結果

使用物化視圖查詢

SELECT city, login_date, sum(login_cnt) cnt
from login_user_log_mv
group 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，因為聚合時機不可控

 

掃碼關注有驚喜！

file

「」
「」