333435 LRUJOIN语句优化-CSDN博客

本文链接：https://blog.csdn.net/weixin_43405469/article/details/119257652

LRU 3/8 old 区域 5/8 young区域首先是进入old 区域一秒以内就算被访问到也不进入 young区域一秒以后才进入防止大表查询污染缓存

Index Nest - Loop Join（NLJ算法）

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

用 straight_join 是固定让 t1 做驱动表 t2做被驱动表

如果没有的话优化器会执行选择那个表作为驱动表不利于我们分析

因为 t2是被驱动表而且 a上索引

语句的执行流程是

从表 t1 中读入一行数据 R；
从数据行 R 中，取出 a 字段到表 t2 里去查找；
取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。

驱动表全表扫描而被驱动表走了索引搜索一棵树的近似复杂度是以2为底的M的对数

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。因此整个执行过程，近似复杂度是 N + N2log M。 所以让N要尽量小

对于 NLJ 要小表驱动大表

对于

b上没有索引

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

假设 t1 1000行 t2 100行

Simple Nested-Loop Join

没有索引也不用 join buffer 就是 simple Nested - Loop Join

需要扫描 1000 * 100 = 1000 00 行

Block Nested - Loop Join

把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

Explain Extra 执行计划出现了 Using Join Buffer

这个跟上面一样是需要 1000 00行但是在内存中比较更快

假如 Join Buffer 放不下 t1 就需要分段放

1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续
第 2 步；
2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件
的，作为结果集的一部分返回；
3. 清空 join_buffer；
4. 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。

假如分了两段放入 join buffer 的话 t2表会被扫描两次因为不确定在上一段 t2被扫描过的行跟这一段是否匹配

建议

Join 很慢就把 Join_buffer_size 方法

能不能使用 join 语句？
1. 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，
其实是没问题的；
2. 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join
操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不
要用。
所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有
出现“Block Nested Loop”字样。

然后选择小表驱动大表

注意区分什么是小表

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 一百行 t2 一千行

这里用第二个语句更好因为只需要放入 t2的前五十行从而减少占满 join buffer的可能性

2
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 跟 t1放入 join buffer 要扫描的行数是一样的

但是 t1 只需要查询 t1.b 而t2是所有数据所以把t1 作为驱动表更好

所以，更准确地说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过
滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，
应该作为驱动表。

对于NLJ的优化

MRR （Multi-Range Read）

MRR 与传统的回表的比较

传统的回表假如查询的是 a索引树 a索引递增的时候 ID主键是不递增的（假设这种场景）那么每次根据a 回表都是一次随机IO 非常慢

MRR 优化的设计思路

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

如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

对于 explain select * from t2 where a > 100 and a < 200

这种语句可以用到 MRR

MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是
说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引
查数据，才能体现出“顺序性”的优势。

Batched Key Access（BKA）

这是对NLJ 算法的优化

NLJ 算法执行的逻辑是：从驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做
join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。

既然如此，我们就把表 t1 的数据取出来一部分，先放到一个临时内存。这个临时内存不是
别人，就是 join_buffer。

这里的意思是如果我们每次只用 t1的一行的话我到 t2表的a索引上查找到对应值之后只有一条数据是随机IO
如果用的是 t1的多条数据
那么在 t2 a索引上可以一次性查出多条数据然后利用MRR 去顺序IO

BNL算法的性能问题

1 如果用BNL 算法 t1表分段放入了 join buffer 而被驱动表是一个大的冷数据表，由于innodb对 buffer pool的 LRU做了优化冷数据先进入 old区域，假如被驱动表在相隔一秒后被再次扫到就会进入 young区但是这个数据以后都不会再用到会造成缓存污染这是冷表的数据小于 bufferpool的 3/8的情况下

假如冷表很大那么业务正常访问的数据页没有机会进入young区域，由于优化机制的存在，一个正常访问的数据页，要进入 young 区域，需要隔 1 秒后再次被访问到。但是，由于我们的 join 语句在循环读磁盘和淘汰内存页，进入 old 区域的数据页，很可能在 1 秒之内就被淘汰了。这样，就会导致这个 MySQL 实例的 Buffer Pool 在这段时间内，young 区域的数据页没有被合理地淘汰。

大表 join 操作虽然对 IO 有影响，但是在语句执行结束后，对 IO 的影响也就结束了。但
是，对 Buffer Pool 的影响就是持续性的，需要依靠后续的查询请求慢慢恢复内存命中
率。
为了减少这种影响，你可以考虑增大 join_buffer_size 的值，减少对被驱动表的扫描次数。

BNL 转 BKA

建索引或者给被驱动表建临时表在临时表上建索引