文档章节

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

 如月王子
发布于 2016/06/24 11:08
字数 1174
阅读 21
收藏 0
点赞 0
评论 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
淮安
一例 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数据库使用JOIN的优化方法

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

walb呀
2017/12/07
0
0
关于SQL Tuning的知识体系

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

N0body
2013/07/21
0
0
SQL Server 优化器内幕【上篇】

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

马弓手三菜
07/10
0
0
PgSQL · 源码分析 · PG优化器浅析

在使用PostgreSQL数据库过程中,对SQL调优最常用的手段是使用explain查看执行计划,很多时候我们只关注了执行计划的结果而未深入了解执行计划是如何生成的。优化器作为数据库核心功能之一,也...

阿里云RDS-数据库内核组
2016/09/08
0
0
postgresql 执行计划理解

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

从前
2013/06/05
0
8
PgSQL · 源码分析 · PG 优化器中的pathkey与索引在排序时的使用

概要 SQL在PostgreSQL中的处理,是类似于流水线方式的处理,先后由: 词法、语法解析,生成解析树后,将其交给语义解析 语义解析,生成查询树,将其交给Planner Planner根据查询树,生成执行...

阿里云RDS-数据库内核组
2017/08/08
0
0
高性能MySQL-3rd-(六)查询性能优化

/ -------------------------------------------------------- * 高性能MySQL-3rd-Baron Schwartz-笔记 * 第六章 查询性能优化 */ -------------------------------------------------------......

zhmsong
2014/01/20
0
0
DRDS分布式SQL引擎—执行计划介绍

DRDS分布式SQL引擎 — 执行计划介绍 前言 数据库系统中,执行计划是对 SQL 如何执行的形式化表示,往往由若干关系操作符构成,用户可以通过对应的 EXPLAIN 命令查看,并通过执行计划大致了解...

lyrewu
07/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

实现异步有哪些方法

有哪些方法可以实现异步呢? 方式一:java 线程池 示例: @Test public final void test_ThreadPool() throws InterruptedException { ScheduledThreadPoolExecutor scheduledThre......

黄威
今天
0
0
linux服务器修改mtu值优化cpu

一、jumbo frames 相关 1、什么是jumbo frames Jumbo frames 是指比标准Ethernet Frames长的frame,即比1518/1522 bit大的frames,Jumbo frame的大小是每个设备厂商规定的,不属于IEEE标准;...

六库科技
今天
0
0
牛客网刷题

1. 二维数组中的查找(难度:易) 题目描述 在一个二维数组中(每个一维数组的长度相同),每一行都按照从左到右递增的顺序排序,每一列都按照从上到下递增的顺序排序。请完成一个函数,输入...

大不了敲一辈子代码
今天
0
0
linux系统的任务计划、服务管理

linux任务计划cron 在linux下,有时候要在我们不在的时候执行一项命令,或启动一个脚本,可以使用任务计划cron功能。 任务计划要用crontab命令完成 选项: -u 指定某个用户,不加-u表示当前用...

黄昏残影
昨天
0
0
设计模式:单例模式

单例模式的定义是确保某个类在任何情况下都只有一个实例,并且需要提供一个全局的访问点供调用者访问该实例的一种模式。 实现以上模式基于以下必须遵守的两点: 1.构造方法私有化 2.提供一个...

人觉非常君
昨天
0
0
《Linux Perf Master》Edition 0.4 发布

在线阅读:https://riboseyim.gitbook.io/perf 在线阅读:https://www.gitbook.com/book/riboseyim/linux-perf-master/details 百度网盘【pdf、mobi、ePub】:https://pan.baidu.com/s/1C20T......

RiboseYim
昨天
1
0
conda 换源

https://mirrors.tuna.tsinghua.edu.cn/help/anaconda/ conda config --add channels https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/free/conda config --add channels https://mir......

阿豪boy
昨天
1
0
Confluence 6 安装补丁类文件

Atlassian 支持或者 Atlassian 缺陷修复小组可能针对有一些关键问题会提供补丁来解决这些问题,但是这些问题还没有放到下一个更新版本中。这些问题将会使用 Class 类文件同时在官方 Jira bug...

honeymose
昨天
0
0
非常实用的IDEA插件之总结

1、Alibaba Java Coding Guidelines 经过247天的持续研发,阿里巴巴于10月14日在杭州云栖大会上,正式发布众所期待的《阿里巴巴Java开发规约》扫描插件!该插件由阿里巴巴P3C项目组研发。P3C...

Gibbons
昨天
1
0
Tomcat介绍,安装jdk,安装tomcat,配置Tomcat监听80端口

Tomcat介绍 Tomcat是Apache软件基金会(Apache Software Foundation)的Jakarta项目中的一个核心项目,由Apache、Sun和其他一些公司及个人共同开发而成。 java程序写的网站用tomcat+jdk来运行...

TaoXu
昨天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部