MySQL中的derived table(r12筆記第47天)

初始MySQL中的derived table還是在一個偶然的問題場景中。

下面的語句在執行的時候丟擲了錯誤。

UPDATE payment_data rr
   SET rr.penalty_date = '2017-4-12'
 where rr.id =
       (SELECT min(r.id)
          FROM payment_data r
         where data_no =
               (SELECT data_no
                  FROM user_debt
                 WHERE out_trade_no = 'bestpay_order_no1491812746329'));

ERROR 1093 (HY000): You can't specify target table 'rr' for update in FROM clause    如果對MySQL查詢最佳化器足夠了解就會明白，其實這種方式是MySQL不支援的，有沒有WA呢，還是有的，那就是透過一種特殊的子查詢來完成，也就是derived table

所以上面的語句使用如下的方式就可以破解。

UPDATE payment_data rr
   SET rr.penalty_date = '2017-4-12'
 where rr.id =
       (SELECT min(t.id)
          FROM (select id,data_no from payment_data r) t
         where t.data_no =
               (SELECT data_no
                  FROM user_debt
                 WHERE out_trade_no = 'bestpay_order_no1491812746329'));
我們回到剛剛提到的Derived table,在官方文件中是這麼說的。

Derived tables is the internal name for subqueries in the      FROM clause.為了充分說明derived table,我還是舉例倒黴的t_fund_info這個表。

首先檢視兩條資料，作為我們測試的基礎資料，其中id是主鍵列.

> select id from t_fund_info limit 1,2;
+---------+
| id      |
+---------+
|  138031 |
| 1754906 |
+---------+如果按照id列來查詢，就會發現效率極高。

> select * from t_fund_info where id=138031;
。。。
1 row in set (0.01 sec)    我們如果檢視執行計劃，就會發現是primary key的掃描方式。

> explain select * from t_fund_info where id=138031;
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t_fund_info | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+----+-------------+-------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)那麼我們繼續換一種思路，使用兩種不同的derived table

第一種：

> select * from (select id from t_fund_info) t where t.id=138031;
1 row in set (1.12 sec)這個時候檢視執行計劃，就會看到derived table的字樣。

> explain select * from (select id from t_fund_info) t where t.id=138031;
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | PRIMARY     | <derived2>  | ALL   | NULL          | NULL    | NULL    | NULL | 1998067 | Using where |
|  2 | DERIVED     | t_fund_info | index | NULL          | account | 182     | NULL | 2127101 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+---------+-------------+
2 rows in set (0.90 sec)看起來是1秒的執行速度，差別還不是很大，我們換第二種方式。

>  select * from (select * from t_fund_info) t where t.id=138031;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_3e34_0.MYI'; try to repair it 
這個時候就會發現這麼一個看似簡單的查詢竟然丟擲了錯誤。

檢視錯誤裡的資訊，是一個MYI的檔案，顯然是使用了臨時表的方式，典型的一個myisam表。

為了驗證這個過程，我儘可能完整的收集了/tmp目錄下的檔案使用情況，可以看到，佔用了2G多的空間，最後發現磁碟空間不足退出。

# df -h|grep \/tmp
/dev/shm              6.0G  4.1G  1.6G  73% /tmp
/dev/shm              6.0G  4.5G  1.2G  79% /tmp
/dev/shm              6.0G  4.8G  903M  85% /tmp
/dev/shm              6.0G  4.9G  739M  88% /tmp
/dev/shm              6.0G  5.0G  625M  90% /tmp
/dev/shm              6.0G  5.2G  498M  92% /tmp
/dev/shm              6.0G  5.3G  386M  94% /tmp
/dev/shm              6.0G  5.4G  250M  96% /tmp
/dev/shm              6.0G  5.5G  110M  99% /tmp
/dev/shm              6.0G  5.7G  4.0K 100% /tmp
/dev/shm              6.0G  3.7G  2.0G  66% /tmp
/dev/shm              6.0G  3.7G  2.0G  66% /tmp這裡有另外一個疑問，那就是這個表t_fund_info是個InnoDB表，佔用空間是400M左右，但是derived table使用率竟然達到了2G以上，不知道MySQL內部是怎麼進一步處理的。

-rw-rw---- 1 mysql mysql       9545 Oct 20  2016 t_fund_info.frm
-rw-rw---- 1 mysql mysql  482344960 Oct 20  2016 t_fund_info.ibd明顯可以看出這種方式還是有潛在的效能問題，難道myisam表佔有的空間更大，顯然不是，我測試了同樣資料量的myisam表，空間大概是270M左右。

那這種方式還有沒有改進的空間呢。我們試試檢視表達的是一個意思。

> create view test_view as select * from t_fund_info;
Query OK, 0 rows affected (0.00 sec)
> select *from test_view where id=138031;
。。。
1 row in set (0.01 sec) 

執行計劃和主鍵的執行計劃一模一樣。

所以對於derived table的改進方式，一種是透過view來改進，另外一種則是儘可能避免使用。