MySQL中实现物化视图

最新推荐文章于 2024-08-15 04:10:12 发布
最新推荐文章于 2024-08-15 04:10:12 发布
阅读量1.6w 收藏 8
点赞数 1
分类专栏： MySQL 文章标签： mysql null insert apple table delete
本文介绍MySQL中物化视图的概念及其实现方法，包括创建、按需更新和即时更新物化视图等，并提供了具体的示例和存储过程。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

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_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

*************************** 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_ins

AFTER 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_mv

  VALUES(NEW.product_name, @new_price_sum, @new_amount_sum, @new_price_avg, @new_amount_avg, @new_sales_cnt)

  ;



END;

$$



DELIMITER ;

DELIMITER $$



CREATE TRIGGER sales_del

AFTER 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_mv

  VALUES(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_upd

AFTER 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_mv

  VALUES(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_ins

AFTER INSERT ON sales

FOR EACH ROW

BEGIN



  INSERT INTO sales_mvl

  VALUES (NEW.product_name, NEW.product_price, NEW.product_amount, NEW.sales_id, NULL);



END;

$$



DELIMITER ;

DROP TRIGGER sales_del;



DELIMITER $$



CREATE TRIGGER sales_del

AFTER DELETE ON sales

FOR EACH ROW

BEGIN



  DELETE FROM sales_mvl

  WHERE sales_id = OLD.sales_id;



END;

$$



DELIMITER ;

DROP TRIGGER sales_upd;



DELIMITER $$



CREATE TRIGGER sales_upd

AFTER UPDATE ON sales

FOR EACH ROW

BEGIN



  UPDATE sales_mvl

     SET 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_mv

  SELECT 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_mv

  SELECT 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_mv

  SELECT 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

  ;



  DELETE

    FROM 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中的触发器（5.0.27）是不是非常快。

大量数据时，物化视图可以提高及时查询速度。

如果插入数据速度上不受限制，这个方法可以提高查询速度。

在大量数据，或者是非内存表、或者内存不够大时，可以提高性能(即，在基础表中查询耗时较大时)

注：感谢小康同学，对本文档的部分翻译

参考文献：

http://www.fromdual.com/mysql-materialized-views

Mysql上实现物化视图的功能
Gemini1995的博客
09-21 2143
在 MySQL 中，物化视图（Materialized View）并不像某些数据库（如 Oracle、PostgreSQL）那样作为一个内置特性存在。物化视图本质上是一个定期更新的视图，它的结果会存储在磁盘上，供后续查询使用，而不需要每次查询时重新计算数据。
ClickHouse 物化视图从 MySQL 获取数据的方式
最新发布
red_eye的博客
06-16 236
最佳实践取决于您的具体需求(实时性要求、数据量、查询模式等)。对于生产环境，通常推荐使用 MaterializedMySQL 或 ETL 流程。
mysql create view 物化视图
weixin_35682010的博客
07-28 326
MySQL中的物化视图：提升查询性能的利器 在数据库管理中，视图是一种虚拟表，其内容由SQL查询定义。视图可以简化复杂的查询，提高数据的安全性和易用性。然而，传统的视图在查询时需要实时计算，可能影响性能。为了解决这个问题，MySQL引入了物化视图（Materialized View），它将查询结果预先计算并存储在物理表中，...
mysql 创建物化视图_MySQL中实现物化视图 | 学步园
weixin_42520239的博客
01-19 1382
0.什么是物化视图物化视图是查询结果的预运算。不同于简单的视图，物化视图的结果一般存储于表中。物化视图用于需要对查询立即做出响应，而又需要耗费很长时间获得结果。物化视图必须能快速更新。它取决于对更新频率和内容的准确性的要求。一般说来物化视图能够在一定时间内及时更新。Mysql本来是不支持视图的，但是在5.0以上的版本，支持了视图功能，但是可惜的是不提供物化视图，但是这也难不住咱们，自己动手丰衣足食...
Mysql 物化视图
时刻学习
10-12 1605
myql最新的8.0不支持物化视图
mysql触发器实现oracle物化视图示例代码
09-10
-- 获取物化视图中当前产品的累计信息 select ifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0) from Order_mv where product_name=new.product_name into @old_price_sum...
Mysql重要内容(视图以及物化视图)
m0_46741750的博客
09-12 4537
简介 这里我想说的是，程序本身不需要关心表的结构，只需要按照视图定义来取数据或更新数据。 什么是视图 说起视图呢，就是说它相当于一个虚拟的表，你看不到它，但是你可以根据它来更新和操作表，视图中的with check option就是针对于可更新的视图的 例子 首先我们来创建一个叫做t的表，然后再创建一个视图，这个视图当中的数据是和t表相关的，也就是对这个表进行了一个限制 create table t (id int); create view v_t as select * from t where id
【MySQL】mysql 视图转物化视图
wjianwei666的专栏
01-28 1043
create view stu_class (编号,姓名,班级)asselect student.id,student.name,stu_info.class from student,stu_info where student.id=stu_info.id;例一：现有一stu_info表（id，class，address），id为学生编号，创建视图，包含编号，姓名，班级。例一：现有student表（id，name，math，chinese），创建视图包含数学成绩，语文成绩，总成绩。
mysql 物化视图_MySQL中的物化视图
weixin_34082139的博客
01-18 239
How to create a materialized view in mysql ?I am not able to create a materialized view as we do in MS SQL Server.Could anyone let me know the ways to create it in mysql.解决方案You can create a non-updat...
物化视图mysql创建
weixin_33895572的博客
08-15 198
我整理的一些关于【MySQL,SQL】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/yOSbkR物化视图在 MySQL 中的创建 物化视图（Materialized View）是数据库中的一种数据对象，它可以存储查询结果。当涉及到复杂的查询或大数据量时，通过物化视图可以显著...
【MySQL】物化视图
咔咔博客
07-25 9520
物化视图是相对于视图而言的，但是两者实际上并没有什么关系就如java/javaScript一样 首先mysql的视图不是一种物化视图，他相当于一个虚拟表，本身并不存储数据，当sql在操作视图时所有数据都是从其他表中查询出来的。者带来的问题是使用视图并不能将常用数据分离出来，优化查询速度，切操作视图的很多命令和普通标一样，这回导致在业务中无法通过sql区分表和视图，使代码变得复杂。 视图是简化设计，...
关于MySQL的物化视图
libie_lt的专栏
12-03 580
请参考MySQL官方文档： https://dev.mysql.com/doc/refman/8.0/en/faqs-views.html#faq-mysql-where-docs-views
mysql 5.6物化视图_Mysql物化视图应用
weixin_35755188的博客
01-27 523
1、基本api//第一步：创建物化视图call flexviews.CREATE('demo','mv_company_sales_items_monthly','INCREMENTAL');----------数据库、视图名称、刷新类型(增量刷新)//获取视图idselect @mvid := last_insert_id();//第二步：增加表，where条件用到call flexviews....
mysql 视图会走索引吗_索引-如何使MySQL使用INDEX进行视图查询？
weixin_39527078的博客
12-20 1273
如何让MySQL使用索引进行视图查询？ 简短的答案，提供了MySQL可以使用的索引。在这种情况下，最佳索引可能是“覆盖”索引：... ON highscores (player, happened_in, score)MySQL可能会使用该索引，并且EXPLAIN将显示：player(由于player = 24(索引前导列上的相等谓词。GROUP BY(索引中的第二列))，可能允许MySQL优化使...
2.物化视图与事务
qq_28613081的博客
05-06 320
1. 物化视图 物化视图是相对于视图而言的，但是两者实际上并没有什么关系就如java/javaScript一样 首先mysql的视图不是一种物化视图，他相当于一个虚拟表，本身并不存储数据，当sql在操作视图时所有数据都是从其他表中查询出来的。者带来的问题是使用视图并不能将常用数据分离出来，优化查询速度，切操作视图的很多命令和普通标一样，这回导致在业务中无法通过sql区分表和视图，是代码变得复杂...
mysql实现物化视图详解及视图与物化视图区别
热门推荐
Just do it!
08-01 3万+
物化视图是相对于视图而言的，但是两者实际上并没有什么关系就如java/javaScript一样 首先mysql的视图不是一种物化视图，他相当于一个虚拟表，本身并不存储数据，当sql在操作视图时所有数据都是从其他表中查询出来的。者带来的问题是使用视图并不能将常用数据分离出来，优化查询速度，切操作视图的很多命令和普通标一样，这回导致在业务中无法通过sql区分表和视图，是代码变得复杂。 实现视图的方
使用MySQL创建物化视图
DfsnVue的博客
10-08 711
在MySQL中，虽然没有直接提供物化视图的功能，但我们可以通过创建临时表和触发器来模拟其行为。物化视图是数据库中的一种特殊对象，它存储了查询的结果集，并在需要时自动刷新。尽管这种方法可以模拟物化视图的行为，但它并没有提供物化视图的所有功能。如果需要更高级的功能，可能需要考虑使用其他数据库管理系统，如PostgreSQL，它提供了原生的物化视图支持。它首先删除物化视图表中的所有数据，然后将源表的数据插入物化视图表中。需要注意的是，每当源表发生更改时，触发器会自动更新物化视图的数据。
mysql物化视图_如何实现物化视图与MySQL？
weixin_29574585的博客
01-18 355
How to implement Materialized Views?If not, how can I implement Materialized View with MySQL?Update:Would the following work? This doesn't occur in a transaction, is that a problem?DROP TABLE IF EXIST...
mysql8.0物化视图
06-11
### MySQL 8.0 中物化视图的实现与使用 在 MySQL 8.0 中，虽然没有直接支持物化视图的功能，但可以通过创建临时表和触发器来模拟其行为[^2]。这种方法可以提高查询性能，并确保当源表发生更改时，物化视图的数据能够自动更新。 #### 模拟物化视图的步骤 1. **创建基表** 首先需要创建一个包含基础数据的表，这是物化视图的数据来源。 ```sql CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), department_id INT, salary DECIMAL(10, 2) ); ``` 2. **创建物化视图的存储结构** 使用 `CREATE TABLE` 创建一个物理表，用于存储物化视图的数据。 ```sql CREATE TABLE mv_employee_summary ( department_id INT, total_salary DECIMAL(15, 2), employee_count INT, PRIMARY KEY (department_id) ); ``` 3. **初始化物化视图数据** 将基表中的数据聚合后插入到物化视图表中。 ```sql INSERT INTO mv_employee_summary (department_id, total_salary, employee_count) SELECT department_id, SUM(salary), COUNT(*) FROM employees GROUP BY department_id; ``` 4. **创建触发器以维护物化视图** 当基表的数据发生变化时（如插入、更新或删除），触发器会自动更新物化视图表中的数据。 - **插入触发器** ```sql DELIMITER $$ CREATE TRIGGER after_employee_insert AFTER INSERT ON employees FOR EACH ROW BEGIN IF EXISTS (SELECT 1 FROM mv_employee_summary WHERE department_id = NEW.department_id) THEN UPDATE mv_employee_summary SET total_salary = total_salary + NEW.salary, employee_count = employee_count + 1 WHERE department_id = NEW.department_id; ELSE INSERT INTO mv_employee_summary (department_id, total_salary, employee_count) VALUES (NEW.department_id, NEW.salary, 1); END IF; END$$ DELIMITER ; ``` - **更新触发器** ```sql DELIMITER $$ CREATE TRIGGER after_employee_update AFTER UPDATE ON employees FOR EACH ROW BEGIN IF OLD.department_id != NEW.department_id OR OLD.salary != NEW.salary THEN UPDATE mv_employee_summary SET total_salary = total_salary - OLD.salary, employee_count = employee_count - 1 WHERE department_id = OLD.department_id; IF NEW.department_id IS NOT NULL THEN IF EXISTS (SELECT 1 FROM mv_employee_summary WHERE department_id = NEW.department_id) THEN UPDATE mv_employee_summary SET total_salary = total_salary + NEW.salary, employee_count = employee_count + 1 WHERE department_id = NEW.department_id; ELSE INSERT INTO mv_employee_summary (department_id, total_salary, employee_count) VALUES (NEW.department_id, NEW.salary, 1); END IF; END IF; END IF; END$$ DELIMITER ; ``` - **删除触发器** ```sql DELIMITER $$ CREATE TRIGGER after_employee_delete AFTER DELETE ON employees FOR EACH ROW BEGIN UPDATE mv_employee_summary SET total_salary = total_salary - OLD.salary, employee_count = employee_count - 1 WHERE department_id = OLD.department_id; IF (SELECT employee_count FROM mv_employee_summary WHERE department_id = OLD.department_id) = 0 THEN DELETE FROM mv_employee_summary WHERE department_id = OLD.department_id; END IF; END$$ DELIMITER ; ``` #### 注意事项 - 物化视图的维护成本较高，特别是在频繁更新的环境中。因此，在设计时需要权衡查询性能和维护开销。 - 触发器的逻辑需要根据具体需求进行调整，例如是否支持多列更新或复杂条件[^2]。 ```sql -- 查询物化视图 SELECT * FROM mv_employee_summary; ``` #### 视图修改语法 如果需要对物化视图的定义进行修改，可以通过重新创建表或使用 `ALTER VIEW` 语句完成。需要注意的是，`ALTER VIEW` 不适用于物化视图，因为它是基于物理表的。因此，通常需要手动删除并重新创建表[^3]。 ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值