MySQL 如何使用MySQL实现Materialized View?
在过去,MySQL 不支持 Materialized View 的概念,只能通过手动重建视图或使用 Trigger 进行更新。然而,现在在 MySQL 5.7 以后的版本,我们可以在 MySQL 中使用可用的技术来实现 Materialized View。本文将向您介绍如何使用 MySQL 实现 Materialized View。
阅读更多:MySQL 教程
什么是Materialized View?
Materialized View 是数据库中的概念,它是一张表,这张表存储已经计算好的结果。通俗来讲,这张表是由一张基础表“计算”得来的结果表,不同的是,这个结果表(MView)是一个物理存在的表,而视图(View)仅是一个虚拟的表。Materialized View 具有触发器,可以根据基础表的更改自动更新它的数据。
Materialized View与View的区别
Materialized View(以下简称MV)与View(以下简称V)的区别是关于存储数据是否经过实际计算之后得出的。MV 会自动更新,而 V 只有当您执行SELECT
语句时才会计算结果。让我们通过一个例子来展示这两种概念之间的区别。
先创建以下三个表:
现在,我们创建一个名为 emp_view
的视图。它将展示所有 department_employee
,以及他们所在的 employee
和 department
。这就是一般的视图:
这里V只有在我们查询 emp_view 时才会计算结果。当我们执行以下查询时:
这个查询将执行之前创建的视图,并返回下面这个结果集:
然后我们将上面的视图转换为 Materialized View。我们将使用一张名为 emp_mview
的物理表来代替视图。而 emp_view
视图每次查询时,都会查询这个物理表。
上面的命令将创建一个名为 emp_mview 的表来存储 emp_view 的结果。创建后,该表中将没有数据。使用以下命令手动填充它所需要的数据:
如果您要更新基础表(即employee,department和department_employee),则 Materialized View 将不会自动更新。相反,使用以下命令手动更新视图:
通过这些命令,我们完成了 Materialized View 的创建。现在我们来看一下如何实现 Materialized View。
实现Materialized View(MView)
Materialized View 在 MySQL 中有两种方法来完成。第一个方法是手动在 MySQL 中创建物理表,将视图结果插入到这个表中。第二个方法是使用 Flexviews 这个开源项目,它可以自动创建 Materialized View,并提供了许多高级特性。
方法一:手动创建
如前所述,手动创建 Materialized View 需要创建一个物理表,将视图的输出插入到该表中。下面是手动创建 Materialized View 的步骤:
- 首先,创建您的视图,确保您已经将列定义为必要的数据类型。
- 创建一个用于存储视图数据的 physical table(物理表),它必须具有与视图相同的列和数据类型。
- 在 MATERIALIZED VIEW 中添加 Trigger / Event,通过这个 Trigger / Event 来更新这个物理表。
注意,在 MySQL 5.7 以下的版本,您需要手动更新 Materialized View 中的数据。但在 MySQL 5.7 和 5.8 版本中,您可以使用 Trigger 来自动化这个过程。
以下是一个示例,演示如何手动创建 Materialized View。
假设我们有以下两个表:
随后,我们创建一个名为 sales_revenue
的视图:
接下来,我们创建一个物理表,用于存储 Materialized View 的数据:
在创建好物理表之后,我们需要通过以下 Trigger 来更新 sales_revenue_mv 表的数据:
以上 Trigger 将在每天的 0 点启动,将销量和收益视图的最新数据插入到 sales_revenue_mv 物理表中。
方法二:使用 Flexviews
使用 Flexviews 可以自动地为 MySQL 中的 Materialized View 创建物理表和相应的 Trigger。
下面是安装 Flexviews 的步骤:
- 首先,下载 Flexviews 的 tar 包并解压缩。
- 确保 MySQL 已经处于启动状态,并且具备 root 用户的操作权限。
- 执行以下命令:
启动 Flexviews 可以使用以下命令:
Flexviews 将为 MySQL 中的 Materialized View 创建相应的 Trigger,并通过这些 Trigger 更新与 Materialized View 相关的物理表中的条目。您可以通过 Flexviews 的选项来配置更新的时间间隔、过滤和其他特性。
总结
Materialized View 是 MySQL 中一个很重要的概念,因为它可以大大提高查询性能,特别是当查询非常密集或要求很高时。使用 Materialized View 可以将查询时间从原来的秒级降低到毫秒级,这对数据分析、业务报表和决策制定等都是非常有帮助的。
在 MySQL 中,有两种方法可以实现 Materialized View。第一种方法是手动创建物理表,并使用事件和触发器来更新此表。这是一种相对简单的方法,但需要更多的工作来建立触发器并设置计划任务。第二种方法是使用 Flexviews 工具自动创建 Materialized View,并设置好触发器和时间更新等选项。这种方法更加方便,但需要更多的设置和配置。
最后,需要指出的是,虽然 Materialized View 在查询性能上有很大的优势,但也增加了存储和更新成本。在实际使用中,需要权衡利弊并选择最适合您的应用程序的方法。