【MySql专栏】—— 关联查询join的流程以及优化

一、应不应该使用关联查询？

对于关联查询来说，并不是所有情况下都能使用的，有的公司会直接禁用关联查询，因为使用关联查询后，那么后序在项目升级时，对数据库进行分库分表后，关联查询就没办法在使用，所有代码都需要重构，不利于后期的维护和重构。本篇文章的前提条件是可以使用关联查询，那么在我们使用关联查询的时候需要注意什么？

为了后序的验证，这里创建两个表

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

create table t2 like t1;

其中：表t1中有100条数据，表t2中有1000条数据

二、关联查询使用了索引（Index Nested-Loop Join）

首先我们来看一下，下面这条语句：

select * from t1 left join t2 on t1.a = t2.a;

这条语句的执行流程如下图：

从中可以分析出来表t1作为驱动表，没有走索引，进行了全表扫描，表t2的字段a有索引，走了这个索引。语句执行流程如下：

1.从表t1中读入一行数据T；

2.从数据行T中找出字段a的值，去表t2中进行查询；

3.取出表t2满足的数据行和数据行T组成结果集，返回给客户端；

4.重复1～3步，直到表t1的数据循环结束。

这个语句扫描的行数流程为：

1.驱动表t1做全表扫描，扫描100行；

2.根据拿到数据行T的字段a，去表t2中匹配，因为表t2中字段a创建了索引，这个查询每次是走树搜索，基本扫描一行数据搞定，所以也是一共扫描就是100行；

3.因此总共扫描200行数据。

这个时候我们来对比一下不使用join，分开实现这个语句是什么流程呢：

1.首先使用语句select * from t1 全表扫描100行；

2.根据查询查询出来的结果集，分别执行select * from t2 where a = T.a,扫描100行；

3.总计也是扫描200行，但是与数据库交互了101次比使用join多交互了100次，而且客户端还有对返回的结果集进行封装，肯定是要比使用join的效果差。

如果我们使用的是表t2做驱动表，t1做被驱动表呢？

select * from t2 left join t1 on t1.a = t2.a

这时这个实行流程就是：

1.在表t2上走全表扫描，扫描1000行

2.把查询到的数据行，去表t1做匹配，走树索引，扫描也要1000行

3.这个使用总计就是2000行。很明显扫描的行数增加了。

这个使用可以总计一个计算公式：如果驱动表的行数为N，被驱动表的行数为M，驱动表走全表扫描，被驱动表走的是索引数的查找，那么驱动表在查找一条数据后，在被驱动表上走普通索引a，在根据普通索引上的主键回表查询数据，走一次树搜索的时间复杂度为log2M，回表一次就是2*log2M，驱动表全表扫描，扫描行数为N，那么总扫描行数为N+N*2*log2M，那么可以总结出，驱动表的数据越小，整个过程扫描的行数就越小，因此我们应该使用小表作为驱动表，大表作为被驱动表。

三、关联查询未使用了索引（Simple Nested-Loop Join）

如果我们使用如下语句进行关联查询：

select * from t1 straight_join t2 on (t1.a=t2.b);

因为t2表的字段b没有索引，那么简单语句分析语句执行流程：

1.表t1走全表扫描，扫描100行

2.从t1表中取出字段，到t2去匹配，因为表t2的字段b上没有索引，那么会走全表扫描，这个过程扫描的行数为100*1000，也就是10万行数据；

显然这个结果，对于我们来说是不能接受的，因此mysql针对这个做了优化，我们可以使用explain查看一下语句执行情况：

其中Extra中，使用了Block Nested Loop，那么使用了Block Nested Loop算法后执行流程如下：

1.从表t1中，全表扫描，取出所有数据100条放入join_buffer中，因为是select * 所以是把全部数据放入缓存；

2.因为表t2上字段b没有索引，把表t2拿出来的数据与join_buffer中的数据进行对比，满足join条件的放入结果集中；

因此整个过程，表t1和表t2都是全表所描总计1100行，因为join_buffer中的数据是无须的，因此每次从表t2中拿出一条数据进行比较需要比较100次，因此总比较次数也是100*1000是10次，但是这个比较是在内存中进行的，速度上会快很多，性能上也会更好。

这个使用我们来总结一下，使用什么表做驱动表会更好，如果小表行数为N，大表行数为M，因为没有索引总扫描行数为N+M，在内存中比较的次数为N*M，从中可以看出来，无论谁是驱动表，并不影响其性能。

如果表的数据量很大，join_buffer放不下这么办呢？

首先我们要直到join_buffer的大小是由join_buffer_size设定的，默认大小为256k，如果我们的数据量非常大，那么我们就会进行分段放入join_buffer中，那么上诉语句的执行流程就会是如下流程：

1.扫描表t1，按顺序将数据放入join_buffer中，假设这是只够放入80条数据，join_buffer就满了，继续步骤2；

2.扫描表t2，将t2中的数据取出，与join_buffer中的数据进行比较，满足join条件，作为结果集放回；

3.清空join_buffer的数据；

4.继续扫描表t1，将剩余的20行数据放入join_buffer中，继续执行步骤2；

针对上诉步骤我们再来分析这个执行过程的流程：放入join_buffer的数据为N行，领一个表为M行，放入join_buffer的次数为K，这个K是和N有关系的，N越大，那么K越大，那么K 约定与x乘以N，x的取之为0～1；

1.那么扫描的总行数为N + xN*M；

2.比较的总次数为N*M

所以这个时候x的值越小，扫描的行数也就越小，而x的大小又是和N相关，所以将小表作为驱动表更合适。

四、是使用left join 还是 right join 还是 inner join呢？

a left join b:表示a是驱动表，b是被驱动表；

a right join b :表示b是驱动表，a是被驱动表；

a inner join b：mysql会自动优化将a b两个表，小表作为驱动表，大表作为被驱动表，因此在开发中，当不确定那个表是小表时，尽量使用inner join来关联两张表吧。