Mysql面试（五）（join语句优化）

最新推荐文章于 2023-10-16 19:10:01 发布

Chenzxxxx

最新推荐文章于 2023-10-16 19:10:01 发布

阅读量172

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_41188974/article/details/107762764

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MRR优化（Multi-Range Read ）
NLJ（Index Nested-Loop Join）算法和BNL（Block Nested-Loop Join）都有优化的空间。
MRR优化的主要目的是尽量使用顺序读盘。

select * from t1 where a>=1 and a<=100;
什么是回表，回表就是，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。之后继续找索引 a 的下个记录，并继续循环。

要想使用MRR优化的话，要设置set optimizer_switch=“mrr_cost_based=off” （由于现在的优化器策略，判断消耗的时候，会更倾向于不使用 MRR，把 mrr_cost_based 设置为 off，就是固定使用 MRR 了。）

使用explain执行语句时，会看到Using MRR。

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

BKA算法（Batched Key Access）
Batched Key Access是对NLJ算法的优化。

BKA算法用到上面提到的MRR的思想，NLJ算法的执行流程是先将驱动表的对应要关联的值一行一行取出来到被驱动表上做匹配，对应上一篇所讲就是表t1，一行行取出a的值，到表t2去join，每次匹配一个值，MRR的优势就用不上了。
那么可以将表t1的一部分数据取出来，放到一个临时内存，这个临时内存就是join_buffer，上一篇提到join_buffer是BNL算法用来存驱动表数据的，对NLJ算法没什么用处，这里可以用在BKA算法中。

join_buffer中放入的数据只会取查询需要的字段，对应上面就是a字段，当join_buffer放不下所有数据，那么会像BNL一样采用分段执行。
要启用BKA算法，需要设置 set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;
BKA算法依赖于MRR

BNL转BKA
一般情况下被驱动表的索引能用上，就可以直接转成BKA算法了。
BNL缺点：
1.可能会多次扫描被驱动表，占用磁盘 IO 资源；
2.判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源；
3.可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率。
现有一个场景
create table t1(id int primary key, a int, b int, index(a));
create table t2(id int primary key, a int, b int, index(a));
t1 1000条数据。 t2 100w条数据。
如果有这么一条语句
select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;
经过where过滤后，要参加join 的只有2000条数据。如果这是一条低频的sql，在表t2的字段b上建立索引又浪费。

但是如果使用BNL算法来执行的话，对于表t2中的每一行，在判断join是否满足条件的时候，都需要遍历join_buffer中的所有行，所以判断的次数是100w*1000 = 10亿次。这将非常的慢。

可以考虑使用临时表，将表t2中满足条件的数据放在临时表，然后给字段b添加索引，让表t1和这个临时表join。
1 create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
2 insert into temp_t select * from t2 where b>=1 and b<=2000;
3 select * from t1 join temp_t on (t1.b=temp_t.b);

hash join
在上面的10亿次操作中，效率是极其低的。
如果join_buffer中维护的不是一个无序数组，而是一个hash表，那整条查询语句的速度就快多了，只需要100w次hash查找。
但是mysql并不支持hash join。

其实可以在业务端自己实现：

select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构。
select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。
把这 2000 行数据一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，作为结果集的一行

优化总结
1.BKA 优化是 MySQL 已经内置支持的，建议默认使用；
2.BNL 算法效率低，建议都尽量转成 BKA 算法。优化的方向就是给被驱动表的关联字段加上索引；
3.基于临时表的改进方案，对于能够提前过滤出小数据的 join 语句来说，效果还是很好的；
4.MySQL 目前的版本还不支持 hash join，但可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。