PostgreSQL教程FG027-PG视图：创建/更新/物化视图基础
目录大纲
Part01-基础概念与理论知识
1.1 视图的概念与特点
视图是PostgreSQL中一种虚拟表，它基于SQL查询的结果集。视图的主要特点包括：
- 虚拟性：不存储实际数据，只存储查询定义
- 简化查询：将复杂查询封装为简单的视图
- 权限控制：可以通过视图限制用户对基础表的访问
- 数据一致性：多个应用可以使用同一视图，确保数据逻辑一致
学习交流加群风哥微信: itpux-com
1.2 物化视图的概念与特点
物化视图是PostgreSQL中一种特殊的视图，它会存储查询结果数据。物化视图的主要特点包括：
- 存储实际数据：将查询结果物理存储
- 查询性能：比普通视图更快，因为不需要每次查询都执行底层SQL
- 需要刷新：数据不会自动更新，需要手动或自动刷新
- 存储空间：会占用额外的存储空间
更多视频教程www.fgedu.net.cn
Part02-生产环境规划与建议
2.1 视图的使用场景与注意事项
视图适合以下场景：
- 简化复杂查询：将多表关联的复杂查询封装为视图
- 权限控制：只允许用户访问视图中的特定列
- 数据抽象：隐藏底层表结构的复杂性
- 报表生成：为特定报表创建专用视图
注意事项：
- 视图可能会影响性能，特别是复杂视图
- 修改视图需要注意底层表的结构变化
- 视图的更新操作有一定限制
风哥提示：视图适合封装复杂查询逻辑，但对于频繁查询的场景，考虑使用物化视图。
2.2 物化视图的适用场景与规划
物化视图适合以下场景：
- 数据仓库和OLAP系统：需要快速查询汇总数据
- 报表系统：需要定期生成报表数据
- 复杂查询：底层查询涉及多个表关联或聚合计算
- 数据同步：需要从多个源表同步数据
规划建议：
- 合理设置刷新策略：根据数据更新频率选择刷新方式
- 考虑存储空间：物化视图会占用额外空间
- 监控性能：定期检查物化视图的大小和查询性能
更多学习教程公众号风哥教程itpux_com
Part03-生产环境项目实施方案
3.1 视图的创建与管理
3.1.1 创建基本视图
CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(100),
salary DECIMAL(10, 2),
hire_date DATE
);
— 插入测试数据
INSERT INTO fgedu_employees (name, department, salary, hire_date) VALUES
(‘张三’, ‘技术部’, 10000.00, ‘2024-01-01’),
(‘李四’, ‘市场部’, 8000.00, ‘2024-02-01’),
(‘王五’, ‘技术部’, 12000.00, ‘2024-03-01’),
(‘赵六’, ‘财务部’, 9000.00, ‘2024-04-01’),
(‘钱七’, ‘技术部’, 11000.00, ‘2024-05-01’);
— 创建视图
CREATE VIEW fgedu_tech_employees AS
SELECT
id,
name,
department,
salary,
hire_date
FROM fgedu_employees
WHERE department = ‘技术部’;
— 查询视图
SELECT * FROM fgedu_tech_employees;
3.1.2 创建复杂视图
CREATE TABLE fgedu_departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager VARCHAR(100)
);
— 插入部门数据
INSERT INTO fgedu_departments (name, manager) VALUES
(‘技术部’, ‘张三’),
(‘市场部’, ‘李四’),
(‘财务部’, ‘赵六’);
— 创建带关联的复杂视图
CREATE VIEW fgedu_employee_details AS
SELECT
e.id,
e.name AS employee_name,
e.department,
d.manager,
e.salary,
e.hire_date
FROM fgedu_employees e
JOIN fgedu_departments d ON e.department = d.name;
— 查询复杂视图
SELECT * FROM fgedu_employee_details;
from PostgreSQL视频:www.itpux.com
3.1.3 视图的更新与删除
CREATE OR REPLACE VIEW fgedu_tech_employees AS
SELECT
id,
name,
department,
salary,
hire_date,
EXTRACT(YEAR FROM CURRENT_DATE) – EXTRACT(YEAR FROM hire_date) AS years_of_service
FROM fgedu_employees
WHERE department = ‘技术部’;
— 删除视图
DROP VIEW IF EXISTS fgedu_tech_employees;
— 重新创建视图
CREATE VIEW fgedu_tech_employees AS
SELECT * FROM fgedu_employees WHERE department = ‘技术部’;
3.2 物化视图的创建与配置
3.2.1 创建物化视图
CREATE MATERIALIZED VIEW fgedu_department_salary_summary AS
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary,
SUM(salary) AS total_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM fgedu_employees
GROUP BY department;
— 查询物化视图
SELECT * FROM fgedu_department_salary_summary;
3.2.2 刷新物化视图
REFRESH MATERIALIZED VIEW fgedu_department_salary_summary;
— 增量刷新物化视图（PostgreSQL 9.4+）
— 注意：需要在创建时指定WITH DATA和UNIQUE INDEX
CREATE MATERIALIZED VIEW fgedu_department_salary_summary WITH (timescaledb.continuous)
AS
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM fgedu_employees
GROUP BY department
WITH DATA;
— 增量刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY fgedu_department_salary_summary;
学习交流加群风哥QQ113257174
Part04-生产案例与实战讲解
4.1 视图实战案例
4.1.1 权限控制视图
CREATE TABLE fgedu_employees_full (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(100),
salary DECIMAL(10, 2),
hire_date DATE,
personal_email VARCHAR(100),
phone_number VARCHAR(20)
);
— 插入测试数据
INSERT INTO fgedu_employees_full (name, department, salary, hire_date, personal_email, phone_number) VALUES
(‘张三’, ‘技术部’, 10000.00, ‘2024-01-01’, ‘zhangsan@fgedu.net.cn’, ‘13800138001’),
(‘李四’, ‘市场部’, 8000.00, ‘2024-02-01’, ‘lisi@fgedu.net.cn’, ‘13800138002’),
(‘王五’, ‘技术部’, 12000.00, ‘2024-03-01’, ‘wangwu@fgedu.net.cn’, ‘13800138003’);
— 创建公开视图（不包含敏感信息）
CREATE VIEW fgedu_employees_public AS
SELECT
id,
name,
department,
hire_date
FROM fgedu_employees_full;
— 创建管理视图（包含所有信息）
CREATE VIEW fgedu_employees_admin AS
SELECT * FROM fgedu_employees_full;
— 授予不同用户不同视图的权限
— GRANT SELECT ON fgedu_employees_public TO regular_user;
— GRANT SELECT ON fgedu_employees_admin TO admin_user;
— 查询不同视图
SELECT * FROM fgedu_employees_public;
SELECT * FROM fgedu_employees_admin;
4.1.2 报表视图
CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
sale_date DATE NOT NULL,
product_id INTEGER,
quantity INTEGER,
unit_price DECIMAL(10, 2),
customer_id INTEGER
);
— 插入测试数据
INSERT INTO fgedu_sales (sale_date, product_id, quantity, unit_price, customer_id) VALUES
(‘2026-01-01’, 1, 10, 100.00, 1),
(‘2026-01-02’, 2, 5, 200.00, 2),
(‘2026-02-01’, 1, 15, 100.00, 1),
(‘2026-02-02’, 3, 8, 150.00, 3),
(‘2026-03-01’, 2, 12, 200.00, 2);
— 创建月度销售报表视图
CREATE VIEW fgedu_monthly_sales_report AS
SELECT
EXTRACT(YEAR FROM sale_date) AS year,
EXTRACT(MONTH FROM sale_date) AS month,
COUNT(*) AS order_count,
SUM(quantity) AS total_quantity,
SUM(quantity * unit_price) AS total_amount,
AVG(quantity * unit_price) AS average_order_amount
FROM fgedu_sales
GROUP BY year, month
ORDER BY year, month;
— 查询报表视图
SELECT * FROM fgedu_monthly_sales_report;
风哥提示：视图可以很好地封装报表逻辑，使报表查询更加简洁明了。
4.2 物化视图实战案例
4.2.1 数据仓库物化视图
CREATE TABLE fgedu_orders (
id SERIAL PRIMARY KEY,
order_date TIMESTAMP NOT NULL,
customer_id INTEGER,
total_amount DECIMAL(10, 2),
status VARCHAR(20)
);
— 插入大量测试数据（模拟生产环境）
INSERT INTO fgedu_orders (order_date, customer_id, total_amount, status)
SELECT
NOW() – (random() * 365)::interval,
(random() * 1000)::integer + 1,
(random() * 10000)::decimal(10, 2),
(ARRAY[‘pending’, ‘completed’, ‘cancelled’])[(random() * 3)::integer + 1]
FROM generate_series(1, 100000);
— 创建物化视图（按日期汇总）
CREATE MATERIALIZED VIEW fgedu_daily_order_summary AS
SELECT
DATE_TRUNC(‘day’, order_date) AS order_day,
COUNT(*) AS order_count,
SUM(total_amount) AS total_sales,
AVG(total_amount) AS average_order,
COUNT(CASE WHEN status = ‘completed’ THEN 1 END) AS completed_count,
COUNT(CASE WHEN status = ‘cancelled’ THEN 1 END) AS cancelled_count
FROM fgedu_orders
GROUP BY order_day
ORDER BY order_day;
— 查询物化视图（快速获取汇总数据）
SELECT * FROM fgedu_daily_order_summary
WHERE order_day >= ‘2026-01-01’ AND order_day < '2026-02-01';
-- 刷新物化视图
REFRESH MATERIALIZED VIEW fgedu_daily_order_summary;
4.2.2 定期刷新物化视图
CREATE OR REPLACE FUNCTION fgedu_refresh_materialized_views()
RETURNS void AS $$
BEGIN
— 刷新销售汇总物化视图
REFRESH MATERIALIZED VIEW fgedu_department_salary_summary;
— 刷新订单汇总物化视图
REFRESH MATERIALIZED VIEW fgedu_daily_order_summary;
— 刷新月度销售报表物化视图
— REFRESH MATERIALIZED VIEW fgedu_monthly_sales_report;
RAISE NOTICE ‘Materialized views refreshed successfully’;
END;
$$ LANGUAGE plpgsql;
— 执行刷新
SELECT fgedu_refresh_materialized_views();
— 可以通过cron作业定期执行
— 例如：每天凌晨2点刷新
— 0 2 * * * psql -d fgedudb -c “SELECT fgedu_refresh_materialized_views();”
更多视频教程www.fgedu.net.cn
Part05-风哥经验总结与分享
5.1 视图与物化视图的性能优化
5.1.1 视图性能优化
- 简化视图逻辑：避免在视图中使用复杂的聚合和关联
- 合理使用索引：确保视图底层的表有适当的索引
- 避免嵌套视图：尽量避免视图中引用其他视图
- 使用物化视图：对于频繁查询的复杂视图，考虑使用物化视图
5.1.2 物化视图性能优化
- 选择合适的刷新策略：根据数据更新频率选择全量或增量刷新
- 合理设置刷新时间：选择系统负载较低的时间刷新
- 使用索引：为物化视图创建适当的索引
- 监控物化视图大小：定期检查并清理过大的物化视图
学习交流加群风哥微信: itpux-com
5.2 常见问题与解决方案
5.2.1 视图常见问题
| 问题 | 解决方案 |
|---|---|
| 视图查询性能差 | 检查底层表的索引，考虑使用物化视图 |
| 视图更新失败 | 确保视图满足可更新条件，或使用INSTEAD OF触发器 |
| 视图依赖的表结构变更 | 及时更新视图定义，确保与底层表结构匹配 |
5.2.2 物化视图常见问题
| 问题 | 解决方案 |
|---|---|
| 物化视图数据过期 | 设置定期刷新策略，确保数据及时更新 |
| 刷新时间过长 | 使用增量刷新，或优化底层查询 |
| 存储空间不足 | 定期清理不必要的物化视图，或使用表空间管理 |
更多学习教程公众号风哥教程itpux_com
5.2.3 最佳实践总结
- 根据使用场景选择合适的视图类型
- 对于简单的查询封装，使用普通视图
- 对于复杂的汇总查询，使用物化视图
- 定期维护视图和物化视图，确保性能稳定
- 监控视图的使用情况，及时调整优化策略
from PostgreSQL视频:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
