七的博客

PostgreSQL11快速入门(九)-视图

PostgreSQL

PostgreSQL11快速入门(九)-视图

这个章节主要讲的是跟视图相关内容，视图也是部分传统行业项目里面用的比较多的一个特性。在实际的项目开发中，还是有视图的用武之地。

1. 什么是视图

  • 视图是一张基于普通的 SQL 语句的结果集产生的虚拟表。
  • 视图本身不存储数据，每次查询视图时才会去动态生成数据。
  • 视图从一张或者多张数据库表中抽取数据。

2. 视图的应用场景

看了上面视图的描述，可能没有感受到视图跟普通的 SQL 或者函数有什么明显优势的地方。 视图在实际项目中的应用场景有这些：

  • 隐藏底层数据表的复杂性 ： 假设某个业务查询使用非常复杂的 SQL 进行数据查询，对于业务方来说其实是比较复杂的，特别是对数模不熟悉的开发人员。 要花很多时间去熟悉数模，但是如果由业务人员或者是高级开发人员定义好视图，其他人直接写简单的 SQL 去查询视图的话，那么开发就会变得很简单。

  • 查询逻辑共享：在一个多项目团队共享数据库的场景下，不同业务线可能都会重复去查询一部分相同的数据，如果将这些重复查询抽象成视图的话，查询逻辑就可以高度复用。

  • 提高数据安全性：在一些数据敏感的项目中，有一部分数据是核心数据，不能对开发团队或者普通员工接触到，那么就可以使用视图来查询不敏感的字段数据，同时原始敏感数据表不对外直接访问。

  • 外部数据集成： 在 PG 中，是支持 dblink 去访问外部的 PG 数据库。 试想下项目中需要从另外一个 PG 数据库中去查询数据，那么这个时候就可以写一个视图去查询外部的数据库，仿佛外部数据库的数据就存在当前 PG 数据库中一样，对用户的查询来说完全感知不到是外部数据库的数据。

  • 视图结果不变性： 视图可以屏蔽数据库表的结构变化 , 即使数据库表的结构发生了改变 , 只要视图的定义不变 , 那么用户查询视图的结果就不会受到影响。

3. 怎么使用视图

3.1 创建视图

创建视图的基本语法如下:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

上面的语法如下：

  • view_name 是视图的名称。
  • SELECT 语句指定视图的查询逻辑。
  • FROM 子句指定查询的基表。
  • WHERE 子句指定查询条件。

可以发现，跟普通的查询 SQL 差异就在于在前面多了 CREATE VIEW view_name AS ， 差别就是这么小。

比如我们可以创建一个视图 , 查询所有已激活的电表信息:

CREATE VIEW active_meters AS

SELECT m.id, m.serial_number, m.model, m.installation_date
FROM meters m
WHERE m.status = 'active';

这个视图就是查询一张表 , 查询状态为 ‘active’ 的电表信息。

那么业务中需要查询所有已激活的电表信息的时候，直接执行:

3.2 查询视图

查询视图的语法与查询普通表相同 , 可以在 SELECT 语句中直接引用视图名称，比如查询上面创建的视图：

select * from active_meters;

这个查询就能拿到所有已经激活的电表信息。当然上面只是一个很简单的 SQL 查询，可以想象如果 SQL 逻辑很复杂的话，但是有了视图的定义后，直接通过一条很简单的 SQL 就可以查询到数据是多么的便利。

3.3 修改视图

使用 CREATE OR REPLACE VIEW 语句来修改已有的视图定义，简单点说就是如果没有就创建，存在就直接修改:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

例如,我们可以修改 active_meters 视图 , 增加一个字段表示电表所属的客户名字:

SELECT m.id, m.serial_number, m.model, m.installation_date , m.customer_name
FROM meters m
WHERE m.status = 'active';

这个视图在原有的基础上,增加了一个 customer_name字段 , 表示电表的所属的客户。

3.4 删除视图

删除视图的语法如下:

DROP VIEW view_name;

删除 active_meters 视图:

DROP VIEW active_meters;

4. 复杂视图例子

下面通过一个例子 , 演示下在使用视图后，对于查询方实现电表数据分析的功能将会变得多么简单 ( 写视图的人肯定不会觉得特别简单，还是需要熟悉数模的 )。

假设要统计每个客户的电表数量、平均用电量和总用电量 , 这些查询都是分别要从几张表关联，不能直接单表查询出来。具体的业务含义可以不用管，同时数据库表名以及字段名等已经做过改写，并非实际项目中用到的。

创建以下几个视图:

  • 统计每个客户的电表数量视图:
CREATE VIEW customer_meter_count AS
SELECT c.id AS customer_id, c.name AS customer_name, COUNT(m.id) AS meter_count
FROM customers c
LEFT JOIN meters m ON c.id = m.customer_id
GROUP BY c.id, c.name;
  • 统计每个客户的平均用电量视图:
CREATE VIEW customer_avg_usage AS
SELECT c.id AS customer_id, c.name AS customer_name, AVG(r.reading) AS avg_usage
FROM customers c
JOIN meters m ON c.id = m.customer_id
JOIN meter_readings r ON m.id = r.meter_id
GROUP BY c.id, c.name;
  • 统计计算每个客户的总用电量视图:
CREATE VIEW customer_total_usage AS
SELECT c.id AS customer_id, c.name AS customer_name, SUM(r.reading) AS total_usage
FROM customers c
JOIN meters m ON c.id = m.customer_id
JOIN meter_readings r ON m.id = r.meter_id
GROUP BY c.id, c.name;

有了这些视图 , 查询客户的电表统计信息就非常简单了。查询客户 ID 为 1062 的电表数量、平均用电量和总用电量的 SQL 如下：

SELECT c.customer_name, c.meter_count, a.avg_usage, t.total_usage
FROM customer_meter_count c
JOIN customer_avg_usage a ON c.customer_id = a.customer_id
JOIN customer_total_usage t ON c.customer_id = t.customer_id
WHERE c.customer_id = 1062;

通过将常用的查询逻辑封装在视图中 , 对后续的一些数据分析的 SQL 查询可以简化很多。当然上面的例子只是为了演示下视图在实际项目场景中的使用，具体还是要根据项目场景进行合理使用。

5. 视图存在的问题

视图虽然存在实用的地方，但是也存在很多需要注意的地方：

  • 性能问题: 因为视图每次查询时都要动态生成数据，在数据量大的项目里面要非常小心，因为查询视图可能会比直接查询基础表更慢，在项目开发初期就要提前预估视图可能随着数据量而性能下降的问题。

  • 权限和安全: 如果视图的安全控制不当，可能会无意中暴露敏感数据。这一点在一些涉及到敏感用户信息的项目更需要注意，数据权限一定要控制好。

6. 参考链接

◀ 上一篇
PostgreSQL11快速入门(八)-函数和存储过程
下一篇 ▶
重构项目随笔