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

举报
黎明的风 发表于 2021/09/03 20:10:06 2021/09/03
【摘要】 本文介绍GaussDB(DWS)的数据库视图对象，我们将讨论使用数据库视图的优点和缺点，以及如何使用系统视图解决一些问题。 

1. 前言

  • 适用版本：【8.1.0（及以上）】

本文介绍GaussDB(DWS)的数据库视图对象，我们将讨论使用数据库视图的优点和缺点，以及如何使用系统视图解决一些问题。

2. 视图概述

当用户对数据库中的一张或者多张表的某些字段的组合感兴趣，而又不想每次键入这些查询时，用户就可以定义一个视图，以便解决这个问题。视图中列可以来自于表里的不同列，这些列都是用户所感兴趣的数据列。

视图与表不同，它在物理上不是真实存在的，而是一个虚表。在数据库里仅存放视图的定义，而不存放视图对应的数据。视图中的这些数据存放在其对应的表中，如果表中的数据发生了变化，从视图中查询出的数据也会随之发生改变。从这个意义来看，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及变化。每一次查看视图或引用视图的的时候，都会运行一次视图上的查询。

用户可以使用SELECT语句从视图里查询数据，对于符合一定约束条件的视图，还可以使用INSERT、UPDATE、DELETE、MERGE INTO等语句修改视图对应的基础表里的数据。视图在提供操作方便的同时，还可以保障数据库数据的安全。

3. 数据库视图的优点

3.1 数据库视图可以简化复杂的查询

数据库视图由许多基础表相关联的 SQL 语句定义，可以使用数据库视图向最终用户和外部应用程序隐藏底层表的复杂性。通过数据库视图，只需要使用简单的 SQL 语句，不需要编写具有许多连接的复杂语句。

3.2 数据库视图有助于限制对特定用户的数据访问

如果不希望所有用户都可以查询敏感数据，就可以使用数据库视图仅向特定用户组公开非敏感数据。

3.3 数据库视图提供了额外的安全层

安全性是任何关系数据库管理系统的重要组成部分，数据库视图为数据库管理系统提供了额外的安全性。数据库视图允许创建只读视图以向特定用户公开只读数据，用户只能在只读视图中检索数据，但不能对其进行更新。

3.4 数据库视图可以定义计算列

数据库表中不应该有计算列，但是数据库视图支持有计算列。假设在订单表中有订购产品的数量和每个产品的价格列，但是订单表定义一列来存储每个订单的总销售额。如果有，这样的数据库模式也不是一个好的设计。在这种情况下，可以创建一个名为总销售额的列， 它是计算结果是产品的价格乘以订购产品的数量。当从数据库视图查询数据时，计算列的数据会动态进行计算。

3.5 数据库视图支持应用兼容性

假设有一个核心数据库，许多应用程序都在使用它，为了适应新的业务需求，有可能会重新设计数据库，删除了一些表并创建了几个新表，修改表的列名，此时并不希望这些更改影响之前的应用程序。在这种情况下，可以使用与已删除的旧表相同的表结构创建数据库视图。应用程序可以访问视图来完成此前功能，这样就无需对应用程序做任何的修改。

4. 传统数据库视图的缺点

除了上述优点外，在传统数据库中使用视图有以下几个缺点：

4.1 性能可能会较差

从传统数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。

GaussDB(DWS)通过对视图解析和查询优化，生成的查询计划基于基础表，依然可以提供高效的查询性能。

4.2 视图对表结构的依赖

由于视图时根据数据库的基础表创建的，每当更改与视图关联的那些表的结构时，也必须更改视图。

GaussDB(DWS) 在8.1.0版本实现了视图的解耦，使得存在视图依赖的基表或其他数据库对象（视图、同义词、函数、表字段）可以单独删除，而其上关联的依赖视图依然存在，而在基表重建后，可以通过ALTER VIEW view_name REBUILD命令重建依赖关系。而8.1.1版本在此基础上又实现了自动重建，可以无感知自动重建依赖关系。视图解耦功能介绍可查看以下链接：

https://bbs.huaweicloud.com/blogs/238425

https://bbs.huaweicloud.com/blogs/282067

https://bbs.huaweicloud.com/blogs/183594

5. 创建视图的语法

创建视图需要使用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;

6. 可更新视图

在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)

7. 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_lock_conflicts：提供集群中有冲突的锁的信息
当某一个锁正在等待另一个锁，或正在被另一个锁等待，即该锁是有冲突的。

test=# select locktype, nodename, dbname, transactionid, queryid, query, pid, mode, granted from pgxc_lock_conflicts;
   locktype    | nodename  | dbname | transactionid |      queryid       |             query             |       pid       |     mode      | granted 
---------------+-----------+--------+---------------+--------------------+-------------------------------+-----------------+---------------+---------
 transactionid | datanode2 | test   |      44195618 | 134263563890983407 | insert into t1 values(3,3,3); | 140058198800128 | ExclusiveLock | t
 transactionid | datanode2 | test   |      44195618 | 134263563890983591 | insert into t1 values(3,3,3); | 140058286868224 | ShareLock     | f
 transactionid | datanode3 | test   |      44195617 | 134263563890983593 | insert into t1 values(1,1,1); | 139666736543488 | ShareLock     | f
 transactionid | datanode3 | test   |      44195617 | 134263563890983401 | insert into t1 values(1,1,1); | 139666786875136 | ExclusiveLock | t
(4 rows)

pgxc_deadlock：获取导致分布式死锁产生的锁等待信息

test=# select locktype, nodename, dbname, waitquery, transactionid, waitgxid, waitpid, waitmode, holdpid, holdmode from pgxc_deadlock;
   locktype    | nodename  | dbname |           waitquery           | transactionid | waitgxid |     waitpid     | waitmode  |     holdpid     |   holdmode    
---------------+-----------+--------+-------------------------------+---------------+----------+-----------------+-----------+-----------------+---------------
 transactionid | datanode2 | test   | insert into t1 values(3,3,3); |      44195618 | 44195617 | 140058286868224 | ShareLock | 140058198800128 | ExclusiveLock
 transactionid | datanode3 | test   | insert into t1 values(1,1,1); |      44195617 | 44195618 | 139666736543488 | ShareLock | 139666786875136 | ExclusiveLock
(2 rows)

8. 总结

本文通过介绍视图原理，视图的创建、更新、依赖以及GaussDB(DWS)常用系统视图的方面带你了解数据库视图对象。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
EI企业智能 SQL 云数据仓库 GaussDB(DWS) 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入