mysql对子查询的优化改写

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

《高性能mysql第三版》提到mysql会将in子查询改写成exists查询(书中基于的mysql版本是5.1.50和5.5)

但是在5.6之后,已经优化成使用半连接查询

首先要提的当然是臭名昭著的MySQL子查询问题,在MySQL5.5及之前的版本,所有有经验的MySQL DBA都会告诉你:绝不能在SQL的WHERE子句中使用子查询,因为那将可能产生灾难性的后果,因为很有可能每扫描一条数据,Where子查询都会被重新执行一遍,workaround的办法就是把WHERE里的子查询提升到FROM中,做成join操作;

和普通join查询不同的是,在semi join中,inner table 的结果集没有重复数据,当两表关联时,例如t1 semi join t2, 当t2存在匹配的记录时,返回t1的记录(t2的记录不会加入操作的结果集中),并且t1的记录最多只返回一次;不像inner join,每一个匹配的记录都会返回,对于semi join,在乎的只是是否匹配子查询而已。

 

 

 

SELECT class_num, class_name FROM class WHERE class_num IN (SELECT class_num FROM roster);

在这里,优化器可以识别出该 IN子句要求子查询仅返回roster表中每个类编号的一个实例 。在这种情况下,查询可以使用半联接;也就是说,该操作仅返回的每一行的一个实例,该实例 class与的行匹配 roster

外部查询规范中允许使用外部联接和内部联接语法,并且表引用可以是基表,派生表或视图引用。

 

 

 

结论:

1、mysql5.5以前会将 in 子查询改写成 exists 查询,如果外部表数据量大的情况下性能会非常糟糕。

2、mysql5.7(5.6没有测过,感兴趣的同学可以测测)对 in 子查询进行了优化,会将sql改写成 join 连接,这样优化器就可以始终优先访问数据量小的表格,减少IO,性能和直接写连接查询几乎是一样的(这点和网上书上说的是有出入的)。

3、exists查询会被分解成一个外部查询和相关子查询(DEPENDENT SUBQUERY),这样子查询会依赖于外部查询的结果,所以始终会对外部表进行全表扫描,外部表数据量大的时候要尤其注意。

 

 

参考文章

http://www.ishenping.com/ArtInfo/3900373.html        mysql数据库中 IN 和 EXISTS 的误区

https://dev.mysql.com/doc/refman/5.5/en/subquery-optimization.html        mysql官方文档解释

https://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html        mysql官方文档解释

https://yq.aliyun.com/articles/41095     MySQL 5.6的优化器改进

 

注意:

1:可以通过show warnings查看

2:阅读参考文章

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看