测试数据

创建两个表t1和t2，它们格式完全相同，区别在与数据量t1是1k，t2是100k。另外需要注意的是三个字段的区别，id是主键，a是索引字段，b则无索引，下面我们会比较不同类型字段对查询的影响。

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;
create table t1 like t2;

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

insert into t1 (select * from t2 where id<=1000)

in

按主键id关联

in关联查询的是主键字段，位置无影响，优化器会主动优化

按索引字段a关联

如果in关联查询的是索引字段，表位置会影响优化策略

首先mysql会选择外层表为驱动表，in包含的表则为被驱动表，这不会随着数据量的变化而变化。确定驱动表后，如果驱动表是小表，则使用FirstMatch策略匹配被驱动的大表；如果驱动表是大表，那就使用LooseScan策略匹配被驱动的小表。

按普通字段b关联

in关联查询对非索引字段，使用物化(Materialization)优化策略

Materialization（直译是物化，可以理解成将子查询‘物化’成表）是一种通过创建临时表来间接关联的优化策略。在执行的过程中，第一次需要子查询结果时，将整个子查询的结果保存到一个内存表（过大时也会落地磁盘），以后需要子查询时直接从表中取值，甚至将表做成hash映射进一步提高效率。

FirstMatch，LooseScan，Materialization都被称为半连接(Semi Join)策略

exists

比起in查询，exists提供的优化手段就相对单薄，同样的查询我们换成exists的形式：

exists默认的优化手段段仅停留在对主键/索引的使用上，没有in关联提供的半连接优化策略

join

直接使用join语句的关键在于驱动表的选择，从下面的测试情况看来，left join会固定使用左表作为驱动表。如果使用主键/索引字段关联被驱动表，那么会触发树搜索。如果使用非索引字段关联被驱动表，那么就需要全表扫描。

join_buffer

值得注意的是，在使用非索引字段来做关联join的时候，对被驱动表的扫描使用的Block Nested Loop方法。 join关联查询中，第一步是从驱动表取出一条数据，第二步是去被驱动表中找出关联上的数据，第三部则是将关联上的数据拼成结果返回。对于第二步，如果对被驱动表做逐条判断性能太低，批量将被驱动表中的数据放入内存做计算效率显然更高。但由于全表扫描，无法预知被驱动表的数据量是否会撑满内存，因此使用分块的方式批量读取（这也是Block的来源）。临时存被驱动表分块数据的空间叫做join_buffer，由join_buffer_size参数控制。当join操作太慢时，可以通过适当增加此参数来优化。

Mysql in/exist/join 策略分析

TOC