mysql派生表和视图的性能

核心提示： mysql> EXPLAIN SELECT * FROM v WHERE i=5;++-+---+---+---+-+-+---+--+---+| id | select_type | TABLE | type | possible_keys | KEY | key_len | re

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能在下一个版本中解决它 -- 本文的例子中的查询表现的如此不同其实是不合逻辑的只是大致的估算。