在 MySQL 中是否可以有索引视图？

Is it possible to have an indexed view in MySQL?

提问by Thomas Owens

I found a posting on the MySQL forums from 2005, but nothing more recent than that. Based on that, it's not possible. But a lot can change in 3-4 years.

我在 MySQL 论坛上发现了 2005 年的帖子，但没有比这更近的了。基于此，这是不可能的。但是很多事情会在 3-4 年内发生变化。

What I'm looking for is a way to have an index over a view but have the table that is viewed remain unindexed. Indexing hurts the writing process and this table is written to quite frequently (to the point where indexing slows everything to a crawl). However, this lack of an index makes my queries painfully slow.

我正在寻找的是一种在视图上建立索引但使查看的表保持未索引状态的方法。索引会损害写入过程，并且该表的写入非常频繁（以至于索引使一切变得缓慢）。然而，缺乏索引使我的查询速度非常缓慢。

采纳答案by KernelM

I don't think MySQL supports materialized views which is what you would need, but it wouldn't help you in this situation anyway. Whether the index is on the view or on the underlying table, it would need to be written and updated at some point during an update of the underlying table, so it would still cause the write speed issues.

我认为 MySQL 不支持您需要的物化视图，但无论如何它在这种情况下对您没有帮助。无论索引是在视图上还是在底层表上，在底层表更新过程中的某个时刻都需要写入和更新索引，因此仍然会导致写入速度问题。

Your best bet would probably be to create summary tables that get updated periodically.

您最好的选择可能是创建定期更新的汇总表。

回答by Noah Goodrich

Have you considered abstracting your transaction processing data from your analytical processing data so that they can both be specialized to meet their unique requirements?

您是否考虑过从您的分析处理数据中抽象出您的事务处理数据，以便它们都可以专门用于满足其独特的要求？

The basic idea being that you have one version of the data that is regularly modified, this would be the transaction processing side and requires heavy normalization and light indexes so that write operations are fast. A second version of the data is structured for analytical processing and tends to be less normalized and more heavily indexed for fast reporting operations.

基本思想是您有一个定期修改的数据版本，这将是事务处理端，需要大量规范化和少量索引，以便写入操作快速。数据的第二个版本是为分析处理而结构化的，并且趋向于标准化程度较低且索引较多，以实现快速报告操作。

Data structured around analytical processing is generally built around the cube methodology of data warehousing, being composed of fact tables that represent the sides of the cube and dimension tables that represent the edges of the cube.

围绕分析处理构建的数据通常围绕数据仓库的多维数据集方法构建，由表示多维数据集侧面的事实表和表示多维数据集边缘的维度表组成。

回答by mateusz.kijowski

Flexviewssupports materialized views in MySQL by tracking changes to underlying tables and updating the table which functions as a materialized view. This approach means that SQL supported by the view is a bit restricted (as the change logging routines have to figure out which tables it should track for changes), but as far as I know this is the closest you can get to materialized views in MySQL.

Flexviews通过跟踪底层表的更改并更新用作物化视图的表来支持 MySQL 中的物化视图。这种方法意味着视图支持的 SQL 受到了一些限制（因为更改日志记录例程必须弄清楚它应该跟踪哪些表以进行更改），但据我所知，这是最接近 MySQL 中物化视图的方法.

回答by dkretz

Do you only want one indexed view? It's unlikely that writing to a table with only one index would be that disruptive. Is there no primary key?

你只想要一个索引视图吗？写入只有一个索引的表不太可能具有破坏性。没有主键吗？

If each record is large, you might improve performance by figuring out how to shorten it. Or shorten the length of the index you need.

如果每个记录都很大，您可以通过弄清楚如何缩短它来提高性能。或者缩短您需要的索引长度。

If this is a write-only table (i.e. you don't need to do updates), it can be deadly in MySQL to start archiving it, or otherwise deleting records (and index keys), requiring the index to start filling (reusing) slots from deleted keys, rather than just appending new index values. Counterintuitive, but you're better off with a larger table in this case.

如果这是一个只写表（即您不需要进行更新），则在 MySQL 中开始归档它可能是致命的，或者以其他方式删除记录（和索引键），需要索引开始填充（重用）来自已删除键的插槽，而不仅仅是附加新的索引值。违反直觉，但在这种情况下，最好使用更大的桌子。