关于java: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 会本人预聚合，然而并不是实时的，具体执行聚合的机会并 不可控。

总结

1. 在创立 MV 表时，肯定要应用 TO 关键字为 MV 表指定存储地位，否则不反对 嵌套视图(多个物化视图持续聚合一个新的视图)
2. 在创立 MV 表时如果用到了多表联查，不能为连贯表指定别名，如果多个连贯表中存在同名字段，在连贯表的查问语句中应用 AS 将字段名辨别开
3. 在创立 MV 表时如果用到了多表联查，只有当第一个查问的表有数据插入时，这个 MV 才会被触发
4. 在创立 MV 表时不要应用 POPULATE 关键字，而是在 MV 表建好之后将数据手动导入 MV 表
5. 在应用 MV 的聚合引擎时，也须要依照聚合查问来写 sql，因为聚合机会不可控

扫码关注有惊喜！