连接查询

连接查询

有表t1和表t2:

mysql>SELECT * FROM t1;

mysql>SELECT * FROM t2;

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加⼊结果集并返回给⽤户。所以把t1和t2两个表连接起来的过程如下图所示：

连接查询的结果集中包含⼀个表中的每⼀条记录与另⼀个表中的每⼀条记录相互匹配的组合，像这样的结果集就可以称之为笛卡尔积。因为表t1中有3条记录，表t2中也有3条记录，所以这两个表连接之后的笛卡尔积就有3×3=9⾏记录。

有过滤条件的连接查询

SELECT * FROM t1, t2 WHERE t1.m1 > 1 
AND t1.m1 = t2.m2 AND t2.n2 < 'd';

在这个查询中我们指明了这三个过滤条件：

• t1.m1 > 1
• t1.m1 = t2.m2
• t2.n2 < 'd'

⼤致执⾏过程如下：

• 1、 ⾸先确定第⼀个需要查询的表，这个表称之为驱动表。

此处假设使⽤t1作为驱动表，那么就需要到t1表中找满⾜t1.m1 > 1的记录，因为表中的数据太少，没在表上建⽴⼆级索引，所以此处查询t1表的访问⽅法就设定为all吧，也就是采⽤全表扫描的⽅式执⾏单表查询。

• 2、 针对上⼀步骤中从驱动表产⽣的结果集中的每⼀条记录，分别需要到t2表中查找匹配的记录。

因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。上⼀步骤从驱动表中得到了2条记录，所以需要查询2次t2表。此时涉及两个表的列的过滤条件t1.m1 = t2.m2就派上⽤场了：

• 当t1.m1 = 2时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2，所以此时t2表相当于有了t2.m2 = 2、t2.n2 < 'd'这两个过滤条件，然后到t2表中执⾏单表查询。
• 当t1.m1 = 3时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执⾏单表查询。

所以整个连接查询的执⾏过程就如下图所示：

外连接与内连接

• WHERE⼦句中的过滤条件
  WHERE⼦句中的过滤条件，不论是内连接还是外连接，凡是不符合WHERE⼦句中的过滤条件的记录都不会被加⼊最后的结果集。

• ON⼦句中的过滤条件
  对于外连接的驱动表的记录来说，如果⽆法在被驱动表中找到匹配ON⼦句中的过滤条件的记录，那么该记录仍然会被加⼊到结果集中，对应的被驱动表记录的各个字段使⽤NULL值填充。

  内连接中的WHERE⼦句和ON⼦句是等价的。

连接原理

嵌套循环连接（Nested-Loop Join）

驱动表只访问⼀次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执⾏单表查询后的结果集中的记录条数

• 步骤1：选取驱动表，使⽤与驱动表相关的过滤条件，选取代价最低的单表访问⽅法来执⾏对驱动表的单表查询。

• 步骤2：对上⼀步骤中查询驱动表得到的结果集中每⼀条记录，都分别到被驱动表中查找匹配的记录。

相当于2个嵌套for循环

如果有3个表进⾏连接的话，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上边过程，⽤伪代码表示⼀下这个过程就是这样：

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
       }
   }
}

基于块的嵌套循环连接（Block Nested-Loop Join）

采⽤嵌套连接查询，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每⼀条记录只会和驱动表结果集的⼀条记录做匹配，之后就会被从内存中清除掉。然后再从驱动表结果集中拿出另⼀条记录，再⼀次把被驱动表的记录加载到内存中⼀遍，周⽽复始，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多⽽且不能使⽤索引进⾏访问，这个I/O代价就⾮常⼤了，所以我们得想办法：尽量减少访问被驱动表的次数。

所以MySQL提出了⼀个join buffer的概念，join buffer就是执⾏连接查询前申请的⼀块固定⼤⼩的内存，先把若⼲条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每⼀条被驱动表的记录⼀次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价。使⽤join buffer的过程如下图所示：

join buffer的⼤⼩是可以通过启动参数或者系统变量join_buffer_size进⾏配置，默认⼤⼩为262144字节（也就是256KB）。
当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率⾼的索引，如果实在不能使⽤索引，并且⾃⼰的机器的内存也⽐较⼤可以尝试调⼤join_buffer_size的值来对连接查询进⾏优化。

驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关⼼的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢哈。