MySQL中的derived table

MySQL 技術 楊建榮的學習筆記 2017-05-02

初始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來改進,另外一種則是儘可能避免使用。

MySQL中的derived table

相關推薦

推薦中...