小编给大家分享一下MySQL的加入功能有什么用,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获、下面让我们一起去了解一下吧!
关于MySQL的加入,大家一定了解过很多它的“轶事趣闻”,比如两表连接要小表驱动大表,阿里开发者规范禁止三张表以上的加入操作,MySQL的加入功能弱爆了等等。这些规范或者言论亦真亦假,时对时错,需要大家自己对加入有深入的了解后才能清楚地理解。
下面,我们就来全面的了解一下MySQL的加入操作。
正文
在日常数据库查询时,我们经常要对多表进行连表操作来一次性获得多个表合并后的数据,这是就要使用到数据库的连接语法. join是在数据领域中十分常见的将两个数据集进行合并的操作,如果大家了解的多的话,会发现MySQL,甲骨文,PostgreSQL和火花都支持该操作。本篇文章的主角是MySQL,下文没有特别说明的话,就是以MySQL的加入为主语。而<强>甲骨文,PostgreSQL和火花则可以算做将其吊打的大老板,其对加入的算法优化和实现方式都要优于MySQL。
MySQL的加入有诸多规则,可能稍有不慎,可能一个不好的加入语句不仅会导致对某一张表的全表查询,还有<强>可能会影响数据库的缓存,导致大部分热点数据都被替换出去,拖累整个数据库性能。
所以,业界针对MySQL的加入总结了很多规范或者原则,比如说小表驱动大表和禁止三张表以上的加入操作。下面我们会依次介绍MySQL连接的算法,和甲骨文和火花的加入实现对比,并在其中穿插解答为什么会形成上述的规范或者原则。
<强>对于加入操作的实现,大概有嵌套循环联接(循环嵌套连接),散列连接(散列连接)和排序合并连接(排序归并连接)三种较为常见的算法强,它们各有优缺点和适用条件,接下来我们会依次来介绍。
MySQL中的嵌套循环联接实现
嵌套循环联接是扫描驱动表,每读出一条记录,就根据加入的关联字段上的索引去被驱动表中查询对应数据。它适用于被连接的数据子集较小的场景,它也是MySQL连接的唯一算法实现,关于它的细节我们接下来会详细讲解。
MySQL中有两个嵌套循环联接算法的变种,分别是指数嵌套循环连接和块嵌套循环连接。
指数嵌套循环连接算法
下面,我们先来初始化一下相关的表结构和数据
有上述命令可知，这两个表都有一个主键索引 id 和一个索引 a，字段 b 上无索引。存储过程 init_data 往表 t1 里插入了 10000 行数据，在表 t2 里插入的是 500 行数据。
为了避免 MySQL 优化器会自行选择表作为驱动表，影响分析 SQL 语句的执行过程，我们直接使用 straight_join 来让 MySQL 使用固定的连接表顺序进行查询，如下语句中，t1是驱动表，t2是被驱动表。
select * from t2 straight_join t1 alt="MySQL的 join功能有什么用">从上图可以看到，t1 表上的 a 字段是由索引的，join 过程中使用了该索引，因此该 SQL 语句的执行流程如下：
- 从 t2 表中读取一行数据 L1；
- 使用L1 的 a 字段，去 t1 表中作为条件进行查询；
- 取出 t1 中满足条件的行， 跟 L1组成相应的行，成为结果集的一部分；
- 重复执行，直到扫描完 t2 表。
这个流程我们就称之为 Index Nested-Loop Join，简称 NLJ，它对应的流程图如下所示。
需要注意的是，在第二步中，根据 a 字段去表t1中查询时，使用了索引，所以每次扫描只会扫描一行(从explain结果得出，根据不同的案例场景而变化)。
假设驱动表的行数是N，被驱动表的行数是 M。因为在这个 join 语句执行过程中，驱动表是走全表扫描，而被驱动表则使用了索引，并且驱动表中的每一行数据都要去被驱动表中进行索引查询，所以整个 join 过程的近似复杂度是 N
