EXPLAIN sql优化步骤（3）DERIVED

EXPLAIN sql优化方法（3）DERIVED
派生表和视图的性能

从MySQL 4.1开始，它已经支持派生表、联机视图或者基本的FROM从句的子查询。

这些特性之间彼此相关，但是它们之间的性能比较如何呢？

MySQL 5.0 中的派生表似乎和视图实现的方式不同，尽管我从合并的代码基数来看觉得在查询优化上应该是一样的。

派生表仍然以临时表的方式显式地处理，而且还是没有索引的临时表（因此最好不要像在例子中那样连接2个派生表）

需要考虑的另一方面是，派生表需要被显式处理，尽管只是执行 EXPLAIN 语句。因此如果在 FROM 字句中的 SELELCT 操作上犯了错误，例如忘记了写上连接的条件，那么 EXPLAIN 可能会一直在运行。

视图则不同，它无需被显式处理，只是把查询简单地重写了一下。只有在无法合并查询或者试图创建者请求时才需要被显式处理。

这意味着它们在性能上的差别如下：

在基本的表上执行有索引的查询，这非常快

1. 
   
2. mysql> SELECT * FROM test WHERE i=5;
3. +---+----------------------------------+
4. | i | j? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
5. +---+----------------------------------+
6. | 5 | 0c88dedb358cd96c9069b73a57682a45 |
7. +---+----------------------------------+
8. 1 row IN SET (0.03 sec)

在派生表上做同样的查询，则如老牛拉破车

1. ?
2. mysql> SELECT * FROM (SELECT * FROM test) t WHERE i=5;
3. +---+----------------------------------+
4. | i | j? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
5. +---+----------------------------------+
6. | 5 | 0c88dedb358cd96c9069b73a57682a45 |
7. +---+----------------------------------+
8. 1 row IN SET (1 min 40.86 sec)

在视图上查询，又快起来了

1. mysql> CREATE VIEW v AS SELECT * FROM test;
2. Query OK, 0 rows affected (0.08 sec)
3. ?
4. mysql> SELECT * FROM v? WHERE i=5;
5. +---+----------------------------------+
6. | i | j? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
7. +---+----------------------------------+
8. | 5 | 0c88dedb358cd96c9069b73a57682a45 |
9. +---+----------------------------------+
10. 1 row IN SET (0.10 sec)

下面的2条EXPLAIN结果也许会让你很惊讶

1. ?
2. mysql> EXPLAIN SELECT * FROM v? WHERE i=5;
3. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
4. | id | select_type | TABLE | type? | possible_keys | KEY? ? ?| key_len | ref? ?| rows | Extra |
5. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
6. |? 1 | PRIMARY? ? ?| test? | const | PRIMARY? ? ? ?| PRIMARY | 4? ? ? ?| const |? ? 1 |? ? ? ?|
7. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
8. 1 row IN SET (0.02 sec)
9. ?
10. mysql> EXPLAIN SELECT * FROM (SELECT * FROM test) t WHERE i=5;
11. +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
12. | id | select_type | TABLE? ? ? | type | possible_keys | KEY? | key_len | ref? | rows? ? | Extra? ? ? ?|
13. +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
14. |? 1 | PRIMARY? ? ?| <derived2> | ALL? | NULL? ? ? ? ? | NULL | NULL? ? | NULL | 1638400 | USING WHERE |
15. |? 2 | DERIVED? ? ?| test? ? ? ?| ALL? | NULL? ? ? ? ? | NULL | NULL? ? | NULL | 1638400 |? ? ? ? ? ? ?|
16. +----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
17. 2 rows IN SET (54.90 sec)

避免使用派生表 -- 如果可能，最好采用其他方式来编写查询语句，大部分情况都比派生表来的快。很多情况下，甚至连独立的临时表都来的快，因为可以适当增加索引。

?

可以考虑使用临时试图来取代派生表 如果确实需要在 FROM 子句中使用到子查询，可以考虑在查询时创建试图，当查询完之后删除试图。

?

不适合多表视图，多表时用派生表取代视图

explain? select sum(pdm.qty) pre_total,pd.pre_doc_id from prepare_doc pd
left join pre_doc_item pdm on pd.pre_doc_id=pdm.pre_doc_id group by pd.pre_doc_id

?
?

?