MySQL表连接原理

一. 前言

表的连接查询是我们在业务中经常用到的，此文用于介绍其底层原理，会涉及到单表的查询底层原理，如果不清楚单表相关原理，请移步至我的上一篇文章(https://editor.csdn.net/md/?articleId=140765158)。

二. 连接分类

基本分为两类，一类是内连接，一类是外连接，而外连接又分为左外，右外。
内连接语法：
SELECT * FROM t1 , t2;
SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2 ;
SELECT * FROM t1 CROSS JOIN t2 ;

左外连接语法：
SELECT * FROM T1 LEFT JOIN T2 ON 连接条件；

右外连接语法：
SELECT * FROM T1 RIGHT JOIN T2 ON 连接条件；

三. ON与WHERE的区别

WHERE在不论是外连接还是内连接的情况下，只要是不符合WHERE子句中的过滤条件的记录都不会被加入到最后的结果集中。
而ON在外连接中，如果在被连接的表中无法匹配到ON子句中的过滤条件的记录时，被连接表的记录依然会以NULL的形式加入到结果集中。但是在内连接中，ON与WHERE的作用相同，不符合他们子句中的过滤条件时，都不会被加入到最后的结果集中。也就是说内连接中的ON与WHERE是等价的，所以内连接中不要求强制写明ON子句。

四. 表连接的原理

首先无论是内连接还是外连接，都会有驱动表和被驱动表两种，如果是内连接，那么确定第一个需要查询的表，这个表就是驱动表，例如SELECT * FROM t1 INNER JOIN t2 ON t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < ‘d’；此查询语句中的驱动表就是t1，被驱动表就是t2,。而在左外连接中，选取左侧的表为驱动表，右侧的表为被驱动表。右外连接中，选取右侧的表为驱动表，左侧的表为被驱动表。

那么驱动表和被驱动表有什么区别呢？

连接查询时，会根据过滤条件先去驱动表中查询数据，当然这个查询就是单表查询了，MySQL会选择成本最低的访问方式（const，ref，ref-or-null，index，range，all,索引合并），从这几种访问方式中估算出性价比最好的方式进行查询。而后对查询后的驱动表的结果集中的每一条记录，都分别到被驱动表中查询匹配记录。
这个过程就像是一个嵌套循环，所以这种“驱动表只访问一次，但被驱动表却可能访问多次，且访问次数取决于对驱动表执行单表查询后的结果集中有多少条记录”的连接执行方式称为嵌套循环连接（Nested-Loop Join）。
值得注意的是，对于嵌套循环连接算法来说，每当我们从驱动表中得到了一条记录时，就根据这条记录立即到被驱动表中查询一次。如果得到匹配的记录，就把组合后的记录发送给客户端，然后再到驱动表中获取下一条记录，这个过程将重复进行。

如何提升被驱动表的查询的速度

1. 适当的给被驱动表添加索引。
2. 基于块的嵌套循环连接，Join Buffer连接缓冲区，也就是在执行连接查询前申请一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个Join Buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性地与Join Buffer中的多条驱动表记录进行匹配。由于匹配的过程的都是在内存中华完成的，所以这样可以显著减少被驱动表的IO代价。

本文部分摘抄来自《MySQL是怎样运行的》——小孩子4919