上面介绍了两种算法,分别是Index Nested-Loop Join和Block Nested-Loop Join
但是这并不是MySQL想要提供给用户的最终抉择
首先是两张表t1 t2来进行展开
| create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1; drop procedure idata; delimiter ;; create procedure idata() begin declare i int; set i=1; while(i<=1000)do insert into t1 values(i, 1001-i, i); set i=i+1; end while; set i=1; while(i<=1000000)do insert into t2 values(i, i, i); set i=i+1; end while; end;; delimiter ; call idata(); |
在表t1中 id和a是相反的
在t2中,有100万条数据
有了一个新的优化 Multi-Range Read的优化
这个优化的核心在于
首先说,InnoDB采用了B+树的结构类型
也就是说,在InnoDB中
我们在一个普通索引上查到了主键id的值,然后回到主键索引上查询整行的数据
然后有这个回表的操作是一行行的查询,
效率必然不如批量的查数据
假设,我们执行了select * from t1 where a>=1 and a<=100;
如果按照a的顺序去查询索引表,那么即为可能是无序的主键id
但是大部分的数据都是按照主键自增的顺序插入得到的,所以按照主键id来顺序查找的话,就是有序的,可能用顺序读来提升性能
MRR的优化思路就这样
1,根据索引a,一次性的查询出所有满足条件的顺序,将id放入read_rnd_buffer的
2.然后进行id递增排序
3.然后将排序后的id数组,去主键索引id中查询记录
这里read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的,在步骤1里面,,如果已经放满了,就会先执行,2 3
然后清空read_rnd_buffer
如果想要稳定的使用MRR优化,还需要开启set optimizer_switch=”mrr_cost_based=off”
让其持久开启
上面说明使用了MRR优化
也就是说MRR能够提升性能的关键,就是查询一定范围的值,然后排序,需要有足够的数量,才能进行体现顺序性的优势
上述说的MRR更加适合于无索引的优化
我们还引入了Batched Key Access
BKA算法对NLJ算法的优化
因为原本的NJL算法,是从驱动表中,一行行的取出a的值,然后利用索引,到表t2做join
那么可以一次性的多传一些值给被驱动表
先把驱动表数据取出来一部分,然后放在一个临时内存中,就是join_buffer中
在NLJ中,因为使用了索引,所以join_buffer在其中没有使用,BKA正好使用这个临时空挡
在join_buffer中,放入的数据是P1-P100,表示的是会去查询需要的字段,但是join_buffer放不下的话,就会拆分成多次来执行
开启BKA需要设置,
set optimizer_switch=’mrr=on,mrr_cost_based=off,batched_key_access=on’;
对于BNL的优化
因为BNL中,可能存在对一个冷表多次查询的情况
多次扫描被驱动表,占用了磁盘IO
判断join条件,需要进行M*N此对比
导致Buffer Pool的数据被污染了
这样,如果发现非要使用BNL算法,也最好先给被驱动表加上索引,转为BKA算法
假如没法使用BKA算法的
可以考虑其他的数据库使用的一种优化方式,在MySQL中可能暂时不支持
就是hash join
如果join buffer中维护的不是一个无序的数组,而是一个hash表的话,那么执行语句就快了吧
但是Mysql没有提供
如果这种优化的思路执行的话
1.select * from t1.取得t1的所有数据,在join buffer中存入一个hash结构
2.然后查询被驱动表中的所有满足数据,获取所有满足条件的数据
3.拿着着2000行数据,到业务端进行hash匹配,符合匹配的,拿其的字段来匹配,最后加入结果集
理论上,这个过程会很快
这章总结下来说,
BNL算法效率低,可以通过加索引编程BKA优化
BKA优化是MySQL默认支持的,可以直接使用
假如有如下的一个语句,如何创建索引
select * from t1 join t2 on(t1.a=t2.a) join t3 on (t2.b=t3.b) where t1.c>=X and t2.c>=Y and t3.c>=Z;
首先
尽可能的缩减过滤条件,选择三个表上的c字段建立索引
那么选定了驱动表,就需要在被驱动表上建立索引
如果是t1 t2 t3这样的顺序,就是t2的a和t3的b上建立索引
如果是t3 t2 t1这样的顺序,就是t2的b和t1的a上建立索引
如果是t2是驱动表的话,就需要额外评估另外两个条件的过滤情况