快捷搜索:  as  test  1111  test aNd 8=8  test++aNd+8=8  as++aNd+8=8  as aNd 8=8

ag8亚洲国际游戏:mysql派生表和视图的性能(1)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 compaag8亚洲国际游戏re 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 optimizatiag8亚洲国际游戏on.

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 eveag8亚洲国际游戏n 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> SELag8亚洲国际游戏ECT * 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 VIEag8亚洲国际游戏W 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结果大概会让你很惊疑

免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。

您可能还会对下面的文章感兴趣: