高性能MySQL：物化视图

物化视图

许多数据库管理系统（例如Oracle 或者微软SQL Server）都提供了一个被称作物化视图的功能 物化视图实际上是预先计算并且存储在磁盘上的表 可以通过各种各样的策略刷新和更新 MySQL 并不原生支持物化视图（我们将在第 章详细探讨支持这种视图的细节） 然而 使用Justin Swanhart 的开源工具Flexviews（//code google /p/flexviews/） 也可以自己实现物化视图 Flexviews 比完全自己实现的解决方案要更精细 并且提供了很多不错的功能使得可以更简单地创建和维护物化视图 它由下面这些部分组成

变更数据抓取（Change Data Capture y CDC）功能 可以读取服务器的二进制日志并且解析相关行的变更

一系列可以帮助创建和管理视图的定义的存储过程

一些可以应用变更到数据库中的物化视图的工具

对比传统的维护汇总表和缓存表的方法 Flexviews 通过提取对源表的更改 可以增量地重新计算物化视图的内容 这意味着不需要通过查询原始数据来更新视图 例如 如果创建了一张汇总表用于计算每个分组的行数 此后增加了一行数据到源表中 Flexviews简单地给相应的组的行数加一即可 同样的技术对其他的聚合函数也有效 例如SUM（）和AVG（） 这实际上是有好处的 基于行的二进制日志包含行更新前后的镜像 所以Flexviews 不仅仅可以获得每行的新值 还可以不需要查找源表就能知道每行数据的旧版本 计算增量数据比从源表中读取数据的效率要高得多

因为版面的限制 这里我们不会完整地探讨怎么使用Flexviews 但是可以给出一个概略 先写出一个SELECT 语句描述想从已经存在的数据库中得到的数据 这可能包含关联和聚合（GROUP BY） Flexviews 中有一个辅助工具可以转换SQL 语句到Flexviews 的API 调用 Flexviews 会做完所有的脏活 累活 监控数据库的变更并且转换后用于更新存储物化视图的表 现在应用可以简单地查询物化视图来替代查询需要检索的表

Flexviews 有不错的SQL 覆盖范围 包括一些棘手的表达式 你可能没有料到一个工具可以在MySQL 服务器之外处理这些工作 这一点对创建基于复杂SQL 表达式的视图很有用 可以用基于物化视图的简单 快速的查询替换原来复杂的查询

      返回目录 高性能MySQL

       编辑推荐

       ASP NET MVC 框架揭秘

       Oracle索引技术

       ASP NET开发培训视频教程

lishixinzhi/Article/program/MySQL/201311/29674

sql数据库创建视图的方法：1、登录“sql server managment”；2、在指定数据库中选择“视图”，右键单击选择“新建视图”选项；3、添加要关联的表；4、默认是主键关联的，可以调整关联字段；5、按“ctrl+s”保存即可

您好，CREATE VIEW

创建一个虚拟表，该表以另一种方式表示一个或多个表中的数据。CREATE VIEW 必须是查询批处理中的第一条语句。

语法

CREATE VIEW [ < database_name > ] [ < owner > ] view_name [ ( column [ ,n ] ) ]

[ WITH < view_attribute > [ ,n ] ]

AS

select_statement

[ WITH CHECK OPTION ]

< view_attribute > ::=

{ ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

参数

view_name

是视图的名称。视图名称必须符合标识符规则。可以选择是否指定视图所有者名称。

column

是视图中的列名。只有在下列情况下，才必须命名 CREATE VIEW 中的列：当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。还可以在 SELECT 语句中指派列名。

如果未指定 column，则视图列将获得与 SELECT 语句中的列相同的名称。

说明 在视图的各列中，列名的权限在 CREATE VIEW 或 ALTER VIEW 语句间均适用，与基础数据源无关。例如，如果在 CREATE VIEW 语句中授予了 title_id 列上的权限，则 ALTER VIEW 语句可以将 title_id 列改名（例如改为 qty），但权限仍与使用 title_id 的视图上的权限相同。

n

是表示可以指定多列的占位符。

AS

是视图要执行的 *** 作。

select_statement

是定义视图的 SELECT 语句。该语句可以使用多个表或其它视图。若要从创建视图的 SELECT 子句所引用的对象中选择，必须具有适当的权限。

视图不必是具体某个表的行和列的简单子集。可以用具有任意复杂性的 SELECT 子句，使用多个表或其它视图来创建视图。

在索引视图定义中，SELECT 语句必须是单个表的语句或带有可选聚合的多表 JOIN。

对于视图定义中的 SELECT 子句有几个限制。CREATE VIEW 语句不能：

包含 COMPUTE 或 COMPUTE BY 子句。

包含 ORDER BY 子句，除非在 SELECT 语句的选择列表中也有一个 TOP 子句。

包含 INTO 关键字。

引用临时表或表变量。

因为 select_statement 使用 SELECT 语句，所以在 FROM 子句中指定 <join_hint> 和 <table_hint> 提示是有效的。有关更多信息，请参见 FROM 和 SELECT。

在 select_statement 中可以使用函数。

select_statement 可使用多个由 UNION 或 UNION ALL 分隔的 SELECT 语句。

WITH CHECK OPTION

强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。通过视图修改行时，WITH CHECK OPTION 可确保提交修改后，仍可通过视图看到修改的数据。

WITH ENCRYPTION

表示 SQL Server 加密包含 CREATE VIEW 语句文本的系统表列。使用 WITH ENCRYPTION 可防止将视图作为 SQL Server 复制的一部分发布。

SCHEMABINDING

将视图绑定到架构上。指定 SCHEMABINDING 时，select_statement 必须包含所引用的表、视图或用户定义函数的两部分名称 (ownerobject)。

不能除去参与用架构绑定子句创建的视图中的表或视图，除非该视图已被除去或更改，不再具有架构绑定。否则，SQL Server 会产生错误。另外，如果对参与具有架构绑定的视图的表执行 ALTER TABLE 语句，而这些语句又会影响该架构绑定视图的定义，则这些语句将会失败。

VIEW_METADATA

指定为引用视图的查询请求浏览模式的元数据时，SQL Server 将向 DBLIB、ODBC 和 OLE DB API 返回有关视图的元数据信息，而不是返回基表或表。浏览模式的元数据是由 SQL Server 向客户端 DB-LIB、ODBC 和 OLE DB API 返回的附加元数据，它允许客户端 API 实现可更新的客户端游标。浏览模式的元数据包含有关结果集内的列所属的基表信息。

对于用 VIEW_METADATA 选项创建的视图，当描述结果集中视图内的列时，浏览模式的元数据返回与基表名相对的视图名。

当用 VIEW_METADATA 创建视图时，如果该视图具有 INSERT 或 UPDATE INSTEAD OF 触发器，则视图的所有列（timestamp 除外）都是可更新的。请参见本主题后面的"可更新视图"。

注释

只能在当前数据库中创建视图。视图最多可以引用 1,024 列。

通过视图进行查询时，Microsoft® SQL Server™ 将检查以确定语句中任意位置引用的所有数据库对象是否都存在，这些对象在语句的上下文中是否有效，以及数据修改语句是否没有违反任何数据完整性规则。如果检查失败，将返回错误信息。如果检查成功，则将 *** 作转换成对基础表的 *** 作。

如果某个视图依赖于已除去的表（或视图），则当有人试图使用该视图时，SQL Server 将产生错误信息。如果创建了新表或视图（该表的结构与以前的基表没有不同之处）以替换除去的表或视图，则视图将再次可用。如果新表或视图的结构发生更改，则必须除去并重新创建该视图。

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

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。

视图是存储在数据库中的查询的SQL 语句，它主要出于两种原因：安全原因， 视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。这个视图就像一个“窗口”，从中只能看到你想看的数据列。这意味着你可以在这个视图上使用SELECT ，而你看到的将是你在视图定义里给出的那些数据列：

既然视图的定义是基于基本表的，哪为什么还要定义视图呢？这是因为合理地使用视图能够带来许多好处：

1、 视图能简化用户 *** 作

视图机制使用户可以将注意力集中在所关心地数据上。如果这些数据不是直接来自基本表，则可以通过定义视图，使数据库看起来结构简单、清晰，并且可以简化用户的的数据查询 *** 作。例如，那些定义了若干张表连接的视图，就将表与表之间的连接 *** 作对用户隐藏起来了。换句话说，用户所作的只是对一个虚表的简单查询，而这个虚表是怎样得来的，用户无需了解。

2、 视图使用户能以多种角度看待同一数据

视图机制能使不同的用户以不同的方式看待同一数据，当许多不同种类的用户共享同一个数据库时，这种灵活性是非常必要的。

3、 视图对重构数据库提供了一定程度的逻辑独立性

数据的物理独立性是指用户的应用程序不依赖于数据库的物理结构。数据的逻辑独立性是指当数据库重构造时，如增加新的关系或对原有的关系增加新的字段，用户的应用程序不会受影响。层次数据库和网状数据库一般能较好地支持数据的物理独立性，而对于逻辑独立性则不能完全的支持。

在关许数据库中，数据库的重构造往往是不可避免的。重构数据库最常见的是将一个基本表“垂直”地分成多个基本表。例如：将学生关系Student（Sno，Sname，Ssex，Sage，Sdept），

分为SX（Sno，Sname，Sage）和SY（Sno，Ssex，Sdept）两个关系。这时原表Student为SX表和SY表自然连接的结果。如果建立一个视图Student：

[sql]  view plain  copy

CREATE VIEW Student（Sno，Sname，Ssex，Sage，Sdept）AS SELECT SXSno，SXSname，SYSsex，SXSage，SYSdept FROM SX，SY WHERE SXSno=SYSno；

这样尽管数据库的逻辑结构改变了（变为SX和SY两个表了），但应用程序不必修改，因为新建立的视图定义为用户原来的关系，使用户的外模式保持不变，用户的应用程序通过视图仍然能够查找数据。

当然，视图只能在一定程度上提供数据的逻辑独立，比如由于视图的更新是有条件的，因此应用程序中修改数据的语句可能仍会因为基本表构造的改变而改变。

4、视图能够对机密数据提供安全保护

有了视图机制，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，使机密数据不出现在不应该看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能。例如，Student表涉及全校15个院系学生数据，可以在其上定义15个视图，每个视图只包含一个院系的学生数据，并只允许每个院系的主任查询和修改本原系学生视图。

5、适当的利用视图可以更清晰地表达查询

例如经常需要执行这样的查询“对每个学生找出他获得最高成绩的课程号”。可以先定义一个视图，求出每个同学获得的最高成绩：

[sql]  view plain  copy

CREATE VIEW VMGRADE

AS

SELECT Sno，MAX(Grade) Mgrade

FROM SC

GROUP BY Sno

然后用如下的查询语句完成查询：

[sql]  view plain  copy

SELECT SCSno，Cno FROM SC，VMGRADE WHERE SCSno = VMGRADESno AND SCGrade = VMGRADEMgrade；

二、数据准备

1、员工表

[sql]  view plain  copy

CREATE TABLE t_employee(

ID INT  PRIMARY KEY  AUTO_INCREMENT,

NAME CHAR(30) NOT NULL,

SEX  CHAR(2) NOT NULL,

AGE INT NOT NULL,

DEPARTMENT CHAR(10) NOT NULL,

SALARY  INT NOT NULL,

HOME CHAR(30),

MARRY CHAR(2) NOT NULL DEFAULT  '否',

HOBBY CHAR(30)

);

插入数据：

[sql]  view plain  copy

INSERT INTO learningt_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'小红','女',20,'人事部','4000','广东','否','网球');

INSERT INTO learningt_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'明日','女',21,'人事部','9000','北京','否','网球');

INSERT INTO learningt_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'天天','男',22,'研发部','8000','上海','否','音乐');

INSERT INTO learningt_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'大大','女',23,'研发部','9000','重庆','否','无');

INSERT INTO learningt_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'王下','女',24,'研发部','9000','四川','是','足球');

INSERT INTO learningt_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'无名','男',25,'销售部','6000','福建','否','游戏');

INSERT INTO learningt_employee(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'不知道','女',26,'销售部','5000','山西','否','篮球');

插入的结果：

然后再定义一张员工信息表：

[sql]  view plain  copy

create TABLE t_employee_detail(

ID INT PRIMARY KEY,

POS CHAR(10) NOT NULL,

EXPERENCE CHAR(10) NOT NULL,

CONSTRAINT `FK_ID` FOREIGN KEY(ID) REFERENCES t_employee(ID)

)

插入如下：

[sql]  view plain  copy

INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(1,'人事管理','工作二年');

INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(2,'人事招聘','工作二年');

INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(3,'初级工程师','工作一年');

INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(4,'中级工程师','工作二年');

INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(5,'高级工程师','工作三年');

INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(6,'销售代表','工作二年');

INSERT INTO t_employee_detail(ID,POS,EXPERENCE) VALUES(7,'销售员','工作一年');

内容：

三、使用案例

1 语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW [db_name]view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

通过该语句可以创建视图，若给定了[OR REPLACE]，则表示当已具有同名的视图时，将覆盖原视图。select_statement是一个查询语句，这个查询语句可从表或其它的视图中查 询。视图属于数据库，因此需要指定数据库的名称，若未指定时，表示在当前的数据库创建新视图。

表和数据库共享数据库中相同的名称空间，因此，数据库不能包含相同名称的表和视图，并且，视图的列名也不能重复。

在创建视图前应先看看是否有权限：

[sql]  view plain  copy

SELECT SELECT_priv,create_view_priv from mysqluser WHERE user='root'

Y表示有创建的权限

2、单表上创建视图

在员工表是创建视图

[sql]  view plain  copy

CREATE VIEW V_VIEW1(ID, NAME, SEX, AGE,DEPARTMENT) AS SELECT ID, NAME, SEX, AGE,DEPARTMENT FROM learningt_employee;

然后是显示内容：

[sql]  view plain  copy

SELECT  FROM V_VIEW1

3、多表上创建视图

[sql]  view plain  copy

CREATE VIEW V_VIEW2(ID, NAME, SEX, AGE,DEPARTMENT,POS,EXPERENCE) AS SELECT aID, aNAME, aSEX, aAGE,aDEPARTMENT,bPOS,bEXPERENCE FROM learningt_employee a,learningt_employee_detail b WHERE aID=bID;

显示结果 

[sql]  view plain  copy

SELECT  FROM V_VIEW2

4、查看视图

（1）DESCRIBE 命令

[sql]  view plain  copy

DESCRIBE V_VIEW2

（2）SHOW TABLE STATUS 

[sql]  view plain  copy

show TABLE status LIKE 'V_VIEW2'

（3）SHOW CREATE view命令

[sql]  view plain  copy

show CREATE view V_VIEW2

5、修改视图

(1)CREATE OR REPLACE命令

[sql]  view plain  copy

CREATE OR REPLACE VIEW V_VIEW1(ID, NAME, SEX) AS SELECT ID, NAME, SEX  FROM learningt_employee;

(2) ALTER 命令

[sql]  view plain  copy

ALTER VIEW  V_VIEW1(ID, NAME) AS SELECT ID, NAME  FROM learningt_employee;

SELECT  FROM learningv_view1

6、更新视图

在MySQL中，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，都是转换到基本表来更新。

更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

更新前：

更新后：

[sql]  view plain  copy

UPDATE V_VIEW2 SET POS='高级工程师' WHERE NAME='天天'

对应 的真实表上的数据也发生改变 了

[sql]  view plain  copy

SELECT  FROM learningt_employee_detail WHERE t_employee_detailID=3

不可更新的视图：

某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：

· 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。

· DISTINCT

· GROUP BY

· HAVING

· UNION或UNION ALL

· 位于选择列表中的子查询

· Join

· FROM子句中的不可更新视图

· WHERE子句中的子查询，引用FROM子句中的表。

· 仅引用文字值（在该情况下，没有要更新的基本表）。

· ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

注意

视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

CASCADED和LOCAL能不能决定视图是否能更新？

WITH[CASCADED|LOCAL] CHECK OPTION能不能决定视图是否能更新？这两个参数的基本定义如下：

LOCAL参数表示更新视图时只要满足该视图本身定义的条件即可。

CASCADED参数表示更新视图时需要满足所有相关视图和表的条件。没有指明时，该参数为默认值。

With check option的用法：

（with check option对于没有where条件的视图不起作用的）

[sql]  view plain  copy

CREATE VIEW V_VIEW3(ID, NAME,SEX,AGE,DEPARTMENT,SALARY, HOME, MARRY, HOBBY) AS SELECT ID, NAME, SEX,AGE,DEPARTMENT,SALARY,HOME,MARRY,HOBBY FROM learningt_employee WHERE DEPARTMENT='人事部' WITH LOCAL CHECK OPTION;

表示只限定插入部门为人事部的人。

然后插入一条：

[sql]  view plain  copy

INSERT INTO learningV_VIEW3(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'会会会会','女',20,'人事部','4500','广东','否','网球');

看下结果：

[sql]  view plain  copy

SELECT  FROM learningV_VIEW3

同时看真实表中的数据：

再来插入一条：

[sql]  view plain  copy

INSERT INTO learningV_VIEW3(ID, NAME, SEX, AGE,DEPARTMENT, SALARY, HOME, MARRY, HOBBY) VALUES(NULL,'qqqqvasvas','女',20,'研发部','4500','上海','否','网球');

结果显示插入失败

对于with check option用法，总结如下：

通过有with check option选项的视图 *** 作基表(只是面对单表，对连接多表的视图正在寻找答案)，有以下结论： 插入后的数据，通过视图能够查询出来就符合WITH CHECK OPTION 否则就不符合；

首先视图只 *** 作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来 *** 作。

1对于update,有with check option，要保证update后，数据要被视图查询出来

2对于delete,有无with check option都一样

4对于insert,有with check option，要保证insert后，数据要被视图查询出来

对于没有where 子句的视图，使用with check option是多余的

7、删除视图

[sql]  view plain  copy

DROP VIEW IF EXISTS 视图名

等

数据库是依照某种数据模型组织起来并存放二级存储器中的数据集合。这种数据集合具有如下特点：尽可能不重复，以最优方式为某个特定组织的多种应用服务，其数据结构独立于使用它的应用程序，对数据的增、删、改和检索由统一软件进行管理和控制。从发展的历史看，数据库是数据管理的高级阶段，它是由文件管理系统发展起来的。

数据库的基本结构分三个层次，反映了观察数据库的三种不同角度。

(1)物理数据层。它是数据库的最内层，是物理存贮设备上实际存储的数据的集合。这些数据是原始数据，是用户加工的对象，由内部模式描述的指令 *** 作处理的位串、字符和字组成。

(2)概念数据层。它是数据库的中间一层，是数据库的整体逻辑表示。指出了每个数据的逻辑定义及数据间的逻辑联系，是存贮记录的集合。它所涉及的是数据库所有对象的逻辑关系，而不是它们的物理情况，是数据库管理员概念下的数据库。

(3)逻辑数据层。它是用户所看到和使用的数据库，表示了一个或一些特定用户使用的数据集合，即逻辑记录的集合。

数据库不同层次之间的联系是通过映射进行转换的。数据库具有以下主要特点：

(1)实现数据共享。数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据库，并提供数据共享。

(2)减少数据的冗余度。同文件系统相比，由于数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性。

(3)数据的独立性。数据的独立性包括数据库中数据库的逻辑结构和应用程序相互独立，也包括数据物理结构的变化不影响数据的逻辑结构。

(4)数据实现集中控制。文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过数据模型表示各种数据的组织以及数据间的联系。

(5)数据一致性和可维护性，以确保数据的安全性和可靠性。主要包括：①安全性控制：以防止数据丢失、错误更新和越权使用；②完整性控制：保证数据的正确性、有效性和相容性；③并发控制：使在同一时间周期内，允许对数据实现多路存取，又能防止用户之间的不正常交互作用；④故障的发现和恢复：由数据库管理系统提供一套方法，可及时发现故障和修复故障，从而防止数据被破坏

以上就是关于高性能MySQL：物化视图全部的内容，包括:高性能MySQL：物化视图、数据库怎样创建视图、数据库的视图(VIEW)增加一个列是用UPDATE 还是INSERT 具体如何实现，请举个例子等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！