EXPLAIN sql優化方法3 DERIVED 派生表

NO IMAGE

派生表和檢視的效能

從MySQL 4.1開始,它已經支援派生表、聯機檢視或者基本的FROM從句的子查詢。

這些特性之間彼此相關,但是它們之間的效能比較如何呢?

MySQL 5.0 中的派生表似乎和檢視實現的方式不同,儘管我從合併的程式碼基數來看覺得在查詢優化上應該是一樣的。

派生表仍然以臨時表的方式顯式地處理,而且還是沒有索引的臨時表(因此最好不要像在例子中那樣連線2個派生表)

需要考慮的另一方面是,派生表需要被顯式處理,儘管只是執行 EXPLAIN 語句。因此如果在 FROM 字句中的 SELELCT 操作上犯了錯誤,例如忘記了寫上連線的條件,那麼 EXPLAIN 可能會一直在執行。

檢視則不同,它無需被顯式處理,只是把查詢簡單地重寫了一下。只有在無法合併查詢或者試圖建立者請求時才需要被顯式處理。

這意味著它們在效能上的差別如下:

在基本的 上執行有索引 的查詢,這非常

  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)

派生表 上做同樣 的查詢,則如老牛拉破車

  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