一、前言
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会自己预聚合，但是并不是实时的，具体执行聚合的时机并 不可控。
总结
- 在创建 MV 表时，一定要使用 TO 关键字为 MV 表指定存储位置，否则不支持 嵌套视图 (多个物化视图继续聚合一个新的视图)
- 在创建 MV 表时如果用到了多表联查，不能为连接表指定别名，如果多个连接表中存在同名字段，在连接表的查询语句中使用 AS 将字段名区分开
- 在创建 MV 表时如果用到了多表联查，只有当第一个查询的表有数据插入时，这个 MV 才会被触发
- 在创建 MV 表时不要使用 POPULATE 关键字，而是在 MV 表建好之后将数据手动导入 MV 表
- 在使用 MV 的聚合引擎时，也需要按照聚合查询来写 sql，因为聚合时机不可控
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70019616/viewspace-2905447/ ，如需转载，请注明出处，否则将追究法律责任。
