如何用MySQL实现物化视图？
如何实现物化视图？
如果没有，我如何使用 MySQL 实现物化视图？
更新：
以下操作可行吗？这不会发生在交易中，这是一个问题吗？
DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`;
CREATE TABLE `myDatabase`.`myMaterializedView` SELECT * from `myDatabase`.`myRegularView`;
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`;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我维护 LeapDB (http://www.leapdb.com)，它向 MySQL 添加了增量可刷新的物化视图（又名快速刷新），即使对于使用联接和聚合的视图也是如此。我已经为这个项目工作了 13 年。它包括一个更改数据捕获实用程序来读取数据库日志。不使用触发器。
它包括两种刷新方法。第一种与您的方法类似，只是构建了新版本，然后使用 RENAME TABLE 将新版本替换为旧版本。视图在任何时候都不可用于查询，但会在短时间内使用 2 倍的空间。
第二种方法是真正的“快速刷新”，它甚至支持聚合和连接。
LeapDB 比 astander 引用的 FromDual 示例要先进得多。
I maintain LeapDB (http://www.leapdb.com) 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 13 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.
LeapDB is significantly more advanced than the FromDual example referenced by astander.
您的示例近似于“完全刷新”物化视图。如果源表包含数百万或数十亿行，您可能需要一个“快速刷新”视图，通常在数据仓库设置中使用。
您可以通过使用插入/更新（upsert）将现有的“视图表”连接到源视图的主键（假设它们可以保留键）或保留上次更新的日期时间来近似快速刷新，并使用它在刷新SQL的条件中减少刷新时间。
另外，请考虑使用表重命名，而不是删除/创建，以便可以构建新视图并将其放置到位，几乎不会出现不可用的情况。首先构建一个新表“mview_new”，然后将“mview”重命名为“mview_old”（或删除它），并将“mview_new”重命名为“mview”。在上面的示例中，当 SQL 填充运行时，您的视图将不可用。
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.
这个线程相当旧，所以我会尝试稍微刷新一下：
我一直在试验甚至在生产中部署了几种在 MySQL 中实现物化视图的方法。基本上所有方法都假设您创建一个普通视图并将数据传输到普通表 - 实际的物化视图。那么，这只是如何刷新物化视图的问题。
以下是我到目前为止所取得的成功：
我一直在我的文章中收集和分析这些方法及其优缺点创建 MySQL 物化视图
期待反馈或建议在 MySQL 中创建物化视图的其他方法
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:
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
根据页面底部的mySQL文档和注释，看起来人们正在创建视图，然后从这些视图创建表。不确定此解决方案是否相当于创建物化视图，但它似乎是目前唯一可用的途径。
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.