文档章节

从一个MySQL left join优化的例子加深对查询计划的理解

 如月王子
发布于 2016/06/24 11:08
字数 1174
阅读 29
收藏 0
   今天遇到一个left join优化的问题,搞了一下午,中间查了不少资料,对MySQL的查询计划还有查询优化有了更进一步的了解,做一个简单的记录:

select c.* from hotel_info_original c
left join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
where h.hotel_id is null

   这个sql是用来查询出c表中有h表中无的记录,所以想到了用left join的特性(返回左边全部记录,右表不满足匹配条件的记录对应行返回null)来满足需求,不料这个查询非常慢。先来看查询计划:



   rows代表这个步骤相对上一步结果的每一行需要扫描的行数,可以看到这个sql需要扫描的行数为35773*8134,非常大的一个数字。本来c和h表的记录条数分别为40000+和10000+,这几乎是两个表做笛卡尔积的开销了(select * from c,h)。
于是我上网查了下MySQL实现join的原理,原来MySQL内部采用了一种叫做 nested loop join的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与 Join,则再通过前两个表的 Join 结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复,基本上MySQL采用的是最容易理解的算法来实现join。所以驱动表的选择非常重要,驱动表的数据小可以显著降低扫描的行数。
那么为什么一般情况下join的效率要高于left join很多?很多人说不明白原因,只人云亦云,我今天下午感悟出来了一点。一般情况下参与联合查询的两张表都会一大一小,如果是join,在没有其他过滤条件的情况下MySQL会选择小表作为驱动表,但是left join一般用作大表去join小表,而left join本身的特性决定了MySQL会用大表去做驱动表,这样下来效率就差了不少,如果我把上面那个sql改成
select c.* from hotel_info_original c
join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
查询计划如下:



     很明显,MySQL选择了小表作为驱动表,再配合(hotel_id,hotel_type)上的索引瞬间降低了好多个数量级。。。。。
另外,我今天还明白了一个关于left join 的通用法则,即:如果where条件中含有右表的非空条件(除开is null),则left join语句等同于join语句,可直接改写成join语句。
后记:
随着查看MySQL reference manual对这个问题进行了更进一步的了解。MySQL在执行join时会把join分为system/const/eq_ref/ref/range/index/ALl等好几类,连接的效率从前往后
依次递减,对于我的第一个sql,连接类型是index,所以几乎是全表扫描的效果。但是我很奇怪我在(hotel_id,hotel_type)两列上声明了unique key,根据官方文档连接类型应该是eq_ref才对,
     这个问题一直困扰了我两天,在google和stackoverflow上都没有找到能够解释这个问题的文章,莫非我这个问题无解了?抱着解决这个问题的决心今天又翻看了一遍MySQL官方文档
关于优化查询的部分,看到了这样一句:这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。我感觉我找到了问题所在,于是我将original和 collection表的(hotel_type,hotel_id)的encoding和collation(决定字符比较的规则)全部改成统一的utf8_general_ci,然后再次运行第一条sql的查询计划,得到如下结果:



     连接类型已经由index优化到了ref,如果将hotel_type申明为not null可以优化到eq_ref,不过这里影响不大了,优化后这条sql能在0.01ms内运行完。

     那么如何优化left join:
1、条件中尽量能够过滤一些行将驱动表变得小一点,用小表去驱动大表

2、右表的条件列一定要加上索引(主键、唯一索引、前缀索引等),最好能够使type达到range及以上(ref,eq_ref,const,system)

3、无视以上两点,一般不要用left join~~!


本文转载自:http://luxuryzh.iteye.com/blog/1976004

共有 人打赏支持
粉丝 15
博文 177
码字总数 3194
作品 0
淮安
私信 提问
SQL数据库使用JOIN的优化方法

很早以前,也是一提到SQL Server,就觉得它的性能没法跟Oracle相比,一提到大数据处理就想到Oracle。自己一路走来,在本地blog上记录了很多优化方面的 post,对的错的都有,没有时间系列的整...

walb呀
2017/12/07
0
0
一例 Hive join 优化实战

由于 hive 与传统关系型数据库面对的业务场景及底层技术架构都有着很大差异,因此,传统数据库领域的一些技能放到 Hive 中可能已不再适用。关于 hive 的优化与原理、应用的文章,前面也陆陆续...

大数据之路
2014/08/29
0
2
SQL Server 优化器内幕【上篇】

我记得我还在微软的时候,有一次和Raymond 一起做1:1,Raymond 问我,将来你的技术方向是什么,我说我想往HA方向发展,因为是我的强项。Raymond问我还有别的吗?我犹豫地说,我也想做优化器...

马弓手三菜
07/10
0
0
sparksql执行流程分析

在前面的文章《spark基础(上篇)》和《spark基础(下篇)》里面已经介绍了spark的一些基础知识,知道了spark sql是spark中一个主要的框架之一。本文我们通过源码,来介绍下spark sql的执行流...

ZPPenny
2017/03/27
0
0
postgresql 执行计划理解

首先看下postgresql 执行计划中的一些术语和关键字。 执行计划运算类型 操作说明 是否有启动时间 Seq Scan 扫描表 无启动时间 Index Scan 索引扫描 无启动时间 Bitmap Index Scan 索引扫描 ...

从前
2013/06/05
0
8

没有更多内容

加载失败,请刷新页面

加载更多

mysql load data 批量导入使用

最近在研究使用mycat,其中有使用批量数据导入,简单记录一下使用方式。 查看系统属性 mysql>show variables like '%secure%'+--------------------------+-----------------------+| Vari...

FansinZhao
8分钟前
0
0
浅谈React的最大亮点——虚拟DOM

在Web开发中,需要将数据的变化实时反映到UI上,这时就需要对DOM进行操作,但是复杂或频繁的DOM操作通常是性能瓶颈产生的原因,为此,React引入了虚拟DOM(Virtual DOM)的机制。 一、什么是...

peakedness丶
11分钟前
0
0
下一代大数据处理引擎,阿里云实时计算独享模式重磅发布

摘要: 11月14日,阿里云重磅发布了实时计算独享模式,即用户独享一部分物理资源,这部分资源在网络/磁盘/CPU/内存等资源上跟其他用户完全独立,是实时计算在原有共享模式基础上的重大升级。...

阿里云官方博客
16分钟前
0
0
Spring MVC 测试样例

1、需要javax.servlet-api V3.0以上支持 <!--3.0以上版本--> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-......

jcc_codingBoy
18分钟前
2
0
kettle增量同步oracle数据到mysql

kettle增量同步oracle数据到mysql 适合表中有更新时间的字段 kettle安装 绿色,下载后解压即可(略), -> Download 连接数据库 需要导入jdbc相关驱动jar包到lib目录(oracle、mysql对应jar...

o00o
22分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部