如何优化JOIN连接？

实验环境

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

结论(前提：可以使用被驱动表的索引)：
1.使用join语句，性能比强行拆成多个单表执行sql语句的性能要好(减少sql的执行次数)
2.小表驱动大表 (驱动表是主动发起查询的表，被驱动表是根据on条件被动被查询的表)
而当被驱动表上没有可用的索引的时候
MySQL将这次join查询调整使用BNL算法， 开辟一块线程级别的内存join buffer，因为是内存的操作，性能会比SNL算法速度会快很多，性能也更好。此时候无论选择大表还是小表做驱动表，执行耗时是一样的。调大join_buffer_size可增加查询性能，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少
但如果使用BNF算法，扫描行数就会很多，尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源，所以这种join尽量不要使用。总是要用“小表”作为驱动表！

什么才称为“小表”，
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

============================================================================

优化join语句

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();

拓展： MRR顺序读优化，需要设置MySQL的优化器策略 set optimizer_switch="mrr_cost_based=off
MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势。
MySQL5.6后引入的BKA算法，是对NLJ算法的优化
启用BKA算法，
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'; 其中，前两个参数的作用是要启用 MRR。这么做的原因是，BKA 算法的优化要依赖于 MRR。

有时候你确实会碰到一些不适合在被驱动表上建索引的情况。比如

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

经过过滤之后，表t2需要参与join的只有2000行数据，如果这条语句同时是一个低频的语句，那么再为这个语句在表t2的字段b上创建一个索引就很浪费了。

因为join buffer里面维护的是一个无序数组，所以每次都要遍历所有，时间复杂度为O(N),如果是一个哈希表的话，那么就不是10亿次判断，而是100万次hash查找。

这，也正是 MySQL 的优化器和执行器一直被诟病的一个原因：不支持哈希 join。并且，MySQL 官方的 roadmap，也是迟迟没有把这个优化排上议程。实际上，这个优化思路，我们可以自己实现在业务端。实现流程大致如下：select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构，比如 C++ 里的 set、PHP 的数组这样的数据结构。select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。理论上，这个过程会比临时表方案的执行速度还要快一些。如果你感兴趣的话，可以自己验证一下