MySQL 连接查询超级详解

雨涛小哥_9232

雨涛小哥_9232

2020-07-08

5196人浏览

转载

1 作用

在数据库中join操作被称为连接,作用是能连接多个表的数据(通过连接条件),从多个表中获取数据合并在一起作为结果集返回给客户端。例如:

表A:


id name age
1 A 18
2 B 19
3 C 20

表B:


id uid gender
1 1 F
2 2 M

通过连接可以获取到合并两个表的数据:

select A.*,B.gender from  A left join B on A.id=B.uid


id name age gender
1 A 18 F
2 B 19 M
3 C 20 null

2 连接关键字

连接两个表我们可以用两个关键字:on,using。on可以指定具体条件,using则指定相同名字和数据类型的列作为等值判断的条件,多个则通过逗号隔开。
如下:

on: select * from A join B on A.id=B.id and B.name=''
using: select * from A join B using(id,name) = select * from A join B on 
A.id=B.id and A.name=B.name

3 连接类型

3.1 内连接

内连接和交叉连接

  • 语法:A  join | inner join |  cross join B
  • 表现:A和B满足连接条件记录的交集,如果没有连接条件,则是A和B的笛卡尔积
  • 特点:在MySQL中,cross join ,inner join和join所实现的功能是一样的。因此在MySQL的官方文档中,指明了三者是等价的关系。

隐式连接

  • 语法:from A,B,C
  • 表现:相当于无法使用on和using的join
  • 特点:逗号是隐式连接运算符。 隐式连接是SQL92中的标准内容,而在SQL99中显式连接才是标准,虽然很多人还在用隐私连接,但是它已经从标准中被移除。从使用的角度来说,还是推荐使用显示连接,这样可以更清楚的显示出多个表之间的连接关系和连接依赖的属性。

3.2 外连接

左外连接

  • 语法:A left join B
  • 表现:左表的数据全部保留,右表满足连接条件的记录展示,不满足的条件的记录则全是null

右外连接

  • 语法:A right join B
  • 表现:右表的数据全部保留,左表满足连接条件的记录展示,不满足的条件的记录则全是null

全外连接

MySQL不支持全外连接,只支持左外连接和右外连接。如果要获取全连接的数据,要可以通过合并左右外连接的数据获取到,如 select * from A left join B on A.name = B.name  union  select * from A right join B on B.name = B.name;。

这里union会自动去重,这样取到的就是全外连接的数据了。

3.3 自然连接

  • 语法:A natural join B ==== A natural left join B ==== A natural right join B
  • 表现:相当于不能指定连接条件的连接,MySQL会使用左右表内相同名字和类型的字段作为连接条件。
  • 特点:自然连接也分自然内连接,左外连接,右外连接,其表现和上面提到的一致,只是连接条件由MySQL自动判定。

4 执行顺序

在连接过程中,MySQL各关键字执行的顺序如下:

from -> on|using -> where -> group by -> having -> select -> order by -> 
limit

可以看到,连接的条件是先于where的,也就是先连接获得结果集后,才对结果集进行where筛选,所以在使用join的时候,我们要尽可能提供连接的条件,而少用where的条件,这样才能提高查询性能。

5 连接算法

join有三种算法,分别是Nested Loop Join,Hash join,Sort Merge Join。MySQL官方文档中提到,MySQL只支持Nested Loop Join这一种算法。

具体来说Nested Loop Join又分三种细分的算法:

  • SNLJ
  • BNLJ
  • INLJ

我们来看下对于连接语句select * from A left join B on A.id=B.tid,这三种算法是怎么连接的。

5.1 Simple Nested Loop Join(SNLJ)

SNLJ是在没有使用到索引的情况下,通过两层循环全量扫描连接的两张表,得到符合条件的两条记录则输出。也就是让两张表做笛卡尔积进行扫描,是比较暴力的算法,会比较耗时。其过程如下:

for (a in A) {
     for (b in B) {
         if (a.id == b.tid) {
             output <a>;
         }
     }
 }</a>

当然,MySQL即使在无索引可用,或者判断全表扫描可能比使用索引更快的情况下,还是不会选择使用过于粗暴的SNLJ算法,而是采用下面的算法。

5.2 Block Nested Loop Join(BNLJ)

INLJ是MySQL无法使用索引的时候采用的join算法。会将外层循环的行分片存入join buffer, 内层循环的每一行与整个buffer中的记录做比较,从而减少内层循环的次数,具体逻辑如下:

for (blockA in A.blocks) {
     for (b in B) {
         if (b.tid in blockA.id) {
             output <a>;
         }
     }
 }</a>

相比于SNLJ算法,BNLJ算法通过外层循环的结果集的分块,可以有效的减少内层循环的次数。

原理

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载

举例来说,外层循环的结果集是100行,使用SNLJ算法需要扫描内部表100次,如果使用BNLJ算法,假设每次分片的数量是10,则会先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中每次循环都直接匹配这10行数据,这样内层循环只需要10次,对内部表的扫描减少了9/10,所以BNLJ算法就能够显著减少内层循环表扫描的次数。

当然这里,不管SNLJ还是BNLJ算法,他们总的比较次数都是一样的,都是要拿外层循环的每一行与内层循环的每一行进行比较。

BNLJ算法减少的是总的扫描行数,SNLJ算法是外层循环要一行行扫描A表的数据,然后取A.id去表B一行行扫描看是否匹配。而BNLJ算法则是外层循环要一行行扫描A表的数据,然后放到内存分块里,然后去表B一行行扫描,扫描出来的B的一行数据与内存分块里的A的数据块进行比较。这里可以一次就是很多行A的数据与B的数据进行比较,而且是在内存中进行比较,速度更加快了。

影响因素

这里BNLJ算法总的扫描行数是由外层循环的数据量N,和分块数量K还有内层循环的数据量M决定的。其中分块数量K与外层循环的数据量N又是息息相关的,我们可以表示为λN,其中λ取值为(0~1)。则总扫描次数C=N+λNM。

可以看出,在这个式子里,N和λ的大小都会影响扫描行数,但是λ才是影响扫描行数的关键因素,这个值越小越好(除非N和M的差值非常大,这时候N才会成为关键影响因素)。

那什么会影响 λ 的大小呢?那就是 MySQL的join_buffer_size设置项的大小了。λ和join_buffer_size成倒数关系,join_buffer_size越大,分块越大,λ越小,分块数量也就越少,也就是外层循环的次数也越少。所以在使用不上索引的时候,我们要优先考虑扩大join_buffer_size的大小,这样优化效果会更明显。而在能使用上索引的时候,MySQL会使用以下算法来进行join。

5.3 Index Nested Loop Join(INLJ)

INLJ是MySQL判断能使用到被驱动表的索引的情况下采用的算法。假设A表的数据行为10,B表的数据行为100,且B.tid建立了索引,则对于select * from A left join B on A.id=B.tid,MySQL会采用Index Nested Loop Join。其过程如下:

for (a in A) {
     if (a.id in B.tid.Index) {
        output <a>;
     }
 }</a>

总共需要循环10次A,每次循环的时候通过索引查询一次B的数据。而如果我们反过来是B left join A的话,总共要循环100次B,由此可见如果使用join的话,需要让小表做驱动表,这样才能有效减少循环次数。但是需要注意的是,这个结论的前提是可以使用被驱动表的索引。

INLJ内层循环读取的是索引,可以减少内存循环的次数,提高join效率,但是也有缺点的,就是如果扫描的索引是非聚簇索引,并且需要访问非索引的数据,会产生一个回表读取数据的操作,这就多了一次随机的I/O操作。例如上面在索引里匹配到了tid,还要去找tid所在的行在磁盘所在的位置,具体可以见我以前的文章:MySQL索引详解之索引的存储方式。

6 注意点

  • 尽量增加连接条件,减少join后数据集的大小
  • 用小结果集驱动大结果集,将筛选结果小的表首先连接,再去连接结果集比较大的表
  • 被驱动表的被join的字段要建立索引,且使用上索引。使用上索引包括使用该字段,且不会有索引失效的情况出现
  • 设置足够大的join_buffer_size

7 外连接常见问题

Q:如果想筛选驱动表的数据,例如左连接筛选左表的数据,该在连接条件还是where筛选?
A:要通过where筛选,连接条件只影响连接过程,不影响连接返回的结果数(某些情况下连接条件会影响连接返回的结果数,例如左连接中,右侧匹配的数据不唯一的时候)

Q:被驱动表匹配的数据行不唯一导致最终连接数据超过驱动表数据量该怎么办?例如对于左连接,右表匹配的数据行不唯一。
A:join之前先对被驱动表去重,例如通过group by去重:A lef join (select * from B group by name)。

相关学习推荐:mysql视频教程

相关文章

PHP速学视频免费教程(入门到精通)
PHP速学视频免费教程(入门到精通)

PHP怎么学习?PHP怎么入门?PHP在哪学?PHP怎么学才快?不用担心,这里为大家提供了PHP速学教程(入门到精通),有需要的小伙伴保存下载就能学习啦!

下载

相关标签:

mysql

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
GoFrame框架Web开发与路由中间件
GoFrame框架Web开发与路由中间件

本专题聚焦GoFrame框架的Web开发,拆解路由注册与匹配规则、请求参数获取、参数校验与中间件执行顺序,说明统一响应、JWT鉴权、文件上传与接口文档生成的落地方式,并补充优雅重启与测试实践。

2026.11.17

20

15

GoFrame框架安装与gf命令行工具
GoFrame框架安装与gf命令行工具

本专题围绕GoFrame框架的安装与工具链配置展开,讲清Go版本要求、gf命令行的安装方式、代理镜像与依赖准备,说明项目初始化、热编译、交叉编译打包的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.17

60

15

GoFrame框架零基础入门
GoFrame框架零基础入门

本专题整理GoFrame框架的入门知识,从模块化设计理念、环境准备到跑通第一个Web服务,讲清目录分层、配置加载、日志与中间件的用法,并对比Gin、Go-Zero的差异,帮助Go开发者建立整体认知。

2026.11.17

60

15

SQLAlchemy 一对多关系怎么建立
SQLAlchemy 一对多关系怎么建立

SQLAlchemy 一对多关系怎么建立?本专题整理父子表外键、relationship 双向映射、关联查询、级联删除和加载优化等相关文章,帮助你理解并实现常见的 ORM 关系建模。

2026.11.17

20

10

SQLAlchemy 如何增删改查
SQLAlchemy 如何增删改查

SQLAlchemy 如何增删改查?本专题整理 ORM 新增记录、条件查询、更新和删除、Session 事务提交与批量处理等实用文章,帮助你掌握 Python 数据库操作流程。

2026.11.17

60

11

SQLAlchemy ORM 模型怎么写
SQLAlchemy ORM 模型怎么写

SQLAlchemy ORM 模型怎么写?本专题整理模型类定义、Mapped 字段映射、主键与约束、表结构创建和常见报错的相关文章,帮助你从基础配置到实际建模逐步掌握 SQLAlchemy。

2026.11.17

40

16

Go-Zero API开发使用教程
Go-Zero API开发使用教程

本专题聚焦Go-Zero的API开发,拆解.api描述文件的语法结构、路由与分组定义、参数绑定与校验规则,说明统一响应体、JWT鉴权、跨域与接口文档生成的落地方式,并给出网关聚合与接口测试的实践建议。

2026.11.12

180

15

Go-Zero安装与goctl环境配置教程
Go-Zero安装与goctl环境配置教程

本专题围绕Go-Zero的安装与工具链配置展开,讲清Go版本要求、goctl安装方式、代理镜像、protoc依赖与环境变量设置,说明多版本共存、升级回退与CI流水线安装的做法,并整理安装阶段的常见报错与验证步骤。

2026.11.12

140

15

Go-Zero零基础入门教程
Go-Zero零基础入门教程

本专题整理Go-Zero的入门知识,从框架定位、环境准备到跑通第一个API服务,讲清目录分层、配置加载、日志与中间件的基本用法,并对比Gin、Kratos的差异,帮助Go开发者建立整体认知,判断它是否适合当前项目。

2026.11.12

160

15

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程