MySQL两个表的亲密接触-连接查询的原理( 二 ) _MySQL

• WHERE 子句中的过滤条件：不论是内连接还是外连接凡是不符合 WHERE 子句中过滤条件的记录都不会被加入到最后的结果集。
• ON 子句中的过滤条件：对于外连接的驱动表中的记录来说，如果无法在被驱动表中找到匹配 ON 子句中过滤条件的记录那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL 值填充。
所以上述的需求我们可以左查询这样来做：
select s1.number, s1.name, s1.major, s2.subject, s2.scorefrom student as s1 left join score as s2 on s1.number = s2.number;语法：
#左连接select * from t1 left join t2 on '连接条件' where '普通过滤条件'#右连接select * from t1 right join t2 on '连接条件' where '普通过滤条件'内连接的另一种写法，也是常用写法
select s1.number, s1.name, s1.major, s2.subject, s2.scorefrom student as s1 inner join score as s2 where s1.number = s2.number;语法：
select * from t1 inner join t2 on '连接条件' where '过滤条件'连接原理上述说了这么多，知识简单回顾一下连接，左连接，右连接这些概念。接下来我们重点说一下 MySQL 采用了什么样的算法来进行表与表之前的连接。
Nested-Loop Join （嵌套循环连接) NLJ前面我们已经介绍过了执行连接查询的大致步骤了，我们再来简单回顾一下

步骤1：选取驱动表，使用相关的过滤条件，选取代价最低的单表访问方法来执行访问。
步骤2：对步骤1中查询到的驱动表结果中的每一条记录，都分别在被驱动表中匹配符合条件的记录。
如果有三个表，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了驱动表，重复上述的过程。

整个过程就像是一个嵌套循环，所以这种连接方式称为嵌套循环连接，这是最简单也是最笨的一种连接查询算法。大致处理过程如下：

for each row in t1 matching range {for each row in t2 matching reference key {for each row in t3 {if row satisfies join conditions, send to client}}}

需要注意的是对于获套循环连接算法法来说，每当我们从驱动表中得到了一条记录时，就根据这条记录立时到被驱动表中查询一次，如果得到了匹配的记录，就把组合后的记录发送给客户端，然后再到驱动表中获取下一条记录。这个过程将重复进行。
有什么方式可以优化吗使用索引加快连接速度这个是我们比较熟悉的方式，也是相对来说最有用的方式，在被驱动表上创建合适的索引，只返回必要的字段等都可以起到一些优化的作用。
Block Nested-Loop Join（块嵌套循环连接）BNL每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取出一条与其匹配，匹配结束后清楚内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录加载到内存匹配，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读这个表好多次，这个IO的代价就非常大了。所以我们得想办法，尽量减少被驱动表的访问次数，于是就出现了下面这种方式。
不再是逐条获取驱动表的数据，而是一块一块的获取，引入join buffer 缓冲区，将驱动表join 相关的部分数据列（大小受join buffer的限制）缓存到 join buffer中，然后开始扫描被驱动表，被驱动表的每一条记录一次性和join buffer中所有的驱动表记录进行匹配（内存中操作）。将简单嵌套循环中的多次比较合并成一次，降低了备驱动表的访问频率。
这里缓存的不只是关联表的列， select后面的列也会缓存起来。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。
join_buffer_size的最大值在32为系统中可以申请4G ，在64为操作系统中可以申请大于4G的空间。