MySQL表连接的原理

1. 回顾表连接

1.1. 驱动表和被驱动表

先来看看驱动表和被驱动表的概念

驱动表就是表在连接MySQL底层先查询的那张表。被驱动表根据驱动表找到的结果集“挨个”去被驱动表中匹配相关记录。

通常来说驱动表中有几个记录组成的结果集，就得查找被驱动表几次。

inner join不分驱动表和被驱动表，left join 左边的表是驱动表，right join 右边的表是驱动表。

1.2. 连接

MySQL中连接分为三种：内连接、左(外)连接、右(外)连接。

假设现在有两张表t1和t2，两张表都有两个字段id,name。

左(外)连接：

// 左连接 左边的表为驱动表，右边的表为被驱动表。
// 驱动表的数据会全部作为结果集返回，如果驱动表中有数据 被驱动表没数据那么被驱动表中字段值为null
select * from t1 left join t2 on t1.id=t2.id

右(外)连接：

// 右连接 左边的表为被驱动表，右边的表为驱动表。
// 驱动表的数据会全部作为结果集返回，如果驱动表中有数据 被驱动表没数据那么被驱动表中字段值为null
select * from t1 right join t2 on t1.id=t2.id

内连接

// 不加条件,以下三条语句是等价的。
select * from t1,t2;
select * from t1 inner join t2;
select * from t1 join t2;
// 内连接条件查询，on和where是等价的：因为内连接不分驱动表和被驱动表，所以在内连接中mysql会把on看作where。
select * from t1 inner join t2 on t1.id=3;
select * from t1 inner join t2 where t1.m1=3;

回顾完三种连接的写法和一些基本概念之后，下面分析一下连接的原理。

2. 原理分析

2.1. 嵌套循环查询

一般查询遵循以下步骤：

1. 查询一次驱动表，查询符合条件的驱动表数据
2. 根据step 1中查出来的数据再去查被驱动表，查询次数=驱动表数据条数
3. 如果是多表连接，那就step 2查询的数据作为新的驱动表数据再去查后面的表

整个过程就是一个嵌套的循环查询的过程。

这个过程的优化点就是被驱动表的查询，可以给被驱动表的条件字段加索引来优化查询速度。

2.2. 基于块的嵌套循环查询

join buffer

在嵌套循环连接查询的时候，被驱动表会被查询多次，如果数据特别多，内存中可能会放不下全部的数据，就会出现扫描后边的数据内存不够就会把前面的数据释放掉，那这样再次需要扫描前面的数据时又要从磁盘重新加载，这个过程会循环多次，那这个I/O消耗就会比较大。

所以MySQL中就有join buffer这么一个概念，join buffer是连接查询前会开辟的一个固定大小的内存区域，大小由join_buffer_size配置。这个区域会装载驱动表的查询结果，然后扫描被驱动表的记录，每条被驱动表的记录一次性和join buffer中全部的记录进行条件匹配。这样被驱动表中的一条记录理论上来说只需要加载一次就会跟驱动表的数据匹配完成，不需要加载多次。