王仕柱

子查询

我们都见过在查询语句中又嵌套了查询语句，那么这个嵌套的查询语句称为子查询

子查询以外的查询称为外层查询

子查询出现的位置

按照子查询出现的位置分为：

  • select子句中

    例如：

      select (select * from table_name limit 1);

  • from子句中

    例如：

      select x,y from (select pos_x as x,pos_y as y from t_player where player_id=1) as t

    from子句中的查询结果相当于一个表，MySQL把这种由子查询结果集组成的表称为派生表

  • where 或者 on子句中

    例如：

      select * from t_player where player_id in (select player_id from t_equipment where level > 100);

子查询返回的结果集

按照它们返回的不同结果集类型而把这些子查询分为不同的类型：

  • 标量子查询

    只返回一个单一值的子查询称为标量子查询。标量子查询可以作为一个单一值或者条件表达式的一部分出现在查询语句。例如：

      select (select level from t_player limit 1)
  SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);

  • 行子查询

    子查询返回一条记录，不过这条记录需要包含多个列（只包含一个列是标量子查询）。例如：

      SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

  • 列子查询

    列子查询就是查询出一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询。例如：

      SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

  • 表子查询

    表子查询就是查询结果既包含很多条记录，又包含很多个列。例如：

      SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

与外层查询关系来区分子查询

  • 不相关子查询

    如果子查询可以单独运行出结果，而不依赖于外层查询的值，把这个子查询称之为不相关子查询

  • 相关子查询

    如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询。例如：

      SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);

    子查询中有一个搜索条件是n1 = n2，n1是表t1的列，也就是说子查询的执行需要依赖于外层查询的值，所以这个子查询就是一个相关子查询

子查询在where条件语句中

  • 使用=、>、<、>=、<=、<>、!=、<=>作为条件运算符时

    这种情况的子查询只能是标量子查询或者行子查询，也就是子查询的结果只能返回一个单一的值或者只能是一条记录。例如：

      SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);
  SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

  • [NOT] IN/ANY/SOME/ALL

    对于列子查询和表子查询来说，它们的结果集中包含很多条记录，这些记录相当于是一个集合，可以通过这些关键字与集合形成条件表达式。例如：

      SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);
	
  SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);
	
  SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);

    顺便提一下ANY、SOME、ALL的语意：

    ANY和SOME是同义词，以这个查询为例：

      SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);

    意思是只要m1大于结果集中的某个值，那么表达式结果为TRUE，否则为FALSE。就这个查询而言就是m1大于结果集的最小值即为TRUE。所以这个子查询可以替换为:

      SELECT * FROM t1 WHERE m1 > (SELECT MIN(m2) FROM t2);

    = ANY相当于判断子查询结果集中是否存在某个值和给定的操作数相等，它的含义和IN是相同的

    ALL的语意用这个查询为例：

      SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);

    意思是m1大于结果集中的所有值，那么表达式结果为TRUE，否则为FALSE。就这个查询而言就是m1大于结果集的最大值即为TRUE。所以这个子查询可以替换为:

      SELECT * FROM t1 WHERE m1 > (SELECT MAX(m2) FROM t2);

  • EXISTS子查询

    EXISTS的语意是是否存在，例如下面查询：

      SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE m2 > 1);

    它的语意就是子查询是否有记录，有记录即为TRUE，否则为FALSE。对于子查询的结果列、子查询的行并不关心

子查询语法

  • 子查询必须用小括号扩起来

  • 在SELECT子句中的子查询必须是标量子查询

  • 对于[NOT] IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT语句

  • 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询

    例如这样的查询：

      DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1);

标量子查询、行子查询的执行方式

对于不相关标量子查询或者行子查询来说，它们的执行方式是简单的：

  1. 先单独执行子查询
  2. 将上一步子查询得到的结果当作外层查询的参数再执行外层查询

对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作对两个单表查询就可以

对于相关的标量子查询或者行子查询来说，以下面查询为例：

SELECT * FROM t1 WHERE m1 = (SELECT m2 FROM t2 WHERE t1.n1 = n2);

它们的执行方式是：

  1. 先从外层查询中获取一条记录，本例中也就是先从t1表中获取一条记录。
  2. 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从t1表中获取的那条记录中找出t1.n1列的值，然后执行子查询
  3. 最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃
  4. 再次执行第一步，获取第二条外层查询中的记录，依次类推

IN子查询

对于不相关的IN子查询，要考虑的是子查询的结果集大小，如果子查询的结果集中的记录条数很少， 那么把子查询和外层查询分别看成两个单独的单表查询效率还是很高的，但是如果子查询的结果集太多的话，就会导致一些问题：

  1. 结果集太多，可能内存中都放不下

  2. 对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这就导致：

    • 无法有效的使用索引，只能对外层查询进行全表扫描

    • 在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长

MySQL的做法是不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：

  • 该临时表的列就是子查询结果集中的列

  • 写入临时表的记录会被去重

    IN语句是判断某个操作数在不在某个集合中，集合中的值重不重复对整个IN语句的结果并没有什么关系，所以在将结果集写入临时表时对记录进行去重可以让临时表变得更小

  • 子查询结果集小于系统变量tmp_table_size或者max_heap_table_size设定的值，会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引

    IN语句的本质就是判断某个操作数在不在某个集合里，如果集合中的数据建立了哈希索引，那么这个匹配的过程就是超级快的

  • 如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引

将子查询结果集中的记录保存到临时表的过程称之为物化Materialize。 可以把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引）， 通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能

对于这个查询：

SELECT * FROM t1 WHERE n1 IN (SELECT n2 FROM t2 where m1 > 1);

若子查询建立了物化表后，那么就有了t1表与物化表，这个查询的语意是：

  1. t1表n1列的值与物化表的列值相等，那么条件成立
  2. 物化表的列值与t1表n1列的值相等，那么条件成立

从语意上和内连接一样，关于连接可以参考这篇文章，这个查询可以修改为：

SELECT t1.* FROM t1 INNER JOIN 物化表 ON n1=物化表列;

转化成内连接的目的是使用查询优化器，查询优化器可以评估不同连接顺序需要的成本是多少，查询优化器会通过运算来选择成本更低的方案来执行查询，关于这个运算过程同样可以参考这篇文章

上面是将子查询结果转换成了物化表，再转换成内连接进而查询，如果去掉转换成物化表的过程，直接转换成内连接，上面这个查询可以写成：

SELECT t1.* FROM t1 INNER JOIN t2 ON n1=n2 WHERE t2.m1 > 1;

如果按照这种方式转换成内连接查询，按照连接条件n1=n2得到的结果数量可能比实际多，我们既希望用到查询优化器又要保证结果正确，这时要用到MySQL内部的半连接semi-join，我们看看半连接有哪些策略：

  • 子查询表上拉Table Pullout execution strategy

    当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中。 就像刚刚转换内连接的例子：

      SELECT * FROM t1 WHERE n1 IN (SELECT n2 FROM t2 where m1 > 1);
  SELECT t1.* FROM t1 INNER JOIN t2 ON n1=n2 WHERE t2.m1 > 1;

    这里必须保证子查询列表里n2是主键或者唯一索引列，才能保证按照连接条件n1=n2得到的结果没有重复记录

  • 重复值消除DuplicateWeedout execution strategy

    这个策略是当转换为半连接查询后，t1表中的某条记录可能在t2表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中， 为了消除重复，我们可以建立一个以t1表的id为主键的临时表，这样在执行连接查询的过程中，每当某条t1表中的记录要加入结果集时，就首先把这条记录的id值加入到这个临时表里， 如果添加成功，说明之前这条s1表中的记录并没有加入最终的结果集，现在把该记录添加到最终的结果集；如果添加失败，说明之前这条t1表中的记录已经加入过最终的结果集， 这里直接把它丢弃，这种使用临时表消除semi-join结果集中的重复值的方式称之为DuplicateWeedout

  • 松散索引扫描LooseScan execution strategy

    对于这样的查询：

      select t1.* from t1 where n3 in (select n2 from t2 where n2 > 1 and n2 < 10)

    当n2是个二级索引时，在子查询中，对于t2表的访问可以使用到n2列的索引，而恰好子查询的查询列表处就是n2列，这样在将该查询转换为半连接查询后， 如果将t2作为驱动表执行查询,对于当n2有多条值相同的记录时，那么只取这些值相同中一条到t1中查询。

    这种虽然是扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为松散索引扫描

  • Semi-Join Materialization execution strategy

    对于上面提到的物化表转内连接的查询其实也是半连接，只不过物化表没有重复记录

  • FirstMatch execution strategy

    这个策略和相关子查询的执行方式一样，即：

    1. 先取一条外层查询的中的记录
    2. 然后到子查询的表中寻找符合匹配条件的记录
    3. 如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，
    4. 如果找不到则把该外层查询的记录丢弃掉；
    5. 然后再开始取下一条外层查询中的记录，重复上边这个过程

对于某些使用IN语句的相关子查询，先将查询转换成半连接，然后再使用半连接的策略

由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询

semi-join条件

并不是所有包含IN子查询的查询语句都可以转换为semi-join，下列的查询格式可以转换为semi-join:

SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...
	
SELECT ... FROM outer_tables WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...

即：

  • 该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON子句中出现

  • 外层查询也可以有其他的搜索条件，只不过和IN子查询的搜索条件必须使用AND连接起来

  • 该子查询必须是一个单一的查询，不能是由若干查询由UNION连接起来的形式

  • 该子查询不能包含GROUP BY或者HAVING语句或者聚集函数

结合能够转换成semi-join的条件，同样有一些不能转换成semi-join的情况：

  • 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来

      SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) OR ...

  • 使用NOT IN而不是IN的情况

      SELECT ... FROM outer_tables WHERE expr NOT IN (SELECT ... FROM inner_tables ...)

  • 在SELECT子句中的IN子查询的情况

      SELECT expr IN (SELECT ... FROM inner_tables ...) FROM outer_tables

  • 子查询中包含GROUP BY、HAVING或者聚集函数的情况

      SELECT ... FROM outer_tables WHERE expr IN (SELECT COUNT(*) FROM inner_tables GROUP BY c1) OR ...

  • 子查询中包含UNION的情况

      SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables_1 WHERE c1=1 UNION SELECT ... FROM inner_tables_2 WHERE c1=1)

MySQL对于不能使用semi-join的情况，通常使用如下方案：

  • 对于不相关子查询将子查询结果集物化

    例如这个查询

      SELECT ... FROM outer_tables WHERE expr NOT IN (SELECT ... FROM inner_tables ...)

  • 不管子查询是相关的还是不相关的，都可以把IN子查询尝试转为EXISTS子查询

    WHERE 或者 ON子句中，可以将IN替换为EXISTS，例如：

      outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
	
  EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr)

    转换的目的是尽量使用索引，例如这个查询(假设n1列为索引列)：

      SELECT * FROM t1 WHERE n1 IN (SELECT t2.n1 FROM t2 WHERE t1.c1 = t2.c2) or m1 > 100;
  SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.c1 = t2.c2 AND t2.n1 = t1.n1) or m1 > 100;

    如果IN子查询不满足转换为semi-join的条件，又不能转换为物化表或者转换为物化表的成本太大，那么它就会被转换为EXISTS查询， 在MySQL5.5以及之前的版本没有引进semi-join和物化的方式优化子查询时，优化器都会把IN子查询转换为EXISTS子查询