MySQL的 join 操作弱爆了？ _MySQL

大家好，我是历小冰，今天我们来学习和吐槽一下 MySQL 的 Join 功能。
关于MySQL 的 join，大家一定了解过很多它的“轶事趣闻”，比如两表 join 要小表驱动大表，阿里开发者规范禁止三张表以上的 join 操作，MySQL 的 join 功能弱爆了等等。这些规范或者言论亦真亦假，时对时错，需要大家自己对 join 有深入的了解后才能清楚地理解。
下面，我们就来全面的了解一下 MySQL 的 join 操作。
正文在日常数据库查询时，我们经常要对多表进行连表操作来一次性获得多个表合并后的数据，这是就要使用到数据库的 join 语法。join 是在数据领域中十分常见的将两个数据集进行合并的操作，如果大家了解的多的话，会发现 MySQL，Oracle，PostgreSQL 和 Spark 都支持该操作。本篇文章的主角是 MySQL，下文没有特别说明的话，就是以 MySQL 的 join 为主语。而 Oracle ，PostgreSQL 和 Spark 则可以算做将其吊打的大boss，其对 join 的算法优化和实现方式都要优于 MySQL 。
MySQL 的 join 有诸多规则，可能稍有不慎，可能一个不好的 join 语句不仅会导致对某一张表的全表查询，还有可能会影响数据库的缓存，导致大部分热点数据都被替换出去，拖累整个数据库性能。
所以，业界针对 MySQL 的 join 总结了很多规范或者原则，比如说小表驱动大表和禁止三张表以上的 join 操作。下面我们会依次介绍 MySQL join 的算法，和 Oracle 和 Spark 的 join 实现对比，并在其中穿插解答为什么会形成上述的规范或者原则。
对于 join 操作的实现，大概有 Nested Loop Join (循环嵌套连接)，Hash Join(散列连接) 和 Sort Merge Join(排序归并连接) 三种较为常见的算法，它们各有优缺点和适用条件，接下来我们会依次来介绍。
MySQL 中的 Nested Loop Join 实现Nested Loop Join 是扫描驱动表，每读出一条记录，就根据 join 的关联字段上的索引去被驱动表中查询对应数据。它适用于被连接的数据子集较小的场景，它也是 MySQL join 的唯一算法实现，关于它的细节我们接下来会详细讲解。
MySQL 中有两个 Nested Loop Join 算法的变种，分别是 Index Nested-Loop Join 和 Block Nested-Loop Join 。
Index Nested-Loop Join 算法下面，我们先来初始化一下相关的表结构和数据
CREATE TABLE `t1` (`id` int(11) NOT NULL,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `a` (`a`)) ENGINE=InnoDB;delimiter ;;# 定义存储过程来初始化t1create procedure init_data()begindeclare i int;set i=1;while(i<=10000)doinsert into t1 values(i, i, i);set i=i+1;end while;end;;delimiter ;# 调用存储过来来初始化t1call init_data();# 创建并初始化t2create table t2 like t1;insert into t2 (select * from t1 where id<=500)有上述命令可知，这两个表都有一个主键索引 id 和一个索引 a，字段 b 上无索引。存储过程 init_data 往表 t1 里插入了 10000 行数据，在表 t2 里插入的是 500 行数据。
为了避免 MySQL 优化器会自行选择表作为驱动表，影响分析 SQL 语句的执行过程，我们直接使用 straight_join 来让 MySQL 使用固定的连接表顺序进行查询，如下语句中，t1是驱动表，t2是被驱动表。
select * from t2 straight_join t1 on (t2.a=t1.a);使用我们之前文章介绍的 explain 命令查看一下该语句的执行计划。

文章插图

从上图可以看到，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