MySQL视图之：`视图`与`表`的`区别`：其在`存储`、`索引`和`更新`上的差异。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 视图：`视图` 与 `表` 的 `区别`

大家好，今天我们来深入探讨 MySQL 视图，重点关注视图与表之间的差异，特别是它们在存储、索引和更新方面的区别。理解这些差异对于我们更好地设计数据库、优化查询和维护数据至关重要。

什么是视图？

在深入比较之前，我们先简单回顾一下视图的概念。视图本质上是一个虚拟表，其内容并非实际存储在数据库中，而是通过一个预定义的 SQL 查询（通常是 SELECT 语句）动态生成的。每次访问视图时，都会执行这个查询，并返回结果集。

存储差异

这是视图和表之间最根本的区别。

表：表是物理实体，数据以行和列的形式实际存储在磁盘上。每个表都占用一定的存储空间，并且需要维护索引、统计信息等元数据。
视图： 视图是逻辑实体，它不占用实际的存储空间。视图的定义存储在数据库的元数据中，仅包含用于生成结果集的 SQL 查询语句。当查询视图时，MySQL 引擎会解析视图的定义，执行相应的查询，并将结果返回给用户。

用一个简单的例子来说明：

假设我们有一个 employees 表和一个 departments 表：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Engineering');

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(101, 'Alice', 1, 50000.00),
(102, 'Bob', 2, 60000.00),
(103, 'Charlie', 1, 55000.00),
(104, 'David', 3, 70000.00),
(105, 'Eve', 3, 75000.00);

现在，我们创建一个视图，用于显示员工姓名和所属部门名称：

CREATE VIEW employee_department AS
SELECT
    e.employee_name,
    d.department_name
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id;

employee_department 视图本身并不存储任何数据。当查询这个视图时：

SELECT * FROM employee_department;

MySQL 引擎会执行视图定义中的 SELECT 语句，从 employees 和 departments 表中检索数据，并将结果返回。

总结： 表是物理存储，而视图是逻辑定义，不存储实际数据。

索引差异

索引是一种数据结构，用于加速数据的检索。它们可以显著提高查询性能，但同时也会增加存储空间和维护成本。

表：可以在表上创建索引，以加快对表中数据的查询速度。索引可以基于一个或多个列，并且可以选择不同的索引类型（如 B-tree 索引、哈希索引等）。
视图： 无法直接在视图上创建索引。因为视图本身不存储数据，所以没有地方可以存储索引。但是，MySQL 引擎在执行视图查询时，会尝试利用底层表上的索引来优化查询。

继续上面的例子，假设我们经常需要根据部门名称查询员工信息。我们可以在 departments 表的 department_name 列上创建一个索引：

CREATE INDEX idx_department_name ON departments (department_name);

当查询 employee_department 视图时，如果查询条件涉及部门名称，MySQL 引擎可能会使用 idx_department_name 索引来加快查询速度。例如：

SELECT * FROM employee_department WHERE department_name = 'Sales';

MySQL 优化器会分析这个查询，发现 department_name 列来自 departments 表，并且 departments 表上存在 idx_department_name 索引。因此，优化器可能会选择使用这个索引来快速定位 department_name 为 ‘Sales’ 的行。

物化视图：

一种特殊的视图类型是物化视图。与普通视图不同，物化视图会将查询结果存储在磁盘上，类似于一个表。因此，可以在物化视图上创建索引，以进一步提高查询性能。但是，物化视图需要定期刷新，以保持与底层表的数据同步。MySQL 8.0及以上版本支持物化视图。以下是一个物化视图的例子：

CREATE MATERIALIZED VIEW employee_department_materialized AS
SELECT
    e.employee_name,
    d.department_name
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id;

CREATE INDEX idx_department_name_mat ON employee_department_materialized (department_name);

需要定期刷新物化视图：

REFRESH MATERIALIZED VIEW employee_department_materialized;

总结： 普通视图不能直接创建索引，但是MySQL会尝试利用基表的索引优化查询。物化视图可以创建索引，但需要定期刷新。

更新差异

视图的可更新性是一个复杂的问题，取决于视图的定义和底层表。一般来说，只有满足特定条件的视图才是可更新的。

表：可以直接对表中的数据进行更新（INSERT、UPDATE、DELETE）。
视图： 并非所有视图都是可更新的。只有满足以下条件的视图通常才是可更新的：
- 视图必须基于单个表。
- 视图不能包含聚合函数（如 COUNT、SUM、AVG 等）。
- 视图不能包含 GROUP BY 或 HAVING 子句。
- 视图不能包含 DISTINCT 关键字。
- 视图不能包含 UNION、UNION ALL、INTERSECT 或 EXCEPT 操作。
- 视图不能包含子查询。
- 视图不能包含 FROM 子句中的多个表（连接）。

如果视图满足上述条件，则可以对其进行更新操作，这些操作会自动传播到底层表。

例如，我们可以更新 employees 表的工资，并通过视图进行更新：

CREATE VIEW employee_salary AS
SELECT
    employee_id,
    employee_name,
    salary
FROM
    employees;

UPDATE employee_salary SET salary = 52000.00 WHERE employee_name = 'Alice';

SELECT * FROM employees WHERE employee_name = 'Alice'; -- 可以看到 employees 表中的工资也更新了

但是，如果视图包含连接操作，则通常无法直接更新。例如，尝试更新 employee_department 视图：

UPDATE employee_department SET department_name = 'New Sales' WHERE employee_name = 'Alice'; -- 这通常会导致错误

这是因为 department_name 来自 departments 表，而 employee_name 来自 employees 表。MySQL 无法确定应该更新哪个表。

WITH CHECK OPTION 子句：

在创建可更新视图时，可以使用 WITH CHECK OPTION 子句来确保插入或更新的数据满足视图的定义。例如：

CREATE VIEW high_salary_employees AS
SELECT
    employee_id,
    employee_name,
    salary
FROM
    employees
WHERE
    salary > 60000
WITH CHECK OPTION;

INSERT INTO high_salary_employees (employee_id, employee_name, salary) VALUES (106, 'Frank', 55000.00); -- 这会导致错误，因为工资低于 60000

UPDATE high_salary_employees SET salary = 55000.00 WHERE employee_name = 'David'; -- 这也会导致错误，因为更新后的工资低于 60000

WITH CHECK OPTION 子句可以帮助我们维护数据的完整性，防止通过视图插入或更新不符合条件的数据。

总结： 表可以自由更新，但视图的可更新性受限。WITH CHECK OPTION可以增强视图的约束力。

视图的优点

虽然视图在存储、索引和更新方面存在一些限制，但它们仍然有很多优点：

简化复杂查询： 视图可以将复杂的查询封装起来，简化用户的访问。用户只需要查询视图，而不需要了解底层表的结构和关联关系。
提高数据安全性： 视图可以限制用户对底层表的访问权限。例如，可以创建一个只包含部分列的视图，并授予用户对该视图的访问权限，从而防止用户访问敏感数据。
提高数据一致性： 视图可以确保用户看到的数据始终是最新的。因为视图是动态生成的，所以每次查询都会执行视图的定义，从而反映底层表的最新状态。
逻辑数据独立性： 视图可以隐藏底层表的结构变化。如果底层表的结构发生变化，只需要修改视图的定义，而不需要修改使用视图的应用程序。

总结：选择表还是视图？

特性	表	视图
存储	物理存储，占用磁盘空间	逻辑定义，不占用存储空间
索引	可以创建索引	无法直接创建索引，但 MySQL 会尝试利用底层表的索引优化查询，物化视图例外。
更新	可以直接更新	可更新性受限，取决于视图的定义。
优点	存储数据，支持索引，灵活更新	简化查询、提高安全性、保证一致性、提供逻辑数据独立性
适用场景	需要存储数据的场景	简化复杂查询、控制数据访问权限、提供数据一致性、隐藏底层表结构变化的场景。

选择使用表还是视图，取决于具体的应用场景。如果需要存储数据，并且需要灵活地进行更新和查询，那么应该使用表。如果只需要简化复杂查询、控制数据访问权限、提供数据一致性或隐藏底层表结构变化，那么可以使用视图。在实际应用中，通常会将表和视图结合起来使用，以达到最佳的效果。

理解并合理使用视图

视图是一个非常有用的工具，可以帮助我们更好地管理和使用数据库。理解视图与表之间的差异，并根据实际需求选择合适的方案，是数据库设计和优化的关键。希望今天的讲解能够帮助大家更好地理解 MySQL 视图，并在实际工作中灵活运用。

MySQL 视图：视图 与 表 的 区别