如何用MySQL实现物化视图?[英] How to implement Materialized View with MySQL?

本文是小编为大家收集整理的关于如何用MySQL实现物化视图?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

如何实现实体视图?

如果没有,我该如何使用MySQL实现实体视图?

更新:

以下工作会吗?这不是在交易中发生的,这是一个问题吗?

DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;

推荐答案

我维护一个名为FlexViews的项目(工具),它为MySQL(又称快速刷新)添加了可增值的实质性视图,即使用于使用加入和聚合的视图.我从事这个项目已经三年了.它包括更改数据捕获实用程序以读取数据库日志.没有使用触发器.

它包括两种刷新方法.第一个类似于您的方法,除了建立新版本,然后将重命名表用于将新版本交换为旧版本.查询的视图绝对不可用,但是2倍空间短时间使用.

第二种方法是真实的"快速刷新",它甚至对聚合和加入也有支持.

有一篇博客文章: http://http://www.mysqlperformanceblog.com/2011/04/04/flexviews-part-3-improving-query-performance-using-susing-materialized-views/

flexViews比Astander引用的单示例要高得多.

其他推荐答案

您的示例近似于"完整刷新"实现的视图.您可能需要一个"快速刷新"视图,通常在数据仓库设置中使用,如果源表包括数百万或数十行.

您将通过使用insert/update(UPSERT)来近似快速刷新(UPSERT),将现有的"视图表"与源视图的主要键(假设它们可以保留键)或保留最后一个更新的date_time,并在刷新SQL的标准中使用它来减少刷新时间.

另外,考虑使用表重命名而不是掉落/创建,因此可以在几乎没有无法获得的差距的情况下构建和放置新视图.首先构建一个新的表'mview_new',然后将" mview"重命名为" mview_old"(或删除它),然后将" mview_new"命名为" mview".在上面的示例中,您的SQL填充运行时,您的视图将不可用.

其他推荐答案

这个线程很旧,所以我会尝试重新修复它:

我一直在尝试,甚至在生产中部署了几种在MySQL中具有实体视图的方法.基本上,所有方法都假设您创建一个正常的视图并将数据传输到普通表格 - 实际实现的视图.然后,这只是您如何刷新物化视图的问题.

这是到目前为止我成功的原因:

  1. 使用触发器 - 您可以在构建视图的源表上设置触发器.这将资源使用量最小化,因为只有在需要时进行刷新才能进行刷新.此外,实体视图中的数据是实时
  2. 使用存储过程或SQL脚本使用CRON作业 - 定期进行刷新.您可以控制何时使用资源.显然,您的数据仅与刷新率允许一样新鲜.
  3. 使用MySQL计划的事件 - 类似于2,但在数据库内运行
  4. FlexViews - 使用Justin提到的FlexDC.最接近真正物质化的东西

我一直在收集和分析这些方法,他们的文章中的利弊创建MySQL物质视图

期待反馈或建议在MySQL

中创建实体视图的其他方法

本文地址:https://www.itbaoku.cn/post/597583.html

问题描述

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 EXISTS `myDatabase`.`myMaterializedView`;
CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;

推荐答案

I maintain a project called Flexviews (http://github.com/greenlion/swanhart-tools) which adds incrementally refreshable materialized views to MySQL (aka fast refresh), even for views that use joins and aggregation. I've been working on this project for three years. It includes a change data capture utility to read the database logs. No triggers are used.

It includes two refresh methods. The first is similar to your method, except a new version is built, and then RENAME TABLE is used to swap the new for the old. At no point is the view unavailable for querying, but 2x the space is used for a short time.

The second method is true "fast refresh", it even has support for aggregation and joins.

There is a blog post about it: http://www.mysqlperformanceblog.com/2011/04/04/flexviews-part-3-improving-query-performance-using-materialized-views/

Flexviews is significantly more advanced than the FromDual example referenced by astander.

其他推荐答案

Your example approximates a "full refresh" materialized view. You may need a "fast refresh" view, often used in a data warehouse setting, if the source tables include millions or billions of rows.

You would approximate a fast refresh by instead using insert / update (upsert) joining the existing "view table" against the primary keys of the source views (assuming they can be key preserved) or keeping a date_time of the last update, and using that in the criteria of the refresh SQL to reduce the refresh time.

Also, consider using table renaming, rather than drop/create, so the new view can be built and put in place with nearly no gap of unavailability. Build a new table 'mview_new' first, then rename the 'mview' to 'mview_old' (or drop it), and rename 'mview_new' to 'mview'. In your above sample, your view will be unavailable while your SQL populate is running.

其他推荐答案

This thread is rather old, so I will try to re-fresh it a bit:

I've been experimenting and even deployed in production several methods for having materialized views in MySQL. Basically all methods assume that you create a normal view and transfer the data to a normal table - the actual materialized view. Then, it's only a question of how you refresh the materialized view.

Here's what I've success with so far:

  1. Using triggers - you can set triggers on the source tables on which you build the view. This minimizes the resource usage as the refresh is only done when needed. Also, data in the materialized view is realtime-ish
  2. Using cron jobs with stored procedures or SQL scripts - refresh is done on a regular basis. You have more control as to when resources are used. Obviously you data is only as fresh as the refresh-rate allows.
  3. Using MySQL scheduled events - similar to 2, but runs inside the database
  4. Flexviews - using FlexDC mentioned by Justin. The closest thing to real materialized

I've been collecting and analyzing these methods, their pros and cons in my article Creating MySQL materialized views

looking forwards for feedback or proposals for other methods for creating materialized views in MySQL