使用MySQL VIEW时的性能注意事项 [英] Performance considerations when using MySQL VIEWs

问题描述

我正在考虑使用MySQL视图从数据库中提取数据时提供抽象.在寻找相关材料时，我遇到了本文，其结尾为:

I was considering using MySQL views to provide an abstraction when pulling data from the DB. As I was looking for material on this, I came across this article, which ends with:

MySQL任重而道远 适当优化了VIEW的查询.

MySQL has long way to go getting queries with VIEWs properly optimized.

文章来自2007年.这是否仍然适用?例如:MySQL解决了这些问题吗?

The article is from 2007. Is this still applicable? Eg: Has MySQL solved these issues?

推荐答案

MySQL视图在功能上可以正常运行，但是在大多数情况下它们的性能很差.

MySQL views work fine functionally, but they perform badly in the majority of cases.

这是因为即使引入一个非常简单的视图也会导致优化器使用更差的查询计划.这使得在一般情况下使用视图不可行.

This is because introducing even quite a simple view tends to cause a much worse query plan to be used by the optimiser. This makes using views impractical in the general case.

服务器通常会将整个视图具体化为临时表，这对于提高性能没有帮助(除非它恰好只有很少的行).

Often the server will materialise the entire view as a temporary table, which is not helpful for good performance (unless it happens to have only a very small number of rows in it).

因此，如果您认为没有观点的解释计划还可以，那么有了观点，它可能会变得很糟糕.据我所知，最新的MySQL开发版本仍未解决该问题.

So if you thought the explain plan was ok without a view, with a view it can turn terrible. This is still unresolved in the latest dev version of MySQL as far as I know.

您可以发表意见，但不要期望表现不错(即可以接受).

You can have views, but don't expect decent (i.e. acceptable) performance.

这篇关于使用MySQL VIEW时的性能注意事项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！