MySQL 外连接和内连接的查询优化怎么做？

目录

1. 表连接方式的分类和需要注意的细节

2. 表连接时底层做了什么事？

3. 左外连接优化方案

4. 内连接优化方案

---

1. 表连接方式的分类和需要注意的细节

多表连接查询，大体上可以分为内连接与外连接。

内连接的意思就是把两个表有关联的部分都取出来，不分主表和次表，在连接时从我们的角度来说是不分谁是驱动表谁是被驱动表，但 MySQL 的查询优化器底层会做一个初步计算，计算出谁作为驱动表效率更高；

外连接则又分为左外连接，右外连接，满外连接(全外连接)。左外连接和右外连接中是分主表和此表的，所以本篇文章中点来说左外连接与右外连接的优化策略。

左外连接中左表是主表，也就是驱动表，右表作为从表，也就是被驱动表；语法为"表A LEFT JOIN 表B ON 查询条件"；

右外连接中右表是主表，作为驱动表，右表是从表，作为被驱动表；语法为"表A RIGHT JOIN 表B ON 查询条件"；

2. 表连接时底层做了什么事？

在没有任何索引的情况下，当两张表在进行连接查询操作的时候，实际上底层做的第一件事就是拿出驱动表一条记录与被驱动表的所有记录去做匹配，看是否满足条件，匹配完毕之后，再拿出驱动表的第二条记录与被驱动表的所有记录去做匹配，看是否有满足条件的记录，以此类推，直到驱动表所有记录均与被驱动表的所有记录进行过匹配并得出了满足条件的结果，两张表的连接查询的第一步连接操作就完成了。大致过程如下图所示

3. 左外连接优化方案

（1）小表驱动大表

通过上面表连接时进行匹配可以看出，两个表在进行连接查询时，驱动次数与驱动表的数据量有关，A表的数据量越少，与B表进行驱动的次数越少，所以第一种优化左外连接的方法就是将数据量小的表作为驱动表，让数据量小的表去与数据量大的表进行匹配，就可以减少驱动次数；

（2）给被驱动表的匹配字段添加索引

如下是 employee 员工表和 department 部门表，部门表的主键 部门id 

SELECT e.employee_id,d.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

驱动表与被驱动表进行匹配时，连接条件是 查询条件字段 = 某个值。本例子中就是 员工表中的部门 id = 部门表中的部门 id。

没有给被驱动表的匹配字段添加索引：取出驱动表的一条数据，确定查询字段为 id = 12，那么与被驱动表匹配查询时，就是挨个将被驱动表中的每条数据取出做判断，看被驱动表的 id 是否为12，这其实就是一个全表扫描的过程。假设 employee 表中有20条数据，deparement 部门表中有30条数据，最坏的情况需要匹配 20 * 30 = 600 次，最好的情况是每次取出被驱动表第一条数据恰好能匹配到，只需要匹配20次。

用时间复杂度来表示就是驱动表时间复杂度O(n) * 被驱动表时间复杂度O(n) = O(n^2)；索引没有索引时两表连接查询的时间复杂度为O(n^2)，三表连接查询就是O(n^3)，随着表数量增多，时间复杂度指数级增长。

给被驱动表的匹配字段添加了索引：接着刚才的说，如果被驱动表中给 id 字段添加了索引，那么被驱动表就可以精准查找 id = 12 的这条记录是否存在，查询不到则说明没有返回空，这样就不需要对被驱动表做全表扫描，省去了大量的时间。这里被驱动表的查询字段如果是主键索引，那么B+树的叶子节点就存有完整的数据，如果被驱动表的查询字段是非主键索引，那么就需要进行一次徽标查询操作，每次驱动只需要在 department 部门被驱动表中进行一次查询，效率得到了很大的提升。

用时间复杂度来表示驱动表扫描数据时间复杂度O(n)，被驱动表主键索引时时间复杂度为O(logn)，非主键索引回表操作时间复杂度为2O(logn)，因此有索引时时间复杂度为O(nlogn)，比没有索引的O(n^2)降低了很多；

（3） 若有WHERE查询条件，给WHERE查询字段添加索引

假如说在两个表进行了多表连接查询之后，我们还需要进一步的做过滤，一般都会在WHERE中添加过滤条件，此时想要进一步提高SQL的执行效率，就可以给WHERE查询条件的字段上添加索引。如果不加索引就会向上面一样进行一个全表扫描，取出每一条表连接之后的数据与WHERE的查询条件做匹配，加了索引就可以精确判断，迅速过滤掉无用的数据，提高性能。

（4）表连接查询查询条件字段类型一定要保持一致

如果不保持一致，就算我们为被驱动表添加了索引，索引也是失效的，因为数据类型不一致，所以数据库底层就需要全表扫描将每条数据都拿出来，然后先进行数据类型的转换，再去做条件的匹配，因此一定要注意，多表连接查询查询条件的字段类型一定要保持一致，否则会因为隐式类型转换导致索引失效。

4. 内连接优化方案

首先需要明确的一点是，内连接查询表面上是不分主表和此表的，默认来说左边的表就是驱动表，右边的表就是被驱动表。但在执行查询之前，SQL优化器会从和判断查询字段是否存在索引和两张表那个表数据量较少，会选出一个数据量小的作为驱动表，选出查询字段有索引的作为被驱动表。

如下图，是我数据库中的 employee员工表和 department 部门表，

department 部门表中有27条数据，

department_id 是主键，有数据库默认生成的主键索引

employee 员工表中有107条数据；

 在 employee 员工表中，我们给 department_id 外键添加普通索引

现在两个表中都有 department_id 的索引， 

如下SQL语句，使用 explain 关键字显示查询计划，我们将 employee 写在坐标作为驱动表，将department 部门表作为被驱动表，看看 employee 会作为驱动表吗

EXPLAIN SELECT e.employee_id,d.department_id
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;

可以观察到，虽然SQL语句中将 employee 写在左边作为驱动表，但 explain 执行计划中显示实际执行SQL语句的时候会将 department 作为驱动表，为什么呢？

因为 department 表中只有27条数据，比employee 表的107条数据少得多，能大大减少驱动次数，提高效率，所以内连接查询中，基本上都是哪个数据量少哪个表作为驱动表。

因此在实际开发设计表的时候，如果需要涉及到多表查询，应该尽量在数据量多的表(被驱动表)中经常需要查询的字段上添加索引，可以大大降低表连接查询匹配消耗的时间。