IT序号网

EXPLAIN sql优化方法(3)DERIVED

sanshao 2021年06月14日 数据库 305 0

派生表和视图的性能

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

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

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

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

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

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

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

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

Java代码   收藏代码
  1. mysql> SELECT * FROM test WHERE i=5 ;  
  2. +---+----------------------------------+  
  3. | i | j                                |  
  4. +---+----------------------------------+  
  5. 5 | 0c88dedb358cd96c9069b73a57682a45 |  
  6. +---+----------------------------------+  
  7. 1 row IN SET ( 0 .03 sec)  

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

Java代码   收藏代码
  1. mysql> SELECT * FROM ( SELECT * FROM test) t WHERE i=5 ;  
  2. +---+----------------------------------+  
  3. | i | j                                |  
  4. +---+----------------------------------+  
  5. 5 | 0c88dedb358cd96c9069b73a57682a45 |  
  6. +---+----------------------------------+  
  7. 1 row IN SET ( 1 min 40 .86 sec)  

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

Java代码   收藏代码
  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结果也许会让你很惊讶

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

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

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

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

Java代码   收藏代码
  1. explain  select sum(pdm.qty) pre_total,pd.pre_doc_id from prepare_doc pd  
  2. left join pre_doc_item pdm on pd.pre_doc_id=pdm.pre_doc_id group by pd.pre_doc_id  


 
 

原文地址:http://hudeyong926.iteye.com/blog/785188
评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!