mysql派生表视图_mysql派生表和视图的性能_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 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 可能会一直在运行。

Views on other hand do not have to be materialized and normally executed by rewriting the query. It only will be materialized if query merge is impossible or if requested by view creator.

视图则不同，它无需被显式处理，只是把查询简单地重写了一下。只有在无法合并查询或者试图创建者请求时才需要被显式处理。

What does it mean in terms of performance:

这意味着它们在性能上的差别如下：

PLAIN TEXT

SQL:

Query ON base TABLE executes USING INDEX AND it IS very fast

在基本的表上执行有索引的查询，这非常快

mysql> SELECT * FROM test WHERE i=5;

+---+----------------------------------+

| i | j |

+---+----------------------------------+

| 5 | 0c88dedb358cd96c9069b73a57682a45 |

+---+----------------------------------+

1 row IN SET (0.03 sec)

Same query USING derived TABLE crawls:

本文原创发布php中文网，转载请注明出处，感谢您的尊重！