mysql 是否有物化视图数据库物化视图

转载

AIGC创想家 2024-07-22 15:04:07

文章标签 mysql 是否有物化视图数据库数据物化视图 Sales 文章分类 MySQL 数据库

1. 关系型数据库的视图

1.1. 视图

视图是指计算机数据库中的视图，是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

1.2. 物化视图

物化视图是包括一个查询结果的数据库对象，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。

物化视图存储基于远程表的数据，也可以称为快照(类似于MSSQL Server中的snapshot，静态快照) 。对于复制，物化视图允许你在本地维护远程数据的副本，这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

2. 视图和物化视图的使用

2.1 视图的使用

数据库中的视图是一个虚拟表，同真实的表一样，视图包含一系列带有名称的行和列数据，行和列数据来自定义视图查询所引用的表，并且在引用视图时动态生成。视图是从一个或者多个表中导出的，视图的行为与表非常相似，但视图是一个虚拟表。在视图中用户可以使用SELECT语句查询数据，以及使用INSERT、UPDATE和DELETE修改记录。视图可以使用户操作方便，而且可以保障数据库系统的安全。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化。同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

视图是一个”虚表”，用大白话说，就是从已经存在的表的全部字段或数据中，挑选出来一部分字段或数据，组成另一张”并不存在的表”,这张虚表被称之”视图”。视图中的字段与对应的数据均来自已经存在的表。对于视图来说，这些已经存在的表就被称为”基表”。基表可以是一张表，也可以是多张表。视图的本质可以理解为一条查询语句，视图中显示的结果，就是这条查询语句查询出的结果。

视图是一个虚拟表（逻辑表）；
视图中的行和列的数据来自一到多张物理表，也可以来源自其他视图；
可以通过视图进行增删改查；
如果通过视图进行增删改，那么物理表的数据也会随之做出同样的增删改；反之亦然。
视图的使用比较少，起码相对于一般SQL来说，不在一个数量级上；
视图是一种”虚表”，所以不能与已经存在的表重名。

2.1.1. 使用视图的优点

视图隐藏了底层的表结构，简化了数据访问操作；
因为隐藏了底层的表结构，所以大大加强了安全性，用户只能看到视图提供的数据；
使用视图，方便了权限管理，让用户对视图有权限而不是对底层表有权限进一步加强了安全性；
视图提供了一个用户访问的接口，当底层表改变后，改变视图的语句来进行适应，使已经建立在这个视图上客户端程序不受影响；

2.1.1. 创建视图

视图的分类

普通视图(Regular View)
索引视图(Indexed View)
分割视图(Partitioned View)

2.1.1.1. 普通视图

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]
 
<view_attribute> ::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ] }

如下案例：

select * from tc_sys_user_back;

select * from tc_sys_user_back_1;

mysql 是否有物化视图数据库物化视图_mysql 是否有物化视图

create view vw_sys_user_back
as
select * from tc_sys_user_back;

select * from vw_sys_user_back;

mysql 是否有物化视图数据库物化视图_mysql 是否有物化视图_02

2.1.1.2. 索引视图

索引视图笔者只在SqlServer上看到过，具体可以参考

postgresql数据库中没有索引视图

https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
   QUOTED_IDENTIFIER, ANSI_NULLS ON;
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
   DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
   WITH SCHEMABINDING
   AS  
      SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
         OrderDate, ProductID, COUNT_BIG(*) AS COUNT
      FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
      WHERE od.SalesOrderID = o.SalesOrderID
      GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
   ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,
   OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o
   ON od.SalesOrderID=o.SalesOrderID
      AND o.OrderDate >= CONVERT(datetime,'05/01/2012',101)
WHERE od.ProductID BETWEEN 700 and 800
   GROUP BY OrderDate, ProductID
   ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o
   ON od.SalesOrderID=o.SalesOrderID
      AND o.OrderDate >= CONVERT(datetime,'03/01/2012',101)
      AND o.OrderDate < CONVERT(datetime,'04/01/2012',101)
    GROUP BY OrderDate
    ORDER BY OrderDate ASC;

2.1.1.3. 物化视图

物化视图是包括一个查询结果的数据库对象，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照(类似于MSSQL Server中的snapshot，静态快照) 。对于复制，物化视图允许你在本地维护远程数据的副本，这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以从物化视图中抽取。对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。(这个是基于本地的基表或者视图的聚合)。物化视图，说白了，就是物理表，只不过这张表通过oracle的内部机制可以定期更新，将一些大的耗时的表连接用物化视图实现，会提高查询的效率。当然要打开查询重写选项;

create materialized view mvw_sys_user_back
as
select * from tc_sys_user_back;

select * from mvw_sys_user_back;

mysql 是否有物化视图数据库物化视图_数据库_03

当我们修改表中的数据时

select * from tc_sys_user_back;

mysql 是否有物化视图数据库物化视图_数据库_04

但是当我们查询视图时，发现视图并没有更新

mysql 是否有物化视图数据库物化视图_Sales_05

refresh materialized view mvw_sys_user_back;

select * from mvw_sys_user_back;

mysql 是否有物化视图数据库物化视图_mysql 是否有物化视图_06

删除物化视图

drop materialized view [if exists] mvw_sys_user_back;

3. 普通视图和物化视图的区别

普通视图和物化视图根本就不是一个东西，说区别都是硬拼到一起的，首先明白基本概念，普通视图是不存储任何数据的，他只有定义，在查询中是转换为对应的定义SQL去查询，而物化视图是将数据转换为一个表，实际存储着数据，这样查询数据，就不用关联一大堆表，如果表很大的话，会在临时表空间内做大量的操作。

普通视图的三个特征：

是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能（如一个视图7个表关联，另一个视图8个表，程序员不知道，觉得很方便，把两个视图关联再做一个视图，那就惨了），他的存在未了在设计上的方便性
其次，是安全，在授权给其他用户或者查看角度，多个表关联只允许查看，不允许修改，单表也可以同WITH READ ONLY来控制，当然有些项目基于视图做面向对象的开发，即在视图上去做INSTAND OF触发器，就我个人而言是不站同的，虽然开发上方便，但是未必是好事。
从不同的角度看不同的维度，视图可以划分维度和权限，并使多个维度的综合，也就是你要什么就可以从不同的角度看，而表是一个实体的而已，一般维度较少（如：人员表和身份表关联，从人员表可以查看人员的维度统计，从身份看，可以看不同种类的身份有那些人或者多少人），其次另一个如系统视图USER_TABLE、TAB、USER_OBJECTS这些视图，不同的用户下看到的肯定是不一样的，看的是自己的东西。

物化视图

OLAP系统中，当然部分OLTP系统的小部分功能未了提高性能会借鉴一点点，因为表关联的开销很大，所以在开发中很多人就像把这个代价交给定期转存来完成，ORACLE当然也提供了这个功能，就是将视图（或者一个大SQL）的信息转换为物理数据存储，然后提供不同的策略：定时刷还是及时刷、增量刷还是全局刷等等可以根据实际情况进行选择，总之你查询的是表，不是视图。

4. 更新视图

4.1. 普通视图更新

要通过视图更新基表数据，必须保证视图是可更新视图。一个可更新视图满足以下条件：

没有使用连接函数、集合运算函数和组函数；
创建视图的SELECT语句中没有聚合函数且没有GROUP BY、ONNECT BY、START WITH子句及DISTINCT关键字；
创建视图的SELECT语句中不包含从基表列通过计算所得的列；
创建视图没有包含只读属性。

create view vw_grp_user_back
as
select * from tc_sys_user_back;

select * from  vw_grp_user_back;


update vw_grp_user_back set f_sex=1;

select * from  vw_grp_user_back;

mysql 是否有物化视图数据库物化视图_数据库_07

4.2. 复杂视图更新

create or replace view vw_grp_user_back_1
as
select tc_sys_user_back.f_name,tc_sys_user_back_1.f_sex from tc_sys_user_back,tc_sys_user_back_1 where tc_sys_user_back.f_id=tc_sys_user_back_1.f_id;
 
select * from vw_grp_user_back_1;
 
update vw_grp_user_back_1 set f_sex=1;

mysql 是否有物化视图数据库物化视图_mysql 是否有物化视图_08

create or replace view vw_grp_user_back_1
as
select tc_sys_user_back.f_id,tc_sys_user_back.f_name,tc_sys_user_back_1.f_sex from tc_sys_user_back,tc_sys_user_back_1 where tc_sys_user_back.f_id=tc_sys_user_back_1.f_id;
 
select * from vw_grp_user_back_1;
 

CREATE OR REPLACE FUNCTION vw_grp_user_back_1_update_trigger()
RETURNS TRIGGER AS $BODY$
BEGIN 
	update tc_sys_user_back set f_sex=new.f_sex where f_id =old.f_id;
	update tc_sys_user_back_1 set f_sex=new.f_sex where f_id =old.f_id;
RETURN NULL;
END; $BODY$
  LANGUAGE plpgsql VOLATILE

CREATE or replace TRIGGER update_vw_grp_user_back_1_trigger
    INSTEAD OF UPDATE ON vw_grp_user_back_1
    FOR EACH ROW EXECUTE PROCEDURE vw_grp_user_back_1_update_trigger();
		

update vw_grp_user_back_1 set f_sex=2;

select 'tc_sys_user_back' as tablename,f_id,f_name,f_sex from tc_sys_user_back
union all
select 'tc_sys_user_back_1' as tablename,f_id,f_name,f_sex from tc_sys_user_back_1

mysql 是否有物化视图数据库物化视图_mysql 是否有物化视图_09

4.3. 删除和插入

参考4.2. 复杂视图更新即可

CREATE TRIGGER insert_v_users_trigger
    INSTEAD OF INSERT ON v_user2
    FOR EACH ROW EXECUTE PROCEDURE v_user2_insert_trigger();
    
CREATE TRIGGER delete_v_users_trigger
    INSTEAD OF DELETE ON v_user2
    FOR EACH ROW EXECUTE PROCEDURE v_user2_delete_trigger();

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：mysqlSHOW FUNCTIONS用不了 mysql show warnings

下一篇：java 定义接口全局常量文件 java定义接口的作用

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯