MySQL视图和索引使用 [英] MySQL Views and index use

问题描述

我当时正在考虑开始使用视图来减少项目中代码和查询的复杂性-其中一些具有一些联接，并且据我了解，MySQL视图将使我们可以更轻松地引用该数据在多个地方.

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不使用索引的视图"，您不能拥有索引的视图"，仅当您使用MERGE" ...减少答案.

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视图是否在其构建的表上使用索引 ??使用视图完全不是一个好主意，因为性能会很糟糕，还是在进行联接时使用基础表上的索引?如果我按在表中建立索引的列对视图进行排序，它的排序速度是否仍会像往常一样快?

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?

进行研究似乎表明视图不使用索引，但是如果是这样的话，没人会使用索引.显然人们愿意，所以...?

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...?

抱歉，这似乎很荒谬.

推荐答案

如果查询视图，MySQL 将考虑在基础表上使用索引.

If you query a view, MySQL will consider using indexes on the underlying tables.

但是不可能将新索引添加到视图中的计算列中.我认为这就是MySQL没有索引视图的人的意思，而不是(例如)

However it is not possible 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视图和索引使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！