一、前言
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_cnt
from login_user_log
group 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_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會自己預聚合，但是并不是實時的，具體執行聚合的時機并 不可控，
總結
- 在創建 MV 表時，一定要使用 TO 關鍵字為 MV 表指定存盤位置，否則不支持 嵌套視圖(多個物化視圖繼續聚合一個新的視圖)
- 在創建 MV 表時如果用到了多表聯查，不能為連接表指定別名，如果多個連接表中存在同名欄位，在連接表的查詢陳述句中使用 AS 將欄位名區分開
- 在創建 MV 表時如果用到了多表聯查，只有當第一個查詢的表有資料插入時，這個 MV 才會被觸發
- 在創建 MV 表時不要使用 POPULATE 關鍵字，而是在 MV 表建好之后將資料手動匯入 MV 表
- 在使用 MV 的聚合引擎時，也需要按照聚合查詢來寫sql，因為聚合時機不可控
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/273648.html
標籤：AI