第二十四章视图

本章目标

通过本章学习，你将能够：

理解视图的本质：一个存储的命名查询（虚拟表）
掌握创建、修改、删除视图的语法
理解可更新视图的限制条件以及 WITH CHECK OPTION 的作用
利用视图实现安全性（列级权限控制）和简化复杂查询
了解视图的三种算法（MERGE、TEMPTABLE、UNDEFINED）及其对性能的影响
识别视图不适合的使用场景（性能错觉、过度嵌套等）
使用视图实现向后兼容和 API 层的概念

前置知识

在学习本章之前，你需要：

熟练掌握 SELECT 查询语法（包括 JOIN、子查询、WHERE、GROUP BY 等）
理解基本的 DDL 操作（CREATE、ALTER、DROP）
了解 MySQL 的权限管理基本概念（GRANT、REVOKE）
理解查询优化的基本概念（索引、执行计划）

24.1 视图的定义与类比

24.1.1 什么是视图

视图（View）是一个基于 SELECT 查询的虚拟表。它不存储数据本身，只在查询视图时动态执行底层的 SELECT 语句。

sql

-- 类比：视图就像电脑上的"快捷方式"
-- 快捷方式不是原始文件，但双击它可以打开原始文件
-- 视图不是物理表，但查询视图可以得到表的数据

-- 视图 ≠ 物理表（不存储数据，每次查询时重新执行）
-- 物化视图 ≠ 物理表（存储数据，定期刷新）—— MySQL 不原生支持

24.1.2 视图的直观示例

sql

-- 假设有一个复杂的查询需要频繁使用
SELECT u.id, u.name, COUNT(o.id) AS order_cnt, SUM(o.amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.name;

-- 每次都写这么长的查询，容易出错且难以维护

-- 使用视图封装
CREATE VIEW user_order_summary AS
SELECT u.id, u.name, COUNT(o.id) AS order_cnt, SUM(o.amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.name;

-- 之后只需简单查询视图
SELECT * FROM user_order_summary WHERE order_cnt > 10;
SELECT * FROM user_order_summary ORDER BY total_spent DESC LIMIT 20;
-- 视图就像一个预先定义好的"查询模板"

24.2 创建和管理视图

24.2.1 CREATE VIEW

sql

-- 基本语法
CREATE [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY {DEFINER | INVOKER}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION];

-- 最简单的视图
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- 使用视图
SELECT * FROM active_users;
SELECT name, email FROM active_users WHERE id > 100;

24.2.2 指定视图列名

sql

-- 方式一：列名继承自 SELECT 中的列
CREATE VIEW user_basic AS
SELECT id, name, email FROM users;

-- 方式二：显式指定视图列名
CREATE VIEW user_basic_v2 (user_id, user_name, user_email) AS
SELECT id, name, email FROM users;

-- 当 SELECT 中包含表达式或函数时，必须显式指定列名
CREATE VIEW order_stats (order_id, total_with_tax) AS
SELECT id, amount * 1.13 FROM orders;
-- 如果不指定列名，列名会是 amount * 1.13（不友好）

-- 更好的做法：在 SELECT 中使用别名
CREATE VIEW order_stats_v2 AS
SELECT id AS order_id, amount * 1.13 AS total_with_tax
FROM orders;

24.2.3 CREATE OR REPLACE VIEW

如果视图已存在则替换，不存在则创建。等价于 DROP VIEW + CREATE VIEW 的原子操作。

sql

-- 创建或替换视图
CREATE OR REPLACE VIEW top_products AS
SELECT product_id, SUM(quantity) AS total_sold
FROM order_items
GROUP BY product_id
HAVING total_sold > 100;

-- 修改视图的 SELECT 逻辑（增加排序）
CREATE OR REPLACE VIEW top_products AS
SELECT product_id, SUM(quantity) AS total_sold
FROM order_items
GROUP BY product_id
HAVING total_sold > 100
ORDER BY total_sold DESC;

-- 没有 OR REPLACE 时，如果视图已存在则会报错
-- CREATE VIEW top_products AS ...;  -- Error: Table 'top_products' already exists

24.2.4 ALTER VIEW

ALTER VIEW 与 CREATE OR REPLACE VIEW 完全相同，提供纯粹的语法兼容性。

sql

-- ALTER VIEW 修改视图定义
ALTER VIEW user_order_summary AS
SELECT u.id, u.name, u.email, COUNT(o.id) AS order_cnt, SUM(o.amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.name, u.email;
-- 等价于 CREATE OR REPLACE VIEW ...

24.2.5 查看和删除视图

sql

-- 查看视图定义
SHOW CREATE VIEW user_order_summary;

-- 查看数据库中的所有视图
SHOW FULL TABLES WHERE Table_type = 'VIEW';

-- 从 information_schema 查询视图信息
SELECT TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'mydb';

-- 删除视图
DROP VIEW [IF EXISTS] view_name;
DROP VIEW IF EXISTS user_order_summary;

-- 同时删除多个视图
DROP VIEW IF EXISTS view1, view2, view3;

24.3 可更新视图

24.3.1 什么是可更新视图

可更新视图是指可以通过视图执行 INSERT、UPDATE、DELETE 操作，操作最终会反映到底层基表中。

sql

-- 创建可更新视图
CREATE VIEW active_users_view AS
SELECT id, name, email, status
FROM users
WHERE status = 'active';

-- 通过视图插入数据
INSERT INTO active_users_view (name, email, status)
VALUES ('Bob', 'bob@example.com', 'active');
-- 底层：INSERT INTO users (name, email, status) VALUES ('Bob', 'bob@example.com', 'active');

-- 通过视图更新数据
UPDATE active_users_view SET email = 'new@example.com' WHERE id = 1;
-- 底层：UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 通过视图删除数据
DELETE FROM active_users_view WHERE id = 1;
-- 底层：DELETE FROM users WHERE id = 1;

24.3.2 可更新视图的限制

要成为可更新视图，SELECT 语句必须满足以下条件：

sql

-- 可更新视图必须满足：
-- 1. 引用且只引用一个基表（不能有 JOIN）
-- 2. 不能包含 DISTINCT
-- 3. 不能包含 GROUP BY 或 HAVING
-- 4. 不能包含 UNION / UNION ALL
-- 5. 不能包含子查询（在 SELECT 列表中）
-- 6. 不能包含聚合函数（SUM, AVG, COUNT, MAX, MIN 等）
-- 7. 不能包含窗口函数
-- 8. 不能包含 FROM 子句中的子查询（派生表）

-- 不可更新视图示例
CREATE VIEW not_updatable_1 AS
SELECT u.name, COUNT(o.id) AS cnt
FROM users u JOIN orders o ON u.id = o.user_id
GROUP BY u.name;  -- 有 JOIN + GROUP BY → 不可更新

CREATE VIEW not_updatable_2 AS
SELECT DISTINCT status FROM orders;  -- 有 DISTINCT → 不可更新

CREATE VIEW not_updatable_3 AS
SELECT id, name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS cnt
FROM users u;  -- SELECT 列表中有子查询 → 不可更新

-- 检查视图是否可更新
SELECT TABLE_NAME, IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = 'mydb';

24.3.3 WITH CHECK OPTION

WITH CHECK OPTION 确保通过视图插入或更新的数据仍然满足视图的 WHERE 条件。

sql

-- 创建视图：只展示 status='active' 的用户
CREATE VIEW active_users_view AS
SELECT id, name, email, status
FROM users
WHERE status = 'active'
WITH CHECK OPTION;

-- 通过视图插入 status='active' 的记录 → 成功
INSERT INTO active_users_view (name, email, status)
VALUES ('Charlie', 'charlie@test.com', 'active');  -- OK

-- 通过视图插入 status='inactive' 的记录 → 失败！
INSERT INTO active_users_view (name, email, status)
VALUES ('David', 'david@test.com', 'inactive');
-- ERROR: CHECK OPTION failed 'mydb.active_users_view'

-- 更新视图中记录的 status 为非 active → 失败！
UPDATE active_users_view SET status = 'inactive' WHERE id = 1;
-- ERROR: CHECK OPTION failed 'mydb.active_users_view'

24.3.4 CASCADED 和 LOCAL

sql

-- WITH CASCADED CHECK OPTION（默认）
-- 检查当前视图 AND 所有底层视图的条件

-- 场景：视图 A 基于视图 B
CREATE VIEW view_b AS
SELECT * FROM products WHERE price > 0;

CREATE VIEW view_a AS
SELECT * FROM view_b WHERE category = 'electronics'
WITH CASCADED CHECK OPTION;  -- CASCADED 是默认值

-- 通过 view_a 插入时：
-- 必须同时满足 view_a 的条件 (category='electronics') AND view_b 的条件 (price>0)

-- WITH LOCAL CHECK OPTION
-- 只检查当前视图的条件，不检查底层视图

CREATE VIEW view_a_local AS
SELECT * FROM view_b WHERE category = 'electronics'
WITH LOCAL CHECK OPTION;

-- 通过 view_a_local 插入时：
-- 只检查 category='electronics'，不检查 price>0

24.4 视图算法（Execution Algorithm）

sql

-- ALGORITHM = MERGE
-- MySQL 将视图的 SELECT 合并到外部查询中，一起优化
-- 类似于 C 语言中的宏展开

-- 示例
CREATE ALGORITHM = MERGE VIEW active_users AS
SELECT id, name FROM users WHERE status = 'active';

SELECT * FROM active_users WHERE name LIKE 'A%';
-- MERGE 后等价于：
SELECT id, name FROM users WHERE status = 'active' AND name LIKE 'A%';
-- 两个条件一起优化，可以利用 idx_status_name 索引

-- ALGORITHM = TEMPTABLE
-- MySQL 先执行视图的 SELECT，将结果存入临时表，再对临时表执行外部查询

CREATE ALGORITHM = TEMPTABLE VIEW order_summary AS
SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders GROUP BY user_id;

SELECT * FROM order_summary WHERE total > 1000;
-- TEMPTABLE 执行流程：
-- 1. 先执行 GROUP BY 聚合，结果存入临时表
-- 2. 从临时表中筛选 total > 1000
-- 外部 WHERE 条件无法下推到视图内部

-- ALGORITHM = UNDEFINED（默认）
-- MySQL 自动选择 MERGE 或 TEMPTABLE
-- 通常：能 MERGE 则 MERGE，否则 TEMPTABLE（如包含 GROUP BY、DISTINCT 等）

三种算法对比：

算法	执行方式	特点	适用场景
MERGE	合并到外部查询	外部 WHERE 可下推，利用索引	简单查询无聚合
TEMPTABLE	先物化结果	外部 WHERE 无法下推	聚合、DISTINCT、UNION 等
UNDEFINED	自动选择	默认行为，MySQL 智能选择	大多数场景

24.5 视图的应用场景

24.5.1 简化复杂查询

sql

-- 原始复杂查询
SELECT
    u.id, u.name,
    COUNT(DISTINCT o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent,
    MAX(o.created_at) AS last_order_date,
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order,
    CASE
        WHEN COALESCE(SUM(o.amount), 0) > 10000 THEN 'VIP'
        WHEN COALESCE(SUM(o.amount), 0) > 1000 THEN 'Gold'
        ELSE 'Normal'
    END AS tier
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.name;

-- 创建视图后，查询变得极其简单
CREATE VIEW user_insights AS
SELECT
    u.id AS user_id, u.name,
    COUNT(DISTINCT o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent,
    MAX(o.created_at) AS last_order_date,
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order,
    CASE
        WHEN COALESCE(SUM(o.amount), 0) > 10000 THEN 'VIP'
        WHEN COALESCE(SUM(o.amount), 0) > 1000 THEN 'Gold'
        ELSE 'Normal'
    END AS tier
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.name;

-- 使用视图
SELECT * FROM user_insights WHERE tier = 'VIP';
SELECT AVG(order_count) FROM user_insights WHERE days_since_last_order < 30;
SELECT tier, COUNT(*) AS cnt FROM user_insights GROUP BY tier;

24.5.2 安全性：列级访问控制

sql

-- 场景：用户表包含敏感字段（password_hash, salary, ssn 等）
-- 使用视图限制不同角色的可见列

-- 公众可访问的用户信息视图（隐藏敏感字段）
CREATE VIEW public_user_info AS
SELECT id, username, nickname, avatar_url, created_at
FROM users;

-- 管理员可访问的完整视图（但隐藏密码哈希）
CREATE VIEW admin_user_info AS
SELECT id, username, email, phone, status, role, created_at, updated_at
FROM users;

-- 然后通过 GRANT 控制访问权限
GRANT SELECT ON public_user_info TO 'app_user'@'%';
GRANT SELECT ON admin_user_info TO 'admin_role'@'%';
REVOKE ALL ON users FROM 'app_user'@'%';  -- 不给基表权限

24.5.3 向后兼容

sql

-- 场景：数据库重构，users 表拆分为 users 和 user_profiles
-- 旧表：users(id, name, email, phone, address, bio, avatar)
-- 新表：users(id, name, email) + user_profiles(user_id, phone, address, bio, avatar)

-- 创建视图模拟旧表结构，保持应用代码兼容
CREATE VIEW users_legacy AS
SELECT u.id, u.name, u.email,
       up.phone, up.address, up.bio, up.avatar
FROM users u
LEFT JOIN user_profiles up ON u.id = up.user_id;

-- 旧的应用代码 SELECT * FROM users 改为 SELECT * FROM users_legacy
-- 无需修改业务逻辑代码

24.5.4 数据聚合与报表

sql

-- 日报表视图
CREATE VIEW daily_sales_report AS
SELECT
    DATE(created_at) AS sale_date,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE status IN ('paid', 'shipped', 'delivered')
GROUP BY DATE(created_at)
ORDER BY sale_date DESC;

-- 查询报表
SELECT * FROM daily_sales_report WHERE sale_date >= CURDATE() - INTERVAL 30 DAY;
SELECT sale_date, total_revenue FROM daily_sales_report WHERE MONTH(sale_date) = 6;

24.6 视图的局限性

24.6.1 性能误区

sql

-- 误区：视图能让查询变快
-- 事实：视图不存储数据，每次查询视图都重新执行底层的 SELECT

-- 示例：以下视图包含复杂 JOIN
CREATE VIEW complex_report AS
SELECT ...
FROM t1
JOIN t2 ON ...
JOIN t3 ON ...
WHERE ...;

-- 查询视图时：
SELECT * FROM complex_report WHERE condition;
-- 实际执行的是完整的 JOIN 查询 + WHERE 条件
-- 视图不会缓存或预计算结果

24.6.2 物化视图的模拟

MySQL 不原生支持物化视图（Materialized View），但可以通过以下方式模拟：

sql

-- 方案一：手动维护汇总表 + 定时刷新
CREATE TABLE daily_sales_summary (
    sale_date DATE PRIMARY KEY,
    order_count INT,
    total_revenue DECIMAL(12, 2),
    updated_at DATETIME
);

-- 通过事件调度器定期刷新
CREATE EVENT refresh_daily_summary
ON SCHEDULE EVERY 1 HOUR
DO
    INSERT INTO daily_sales_summary (sale_date, order_count, total_revenue, updated_at)
    SELECT DATE(created_at), COUNT(*), SUM(amount), NOW()
    FROM orders
    WHERE DATE(created_at) = CURDATE()
    ON DUPLICATE KEY UPDATE
        order_count = VALUES(order_count),
        total_revenue = VALUES(total_revenue),
        updated_at = VALUES(updated_at);

-- 方案二：使用触发器增量更新（适用于小数据量或低写入频率）
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
    INSERT INTO daily_sales_summary (sale_date, order_count, total_revenue)
    VALUES (DATE(NEW.created_at), 1, NEW.amount)
    ON DUPLICATE KEY UPDATE
        order_count = order_count + 1,
        total_revenue = total_revenue + NEW.amount;

-- 注意：触发器会增加写入延迟，高并发场景不适用

24.6.3 视图嵌套

sql

-- 视图可以基于其他视图，但嵌套层级过多会导致：
-- 1. 性能问题（层层展开）
-- 2. 调试困难
-- 3. 优化器难以生成高效执行计划

-- 不建议的做法：
CREATE VIEW v1 AS SELECT ... FROM t1;
CREATE VIEW v2 AS SELECT ... FROM v1;
CREATE VIEW v3 AS SELECT ... FROM v2;
CREATE VIEW v4 AS SELECT ... FROM v3;
-- 四层嵌套，查询 v4 时优化器可能需要处理极复杂的展开

24.7 视图的权限管理

sql

-- 创建视图的权限
GRANT CREATE VIEW ON mydb.* TO 'developer'@'%';

-- 视图的 DEFINER 和 SQL SECURITY
-- DEFINER：指定视图以哪个用户的身份执行
CREATE DEFINER = 'admin'@'localhost' SQL SECURITY DEFINER
VIEW secure_view AS
SELECT * FROM sensitive_data;

-- SQL SECURITY DEFINER（默认）：以视图创建者的权限执行
-- 这意味着：查询者不需要基表权限，只要有视图权限即可
-- 常见用途：授予用户访问视图，但不授予基表权限

-- SQL SECURITY INVOKER：以当前查询者的权限执行
-- 查询者必须有基表的访问权限
CREATE SQL SECURITY INVOKER VIEW open_view AS
SELECT * FROM public_data;

-- 查看视图权限要求
SHOW CREATE VIEW view_name;
-- 检查 DEFINER 和 SQL SECURITY 设置

常见错误

认为视图能提升性能
- 错误：创建一个包含多表 JOIN 和聚合的视图，期望查询视图比直接写 SQL 快。
- 原因：视图只是保存的查询，每次访问视图都会重新执行底层 SQL。MySQL 不原生支持物化视图。
- 解决：对于频繁访问的复杂聚合，使用汇总表 + 定时刷新模拟物化视图。对于简单查询，视图确实能简化代码。
在视图的 WHERE 条件上期待索引优化
- 错误：视图 SELECT 中包含 GROUP BY 聚合，但在外部 WHERE view_column > 100 时期望使用索引。
- 原因：包含 GROUP BY 的视图使用 TEMPTABLE 算法，外部 WHERE 条件无法下推到基表。
- 解决：将过滤条件尽可能放在视图定义的 WHERE 中，或改用手动查询而非视图。
WITH CHECK OPTION 遗漏
- 错误：创建视图 WHERE status='active'，插入 status='inactive' 的记录成功，但该记录不显示在视图中。
- 后果：通过视图插入的数据在视图中"消失"，造成数据不一致的困惑。
- 解决：对于需要数据一致性的可更新视图，始终使用 WITH CHECK OPTION。
可更新视图的条件不满足
- 错误：对包含 JOIN 的视图执行 UPDATE，期望更新基表。
- 原因：涉及多表的视图不可更新（某些数据库允许，MySQL 限制更严格）。
- 解决：直接操作基表，或在应用层分别更新各个表。
依赖视图进行递归嵌套
- 错误：视图 A 引用视图 B，视图 B 引用视图 C，视图 C 又引用视图 A。
- 后果：查询时出现循环引用错误或无限递归。
- 解决：限制视图嵌套深度（通常不超过 2 层），避免循环引用。使用 EXPLAIN 检查查询展开后的复杂度。

本章练习

简化查询视图设计：某个系统中有 5 个需要频繁 JOIN 5 张表的报表查询。设计一个视图封装这个 JOIN，使得报表查询只需要 SELECT ... FROM report_view WHERE ...。
安全视图设计：users 表包含 id, username, password_hash, email, phone, salary, ssn, address, created_at。为三个角色设计不同的视图：(1) 客服人员只能看到 id, username, email, phone；(2) 部门经理可以看到除 password_hash 和 ssn 外的所有列；(3) DBA 拥有基表的全部访问权限。
CHECK OPTION 练习：创建一个 products 表的视图（WHERE quantity > 0），分别使用 WITH CASCADED CHECK OPTION 和 WITH LOCAL CHECK OPTION。测试插入 quantity=0 和 quantity=-1 的行为，并解释 CASCADED 和 LOCAL 的区别。
物化视图模拟：设计一个定时刷新的日销售汇总表，比较使用事件调度器和触发器的两种方案优劣，编写完整的 SQL 实现。
视图算法影响分析：创建两个视图（一个使用 MERGE 算法、一个使用 TEMPTABLE 算法），分别对它们查询并 EXPLAIN，对比执行计划的差异，解释为什么 TEMPTABLE 的视图上外部 WHERE 条件无法使用索引。
视图重构实践：假设有一张 orders 表需要垂直拆分为 orders_base 和 orders_detail。设计一个视图模拟旧的 orders 表结构，使所有旧的 SELECT 查询无需修改。同时说明对于 INSERT/UPDATE/DELETE 操作，视图无法满足需求时的替代方案。

第二十四章 视图 ​

本章目标 ​

前置知识 ​

24.1 视图的定义与类比 ​

24.1.1 什么是视图 ​

24.1.2 视图的直观示例 ​

24.2 创建和管理视图 ​

24.2.1 CREATE VIEW ​

24.2.2 指定视图列名 ​

24.2.3 CREATE OR REPLACE VIEW ​

24.2.4 ALTER VIEW ​

24.2.5 查看和删除视图 ​

24.3 可更新视图 ​

24.3.1 什么是可更新视图 ​

24.3.2 可更新视图的限制 ​

24.3.3 WITH CHECK OPTION ​

24.3.4 CASCADED 和 LOCAL ​

24.4 视图算法（Execution Algorithm） ​

24.5 视图的应用场景 ​

24.5.1 简化复杂查询 ​

24.5.2 安全性：列级访问控制 ​

24.5.3 向后兼容 ​

24.5.4 数据聚合与报表 ​

24.6 视图的局限性 ​

24.6.1 性能误区 ​

24.6.2 物化视图的模拟 ​

24.6.3 视图嵌套 ​

24.7 视图的权限管理 ​

常见错误 ​

本章练习 ​