物化视图

什么是物化视图

MySQL中，视图实际是将一个查询语句的执行逻辑记下来，以后使用视图中的数据时，先按照记录的执行逻辑得到结果，再将结果用于新的查询计算。

普通的视图时不会保存数据的，仅保存查询语句，也就是计算逻辑。而物化视图会将查询的结果根据相应的引擎存入磁盘或内存中，所以可以理解物化视图就是一张表(的确是一张表，当指定表名时会将数据存在指定的表中，不指定表名也会自动生成一张.inner.表名的表保存数据)

物化视图的优缺点

• 优点：查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总的行数少了，因为都预计算好了。
• 缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源， 比如数据带宽占满、 存储一下子增加了很多

如何创建物化视图

物化视图的创建语法和标准视图很类似

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name]
[ENGINE = engine] [POPULATE] AS SELECT ...

这里解释下各个可选参数的含义：

• MATERIALIZED：物化视图，不设置的话就是普通视图
• [TO[db.]name]：指定存储表名，不指定的话会自动生成.inner.表名的表存储数据。这里需要注意，指定的表需要已存在(表结构需要和后面select的字段对应)，同时由于指定了已存在的表，所以就不能再指定表引擎配置
• 如果不指定 [TO[db.]name]，则必须指定表引擎，说明数据存储方式
• POPULATE：可以理解为在创建物化视图时，是否执行后面的select语句并将结果放入新生成的物化视图中，如果不指定该配置，新生成的物化视图中没有数据(这种情况下一般会手动向物化视图中插入数据)
• 物化视图的 alter 操作有些限制，操作起来不大方便

这里需要注意：clickhouse 官方并不推荐使用 POPULATE，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图。

当物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新
物化视图不支持同步删除，若源表的数据不存在（删除了）则物化视图的数据仍然保留

MySQL物化引擎

MySQL物化引擎在中20.8.2.3版本新增，之前为试验功能；
MySQL物化引擎实际上是一个库引擎（目前只支持库级别，不支持表级别），前面有提到，ClickHouse的引擎可以决定数据的存储位置以及读取写入方式，而这里的MaterializeMySQL引擎就是将数据存储在MySQL中，ClickHouse中的库可以近似看作MySQL库的映射；

ClickHouse中的使用MaterializeMySQL库引擎的库下表会与MySQL中的库建立连接，实时同步MySQL库下的数据信息，包括表的新增和删除，也包括数据的增删改操作。

对于MySQL来说数据的增删改是直接进行修改的，在client上不会体现修改，而ClickHouse库下的对应表会记录这些变化，同样的一张表，在ClickHouse下会多出两个_sign和_version字段，_sign字段空值当前行数据是否被删除(1有效，-1被删除)，_version为数据版本号，当同一条数据被update或者delete时，ClickHouse中的表不会真的删除或修改这条数据，而是将插入一条新的数据，将原本数据的_sign字段设为删除，同时新记录的_version增加1。
这些操作保证了MaterializeMySQL可以做到与OLTP数据库保证实时同步（数据的修改立即生效），而且可以捕捉到数据的每个变化，做到了OLTP和OLAP数据库的融合。

MaterializeMySQL库创建

要创建MaterializeMySQL库，首先要有对应的MySQL数据库，首先MySQL需要支持对应功能，编辑/etc/my.cnf文件，增加以下配置

# 确保 MySQL 开启了 binlog 功能，且格式为 ROW
server-id=1
log-bin=mysql-bin
binlog_format=ROW

同时，如果MySQL有主从配置的话，可以考虑增加以下配置：

gtid-mode=on
enforce-gtid-consistency=1 # 设置为主从强一致性
log-slave-update=1 # 记录日志
default_authentication_plugin=mysql_native_password # 没有这个CK建库会失败

以上配置修改完成后，重启mysql，并进入mysql；

-- 首先查看下这个变量，需要值为mysql_native_password，如果不是可以在配置文件或命令行修改
show global variables like '%default_authentication_plugin%';

default_authentication_plugin变量如果是其他模式，会导致ClickHouse无法正常同步MySQL数据，导致建库失败；
部分mysql版本可能没有这个变量

此时如果在ClickHouse建库也会失败，个人暂未找到解决方案，尝试其他版本可以成功建立映射

解决配置问题后，可以在MySQL下创建要映射的数据库，并创建几个测试表：

-- 创建测试库
CREATE DATABASE testck;

-- 创建第一张测试表
CREATE TABLE `testck`.`t_organization` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int NOT NULL,
`name` text DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`code`)
) ENGINE=InnoDB;
-- 插入测试数据
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1000,'Realinsight',NOW());
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1001, 'Realindex',NOW());
INSERT INTO testck.t_organization (code, name,updatetime)
VALUES(1002,'EDT',NOW());

-- 创建第二张测试表
CREATE TABLE `testck`.`t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` int,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
-- 插入测试数据
INSERT INTO testck.t_user (code) VALUES(1);

MySQL这边准备好之后，打开ClickHouse

-- 由于当前版本MaterializeMySQL功能仍为测试功能，需要先打开配置
set allow_experimental_database_materialize_mysql=1;
-- 配置开启后就可以建库了
CREATE DATABASE test_binlog ENGINE =
MaterializeMySQL('192.168.226.140:3306','testck','root','123456');
-- 这里指定的四个参数分别为mysql连接ip及端口、库名、用户名和密码

建库成功之后，就可以看到库下面的两张表了

同时建库时，也可以看到MySQL库下同样的数据

此时在MySQL中增删改数据，ClickHouse中也可以获得实时同步，这里就不一一展示了，下面看下_sign和_version两个字段的效果

-- 首先在MySQL中新增、修改和删除各执行一次
update testck.t_organization set name='abcdef' where id=1;
delete from testck.t_organization where id=2;
insert into testck.t_organization values(4,1010,'WSL_KJDS_HZSL','2021-12-12 03:22:56');

此时如果正常查询，会得到与在MySQL中一样的结果

但是当带上_sign和_version字段后，就可以查看到历史变动的情况

对于删除的数据，ClickHouse中会新插入一条与被删除数据相同的数据，并在新数据中将_sign字段置为-1；
对于修改的数据，ClickHouse中也会新插入一条数据，修改对应字段后，将_version增加，覆盖掉原本的数据；
而对于新增的数据，ClickHouse中也会正常新增

如果在MySQL中删除表

drop table testck.t_user;

ClickHouse中也会对应删除

最后

以上就是忧郁灯泡为你收集整理的ClickHouse-8（物化视图和MaterializeMySQL）物化视图MySQL物化引擎的全部内容，希望文章能够帮你解决ClickHouse-8（物化视图和MaterializeMySQL）物化视图MySQL物化引擎所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。