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来改进,另外一种则是尽可能避免使用。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-04-27

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

循序渐进:Oracle 12c新特性Sharding技术解读

引言 数据库构架设计中主要有 Shared Everthting、Shared Nothing 和 Shared Disk: Shared Everthting...

48570
来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

17220
来自专栏后端技术探索

mysql 水平分表的几种方法

当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

1.9K20
来自专栏IT大咖说

2018年,让你的数据库变更快的十个建议

摘要 大多数网站的内容都存在数据库里,用户通过请求来访问内容。数据库非常的快,有许多技巧能让你优化数据库的速度,使你不浪费服务器的资源。在这篇文章中,我收录了十...

43470
来自专栏互联网技术栈

MySQL 锁机制——必知必会

MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。

15860
来自专栏MYSQL轻松学

MySQL 面试选择题15道(单选)

1、MySQL数据库四种特性,不包括() A.原子性 B.事务性 C.一致性 D.隔离性 2、MySQL报错error 1062 的意思是() A.连接数据库失...

77260
来自专栏chenssy

【死磕Sharding-jdbc】---结果合并总结

这句SQL会使得MySQL在无法利用索引的情况下跳过1000000条记录后,再获取10条记录,其性能可想而知。而在分库分表的情况下(假设分为2个库),为了保证数...

20330
来自专栏FreeBuf

如何对已损坏的SQLite数据库取证分析?

SQLite是当今最流行的数据库之一,许多移动应用台式计算机以及便携式笔记本上都用它来存储数据(例如桌面工具、浏览器以及社交媒体软件等),因此SQLite在电子...

46890
来自专栏恰童鞋骚年

Hadoop学习笔记—20.网站日志分析项目案例(三)统计分析

  为了能够借助Hive进行统计分析,首先我们需要将清洗后的数据存入Hive中,那么我们需要先建立一张表。这里我们选择分区表,以日期作为分区的指标,建表语句如下...

15320
来自专栏aoho求索

MySQL探秘(五):InnoDB锁的类型和状态查询

 锁是数据库系统区分于文件系统的一个关键特性。数据库使用锁来支持对共享资源进行并发访问,提供数据的完整性和一致性。此外,数据库事务的隔离性也是通过锁实现的。In...

13810

扫码关注云+社区

领取腾讯云代金券