文档章节

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

 如月王子
发布于 2016/06/24 11:08
字数 1174
阅读 64
收藏 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~~!


粉丝 15
博文 177
码字总数 3194
作品 0
淮安
私信 提问
加载中
请先登录后再评论。
Netty那点事(三)Channel与Pipeline

Channel是理解和使用Netty的核心。Channel的涉及内容较多,这里我使用由浅入深的介绍方法。在这篇文章中,我们主要介绍Channel部分中Pipeline实现机制。为了避免枯燥,借用一下《盗梦空间》的...

黄亿华
2013/11/24
2W
22
用vertx实现高吞吐量的站点计数器

工具:vertx,redis,mongodb,log4j 源代码地址:https://github.com/jianglibo/visitrank 先看架构图: 如果你不熟悉vertx,请先google一下。我这里将vertx当作一个容器,上面所有的圆圈要...

jianglibo
2014/04/03
4.2K
3
SQLServer实现split分割字符串到列

网上已有人实现sqlserver的split函数可将字符串分割成行,但是我们习惯了split返回数组或者列表,因此这里对其做一些改动,最终实现也许不尽如意,但是也能解决一些问题。 先贴上某大牛写的s...

cwalet
2014/05/21
9.7K
0
Swift百万线程攻破单例(Singleton)模式

一、不安全的单例实现 在上一篇文章我们给出了单例的设计模式,直接给出了线程安全的实现方法。单例的实现有多种方法,如下面: class SwiftSingleton { } 这段代码的实现,在shared中进行条...

一叶博客
2014/06/20
3.5K
16
5分钟 maven3 快速入门指南

前提条件 你首先需要了解如何在电脑上安装软件。如果你不知道如何做到这一点,请询问你办公室,学校里的人,或花钱找人来解释这个给你。 不建议给Maven的服务邮箱来发邮件寻求支持。 安装Mav...

fanl1982
2014/01/23
1.2W
7

没有更多内容

加载失败,请刷新页面

加载更多

教师必备的7个免费下载教学课件网站

教学课件是教师教学的必备工具,好的课件可以帮助学生更好的融入课堂氛围,吸引学生关注课堂教学知识,帮助增进学生对教学知识的理解,从而更好的实现学习目的。本期,小编收集了比较好的7个...

V5codings
03/01
0
0
ubuntu 安装 oh my zsh

https://github.com/ohmyzsh/ohmyzsh https://ohmyz.sh/#install 安装 sudo apt-get install zsh -ysh -c "$(wget https://raw.github.com/ohmyzsh/ohmyzsh/master/tools/install.sh ......

阿豪boy
10分钟前
0
0
自然语言处理中的语言模型简介

作者|Devyanshu Shukla 编译|Flin 来源|medium 在这篇文章中,我们将讨论关于语言模型(LM)的所有内容 什么是LM LM的应用 如何生成LM LM的评估 介绍 NLP中的语言模型是计算句子(单词序列)...

人工智能遇见磐创
49分钟前
9
0
electron-vue跨平台桌面应用开发实战教程(十二)——集成加密版的sqlite3:sqlcipher

本文主要讲解集成及使用sqlcipher,一个可以加密的sqlite。sqlcipher官方npm地址:https://www.npmjs.com/package/@journeyapps/sqlcipher 由于和sqlite的功能一样,只是增加了加密的功能,所...

david_zh
今天
23
0
传统数据仓库搭建思路

该图为阿里云大学课程学习整理所得,在语雀平台制作。

wffger
今天
17
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部