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

对不起，如果这看起来有点荒谬.

Sorry if this seems kind of absurd.

推荐答案

如果你查询一个视图，MySQL 将考虑在底层表上使用索引.

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

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

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屋！