MySQL中的derivedtable(r12筆記第47天)

初始MySQL中的derived table還是在一個(gè)偶然的問題場(chǎng)景中。

管城網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián)建站,管城網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為管城上千提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站制作要多少錢，請(qǐng)找那個(gè)售后服務(wù)好的管城做網(wǎng)站的公司定做！

下面的語(yǔ)句在執(zhí)行的時(shí)候拋出了錯(cuò)誤。

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    如果對(duì)MySQL查詢優(yōu)化器足夠了解就會(huì)明白，其實(shí)這種方式是MySQL不支持的，有沒有WA呢，還是有的，那就是通過(guò)一種特殊的子查詢來(lái)完成，也就是derived table

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

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,在官方文檔中是這么說(shuō)的。

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

首先查看兩條數(shù)據(jù)，作為我們測(cè)試的基礎(chǔ)數(shù)據(jù)，其中id是主鍵列.

> select id from t_fund_info limit 1,2;
+---------+
| id      |
+---------+
|  138031 |
| 1754906 |
+---------+如果按照id列來(lái)查詢，就會(huì)發(fā)現(xiàn)效率極高。

> select * from t_fund_info where id=138031;
。。。
1 row in set (0.01 sec)    我們?nèi)绻榭磮?zhí)行計(jì)劃，就會(huì)發(fā)現(xiàn)是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)那么我們繼續(xù)換一種思路，使用兩種不同的derived table

第一種：

> select * from (select id from t_fund_info) t where t.id=138031;
1 row in set (1.12 sec)這個(gè)時(shí)候查看執(zhí)行計(jì)劃，就會(huì)看到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     |   | 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)看起來(lái)是1秒的執(zhí)行速度，差別還不是很大，我們換第二種方式。

>  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 
這個(gè)時(shí)候就會(huì)發(fā)現(xiàn)這么一個(gè)看似簡(jiǎn)單的查詢竟然拋出了錯(cuò)誤。

查看錯(cuò)誤里的信息，是一個(gè)MYI的文件，顯然是使用了臨時(shí)表的方式，典型的一個(gè)myisam表。

為了驗(yàn)證這個(gè)過(guò)程，我盡可能完整的收集了/tmp目錄下的文件使用情況，可以看到，占用了2G多的空間，最后發(fā)現(xiàn)磁盤空間不足退出。

# 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這里有另外一個(gè)疑問，那就是這個(gè)表t_fund_info是個(gè)InnoDB表，占用空間是400M左右，但是derived table使用率竟然達(dá)到了2G以上，不知道MySQL內(nèi)部是怎么進(jìn)一步處理的。

-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明顯可以看出這種方式還是有潛在的性能問題，難道m(xù)yisam表占有的空間更大，顯然不是，我測(cè)試了同樣數(shù)據(jù)量的myisam表，空間大概是270M左右。

那這種方式還有沒有改進(jìn)的空間呢。我們?cè)囋囈晥D表達(dá)的是一個(gè)意思。

> 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) 

執(zhí)行計(jì)劃和主鍵的執(zhí)行計(jì)劃一模一樣。

所以對(duì)于derived table的改進(jìn)方式，一種是通過(guò)view來(lái)改進(jìn)，另外一種則是盡可能避免使用。

網(wǎng)站名稱：MySQL中的derivedtable(r12筆記第47天)
分享網(wǎng)址：http://m.jiaotiyi.com/article/igppcj.html