文档章节

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

 如月王子
发布于 2016/06/24 11:08
字数 1174
阅读 27
收藏 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
sparksql执行流程分析

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

ZPPenny
2017/03/27
0
0
SQL Server 优化器内幕【上篇】

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

马弓手三菜
07/10
0
0
关于SQL Tuning的知识体系

要很好的做SQL Tuning的工作,非常有必要理解SQL 优化引擎是如何工作,而SQL执行计划就是SQL 优化引擎工作的结果。 要读懂执行计划,需要知道access path,需要理解join order,join method...

N0body
2013/07/21
0
0

没有更多内容

加载失败,请刷新页面

加载更多

如何通过 J2Cache 实现分布式 session 存储

做 Java Web 开发的人多数都会需要使用到 session (会话),我们使用 session 来保存一些需要在两个不同的请求之间共享数据。一般 Java 的 Web 容器像 Tomcat、Resin、Jetty 等等,它们会在...

红薯
今天
3
0
C++ std::thread

C++11提供了std::thread类来表示一个多线程对象。 1,首先介绍一下std::this_thread命名空间: (1)std::this_thread::get_id():返回当前线程id (2)std::this_thread::yield():用户接口...

yepanl
今天
3
0
Nignx缓存文件与动态文件自动均衡的配置

下面这段nginx的配置脚本的作用是,自动判断是否存在缓存文件,如果有优先输出缓存文件,不经过php,如果没有,则回到php去处理,同时生成缓存文件。 PHP框架是ThinkPHP,最后一个rewrite有关...

swingcoder
今天
1
0
20180920 usermod命令与用户密码管理

命令 usermod usermod 命令的选项和 useradd 差不多。 一个用户可以属于多个组,但是gid只有一个;除了gid,其他的组(groups)叫做扩展组。 usermod -u 1010 username # 更改用户idusermod ...

野雪球
今天
3
0
Java网络编程基础

1. 简单了解网络通信协议TCP/IP网络模型相关名词 应用层(HTTP,FTP,DNS等) 传输层(TCP,UDP) 网络层(IP,ICMP等) 链路层(驱动程序,接口等) 链路层:用于定义物理传输通道,通常是对...

江左煤郎
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部