1. 云图网首页
  2. 技术专区
  3. 编程笔记

带你认识数据库视图对象，下次不要再认成“表”了

• 编程笔记

摘要：本文介绍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 VIEW
test=# insert into v1 values(1, 2, 3);
INSERT 0 1
test=# delete from v1 where a = 1;
DELETE 3
test=# update v1 set b = 100 where a = 2;
UPDATE 2
test=# 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 TABLE
test=# CREATE VIEW products_view AS SELECT * FROM products;
CREATE VIEW
test=# 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 TABLE
test=# CREATE VIEW newproducts_view AS SELECT * FROM newproducts;
CREATE VIEW
test=# INSERT INTO products_view VALUES (1501, 'vivitar 35mm', 'electrncs');
INSERT 0 1
test=# INSERT INTO products_view VALUES (1502, 'olympus is50', 'electrncs');
INSERT 0 1
test=# INSERT INTO products_view VALUES (1600, 'play gym', 'toys');
INSERT 0 1
test=# INSERT INTO products_view VALUES (1601, 'lamaze', 'toys');
INSERT 0 1
test=# INSERT INTO products_view VALUES (1666, 'harry potter', 'dvd');
INSERT 0 1
test=# INSERT INTO newproducts_view VALUES (1502, 'olympus camera', 'electrncs');
INSERT 0 1
test=# INSERT INTO newproducts_view VALUES (1601, 'lamaze', 'toys');
INSERT 0 1
test=# INSERT INTO newproducts_view VALUES (1666, 'harry potter', 'toys');
INSERT 0 1
test=# INSERT INTO newproducts_view VALUES (1700, 'wait interface', 'books');
INSERT 0 1
MERGE INTO products_view p
USING newproducts_view np
ON (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 4
test=# 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)还提供了许多视图用于展示数据库的内部状态，以下几个视图，在定位故障时会经常使用。

  • pg_stat_activity：用于查询当前实例上各个Session的状态
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)
  • pg_thread_wait_status：用于查询该实例上各个线程的等待事件
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)
  • pg_locks：用于查询当前实例上的锁状态
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)
  • pgxc_node：用于显示集群中所有实例的IP和端口号
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级数仓黑科技，后台还可获取众多学习资料哦~

 

点击关注，第一时间了解华为云新鲜技术~

{{o.name}}


{{m.name}}

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/145928.html

赞 (0)
0
上一篇 2021年9月8日
下一篇 2021年9月8日

相关推荐

发表回复

登录后才能评论