33
LRU 3/8 old 区域 5/8 young区域 首先是进入old 区域 一秒以内 就算被访问到 也不进入 young区域 一秒以后 才进入 防止 大表查询 污染缓存
34
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
建索引 或者 给 被驱动表 建临时表 在临时表上建索引