拿什么拯救你，我的MySQL子查询

mysql的子查询一直被人所诟病，主要原因是mysql子查询奉行的是“由外到内”的政策。首先运行外部表达式，然后把外部表达式的值传递给子查询，子查询再将自己的运行结果与外部表达式的值进行比较，匹配的话则返回记录，这样在外部表的记录比较多的情况下，效率是很低的。所以许多的公司都在自己的开发规范中明确规定了不能使用子查询,一律以表关联操作来代替。其实mysql对子查询还是做了一些优化的，特别是在mysql5.6中，推出了多个针对子查询的优化措施，一个很重要的技术就是semi_join。

所谓的semi_join是相对inner join而说的，在inner join的场景下，左节点的表必须对右节点的表中每条匹配记录都返回一条记录，这样在右节点有多条记录匹配的话，左节点也必须返回多条重复记录，会造成一些不必要的性能损失。

      而在另外一些场景中，我们只关心的是在另外一个表中是否存在相匹配的记录，而不是有几条记录匹配。这时候我们就可以使用到semi_join的技术了。在semi_join的方法中，无论右节点有几条记录匹配，左节点都只返回一条相同记录。并且semi_join只返回外部查询表中的记录，而不会返回内部查询表中的记录。这也是我们进行semi_join优化的基础，即我们只需要从semi_join中获取最少量的，足以对外部表进行筛选的信息就够了。所谓的最少量，体现在优化上就是如何去重。

假设有两张表class和roster

class表中记录的是课程号和课程名称，字段class_num具有唯一值。

roster记录的是课程号和参加该课程的学号，多个学生可能参加同一门课程，所以字段class_num有多个值。

如果要查看哪些课程有学生参加了，可以使用inner join的方式

mysql> SELECT class.class_num, class.class_name FROM class INNER JOIN roster WHERE class.class_num = roster.class_num;      

在这里class_num 等于3的记录重复了，而我们这时候只需要知道有哪些课程有学生参加了，并不需要知道有多少个学生参加，所以这里的重复记录对我们来说并没有意义。虽然我们可以用distinct来去重，但这显然会影响性能，这时候我们就可以用到子查询了。

mysql>  SELECT class_num, class_name FROM class WHERE class_num IN (SELECT class_num FROM roster);  

这个子查询中有两个要素需要注意：

1. 所查询的记录的字段都来自于class表，roster表中的字段并没有参与

2. 虽然roster表中有多条记录匹配，但class表中只返回一条记录

下面是mysql 重构后的sql的形式，提示已经使用了semi_join

/* select#1 */ select`employees`.`class`.`class_num` AS `class_num`,

`employees`.`class`.`class_name` AS`class_name` from `employees`.`class` 

semi join (`employees`.`roster`) where(`employees`.`class`.`class_num` = 

`<subquery2>`.`class_num`) 

mysql支持的semi_join的策略有4个，分别如下：

1. DuplicateWeedout: 像表连接一样运行semi_join，然后用临时表来消除重复记录。

2. FirstMatch: 当扫描内部表时，发现有多条记录与外部表匹配时，只返回第一条记录，而不是全部返回。这种方式减少了许多不必要的记录的产生。

3. LooseScan: 把inner-table数据基于索引进行分组，取每组第一条数据进行匹配。

4. Materialize: 将inner-table去重固化成一个带索引的临时表，索引可以被用来去除重复记录，或者被用来与外部表进行关联。

1. 必须是形式类似于下面的IN子查询

  SELECT ...

   FROMot1, ...

  WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);

2. 子查询必须是个单独的SELECT操作，而不能用UNION进行合并

3. 子查询不能使用GROUP BY或HAVING以及其它聚集函数

4. 不能够在ORDER BY 中跟随LIMIT

5. 外部表数量与内部表的数量只和不能超过最大表关联的数量

6. optimizer_switch参数中的semi_join必须设置为ON

1. 当使用了DuplicateWeedout的策略时，会在Extra列中显示Start temporary和End temporary的提示

2.当使用了FirstMatch策略时，会在Extra列中显示 FirstMatch(tbl_name)的提示

3.当使用了 LooseScan策略时，会在Extra列中显示 LooseScan(m..n)的提示，m和n是索引分组号

4.当使用了Materialize策略时，分两种情况：

  1) 在5.6.7之前，如果是单表的话会在Extra列中显示Materialize的提示；如果是多表的话会在Exatra列中显示 Start materialize and End materialize的提示；

 2) 在5.6.7之后，会在select_type列中显示 MATERIALIZED ，并在rows列中显示物化的行数。