MySQL 如何使用MySQL实现Materialized View?

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语句时才会计算结果。让我们通过一个例子来展示这两种概念之间的区别。

先创建以下三个表:

CREATE TABLE employee (
    id INT PRIMARY KEY, 
    name VARCHAR(50),
    department VARCHAR(50)
);

CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    location VARCHAR(50)
);

CREATE TABLE department_employee (
    employee_id INT, 
    department_id INT,
    PRIMARY KEY (employee_id, department_id),
    FOREIGN KEY (employee_id) REFERENCES employee(id),
    FOREIGN KEY (department_id) REFERENCES department(id)
);
SQL

现在,我们创建一个名为 emp_view 的视图。它将展示所有 department_employee,以及他们所在的 employeedepartment。这就是一般的视图:

CREATE OR REPLACE VIEW emp_view AS
SELECT 
    de.employee_id, 
    e.name AS employee_name,
    d.name AS department_name,
    d.location AS department_location
FROM department_employee de
JOIN employee e ON de.employee_id = e.id
JOIN department d ON de.department_id = d.id;
SQL

这里V只有在我们查询 emp_view 时才会计算结果。当我们执行以下查询时:

SELECT * FROM emp_view;
SQL

这个查询将执行之前创建的视图,并返回下面这个结果集:

+-------------+---------------+-----------------+----------------------+
| employee_id | employee_name | department_name | department_location |
+-------------+---------------+-----------------+----------------------+
|           1 | John Doe      | Sales           | San Francisco        |
|           2 | Jane Doe      | Engineering     | San Francisco        |
+-------------+---------------+-----------------+----------------------+
SQL

然后我们将上面的视图转换为 Materialized View。我们将使用一张名为 emp_mview 的物理表来代替视图。而 emp_view 视图每次查询时,都会查询这个物理表。

CREATE MATERIALIZED VIEW emp_mview AS
SELECT * FROM emp_view WITH NO DATA;
SQL

上面的命令将创建一个名为 emp_mview 的表来存储 emp_view 的结果。创建后,该表中将没有数据。使用以下命令手动填充它所需要的数据:

INSERT INTO emp_mview SELECT * FROM emp_view;
SQL

如果您要更新基础表(即employee,department和department_employee),则 Materialized View 将不会自动更新。相反,使用以下命令手动更新视图:

TRUNCATE TABLE emp_mview;
INSERT INTO emp_mview SELECT * FROM emp_view;
SQL

通过这些命令,我们完成了 Materialized View 的创建。现在我们来看一下如何实现 Materialized View。

实现Materialized View(MView)

Materialized View 在 MySQL 中有两种方法来完成。第一个方法是手动在 MySQL 中创建物理表,将视图结果插入到这个表中。第二个方法是使用 Flexviews 这个开源项目,它可以自动创建 Materialized View,并提供了许多高级特性。

方法一:手动创建

如前所述,手动创建 Materialized View 需要创建一个物理表,将视图的输出插入到该表中。下面是手动创建 Materialized View 的步骤:

  1. 首先,创建您的视图,确保您已经将列定义为必要的数据类型。
  2. 创建一个用于存储视图数据的 physical table(物理表),它必须具有与视图相同的列和数据类型。
  3. 在 MATERIALIZED VIEW 中添加 Trigger / Event,通过这个 Trigger / Event 来更新这个物理表。

注意,在 MySQL 5.7 以下的版本,您需要手动更新 Materialized View 中的数据。但在 MySQL 5.7 和 5.8 版本中,您可以使用 Trigger 来自动化这个过程。

以下是一个示例,演示如何手动创建 Materialized View。

假设我们有以下两个表:

CREATE TABLE sales (
    date DATE NOT NULL,
    region VARCHAR(50) NOT NULL,
    product VARCHAR(50) NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (date, region, product)
);

CREATE TABLE revenue (
    date DATE NOT NULL,
    region VARCHAR(50) NOT NULL,
    product VARCHAR(50) NOT NULL,
    revenue DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (date, region, product)
);
SQL

随后,我们创建一个名为 sales_revenue 的视图:

CREATE OR REPLACE VIEW sales_revenue AS
SELECT
    s.date,
    s.region,
    s.product,
    SUM(s.quantity * r.revenue) AS total_revenue
FROM sales s
JOIN revenue r ON (s.date = r.date AND s.region = r.region AND s.product = r.product)
GROUP BY s.date, s.region, s.product;
SQL

接下来,我们创建一个物理表,用于存储 Materialized View 的数据:

CREATE TABLE sales_revenue_mv (
    date DATE NOT NULL,
    region VARCHAR(50) NOT NULL,
    product VARCHAR(50) NOT NULL,
    total_revenue DECIMAL(10, 2)
);
SQL

在创建好物理表之后,我们需要通过以下 Trigger 来更新 sales_revenue_mv 表的数据:

CREATE EVENT sales_revenue_mv_event
ON SCHEDULE EVERY 1 DAY STARTS NOW()
DO
BEGIN
    TRUNCATE TABLE sales_revenue_mv;
    INSERT INTO sales_revenue_mv SELECT * FROM sales_revenue;
END;
SQL

以上 Trigger 将在每天的 0 点启动,将销量和收益视图的最新数据插入到 sales_revenue_mv 物理表中。

方法二:使用 Flexviews

使用 Flexviews 可以自动地为 MySQL 中的 Materialized View 创建物理表和相应的 Trigger。

下面是安装 Flexviews 的步骤:

  1. 首先,下载 Flexviews 的 tar 包并解压缩。
  2. 确保 MySQL 已经处于启动状态,并且具备 root 用户的操作权限。
  3. 执行以下命令:
tar -xf flexviews-<verison>.tar.gz
cd flexviews-<version>
./configure
make
sudo make install
SQL

启动 Flexviews 可以使用以下命令:

flexviews --user=<username> --password=<password> --host=<hostname> --port=<port> --database=<database_name>
SQL

Flexviews 将为 MySQL 中的 Materialized View 创建相应的 Trigger,并通过这些 Trigger 更新与 Materialized View 相关的物理表中的条目。您可以通过 Flexviews 的选项来配置更新的时间间隔、过滤和其他特性。

总结

Materialized View 是 MySQL 中一个很重要的概念,因为它可以大大提高查询性能,特别是当查询非常密集或要求很高时。使用 Materialized View 可以将查询时间从原来的秒级降低到毫秒级,这对数据分析、业务报表和决策制定等都是非常有帮助的。

在 MySQL 中,有两种方法可以实现 Materialized View。第一种方法是手动创建物理表,并使用事件和触发器来更新此表。这是一种相对简单的方法,但需要更多的工作来建立触发器并设置计划任务。第二种方法是使用 Flexviews 工具自动创建 Materialized View,并设置好触发器和时间更新等选项。这种方法更加方便,但需要更多的设置和配置。

最后,需要指出的是,虽然 Materialized View 在查询性能上有很大的优势,但也增加了存储和更新成本。在实际使用中,需要权衡利弊并选择最适合您的应用程序的方法。

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

登录

注册