【Mysql】表连接的原理

通过《初探表连接的原理》我们重新认识了下表的连接、内连接、外连接等概念。
下面深入连接的原理以及连接的算法实现。

嵌套循环连接

表进行内连接的时候，会根据查询成本选择一个优先访问的表作为驱动表（外连接，则是指定了驱动表），然后根据驱动表的查询结果再去被驱动表中查询，对驱动表只会进行一次查询，而对被驱动表的查询则是根据驱动表中查询的结果数，进行循环查询。
这就是嵌套循环中的循环操作，那嵌套呢？
我们也会有多表连接的情况，在进行多表连接的时候，比如t1,t2,t3进行连接，先从驱动表 t1中查询出符合条件的数据，然后在循环遍历查询t2，循环的次数等于t1表中查询出的结果数，然后再将t1和t2连表查询的结果作为新的驱动表,t3作为新的被驱动表，根据t1和t2的结果数，循环查询t3。

举个🌰

我们还是把昨天的表结构拿过来

# 创建两个表t1,t2
CREATE TABLE t1 (m1 int, n1 char(1));
CREATE TABLE t2 (m2 int, n2 char(1));
CREATE TABLE t3 (m3 int, n3 char(1));
# 向t1,t2中插入几条数据
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');
INSERT INTO t3 VALUES(4, 'd'), (5, 'e'), (6, 'f');

#表结构
mysql> select * from t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from t3;
+------+------+
| m3   | n3   |
+------+------+
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
3 rows in set (0.00 sec)

执行连接查询

mysql> select * from t1,t2,t3 where t1.m1>1 and t1.m1=t2.m2 and t2.n2<'d' and t3.m3>t2.m2;
+------+------+------+------+------+------+
| m1   | n1   | m2   | n2   | m3   | n3   |
+------+------+------+------+------+------+
|    3 | c    |    3 | c    |    4 | d    |
|    2 | b    |    2 | b    |    4 | d    |
|    3 | c    |    3 | c    |    5 | e    |
|    2 | b    |    2 | b    |    5 | e    |
|    3 | c    |    3 | c    |    6 | f    |
|    2 | b    |    2 | b    |    6 | f    |
+------+------+------+------+------+------+
6 rows in set (0.00 sec)

因为我们没有建立索引，所以两个表的访问方式都是all 全表扫描，我们假定t1表作为驱动表，t2表作为被驱动表。查询的过程大致如下：

for each row in t1 {   #此处表示遍历满足对t1单表查询结果集中的每一条记录
    
    for each row in t2 {   #此处表示对于某条t1表的记录来说，遍历满足对t2单表查询结果集中的每一条记录
    
        for each row in t3 {   #此处表示对于某条t1和t2表的记录组合来说，对t3表进行单表查询
            if row satisfies join conditions, send to client
        }
    }
}

这就是嵌套循环连接，也是连接算法中最简单的实现，当然也是很笨拙的一种实现方式。
试想如果我们的连接的表中有十万百万千万条数据，那么循环嵌套连接的算法会遍历多少条数据？又谈什么性能呢？所以有下面这种处理

使用索引加速连接查询速度

在上面的嵌套循环查询中，在循环查询被驱动表的时候，每一次的查询可以看作对被驱动表的一次查询。比如：

select * from t1,t2 where t1.m1>1 and t1.m1=t2.m2;

#先在t1表中查询出t1.m1>1的数据
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
# 然后在查询t2被驱动表时，可以理解成以下过程
# 第一次查询
select * from t2 where t2.m2=2;
# 第二次查询
select * from t2 where t2.m2=3;

如果t2表恰巧给m2建立了索引，那么以上对于t2表的访问方式就不是all全表扫描而变成ref级别，如果m2是唯一二级索引或者主键索引，那么访问级别就可以达到const（在连表查询中 叫做eq_ref,在对单表的访问中使用主键索引或者唯一二级索引叫做 const);
如果增加范围查询条件呢？

select * from t1,t2 where t1.m1>1 and t1.m1=t2.m2 and t2.n2<'f';

主要的查询步骤和上面的差不多，只是在对t2表进行查询的时候，多了t2.n2<'f’的查询条件，如果n2列也建立索引那么访问级别就可能达到range,即使n2没有建立索引或者索引未生效，那么此时的访问级别也是eq_ref+回表查询，这样的查询方式比all全表扫描要快出不少

所以，我们在做连表查询的时候要尽量使用索引，不要select * ，这样可以加快我们连表查询的速度；

基于块的嵌套循环查询

在进行表连接查询的时候，会经历循环嵌套查询，在实际的查询过程中，会将扫描表加载到内存中，然后在内存中查找符合条件的记录。如果我们连接的表有上百万千万的数据，那么这种做法很有可能会导致内存不足，所以，mysql设计有一个join buffer的概念：

join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价

和嵌套循环连接有什么区别呢？
循环嵌套连接从驱动表中查询符合条件的结果集后，每次访问被驱动表，被驱动表的数据会加载到内存中，然后被驱动表中的每一条记录只会与驱动表中的一条记录进行比较，然后就会在内存中清除，重复此过程。

如果join buffer 的空间足够大，能容纳从驱动表中查询的所有结果集，那么只需要访问一次被驱动表就能完成数据筛选的过程了。

mysql中提供了相应的参数，以便于我们设置join buffer的大小，join_buffer_size 此变量的默认大小为256k。

有一点需要我们格外注意，不是驱动表中所有的数据都会被加载进join buffer中的，只有被查询的列，所以我们在做查询的时候，不要使用*查询所有的列，要指定要查询的列的字段。