MySQL-33:join语句原理 小灰灰 2022-12-29 14:07 126阅读 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
还没有评论,来说两句吧...