mysql实现物化视图详解及视图与物化视图区别
再⼀次sql优化中⼀个select count(*)语句因数据量实在太⼤，已经⽆法从简单的索引什么进⾏优化了，在同事的推荐下考虑到了物化视图
物化视图是相对于视图⽽⾔的，但是两者实际上并没有什么关系就如java/javaScript⼀样
css代码放在哪个位置⾸先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
webpack从入门到精通
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_mv
SELECT product_name, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount), COUNT(*) FROM sales
GROUP BY product_name;
最简单的⽅法，我们得到了预期的正确结果：
mysql> SELECT * FROM sales_mv /G
*************************** 1. row ***************************
product_name: Apple
price_sum: 7.70
amount_sum: 6
price_avg: 2.56667
amount_avg: 2
sales_cnt: 3
怎么获取一个网站的php源码*************************** 2. row ***************************
product_name: Pear
price_sum: 18.50
amount_sum: 6
price_avg: 9.25
amount_avg: 3
sales_cnt: 2
*************************** 3. row ***************************
java中vector用法product_name: Plum
price_sum: 4.85
amount_sum: 3
price_avg: 4.85
amount_avg: 3
sales_cnt: 1
3 rows in set (0.01 sec)
这会导致我们刚才提到的“从不更新”模式失败。但是这不是我们想要的。
3.2 按需更新物化视图
根据需要更新物化视图，我们可以⽤存储过程来实现
DROP PROCEDURE refresh_mv_now;
DELIMITER $$
CREATE PROCEDURE refresh_mv_now (
OUT rc INT
)
BEGIN
TRUNCATE TABLE sales_mv;
INSERT INTO sales_mv
mysql面试题及讲解
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论