你想要的-提高统计clickhouse的查询效率，clickhouse物化视图的应用-clickhouse实战-利志分享

你想要的-提高统计clickhouse的查询效率，clickhouse物化视图的应用

阅读：355 分享次数：0

做数据分析的同学总有会吐槽的
1：为什么我写个sql放到线上去执行就这么慢呢？能不能快点出结果？
2：能不能把这几个表组合成一个大宽表让我只做一个表的查询，不用手动连那么多表？
.....

相信很多做数据开发的同学总会听到这样的吐槽，哈哈，怎么解决这种问题是数据开发同学头痛问题。

先我们来讲一个案例，在我们的业务中，注册是不需要手机号，注册之后会填写个人信息，然后呢，我们要统计某个年龄段绑定手机的留存。这样我们上报会产生三个事件，然后这样的话我们在数据表里面就会有三张表注册表（user）,绑定手机表（bind）,用户信息表（user_info），这样分析师如果要计算某个年龄段绑定手机的留存，需要连三个表，像我们的用户都是亿级，join查询起来三个表都是比较好资源，然后我们就想把三个表通过join方法组合成一个大宽表。下面我之前想到的是有两种方案：
方案一：通过脚本定时查询数据把数据汇总到一个表里面
方案二：利用物化视图来解决，但是发现好像还是得配合脚本处理，因为物化视图有个问题就是左边驱动，如果其他表变化是不会更新物化视图的数据。

我这里就细化物化视图这种解决方案。

物化视图概念：
我们都知道，数据库中的视图（view）是从一张或多张数据库表查询导出的虚拟表，反映基础表中数据的变化，且本身不存储数据。然而物化视图是查询结果集的一份持久化存储，所以它与普通视图完全不同，而非常趋近于表。“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表join之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照（snapshot）。如果要更新数据的话，需要用户手动进行，如周期性执行SQL，或利用触发器等机制。

下面我来个例子看看物化视图使用的例子：
建立三个表：

CREATE TABLE user( user_id UInt16,phone String,name String,create_time DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY (user_id) SETTINGS index_granularity = 819;
    CREATE TABLE bind( user_id UInt16,phone String,create_time DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY (user_id) SETTINGS index_granularity = 819;
    CREATE TABLE user_info( user_id UInt16,sig String,node String,create_time DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY (user_id) SETTINGS index_granularity = 819;
	
插入一些数据：

insert into user values(1,'18475484454','张三',now()),(2,'18465484454','李四',now()),(3,'','王五',now()),(4,'','小狗',now())
    insert into bind values(1,'18475484454',now()),(3,'18475484457',now())
    insert into user_info values(1,'我的地盘我做主','我的签名',now()),(3,'我的地盘我做主2','我的签名2',now())
	
	
然后我建个物化视图，我发现建物化视图的坑还是挺多，我这里贴一下我的错误写法和正确写法，我用的clickhouse的版本是21.2.5.5。

错误写法：不能加别名，不然识别不

1：CREATE MATERIALIZED VIEW big_user ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) POPULATE AS select a.user_id user_id,a.name name,a.create_time create_time,b.phone phone,c.sig sig,c.node node from user a left join bind b on a.user_id = b.user_id left join user_info c on a.user_id = c.user_id
    2：CREATE MATERIALIZED VIEW big_user ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) POPULATE AS select a.user_id user_id,a.name name,a.create_time create_time,b.phone phone,c.sig sig,c.node node from user as a left join bind as b on a.user_id = b.user_id left join user_info as c on a.user_id = c.user_id
	
正确写法：

CREATE MATERIALIZED VIEW big_user ENGINE = MergeTree() PARTITION BY toYYYYMM(user.create_time) ORDER BY (user.user_id) POPULATE AS select user.user_id,user.name,user.create_time,bind.phone,user_info.sig,user_info.node from user left join bind on user.user_id = bind.user_id left join user_info on user.user_id = user_info.user_id
	
到这里我的物化视图建立成功了，我们来看下数据执行：

select * from big_user;
	
刚开始是没有user_id为6的，然后我执行了

insert into user values(6,'18475484454','张三',now())
这个sql之后，就出现下面的图了
![](https://zengzhihai.com/upload_images/111.png)

后面我又执行了一条插入的数据，结果还是没变，这个是因为物化视图的驱动是目前是单标驱动。

insert into bind values(6,'19975484454',now())
![](https://zengzhihai.com/upload_images/111.png)

好了，上面说了使用clickhouse的物化视图的使用，还讲了处理成大宽表的两种方案，如果有更好的方案的同学也欢迎一起交流。

总结：
1：如果物化视图是由两表join产生的，那么物化视图仅有在左表插入数据时才更新。如果只有右表插入数据，则不更新。
2：建立物化视图的时候，POPULATE字段的使用得注意，如果线上很多数据，插入比较多，正在插入的时候表的数据是不会更新到物化视图表里面的，会存在丢数据。

感觉本站内容不错，读后有收获？

我要小额打赏，鼓励作者写出更好的教程

扫码关注公众号：talk_lizhi