-
2021-01-19 20:13:22
视图简介
需要MySQL 5 MySQL 5添加了对视图的支持。因此,本章内容适用于MySQL 5及以后的版本。
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
理解视图的最好方法是看一个例子:
输入:select cust_name ,cust_contact from customers,orders,orderitems where customers.cust_id = orders.cust_id and orderutems.order_num = order.orders.order_num and prod_id = 'TNT2';
此查询用来检索订购了某个特定产品的客户。任何需要这个数据的人都必须理解相关表的结构,并且知道如何创建查询和对表进行联结。为了检索其他产品(或多个产品)的相同数据,必须修改最后的 WHERE 子句。现在,假如可以把整个查询包装成一个名为 productcustomers 的虚拟表,则可以如下轻松地检索出相同的数据:
输入:select cust_name ,cust_contact from productcustomers where prod_id = 'TNT2';
这就是视图的作用。 productcustomers 是一个视图,作为视图,它不包含表中应该有的任何列或数据,它包含的是一个SQL查询(与上面用以正确联结表的相同的查询)。
为什么使用视图
我们已经看到了视图应用的一个例子。下面是视图的一些常见应用。
1.重用SQL语句。
2.简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
3.使用表的组成部分而不是整个表。
4.保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5.更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
在视图创建之后,可以用与表基本相同的方式利用它们。可以对视图执行 SELECT 操作,过滤和排序数据,将视图联结到其他视图或表,甚至能添加和更新数据(添加和更新数据存在某些限制。关于这个内容稍后还要做进一步的介绍)。
重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时,视图将返回改变过的数据。
性能问题 因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图,可能会发现性能下降得很厉害。因此,在部署使用了大量视图的应用前,应该进行测试。
使用视图
在理解什么是视图(以及管理它们的规则及约束)后,我们来看一下视图的创建。
1.视图用 CREATE VIEW 语句来创建。
2.使用 SHOW CREATE VIEW viewname ;来查看创建视图的语句。
3.用 DROP 删除视图,其语法为 DROP VIEW viewname;。
4.更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE ORREPLACE VIEW。如果要更新的视图不存在,则第 2 条更新语句会创建一个视图;如果要更新的视图存在,则第 2 条更新语句会替换原有视图。
视图的规则和限制
下面是关于视图创建和使用的一些最常见的规则和限制。
1.与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
2.对于可以创建的视图数目没有限制。
3.为了创建视图,必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
4.视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
5.ORDER BY 可以用在视图中,但如果从该视图检索数据 SELECT 中也含有 ORDER BY ,那么该视图中的 ORDER BY 将被覆盖。
6.视图不能索引,也不能有关联的触发器或默认值。
7.视图可以和表一起使用。例如,编写一条联结表和视图的 SELECT语句。
更多相关内容 -
STM32F103RBT6+MPU6050+AD623+SN65HVD230主板ALTIUM硬件原理图+PCB(带3视图)
2022-02-07 17:26:10STM32F103RBT6+MPU6050+AD623+SN65HVD230主板ALTIUM硬件原理图+PCB(带3视图)文件,2层板设计,大小为61x41mm,Altium Designer 设计的工程文件,包括完整的原理图及PCB文件,可以用Altium(AD)软件打开或修改,可作为... -
active_decorator:Rails 4、5和6的ORM不可知论的真正面向对象的视图助手
2021-02-02 03:47:39Rails 4,Rails 5和Rails 6的简单,Rubyish视图帮助器。使您的帮助器和视图保持面向对象的状态! 产品特点 仅在以下情况下自动将装饰器模块混合到相应的模型中: 从控制器到视图传递模型或模型集合或ActiveRecord ... -
在PowerPoint2010中默认的视图模式是A.普通视图B.阅读视图C.幻灯片浏览视图D.备注视图
2020-12-21 10:30:27在PowerPoint2010中默认的视图模式是A.普通视图B.阅读视图C.幻灯片浏览视图D.备注视图更多相关问题蔬菜植物春化中种子植物感应型的代表蔬菜是(B )等。季节变动一般是指市场现象以年度为周期,随着自然季节的变化,...在PowerPoint2010中默认的视图模式是A.普通视图B.阅读视图C.幻灯片浏览视图D.备注视图
更多相关问题
蔬菜植物春化中种子植物感应型的代表蔬菜是(B )等。
季节变动一般是指市场现象以年度为周期,随着自然季节的变化,每年都呈现的有规律的循环变动。( )
88. In the Northern Hemisphere, a wind that shifts counterclockwise is a ______.
世界三大无酒精饮料为咖啡、茶、可可。( )
强权政治主导下的实用主义,让国际反恐陷入 “越反越恐”的怪圈。
《圣经》如果被看为一个文学文本的话,它的文学性就在于其中的夸张性比喻等修辞。
领导开车时我坐在()
对同一行政相对人的同一违反行政管理秩序的行为不得以同一事实和同一依据给予两次以上的行政处罚。这体现了()
向候选人提问时,你不应该
提高了车辆运用效率和经济效益是公路零担货物运输的优点。
3.配合物[CoCl 2NH3(en)]Cl2中,中心原子的配位数为( )A.3B.4C.5D.6
( )是指企业决定同时经营两种或两种以上互相竞争的品牌
影视拍摄时,当拍摄环境不安全、不利于演员安全时,应该让演员自己注意安全。
提高了车辆运用效率和经济效益是公路零担货物运输的优点。
投资性房地产就是固定资产。
可吸入颗粒物的空气动力学直径是:
在会议开始或者结束时递送名片( )
11 a sentence ________
请选出带有辅音音素[z]的单词或词组:
某公司今年与上年相比,销售收入增长10%,净利润增长8%,资产总额增加12%,负债总额增加9%。可以判断,该公司净资产收益率比上年上升了。
若商品的价格1上涨6%,销售量下降6%,则销售额()
甲赠与乙一部手机。一个月后,乙将甲之子丙打成重伤。则( )。
财政机关预拨给行政、事业单位下年度的经费,应借记( )科目。
4. 甲公司与客户签订一合同以出售A、B、C三种产品,交易价格总价为100万元。A、B、C各产品的单独售价分别为40万元、55万元、45万元,合计140万元。该企业经常将B及C产品合并按60万元价格出售,经常将A产品按40万元出售。甲公司下列会计处理的表述中正确的有( )。
( )是指企业决定同时经营两种或两种以上互相竞争的品牌
•1.( )是研究学校情境中学与教的基本心理规律的科学。
有一批经过热变形的锻件,晶粒粗大,不符合质量要求,主要原因是( )。
88. In the Northern Hemisphere, a wind that shifts counterclockwise is a ______.
某公司今年与上年相比,销售收入增长10%,净利润增长8%,资产总额增加12%,负债总额增加9%。可以判断,该公司净资产收益率比上年上升了。
假设投资者小王手上有资金50000元,打算用来投资某个股权众筹项目,该项目单笔投资额为15000元,请问小王在进行认购操作时最多可认购几份项目份额?( )
( )是指企业决定同时经营两种或两种以上互相竞争的品牌
某国有一家非常受欢迎的冰淇淋店 , 最近将一种冰淇淋的单价从过去的 1.8 元提到 2 元 , 销售仍然不错。然而 , 在提价一周之内 , 几个服务员陆续辞职不干了。 下列哪一项最能解释上述现象?
蔬菜植物春化中种子植物感应型的代表蔬菜是(B )等。
某地进行国有商业企业经营情况调查,则调查对象是( )。
世界三大无酒精饮料为咖啡、茶、可可。( )
-
mysql实现物化视图
2020-01-14 10:30:33我们要知道售价和每种产品获得的利润,就要使用到两次的分组查询,我们晓得在mysql中连接查询和分组排序是会用到临时表和filesort的,这个如果数据量大的话,是十分耗时,如题查询如下: EXPLAIN SELECT ...写在前面,本文是博主在做数据库适配时遇到的问题。原项目用的是国产数据库人大金仓,因用户要求,需要迁移到mysql5.7上,在搜索解决方法之前有了大致思路就是建立物化视图表,然后使用存储过程或者触发器去更新物化视图表就可以了,但想要找有没有更优的方案,于是就进行了搜索。转载的本篇文字思路与我所想基本一致,步骤比较详细,转载留存一下。
在一次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的,这个如果数据量大的话,是十分耗时,如题查询如下:
EXPLAINSELECT 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_mvSELECT 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
*************************** 2. row ***************************
product_name: Pear
price_sum: 18.50
amount_sum: 6
price_avg: 9.25
amount_avg: 3
sales_cnt: 2
*************************** 3. row ***************************
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
SELECT product_name
, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount)
, COUNT(*)
FROM sales
GROUP BY product_name;
SET rc = 0;
END;
$$
DELIMITER ;
好!我们看下他的运行结果:
CALL refresh_mv_now(@rc);
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
*************************** 2. row ***************************
product_name: Pear
price_sum: 18.50
amount_sum: 6
price_avg: 9.25
amount_avg: 3
sales_cnt: 2
*************************** 3. row ***************************
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.00 sec)
INSERT INTO sales VALUES
(NULL, ‘Apple’, 2.25, 3), (NULL, ‘Plum’, 3.35, 1)
, (NULL, ‘Pear’, 1.80, 2);
CALL refresh_mv_now(@rc);
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
*************************** 2. row ***************************
product_name: Pear
price_sum: 18.50
amount_sum: 6
price_avg: 9.25
amount_avg: 3
sales_cnt: 2
*************************** 3. row ***************************
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.00 sec)
3.3
即时更新物化视图每条语句之后做全部更新没有任何意义。但是我们想要合适的结果。做到这一点还是有点复杂的。
在每次insert sales 表我们都要更新物化视图。我们可以在sales表中透明的使用insert/update/delete触发器实现。
现在来创建需要的触发器:
DELIMITER $$
CREATE TRIGGER sales_insAFTER INSERT ON sales
FOR EACH ROW
BEGIN
SET @old_price_sum = 0;SET @old_amount_sum = 0;
SET @old_price_avg = 0;
SET @old_amount_avg = 0;
SET @old_sales_cnt = 0;
SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(amount_avg, 0), IFNULL(sales_cnt, 0)
FROM sales_mv
WHERE product_name = NEW.product_name
INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_amount_avg, @old_sales_cnt;
SET @new_price_sum = @old_price_sum + NEW.product_price;SET @new_amount_sum = @old_amount_sum + NEW.product_amount;
SET @new_sales_cnt = @old_sales_cnt + 1;
SET @new_price_avg = @new_price_sum / @new_sales_cnt;
SET @new_amount_avg = @new_amount_sum / @new_sales_cnt;
REPLACE INTO sales_mvVALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_amount_avg, @new_sales_cnt)
;
END;$$
DELIMITER ;DELIMITER $$
CREATE TRIGGER sales_delAFTER DELETE ON sales
FOR EACH ROW
BEGIN
SET @old_price_sum = 0;SET @old_amount_sum = 0;
SET @old_price_avg = 0;
SET @old_amount_avg = 0;
SET @old_sales_cnt = 0;
SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(amount_avg, 0), IFNULL(sales_cnt, 0)
FROM sales_mv
WHERE product_name = OLD.product_name
INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_amount_avg, @old_sales_cnt
;
SET @new_price_sum = @old_price_sum - OLD.product_price;SET @new_amount_sum = @old_amount_sum - OLD.product_amount;
SET @new_price_avg = @new_price_sum / @new_amount_sum;
SET @new_sales_cnt = @old_sales_cnt - 1;
SET @new_amount_avg = @new_amount_sum / @new_sales_cnt;
REPLACE INTO sales_mvVALUES(OLD.product_name, @new_price_sum, @new_amount_sum, IFNULL(@new_price_avg, 0)
, IFNULL(@new_amount_avg, 0), @new_sales_cnt)
;
END;$$
DELIMITER ;DELIMITER $$
CREATE TRIGGER sales_updAFTER UPDATE ON sales
FOR EACH ROW
BEGIN
SET @old_price_sum = 0;SET @old_amount_sum = 0;
SET @old_price_avg = 0;
SET @old_amount_avg = 0;
SET @old_sales_cnt = 0;
SELECT IFNULL(price_sum, 0), IFNULL(amount_sum, 0), IFNULL(price_avg, 0), IFNULL(amount_avg, 0), IFNULL(sales_cnt, 0)
FROM sales_mv
WHERE product_name = OLD.product_name
INTO @old_price_sum, @old_amount_sum, @old_price_avg, @old_amount_avg, @old_sales_cnt
;
SET @new_price_sum = @old_price_sum + (NEW.product_price - OLD.product_price);SET @new_amount_sum = @old_amount_sum + (NEW.product_amount - OLD.product_amount);
SET @new_sales_cnt = @old_sales_cnt;
SET @new_price_avg = @new_price_sum / @new_sales_count;
SET @new_amount_avg = @new_amount_sum / @new_sales_cnt;
REPLACE INTO sales_mvVALUES(OLD.product_name, @new_price_sum, @new_amount_sum, IFNULL(@new_price_avg, 0)
, IFNULL(@new_amount_avg, 0), @new_sales_cnt)
;
END;$$
DELIMITER ;
现在来看结果:INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1);
INSERT INTO sales VALUES (NULL, 'Apple', 2.40, 2);
INSERT INTO sales VALUES (NULL, 'Apple', 4.05, 3);
INSERT INTO sales VALUES (NULL, 'Pear', 6.30, 2);
INSERT INTO sales VALUES (NULL, 'Pear', 12.20, 4);
INSERT INTO sales VALUES (NULL, 'Plum', 4.85, 3);
DELETE FROM sales WHERE sales_id = 5;DELETE FROM sales WHERE sales_id = 4;
UPDATE sales SET product_amount = 3 where sales_id = 2;
SELECT * from sales_v;
来看一下结果:INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1);
INSERT INTO sales VALUES (NULL, 'Apple', 2.40, 2);
INSERT INTO sales VALUES (NULL, 'Apple', 4.05, 3);
INSERT INTO sales VALUES (NULL, 'Pear', 6.30, 2);
INSERT INTO sales VALUES (NULL, 'Pear', 12.20, 4);
INSERT INTO sales VALUES (NULL, 'Plum', 4.85, 3);
DELETE FROM sales WHERE sales_id = 5;DELETE FROM sales WHERE sales_id = 4;
UPDATE sales SET product_amount = 3 where sales_id = 2;
SELECT * from sales_v;
3.4
具有快速拍摄功能的物化视图与上面例子的不同之处在于改变并不立即使用,改变存放在日志表中在一定时间之后才更新物化视图。
除了以上的的例子我们需要另外的日志表
CREATE TABLE sales_mvl (
product_name VARCHAR(128) NOT NULL
, product_price DECIMAL(8,2) NOT NULL
, product_amount SMALLINT NOT NULL
, sales_id INT UNSIGNED NOT NULL
, product_ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP()
);
不是更新mv日志是填充:DROP TRIGGER sales_ins;
DELIMITER $$
CREATE TRIGGER sales_insAFTER INSERT ON sales
FOR EACH ROW
BEGIN
INSERT INTO sales_mvlVALUES (NEW.product_name, NEW.product_price, NEW.product_amount, NEW.sales_id, NULL);
END;$$
DELIMITER ;DROP TRIGGER sales_del;
DELIMITER $$
CREATE TRIGGER sales_delAFTER DELETE ON sales
FOR EACH ROW
BEGIN
DELETE FROM sales_mvlWHERE sales_id = OLD.sales_id;
END;$$
DELIMITER ;DROP TRIGGER sales_upd;
DELIMITER $$
CREATE TRIGGER sales_updAFTER UPDATE ON sales
FOR EACH ROW
BEGIN
UPDATE sales_mvlSET product_name = NEW.product_name
, product_price = NEW.product_price
, product_amount = NEW.product_amount
, sales_id = NEW.sales_id
, product_ts = CURRENT_TIMESTAMP()
WHERE sales_id = OLD.sales_id;
END;$$
DELIMITER ;
建立一个更新物化视图的存储过程。可能模式:l 完全更新(更新至当前)
l 更新(更新到特定时间戳)
l 重建(全部重建清除MV日志表)
DELIMITER $$
CREATE PROCEDURE refresh_mv (IN method VARCHAR(16)
, IN ts TIMESTAMP
, OUT rc INT
)
BEGIN
IF UPPER(method) = 'REBUILD' THEN
TRUNCATE TABLE sales_mvl;TRUNCATE TABLE sales_mv;
INSERT INTO sales_mvSELECT product_name
, SUM(product_price), SUM(product_amount), AVG(product_price), AVG(product_amount)
, COUNT(*)
FROM sales
GROUP BY product_name
;
ELSEIF UPPER(method) = 'REFRESH FULL' THEN
REPLACE INTO sales_mvSELECT product_name, SUM(price_sum), SUM(amount_sum)
, SUM(price_sum)/SUM(sales_cnt), SUM(amount_sum)/SUM(sales_cnt)
, SUM(sales_cnt)
FROM (
SELECT product_name, price_sum, amount_sum, sales_cnt
FROM sales_mv
UNION ALL
SELECT product_name
, SUM(product_price), SUM(product_amount), COUNT(*)
FROM sales_mvl
GROUP BY product_name
) x
GROUP BY product_name
;
TRUNCATE TABLE sales_mvl;
SET rc = 0;ELSEIF UPPER(method) = 'REFRESH' THEN
REPLACE INTO sales_mvSELECT product_name, SUM(price_sum), SUM(amount_sum)
, SUM(price_sum)/SUM(sales_cnt), SUM(amount_sum)/SUM(sales_cnt)
, SUM(sales_cnt)
FROM (
SELECT product_name, price_sum, amount_sum, sales_cnt
FROM sales_mv
UNION ALL
SELECT product_name
, SUM(product_price), SUM(product_amount), COUNT(*)
FROM sales_mvl
WHERE product_ts < ts
GROUP BY product_name
) x
GROUP BY product_name
;
DELETEFROM sales_mvl
WHERE product_ts < ts
;
SET rc = 0;ELSE
SET rc = 1;
END IF;
END;$$
DELIMITER ;
检验是否正常工作:INSERT INTO sales VALUES (NULL, 'Apple', 1.25, 1);
wait some time
INSERT INTO sales VALUES (NULL, 'Apple', 2.40, 2);wait some time
INSERT INTO sales VALUES (NULL, 'Apple', 4.05, 3);wait some time
INSERT INTO sales VALUES (NULL, 'Pear', 6.30, 2);wait some time
INSERT INTO sales VALUES (NULL, 'Pear', 12.20, 4);wait some time
INSERT INTO sales VALUES (NULL, 'Plum', 4.85, 3);
SELECT * from sales_mvl;SELECT * from sales_v;
CALL refresh_mv('REFRESH', '2006-11-06 10:57:55', @rc);SELECT * from sales_v;
CALL refresh_mv('REFRESH FULL', NULL, @rc);SELECT * from sales_v;
CALL refresh_mv('REBUILD', NULL, @rc);SELECT * from sales_v;
4
结论上面有说道可以通过存储过程和mysql的触发器去进行更新,不过如果是项目中的一些后台展示功能,不是很紧要的展示功能可以通过java的scheduled执行定时任务,比如,一个小时查询一次新增数据。
MySQL中的触发器(5.0.27)是不是非常快。
大量数据时,物化视图可以提高及时查询速度。
如果插入数据速度上不受限制,这个方法可以提高查询速度。
在大量数据,或者是非内存表、或者内存不够大时,可以提高性能(即,在基础表中查询耗时较大时)
注:感谢小康同学,对本文档的部分翻译
参考文献:
http://www.fromdual.com/mysql-materialized-views
然后还可以通过 Justin Swanhart的开源工具Flexviews(http://code.google.com/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视图全面详解
2020-05-20 23:53:45视图 视图(View)是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在...
视图
视图(View)是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。
视图其实就是一个select返回的结果集,用于方便我们查询而创建的"临时表",简化我们的查询语句。
- 视图的优势
- 简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,对用户来说已经是过滤好的复合条件的结果集。
- 安全:使用视图的用户只能访问他们被允许查询的结果集,对表的权限管理并不能限制到某个行某个列,但是通过视图就可以简单的实现。
- 数据独立:一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响,源表增加列对视图没有影响;源表修改列名,则可以通过修改视图来解决,不会造成对访问者的影响。
使用视图的大部分情况是为了保障数据安全性,提高查询效率。
创建测试表:
CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `db01`; DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; insert into `class`(`id`,`name`) values (1,'Java'), (2,'UI'), (3,'产品'); DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*Data for the table `student` */ insert into `student`(`id`,`name`,`class_id`) values (1,'张三',1), (2,'李四',1), (3,'王五',2), (4,'赵刘',1), (5,'钱七',3);
1.1 创建视图
- 语法
CREATE [O R 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]
-
OR REPLACE:如图视图名重复,则表示替换原有视图
-
ALGORITHM:表示创建视图时所使用的算法
- UNDEFINED:默认值,没有指定算法时一般采用merge算法
- MERGE:合并算法,在基于视图创建新的视图时,将创建视图时所使用的select语句与将要创建新的视图的select语句进行合并处理,效率较高。
- TEMPTABLE:临时表算法,在基于视图创建新的视图时,先执行原有视图的select语句,然后再执行新视图的select语句,查询两次,效率较低。
示例:
create or replace ALGORITHM=MERGE view test1 as select * from user;
创建
test1
视图,如果此前已经有test1
视图,那么替换他,并且该视图的算法为merge
。(合并算法)- DEFINER:指出谁是视图的创建者
- USER:指定创建视图的用户
- CURRENT_USER:不指定创建视图的用户(默认是当前操作用户)
示例:
create or replace definer='root' view test2 as select * from user;
定义
test2
视图是root用户创建的。搭配SQL SECURITY
一起使用。- SQL SECURITY:设置操作的视图的安全策略
- DEFINER:只有创建视图的用户才有权访问
- INVOKER:任何用户都可以访问(前提必须有操作权限,如select、update权限等)
示例:
create sql security=invoker view test3 as select * from user;
代表任何人都具备操作
test3
视图的权限。-
WITH:更改视图数据时,检查数据
- LOCAL:更改视图时,如果更改后的数据不符合当初创建视图时的条件时,则不予修改,并报如下错误:
CHECK OPTION failed 'dbName.viewName
示例:
-- 创建视图 mysql> create view test4 as select * from student where class_id=1 with local check option; Query OK, 0 rows affected (0.00 sec) mysql> update test4 set class_id=2 where id=1; ERROR 1369 (HY000): CHECK OPTION failed 'db01.test4' mysql>
LOCAL:只会检查当前修改的条件是否符合当前视图创建时的条件,如果在创建此视图时使用到了别的视图,LOCAL方式不会检查其嵌套的视图。
小测试:
mysql> select * from test4; +----+--------+----------+ | id | name | class_id | +----+--------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 4 | 赵刘 | 1 | +----+--------+----------+ 3 rows in set (0.00 sec) mysql> create or replace view test5 as select * from test4 where name='张三' with local check option; Query OK, 0 rows affected (0.00 sec) mysql> update test5 set class_id=2 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
只要修改后的数据满足本次视图创建的条件即可。不需要满足创建此视图所引用的其他视图的创建条件。
- CASCADED:在视图嵌套的过程中,修改当前视图中的数据,必须满足创建此视图所引用到的所有视图的创建条件。
示例:
mysql> select * from student; +----+--------+----------+ | id | name | class_id | +----+--------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 3 | 王五 | 2 | | 4 | 赵刘 | 1 | | 5 | 钱七 | 3 | +----+--------+----------+ 5 rows in set (0.00 sec) mysql> select * from test4; +----+--------+----------+ | id | name | class_id | +----+--------+----------+ | 1 | 张三 | 1 | | 2 | 李四 | 1 | | 4 | 赵刘 | 1 | +----+--------+----------+ 3 rows in set (0.00 sec) mysql> create or replace view test5 as select * from test4 where name='张三' with cascaded check option; Query OK, 0 rows affected (0.00 sec) mysql> update test5 set class_id=2 where id=1; # 不满足创建test4视图的条件 ERROR 1369 (HY000): CHECK OPTION failed 'db01.test5' mysql> update test5 set name='zs' where id=1; # 不满足创建test5视图的条件 ERROR 1369 (HY000): CHECK OPTION failed 'db01.test5' mysql>
CASCADED
不仅要满足当前视图的创建条件,也需要满足本次创建视图时引用到的其他视图的创建条件。
1.2 修改视图
1)创建时修改
- 语法:
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]
- 示例:
create or replace view test4 as select * from student where class_id=1;
2)使用alter语句更改视图
- 语法:
ALTER [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]
- 示例:
alter ALGORITHM=TEMPTABLE view test4 as select * from student where class_id=3;
3)更新视图
- 语法:
update viewName set column=newValue
- 示例:
update test4 set name='qq' where id=5;
有下列内容之一,视图不能做DML操作:1)select子句中包含distinct
2)select子句中包含组函数
3)select语句中包含group by子句
4)select语句中包含order by子句
5)select语句中包含union 、union all等集合运算符
6)where子句中包含相关子查询
7)from子句中包含多个表
8)如果视图中有计算列,则不能更新
9)如果基表中有某个具有非空约束的列未出现在视图定义中,则不能做insert操作
1.3 删除视图
删除视图时,只能删除视图的定义,不会删除数据,也就是说不动基表
- 语法:
DROP VIEW [IF EXISTS] view_name [, view_name] ...
- 示例:
drop view test1,test2,test3,test4;
- 视图的优势
-
SQL VIEW(视图)
2021-03-09 23:38:25SQL CREATE VIEW 实例可以从某个查询内部、某个存储过程内部,或者从另一个视图内部来使用视图。...视图 "Current Product List" 会从 Products 表列出所有正在使用的产品。这个视图使用下列 SQL 创建:CREATE VIEW... -
mysql实现物化视图详解及视图与物化视图区别
2017-08-01 11:07:58物化视图是相对于视图而言的,但是两者实际上并没有什么关系就如java/javaScript一样 首先mysql的视图不是一种物化视图,他相当于一个虚拟表,本身并不存储数据,当sql在操作视图时所有数据都是从其他表中查询出来的... -
mysql如何创建视图
2021-01-12 11:53:41在 SQL 中,视图是基于 SQL 语句的结果集的可视化的表。 视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句,我们... -
mysql视图创建注意事项(转)
2021-01-28 04:28:08视图概述视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用... -
使用视图
2018-06-03 21:00:25一、视图 视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。二、使用视图的意义 1.替代重复的SQL语句; 2.简化复杂的SQL操作。在编写查询后,可以方便地重用它,而不必知道它的基本... -
UG NX 12 视图布局
2021-09-22 10:05:06在进行三维产品设计时,可能会同时应用到一个模型对象的多个视图,这样可以更直观地从多角度观察模型对象,如下图所示。 NX 12 提供了实用的视图布局功能,包括新建视图布局、打开视图布局、保存视图布局布置、... -
(二十一) Oracle 视图和分页
2019-08-01 21:40:10文章目录创建数据库其他对象视图什么是视图视图的优越性视图类型创建一个简单视图用子查询中的列别名创建视图从视图中取回数据创建一个复杂视图指定列别名创建视图中DML操作的执行规则拒接DML操作删除视图内建视图... -
mysql创建用户视图 事务存储过程 触发器 约束的打怪
2021-01-19 01:24:04显示所有视图 Select * from information_schema.views \G 6.修改视图 Alter view 视图名称 as select 查询 查询(使用)视图 7.更新视图中数据 8.删除视图 Drop view 视图的名称 9.视图算法 概述: 指的是一个视图是在... -
MYSQL学习与数据库综合实验(四)——视图实验
2020-12-05 03:02:524.视图实验 4.1视图的定义 MySQL 视图(View)是一种虚拟存在的表,同真实表一样,视图也由列和行构成,但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表,并且还是在使用视图时... -
5.3 使用查询设计视图
2021-07-16 16:04:56查询设计视图是创建、编辑和修改查询的基本工具。 使用查询向导虽然可以快速地创建查询,但是对于创建指定条件的查询、创建参数查询和创建复杂的查询,查询向导就不能完全胜任了,这种情况下,可以使用查询设计视图... -
数据库原理及应用——数据更新和视图创建
2022-01-15 17:31:58(1)所使用的学生管理库中的三张表: (2)数据更新具体完成以下例题: (3)视图操作具体完成以下例题: -
数据库之视图、索引
2016-10-10 09:03:01数据库视图以及索引的概念和作用介绍 -
SQL 使用视图
2018-06-16 20:44:29视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询。下面输入▼SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND ... -
关系数据库——视图/存储过程/触发器
2019-12-02 09:51:43视图 视图是虚拟的表,与包含数据的表不同,视图只包含使用时动态检索数据的查询,主要是用于查询。 为什么使用视图 重用sql语句 简化复杂的sql操作,在编写查询后,可以方便地重用它而不必知道他的基本查询细节... -
3.SQL视图的定义和操作
2022-04-10 15:03:38SQL视图操作 -
亚马逊采摘挑战赛APC:6D姿态估计的多视图自我监督深度学习6D Pose Estimation in the Amazon Picking ...
2019-10-07 17:49:20Multi-view Self-supervised...亚马逊采摘挑战赛(APC)中用于6D姿态估计的多视图自我监督深度学习 文章所提出的姿态估计方法,实际上属于简化的基于模板匹配的方法。利用CNN对多视图RGB-D图像点云进行分割,并将其... -
ASP.NET MVC4 Razor视图企业网站模板
2018-08-06 11:38:16.NET MVC Razor视图企业网站模板源码 源码描述: 一、源码特点 本系统采用asp.net mvc+easy ui 开发,包含新闻管理,产品管理,留言管理,下载管理,焦点图片管理等多个模块,操作简便快捷,界面友好,通用性强,... -
第2.8章:StarRocks表设计--物化视图
2022-01-04 22:19:17在实际的业务中,我们通常需要对数据进行对原始明细数据任意维度的分析以及固定维度聚合分析。两种场景并存的情况下,聚合...物化视图就是StarRocks为明细模型打造的“预计算”方案,本质上也是“空间换时间”,但实现 -
mysqldump的使用--解决不能导出视图的问题
2020-12-24 17:08:45在产品上线的过程中,发现同事恢复的数据库出现了问题,测试人员发现试图中没有数据,而视图基于的表是有数据的,开始以为是关联的字段没有相关匹配出现空数据。后来随着测试的进行,发现以前通过的测试用例不能通过... -
SQL数据库数据更新、视图操作
2019-07-06 10:14:50将每个订单的订单编号,顾客编号,产品总数量,总金额插入到数据库中 create table Order_Information (OrdersID int, CustomerID nchar(5),Number smallint ,Totalmoney money) insert into Order_Information ... -
架构蓝图设计之4+1视图解析
2019-07-10 20:25:23图 6 代表了加拿大的 Hughes Aircraft 开发的空中交通控制系统(Air Traffic Control system)产品线的 5 个分层开发组织结构。这是和图 3 b 描述的逻辑架构相对应的开发架构。 第一层 和第二层组成了独立于域的... -
(3)在产品销售数据库上创建冰箱产品表的视图BXCP。 (4)在BXCP视图上查询库存量在100台以下的产品编号。_学...
2021-01-28 04:39:38(3)在产品销售数据库上创建冰箱产品表的视图BXCP。 (4)在BXCP视图上查询库存量在100台以下的产品编号。 【填空题】一维无限深势阱中的粒子,处于基态时,在 处概率密度最大 【单选题】气门重叠角: 进、排气门同时开启... -
MySQL入门(11)——基于datagrip的表关联查询及视图
2021-06-09 12:41:076、视图不能索引,也不能有关联的触发器或默认值。 7、视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。 2、创建视图 在理解什么是视图(以及管理它们的规则及约束)后,来看一下视图的创建。 视图... -
建立索引和创建视图(结合YGGL.sql)
2021-01-19 03:39:25Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 (3) 创建表的同时创建索引 创建表cpk(产品编号,产品名称,单价,库存量),并对产品编号创建主键,在库存量和单价上创建复合索引cpk...