如何用 MySQL 实现物化视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2534506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to implement Materialized View with MySQL?
提问by GregJohn
How to implement Materialized Views?
如何实现物化视图？
If not, how can I implement Materialized View with MySQL?
如果没有，我如何使用 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`;
回答by Justin Swanhart
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.
我维护了一个名为 Flexviews ( http://github.com/greenlion/swanhart-tools)的项目，它为 MySQL 添加了可增量刷新的物化视图（又名快速刷新），即使对于使用连接和聚合的视图也是如此。我已经在这个项目上工作了三年。它包括一个更改数据捕获实用程序来读取数据库日志。不使用触发器。
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.
它包括两种刷新方法。第一个与您的方法类似，只是构建了一个新版本，然后使用 RENAME TABLE 将新版本替换为旧版本。该视图在任何时候都无法用于查询，但会在短时间内使用 2 倍的空间。
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/
有一篇关于它的博客文章：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.
Flexviews 比 asstander 引用的 FromDual 示例要先进得多。
回答by codenheim
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.
您可以通过使用插入/更新（upsert）将现有的“视图表”与源视图的主键连接起来（假设它们可以保留键）或保留上次更新的 date_time 来近似快速刷新，并使用它在刷新 SQL 的标准中减少刷新时间。
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.
此外，请考虑使用表重命名，而不是删除/创建，这样新视图可以在几乎没有不可用间隙的情况下构建和放置到位。先建立一个新表'mview_new'，然后将'mview'重命名为'mview_old'（或删除它），并将'mview_new'重命名为'mview'。在上面的示例中，当您的 SQL 填充运行时，您的视图将不可用。
回答by s2t2
According to the mySQL docs and comments at the bottom of the page, it just seems like people are creating views then creating tables from those views. Not sure if this solution is the equivalent of creating a materialized view, but it seems to be the only avenue available at this time.
根据页面底部的mySQL 文档和评论，似乎人们正在创建视图，然后从这些视图创建表。不确定这个解决方案是否等同于创建物化视图，但它似乎是目前唯一可用的途径。
回答by coding-dude.com
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.
我一直在试验甚至在生产中部署了几种在 MySQL 中拥有物化视图的方法。基本上所有方法都假设您创建一个普通视图并将数据传输到一个普通表 - 实际的物化视图。然后，这只是如何刷新物化视图的问题。
Here's what I've success with so far:
到目前为止，这是我取得的成功：
- 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
- 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.
- Using MySQL scheduled events - similar to 2, but runs inside the database
- Flexviews - using FlexDC mentioned by Justin. The closest thing to real materialized
- 使用触发器 - 您可以在构建视图的源表上设置触发器。这最大限度地减少了资源使用，因为仅在需要时才进行刷新。此外，物化视图中的数据是实时的
- 将 cron 作业与存储过程或 SQL 脚本一起使用 - 定期进行刷新。您可以更好地控制何时使用资源。显然，您的数据只有在刷新率允许的情况下才新鲜。
- 使用 MySQL 计划事件 - 类似于 2，但在数据库内部运行
- Flexviews - 使用 Justin 提到的 FlexDC。最接近真实的东西实现了
I've been collecting and analyzing these methods, their pros and cons in my article Creating MySQL materialized views
我一直在我的文章创建 MySQL 物化视图中收集和分析这些方法及其优缺点
looking forwards for feedback or proposals for other methods for creating materialized views in MySQL
期待在 MySQL 中创建物化视图的其他方法的反馈或建议
