派生表和视图的性能
从MySQL 4.1开始,它已经支持派生表、联机视图或者基本的FROM从句的子查询。
这些特性之间彼此相关,但是它们之间的性能比较如何呢?
MySQL 5.0 中的派生表似乎和视图实现的方式不同,尽管我从合并的代码基数来看觉得在查询优化上应该是一样的。
派生表仍然以临时表的方式显式地处理,而且还是没有索引的临时表(因此最好不要像在例子中那样连接2个派生表)
需要考虑的另一方面是,派生表需要被显式处理,尽管只是执行 EXPLAIN 语句。因此如果在 FROM 字句中的 SELELCT 操作上犯了错误,例如忘记了写上连接的条件,那么 EXPLAIN 可能会一直在运行。
视图则不同,它无需被显式处理,只是把查询简单地重写了一下。只有在无法合并查询或者试图创建者请求时才需要被显式处理。
这意味着它们在性能上的差别如下:
在基本的表上执行有索引的查询,这非常快
-
- mysql>SELECT*FROMtestWHEREi=5;
-
+---+----------------------------------+
- | i | j<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>|</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
-
+---+----------------------------------+
- |5| 0c88dedb358cd96c9069b73a<wbr>57682a45 |</wbr>
-
+---+----------------------------------+
- 1rowINSET(0.03sec)
在派生表上做同样的查询,则如老牛拉破车
- <wbr></wbr>
-
mysql>SELECT*FROM(SELECT*FROMtest)tWHEREi=5;
- +---+----------------------------------+
-
| i | j<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>|</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
- +---+----------------------------------+
-
|5| 0c88dedb358cd96c9069b73a<wbr>57682a45 |</wbr>
- +---+----------------------------------+
-
1rowINSET(1min40.86sec)
在视图上查询,又快起来了
-
mysql>CREATEVIEWvASSELECT*FROMtest;
- Query OK,0rows affected(0.08sec)
-
<wbr></wbr>
- mysql>SELECT*FROMv<wbr><span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>WHERE</strong></span>i=<span style="word-wrap:normal; word-break:normal; color:rgb(128,0,0)">5</span>;</wbr>
-
+---+----------------------------------+
- | i | j<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>|</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
-
+---+----------------------------------+
- |5| 0c88dedb358cd96c9069b73a<wbr>57682a45 |</wbr>
-
+---+----------------------------------+
- 1rowINSET(0.10sec)
下面的2条EXPLAIN结果也许会让你很惊讶
-
<wbr></wbr>
- mysql>EXPLAINSELECT*FROMv<wbr><span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>WHERE</strong></span>i=<span style="word-wrap:normal; word-break:normal; color:rgb(128,0,0)">5</span>;</wbr>
-
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- | id | select_type |TABLE| type<wbr>| possible_keys |<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>KEY</strong></span><wbr><wbr><wbr>| key_len | ref<wbr><wbr>| rows | Extra |</wbr></wbr></wbr></wbr></wbr></wbr>
-
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- |<wbr><span style="word-wrap:normal; word-break:normal; color:rgb(128,0,0)">1</span>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>PRIMARY</strong></span><wbr><wbr><wbr>| test<wbr>| const |<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>PRIMARY</strong></span><wbr><wbr><wbr><wbr>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>PRIMARY</strong></span>|<span style="word-wrap:normal; word-break:normal; color:rgb(128,0,0)">4</span><wbr><wbr><wbr><wbr>| const |<wbr><wbr><span style="word-wrap:normal; word-break:normal; color:rgb(128,0,0)">1</span>|<wbr><wbr><wbr><wbr>|</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
-
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
- 1rowINSET(0.02sec)
-
<wbr></wbr>
- mysql>EXPLAINSELECT*FROM(SELECT*FROMtest)tWHEREi=5;
-
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
- | id | select_type |TABLE<wbr><wbr><wbr>| type | possible_keys |<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>KEY</strong></span><wbr>| key_len | ref<wbr>| rows<wbr><wbr>| Extra<wbr><wbr><wbr><wbr>|</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
-
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
- |<wbr><span style="word-wrap:normal; word-break:normal; color:rgb(128,0,0)">1</span>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>PRIMARY</strong></span><wbr><wbr><wbr>| <derived2> |<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>ALL</strong></span><wbr>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>NULL</strong></span><wbr><wbr><wbr><wbr><wbr>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>NULL</strong></span>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>NULL</strong></span><wbr><wbr>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>NULL</strong></span>|<span style="word-wrap:normal; word-break:normal; color:rgb(128,0,0)">1638400</span>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>USING</strong></span><span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>WHERE</strong></span>|</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
-
|<wbr><span style="word-wrap:normal; word-break:normal; color:rgb(128,0,0)">2</span>| DERIVED<wbr><wbr><wbr>| test<wbr><wbr><wbr><wbr>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>ALL</strong></span><wbr>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>NULL</strong></span><wbr><wbr><wbr><wbr><wbr>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>NULL</strong></span>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>NULL</strong></span><wbr><wbr>|<span style="word-wrap:normal; word-break:normal; color:rgb(153,51,51)"><strong>NULL</strong></span>|<span style="word-wrap:normal; word-break:normal; color:rgb(128,0,0)">1638400</span>|<wbr><wbr><wbr><wbr><wbr><wbr><wbr>|</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
- +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
-
2rowsINSET(54.90sec)
避免使用派生表-- 如果可能,最好采用其他方式来编写查询语句,大部分情况都比派生表来的快。很多情况下,甚至连独立的临时表都来的快,因为可以适当增加索引。
<wbr></wbr>
可以考虑使用临时试图来取代派生表如果确实需要在 FROM 子句中使用到子查询,可以考虑在查询时创建试图,当查询完之后删除试图。
<wbr></wbr>
不适合多表视图,多表时用派生表取代视图
explain<wbr>select sum(pdm.qty) pre_total,pd.pre_doc_id from prepare_doc pd<br> left join pre_doc_item pdm on pd.pre_doc_id=pdm.pre_doc_id group by pd.pre_doc_id</wbr>