mysql进阶优化篇04——深入JOIN语句的底层原理

前 言
🍉 作者简介：半旧518，长跑型选手，立志坚持写10年博客，专注于java后端
🍌 专栏简介：mysql基础、进阶，主要讲解mysql数据库sql刷题、进阶知识，包括索引、数据库调优、分库分表等
🌰 文章简介：本文将介绍JOIN语句的底层原理，建议收藏备用。
🍓 相关推荐：

• MySql进阶索引篇01——深度讲解索引的数据结构：B+树
• Mysql进阶索引篇02——InnoDB存储引擎的数据存储结构
• Mysql进阶索引篇03——2个新特性，11+7条设计原则教你创建索引
• Mysql进阶优化篇01——四万字详解数据库性能分析工具(深入、全面、详细，收藏备用) Mysql进阶优化篇02——索引失效的10种情况及原理 Mysql进阶优化篇03——多表查询的优化
• 大厂SQL面试真题大全

文章目录

join 方式连接多表，本质就是各个表之间数据的循环匹配。MySQL 5.5 版本之前，MySQL 只支持一种表间关联方式，就是嵌套循环。如果关联表的数据量很大，则 join 关联的执行时间会非常漫长。在 MySQL 5.5 以后的版本中，MySQL 通过引入 BNLJ 算法来优化嵌套执行。

1.驱动表和被驱动表

驱动表就是主表，被驱动表就是从表、非驱动表。
对于内连接来说:

SELECT*FROM A JOIN B ON...SQL 复制

A 并不一定就是驱动表，优化器会根据你的查询语句做优化，决定先查哪张表。先查询的表就是驱动表，反之就是被驱动表。通过 explain 关键字可以查看。该专栏的上一篇博客Mysql进阶优化篇03——多表查询的优化已经总结了优化器选择的规律：两表中一个表有索引，一个表没有索引，则没有索引的为驱动表，有索引的为被驱动表；两个表都没有索引，则小表驱动大表。

💡Q:上面的规律是一成不变的吗？如果一个表有索引，但是数据量很小，一个表没有索引，但是数据量很大，情况会是怎样的呢？
我们要明白优化器的优化原理：对于内连接mysql会选择扫描次数比较少的作为驱动表，因此实际生产中最好使用

Explain

测试验证。

对于外连接来说：

SELECT*FROM A LEFTJOIN B ON...

或

SELECT*FROM B RIGHTJOIN A ON...

通常，大家会认为 A 就是驱动表，B 就是被驱动表。但也未必。测试如下：

CREATETABLE a(f1 INT,f2 INT,INDEX(f1))ENGINE=INNODB;CREATETABLE b(f1 INT,f2 INT)ENGINE=INNODB;INSERTINTO a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);INSERTINTO b values(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

EXPLAINSELECT*FROM a LEFTJOIN b ON(a.f1=b.f1)WHERE(a.f2=b.f2);

执行结果如下。

明明我们写的是

a LEFT JOIN b

,但是我们执行sql查询时，却是b作为了驱动表，a作为了被驱动表。

实际上，查询优化器会帮你把外连接改造为内连接，然后根据其优化策略选择驱动表与被驱动表。

不信您可以执行下面sql对比验证。

EXPLAINSELECT*FROM a  JOIN b ON(a.f1=b.f1)WHERE(a.f2=b.f2);

再来.

EXPLAINSELECT*FROM a LEFTJOIN b ON(a.f1=b.f1)AND(a.f2=b.f2);

其执行结果如下，此时a作为了驱动表，b作为了被驱动表。

而其对应的内连接查询如下。此时a作为了被驱动表，b作为了驱动表！

EXPLAINSELECT*FROM a  JOIN b ON(a.f1=b.f1)AND(a.f2=b.f2);

是不是越来越迷糊了，因此还是建议您在遇到复杂的场景时，不要跟着感觉走，而是多用用

Explain

。同时，我们接下来层层深入JOIN语句的底层原理，揭开神秘面纱。

2.Simple Nested-Loop Join（简单的嵌套循环连接）

算法相当简单，从表 A 取出一条数据 1，遍历表 B，将匹配到的数据放到 result。以此类推，驱动表 A 中的每一条记录与被动驱动表 B 的记录进行判断：

可以看到这种方式效率是非常低的，以上述表 A 数据 100 条，表 B 数据 1000 条，则 A*B=10 万次。开销统计如下：

当然 MySQL 肯定不会这么粗暴的进行表的连接，所以就出现了后面的两种其的优化算法。

另外，从读取记录数来看：A+B*A中，驱动表A对性能的影响权重更大。因此我们优化器会选择小表驱动大表。

3.Index Nested-Loop Join（索引嵌套循环连接）

Index Nested-Loop Join 其优化的思路主要是为了 减少内层表数据的匹配次数，所以要求被驱动表上必须 有索引 才行。通过外层表匹配条件直接与内层索引进行匹配，避免和内层表的每条记录进行比较，这样极大地减少了对内层表的匹配次数。下图是给被驱动表B加上了索引后的原理图。可以看到，b字段是主键时，可以很快找到被驱动表中匹配的字段；索引是非主键时，还需要进行一次回表操作。

驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故 MySQL 优化器都倾向于使用记录数少的表作为驱动表（外表）。

其性能开销如下表所示，其中Join比较的此时为AB的B+树索引层数。回表次数，如果是主键索引就不需要回表了，如果是二级索引需要回表B匹配的数据条数。

4 Block Nested-Loop Join（快嵌套循环连接）

如果存在索引，那么会使用 index 的方式进行 join，如果 join 的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把驱动表的记录再加载到内存匹配，这样周而复始，大大增加了 IO 次数。为了减少被驱动表的 IO 次数，就出现了 Block Nested-Loop Join

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer 缓冲区，将驱动表 join 相关的部分数据列（大小受 join buffer 的限制）缓存到 join buffer 中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和 join buffer 中的所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中的多次比较合并成一次，降低了被动表的访问频率。

🎃注意：
这里缓存的不只是关联表的列，sql中select 后面要查询的列也会缓存起来。
在一个有 N 个 join 关联的 SQL 中会分配 N-1 个 join buffer。所以查询的时候尽量减少不必要的字段，可以 让 join buffer 中存放更多的列。

其原理如下图。

其开销统计如下。其中内表扫描次数为A表中数据占用的内存大小(包括缓存的select后字段)，除缓冲池的大小，再加1(我的理解是缓冲池的数据也需要扫描一次，如果我的理解有误，欢迎指正)

🎄参数设置：
block_nested_loop
通过 show variables like ‘%optimizer_switch%’ 查看 block_nested_loop 状态。默认是开启的。
join_buffer_size
驱动表能不能一次加载完，要看 join buffer 能不能存储所有的数据，默认情况下 join_buffer_size = 256K。
join buffer size 的最大值在 32 位系统可以申请 4G，而在 64 位操做系统下可以申请大于 4G 的 join_buffer空间（64 位 Windows 除外，其大值会被截断为 4GB并发出警告）。

5 JOIN小结

(1)保证被驱动表的 JOIN 字段已经创建了索引

(2)需要 JOIN 的字段，数据类型保持绝对一致。

(3)LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。

(4)INNER JOIN 时，MySQL 会自动将小结果集的表选为驱动表 。选择相信 MySQL 优化策略。

(5)能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
不建议使用子查询，建议将子查询 SQL 拆开结合程序多次查询，或使用 JOIN 来代替子查询。

(6)整体效率
INLJ > BNLJ > SNLJ

(7)正确理解小表驱动大表：大小不是指表中的记录数，而是永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）。比如A表有100条记录，B表有1000条记录，但是where条件过滤后，B表结果集只留下50个记录，A表结果集有80条记录，此时就可能是B表驱动A表。其实上面的例子还是不够准确，因为结果集的大小也不能粗略的用结果集的行数表示，而是表行数 * 每行大小。其实要理解你只需要结合Join Buffer就好了，因为表行数 * 每行大小越小，其占用内存越小,就可以在Join Buffer中尽量少的次数加载完了。

6 hash join

从 MySQL 8.0.20 版本开始将废弃 BNLJ，因为加入了 hash join 默认都会使用 hash join

Nested Loop：

对于被连接的数据子集较小的情况，Nested Loop 是个较好的选择。

Hash Join 是做 大数据集连接 时的常用方法，优化器使用两个表中较小（相对较小）的表利用 join key 在内存中建立 散列表，然后扫描较大的表并探测散列表，找出与 Hash 表匹配的行。

这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和,在表很大的情况下并不能完全放入内存，这时优化器会将它分割成 若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高 I/O 的性能。

它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是 Join 的重型升降机。Hash Join 只能应用于等值连接（如 WHERE A.COL1 = B.COL2），这是由 Hash 的特点决定的。

工欲善其事，必先利其器”。要想成为工作上的数据库高手，面试时的题霸，独步江湖，就必须拿到一份"武林秘籍"。

我个人强推牛客网：找工作神器|大厂java面经汇总|超全笔试题库

推荐理由：
1.刷题题库，题目特别全面，刷爆笔试再也不担心

链接： 找工作神器|大厂java面经汇总|超全笔试题库
2.超全面试题、成体系、高质量，还有AI模拟面试黑科技

链接： 工作神器|大厂java面经汇总|超全笔试题库
3.超多面经，大厂面经很多

4.内推机会，大厂招聘特别多

链接： 找工作神器|大厂java面经汇总|超全笔试题库
5.大厂真题，直接拿到大厂真实题库，而且和许多大厂都有直接合作，题目通过率高有机会获得大厂内推资格。

链接： 找工作神器|大厂java面经汇总|超全笔试题库