深入了解MySQL ClickHouse中的物化视图功能寻技术

数据表与视图

数据库表是一种关系型数据库中的基本对象，用于存储数据。每个表包含多个列和行，其中每个列代表一种数据类型，每一行则表示一条记录
视图是一种虚拟的表格，它并不实际存在于数据库中，而是通过一个SQL查询语句定义。视图在数据库管理中具有重要作用，在实践中也是非常常见的。
视图可以从一个或多个表中选择部分列或行作为数据显示，并提供了访问数据的一种灵活方式
与表格不同的是，视图不能存储数据，但对于一些常用的查询操作，却能大大简化SQL操作
通过创建视图，可以大大简化复杂的查询，同时也可以保持数据独立性，提高数据的安全性
视图可以为数据提供安全保护，限制用户对特定数据的访问权限。例如，你的系统要和另一个系统对接，对方要求访问某些数据，又不想搞接口对接；此时就可以根据对方的需要，建几个视图，再给对方开个只读这几个视图的低权限用户；这样既满足对方查询需求，又不会被对方读取或修改到其他数据。

ClickHouse的物化视图

像上面说的，视图并不存储任何数据，而是通过查询操作动态生成数据。它可以帮助用户在数据表上创建一层抽象，获得一些更加方便和易于维护的业务逻辑
ClickHouse是一个快速的列式数据库管理系统，ClickHouse的视图也是一种虚拟的表，其内容由一个查询定义
ClickHouse的普通视图（view），和其他数据库一样，没有缓存数据，每次查询都需要重新生成数据
但是ClickHouse还支持一种物化视图（Materialized View），字面意义理解，就是实际去物理存储视图
物化视图是一种特殊的视图，它会在创建的时候自动执行查询，并将结果保存到一张实际的表格中。在查询物化视图时，可以直接使用保存的结果进行查询，而无需重新执行查询语句，从而显著提高查询效率
简单来说，就是“空间换时间”，提前查询好存储起来，占用了一定的空间，当查询时直接拿来使用，因此在使用时需要权衡查询效率与存储空间的使用。

物化视图的更新

ClickHouse的物化视图（Materialized View）是一种特殊的表，它能够在底层数据更新后，自动更新自己的数据。数据更新包括两个方面的变化：基础表的数据修改和基础表的数据新增。

基础表的数据修改

如果基础表的数据修改，物化视图会自动更新。这是通过ClickHouse的引擎和存储方式来实现的。当基础表的一行记录被修改，ClickHouse会将这个修改转化为一个新的INSERT语句，并且将其发送到物化视图中。这样，物化视图就能够自动更新自己的数据。

基础表的数据新增

如果基础表的数据新增，物化视图同样会自动更新。这是通过设置物化视图的刷新机制来实现的。刷新机制有两种类型：定时刷新和自动刷新。

（1）定时刷新

定时刷新需要使用MATERIALIZED VIEW的REFRESH INTERVAL语法来指定刷新时间间隔。例如，以下语句表示每5分钟自动刷新物化视图：

CREATE MATERIALIZED VIEW my_mv
engine = MergeTree
AS SELECT * FROM my_table
SETTINGS refresh_interval = 300

（2）自动刷新

自动刷新可以通过在基础表上创建触发器实现。当基础表有数据插入时，触发器会自动插入相应的数据到物化视图中。例如，以下语句创建了一个触发器，当my_table中插入一条新数据时，相应的数据将插入到my_mv中：

CREATE TRIGGER insert_trigger ON my_table FOR INSERT
AS INSERT INTO my_mv SELECT * FROM my_table WHERE id = NEW.id

另外，我们也可以手动刷新物化视图，甚至可以使用一些定时手段，实时或定时去触发它。当然，我们要考虑资源占用和性能损耗。

REFRESH MATERIALIZED VIEW xxx;

需要注意的是，ClickHouse的物化视图虽然能够自动更新数据，但是会带来一些性能上的损失，尤其是在基础表数据量较大的情况下。因此，在设计物化视图时，需要考虑这个因素，同时选择合适的刷新机制来平衡性能和数据实时性的需求。

当然，我们既然选择使用ClickHouse，肯定是数据新增比较多，而极少去修改删除。对于一般对实时要求不高的业务场景，定时刷新完全足够我们使用了。

使用示例

在ClickHouse中，创建视图的语法为：

CREATE VIEW [IF NOT EXISTS] name [ON CLUSTER cluster]
( SELECT ... )

其中，name为视图的名称，SELECT语句为需要执行的查询语句。创建视图之后，可以使用SELECT语句查询视图的内容。

在ClickHouse中，创建物化视图的语法为：

CREATE MATERIALIZED VIEW [IF NOT EXISTS] name [ON CLUSTER cluster] TO [db.]table [ENGINE = engine] [POPULATE] AS SELECT ...

其中，name为物化视图的名称，[db.]table为保存结果的表格名称，SELECT语句为需要执行的查询语句。在创建物化视图时，可以选择是否执行第一次查询，并将结果保存到表格中。后续查询时，可以直接使用表格中保存的结果进行查询，从而提高查询效率。

像一些其他参数，例如PARTITION BY、ORDER BY等也可以在创建物化视图时使用，和创建表的使用方法一样，也可以创建完物化视图后再去更新设置
下面给出一个普通物化视图的完整示例。假设我们有一个表名为example，并且我们想创建一个名为example_mv的物化视图，refresh_interval设置为5分钟，以便定时更新数据。以下是创建该视图的示例SQL语句：

CREATE MATERIALIZED VIEW example_mv 
ENGINE = MergeTree()
AS 
SELECT
    Column1,
    Column2,
    ...
FROM example
-- 设置刷新间隔为5分钟
SETTINGS refresh_interval = 300

本文地址：https://www.xunjs.com/mysql/view_11033.html

Tags：物化图功视图了解深入功能 mysql

数据表与视图

ClickHouse的物化视图

物化视图的更新

使用示例

相关推荐