有可能有一个MySQL索引视图?Is it possible to have an indexed view in MySQL?

- 此内容更新于:2014-12-30
主题:

原文:

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.

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.

Pacerier的回复:看到stackoverflow.com/q/7922675/632951

(原文:See stackoverflow.com/q/7922675/632951)

解决方案:
原文:

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.

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

Thomas Owens的回复:谢谢。我做了一些更多的搜索物化视图,它看起来像你是正确的。

(原文:Thanks. I did some more searching on materialized views, and it looks like you are correct.)

解决方案:
原文:

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.

Thomas Owens的回复:这# 39;实际上我# 39;米现在工作。我想。我# 39;米会有一个表的数据,我需要这# 39;年代定期更新索引的查询,所以我只需要查询去表一次取消建立索引(长时间单位)用新数据更新索引表。

(原文:That's actually what I'm working on now. I think. I'm going to have a table that has the data that I need that's updated on a regular basis that is indexed for queries, so I only have to query the unindexed table once every [long unit of time] to update the indexed table with new data.)

解决方案:
原文:

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.

解决方案:
Flexviews支持物化视图在MySQL中通过跟踪更改底层表和更新表函数作为物化视图。这种方法意味着SQL支持的观点是有点限制(如改变日志程序必须找出哪些表应该跟踪的变化),但据我所知这是最接近你可以在MySQL物化视图。
原文:

Flexviews supports 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.