MySQL调优篇 | 表连接方式及算法（3）

数据与人 2021-12-20

1210

点击上方"数据与人", 右上角选择“设为星标”

分享干货，共同成长！

【前言】

经常有一些朋友向我咨询，如何写出高效的SQL，这不是三言两语能说得清的，索性认真来写一下，增删查改方面的知识我不再赘述，如果有基础薄弱的同学，可以好好的补一补再来看。

以MySQL为基础，MySQL调优篇内容主要包含MySQL逻辑架构、索引知识、表关联算法、explain执行计划解读及SQL调优实战等。

文章受众主要为两类人：

第一类人是工作中不可避免的会接触到MySQL的人，比如说一些项目人员、开发人员、测试人员等。

第二类人是专职DBA。

其实不管是专职的还是非专职的，就我接触到的情况而言，很多DBA平时维护MySQL看起来没什么问题，但其实没有很好的理论支撑，知其然而不知其所以然，解释一个简单的问题就能问倒一大部分的人。

比如说：MySQL的逻辑架构，分析当前业务架构优缺点？SQL工作原理是什么样的？

而且很多公司招聘面试的时候，考验的也是背后的原理居多，基本上没有机试。面试官问一个问题，即便你会解决但就是说不出原理，那么你肯定要不了高薪。

理论+实战=高薪

文章能够让大家有所收获、有所借鉴那是最好的。

【表连接方式】

常见的七种Join理论，如图所示：

1、左连接：A独有+在A中的B部分

语法：

select * from A left join B on A.key = B.key

没有满足A的B补Null。

2、内连接：A和B的交集

语法：

select * from A inner join B on A.key = B.key

3、右连接：B独有+在B中的A部分

语法：

select * from A right join B on A.key = B.key

没有满足B的A补Null。

上面三个是非常常见且常用的Join，那么还有四类Join：

4、A去掉B的部分，A的独有

对比左连接，其实是把中间属于A的B部分给干掉了。

语法：

select * from A LEFT JOIN B on A.aid = B.bid    左连接
where B.bid is null   B不在A的部分

因为本身左连接已经把所有A的值都包含出来了，同时多了的部分，就是B在A的部分，只要拿到为空的部分，其实就是B不在A的部分。

5、B去掉A的部分，B的独有

对比右连接，其实是把中间属于B的A部分给干掉了

语法：

select * from A RIGHT JOIN B on A.aid = B.bid    右连接
where A.aid is null   A不在B的部分

6、A和B去掉交集部分

语法：

select * from A LEFT JOIN B on A.aid = B.bid    左连接
where B.bid is null   B不在A的部分
union
select * from A RIGHT JOIN B on A.aid = B.bid    右连接
where A.aid is null   A不在B的部分

实际上是不是上面两个的合体？

7、外连接：A和B的并集

语法：

select * from A LEFT JOIN B on A.aid = B.bid  
union
select * from A RIGHT JOIN B on A.aid = B.bid

【表连接算法】

MySQL数据库根据不同的使用场合，支持两种Nested-Loops Join算法，一种是Simple Nested-Loops Join（NLJ）算法，另一种是Block Nested-Loops Join（BNL）算法。

1、简单嵌套循环连接（Simple Netsted-Loop Join）

对于两表连接，驱动表只会被访问一遍，被驱动表具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。

对于内连接，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左外连接的驱动表就是左边的那个表，而右外连接的驱动表就是右边那个表。

两表连接的大致过程：

选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
对上一步中查询驱动得到的结果集中每一条记录都被分别到被驱动表中查找匹配的记录。

如图：

如果有3个表进行连接的话，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上边过程。

这种驱动表只访问一次，但被驱动表却可能被多次访问的连接执行方式称之为嵌套循环连接（Nested-Loop Join）。

2、索引嵌套循环连接（Index Nested Loops Join），

有一方在连接字段上有索引，这种场景在MySQL的使用中见的比较多。

优化器会考虑选择有索引的一方作为被驱动表，双方都有索引则选择索引高度低的，索引高度一样则选择记录数多的作为被驱动表，对于驱动表的每一条记录，在被驱动表中使用索引查询，大大减少了比较次数，提高了查询效率。

索引是主键时效率更高。

如图：

3、基于块的嵌套循环连接（Block Nested-Loop Join）

扫描一个表的过程其实就是把这个表的数据从磁盘上加载到内存中，然后在内存中比较匹配条件。

在实际环境中，面对百千万的数，内存放不下，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，等扫描后面的记录的时候可能内存不足，需要把前面的数据在内存中释放掉。

而采用嵌套循环连接算法的两个表中，被驱动表要被访问好多次，如果被驱动表中的数据特别多而且不能使用索引访问的话，那就相当于从磁盘上读好多次这个表，这个IO代价就非常大，所以我们应该尽量减少访问被驱动表的次数。

在嵌套循环连接中，驱动表查询结果集中有多少条记录，就需要驱动表数据被加载多少次来进行匹配，那可不可以把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价了。

所以提出可join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录进行匹配，因为匹配的过程是在内存中完成的，所以这样可以减少被驱动表的IO代价。