派生表和视图的性能

Submitted by yejr on 周四, 2006/08/31 - 22:48

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:

  1. Query ON base TABLE executes USING INDEX AND it IS very fast
  2. 在基本的表上执行有索引的查询，这非常快
  4. mysql> SELECT * FROM test WHERE i=5;
  5. +---+----------------------------------+
  6. | i | j                                |
  7. +---+----------------------------------+
  8. | 5 | 0c88dedb358cd96c9069b73a57682a45 |
  9. +---+----------------------------------+
  10. 1 row IN SET (0.03 sec)
  11.  
  12.  
  13. Same query USING derived TABLE crawls:
  14. 在派生表上做同样的查询，则如老牛拉破车
  15.  
  16. mysql> SELECT * FROM (SELECT * FROM test) t WHERE i=5;
  17. +---+----------------------------------+
  18. | i | j                                |
  19. +---+----------------------------------+
  20. | 5 | 0c88dedb358cd96c9069b73a57682a45 |
  21. +---+----------------------------------+
  22. 1 row IN SET (1 min 40.86 sec)
  23.  
  24. Query USING VIEW IS fast again:
  25. 在试图上查询，又快起来了
  26.  
  27. mysql> CREATE VIEW v AS SELECT * FROM test;
  28. Query OK, 0 rows affected (0.08 sec)
  29.  
  30. mysql> SELECT * FROM v  WHERE i=5;
  31. +---+----------------------------------+
  32. | i | j                                |
  33. +---+----------------------------------+
  34. | 5 | 0c88dedb358cd96c9069b73a57682a45 |
  35. +---+----------------------------------+
  36. 1 row IN SET (0.10 sec)
  37.  
  38. Here are couple of explains IF you are curios
  39. 下面的2条EXPLAIN结果也许会让你很惊讶
  40.  
  41. mysql> EXPLAIN SELECT * FROM v  WHERE i=5;
  42. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  43. | id | select_type | TABLE | type  | possible_keys | KEY     | key_len | ref   | rows | Extra |
  44. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  45. |  1 | PRIMARY     | test  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
  46. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  47. 1 row IN SET (0.02 sec)
  48.  
  49. mysql> EXPLAIN SELECT * FROM (SELECT * FROM test) t WHERE i=5;
  50. +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
  51. | id | select_type | TABLE      | type | possible_keys | KEY  | key_len | ref  | rows    | Extra       |
  52. +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
  53. |  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1638400 | USING WHERE |
  54. |  2 | DERIVED     | test       | ALL  | NULL          | NULL | NULL    | NULL | 1638400 |             |
  55. +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
  56. 2 rows IN SET (54.90 sec)
  57.  
  58. Note how long it took just TO execute EXPLAIN FOR derived TABLE
  59. 请注意后面这条花了多长时间才执行完 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能在下一个版本中解决它 -- 本文的例子中的查询表现的如此不同其实是不合逻辑的只是大致的估算。


技术相关: