mysql派生表和视图的性能

核心提示：Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause.In MySQL 5.0 support for views

Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause.

In MySQL 5.0 support for views was added.

从MySQL 4.1开始，它已经支持派生表、联机视图或者基本的FROM从句的子查询。

These features are quite related to each other but how do they compare in terms of performance ?

这些特性之间彼此相关，但是它们之间的性能比较如何呢？

Derived Tables in MySQL 5.0 seems to have different implementation from views, even though I would expect code base to be merged as it is quite the same task in terms of query optimization.

MySQL 5.0 中的派生表似乎和视图实现的方式不同，尽管我从合并的代码基数来看觉得在查询优化上应该是一样的。

Derived Tables are still handled by materializing them in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example).

派生表仍然以临时表的方式显式地处理，而且还是没有索引的临时表（因此最好不要像在例子中那样连接2个派生表）。

One more thing to watch for is the fact derived table is going to be materialized even to execute EXPLAIN statement. So if you have done mistake in select in from clause, ie forgotten join condition you might have EXPLAIN running forever.

需要考虑的另一方面是，派生表需要被显式处理，尽管只是执行 EXPLAIN 语句。因此如果在 FROM 字句中的 SELELCT 操作上犯了错误，例如忘记了写上连接的条件，那么 EXPLAIN 可能会一直在运行。