MySQL的join功能弱爆了？

发布时间：12/01 来源：未知浏览：关键词：

今天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 快，而且支撑更多的连贯前提缺陷无索引或者表记载多时效率低创立哈希表需要批量内存，首先次的效果返回较慢所有的表都需要排序。它为最优化的吞吐量而设计，而且在效果没有全部寻到前不返回数据需要索引是(没有索引效率太差)否否