mysql派生表和视图的性能

核心提示： 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 i

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:

在派生表上做同样的查询，则如老牛拉破车

mysql> SELECT * FROM (SELECT * FROM test) t WHERE i=5;
+---+----------------------------------+
| i | j |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET (1 min 40.86 sec)
Query USING VIEW IS fast again:

在试图上查询，又快起来了

mysql> CREATE VIEW v AS SELECT * FROM test;
Query OK, 0 rows affected (0.08 sec)
mysql> SELECT * FROM v WHERE i=5;
+---+----------------------------------+
| i | j |
+---+----------------------------------+
| 5 | 0c88dedb358cd96c9069b73a57682a45 |
+---+----------------------------------+
1 row IN SET (0.10 sec)
Here are couple of explains IF you are curios

下面的2条EXPLAIN结果也许会让你很惊讶