读《MySQL性能调优与架构设计》笔记之Join 的实现原理及优化思路

落日映苍穹つ 2022-08-06 11:15 131阅读 0赞

Join 的实现原理在MySQL中，只有一种Join 算法，就是大名鼎鼎的Nested Loop Join，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

下面我们将通过一个三表Join 语句示例来说明MySQL 的Nested Loop Join 实现方式。

Query 如下：

![20150118183311368][]

为了便于示例，我们通过如下操作为group\_message 表增加了一个group\_id 的索引：

**        create  index**idx\_group\_message\_gid\_uid **on** group\_message(group\_id);

然后看看我们的Query 的执行计划：

![20150118183315406][]

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type:SIMPLE

table: g

type: ref

possible\_keys:user\_group\_gid\_ind,user\_group\_uid\_ind,user\_group\_gid\_uid\_ind

key:user\_group\_uid\_ind

key\_len: 4

ref: const

rows: 2

Extra:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type:SIMPLE

table: m

type: ref

possible\_keys:PRIMARY,idx\_group\_message\_gid\_uid

key:idx\_group\_message\_gid\_uid

key\_len: 4

ref:example.g.group\_id

rows: 3

Extra:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type:SIMPLE

table: c

type: ref

possible\_keys:idx\_group\_message\_content\_msg\_id

key:idx\_group\_message\_content\_msg\_id

key\_len: 4

ref:example.m.id

rows: 2

Extra:

我们可以看出，MySQL Query Optimizer 选择了user\_group作为驱动表，首先利用我们传入的条件user\_id通过该表上面的索引user\_group\_uid\_ind来进行const条件的索引ref查找，然后以user\_group表中过滤出来的结果集的group\_id字段作为查询条件，对group\_message循环查询，然后再通过user\_group和group\_message 两个表的结果集中的group\_message的id作为条件与group\_message\_content的group\_msg\_id比较进行循环查询，才得到最终的结果。

下图可以更清晰的标识出实际的执行情况：

![20150118183359515][]

假设我们去掉group\_message\_content 表上面的group\_msg\_id字段的索引，然后再看看执行计划会变成怎样：

**drop  index** idx\_group\_message\_content\_msg\_id **on** group\_message\_content;

Query OK, 96rows affected (0.11 sec)

![20150118183421515][]

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type:SIMPLE

table: g

type: ref

possible\_keys:idx\_user\_group\_uid

key:idx\_user\_group\_uid

key\_len: 4

ref: const

rows: 2

Extra:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type:SIMPLE

table: m

type: ref

possible\_keys:PRIMARY,idx\_group\_message\_gid\_uid

key:idx\_group\_message\_gid\_uid

key\_len: 4

ref:example.g.group\_id

rows: 3

Extra:

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

id: 1

select\_type:SIMPLE

table: c

type: ALL

possible\_keys:NULL

key: NULL

key\_len: NULL

ref: NULL

rows: 96

Extra: Using where; Usingjoin buffer

我们看到不仅仅group\_message\_content表的访问从ref变成了ALL，此外，在最后一行的Extra信息从没有任何内容变成为Using where; Using join buffer，也就是说，对于从ref变成ALL 很容易理解，没有可以使用的索引的索引了嘛，当然得进行全表扫描了，Using where 也是因为变成全表扫描之后，我们需要取得的content 字段只能通过对表中的数据进行where 过滤才能取得，但是后面出现的Using join buffer 是一个啥呢？

实际上，这里的Join 正是利用到了我们在之前“MySQL Server 性能优化”一章中所提到的一个Cache 参数相关的内容，也就是我们通过join\_buffer\_size参数所设置的Join Buffer。

实际上，Join Buffer 只有当我们的Join 类型为ALL（如示例中），index，rang 或者是index\_merge 的时候才能够使用，所以，在我们去掉group\_message\_content表的group\_msg\_id字段的索引之前，由于Join 是ref类型的，所以我们的执行计划中并没有看到有使用Join Buffer。

如果通过类似于下面的图片来展现或许大家会觉得更容易理解一些，如下：

![20150118183509772][]

通过上面的示例，我想大家应该对MySQL 中Nested Join 的实现原理有了一个了解了，也应该清楚MySQL 使用Join Buffer 的方法了。当然，这里并没有涉及到外连接的内容，  实际对于外连接来说，可能存在的区别主要是连接顺序以及组合空值记录方面。

**Join 语句的优化**

在明白了MySQL中Join 的实现原理之后，我们就比较清楚的知道该如何去优化一个一个Join 语句了。

1. 尽可能减少Join 语句中的Nested Loop  的循环总次数；

如何减少NestedLoop 的循环总次数？最有效的办法只有一个，那就是让驱动表的结果集尽可能的小，这也正是在本章第二节中的优化基本原则之一“永远用小结果集驱动大的结果集”。

当然，此优化的前提条件是通过Join 条件对各个表的每次访问的资源消耗差别不是太大。如果访问存在较大的差别的时候（一般都是因为索引的区别），我们就不能简单的通过结果集的大小来判断需要Join 语句的驱动顺序，而是要通过比较循环次数和每次循环所需要的消耗的乘积的大小来得到如何驱动更优化。

2. 优先优化Nested Loop 的内层循环；

内层循环是循环中执行次数最多的，每次循环节约很小的资源，在整个循环中就能节约很大的资源。

3. 保证Join 语句中被驱动表上Join  条件字段已经被索引；

保证被驱动表上Join条件字段已经被索引的目的，正是针对上面两点的考虑，只有让被驱动表的Join 条件字段被索引了，才能保证循环中每次查询都能够消耗较少的资源，这也正是优化内层循环的实际优化方法。

4. 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer 的设置；

当在某些特殊的环境中，我们的Join必须是All，Index，range或者是index\_merge 类型的时候，JoinBuffer 就会派上用场了。在这种情况下，JoinBuffer 的大小将对整个Join 语句的消耗起到非常关键的作用。

[20150118183311368]: /images/20220806/842e7511d5384f038b682d0eed6466f8.png
[20150118183315406]: /images/20220806/5f0b24363f81413499f966c2bf841293.png
[20150118183359515]: /images/20220806/c02e8f97cb2a4ca1ba4d4706350ea39a.png
[20150118183421515]: /images/20220806/0adc6d73451047bfb9967faf01ed28ff.png
[20150118183509772]: /images/20220806/39e60300930246f38c971ff086f973d5.png