Mysql单表访问方法，索引合并，多表连接原理，基于规则的优化，子查询优化( 六 ) _生活百科

这种将子查询结果集中的记录保存到临时表的过程称作物化表

物化表转连接
假如上面物化表名称为tmp ，并且为查询结果建立了索引pk ， select * from s1 where key1 in (select f from s2 where key3='a')查询就是判断s1中key1是否在tmp表的主键中，建立了索引那么判断是非常块的。但是mysql还会继续优化为内连接，如
select s1.* from s1 inner join tmp on s1.key1 = tmp.pk 转换为内连接后，查询优化器，可以评估使用不同连接顺序的成本，并选择成本最小的查询方式

子查询优化为半连接

转换为物化表，再执行查询具备建立临时表的成本。
select * from s1 where key1 in (select f from s2 where key3='a')可以被优化为
select s1.* from s1 inner join s2 on s1.key1 = s2.f where s2.key3 = 'a'
但是这存在s2中满足s2.key3 = 'a' 存在多个重复值f ，导致s1中一条记录连接相同f值多条记录的情况，失去了in子句去重的特性
为了解决这个问题，引入了半连接的操作，将s1和s2进行半连接意思就是：对于s1中某条记录来说，只关心在s2表中是否存在匹配的记录，而不关心具备有多少个记录与之匹配，最终结果集只保留s1表中的记录
那么如何实现半连接
1. Table pullout 子查询中表上拉
  如果in中的子查询查询的是被驱动表的唯一索引，或者主键列时，直接将in子句优化为内连接
  select * from s1 where key1 in (select f from s2 where key3='a')如果f是主键或者唯一索引，那么直接优化为select s1.* from s1 inner join s2 on s1.key1 = s2.f where s2.key3 = 'a' ，因为f列中的值本身就是不重复的
2. Duplicate weedout 重复值消除
  还是优化为内连接，即使f列存在重复值，也直接执行查询，但是执行结果使用临时表存储驱动表的主键id ，消除在子查询中查询到的重复值导致内连接产生重复行的问题
3. LooseScan 松散扫描
  首先将in优化成select s1.* from s1 inner join s2 on s1.key1 = s2.f where s2.key3 = 'a'内连接，然后将s2作为驱动表，这时候先执行select f from s2 where s2.key3= 'a' ，可能得到多个重复的f ，但是只取第一个去被驱动表s1中匹配，虽然是扫描索引，但是只取第一条记录执行匹配操作的方式称为松散扫描
4. Semi-join Materialzation 半连接物化
  就是上面提到的，将in中子查询查询结果物化成一张表，然后执行连接查询
5. FirstMatch 首次匹配
  这个也很好理解，相当于在嵌套循环连接中，先从外层查询中取一条记录，然后到子查询表中找到符合的记录，如果能找到一条那么加入到结果集并停止寻找多条，循环往复直到结束。