mysql 创建物化视图_MySQL中实现物化视图 | 学步园

0.什么是物化视图

物化视图是查询结果的预运算。不同于简单的视图，物化视图的结果一般存储于表中。物化视图用于需要对查询立即做出响应，而又需要耗费很长时间获得结果。物化视图必须能快速更新。它取决于对更新频率和内容的准确性的要求。一般说来物化视图能够在一定时间内及时更新。

Mysql本来是不支持视图的，但是在5.0以上的版本，支持了视图功能，但是可惜的是不提供物化视图，但是这也难不住咱们，自己动手丰衣足食。

1. 实现自己的物化视图

看一个它是如何实现的简单的查询实例：

SELECT COUNT(*)

FROM MyISAM_table;

由于计数值存储在表的头部 立即返回结果。接下来的例子会耗费几秒到数分钟。

SELECT COUNT(*) FROM innodb_huge;

对此的可能解决方案是创建一个存储所有 InnoDB 行的表。

CREATE TABLE innodb_row_count (

id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

, schema_name VARCHAR(64)  NOT NULL

, table_name  VARCHAR(64)  NOT NULL

, row_count   INT UNSIGNED NOT NULL

);

取决于对该信息结果正确性的需要，该表可以每天更新一次(花费系统资源最少，结果错误最大)，一小时一次甚至是极端情况下每次改变都更新(最慢)。

另一种可能就是从信息架构中读取数据。但是信息会有高达20%的错误。

SELECT table_schema, table_name, table_rows

FROM information_schema.tables  WHERE table_type = ‘BASE TABLE’;

2.更新物化视图

物化视图的更新方式有很多种。比如：

l         从不更新(只在开始更新，只用于静态数据)

l         根据需要(比如每天，比如每夜)

l         及时(每次数据修改之后)

一半使用的更新方法：

l         全部更新(速度慢，完全从无到有)

l         延时的(速度快，使用log表)

通过在日志表中存储变更信息，通常会产生简单的“快照”或者延时状况：

l         及时更新

l         完全更新

3.测试

为了理解这个方法，我们举个例子，详细讲解一下。

CREATE TABLE sales (

sales_id       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

, product_name   VARCHAR(128) NOT NULL

, product_price  DECIMAL(8,2) NOT NULL

, product_amount SMALLINT     NOT NULL

);

INSERT INTO sales VALUES

(NULL, 'Apple', 1.25, 1), (NULL, 'Apple', 2.40, 2),

(NULL, 'Apple', 4.05, 3), (NULL, 'Pear', 6.30, 2),

(NULL, 'Pear', 12.20, 4), (NULL, 'Plum', 4.85, 3);

SELECT * FROM sales;

我们要知道售价和每种产品获得的利润，就要使用到两次的分组查询，我们晓得在mysql中连接查询和分组排序是会用到临时表和filesort的，这个如果数据量大的话，是十分耗时，如题查询如下：

EXPLAIN

SELECT product_name

, SUM(product_price) AS price_sum, SUM(product_amount) AS amount_sum

, AVG(product_price) AS price_avg, AVG(product_amount) amount_agg

, COUNT(*)

FROM sales

GROUP BY product_name

ORDER BY price_sum \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: sales

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 6

Extra: Using temporary; Using filesort

1 row in set (0.00 sec)

因为表中记录较少，因此速度很快，但是如果记录量很大这种，查询将会花费很多时间。

3.1创建物化视图

DROP TABLE sales_mv;

CREATE TABLE sales_mv (

product_name VARCHAR(128)  NOT NULL ,

price_sum    DECIMAL(10,2) NOT NULL,

amount_sum   INT           NOT NULL,

price_avg    FLOAT         NOT NULL,

amount_avg   FLOAT         NOT NULL,

sales_cnt    INT           NOT NULL,

UNIQUE INDEX product (product_name)

);

INSERT INTO sales_