MySQL中的derived table(r12笔记第47天)
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来改进，另外一种则是尽可能避免使用。