— 视图创建策略
— 1. 封装复杂的查询逻辑
— 2. 提高代码的重用性
— 3. 控制数据访问权限
— 4. 提供一致的数据接口

— 示例：创建用户视图
CREATE VIEW fgedu_user_view AS
SELECT id, name, email, created_at
FROM fgedu_users
WHERE status = ‘ACTIVE’;

— 更新视图
CREATE OR REPLACE VIEW fgedu_user_view AS
SELECT id, name, email, phone, created_at
FROM fgedu_users
WHERE status = ‘ACTIVE’;

— 删除视图
DROP VIEW IF EXISTS fgedu_user_view;

— 监控视图的使用情况
— 启用慢查询日志
SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL slow_query_log_file = ‘/mysql/data/fgedu-slow.log’;
SET GLOBAL long_query_time = 1;

— 创建简单视图
CREATE VIEW fgedu_user_view AS
SELECT id, name, email, created_at
FROM fgedu_users
WHERE status = ‘ACTIVE’;

— 创建复杂视图
CREATE VIEW fgedu_order_view AS
SELECT
o.id AS order_id,
o.order_no,
o.amount,
o.status,
o.created_at,
u.name AS customer_name,
u.email AS customer_email
FROM fgedu_orders o
JOIN fgedu_users u ON o.customer_id = u.id;

— 创建带条件的视图
CREATE VIEW fgedu_recent_orders AS
SELECT *
FROM fgedu_orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

— 原视图（未优化）
CREATE VIEW fgedu_sales_report AS
SELECT
p.id AS product_id,
p.name AS product_name,
SUM(o.amount) AS total_sales,
COUNT(o.id) AS order_count
FROM fgedu_products p
LEFT JOIN fgedu_orders o ON p.id = o.product_id
GROUP BY p.id, p.name;

— 优化后视图
CREATE VIEW fgedu_sales_report AS
SELECT
p.id AS product_id,
p.name AS product_name,
COALESCE(SUM(o.amount), 0) AS total_sales,
COALESCE(COUNT(o.id), 0) AS order_count
FROM fgedu_products p
LEFT JOIN fgedu_orders o ON p.id = o.product_id
GROUP BY p.id, p.name;

— 测试视图性能
EXPLAIN SELECT * FROM fgedu_sales_report;

— 查看视图的创建语句
SHOW CREATE VIEW fgedu_sales_report;

— 查看视图的结构
DESCRIBE fgedu_sales_report;

— 测试视图查询性能
EXPLAIN SELECT * FROM fgedu_sales_report WHERE product_id = 1;

— 查看视图的使用情况
— 启用慢查询日志
SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL slow_query_log_file = ‘/mysql/data/fgedu-slow.log’;
SET GLOBAL long_query_time = 1;

— 查看慢查询日志中的视图使用情况
SELECT * FROM mysql.slow_log WHERE sql_text LIKE ‘%fgedu_sales_report%’;

— 创建用户视图
CREATE VIEW fgedu_user_view AS
SELECT id, name, email, created_at
FROM fgedu_users
WHERE status = ‘ACTIVE’;

— 使用视图
SELECT * FROM fgedu_user_view;

— 测试视图性能
EXPLAIN SELECT * FROM fgedu_user_view WHERE id = 1;

— 创建销售报表视图
CREATE VIEW fgedu_sales_report AS
SELECT
DATE_FORMAT(o.created_at, ‘%Y-%m’) AS month,
p.category AS product_category,
SUM(o.amount) AS total_sales,
COUNT(o.id) AS order_count,
AVG(o.amount) AS average_order_amount
FROM fgedu_orders o
JOIN fgedu_products p ON o.product_id = p.id
GROUP BY DATE_FORMAT(o.created_at, ‘%Y-%m’), p.category
ORDER BY month DESC, product_category;

— 使用视图
SELECT * FROM fgedu_sales_report;

— 测试视图性能
EXPLAIN SELECT * FROM fgedu_sales_report WHERE month = ‘2024-01’;

— 优化前：视图包含复杂的连接和聚合
CREATE VIEW fgedu_complex_view AS
SELECT
u.id AS user_id,
u.name AS user_name,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_spent,
MAX(o.created_at) AS last_order_date
FROM fgedu_users u
LEFT JOIN fgedu_orders o ON u.id = o.customer_id
GROUP BY u.id, u.name;

— 优化后：使用子查询和索引
CREATE VIEW fgedu_complex_view AS
SELECT
u.id AS user_id,
u.name AS user_name,
COALESCE(order_stats.order_count, 0) AS order_count,
COALESCE(order_stats.total_spent, 0) AS total_spent,
order_stats.last_order_date
FROM fgedu_users u
LEFT JOIN (
SELECT
customer_id,
COUNT(id) AS order_count,
SUM(amount) AS total_spent,
MAX(created_at) AS last_order_date
FROM fgedu_orders
GROUP BY customer_id
) order_stats ON u.id = order_stats.customer_id;

— 测试优化效果
EXPLAIN SELECT * FROM fgedu_complex_view;