mysql 5.6物化视图_mysql实现物化视图详解及视图与物化视图区别

再一次sql优化中一个select count(*)语句因数据量实在太大，已经无法从简单的索引什么进行优化了，在同事的推荐下考虑到了物化视图

物化视图是相对于视图而言的，但是两者实际上并没有什么关系就如java/javaScript一样

首先mysql的视图不是一种物化视图，他相当于一个虚拟表，本身并不存储数据，当sql在操作视图时所有数据都是从其他表中查询出来的。者带来的问题是使用视图并不能将常用数据分离出来，优化查询速度，切操作视图的很多命令和普通标一样，这回导致在业务中无法通过sql区分表和视图，是代码变得复杂。

视图是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能(如一个视图7个表关联，另一个视图8个表，程序员不知道，觉得很方便，把两个视图关联再做一个视图，那就惨了)，他的存在未了在设计上的方便性

实现视图的方法有两种，分别为合并算法和临时表算法，合并算法是指查询视图是将视图定义的sql合并到查询sql中，比如create view v1 as select * from user where sex = m;当我们要查询视图时，mysql会将select id,name from v1;并合成select id,name fromwhere sex= m;  临时表算法是将视图查出来的数据保存到一个临时表中，查询的时候查这个临时表。不管是合并算法和临时算法，都会给数据库带来额外的开销，切如果使用临时表后会使mysql的优化变得很困难，比如索引，而且视图还引入了一些其他的问题，是的其背后的逻辑非常复杂。

当然，视图在某些情况下还是可以帮助提升性能的，单视图的性能很难预测，且在mysql的优化器中，视图的代码执行路径也完全不同，无法直观预测其性能。

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

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 t