mysql 连接查询原理

在普通连接查询过程中

首先确认第一个需要查询的表，称为驱动表，记为 t1

从驱动表中获取到每一条记录，都需要到另外一张表中（记为t2）查找匹配记录

所谓匹配的记录，是指符合过滤条件的记录。

不是所有满足条件的驱动表记录先查询出来放到一个地方，然后再去被驱动表查询的，而是每获得一条驱动表记录，立即到被驱动表中寻找匹配的记录。

根据选取的驱动表不同，外连接分为两种

• 左外连接，选择左侧的表作为驱动表

• 右外连接，选择右侧的表作为驱动表

过滤条件在不同地方有不同语义

where 子句中的过滤条件

就是平常见的那种。无论是内连接还是外连接，只要不符合 where 子句中过滤条件的记录都不会被加入到最后的结果集。

on 子句中的过滤条件

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 on 子句中过滤条件的记录，那么该驱动表记录仍然会被加入到结果集中，对应被驱动表记录的各个字段使用 null 值填充。

对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中有多少条记录。

对于内连接来说，选取哪个表为驱动表都没关系。

外连接的驱动表是固定的，左连接的驱动表是左边的那个表，右连接的驱动表就是右边的那个表。

查询的大致过程

1. 选取驱动表，使用于驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。

2. 对得到的结果集中的每一条记录，分别到驱动表中查找匹配的记录。

如果有3个表进行连接，那么步骤2得到的结果集就像是新的驱动表，然后第3个表就成了被驱动表，重复过程。

这个过程就像一个嵌套循环，这种“驱动表只访问一次，被驱动表可能访问多次且访问次数取决于对驱动表执行单表查询后结果集中有多少条记录”的连接执行方式被称为嵌套循环连接（Nested-Loop Join），是最简单也是最笨的一种连接查询算法。

对于嵌套循环连接算法来说，从驱动表中得到了一条记录时，就根据这条记录立即到被驱动表中查询一次。如果得到了匹配的记录，就把组合后的记录发送给 client，再到驱动表中获取下一条记录，重复过程。如果这个被驱动表中的数据特别多而且不能使用索引进行访问，相当于从磁盘上读数据，这个I/O代价太大，所以需要减少被驱动表的访问次数。

是否可以把被驱动表中的记录加载到内存时，一次性与驱动表中的多条记录进行匹配呢？这样就可以大大减少从磁盘上读取被驱动表的代价了。

Block Nested-Loop Join

mysql 开发人员设计了一个名为 join buffer （连接缓冲区）的概念。在执行连接查询前申请一块固定大小的内存。先把驱动表中的记录存在这个连接缓冲区中，然后扫描被驱动表，每一条被驱动表中的记录一次性与连接缓冲区中的多条记录进行匹配。由于匹配过程都是在内存中弯沉给的，这样可以减少被驱动表的I/O代价。

最好的情况是连接缓冲区足够大，能容纳驱动表结果集中所有记录，只需要访问一次被驱动表就可以就可以完成连接查询了。

连接缓冲区中不会存放驱动表中记录所有列，只有查询列表中的列和过滤条件中的列才会被放到连接缓冲区中。最好不要把*作为查询列表，只需要把关心的列放到查询列表就可以了，可以在连接缓冲区中放更多记录。

innodb 是由 innodb_buffer_pool_size 控制。

https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html