likes
comments
collection
share

PostgreSQL技术问答34 - View视图本文讨论了一个SQL数据库的标准特性-View视图。涉及了对视图的理解

作者站长头像
站长
· 阅读数 27

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是在Postgres中，一个比较核心和重要的功能： View - 视图。

什么是View视图

View，中文意为视图，是SQL关系数据库的标准概念和功能。这里的视图，是相对数据表而言的一种逻辑概念，它提供了一种数据检视的方式，就是看起来像表，其呈现和数据结构也和表相同(下图)，但它实际上不是一个表，而是一个查询的结果。

PostgreSQL技术问答34 - View视图本文讨论了一个SQL数据库的标准特性-View视图。涉及了对视图的理解

所以，视图定义的内容，是一个标准的查询。当在数据库中，像普通数据表一样使用视图的时候(通常是作为数据源进行查询)时，数据库系统会在后端执行这个视图的查询，并返回结果集作为新查询的数据源，然后执行最终的查询。从这个简单的工作原理我们可以看到视图的一些特点包括：

  • 视图的定义不是一般的行和列，而是(也只能是)一个查询语句
  • 视图的呈现方式是一个表，也可以包括数据列和数据行，但它不实际存储任何数据
  • 由于只是一个查询的结果，所以视图在逻辑上是只读的
  • 视图使用的时候，就会实际执行查询
  • 查询的类型基本没有什么限制，可以是简单查询，也可以是复杂查询包括表关联、条件过滤、记录集合并等，只要结果是一个标准记录集的形式就可以

为什么要提出视图这个特性

视图的出现，是因为在数据库应用中，有一些通用的需求，而视图能够很好和优雅的解决这些问题。视图的优点和一些实用的场景包括：

  • 简化查询

使用视图，对于最终用户而言，可以隐藏其定义的复杂性。开发者可以像使用一个表一样使用视图，而不需要重新编写查询语句。

  • 灵活性

使用视图，可以在一定程度上提高数据定义的灵活性，并向使用方隐藏其复杂性。例如可以在定义视图时增加或者减少字段，进行数据处理、转换和变形，对结果进行过滤等等。

  • 性能

在某些条件下，视图可以帮助提高信息查询的性能。因为视图的定义可以对查询结果进行限制，包括在字段的维度和记录集合的维度，这样基于这个逻辑表的查询，能够被事先限定在一个范围之内，避免由于查询编写不当造成的性能问题。

  • 提升安全性

视图定义的逻辑表内容是不能修改的，这样暴露给只需要进行查询操作的应用程序，可以避免其无意或者有意的进行修改的操作。另外，基于视图来定义授权方式，也可以避免用户直接访问原始表带来的安全问题。如定义某些用户和应用，只能访问为其准备的视图，还可以向其隐藏数据库的其他结构。

  • 保持结构

使用视图，可以在输出时候，保持和固定一种字段结构，当表的定义修改的时候，只需要对视图进行调整，就可以保证原来的结构，而不需要在应用程序中进行调整。这样可以简化应用的演进和维护工作。

当然，视图技术也有一些问题，开发者在使用的时候，需要知晓和了解。

  • 性能

视图本质上是一个查询的结果集，并没有在性能上进行优化，而且如果基于视图进行查询，就会产生由于查询的嵌套，导致的性能降低。

  • 维护

使用视图之前，需要进行创建。在使用的过程中，还需要注意视图的定义，是否满足和符合业务的需求，这也需要维护，这些都会带来操作和管理的复杂性。

如何创建和使用视图

在使用之前，需要先创建和定义视图。创建视图的标准形式和如下：

    CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

简单的形式就是 create view name as query。这里简单总结一下：

  • create view 作为创建视图的语句声明
  • 必须指定一个视图的名称，在数据库内部不能是关键字或者和其他数据库对象冲突
  • as 作为关键字，后跟实际的查询语句
  • 查询语句，基本上不受什么限制

使用视图就更简单了，和使用正常的表没有什么区别，可以当作查询的标准数据源使用。只需要知晓这个表是不能被修改的。

这就是一个视图的基本的创建工作。这里面还有一些选项和高级的设置，我们也来简单了解一下：

  • 可选 or replace

这个操作可以替换当前定义(可以省略drop操作)，但使用Replace有一个限制，就是视图结构不能改变(查询语言的内容可以改变，但输入的记录集字段列表不变)，这个设计，可能是考虑到一些编译和依赖的问题

  • Temp/Temporary

创建临时视图，就是只在当前会话内有效的视图定义。使用这个选项定义的视图对象，将在会话结束时被清理和删除。

  • Recursive

可以创建一个“递归”的视图，本质上是一个CTE递归查询的视图形式。下面是一个简单的例子：


CREATE RECURSIVE VIEW nums_1_100 (n) AS VALUES (1)
UNION ALL  SELECT n+1 FROM nums_1_100 WHERE n < 100;

-- 使用视图
select * from nums_1_100;

看这个视图的定义，稍微觉得有点奇怪，类似于函数的写法。但实际上这个是CTE定义的一种变形。按照官方技术文档的解释，递归视图定义等效于：

CREATE VIEW [ schema . ] view_name AS 
WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name;
  • WITH ( view_option_name [= view_option_value] [, ... ] )

可以设置一些视图选项，包括： check_option( enum)，更新检查选项(同Check Option)；security_barrier( boolean), 是否提供行级安全性；security_invoker( boolean)，设置是否根据视图用户而非所有者的权限检查底层基础关系。

  • WITH [ CASCADED | LOCAL ] CHECK OPTION

该选项控制自动更新视图的行为。也就是说，如果视图定义中，包括了修改视图的选项，则需要选择是否保证修改后的记录会出现在视图查询的结果当中。

举个例子

下面是一个简单的示例

-- 原始数据表
defaultdb=> select * from gcode;
   id   | pid  |  name  
--------+------+--------
     11 |    0 | 北京市
 110108 |   11 | 海淀区
     51 |    0 | 四川省
   5101 |   51 | 成都市
 510104 | 5101 | 锦江区
 510107 | 5101 | 青羊区
   5103 |   51 | 自贡市
   5107 |   51 | 绵阳市
 510704 | 5107 | 游仙区
(9 rows)

-- 创建视图
defaultdb=> create or replace view gcode_sc as select id, pid, name from gcode where id = 51 or pid = 51 or (pid / 100 = 51);
CREATE VIEW

-- 使用视图
defaultdb=> select * from gcode_sc where pid > 51;
   id   | pid  |  name  
--------+------+--------
 510104 | 5101 | 锦江区
 510107 | 5101 | 青羊区
 510704 | 5107 | 游仙区
(3 rows)

-- 删除视图
defaultdb=> DROP VIEW IF EXISTS gcode_sc;
DROP VIEW

如何检查已经存在的视图

在psql中，可以使用 \dv指令，检查当前存在的视图对象。

如果使用标准的SQL语句执行环境，可以使用系统对象视图pg_views来进行查询：


SELECT viewname, view_definition, view_definition_only
FROM pg_views
WHERE schemaname = 'public' AND viewname = 'my_view';

我们也可以使用对象在pg_views和pg_tables这些系统表中的存在性，来检查一个对象，是否是视图或者标准的数据表。

如何重命名视图，修改视图定义和删除视图

  • 重命名视图，可以使用alter view ... rename to ... 语句：

    ALTER VIEW viewName RENAME TO new_viewName;

  • 删除视图，可以使用 drop view 语句，建议增加 if exists 选项以避免抛出错误：

    DROP VIEW viewName;

此外，如果有视图的嵌套或者依赖的话，删除视图可能会出现错误，所以需要先解除这些依赖，如先删除依赖的视图。

  • 修改视图定义的内容

当然可以先删除视图，然后重新创建。为了方便操作，Postgres也提供了在创建时的 or replace 选项，可以直接修改和替换原有的视图定义内容。但需要注意的是这种操作必须保证新的视图输出数据结构和原来的相同，否则会抛出错误。 笔者猜想，这应该是和Postgres在创建时，需要做一些编译和优化的工作相关。

视图能够使用索引吗？

视图的特点在PostgreSQL中，视图（view）是虚拟表，它基于一个或多个真实的表或其他视图创建。视图本身不存储数据，而是在需要时动态生成数据。

理论上不能在视图上创建索引，因为索引都是针对真实存在于物理存储上的地址。而视图只是一个查询动态生成的结果，使用时都是在内存中的，谈不上索引位置。

但是，可以通过在原表上创建索引，同样能够对视图生成和查询的性能带来影响，但需要注意视图所使用的查询，和原表中字段之间的关系，并创建适合的索引。

此外，如果使用实体化视图(另外讨论)，是可以使用索引来改善查询性能的。

视图可以使用其他的视图吗

当然，理论上视图就是一个逻辑和虚拟的表，使用查询当然可以基于视图。但在一般情况下，并不建议这样做，因为嵌套视图相当于嵌套查询，其解析和执行有一定的代价，也不利于维护和管理。

可更新视图是怎么回事？

虽然在逻辑上而言，视图是一个查询结果记录集，输出后就和原始数据表没有关联了。所以视图从语义和原理上而言，是不能也不应该可以更新的。但为了支持一些业务的需求，在数据库层面上，如果满足一定的前提条件，数据库也是允许应用通过视图来对实际的数据进行更新。

显然，这里应该有一些限制。在Postgres中，可以更新的视图应该满足下面一些条件和限制：

  • 简单视图，视图的列表中必须只有一个条目FROM，该条目必须是表或另一个可更新的视图
  • 定义的顶层不得包含WITH、DISTINCT、GROUP BY、HAVING、LIMIT或OFFSET
  • 不能在顶层包含集合操作(UNION.. 、INTERSECT或EXCEPT等)
  • 选择列表不得包含任何聚合、窗口函数或集合返回函数
  • 还有当前引用视图的账号，必须具备数据操作更新的权限

有些更新的过程，还可以通过视图定义中的设置进行控制，如前面提到的 [ CASCADED | LOCAL ] CHECK等。

此外，Postgres还支持“部分可更新”的视图，下面是一个来自官方文档的例子：

CREATE VIEW comedies AS
    SELECT f.*,
           country_code_to_name(f.country_code) AS country,
           (SELECT avg(r.rating)
            FROM user_ratings r
            WHERE r.film_id = f.id) AS avg_rating
    FROM films f
    WHERE f.kind = 'Comedy';

在这个视图中，如果进行更新操作，则相关联的films表中的记录是可以更新的，但不会影响到country和avg_rating这两个计算列。因为，这个视图定义，并不违反视图定义的限制。这样，就给视图的应用，带来了更高的灵活性。

有没有可以存储数据的视图，这样是不是性能要好一些

Postgres支持这种应用方式，就是所谓的实体化视图(MATERIALIZED VIEW)。由于相关的内容较多，笔者会在另一个章节中专门详细讨论，这篇博文的地址如下：

小结

本文讨论了一个SQL数据库的标准特性-View视图。涉及了对视图的理解，定义和使用的方式，使用的场景，限制和不足等，还讨论了可更新视图相关的问题。

转载自:https://juejin.cn/post/7405182512451338252
评论
请登录