MySQL 视图和索引使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7922675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Views and index use
提问by Xkeeper
I was thinking of starting to utilize views to reduce the complexity of code and queries in our project -- some of them have a few joins, and from what I understand, MySQL views would allow us to reference that data a little easier in multiple places.
我正在考虑开始利用视图来降低我们项目中代码和查询的复杂性——其中一些有一些连接，据我所知，MySQL 视图允许我们在多个地方更容易地引用这些数据.
There's a lot of stuff being thrown around, where "MySQL doesn't use indexes for views", "You can't have an indexed view", "Only if you use MERGE"... There is no clear-cut answer.
有很多东西被抛出，其中“MySQL 不为视图使用索引”、“你不能有索引视图”、“只有你使用 MERGE”......没有明确的答案。
So, to cut to the chase: Do MySQL views use indexes on the tables they are built from?Is it a bad idea to use views at all because performance will be abysmal, or will it use the indexes on the underlying tables when doing its joins? If I sort a view by a column that is indexed in the table, does it still sort as fast as it normally would?
那么，切入正题：MySQL 视图是否在构建它们的表上使用索引？完全使用视图是一个坏主意，因为性能会很糟糕，还是在进行连接时会使用基础表上的索引？如果我按表中索引的列对视图进行排序，它的排序速度是否仍像通常一样快？
Doing my research seems to indicate that views don't use indexes, but if that was the case nobody would ever use them; obviously people do, so...?
做我的研究似乎表明视图不使用索引，但如果是这样的话，没有人会使用它们；显然人们这样做，所以......？
Sorry if this seems kind of absurd.
对不起，如果这看起来有点荒谬。
采纳答案by Mark Byers
If you query a view, MySQL willconsider using indexes on the underlying tables.
如果你查询一个视图，MySQL会考虑在底层表上使用索引。
However it is notpossible to add a new index to a calculated column in the view. I think this is what people meant by MySQL not having indexed views, as opposed to (for example) SQL Server's indexed views.
然而，它是不是可以将新的索引添加到视图中的计算列。我认为这就是人们所说的 MySQL 没有索引视图，而不是（例如）SQL Server 的索引视图。
回答by Icarus
Do MySQL views use indexes on the tables they are built from?
MySQL 视图是否在构建它们的表上使用索引？
Yes.
是的。
What people probably refer to when they say MySQL doesn't use indexes for viewsis of something called materialized viewsor indexed viewson which the actual view is physically stored on the file system as regular table would be. Indexes can be created for these views on some DBMS such as Oracle or SQL Server. Essentially, indexed viewsbecome a copy of the original tables that compose it and is kept in sync automatically, sort of speak.
人们在谈论时可能指的MySQL doesn't use indexes for views是某种称为materialized views或indexed views实际视图物理存储在文件系统上的东西，就像常规表一样。可以在某些 DBMS（例如 Oracle 或 SQL Server）上为这些视图创建索引。本质上，indexed views成为组成它的原始表格的副本，可以kept in sync automatically说是。
Read this article regarding Indexed Viewson SQL Server, for example.
例如，请阅读有关SQL Server 上的索引视图的这篇文章。
