35 | join语句怎么优化

Multi-Range Read（MRR）优化

优化思路：顺序读盘比随机读盘效率高

一般MySQL主键都是自增的，按照主键递增读取数据近似顺序读。

优化后的语句执行流程：

1. 根据索引取出主键id，将id放入一块临时空间中（read_rnd_buffer）；
2. 对read_rnd_buffer中的id递增排序；
3. 排序后的id数组依次到主键id索引中查记录，将结果放到结果集中。

如果一次放不下所有的id，也是分批次的放。

MySQL官方文档：对于这个优化，MySQL在判断消耗的时候，更倾向于不使用MRR。如果想要稳定的使用MRR的话需要设置：

set optimizer_switch="mrr_cost_based=off"。

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

查看optimizer_switch的值：select @@optimizer_switch;

Batched Key Access(BKA)

MySQL 5.6版本后引入该算法。其实是对NLJ(Index Nested-Loop Join)算法的优化。

按照NLJ原来的执行流程，每次都是从驱动表拿一个值去被驱动表做查询，这样就用不上MRR的优势了。

把驱动表的数据一次多取出一些来放到一个临时内存中（这个临时内存就是join_buffer），把需要查询的字段放进去，如果一次放不下，也是分批次放（其实意思就是把BNL的join_buffer运用到NLJ中，然后再通过BKA优化）。

BKA算法的启用：

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

前两个参数的通是启用MRR，因为BKA算法的优化要依赖MRR。

BNL（Block Nested_Loop Join）算法性能问题

InnoDB对Buffer Pool使用的算法是LRU算法（最近最久未使用原则），并且分为young和old区。第一次从磁盘读入的数据页会先放到old区，如果1秒之后再次访问该数据页，就会把该数据页移动到链表头部，否则还保持原来的位置不变。

如果join的是一个冷表，并且old区能够全部放得下该表的所有数据，因为我们join的过程中，会多次扫描这个冷表，这可能对一些数据页的访问会超过1秒，然后就会移动到young区，把正常业务访问的数据淘汰掉。

如果join的表是一个比较大的冷表，old区放不下所有数据，因为我们在重复扫描这个冷表，这会导致正常业务的数据进入old区后可能很快就被淘汰了，没机会进入young区，另外可能还会导致一部分冷表数据进入young区，将young区的一部分正常业务访问的数据淘汰掉。

这两种情况都会导致Buffer Pool的内存命中率下降，磁盘IO压力增大，这时候正常业务的效率会变慢，因为取数需要读盘。而且，虽然join查询慢，结束了就没事了，但是对Buffer Pool内存命中率的影响却是持续的，需要业务慢慢的去刷数据来恢复，慢慢的内存命中率才会上来。

可以通过增大join_buffer_size来减少堆被驱动表的扫描次数。

BNL转BKA

一些情况下，可以直接在被驱动表上建索引，将BNA转成BKA。

如果SQL是一个低频SQL，家索引浪费，但是不加索引会浪费CPU资源，这时候怎么办？比如：

select * from t1 join t2 on t.b=t2.b where t2.b>=1 and t2.b<=2000;(t2:100万行数据  t1:1000行数据   b没有索引)

这种情况可以考虑使用临时表：

1. 把表t2中满足条件的数据放在临时表tmp_t中；
2. 给临时表tmp_t的字段上加索引（可以使用BKA）；
3. 表t1和tmp_t做join。

create temporary table tmp_t(id int primary key, a int, b int, index(b))engine=innodb;

insert into tmp_t elect * from t2 where b>=1 and b<=2000;

select * from t1 join tmp_t on t1.b=t.b;

扩展-hash join

MySQL优化器和执行器不支持哈希join

如果支持的话，前面就散10亿的操作，如果join_buffer里维护的是一个哈希表的话，结果就是100万次hash查找。

 

业务端也可以模拟hash join：

1. select * from t1;取全部1000行数据，业务端放入一个hash结构;
2. select * from t2 where b>=1 and b<= 2000;获取表t2中满足条件的2000行数据；
3. 业务端这两个集合做计算，得到结果集。

 

上一篇：34 | 到底可不可以使用join？

下一篇：36 | 为什么临时表可以重名