likes
comments
collection
share

MySQL-一篇文章彻底搞定mysql的join

作者站长头像
站长
· 阅读数 81
可能大家都从网上了解过，有很多公司是不允许代码中出现多个表的连接查询的。本文就概述一下连接查询的执行原理。

1.Index Nested-Loop Join

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

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;

准备t1和t2两个表，t2表中插入1000行数据，t1表中插入100行数据，两个表都创建了主键索引id 和一个索引a。 让我们看一下这个语句的执行计划

select * from t1 straight_join t2 on (t1.a=t2.a);

MySQL-一篇文章彻底搞定mysql的join 在这条语句中我们可以看出是通过t1来连接查询的t2.因此我们把t1称为驱动表，t2称为被驱动表。 接下来让我们来分析一下这个语句的执行流程

  • 可以看到这个语句执行的时候使用了被驱动表t2上的a索引。因此这个语句的执行流程是这样的
  1. 从表t1中读取一行数据（假设为数据Q）
  2. 从数据Q中，取出a字段到表t2中去查找
  3. 取出t2表中满足条件的数据，和数据Q组成一个结果集（假设为结果集M）
  4. 重复执行1-3步骤。直到遍历完表t1并且找出了结果集。

这个过程简单来说就是先取出t1表中的一行，然后去t2中查找满足条件的记录，因为用到了t2表中的a索引，所以去t2表查询时走的不是全表扫描。这个和平时我们写的嵌套查询类似，并且还用到了被驱动表中的索引，在MySQL中我们把这个算法称之为 Index Nested-Loop Join.简称"NLJ";

在这个过程中我们需要扫描t1表100行，然后每取出一行还要去t2表找出对应的行数，因为t2走了索引，通过树搜索，数据一一对应不是全表扫描（就相当于直接找出来的）每次搜索只扫描一行，一共也扫描了100行。所以一共扫描了200行。

如果我们直接使用单表查询呢？就是先执行select * from t1，查出表 t1 的所有数据，这里有 100 行；然后循环这100行，每次循环还需要去select * from t2 where a=t1查出来的条件。因为a有索引都不是全表扫描，所以也只是扫描了200行。但是我们执行了101次sql语句呀。比join多了100次和数据库的交互。肯定没有join好啊。

但如果我们把sql改成这个呢select * from t1 straight_join t2 on (t1.a=t2.b); 很明显这个语句是没有使用任何索引的。那就代表每次查询都要走全表扫描的。简单来说我们从t1表中查询出一条以后（假设为数据Q）然后拿着数据Q的a字段的值去和t2表的每一行进行比较看是否和t2表的b值相等。这样算了一共要扫描10010000=1000000行数据。如果表稍微大点的话其实就很恐怖了。同样在mysql中我们把这个算法称之为Simple Nested-Loop join。当然了MySQl可没有这么笨，在MySQL而是使用了一个叫Block Nested-Loop join的算法，简称BNL。

2.Block Nested-Loop join 执行流程

这个时候如果被驱动表中没有索引的话，执行流程是这样的。把表t1的数据读取出来以后存入join_buffer。由于我们写的是select ，因此我们是把整个表都放进了join_buffer中了。扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比。满足join条件的，作为结果集的一部分返回。让我们来看一下这个语句的执行计划

MySQL-一篇文章彻底搞定mysql的join 可以看到，在这个过程中，对表t1和t2都做了一次全表扫描，扫描的总行数是1100。可能有人会问为什么扫描的行数不是1000000次了。是因为MySQL把所有的数据全部都放到join_buffer里面了，join_buffer就是内存。MySQL在内存里面进行10万次的判断操作。这种方式和Simple Nested-Loop Join相比肯定是更快的。因为访问内存永远优于访问磁盘。 这个时候可能有人会问了，join_buffer放不下怎么办。join_buffer 的大小是由参数join_buffer_size 设定的，默认值是 256k。如果放不下就分段放。 执行过程就变成了

  1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；
  2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
  3. 清空 join_buffer；
  4. 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。 通过这个过程可以看到判断的次数仍然是（88+12）1000=10万次。扫描的行数变成了NKM。（N代表t1表的行数，k代表分了多少段，M代表t2表的行数）显然k不是常数，N越大可能分的段数越多k也就越大。同时也会我们设置的join_buffer_size有关。因此k可以替换为λN。所以扫描行数就变成N+λNM；内存判断为N*M次。从这个公式我们也可以得出一个结论，就是小表驱动大表代价更小一些。 什么是小表： 这里的小表并不是整个表的大小，而是我们要使用的数据的大小。举个例子100行数据和50行数据需要的地址肯定不一样吧。select *和select id 查出来的结果占内存的大小肯定也不一样吧。需要综合考虑的。看下下面这两个例子。 select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50; select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50; 首先俩个都没有使用索引。第一个语句需要将t1的所有行数据放入join_buffer后然后再去t2里面找对应的数据。第二个语句需要将t2的50行数据放入join_buffer中然后再去对应的t1表里去找。那么这种情况肯定是t2驱动t1. select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100; select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100; 表 t1 和 t2 都是只有 100 行参加 join。但是，这两条语句每次查询放入 join_buffer 中的数据是不一样的：
  • 表 t1 只查字段 b，因此如果把 t1 放到 join_buffer 中，则 join_buffer 中只需要放入 b 的值；
  • 表 t2 需要查所有的字段，因此如果把表 t2 放到 join_buffer 中的话，就需要放入三个字段 id、a 和 b。

3. Multi-Range Read 优化

通过以上分析，BNL 算法在大表 join 的时候性能就差多了，比较次数等于两个表参与 join 的行数的乘积，很消耗 CPU 资源。现在介绍一下Multi-Range Read优化。这个优化的主要目的是尽量使用顺序读盘。 看下这个语句select * from t2 where a>=1 and a<=100; (a是有索引的)。这个语句的执行会进行经典的回表操作InnoDB 在普通索引 a 上查到主键 id 的值后，再根据一个个主键 id 的值到主键索引上去查整行数据。但如果随着a的值递增顺序查询的话，id的值就变成随机的了。就会出现随机访问。性能相对较差。虽然按行查询这个机制不能改，但是调整查询顺序，还是能够加速的因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。 MRR优化思路：

  1. 根据索引a，定位到满足条件的记录。将id值放入read_rnd_buffer中。
  2. 将read_rnd_buffer中的id进行递增排序
  3. 排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的，如果步骤1导致read_rnd_buffer满了，就会先执行2和3.然后清空read_rnd_buffer。继续执行。 另外需要说明的是，如果你想要稳定地使用 MRR 优化的话，需要设置 set optimizer_switch="mrr_cost_based=off" 看下执行计划

MySQL-一篇文章彻底搞定mysql的join 可以看到Extra字段多了Using MRR，表示的是用上了MRR优化。 MRR能够提升性能的核心在于这条查询语句在索引a上做的是一个范围查询（就是多值查询）可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。

4. Batched Key Access--NJL算法的优化。

MySQL 在 5.6 版本后开始引入的 Batched Key Access(BKA) 算法了。这个 BKA 算法，其实就是对 NLJ 算法的优化。 NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。通过上文的我们知道join_buffer在BNL算法里的作用是暂存驱动表。BKA算法中也利用了这个特性。 BKA算法执行流程： 我们将join_buffer中存放t1表中的数据，然后在去t2表中去匹配对应的数据组成结果集即可。 如果要使用 BKA 优化算法的话，你需要在执行 SQL 语句之前，先设置

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

其中，前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于 MRR。

5.BNL转BKA

一些情况下，我们可以直接在被驱动表上建索引，这时就可以直接转成 BKA 算法了（最优的办法）。 但有的时候我们不可能就针对一个连接查询就建一个索引。这肯定是不合适的。当俩个表数据量很大的时候我们在join_buffer中的做的判断也就会越来越多。性能看起来也没有那么高。这个时候我们可以采用临时表。1. 把我们被驱动表中满足条件的数据放入临时表当中。2。 为了让join使用BKA算法。给临时表上创建对应的索引。让驱动表和临时表做join操作。这样的话性能会有很大的提升。

6.扩展 -hash join

看到这里你可能发现了，其实上面计算 10 亿次那个操作，看上去有点儿傻。如果 join_buffer 里面维护的不是一个无序数组，而是一个哈希表的话，那么就不是 10 亿次判断，而是 100 万次 hash 查找。这样的话，整条语句的执行速度就快多了吧？

确实如此。

这，也正是 MySQL 的优化器和执行器一直被诟病的一个原因：不支持哈希 join。并且，MySQL 官方的 roadmap，也是迟迟没有把这个优化排上议程。

实际上，这个优化思路，我们可以自己实现在业务端。实现流程大致如下：

  1. select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构，比如 C++ 里的 set、PHP 的数组这样的数据结构。
  2. select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。
  3. 把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。

7.总结

  • 如果可以使用被驱动表的索引，join 语句还是有其优势的---NLJ算法；
  • 不能使用被驱动表的索引，只能使用 Block Nested-Loop Join 算法，这样的语句就尽量不要使用；
  • 在使用 join 的时候，应该让小表做驱动表。
  • 对NLJ和BNL算法也有对应的优化
    • BKA 优化是 MySQL 已经内置支持的，建议你默认使用；
    • BNL 算法效率低，建议你都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
    • 基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；
    • MySQL 目前的版本还不支持 hash join，但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。
转载自:https://juejin.cn/post/7140270351858499614
评论
请登录