MySQL表连接之驱动表与被驱动表

众所周知， MySQL的驱动表与被驱动表是优化器自动优化选择的结果 （与表连接的前后顺序等无关），我们可以用explain执行计划来知晓：

如上所示，前面一行t1是驱动表，后面一行t2是被驱动表。那么驱动表与被驱动表的选择是否有规律可循呢？下面是百度搜索两个主流的博文对驱动表与被驱动表的阐释：

1. MySQL连接查询驱动表被驱动表以及性能优化 - 阿伟~ - 博客园 博文A 主要结论：

2. mysql驱动表与被驱动表及join优化_java小小小黑的博客-CSDN博客_mysql驱动表和被驱动表 博文B 其主要结论：

两个帖子的结论是都差不多，而且还给出了例子来佐证。那么网上的结论是否权威？是否有普遍性？是否存在缺陷？

让我们来一起打破砂锅问到底。下面有两张表结构一模一样的表t1,t2：其中t1 100条数据，t2 1000条数据；t1（t2）结构如下：

按照上面博文的结论，left join左边是t2表，应该是驱动表。我们查看下结果：

与 博文B 中观点1相违背（同理观点2也违背），与实际不符，但究竟这是为什么呢？

下面发一张MySQL的执行过程（来源于《MySQL实战45讲》中01讲【一条SQL查询语句是如何执行的】）

so die si ne，原来sql执行的过程是这样呀。等等，不对，这跟刚才SQL又有什么关系，上面left join中t2表还是左边的呀。

我们知道MySQL高版本的性能越来越好，它是不断进行优化迭代的。远古的mysql版本可能还需要人工把小表放在前面，大表放在后面等这些需要人工调优的经验早就已经被解决了。也就是说我们写的语句，MySQL为了追求更好的效率，它在执行器执行前已经帮我们优化了。那么实际优化后的sql如何查看呢？用show warning命令：

其中Message就是优化后实际执行的sql语句，格式化后如下：

优化后left join左连接变成了内连接(inner) join。所以用优化后的sql看，表t1是小表所以作为驱动表，与实际结果相符。

left join 竟然优化成了join,太神奇了，但这是为什么呢？原因在于mysql中null与任何值做等值或者不等值比较的时候都是null，即使是select null=null 也是null。这样where 条件t1.a=t2.a查询条件不会包含t2.a为NULL的行，实际效果其实跟join一样，被优化器智能的优化了。

我们直接看执行计划看实际结果吧：

结果显示t2是驱动表，t1是被驱动表。t2是1000条数据按理说是大表应该是被驱动表，与 博文A ， 博文B 的结论又不一致了。

《MySQL实战45讲》中34讲【到底可不可以使用join】已经讲的很透彻了，很深入了，我就不在这里献丑了。啰嗦几句大概就是驱动表是全表扫描不走索引，所以选被驱动表t1可以走索引，不会全表扫描，减少IO次数，性能高。里面对大表小表的总结，简直是精髓，特意在此再次着重强调：

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

按照上面分析，我们先独立思考下MySQL会选择哪张表作为驱动表呢？

表t1,t2在字段a上都有索引不会全表扫描，其中t1.a=5条件过滤后只有一条，很显然嘛，t1数据量少是小表，肯定是驱动表，错不了，再说了前面的红色粗体已经强调了，不会有错的。

有冇搞错？事实又被打脸了。还记得在开篇我们说过的mysql优化器会对sql语句进行优化的吗？下面我们看下执行计划与优化的sql语句：

格式化后的优化SQL如下：

优化后两表t1,t2都走索引，并且都只有一条结果返回，因此都只会扫描一行，数据量一样，所以谁在前面谁就是驱动表，也就是上面sql中表t2。一切都释然，豁然开通！

回头再仔细想想，高，实在是高！仔细深思之后MySQL优化后的句子真让人猛拍大腿。高明之处在于：

1. 本来join连接是个M*N的嵌套循环，优化后变成了M+N的判断，两表不再嵌套判断了。

2. 优化后，两表没有多大必然联系，只需把两表的结果集拼接即可，互不干扰。如果mysql未来可以多线程查询，岂不十分快哉！

小伙伴们还记得我们在上一章 MySQL索引初探 中编码类型不一致发生隐式转换时有时候走索引，有时候索引又失效的问题吗？下面我们选取有代表性的一条记录来分析：

其中表demo_test总共有640条数据，demo_test_ass有3条数据。显然经过过滤条件t.rid>1完成后demo_test_ass数据量小，应该作为驱动表。虽然test.c_utf8mb4 = t.c2两字段连接中发生了t.c2字段发生隐式转换，但是实际上并不影响被驱动表test上的c_utf8mb4索引。

好了，本章到此结束，让我们一起 总结一下MySQL驱动表与被驱动表的选取原则 ：

หน ง 同等条件，优先选取有索引的表作为被驱动表。 在此介绍一下什么叫同等条件，比如上面的②中的语句。 两表没有其他额外的过滤条件，因此选关联字段有索引的t1作为被驱动表。但是如果加了条件(and t1.id=3)，此时t1数据量少，就选取了t2作为被驱动表。

สอง MySQL选择驱动表与被驱动表是基于优化器优化后的，小表是驱动表，大表是被驱动表。 基于优化器优化后开篇的 博文A与B 结论成立。

当然这都是我一家之言，并不是官方结论，目前暂未找到官方确切对于驱动表与被驱动表的解释，请大家踊跃拍砖！

mysql中的表连接分为三种

1. 左连接 left join

左连接以左表为基础，查询出左表所有数据并且去匹配右表的数据，如果右表没

有数据，则为空

2. 右连接 right join

右连接以右表为基础，查询出右表所有数据并且去匹配左表的数据，如果左表没

有数据，则为空

3. 内连接 inner join

内连接会把左右表匹配的数据查询出来，不存在的数据直接忽略

驱动表与被驱动表的概念

驱动表是表连接中的基础表，也就是通过驱动表的数据结果集作为循环基础数据，然后一条一条的通过这个结果集的数据作为过滤条件到被驱动表中查询数据，然后合并

驱动与被驱动

左连接中 左表是驱动表，右表是被驱动表

右连接中 右表是驱动表，左表是被驱动表

内连接中 表数据量较小的表会由mysql自动选择作为驱动表去驱动大表

有一个重点是，如果where条件存在的话 mysql会根据where实际条件进行驱动表的选择

sql优化中，一个比较重要的点就是要用小表驱动大表

原因

mysql表关联的算法，是通过驱动表去循环被驱动表，比如说，20w的大表和200条的小表，如果大表驱动，那么是20w条记录外循环，内循环200条去连接查找，需要通过20w次连接，如果小表驱动，那么是200条记录外循环，内循环20w条去连接查找，只需要通过200次连接就可以了，并且驱动表是不会使用索引的

首先，MySql join连接可以连接两张或更多的数据表，但是并没有谁是“驱动表”之说。

Join分为"inner join"内连接和"outer join"外连接两种形式，外连接还可以进一步分为左连接和右连接。

内连接（inner join）只输出被连接的表它们的关联字段拥有交集的那些记录行，没交集的记录行将被剔除掉。

外连接（left join左连接/right join右连接）:

"left join" 输出左表所有的记录行和右表之间关联字段拥有交集的记录行，对于右表找不到对应记录行的，数据库引擎会将空值Null赋值到右表的各个字段里。

"right join"的情况则于"left join" 刚好相反。

"inner join"——只输出表之间存在交集的那些记录行；

"left join" ——输出左表所有的记录行以及右表与之有交集的记录行，右表找不到对应

记录的，数据库引擎则将Null赋值到右表的各个字段；

"right join"——输出右表所有的记录行以及左表与之有交集的记录行，左表找不到对应

记录的，数据库引擎则将Null赋值到左表的各个字段；

---