MySQL教程FG276-MySQL视图优化

# 视图概述

1. 视图定义

视图特点：
– 存储查询定义的虚拟表
– 不存储实际数据
– 动态生成结果集
– 简化复杂查询

视图优势：
– 简化复杂查询
– 提供数据安全
– 实现逻辑独立
– 统一数据接口

视图用途：
– 报表查询
– 数据权限控制
– 复杂查询封装
– 数据格式转换

2. 创建视图

创建基本视图：
mysql> CREATE VIEW v_customer_orders AS
SELECT
c.id AS customer_id,
c.name AS customer_name,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查询视图：
mysql> SELECT * FROM v_customer_orders LIMIT 5;

输出示例：
+————-+—————+————-+————–+
| customer_id | customer_name | order_count | total_amount |
+————-+—————+————-+————–+
| 1 | Customer A | 10 | 5000.00 |
| 2 | Customer B | 5 | 2500.00 |
| 3 | Customer C | 15 | 7500.00 |
+————-+—————+————-+————–+

3. 视图操作

查看视图定义：
mysql> SHOW CREATE VIEW v_customer_orders\G

输出示例：
*************************** 1. row ***************************
View: v_customer_orders
Create View: CREATE ALGORITHM=UNDEFINED
DEFINER=`root`@`localhost`
SQL SECURITY DEFINER
VIEW `v_customer_orders` AS
SELECT c.id AS customer_id…
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci

修改视图：
mysql> CREATE OR REPLACE VIEW v_customer_orders AS
SELECT
c.id AS customer_id,
c.name AS customer_name,
c.region,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.region;

输出示例：
Query OK, 0 rows affected (0.01 sec)

删除视图：
mysql> DROP VIEW IF EXISTS v_customer_orders;

输出示例：
Query OK, 0 rows affected (0.00 sec)

4. 视图类型

简单视图：
mysql> CREATE VIEW v_active_customers AS
SELECT id, name, email FROM customers WHERE status = ‘active’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

复杂视图：
mysql> CREATE VIEW v_sales_report AS
SELECT
p.category,
c.region,
SUM(oi.quantity) AS total_qty,
SUM(oi.quantity * oi.price) AS total_sales
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY p.category, c.region;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 查看数据库中的视图

查看所有视图：
mysql> SELECT TABLE_NAME, TABLE_TYPE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘production_db’
AND TABLE_TYPE = ‘VIEW’;

查看视图详细信息：
mysql> SELECT * FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = ‘production_db’;

# 视图类型

1. 简单视图与复杂视图

简单视图（可更新）：
mysql> CREATE VIEW v_simple AS
SELECT id, name, status FROM customers WHERE status = ‘active’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

复杂视图（不可更新）：
mysql> CREATE VIEW v_complex AS
SELECT category, COUNT(*) AS count, SUM(amount) AS total
FROM orders
GROUP BY category;

输出示例：
Query OK, 0 rows affected (0.01 sec)

区别：
+——————-+——————+——————+
| 特性 | 简单视图 | 复杂视图 |
+——————-+——————+——————+
| 聚合函数 | 不支持 | 支持 |
| GROUP BY | 不支持 | 支持 |
| DISTINCT | 不支持 | 支持 |
| UNION | 不支持 | 支持 |
| 子查询 | 不支持 | 支持 |
| 可更新 | 是 | 否 |
+——————-+——————+——————+

2. 可更新视图

创建可更新视图：
mysql> CREATE VIEW v_updatable_orders AS
SELECT id, customer_id, amount, status
FROM orders
WHERE status = ‘pending’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

通过视图插入数据：
mysql> INSERT INTO v_updatable_orders
(id, customer_id, amount, status)
VALUES (1, 100, 500.00, ‘pending’);

输出示例：
Query OK, 1 row affected (0.01 sec)

通过视图更新数据：
mysql> UPDATE v_updatable_orders
SET amount = 600.00
WHERE id = 1;

输出示例：
Query OK, 1 row affected (0.00 sec)

通过视图删除数据：
mysql> DELETE FROM v_updatable_orders WHERE id = 1;

输出示例：
Query OK, 1 row affected (0.00 sec)

3. 检查选项视图

WITH CHECK OPTION视图：
mysql> CREATE VIEW v_active_only AS
SELECT id, name, status
FROM customers
WHERE status = ‘active’
WITH CHECK OPTION;

输出示例：
Query OK, 0 rows affected (0.01 sec)

尝试插入不符合条件的数据：
mysql> INSERT INTO v_active_only (id, name, status)
VALUES (1, ‘Test’, ‘inactive’);

输出示例：
ERROR 1369 (HY000): CHECK OPTION failed ‘production_db.v_active_only’

CASCADED检查选项：
mysql> CREATE VIEW v_cascaded AS
SELECT * FROM v_active_only
WHERE region = ‘East’
WITH CASCADED CHECK OPTION;

输出示例：
Query OK, 0 rows affected (0.01 sec)

LOCAL检查选项：
mysql> CREATE VIEW v_local AS
SELECT * FROM v_active_only
WHERE region = ‘East’
WITH LOCAL CHECK OPTION;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 算法类型

MERGE算法：
mysql> CREATE ALGORITHM = MERGE VIEW v_merge AS
SELECT id, name FROM customers WHERE status = ‘active’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

TEMPTABLE算法：
mysql> CREATE ALGORITHM = TEMPTABLE VIEW v_temptable AS
SELECT id, name FROM customers WHERE status = ‘active’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

UNDEFINED算法（默认）：
mysql> CREATE ALGORITHM = UNDEFINED VIEW v_undefined AS
SELECT id, name FROM customers WHERE status = ‘active’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

算法对比：
+——————-+——————+——————+
| 特性 | MERGE | TEMPTABLE |
+——————-+——————+——————+
| 性能 | 高 | 较低 |
| 使用临时表 | 否 | 是 |
| 可更新 | 是 | 否 |
| 索引利用 | 可以利用基表索引 | 不能利用 |
+——————-+——————+——————+

5. 安全类型

DEFINER安全：
mysql> CREATE DEFINER = ‘admin’@’localhost’
SQL SECURITY DEFINER
VIEW v_admin_view AS
SELECT * FROM sensitive_data;

输出示例：
Query OK, 0 rows affected (0.01 sec)

INVOKER安全：
mysql> CREATE SQL SECURITY INVOKER
VIEW v_user_view AS
SELECT * FROM user_data WHERE user_id = CURRENT_USER();

输出示例：
Query OK, 0 rows affected (0.01 sec)

# 视图特性

1. 虚拟表特性

特点：视图不存储数据，每次查询动态生成

验证视图不存储数据：
mysql> SELECT TABLE_NAME, TABLE_TYPE, ENGINE
FROM information_schema.TABLES
WHERE TABLE_NAME = ‘v_customer_orders’;

查看视图数据目录：
ls -la /var/lib/mysql/production_db/v_customer_orders*

输出示例：
ls: cannot access: No such file or directory

2. 查询重写

特点：视图查询会被重写为基表查询

创建视图：
mysql> CREATE VIEW v_products AS
SELECT id, name, price FROM products WHERE active = 1;

查询视图：
mysql> EXPLAIN SELECT * FROM v_products WHERE price > 100;

视图定义被合并到查询中：
— 实际执行：SELECT id, name, price FROM products WHERE active = 1 AND price > 100

3. 索引利用

特点：视图可以利用基表的索引

创建带索引的基表：
mysql> CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
category VARCHAR(50),
active TINYINT,
INDEX idx_price (price),
INDEX idx_category (category)
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

创建视图：
mysql> CREATE VIEW v_active_products AS
SELECT id, name, price, category
FROM products WHERE active = 1;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查询视图使用索引：
mysql> EXPLAIN SELECT * FROM v_active_products WHERE price > 100;

4. 嵌套视图

特点：视图可以基于其他视图创建

创建基础视图：
mysql> CREATE VIEW v_base AS
SELECT id, name, price FROM products;

输出示例：
Query OK, 0 rows affected (0.01 sec)

创建嵌套视图：
mysql> CREATE VIEW v_nested AS
SELECT id, name, price FROM v_base WHERE price > 100;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查看嵌套视图：
mysql> SHOW CREATE VIEW v_nested\G

输出示例：
*************************** 1. row ***************************
View: v_nested
Create View: CREATE VIEW `v_nested` AS
SELECT id, name, price FROM v_base WHERE price > 100

5. 视图依赖

特点：视图依赖基表，基表变化影响视图

查看视图依赖：
mysql> SELECT * FROM information_schema.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA = ‘production_db’;

删除基表后视图失效：
mysql> DROP TABLE customers;

输出示例：
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM v_customer_orders;

输出示例：
ERROR 1356 (HY000): View ‘production_db.v_customer_orders’
references invalid table(s) or column(s) or function(s)

# 视图设计原则

1. 视图命名规范

创建规范命名视图：
mysql> CREATE VIEW v_rpt_monthly_sales AS
SELECT
DATE_FORMAT(order_date, ‘%Y-%m’) AS month,
COUNT(*) AS order_count,
SUM(amount) AS total_sales
FROM orders
GROUP BY DATE_FORMAT(order_date, ‘%Y-%m’);

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 查询简化原则

简化复杂JOIN：
mysql> CREATE VIEW v_order_details AS
SELECT
o.id AS order_id,
o.order_date,
c.name AS customer_name,
c.email,
p.name AS product_name,
oi.quantity,
oi.price,
oi.quantity * oi.price AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

输出示例：
Query OK, 0 rows affected (0.01 sec)

使用简化后的视图：
mysql> SELECT * FROM v_order_details WHERE order_id = 1;

3. 数据安全原则

创建敏感数据过滤视图：
mysql> CREATE VIEW v_customer_public AS
SELECT
id,
name,
region,
created_at
FROM customers;

输出示例：
Query OK, 0 rows affected (0.01 sec)

授权视图访问：
mysql> GRANT SELECT ON production_db.v_customer_public TO ‘app_user’@’%’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

撤销基表访问：
mysql> REVOKE ALL ON production_db.customers FROM ‘app_user’@’%’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

4. 性能优化原则

选择合适的算法：
— 简单查询使用MERGE
mysql> CREATE ALGORITHM = MERGE VIEW v_simple_merge AS
SELECT id, name FROM customers WHERE status = ‘active’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

— 复杂查询使用TEMPTABLE
mysql> CREATE ALGORITHM = TEMPTABLE VIEW v_complex_temp AS
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 可维护性原则

添加视图注释：
mysql> CREATE VIEW v_active_customers AS
SELECT id, name, email, region
FROM customers
WHERE status = ‘active’
COMMENT ‘活跃客户视图，用于报表和营销活动’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查看视图注释：
mysql> SELECT TABLE_NAME, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_TYPE = ‘VIEW’;

# 视图优化策略

1. 算法选择优化

查看视图算法：
mysql> SELECT TABLE_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = ‘production_db’;

优化算法选择：
— 对于简单查询，使用MERGE算法
mysql> CREATE OR REPLACE ALGORITHM = MERGE VIEW v_optimized AS
SELECT id, name FROM customers WHERE status = ‘active’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

— 验证是否使用索引
mysql> EXPLAIN SELECT * FROM v_optimized WHERE id > 100;

2. 索引优化

为视图基表创建索引：
mysql> CREATE INDEX idx_customer_status ON customers (status);
mysql> CREATE INDEX idx_order_customer ON orders (customer_id);
mysql> CREATE INDEX idx_order_date ON orders (order_date);

输出示例：
Query OK, 0 rows affected (0.10 sec)

验证索引使用：
mysql> EXPLAIN SELECT * FROM v_customer_orders WHERE customer_id = 1;

输出示例：
+—-+————-+——–+——-+——————–+——————–+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——–+——-+——————–+——————–+———+——-+——+————-+
| 1 | SIMPLE | c | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | o | ref | idx_order_customer | idx_order_customer | 5 | const | 10 | Using index |
+—-+————-+——–+——-+——————–+——————–+———+——-+——+————-+

3. 查询条件优化

在视图查询中添加条件：
— 低效：先执行视图全部查询
mysql> SELECT * FROM v_all_orders WHERE customer_id = 1;

— 高效：视图定义中包含常用条件
mysql> CREATE VIEW v_recent_orders AS
SELECT * FROM orders
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

输出示例：
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM v_recent_orders WHERE customer_id = 1;

输出示例：
+—-+————-+———————+——–+
| id | customer_id | order_date | amount |
+—-+————-+———————+——–+
| 1 | 1 | 2026-03-15 10:00:00 | 500.00 |
+—-+————-+———————+——–+

4. 避免嵌套过深

检查视图嵌套层级：
mysql> SELECT VIEW_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE VIEW_DEFINITION LIKE ‘%v_%’;

扁平化视图：
— 低效：多层嵌套
mysql> CREATE VIEW v_level1 AS SELECT * FROM orders WHERE status = ‘pending’;
mysql> CREATE VIEW v_level2 AS SELECT * FROM v_level1 WHERE amount > 100;
mysql> CREATE VIEW v_level3 AS SELECT * FROM v_level2 WHERE region = ‘East’;

— 高效：扁平化
mysql> CREATE VIEW v_optimized AS
SELECT * FROM orders
WHERE status = ‘pending’
AND amount > 100
AND region = ‘East’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 物化视图替代

MySQL不支持物化视图，可使用表替代：

创建汇总表：
mysql> CREATE TABLE mv_daily_sales (
sale_date DATE PRIMARY KEY,
order_count INT,
total_amount DECIMAL(12,2),
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

定时更新汇总表：
mysql> INSERT INTO mv_daily_sales (sale_date, order_count, total_amount)
SELECT
DATE(order_date) AS sale_date,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
WHERE DATE(order_date) = CURDATE()
ON DUPLICATE KEY UPDATE
order_count = VALUES(order_count),
total_amount = VALUES(total_amount),
updated_at = NOW();

输出示例：
Query OK, 1 row affected (0.01 sec)

# 视图监控

1. 监控视图使用

查看视图定义：
mysql> SELECT
TABLE_SCHEMA,
TABLE_NAME,
VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = ‘production_db’;

2. 监控视图性能

分析视图查询性能：
mysql> EXPLAIN FORMAT=JSON
SELECT * FROM v_customer_orders WHERE customer_id = 1\G

输出示例：
*************************** 1. row ***************************
EXPLAIN: {
“query_block”: {
“select_id”: 1,
“cost_info”: {
“query_cost”: “10.00”
},
“nested_loop”: [
{
“table”: {
“table_name”: “c”,
“access_type”: “const”
}
}
]
}
}

3. 监控视图依赖

查看视图依赖的表：
mysql> SELECT * FROM information_schema.VIEW_TABLE_USAGE
WHERE VIEW_SCHEMA = ‘production_db’;

4. 监控视图状态

检查视图是否有效：
mysql> SELECT
TABLE_SCHEMA,
TABLE_NAME,
CASE
WHEN TABLE_TYPE = ‘VIEW’ THEN ‘Valid’
ELSE ‘Invalid’
END AS status
FROM information_schema.TABLES
WHERE TABLE_TYPE = ‘VIEW’;

5. 创建监控视图

创建视图统计视图：
mysql> CREATE VIEW v_view_statistics AS
SELECT
TABLE_SCHEMA,
TABLE_NAME AS view_name,
DEFINER,
SECURITY_TYPE,
IS_UPDATABLE
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE();

查询统计：
mysql> SELECT * FROM v_view_statistics;

# 视图实施规范

1. 视图创建规范

标准视图模板：
mysql> CREATE OR REPLACE VIEW v_standard_template AS
SELECT
column1,
column2,
column3
FROM table_name
WHERE condition
COMMENT ‘视图描述：用途和注意事项’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

规范要求：
– 使用标准命名前缀
– 添加视图注释
– 明确指定列名
– 选择合适的算法

2. 权限管理规范

创建只读视图用户：
mysql> CREATE USER ‘view_user’@’%’ IDENTIFIED BY ‘password’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

授予视图访问权限：
mysql> GRANT SELECT ON production_db.v_customer_public TO ‘view_user’@’%’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

验证权限：
mysql> SHOW GRANTS FOR ‘view_user’@’%’;

3. 版本控制规范

导出视图定义：
mysqldump -u root -p –no-data –routines –triggers \
production_db > views_backup.sql

输出示例：
— MySQL dump 10.13 Distrib 8.4.0
— Dumping routines for database ‘production_db’
CREATE VIEW v_customer_orders AS…

视图变更记录：
mysql> CREATE TABLE view_change_log (
id INT AUTO_INCREMENT PRIMARY KEY,
view_name VARCHAR(100),
change_type VARCHAR(20),
old_definition TEXT,
new_definition TEXT,
changed_by VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

4. 测试规范

创建视图测试脚本：
mysql> DELIMITER //
mysql> CREATE PROCEDURE test_view_validity()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_name VARCHAR(100);
DECLARE cur CURSOR FOR
SELECT TABLE_NAME FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO v_name;
IF done THEN
LEAVE read_loop;
END IF;

SET @sql = CONCAT(‘SELECT 1 FROM ‘, v_name, ‘ LIMIT 1’);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;

SELECT ‘All views are valid’ AS result;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

执行测试：
mysql> CALL test_view_validity();

输出示例：
+———————-+
| result |
+———————-+
| All views are valid |
+———————-+

5. 文档规范

创建视图文档表：
mysql> CREATE TABLE view_documentation (
view_name VARCHAR(100) PRIMARY KEY,
description TEXT,
purpose VARCHAR(200),
owner VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

输出示例：
Query OK, 0 rows affected (0.02 sec)

插入文档：
mysql> INSERT INTO view_documentation (view_name, description, purpose, owner)
VALUES (
‘v_customer_orders’,
‘客户订单汇总视图，包含订单数量和总金额’,
‘用于客户报表和数据分析’,
‘dba_team’
);

输出示例：
Query OK, 1 row affected (0.01 sec)

# 视图应用场景

1. 报表查询

创建报表视图：
mysql> CREATE VIEW v_rpt_sales_summary AS
SELECT
DATE_FORMAT(o.order_date, ‘%Y-%m’) AS month,
c.region,
COUNT(DISTINCT o.id) AS order_count,
COUNT(DISTINCT o.customer_id) AS customer_count,
SUM(o.amount) AS total_sales,
AVG(o.amount) AS avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY DATE_FORMAT(o.order_date, ‘%Y-%m’), c.region;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查询报表：
mysql> SELECT * FROM v_rpt_sales_summary WHERE month = ‘2026-03’;

输出示例：
+———+——–+————-+—————-+————–+—————-+
| month | region | order_count | customer_count | total_sales | avg_order_value|
+———+——–+————-+—————-+————–+—————-+
| 2026-03 | East | 500 | 200 | 50000.00 | 100.00 |
| 2026-03 | West | 300 | 150 | 30000.00 | 100.00 |
+———+——–+————-+—————-+————–+—————-+

2. 数据权限控制

创建部门数据视图：
mysql> CREATE VIEW v_dept_data AS
SELECT * FROM employees
WHERE department_id = (
SELECT department_id FROM user_dept_map
WHERE user_id = CURRENT_USER()
);

输出示例：
Query OK, 0 rows affected (0.01 sec)

授权部门用户：
mysql> GRANT SELECT ON production_db.v_dept_data TO ‘dept_user’@’%’;

输出示例：
Query OK, 0 rows affected (0.01 sec)

3. 数据格式转换

创建格式化视图：
mysql> CREATE VIEW v_formatted_orders AS
SELECT
o.id,
DATE_FORMAT(o.order_date, ‘%Y年%m月%d日’) AS order_date_cn,
FORMAT(o.amount, 2) AS amount_formatted,
CASE o.status
WHEN ‘pending’ THEN ‘待处理’
WHEN ‘processing’ THEN ‘处理中’
WHEN ‘completed’ THEN ‘已完成’
WHEN ‘cancelled’ THEN ‘已取消’
END AS status_cn,
c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查询格式化数据：
mysql> SELECT * FROM v_formatted_orders LIMIT 3;

输出示例：
+—-+——————+——————+———–+—————+
| id | order_date_cn | amount_formatted | status_cn | customer_name |
+—-+——————+——————+———–+—————+
| 1 | 2026年04月01日 | 500.00 | 已完成 | Customer A |
+—-+——————+——————+———–+—————+

4. 复杂计算封装

创建计算视图：
mysql> CREATE VIEW v_customer_metrics AS
SELECT
c.id,
c.name,
COUNT(DISTINCT o.id) AS total_orders,
SUM(o.amount) AS total_spent,
AVG(o.amount) AS avg_order_value,
MAX(o.order_date) AS last_order_date,
DATEDIFF(NOW(), MAX(o.order_date)) AS days_since_last_order,
CASE
WHEN DATEDIFF(NOW(), MAX(o.order_date)) <= 30 THEN 'Active' WHEN DATEDIFF(NOW(), MAX(o.order_date)) <= 90 THEN 'Inactive' ELSE 'Churned' END AS customer_status FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name; 输出示例： Query OK, 0 rows affected (0.01 sec) 查询客户指标： mysql> SELECT * FROM v_customer_metrics WHERE customer_status = ‘Active’;

输出示例：
+—-+————+————–+————-+—————-+———————+———————-+—————–+
| id | name | total_orders | total_spent | avg_order_value| last_order_date | days_since_last_order| customer_status |
+—-+————+————–+————-+—————-+———————+———————-+—————–+
| 1 | Customer A | 10 | 5000.00 | 500.00 | 2026-03-25 10:00:00 | 7 | Active |
+—-+————+————–+————-+—————-+———————+———————-+—————–+

5. 历史数据访问

创建历史数据视图：
mysql> CREATE VIEW v_order_history AS
SELECT
o.id,
o.order_date,
o.amount,
o.status,
h.changed_at,
h.old_status,
h.new_status,
h.changed_by
FROM orders o
JOIN order_history h ON o.id = h.order_id;

输出示例：
Query OK, 0 rows affected (0.01 sec)

查询历史记录：
mysql> SELECT * FROM v_order_history WHERE id = 1;

输出示例：
+—-+———————+——–+———-+———————+————+————+————+
| id | order_date | amount | status | changed_at | old_status | new_status | changed_by |
+—-+———————+——–+———-+———————+————+————+————+
| 1 | 2026-04-01 10:00:00 | 500.00 | completed| 2026-04-01 11:00:00 | pending | processing | admin |
| 1 | 2026-04-01 10:00:00 | 500.00 | completed| 2026-04-01 12:00:00 | processing | completed | admin |
+—-+———————+——–+———-+———————+————+————+————+

# 视图维护

1. 视图更新

更新视图定义：
mysql> CREATE OR REPLACE VIEW v_customer_orders AS
SELECT
c.id AS customer_id,
c.name AS customer_name,
c.region,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_amount,
MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.region;

输出示例：
Query OK, 0 rows affected (0.01 sec)

使用ALTER VIEW：
mysql> ALTER VIEW v_customer_orders AS
SELECT
c.id AS customer_id,
c.name AS customer_name,
COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

输出示例：
Query OK, 0 rows affected (0.01 sec)

2. 视图删除

删除单个视图：
mysql> DROP VIEW IF EXISTS v_customer_orders;

输出示例：
Query OK, 0 rows affected (0.00 sec)

删除多个视图：
mysql> DROP VIEW IF EXISTS v_view1, v_view2, v_view3;

输出示例：
Query OK, 0 rows affected (0.00 sec)

3. 视图依赖检查

检查视图依赖：
mysql> SELECT * FROM information_schema.VIEW_TABLE_USAGE
WHERE TABLE_NAME = ‘customers’;

删除表前检查依赖：
mysql> SELECT COUNT(*) FROM information_schema.VIEW_TABLE_USAGE
WHERE TABLE_NAME = ‘customers’;

输出示例：
+———-+
| COUNT(*) |
+———-+
| 2 |
+———-+

4. 视图重建

批量重建视图：
mysql> DELIMITER //
mysql> CREATE PROCEDURE rebuild_all_views()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_name VARCHAR(100);
DECLARE v_def TEXT;
DECLARE cur CURSOR FOR
SELECT TABLE_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO v_name, v_def;
IF done THEN
LEAVE read_loop;
END IF;

SET @sql = CONCAT(‘CREATE OR REPLACE VIEW ‘, v_name, ‘ AS ‘, v_def);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;

SELECT ‘All views rebuilt’ AS result;
END //
mysql> DELIMITER ;

输出示例：
Query OK, 0 rows affected (0.01 sec)

5. 视图备份

导出视图定义：
mysqldump -u root -p –no-data –routines \
production_db > views_backup_$(date +%Y%m%d).sql

输出示例：
— MySQL dump completed

导入视图定义：
mysql -u root -p production_db < views_backup_20260401.sql 输出示例： Query OK, 0 rows affected

# 复杂视图优化案例

# 案例：多表关联报表视图优化

# 问题描述：
# 原始视图查询执行时间超过10秒

# 步骤1：分析原始视图
mysql> SHOW CREATE VIEW v_sales_report\G

# 输出示例：
# *************************** 1. row ***************************
# View: v_sales_report
# Create View: CREATE VIEW v_sales_report AS
# SELECT … FROM orders o
# JOIN customers c …
# JOIN order_items oi …
# JOIN products p …
# GROUP BY …

# 步骤2：分析执行计划
mysql> EXPLAIN SELECT * FROM v_sales_report WHERE region = ‘East’\G

# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: c
# type: ALL
# possible_keys: NULL
# key: NULL
# rows: 10000
# Extra: Using temporary; Using filesort

# 步骤3：添加索引
mysql> CREATE INDEX idx_region ON customers (region);
mysql> CREATE INDEX idx_customer_id ON orders (customer_id);
mysql> CREATE INDEX idx_order_id ON order_items (order_id);

# 输出示例：
# Query OK, 0 rows affected (0.10 sec)

# 步骤4：优化视图定义
mysql> CREATE OR REPLACE VIEW v_sales_report AS
SELECT
p.category,
c.region,
SUM(oi.quantity) AS total_qty,
SUM(oi.quantity * oi.price) AS total_sales
FROM orders o
STRAIGHT_JOIN customers c ON o.customer_id = c.id
STRAIGHT_JOIN order_items oi ON o.id = oi.order_id
STRAIGHT_JOIN products p ON oi.product_id = p.id
GROUP BY p.category, c.region;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 步骤5：验证优化效果
mysql> EXPLAIN SELECT * FROM v_sales_report WHERE region = ‘East’\G

# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: SIMPLE
# table: c
# type: ref
# possible_keys: idx_region
# key: idx_region
# rows: 1000
# Extra: NULL

# 性能对比：
# +——————+————+
# | 阶段 | 执行时间 |
# +——————+————+
# | 优化前 | 10秒 |
# | 优化后 | 0.5秒 |
# +——————+————+

# 性能提升：20倍

# 安全视图案例

# 案例：敏感数据保护

# 问题描述：
# 需要限制普通用户访问敏感数据

# 步骤1：创建基表
mysql> CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2),
ssn VARCHAR(20),
bank_account VARCHAR(30)
);

# 输出示例：
# Query OK, 0 rows affected (0.02 sec)

# 步骤2：创建安全视图
mysql> CREATE VIEW v_employee_public AS
SELECT
id,
name,
department
FROM employees;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 步骤3：创建部门视图
mysql> CREATE DEFINER = ‘hr_admin’@’localhost’
SQL SECURITY DEFINER
VIEW v_employee_hr AS
SELECT
id,
name,
department,
salary
FROM employees;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 步骤4：设置权限
mysql> CREATE USER ‘app_user’@’%’ IDENTIFIED BY ‘password’;
mysql> GRANT SELECT ON production_db.v_employee_public TO ‘app_user’@’%’;

mysql> CREATE USER ‘hr_user’@’%’ IDENTIFIED BY ‘password’;
mysql> GRANT SELECT ON production_db.v_employee_hr TO ‘hr_user’@’%’;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 步骤5：验证权限隔离
— app_user只能看到基本信息
mysql> SELECT * FROM v_employee_public;

# 输出示例：
# +—-+————+————+
# | id | name | department |
# +—-+————+————+
# | 1 | Employee 1 | Sales |
# +—-+————+————+

— hr_user可以看到薪资信息
mysql> SELECT * FROM v_employee_hr;

# 输出示例：
# +—-+————+————+———-+
# | id | name | department | salary |
# +—-+————+————+———-+
# | 1 | Employee 1 | Sales | 5000.00 |
# +—-+————+————+———-+

# 性能优化案例

# 案例：TEMPTABLE算法优化

# 问题描述：
# 视图使用TEMPTABLE算法导致性能下降

# 步骤1：检查当前算法
mysql> SELECT TABLE_NAME, VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_NAME = ‘v_complex_report’;

# 步骤2：分析性能问题
mysql> EXPLAIN SELECT * FROM v_complex_report WHERE category = ‘Electronics’\G

# 输出示例：
# *************************** 1. row ***************************
# id: 1
# select_type: PRIMARY
# table:
# type: ALL
# rows: 10000
# Extra: Using where

# 步骤3：优化方案1 – 改用MERGE算法
mysql> CREATE OR REPLACE ALGORITHM = MERGE VIEW v_complex_report AS
SELECT
p.category,
o.order_date,
SUM(oi.quantity) AS total_qty
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY p.category, o.order_date;

# 输出示例：
# Query OK, 0 rows affected (0.01 sec)

# 步骤4：优化方案2 – 创建物化表
mysql> CREATE TABLE mv_category_sales (
category VARCHAR(50),
sale_date DATE,
total_qty INT,
total_amount DECIMAL(12,2),
PRIMARY KEY (category, sale_date)
);

# 输出示例：
# Query OK, 0 rows affected (0.02 sec)

# 步骤5：定时刷新物化表
mysql> CREATE EVENT refresh_mv_category_sales
ON SCHEDULE EVERY 1 HOUR
DO
REPLACE INTO mv_category_sales
SELECT
p.category,
DATE(o.order_date) AS sale_date,
SUM(oi.quantity) AS total_qty,
SUM(oi.quantity * oi.price) AS total_amount
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY p.category, DATE(o.order_date);

# 输出示例：
# Query OK, 0 rows affected (0

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

MySQL教程FG276-MySQL视图优化

Part01-基础概念与理论知识

1.1 视图概述

1.2 视图类型

1.3 视图特性

Part02-生产环境规划与建议

2.1 视图设计原则

2.2 视图优化策略

2.3 视图监控

Part03-生产环境项目实施方案

3.1 视图实施规范

3.2 视图应用场景

3.3 视图维护

Part04-生产案例与实战讲解

4.1 复杂视图优化案例

4.2 安全视图案例

4.3 性能优化案例

联系我们

MySQL教程FG276-MySQL视图优化

Part01-基础概念与理论知识

1.1 视图概述

1.2 视图类型

1.3 视图特性

Part02-生产环境规划与建议

2.1 视图设计原则

2.2 视图优化策略

2.3 视图监控

Part03-生产环境项目实施方案

3.1 视图实施规范

3.2 视图应用场景

3.3 视图维护

Part04-生产案例与实战讲解

4.1 复杂视图优化案例

4.2 安全视图案例

4.3 性能优化案例

相关推荐

联系我们