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.
我从2005年开始在MySQL论坛上发帖，但没有比这更新的了。基于此，这是不可能的。但很多都可以在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.
我正在寻找的是一种方法，在视图上有一个索引，但是查看的表仍然没有索引。索引会损害写入过程，并且会经常写入此表（直到索引将所有内容减慢到爬行的程度）。但是，缺少索引会使我的查询变得非常缓慢。
4 个解决方案
#1
28
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.
您最好的选择可能是创建定期更新的汇总表。
#2
7
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.
围绕分析处理构建的数据通常围绕数据仓库的立方体方法构建，由表示立方体边的事实表和表示立方体边的维表组成。
#3
2
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.
Flexviews通过跟踪对基础表的更改并更新用作实例化视图的表来支持MySQL中的物化视图。这种方法意味着视图支持的SQL有点受限制（因为更改日志记录例程必须确定它应该跟踪哪些表进行更改），但据我所知，这是最接近MySQL的物化视图。
#4
0
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中开始存档它，或者以其他方式删除记录（和索引键），要求索引开始填充（重用）可能是致命的来自已删除密钥的插槽，而不是仅添加新索引值。违反直觉，但在这种情况下，你最好使用更大的桌子。
#1
28
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.
您最好的选择可能是创建定期更新的汇总表。
#2
7
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.
围绕分析处理构建的数据通常围绕数据仓库的立方体方法构建，由表示立方体边的事实表和表示立方体边的维表组成。
#3
2
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.
Flexviews通过跟踪对基础表的更改并更新用作实例化视图的表来支持MySQL中的物化视图。这种方法意味着视图支持的SQL有点受限制（因为更改日志记录例程必须确定它应该跟踪哪些表进行更改），但据我所知，这是最接近MySQL的物化视图。
#4
0
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中开始存档它，或者以其他方式删除记录（和索引键），要求索引开始填充（重用）可能是致命的来自已删除密钥的插槽，而不是仅添加新索引值。违反直觉，但在这种情况下，你最好使用更大的桌子。