MySQL 外连接和内连接的查询优化怎么做？-CSDN博客

目录

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次。

给被驱动表的匹配字段添加了索引接着刚才的说如果被驱动表中给 id 字段添加了索引那么被驱动表就可以精准查找 id = 12 的这条记录是否存在查询不到则说明没有返回空这样就不需要对被驱动表做全表扫描省去了大量的时间。每次驱动只需要在 department 部门被驱动表中进行一次查询效率得到了很大的提升。

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条数据少得多能大大减少驱动次数提高效率所以内连接查询中基本上都是哪个数据量少哪个表作为驱动表。

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