— 场景：为不同角色创建不同的视图，限制数据访问范围

— 实施步骤：

— 1. 创建用户表
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb

fgedudb=# CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
role VARCHAR(20) NOT NULL,
active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 2. 插入测试数据
fgedudb=# INSERT INTO fgedu_users (name, email, password, role) VALUES
(‘Admin User’, ‘admin@fgedu.net.cn’, ‘admin123’, ‘admin’),
(‘Manager User’, ‘manager@fgedu.net.cn’, ‘manager123’, ‘manager’),
(‘Regular User’, ‘user@fgedu.net.cn’, ‘user123’, ‘user’);

— 3. 创建管理员视图（查看所有用户）
fgedudb=# CREATE VIEW v_admin_users AS
SELECT id, name, email, role, active, created_at
FROM fgedu_users;

— 4. 创建经理视图（查看除管理员外的用户）
fgedudb=# CREATE VIEW v_manager_users AS
SELECT id, name, email, role, active, created_at
FROM fgedu_users
WHERE role != ‘admin’;

— 5. 创建普通用户视图（只查看自己）
fgedudb=# CREATE OR REPLACE VIEW v_user_profile AS
SELECT id, name, email, role, active, created_at
FROM fgedu_users
WHERE id = current_user::INTEGER;

— 6. 授予权限
— 创建角色
fgedudb=# CREATE ROLE admin_role;
fgedudb=# CREATE ROLE manager_role;
fgedudb=# CREATE ROLE user_role;

— 授予视图权限
fgedudb=# GRANT SELECT ON v_admin_users TO admin_role;
fgedudb=# GRANT SELECT ON v_manager_users TO manager_role;
fgedudb=# GRANT SELECT ON v_user_profile TO user_role;

— 测试视图
— 以管理员身份查询
fgedudb=# SET ROLE admin_role;
fgedudb=# SELECT * FROM v_admin_users;

— 以经理身份查询
fgedudb=# SET ROLE manager_role;
fgedudb=# SELECT * FROM v_manager_users;

— 以普通用户身份查询
fgedudb=# SET ROLE user_role;
fgedudb=# SELECT * FROM v_user_profile;

— 场景：创建物化视图用于销售数据分析，提高查询性能

— 实施步骤：

— 1. 创建销售相关表
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb

— 创建产品表
fgedudb=# CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
price DECIMAL(10,2) NOT NULL
);

— 创建订单表
fgedudb=# CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
customer_id INTEGER NOT NULL,
total_amount DECIMAL(10,2) NOT NULL
);

— 创建订单项表
fgedudb=# CREATE TABLE fgedu_order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES fgedu_orders(id),
product_id INTEGER REFERENCES fgedu_products(id),
quantity INTEGER NOT NULL,
unit_price DECIMAL(10,2) NOT NULL
);

— 2. 插入测试数据
— 插入产品数据
fgedudb=# INSERT INTO fgedu_products (name, category, price) VALUES
(‘Product A’, ‘Electronics’, 100.00),
(‘Product B’, ‘Electronics’, 200.00),
(‘Product C’, ‘Clothing’, 50.00),
(‘Product D’, ‘Clothing’, 75.00),
(‘Product E’, ‘Home’, 150.00);

— 插入订单数据
fgedudb=# INSERT INTO fgedu_orders (customer_id, total_amount) VALUES
(1, 300.00),
(2, 125.00),
(1, 250.00),
(3, 450.00),
(2, 150.00);

— 插入订单项数据
fgedudb=# INSERT INTO fgedu_order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 100.00),
(1, 2, 1, 200.00),
(2, 3, 1, 50.00),
(2, 4, 1, 75.00),
(3, 2, 1, 200.00),
(3, 5, 1, 150.00),
(4, 1, 2, 100.00),
(4, 2, 1, 200.00),
(4, 5, 1, 150.00),
(5, 3, 2, 50.00),
(5, 4, 1, 75.00);

— 3. 创建销售分析物化视图
— 按类别分析销售
fgedudb=# CREATE MATERIALIZED VIEW mv_fgedu_sales_by_category AS
SELECT
p.category,
COUNT(DISTINCT o.id) AS order_count,
COUNT(oi.id) AS item_count,
SUM(oi.quantity) AS total_quantity,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM fgedu_products p
JOIN fgedu_order_items oi ON p.id = oi.product_id
JOIN fgedu_orders o ON oi.order_id = o.id
GROUP BY p.category
ORDER BY total_sales DESC;

— 按日期分析销售
fgedudb=# CREATE MATERIALIZED VIEW mv_fgedu_sales_by_date AS
SELECT
DATE(o.order_date) AS sale_date,
COUNT(*) AS order_count,
SUM(o.total_amount) AS total_sales,
AVG(o.total_amount) AS avg_order_value
FROM fgedu_orders o
GROUP BY DATE(o.order_date)
ORDER BY sale_date;

— 4. 为物化视图创建索引
fgedudb=# CREATE INDEX idx_mv_fgedu_sales_by_category ON mv_fgedu_sales_by_category(category);
fgedudb=# CREATE INDEX idx_mv_fgedu_sales_by_date ON mv_fgedu_sales_by_date(sale_date);

— 5. 刷新物化视图
fgedudb=# REFRESH MATERIALIZED VIEW mv_fgedu_sales_by_category;
fgedudb=# REFRESH MATERIALIZED VIEW mv_fgedu_sales_by_date;

— 6. 查询物化视图
— 查询按类别销售
fgedudb=# SELECT * FROM mv_fgedu_sales_by_category;

— 查询按日期销售
fgedudb=# SELECT * FROM mv_fgedu_sales_by_date;

— 7. 设置定期刷新
— 创建刷新脚本
$ vi /postgresql/scripts/refresh_sales_views.sh

#!/bin/bash
# refresh_sales_views.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “REFRESH MATERIALIZED VIEW CONCURRENTLY mv_fgedu_sales_by_category;”
psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “REFRESH MATERIALIZED VIEW CONCURRENTLY mv_fgedu_sales_by_date;”

— 设置定时任务
$ crontab -e

0 * * * * /postgresql/scripts/refresh_sales_views.sh

— 场景：比较视图和物化视图在复杂查询中的性能差异

— 实施步骤：

— 1. 创建测试表
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb

— 创建大表
fgedudb=# CREATE TABLE fgedu_large_table (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
value DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入测试数据
fgedudb=# INSERT INTO fgedu_large_table (user_id, category_id, value)
SELECT
(random() * 1000)::INTEGER + 1,
(random() * 100)::INTEGER + 1,
random() * 1000
FROM generate_series(1, 1000000);

— 2. 创建视图
fgedudb=# CREATE VIEW v_fgedu_summary AS
SELECT
user_id,
category_id,
COUNT(*) AS count,
SUM(value) AS total_value,
AVG(value) AS avg_value,
MIN(value) AS min_value,
MAX(value) AS max_value
FROM fgedu_large_table
GROUP BY user_id, category_id;

— 3. 创建物化视图
fgedudb=# CREATE MATERIALIZED VIEW mv_fgedu_summary AS
SELECT
user_id,
category_id,
COUNT(*) AS count,
SUM(value) AS total_value,
AVG(value) AS avg_value,
MIN(value) AS min_value,
MAX(value) AS max_value
FROM fgedu_large_table
GROUP BY user_id, category_id;

— 为物化视图创建索引
fgedudb=# CREATE INDEX idx_mv_fgedu_summary_user_category ON mv_fgedu_summary(user_id, category_id);

— 4. 测试查询性能
— 测试视图查询性能
fgedudb=# EXPLAIN ANALYZE SELECT * FROM v_fgedu_summary WHERE user_id = 500;

— 测试物化视图查询性能
fgedudb=# EXPLAIN ANALYZE SELECT * FROM mv_fgedu_summary WHERE user_id = 500;

— 5. 测试数据更新后的性能
— 更新数据
fgedudb=# UPDATE fgedu_large_table SET value = value * 1.1 WHERE user_id = 500;

— 测试视图查询性能（数据已更新）
fgedudb=# EXPLAIN ANALYZE SELECT * FROM v_fgedu_summary WHERE user_id = 500;

— 测试物化视图查询性能（数据未更新）
fgedudb=# EXPLAIN ANALYZE SELECT * FROM mv_fgedu_summary WHERE user_id = 500;

— 刷新物化视图
fgedudb=# REFRESH MATERIALIZED VIEW mv_fgedu_summary;

— 测试物化视图查询性能（数据已更新）
fgedudb=# EXPLAIN ANALYZE SELECT * FROM mv_fgedu_summary WHERE user_id = 500;

— 6. 性能比较结果
— 视图：每次查询都执行聚合操作，性能较慢，但数据始终最新
— 物化视图：查询速度快，但需要定期刷新以保持数据一致性

— 1. 视图设计
— – 保持视图简洁，避免过于复杂的逻辑
— – 只包含必要的列，减少数据传输
— – 使用有意义的视图名称，如v_前缀
— – 考虑使用WITH CHECK OPTION确保数据一致性

— 2. 物化视图设计
— – 用于存储复杂查询的结果，如聚合、连接等
— – 用于频繁访问但不经常变化的数据
— – 使用有意义的名称，如mv_前缀
— – 为物化视图创建适当的索引

— 3. 性能优化
— – 对于频繁访问的复杂查询，使用物化视图
— – 为物化视图创建合适的索引
— – 考虑使用增量刷新减少刷新时间
— – 安排在低峰期刷新物化视图

— 4. 维护管理
— – 定期检查视图的有效性
— – 定期刷新物化视图
— – 监控视图和物化视图的使用情况
— – 定期进行VACUUM和ANALYZE操作

— 5. 安全性
— – 基于视图授予用户权限，而不是直接授予表权限
— – 限制用户对敏感数据的访问
— – 定期审查视图权限

— 6. 监控与故障排除
— – 监控视图和物化视图的性能
— – 排查视图执行计划中的性能问题
— – 解决物化视图刷新失败的问题
— – 监控物化视图的存储空间使用