之前说过在join时如果能使用到索引也就是说使用NLJ(Index Nested-Loop Join)算法时,性能还是不错的,但是在使用不到索引使用Block Nested-Loop Join(BNL)就不太好了,尤其是在大表join时,比较次数等于两个表参数join的行数的乘积,很消耗cpu资源。今天就来谈谈如何对这种情况做一些优化。

为了便于分析,先创建两个表,,并填充数据

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  -- 给表1填充1000条数据,并且a是逆序插入的    insert into t1 values(i, 1001-i, i);    set i=i+1;  end while;    set i=1;  while(i<=)do  -- 给表2填充100W条数据    insert into t2 values(i, i, i);    set i=i+1;  end while;end;; delimiter ; call idata();先介绍一下Multi-Range-Read优化,这个优化的主要目的是尽量使用顺序读盘。

假设,我们执行

select * from t1 where a>=1 and a<=100;正常顺序来说,通过索引a回表时,会先查a=1,id=1000,一直查到 a=100,id=991。但是因为大多数的数据都是按照主键递增顺序查询的,对磁盘的读取比较接近顺序读,能够提升性能,所以经过MRB优化时会先将id按照升序排列,然后一条一条回表。

致的执行流程为:

1.根据索引a,定位到满足条件的记录,将id值放入read_rnd_buffer中;

2.将read_rnd_buffer中的id进行排序;

3.排序后的id,依次到主键id索引中查记录,并作为结果集返回

read_rnd_buffer 的大小是由read_rnd_buffer_length控制的,如果read_rnd_buffer放满了会先执行2和3然后清空read_rnd_buffer,之后继续循环以上步骤。

比如要执行这个sql

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;使用临时表1.把表2中满足条件的数据放在临时表tmp中

2.给临时表tmp中加上索引

3.让表t1和tmp表做join查询

对应的sql如下

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);两种sql的执行效率大家可以具体的试一下,使用临时表肯定是要比直接join快很多的。

因为mysql的优化器和执行器不支持哈希 join,所以需要自己去在客户端实现。

大致思路如下:

1.select * from t1;取得表t1的全部1000行数据,在业务端村一个hash结构 ,比如php里面的dict这样的数据结构

2.select * from t2 where b >= 1 and b <= 2000;获取t2表中满足条件的2000条数据。

3.把2000行数据,一行 一行的取到业务端,到hash中找匹配的数据,如果满足条件就放到结果集中。

理论上会比临时方案表更快一些,感兴趣的可以自己动手实现一下。