InnoDB的视图

视图（View）是一个命名的虚表，它由一个查询来定义，可以当做表使用。与持久表（permanent table）不同的是，视图中的数据没有物理表现形式。

视图的作用

视图在数据库中发挥着重要的作用。视图的主要用途之一是被用做一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表（base table）的结构，只需要按照视图定义来获取数据或者更新数据，因此，视图同时在一定程度上起到一个安全层的作用。

MySQL从5.0版本开始支持视图，创建视图的语法如下：

CREATE

[OR REPLACE]

[ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]

[DEFINER={user|CURRENT_USER}]

[SQL SECURITY{DEFINER|INVOKER}]

VIEW view_name[(column_list)]

AS select_statement

[WITH[CASCADED|LOCAL]CHECK OPTION]

虽然视图是基于基表的一个虚拟表，但是我们可以对某些视图进行更新操作，其实就是通过视图的定义来更新基本表，我们称可以进行更新操作的视图为可更新视图（updatable view）。视图定义中的WITH CHECK OPTION就是指对于可更新的视图，更新的值是否需要检查。

我们先看个例子：

create table t(id int);

create view v_t as select * from t where t＜10;

ERROR 1054（42S22）：Unknown column't'in'where clause'

create view v_t as select * from t where id＜10;

insert into v_t select 20;

select * from v_t；

我们创建了一个id＜10的视图，但是往里插入了id为20的值，插入操作并没有报错，但是我们查询视图还是没有能查到数据。

接着我们更改一下视图的定义，加上WITH CHECK OPTION：

alter view v_t as select * from t where id＜10 with check option;

insert into v_t select 20;

ERROR 1369（HY000）：CHECK OPTION failed'mytest.v_t'

这次MySQL数据库会对更新视图插入的数据进行检查，对于不满足视图定义条件的，将会抛出一个异常，不允许数据的更新。

MysQL DBA一个常用的命令是show tables，会显示出当前数据库下的表，视图是虚表，同样被作为表而显示出来，

我们来看前面的例子：show tables;

show tables命令把表t和视图v_t都显示出来了。如果我们只想查看当前数据库下的基表，可以通过information_schema架构下的TABLE表来查询，并搜索表类型为BASE TABLE的表，如：

select * from information_schema.TABLES where table_type='BASE TABLE' and table_schema=database();

要想查看视图的一些元数据（meta data），可以访问information_schema架构下的VIEWS表，该表给出了视图的详细信息，包括视图定义者（definer）、定义内容、是否是可更新视图、字符集等。如我们查询VIEWS表，可得：

select * from information_schema.VIEWS where table_schema=database();

物化视图

Oracle数据库支持物化视图——该视图不是基于基表的虚表，而是根据基表实际存在的实表。物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作结果，这样，在执行复杂查询时，就可以避免进行这些耗时的操作，从而快速得到结果。物化视图的好处是，对于一些复杂的统计类查询能直接查出结果。在Microsoft SQL Server数据库中，称这种视图为索引视图。

在Oracle数据库中，物化视图的创建方式包括BUILD IMMEDIATE和BUILD DEFERRED这两种。BUILD IMMEDIATE是默认的创建方式，在创建物化视图的时候就生成数据，而BUILD DEFERRED则在创建时不生成数据，以后根据需要再生成数据。

查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果。如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。

物化视图的刷新是指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。

刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND指物化视图在用户需要的时候进行刷新，ON COMMIT指物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。

MySQL数据库本身并不支持物化视图，换句话说，MySQL数据库中的视图总是虚拟的，但是我们可以通过一些机制来实现物化视图的功能。

要创建一个ON DEMAND的物化视图还是比较简单的，我们可以定时把数据导入另一张表。例如，我们有如下的订单表，记录了用户采购电脑设备：

create table Orders(

　　order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

　　product_name VARCHAR(30) NOT NULL,

　　price DECIMAL(8,2) NOT NULL,

　　amount SMALLINT NOT NULL,

　　primary key(order_id)

)ENGINE=InnoDB;

INSERT INTO Orders VALUES

　　(NULL,'CPU',135.5,1),

　　(NULL,'Memory',48.2,3),

　　(NULL,'CPU',125.6,3),

　　(NULL,'CPU',105.3,4);

select * from Orders\G;

接着我们建立一张物化视图，用来统计每件物品的信息，如：

CREATE TABLE Orders_MV(

　　product_name VARCHAR(30) NOT NULL,

　　price_sum DECIMAL(8,2) NOT NULL,

　　amount_sum INT NOT NULL,

　　price_avg FLOAT NOT NULL,

　　orders_cnt INT NOT NULL,

　　UNIQUE INDEX(product_name)

);

INSERT INTO Orders_MV

　　SELECT product_name,

　　SUM（price）,SUM（amount）,AVG（price）

　　COUNT（*） 

　　FROM Orders

　　GROUP BY product_name;

select * from Orders_MV;

这里我们把物化视图定义为一张表，只不过表名以_MV结尾，让DBA能很好地理解这张表的作用。这样就有了一个统计信息，如果是要实现ON DEMAND的物化视图，只需把表清空，重新导入数据即可。当然，这是完全（Complete）刷新方式。要实现快（Fast）刷新方式，其实也是可以的，只不过稍微复杂点，需要记录上次统计时的order_id的位置。

但是如果要实现On Commit的物化视图，这就不是如上面这么简单了。Oracle数据库中通过物化视图日志来实现，很显然MySQL数据库没有这个日志，但是通过触发器，我们同样可以达到这个目的：

DELIMITER$$

CREATE TRIGGER tgr_Orders_insert

AFTER INSERT ON Orders

FOR EACH ROW

BEGIN

　　SET@old_price_sum=0;

　　SET@old_amount_sum=0;

　　SET@old_price_avg=0;

　　SET@old_orders_cnt=0;

　　SELECT IFNULL(price_sum，0),IFNULL(amount_sum,0),IFNULL(price_avg,0),IFNULL(orders_cnt，0)

　　FROM Orders_MV

　　WHERE product_name=NEW.product_name

　　INTO @old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt;

　　SET@new_price_sum=@old_price_sum+NEW.price；

　　SET@new_amount_sum=@old_amount_sum+NEW.amount；

　　SET@new_orders_cnt=@old_orders_cnt+1；

　　SET@new_price_avg=@new_price_sum/@new_orders_cnt；

　　REPLACE INTO Orders_MV

　　VALUES(NEW.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt);

END;

$$

DELIMITER;

insert into Orders values(NULL,'SSD',299,3);

insert into Orders values(NULL,'Memory',47.9,5);

select * from Orders_MV;

这里对表Orders添加了一个INSERT的触发器，每次Insert操作都会重新统计Orders_MV中的数据，这样就实现了ON_Commit的物化视图功能。但是Orders表可能还会有Update和Delete的操作，所以应该还需要实现Delete和Update的触发器。

通过触发器我们实现了物化视图的功能，但是MySQL本身并不支持物化视图，因此对于物化视图支持的查询重写（Query Rewrite）功能就显得无能为力了。