正文

Multi-Range Read 优化

MRR 的优化就是利用回表的概念将随机读取变成顺序读取。

• 非聚集索引上存储的都是主键的值，然后到聚集索引上查询出来完整的数据，这个过程叫做回表。
• 大多的主键上都是递增的，那么聚集索引的顺序就是物理磁盘存储的顺序。
• 如果是顺序读取，那么就能提升性能。

select * from t1 where a>=1 and a<=100;

MRR 的设计思路就是

1. 根据索引 a，查询到主键的 id，将 id 放入 read_rnd_buffer 中。如果 read_rnd_buffer 放满了先执行下面两步。然后清空 read_rnd_buffer 再继续。
2. 将 read_rnd_buffer 中的 id 递增排序。
3. 根据 id 到聚集索引中查询。

read_rnd_buffer 的大小是由 read_rnd_buffer_size 参数控制的。

笔记

如果你想要稳定地使用 MRR 优化的话，需要设置 set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

MRR 的核心在于，查询语句在一个索引上做的是多值查询（范围查询），而且没有触发到覆盖索引。

注意

虽然 Multi-Range Read（MRR）优化可以提高索引查询的性能，但并不是所有情况下启用 MRR 都会提升性能。在某些具体的场景下，可能会产生额外的磁盘 I/O 开销。例如，如果查询的数据可以直接使用索引数据就能满足（使用覆盖索引），这样 MRR 就无法进行优化。

此外，MRR 在本质上是一种用「空间换时间」的做法。MySQL 不可能给你无限的内存来进行排序，这块内存的大小就由参数 read_rnd_buffer_size 来控制 ¹²。如果 read_rnd_buffer 满了，就会先把满了的 rowid 排好序去磁盘读取，接着清空，然后再往里面继续放 rowid ，直到 read_rnd_buffer 又达到 read_rnd_buffer_size 配置的上限，如此循环。这可能会导致额外的内存和 CPU 开销。

Batched Key Access

MySQL5.6 之后引入了 Batched Key Access (BKA) 算法。BKA 其实是对 NLJ 的一种优化。

• NLJ 是用不到 join buffer 的，而且 NLJ 是将被驱动表一行一行进行取出比对。
• BKA 在 NLJ 上进行了优化一次从被驱动表中取出多行，而且用到了 join buffer。

BKA 的步骤

1. 将驱动表的相关列放入到 join buffer 中。
2. 批量将索引值发送到 MRR 接口，MRR 根据索引的值对其对应的主键 ID 进行排序。
3. 返回主键 ID。根据主键 ID 去查询。

笔记

在 MySQL 5.6 中默认关闭 BKA（MySQL 5.7 默认打开），必须将 optimizer_switch 系统变量的 batched_key_access 标志设置为 on。BKA 使用 MRR，因此 mrr 标志也必须打开。目前，MRR 的成本估算过于悲观。因此，mrr_cost_based 也必须关闭才能使用 BKA。

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

BNL 算法的性能问题

这里正好回答一下上一章的问题的答案：使用 Block Nested-Loop Join (BNL) 算法时，可能会对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表，除了会导致 IO 压力大以外，还会对系统有什么影响呢？

MySQL 对 LRU 做了优化，分为了 young 和 old 区域，如果冷表过大，可能正常访问的数据页，没有机会进入 young 区域。

• 一个正常访问的数据，要想进入 young 区域，需要隔一秒后再次被访问到。
• 但是 join 语句在循环读取磁盘，并且可能需要替换内存中的一些数据页。导致进入 old 区域的新数据可能还没来来得及被访问就被淘汰，下次还需要读取磁盘。
• 而且还导致这些数据无法进入 young 区域，young 区域原来的数据长时间不被访问也被淘汰出去，最后内存命中率降低。

大表进行 join 的时候，虽然会产生大量的 IO 占用，但是这个过程只持续到 join 结束，但是 join buffer 影响是持续的，因为原本在 join buffer 中的数据页会被替换出去，而 join 之后的查询语句可能又要把数据加载到 join buffer 中。

所以建议增大 join_buffer_size 的值，减少被驱动表的扫描次数。而且在写 sql 之前先 explain 看一下，如果这个 sql 一定要用到 BNL 算法，尽量在被驱动表上对搜索字段加上索引，把 BNL 转成 BKA。

BNL 转 BKA

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

t2 的 b 字段是没有索引的，BNL 的话会先将 t1 的所有数据放到 join_buffer 中，然后从 t2 取出一行就和 join_buffer 中所有数据进行对比，等于说要进行$1000 \times 1000000 = 1000000000$ 次。下面是 explain 命令的结果。

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1000	100.00	Using where
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	998660	1.11	Using where; Using join buffer (hash join)

但是在 t2 上加索引很不合适，t2 有 100 万行数据，这还只是模拟的，如果换成真实的会更加慢，这个时候只能用临时表来解决了。大概思路是

1. 把 t2 上符合条件的先过滤出来，存在临时表 temp 中。
2. 在临时表 temp 的 b 字段上加上索引。让其变成了 NLJ 算法再转化成 BKA 算法。
3. t1 和临时表进行连接查询。

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	1000	100.00	Using where
1	SIMPLE	temp_t	NULL	ref	b	b	5	mysql45.t1.b	1	100.00	NULL

扫描行数直线下降，但是也可以看到即使是上面 998660+1000 行扫描速度也是很快的，这是因为 MySQL 完成了 hash join 这个算法。

扩展 - hash join

这个现在 MySQL 已经解决了

hash join 的大概思路是。

1. select * from t1; 将 t1 的 1000 行数据存到一个 hash 表中。
2. select * from t2 where b>=1 and b<=2000; 获取 t2 中满足条件的 2000 行。
3. 一行一行到 hash 表中进行判断

这个过程比临时表要快。