关于数据库:带你认识数据库视图对象下次不要再认成表了

5次阅读

共计 7486 个字符，预计需要花费 19 分钟才能阅读完成。

摘要： 本文介绍 GaussDB(DWS) 的数据库视图对象，咱们将探讨应用数据库视图的长处和毛病，以及如何应用零碎视图解决一些问题。

本文分享自华为云社区《GaussDB(DWS) SQL 进阶之数据库对象 – 视图》，作者：拂晓的风。

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

视图与表不同，它在物理上不是实在存在的，而是一个虚表。在数据库里仅寄存视图的定义，而不寄存视图对应的数据。视图中的这些数据寄存在其对应的表中，如果表中的数据产生了变动，从视图中查问出的数据也会随之产生扭转。从这个意义来看，视图就像一个窗口，透过它能够看到数据库中用户感兴趣的数据及变动。每一次查看视图或援用视图的的时候，都会运行一次视图上的查问。

用户能够应用 SELECT 语句从视图里查问数据，对于合乎肯定约束条件的视图，还能够应用 INSERT、UPDATE、DELETE、MERGE INTO 等语句批改视图对应的根底表里的数据。视图在提供操作不便的同时，还能够保障数据库数据的平安。

数据库视图由许多根底表相关联的 SQL 语句定义，能够应用数据库视图向最终用户和内部应用程序暗藏底层表的复杂性。通过数据库视图，只须要应用简略的 SQL 语句，不须要编写具备许多连贯的简单语句。

如果不心愿所有用户都能够查问敏感数据，就能够应用数据库视图仅向特定用户组公开非敏感数据。

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

数据库表中不应该有计算列，然而数据库视图反对有计算列。假如在订单表中有订购产品的数量和每个产品的价格列，然而订单表定义一列来存储每个订单的总销售额。如果有，这样的数据库模式也不是一个好的设计。在这种状况下，能够创立一个名为总销售额的列，它是计算结果是产品的价格乘以订购产品的数量。当从数据库视图查问数据时，计算列的数据会动静进行计算。

假如有一个外围数据库，许多应用程序都在应用它，为了适应新的业务需要，有可能会从新设计数据库，删除了一些表并创立了几个新表，批改表的列名，此时并不心愿这些更改影响之前的应用程序。在这种状况下，能够应用与已删除的旧表雷同的表构造创立数据库视图。应用程序能够拜访视图来实现此前性能，这样就无需对应用程序做任何的批改。

除了上述长处外，应用数据库视图有以下几个毛病：

从数据库视图查问数据可能会很慢，特地是如果视图是基于其余视图创立的。

因为视图时依据数据库的根底表创立的，每当更改与视图关联的那些表的构造时，也必须更改视图。

创立视图须要应用 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) 还提供了许多视图用于展现数据库的外部状态，以下几个视图，在定位故障时会常常应用。

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 级数仓黑科技，后盾还可获取泛滥学习材料哦~

点击关注，第一工夫理解华为云陈腐技术~

正文完

数据库

发表至：数据库

2021-09-07

0

关于数据库:OpenMLDB-BUG-悬赏令

关于数据库:专访阿里云王伟民一站式全链路阿里云向云原生数据库20跃迁

关于数据库:TiDB-数据一致性校验实现Syncdiffinspector-优化方案

关于数据库:我们毕业啦Hacking-Camp-2021-结营六大生态项目进入新阶段

关于服务器:安全架构如何让企业在未来动荡中保持安全

关于数据库:带你认识数据库视图对象下次不要再认成表了

（一）视图概述

（二）数据库视图的长处

1. 数据库视图能够简化简单的查问

2. 数据库视图有助于限度对特定用户的数据拜访。

3. 数据库视图提供了额定的平安层。

4. 数据库视图能够定义计算列。

5. 数据库视图反对利用兼容性

（三）数据库视图的毛病

1. 性能可能会较差

2. 视图对表构造的依赖

（四）创立视图的语法

（五）可更新视图

（六）GaussDB(DWS) 罕用零碎视图

站内搜索