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:
在派生表上做同樣的查詢，則如老牛拉破車
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結果也許會讓你很驚訝
MySQL> EXPLAIN SELECT * FROM v WHERE i=5;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | test | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row IN SET (0.02 sec)
MySQL> EXPLAIN SELECT * FROM (SELECT * FROM test) t WHERE i=5;
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1638400 | USING WHERE |
| 2 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1638400 | |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
2 rows IN SET (54.90 sec)
Note how long it took just TO execute EXPLAIN FOR derived TABLE
請注意後面這條花了多長時間才執行完 EXPLAIN。
So what does it mean in practice:
實際上這意味著：
Avoid derived tables - If there is other way to write the query it will be faster in most cases. In many cases even separate temporary table will be faster as you can add proper indexes to the table in this case.
避免使用派生表 -- 如果可能，最好采用其他方式來編寫查詢語句，大部分情況都比派生表來的快。很多情況下，甚至連獨立的臨時表都來的快，因為可以適當增加索引。
Consider using temporary views instead of derived tables If you really need to use subselect in from clause consider creating vIEw using it in the query and dropping it after query was executed.
可以考慮使用臨時試圖來取代派生表 如果確實需要在 FROM 子句中使用到子查詢，可以考慮在查詢時創建試圖，當查詢完之後刪除試圖。
In any case it is pretty annoying gotcha which I hope MySQL will fix in next MySQL versions - the fact querIEs in this example behave differently is illogical and counter intuitive.
無論如何，這都是相當煩人的，因此希望MySQL能在下一個版本中解決它 -- 本文的例子中的查詢表現的如此不同其實是不合邏輯的只是大致的估算。
