MySQL的join功能弱爆了？

今天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 ;;
# 定义存储过程来初始化t1
create procedure init_data()
begin
  declare i int;
  set i=1;
  while(i<=10000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
# 调用存储过来来初始化t1
call init_data();
# 创建并初始化t2
create 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 命令查看一下该语句的执行计划。

Nested Loop JoinHash JoinSorted Merge Join连接条件适用于任何条件只适用于等值连接（=）等值或非等值连接(>，<，=，>=，<=)，‘<>’除外主要消耗资源CPU、磁盘I/O内存、临时空间内存、临时空间特点当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果当缺乏索引或者索引条件模糊时，Hash Join 比 Nested Loop 有效。通常比 Merge Join 快。在数据仓库环境下，如果表的纪录数多，效率高当缺乏索引或者索引条件模糊时，Sort Merge Join 比 Nested Loop 有效。当连接字段有索引或者提前排好序时，比 hash join 快，并且支持更多的连接条件缺点无索引或者表记录多时效率低建立哈希表需要大量内存，第一次的结果返回较慢所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据需要索引是(没有索引效率太差)否否

对于 Join 操作的理解

讲完了 Join 相关的算法，我们这里也聊一聊对于 join 操作的业务理解。

在业务不复杂的情况下，大多数join并不是无可替代。比如订单记录里一般只有订单用户的 user_id，返回信息时需要取得用户姓名，可能的实现方案有如下几种：

1. 一次数据库操作，使用 join 操作，订单表和用户表进行 join，连同用户名一起返回；
2. 两次数据库操作，分两次查询，第一次获得订单信息和 user_id，第二次根据 user_id 取姓名，使用代码程序进行信息合并；
3. 使用冗余用户名称或者从 ES 等非关系数据库中读取。

上述方案都能解决数据聚合的问题，而且基于程序代码来处理，比数据库 join 更容易调试和优化，比如取用户姓名不从数据库中取，而是先从缓存中查找。

当然， join 操作也不是一无是处，所以技术都有其使用场景，上边这些方案或者规则都是互联网开发团队总结出来的，适用于高并发、轻写重读、分布式、业务逻辑简单的情况，这些场景一般对数据的一致性要求都不高，甚至允许脏读。

但是，在金融银行或者财务等企业应用场景，join 操作则是不可或缺的，这些应用一般都是低并发、频繁复杂数据写入、CPU密集而非IO密集，主要业务逻辑通过数据库处理甚至包含大量存储过程、对一致性与完整性要求很高的系统。

更多相关免费学习推荐：mysql教程(视频)

以上就是MySQL 的 join 功能弱爆了？的详细内容，更多请关注 第一PHP社区 其它相关文章！