是否可以在 MySQL 中拥有索引视图? [英] Is it possible to have an indexed view in MySQL?

问题描述

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

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.

推荐答案

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

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.

这篇关于是否可以在 MySQL 中拥有索引视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！