ClickHouse性能优化?试试物化视图

一、前言

  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为登录时间

mysql> 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. 查询统计结果

使用物化视图查询

mysql> 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,因为聚合时机不可控
原文地址:https://www.cnblogs.com/OrcinusOrca/p/14739109.html