摘要： 本文介绍 GaussDB(DWS)的数据库视图对象，我们将讨论使用数据库视图的优点和缺点，以及如何使用系统视图解决一些问题。
本文分享自华为云社区《GaussDB(DWS) SQL进阶之数据库对象 - 视图》，作者：黎明的风 。
（一）视图概述
当用户对数据库中的一张或者多张表的某些字段的组合感兴趣，而又不想每次键入这些查询时，用户就可以定义一个视图，以便解决这个问题。视图中列可以来自于表里的不同列，这些列都是用户所感兴趣的数据列。
视图与表不同，它在物理上不是真实存在的，而是一个虚表。在数据库里仅存放视图的定义，而不存放视图对应的数据。视图中的这些数据存放在其对应的表中，如果表中的数据发生了变化，从视图中查询出的数据也会随之发生改变。从这个意义来看，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及变化。每一次查看视图或引用视图的的时候，都会运行一次视图上的查询。
用户可以使用 SELECT 语句从视图里查询数据，对于符合一定约束条件的视图，还可以使用 INSERT、UPDATE、DELETE、MERGE INTO 等语句修改视图对应的基础表里的数据。视图在提供操作方便的同时，还可以保障数据库数据的安全。
（二）数据库视图的优点
1.数据库视图可以简化复杂的查询
数据库视图由许多基础表相关联的 SQL 语句定义，可以使用数据库视图向最终用户和外部应用程序隐藏底层表的复杂性。通过数据库视图，只需要使用简单的 SQL 语句，不需要编写具有许多连接的复杂语句。
2.数据库视图有助于限制对特定用户的数据访问。
如果不希望所有用户都可以查询敏感数据，就可以使用数据库视图仅向特定用户组公开非敏感数据。
3.数据库视图提供了额外的安全层。
安全性是任何关系数据库管理系统的重要组成部分，数据库视图为数据库管理系统提供了额外的安全性。数据库视图允许创建只读视图以向特定用户公开只读数据，用户只能在只读视图中检索数据，但不能对其进行更新。
4.数据库视图可以定义计算列。
数据库表中不应该有计算列，但是数据库视图支持有计算列。假设在订单表中有订购产品的数量和每个产品的价格列，但是订单表定义一列来存储每个订单的总销售额。如果有，这样的数据库模式也不是一个好的设计。在这种情况下，可以创建一个名为总销售额的列， 它是计算结果是产品的价格乘以订购产品的数量。当从数据库视图查询数据时，计算列的数据会动态进行计算。
5.数据库视图支持应用兼容性
假设有一个核心数据库，许多应用程序都在使用它，为了适应新的业务需求，有可能会重新设计数据库，删除了一些表并创建了几个新表，修改表的列名，此时并不希望这些更改影响之前的应用程序。在这种情况下，可以使用与已删除的旧表相同的表结构创建数据库视图。应用程序可以访问视图来完成此前功能，这样就无需对应用程序做任何的修改。
（三）数据库视图的缺点
除了上述优点外，使用数据库视图有以下几个缺点：
1.性能可能会较差
从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。
2.视图对表结构的依赖
由于视图时根据数据库的基础表创建的，每当更改与视图关联的那些表的结构时，也必须更改视图。
（四）创建视图的语法
创建视图需要使用 CREATE VIEW 语句，其语法格式如下：
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ] [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ] AS query;
复制代码
语法中的 CREATE 表示创建，OR REPLACE 用于替换已经创建的视图，TEMP 或 TEMPORARY 表示创建临时视图，view_name 是要创建的名字字符串，column_name 表示属性列的名字，query 表示为视图提供行和列的 SELECT 查询语句或 VALUES 语句，WITH 子句可以为视图指定一个可选的参数，目前支持的参数为 security_barrier，当 VIEW 试图提供行级安全时，应使用该参数。
下面是视图的基本操作示例：
--创建字段spcname为pg_default组成的视图。test=# CREATE VIEW myView AS SELECT * FROM pg_tablespace WHERE spcname = 'pg_default';
--查看视图。test=# SELECT * FROM myView ;
--删除视图myView。test=# DROP VIEW myView;
复制代码
（五）可更新视图
在 GaussDB(DWS)上，当开启视图可更新参数（enable_view_update）后，系统允许对简单视图使用 INSERT，UPDATE、DELETE 和 MERGE INTO 语句进行更新，满足以下所有条件的视图可进行更新：
视图定义的 FROM 语句中只能有一个普通表，不能是系统表、外表、dfs 表、delta 表、toast 表、错误表。
视图中包含可更新的列，这些列是对基础表可更新列的简单引用。
视图定义不能包含 WITH、DISTINCT、GROUP BY、ORDER BY、FOR UPDATE、FOR SHARE、HAVING、TABLESAMPLE、LIMIT、OFFSET 子句。
视图定义不能包含 UNION、INTERSECT、EXCEPT 集合操作。
视图定义的选择列表不能包含聚集函数、窗口函数、返回集合的函数。
视图上不能有触发时机为 INSTEAD OF 的触发器。
视图定义不能包含子链接。
视图定义不能包含属性为 VOLATILE 的函数（函数值可以在一次表扫描内改变的函数）
视图定义不能对表的分布键所在列起别名，或将普通列起别名为分布键列名。
视图更新操作中包含 RETURNING 子句时，视图定义中的列只能来自于基础表。
如果可更新的视图定义包含 WHERE 条件，则该条件将会限制 UPDATE 和 DELETE 语句修改基础表上的行。如果 UPDATE 语句更改行后不再满足 WHERE 条件，更新后通过视图将无法查询到。类似地如果 INSERT 命令插入了不满足 WHERE 条件的数据，插入后通过视图将无法查询到。在视图上执行插入、更新或删除的用户必须在视图和表上具有相应的插入、更新或删除权限。
以下是对可更新视图的进行更新操作的例子：
test=# create view v1 as select * from t1;CREATE VIEWtest=# insert into v1 values(1, 2, 3);INSERT 0 1test=# delete from v1 where a = 1;DELETE 3test=# update v1 set b = 100 where a = 2;UPDATE 2test=# delete from v1 where a = 2;DELETE 2
复制代码
以下是可更新视图执行 MERGE INTO 语句的例子：
test=# CREATE TABLE products(product_id INTEGER, product_name VARCHAR2(60), category VARCHAR2(60));NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'product_id' as the distribution column by default.HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.CREATE TABLEtest=# CREATE VIEW products_view AS SELECT * FROM products;CREATE VIEWtest=# CREATE TABLE newproducts(product_id INTEGER, product_name VARCHAR2(60), category VARCHAR2(60));NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'product_id' as the distribution column by default.HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.CREATE TABLEtest=# CREATE VIEW newproducts_view AS SELECT * FROM newproducts;CREATE VIEWtest=# INSERT INTO products_view VALUES (1501, 'vivitar 35mm', 'electrncs');INSERT 0 1test=# INSERT INTO products_view VALUES (1502, 'olympus is50', 'electrncs');INSERT 0 1test=# INSERT INTO products_view VALUES (1600, 'play gym', 'toys');INSERT 0 1test=# INSERT INTO products_view VALUES (1601, 'lamaze', 'toys');INSERT 0 1test=# INSERT INTO products_view VALUES (1666, 'harry potter', 'dvd');INSERT 0 1test=# INSERT INTO newproducts_view VALUES (1502, 'olympus camera', 'electrncs');INSERT 0 1test=# INSERT INTO newproducts_view VALUES (1601, 'lamaze', 'toys');INSERT 0 1test=# INSERT INTO newproducts_view VALUES (1666, 'harry potter', 'toys');INSERT 0 1test=# INSERT INTO newproducts_view VALUES (1700, 'wait interface', 'books');INSERT 0 1MERGE INTO products_view pUSING newproducts_view npON (p.product_id = np.product_id)WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'WHEN NOT MATCHED THEN INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';MERGE 4test=# SELECT * FROM products_view ORDER BY 1; product_id | product_name | category ------------+----------------+----------- 1501 | vivitar 35mm | electrncs 1502 | olympus camera | electrncs 1600 | play gym | toys 1601 | lamaze | toys 1666 | harry potter | toys 1700 | wait interface | books(6 rows)
复制代码
（六）GaussDB(DWS)常用系统视图
GaussDB(DWS)还提供了许多视图用于展示数据库的内部状态，以下几个视图，在定位故障时会经常使用。
test=# select datid, pid, application_name, query_id, query from pg_stat_activity; datid | pid | application_name | query_id | query -------+-----------------+--------------------+--------------------+----------------------------------------------------------------------------- 14950 | 139706178189056 | JobScheduler | 0 | 14950 | 139706093266688 | WDRSnapshot | 0 | 14950 | 139706040301312 | workload | 100768041662414941 | WLM fetch collect info from data nodes 14950 | 139705995208448 | CalculateSpaceInfo | 0 | 14950 | 139705978427136 | WorkloadMonitor | 100768041662414940 | WLM monitor update and verify local info 14950 | 139705953277696 | WLMArbiter | 0 | WLM arbiter sync info by CCN and CNs 16390 | 139705917097728 | gsql | 100768041662414942 | select datid, pid, application_name, query_id, query from pg_stat_activity;(7 rows)
复制代码
test=# select * from pg_thread_wait_status; node_name | db_name | thread_name | query_id | tid | lwtid | ptid | tlevel | smpid | wait_status | wait_event --------------+----------+--------------------+--------------------+-----------------+--------+------+--------+-------+-------------+------------ coordinator1 | postgres | JobScheduler | 0 | 139706178189056 | 128830 | | 0 | 0 | none | coordinator1 | postgres | WDRSnapshot | 0 | 139706093266688 | 128834 | | 0 | 0 | none | coordinator1 | postgres | workload | 100768041662415325 | 139706040301312 | 128837 | | 0 | 0 | none | coordinator1 | postgres | CalculateSpaceInfo | 0 | 139705995208448 | 128838 | | 0 | 0 | none | coordinator1 | postgres | WorkloadMonitor | 100768041662415251 | 139705978427136 | 128839 | | 0 | 0 | none | coordinator1 | postgres | WLMArbiter | 0 | 139705953277696 | 128840 | | 0 | 0 | none | coordinator1 | test | gsql | 100768041662415326 | 139705917097728 | 129109 | | 0 | 0 | none | coordinator1 | | Background writer | 0 | 139706242688768 | 128826 | | 0 | 0 | none | coordinator1 | | CheckPointer | 0 | 139706262091520 | 128825 | | 0 | 0 | none | coordinator1 | | Wal Writer | 0 | 139706225907456 | 128827 | | 0 | 0 | none | coordinator1 | | TwoPhase Cleaner | 0 | 139706076485376 | 128835 | | 0 | 0 | none | coordinator1 | | LWLock Monitor | 0 | 139706057082624 | 128836 | | 0 | 0 | none | (12 rows)
复制代码
test=# select locktype, database, relation, pid, mode from pg_locks; locktype | database | relation | pid | mode ------------+----------+----------+-----------------+----------------- relation | 16390 | 11800 | 139705917097728 | AccessShareLock virtualxid | | | 139705917097728 | ExclusiveLock virtualxid | | | 139705953277696 | ExclusiveLock virtualxid | | | 139705978427136 | ExclusiveLock virtualxid | | | 139706040301312 | ExclusiveLock(5 rows)
复制代码
test=# select node_name, node_type, node_port, node_host from pgxc_node; node_name | node_type | node_port | node_host --------------+-----------+-----------+----------- coordinator1 | C | 56200 | localhost datanode1 | D | 56201 | localhost datanode2 | D | 56202 | localhost datanode3 | D | 56203 | localhost datanode4 | D | 56204 | localhost(5 rows)
复制代码
想了解 GuassDB(DWS)更多信息，欢迎微信搜索“GaussDB DWS”关注微信公众号，和您分享最新最全的 PB 级数仓黑科技，后台还可获取众多学习资料哦~
点击关注，第一时间了解华为云新鲜技术~
评论