MySQL-33：join语句原理

小灰灰 2022-12-29 14:07 170阅读 0赞

现在有表t，r，表t有a，b字段，表r有c，d字段，a字段和c字段有建立索引，表t有100000行数据，表r有6行数据。

我们先来普通的join语句，在join语句中，存在驱动表和被驱动表，MySQL会已小表已用来驱动大表，小表就是数据量较小的那个。

例如执行如下语句，我们就会看来是用表r驱动表t的

EXPLAIN select * from t join r on r.c = t.a;

看到的情况如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

表r走全表扫描，表t走索引c，我们就可以想到join的执行流程如下：

1.  表r走全表扫描，从表r中取到一行符合条件的数据。
2.  拿到这行数据去表t中走索引c。
3.  查到符合条件的数据，则加入结果集。
4.  重复上述步骤，直到走完表r。

在这个过程中，对驱动表r走全表扫描，也就是扫描了6行；对被驱动表t走索引c，每一个数据都只需要扫描一行，也就是6行，所以整个流程扫描的行数为12行。

join是用小表驱动大表，那有指定驱动表的嘛？有的，straight\_join

#### 33.1 straight\_join ####

通过如下语句，我们就能指定，前表为驱动表，后表为被驱动表。

select * from t straight_join r on r.c = t.a;

通过下图，我们能明显看出，表t就是驱动表，走的全表扫描，一次join流程的扫描行数达到了100006行之多，所以如何如何选择驱动也是一个问题。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

**如何选择驱动表：**

首先，明白一点，驱动表走全表扫描，被驱动表走索引。

先假设驱动表有N行数据，被驱动表有M行数据，那它扫描的总行数为 N + N \* 2 \* log2M，因为 每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M ，走索引a之后再进行回表，需要需要乘以2。

从上述公式能很容易看出，驱动表的数据行数大小起决定性的作用。

\*\*在不用join情况下：\*\*我们还是从小表出发

1.  通过`select * from r`来获取数据集合。
2.  扫描数据集合，依次一行一行取出去表t中进行配对。
3.  它的扫描总行数为18行，也就是比之前用小表驱动大表的方式多了6次扫描行数。

现在我们，来看下，用不上索引的情况。

不过，这个需要介绍下，MySQL当可以走被驱动表索引时，用的算法为Simple Nested-Loop Join ，没用索引时用的算法为 Block Nested-Loop Join ，下面，我们就来说他。

#### 33.2 Block Nested-Loop Join ####

![在这里插入图片描述][20201222234957249.jpg_pic_center]

上图显示了，表t使用join buffer。

由于被驱动表是没有可用索引的，它的算法流程如下：

*  把表r的数据放入名为join\_buffer的内存中。
 *  扫描表t，将表t的每一行取出来对比，满足条件的行返回加入结果集。
 *  它的扫描为6+10000行。

但是有个问题，上述的情况是建立在join\_buffer放的下表r的情况，那如何放不下？

MySQL提供参数`join_buffer_size`来控制join\_buffer大小，默认大小为256K。如果放不下整张表，就将表进行分段放置，它会在第一轮对比结束后，清空join\_buffer，再加入新的表r数据。

现在我们来假设下，驱动表的数据行数是 N行，完全加入join\_buffer需要分 K 段，被驱动表的数据行数是 M行。

那我们扫描的行数就是N+ceil(N/k)\*M，我们可以看出，影响的主要因素是K，也就join\_buffer的大小，它越小，就可以较少对被驱动表的扫描次数。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/b21f67acada64f8c9bb27e3ea70872c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/527e7e5f9557462cb811f36da2ce62ae.png
[20201222234957249.jpg_pic_center]: /images/20221120/0c802f9259c34198b649f99136c621e6.png