专栏首页IT技术精选文摘MySQL 有几种Join,其底层实现原理是什么?

MySQL 有几种Join,其底层实现原理是什么?

mysql只支持一种join算法:Nested-Loop Join(嵌套循环连接),但Nested-Loop Join有三种变种:

原理:

1.Simple Nested-Loop Join

如下图,r为驱动表,s为匹配表,可以看到从r中分别取出r1、r2、......、rn去匹配s表的左右列,然后再合并数据,对s表进行了rn次访问,对数据库开销大

2.Index Nested-Loop Join(索引嵌套):

这个要求非驱动表(匹配表s)上有索引,可以通过索引来减少比较,加速查询。

在查询时,驱动表(r)会根据关联字段的索引进行查找,挡在索引上找到符合的值,再回表进行查询,也就是只有当匹配到索引以后才会进行回表查询。

如果非驱动表(s)的关联健是主键的话,性能会非常高,如果不是主键,要进行多次回表查询,先关联索引,然后根据二级索引的主键ID进行回表操作,性能上比索引是主键要慢。

3.Block Nested-Loop Join:

如果有索引,会选取第二种方式进行join,但如果join列没有索引,就会采用Block Nested-Loop Join。

可以看到中间有个join buffer缓冲区,是将驱动表的所有join相关的列都先缓存到join buffer中,然后批量与匹配表进行匹配,将第一种多次比较合并为一次,降低了非驱动表(s)的访问频率。

默认情况下join_buffer_size=256K,在查找的时候MySQL会将所有的需要的列缓存到join buffer当中,包括select的列,而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

实例:

假设两张表a 和 b

其中b的关联有comments_id,所以有索引。

1.join

使用的是Index Nested-Loop Join,先对驱动表a的主键筛选,得到一条,然后对非驱动表b的索引进行seek匹配,预计得到一条数据。

下面这种情况没用到索引:

使用Block Nested-Loop Join,如果b表数据少,作为驱动表,将b的需要的数据缓存到join buffer中,批量对a表扫描

2.left join:

这里用到了索引,所以会采用Index Nested-Loop Join,因为没有筛选条件,会选择一张表作为驱动表去进行join,去关联非驱动表的索引。

如果加了条件

就会从驱动表筛选出一条来进行对非驱动表的匹配。

left join:会保全左表数据,如果右表没相关数据,会显示null

fight join:会保全右表数据,如果左表没相关数据,会显示null

inner join:部分主从表,结果会取两个表针对on条件相匹配的最小集

本文分享自微信公众号 - IT技术精选文摘(ITHK01)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-11-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL索引背后的数据结构及算法原理

    用户1263954
  • MySQL-性能优化-索引和查询优化

    用户1263954
  • MySQL索引设计概要

    在关系型数据库中设计索引其实并不是复杂的事情,很多开发者都觉得设计索引能够提升数据库的性能,相关的知识一定非常复杂。 ? 然而这种想法是不正确的,索引其实并不是...

    用户1263954
  • =Java面试通关要点汇总集之核心篇参考答案

    小小明童鞋
  • Java面试通关要点汇总集之核心篇参考答案

    res = mysql_query( 'select * from order where date < = $curDate'); 原因: 释放了数据库的CP...

    码农小胖哥
  • 数据 优化查询的目的

    数据通
  • 数据优化查询的意义

    数据通20847430
  • python入门——解释器、编辑器、包的安装

    解释器 python并不需要先编译再执行,而是直接由解释器解释执行。python解释器的版本目前主要以python2.7和python3.6(或python3....

    企鹅号小编
  • 解决g++: error: /usr/lib/libuuid.a: 没有那个文件或目录

    AlicFeng
  • python 匿名函数lambda

    在python开发中常规的函数在调用之前都需要先声明,而python还有一种匿名函数,有速写函数的功能并且匿名函数不需要声明也没有函数名字,完全不需要担心函数名...

    猿说编程[Python和C]

扫码关注云+社区

领取腾讯云代金券