mysql连接查询和子查询

简介

当多个表进行关联查询，就会产生连接查询和子查询。

SQL

//简单的连接查询语句
select student.* from student inner join mark on student.name=mark.name;

//简单的子查询语句
select student.* from student where name in (select name from mark)

连接查询原理

• 连接查询的执行过程
  简单点说就是一个嵌套循环。上面的连接查询有两种执行策略：一种是从student取一条数据s出来，然后到mark中取满足mark.name=s.name(将mark.name与常量比较)的行出来，反复执行这个过程；另外一种执行策略是交换上面的student和mark取数的顺序。先取数的那个表需要进行一次全表扫描（或者索引扫描），被称为驱动表，后取数的那个表称为被驱动表。
• 应该选择哪种执行顺序？
  假设student和mark的表记录数分别是s和m，全表扫描的代价分别是ks和km，student.name和mark.name都有索引，student.name和mark.name索引的单次等值检索代价分别是In^s 和In^m （假设索引字段大小相同）;那么两种策略的代价就分别是ks+sIn^m km+mIn^s 如果mark表是student表的n倍，则分别为ks+s(In^s +In^n ) kns+nsIn^s
  存储性能决定了k的大小（全表顺序扫描的成本系数）。

连接查询优化

• 建立索引：考虑在on和where条件中的列建立索引，或者联合索引；考虑在select选择的列上建立覆盖索引。
• 左连接的时候限制了驱动表是左表，限制了优化器选择右表作为驱动表的可能。但是如果连接条件中where条件中限制了被驱动表中的记录不可能是空，就可以转为内连接。

子查询原理

• 子查询分类：
  根据连接方式分为标量子查询和IN子查询。标量子查询的子查询只返回1行数据，外层查询和子查询使用=,>等符合连接；IN子查询使用关键字IN连接，这里主要讨论IN子查询。
  根据外层查询与子查询是否存在关联条件，分为相关子查询和不相关子查询。
• 子查询执行过程
  子查询执行过程分为物化表和半连接。对于不相关子查询，可以当作两个独立的查询执行，子查询的结果可以保存到物化表（临时表，不存在重复数据，较小就放在内存，较大就放在磁盘），然后再与外查询进行比较。由于物化表不存在重复数据，可以把IN子查询转为内连接查询。为了省去物化表的开销，子查询有时候可以转为连接查询。

方式	原理
table pullout	如果子查询的select中包含主键或者唯一索引，就不需要对数据去重，可以直接转内连接。
duplicate weedout	建立一个临时表存放外层查询的主键，对结果去重。
LooseScan	索引扫描，只取子查询的键值相同的第一条记录去执行匹配。
semi-join Materialization	物化子查询，然后物化表与外层表做连接查询。
FirstMatch	对于相关子查询，无法物化子查询，需要先从外层表取数，然后在子查询中找到第一条匹配的记录就退出。

• 子查询转连接查询的条件

//sql格式
select ... from outer_table where 
   expr in (select ... from inner_table ...) and ...

与其他条件使用and连接，不能有or连接，不能含group by,having子句。