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_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,因為聚合時機不可控
掃碼關注有驚喜!
- 如何基於Security框架兼容多套用户密碼加密方式
- 基於Kubernetes(k8s)部署Dubbo Nacos服務
- 基於jib-maven-plugin快速構建微服務docker鏡像
- 基於minikube快速搭建單節點環境
- 隱私計算FATE-多分類神經網絡算法測試
- 隱私計算FATE-離線預測
- 隱私計算FATE-模型訓練
- 隱私計算FATE-核心概念與單機部署
- Hyperledger Fabric 核心概念
- Hyperledger Fabric 2.x Java區塊鏈應用
- Hyperledger Fabric 2.x 自定義智能合約
- Hyperledger Fabric 2.x 環境搭建
- Spring Boot 如何熱加載jar實現動態插件?
- 如何基於Security實現OIDC單點登錄?
- 第三方API對接如何設計接口認證?
- 免費正版 IntelliJ IDEA license 詳細指南
- ClickHouse性能優化?試試物化視圖
- 全量同步Elasticsearch方案之Canal
- Canal高可用架構部署
- 大數據量查詢容易OOM?試試MySQL流式查詢