创建物化视图和新表哪个更好？
我有一些要求较高的 mysql 查询，必须从 5-7 mysql 表中选择相同的经常更新的数据集。 “选择”操作比 CUD 操作要多一些。
我正在考虑创建一个表或物化视图来收集其他表中所有要求较高的列，从而减少对不同表的总体查询时间，从而提高性能。
如果我创建该表，每次更新其他表时，我可能需要执行额外的插入/更新/删除操作。
如果我创建物化视图，我担心性能是否可以大大提高。因为其他表的数据变化非常频繁。最有可能的是，每次选择视图之前都需要先创建视图。
有什么想法吗？例如如何缓存？我可以采取其他额外措施吗？
I have some demanding mysql queries that have to select same frequently updated datasets from 5-7 mysql tables. 'Select' operation would be a bit more than CUD.
I am thinking of creating a table or materialized view to gather all demanding columns from other tables, so as to reduce overall query times to different tables and thus increase performance.
If I create that table, I may need to do extra insert / update / delete operation each time other tables updated.
if I create materialized view, I am worrying if the performance can be greatly improved. Because data from other tables are changing very frequently. Most likely, the view may need to be created first everytime before selecting it.
Any ideas? e.g. how to cache? other extra measures I can do?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
视图只不过是查询。因此，无论您是进行查询以从视图中选择还是只执行普通 sql，性能都是相同的。
缓存是一个非常复杂且具体的问题。因此，没有万能药，为了做出决定，应该提供更多细节。
Views are nothing but query. So doesn't matter whether you make query to select from view or just execute plain sql - the performance will be the same.
Caching is very complex and specific question. So there is no panacea and to make decision more details should be provided.
在我看来，您正在按照“物化视图<的思路进行思考/a>”的概念。
Mysql 没有提供这种实现，尽管可以用一些 更多 或不太复杂（我做了类似的事情Postgresql 中的后者 - 对于报告中经常使用的复杂非参数化查询非常方便，并且可以容忍不完全最新的数据）。
It seems to me that you are thinking along the lines of the "materialized view" concept.
Mysql does not provide an implementation of this, though it can be simulated with some more or less sophistication (I do something similar to the later in Postgresql - it's handy for complex non-parametrized queries that are frequently used in reports, and for which is tolerable to have not totally up-to-date data).
如下所述，没有提高性能的灵丹妙药。而且，与上面所说的不同，索引并不是数据库性能最重要的事情，正确设置的数据库才是最重要的。随着数据库变得越来越大，数据库配置可能会主导性能。其中最重要的是拥有适当配置的磁盘子系统，因为大型数据库的性能始终受到磁盘传输数据速度的限制。
至于您的具体问题，通过查询伪造物化视图可能对您有帮助，也可能没有帮助。它可能会降低您的插入和更新性能，同时可能会提高您的选择性能。在需要时按需创建“视图”对您来说绝对没有任何作用，因为无论如何您都必须运行缓慢的查询来创建它。由于 MySQL 不直接支持物化视图，因此标准视图对您没有任何作用。
如果没有更多细节，就不可能提供更好的帮助。
As said below, there is no panacea for increasing performance. And, unlike what was said above, indexes are not the most important thing for DB performance - having a correctly set up database is. As the database gets larger DB configuration can come to dominate performance. Most important in this is having an appropriately configured disk subsystem since large databases are always limited in their performance by how fast data can be transferred to/from disk.
As for your specific questions, faking a materialized view via queries may or may not help you. It will likely slow down your insert and update performance while possibly increasing your select performance. Creating the "view" on demand when needed will do absolutely nothing for you as you're having to run your slow query to create it anyways. Since MySQL doesn't directly support materializing views a standard view will do nothing for you.
Without more details better help is impossible to give.