在一个查询语句 A 里的某个位置也可以有另一个查询语句 B，这个出现在 A 语句的某个位置中的查询 B 就被称为子查询，A 也被称之为外层查询。子查询可以在一个外层查询的各种位置出现：

  • SELECT 子句中

    SELECT (SELECT m1 FROM e1 LIMIT 1)

其中 (SELECT m1 FROM e1 LIMIT 1) 就是子查询。

  • FROM 子句中

    SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM e2 WHERE m2 > 2) AS t

其中 (SELECT m2 + 1 AS m, n2 AS n FROM e2 WHERE m2 > 2) 是子查询，这里可以把子查询的查询结果当作是一个表，子查询后边的 AS t 表明这个子查询的结果就相当于一个名称为 t 的表，这个名叫 t 的表的列就是子查询结果中的列，比如例子中表 t 就有两个列：m 列和 n 列。这个放在 FROM 子句中的子查询本质上相当于一个表，但又和我们平常使用的表有点儿不一样，MySQL 把这种由子查询结果集组成的表称之为派生表。

  • WHERE 子句中

把子查询放在外层查询的 WHERE 子句中是我们最常用的一种使用子查询的方式，比如：

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2)

这个查询表明我们想要将 (SELECT m2 FROM e2) 这个子查询的结果作为外层查询的 IN 语句参数，整个查询语句的意思就是我们想找 e1 表中的某些记录，这些记录的 m1 列的值能在 e2 表的 m2 列找到匹配的值。

  • ORDER BY子句、GROUP BY子句中

这种写法虽然语法支持，但是没有什么意义。

子查询类型

子查询可以按照返回的结果集、与外层查询关系可以分成不同的子查询，还有特殊的 IN/ANY/SOME/ALL 子查询，EXISTS 子查询。

按返回结果集区分

因为子查询本身也算是一个查询，所以可以按照它们返回的不同结果集类型而把这些子查询分为不同的类型：

  1. 标量子查询

那些只返回一个单一值的子查询称之为标量子查询，比如这样：

SELECT (SELECT m1 FROM e1 LIMIT 1) SELECT FROM e1 WHERE m1 = (SELECT MIN(m2) FROM e2) SELECT FROM e1 WHERE m1 < (SELECT MIN(m2) FROM e2)

这些查询语句中的子查询都返回一个单一的值，也就是一个标量。这些标量子查询可以作为一个单一值或者表达式的一部分出现在查询语句的各个地方。

  1. 行子查询

顾名思义，就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。比如这样：

SELECT * FROM e1 WHERE (m1, n1) = (SELECT m2, n2 FROM e2 LIMIT 1)

其中的 (SELECT m2, n2 FROM e2 LIMIT 1) 就是一个行子查询，整条语句的含义就是要从e1 表中找一些记录，这些记录的 m1 和 n1 列分别等于子查询结果中的 m2 和 n2 列。

  1. 列子查询

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

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2)

其中的 (SELECT m2 FROM e2) 就是一个列子查询，表明查询出 e2 表的 m2 列的值作为外层查询 IN 语句的参数。

  1. 表子查询

顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列，比如这样：

SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2)

其中的 (SELECT m2, n2 FROM e2) 就是一个表子查询，这里需要和行子查询对比一下，行子查询中我们用 LIMIT 1 来保证子查询的结果只有一条记录，表子查询中不需要这个限制

按与外层查询关系

按与外层查询关系来区分子查询，可以分为相关子查询和不相关子查询：

  1. 不相关子查询

如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。我们前边介绍的那些子查询全部都可以看作不相关子查询。

  1. 相关子查询

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

SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE e1.n1 = e2.n2)

例子中的子查询是 (SELECT m2 FROM e2 WHERE n1 = n2)，可是这个查询中有一个搜索条件是 n1 = n2，别忘了 n1 是表 e1 的列，也就是外层查询的列，也就是说子查询的执行需要依赖于外层查询的值，所以这个子查询就是一个相关子查询。

IN/ANY/SOME/ALL子查询

对于列子查询和表子查询来说，它们的结果集中包含很多条记录，这些记录相当于是一个集合，所以就不能单纯的和另外一个操作数使用操作符来组成布尔表达式了，MySQL 通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式：

  1. IN/NOT IN

具体的语法形式如下：操作数 [NOT] IN (子查询)

这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中，比如下边的查询的意思是找出 e1 表中的某些记录，这些记录存在于子查询的结果集中：

SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2)

  1. ANY/SOME

具体的语法形式如下：操作数 比较符 ANY/SOME (子查询)

这个布尔表达式的意思是只要子查询结果集中存在某个值和给定的操作数做比较操作，比较结果为 TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为 FALSE。比方说下边这个查询：

SELECT * FROM e1 WHERE m1 > ANY(SELECT m2 FROM e2);

这个查询的意思就是对于 e1 表的某条记录的 m1 列的值来说，如果子查询 (SELECT m2 FROM e2) 的结果集中存在一个小于 m1 列的值，那么整个布尔表达式的值就是 TRUE，否则为 FALSE，也就是说只要 m1 列的值大于子查询结果集中最小的值，整个表达式的结果就是 TRUE，所以上边的查询本质上等价于这个查询：

SELECT * FROM e1 WHERE m1 > (SELECT MIN(m2) FROM e2)

  1. ALL

具体的语法形式如下：操作数 比较操作 ALL(子查询)

这个布尔表达式的意思是子查询结果集中所有的值和给定的操作数做比较操作比较结果为 TRUE，那么整个表达式的结果就为 TRUE，否则整个表达式的结果就为 FALSE。比方说下边这个查询：

SELECT * FROM e1 WHERE m1 > ALL(SELECT m2 FROM e2)

这个查询的意思就是对于 e1 表的某条记录的 m1 列的值来说，如果子查询 (SELECT m2 FROM e2) 的结果集中的所有值都小于 m1 列的值，那么整个布尔表达式的值就是TRUE，否则为 FALSE，也就是说只要 m1 列的值大于子查询结果集中最大的值，整个表达式的结果就是 TRUE，所以上边的查询本质上等价于这个查询：

SELECT * FROM e1 WHERE m1 > (SELECT MAX(m2) FROM e2)

EXISTS子查询

有的时候我们仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是什么，可以使用把 EXISTS 或者 NOT EXISTS 放在子查询语句前边，就像这样：

SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2)

对于子查询 (SELECT 1 FROM e2) 来说，我们并不关心这个子查询最后到底查询出的结果是什么，所以查询列表里填 *、某个列名，或者其他啥东西都无所谓，我们真正关心的是子查询的结果集中是否存在记录。也就是说只要 (SELECT 1 FROM e2) 这个查询中有记录，那么整个 EXISTS 表达式的结果就为TRUE。

子查询执行方式

对于不同的子查询，MySQL 的执行方式是不一样的。下面我们就来介绍一下不同的子查询在 MySQL 中的执行方式。

IN 子查询

如果该子查询是不相关 IN 子查询，比如下边这个查询：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2)

先单独执行 (SELECT order_note FROM s2) 这个子查询。然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询 SELECT * FROM s1 WHERE order_note IN (…)

如果该子查询是相关 IN 子查询，比如下边这个查询：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE s1.order_no= s2.order_no)

这个查询中的子查询中出现了 s1.order_no= s2.order_no 这样的条件，意味着该子查询的执行依赖着外层查询的值，先从外层查询中获取一条记录，本例中也就是先从 s1 表中获取一条记录，然后执行子查询。最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。再次执行第一步，获取第二条外层查询中的记录，依次类推（查询次数为外层查询与子查询作笛卡尔积）。

EXISTS子查询的执行

如果 [NOT] EXISTS 子查询是不相关子查询，可以先执行子查询，得出该 [NOT] EXISTS子
查询的结果是 TRUE 还是 FALSE，并重写原先的查询语句，比如对这个查询来说：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE expire_time= ‘a’) OR order_no> ‘2021-03-22 18:28:28’

因为这个语句里的子查询是不相关子查询，所以优化器会首先执行该子查询，假设该EXISTS 子查询的结果为 TRUE，那么接着优化器会重写查询为：

SELECT * FROM s1 WHERE TRUE OR order_no> ‘2021-03-22 18:28:28’

进一步简化后就变成了：

SELECT * FROM s1 WHERE TRUE

对于相关的 [NOT] EXISTS 子查询来说，比如这个查询：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.order_note = s2.order_note)

很不幸，这个查询只能按照我们想象中的那种执行相关子查询的方式来执行，即从外层查询的结果集中拿出一条记录，然后执行一次子查询。不过如果[NOT] EXISTS 子查询中如果可以使用索引的话，那查询速度也会加快不少。

MySQL 对 IN 子查询的优化

对于相关的标量子查询或行子查询和不相关的标量子查询或行子查询来说，MySQL 内部的执行和我们上面说的一模一样，对于两种使用标量子查询以及行子查询的场景中，MySQL 优化器的执行方式对我们来说并没有什么新鲜的。但是 MySQL 对 IN 子查询的优化却大有搞头。

表物化

对于不相关的 IN 子查询，比如这样：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = ‘a’)

我们最开始的感觉就是这种不相关的 IN 子查询和不相关的标量子查询或者行子查询是一样的，都是把外层查询和子查询当作两个独立的单表查询来对待。但是 MySQL 为了优化 IN 子查询下了很大力气，所以整个执行过程并不像我们想象的那么简单。对于不相关的IN 子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率很高，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：

  1. 子查询结果集太多，可能内存都放不下。
  2. 对于外层查询来说，如果子查询的结果集太多，那就意味着 IN 子句中的参数特别多，这就导致：无法有效的使用索引（查找每一条记录都要从 IN 字句中取出所有的结果集到 B+树中找一遍，最后还要进行回表），可能外层查询进行全表扫描成本还更低。

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

SELECT * FROM tbl_name WHERE column IN (a, b)

这样相当于需要对 tbl_name 表中的每条记录判断一下它的 column 列是否符合 column = a OR column = b。在IN子句中的参数比较少时这并不是什么问题，如果 IN 子句中的参数比较多时，比如这样：

SELECT * FROM tbl_name WHERE column IN (a, b, c …, …)

那么这样每条记录需要判断一下它的 column 列是否符合 column = a OR column = b OR
column = c OR …，这样性能耗费可就多了。

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

  1. 该临时表的列就是子查询结果集中的列。
  2. 写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引。

一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+树索引。

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

semi-join

将子查询进行物化之后再执行查询都会有建立临时表的成本，能不能不进行物化操作直接把子查询转换为连接呢？让我们重新审视一下上边的查询语句：

SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = ‘a’)

我们可以把这个查询理解成：对于 s1 表中的某条记录，如果我们能在s2表（准确的说是执行完 WHERE s2.order_no= ‘a’ 之后的结果集）中找到一条或多条记录，这些记录的order_note 的值等于 s1 表记录的 order_note 列的值，那么该条 s1 表的记录就会被加入到最终的结果集。这个过程其实和把 s1 和 s2 两个表连接起来的效果很像：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.order_note = s2.order_note WHERE s2.order_no= ‘a’

只不过我们不能保证对于 s1 表的某条记录来说，在 s2 表（准确的说是执行完 WHERE s2.order_no= ‘a’ 之后的结果集）中有多少条记录满足 s1.order_no = s2.order_no 这个条件，不过我们可以分三种情况讨论：

  • 情况一：对于 s1 表的某条记录来说，s2 表中没有任何记录满足 s1.order_note = s2.order_note 这个条件，那么该记录自然也不会加入到最后的结果集。
  • 情况二：对于 s1 表的某条记录来说，s2 表中有且只有1条记录满足 s1.order_note = s2.order_note 这个条件，那么该记录会被加入最终的结果集。
  • 情况三：对于 s1 表的某条记录来说，s2 表中至少有2条记录满足 s1.order_note = s2.order_note 这个条件，那么该记录会被多次加入最终的结果集。

对于 s1 表的某条记录来说，由于我们只关心 s2 表中是否存在记录满足 s1.order_no = s2.order_note 这个条件，而不关心具体有多少条记录与之匹配，又因为有情况三的存在，我们上边所说的 IN 子查询和两表连接之间并不完全等价。但是将子查询转换为连接又真的可以充分发挥优化器的作用，所以MySQL在这里提出了一个新概念 —- 半连接。

将 s1 表和 s2 表进行半连接的意思就是：对于 s1 表的某条记录来说，我们只关心在 s2 表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留 s1 表的记录。为了让大家有更直观的感受，我们假设 MySQL 内部是这么改写上边的子查询的：

SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.order_note = s2.order_note WHERE order_no= ‘a’;

注意： semi-join 只是在 MySQL 内部采用的一种执行子查询的方式，MySQL 并没有提供面向用户的 semi-join 语法。

怎么实现这种所谓的半连接呢？MySQL准备了好几种办法，比如 Table pullout （子查询中的表上拉）、DuplicateWeedout execution strategy （重复值消除）、LooseScan execution strategy （松散扫描）、Semi-join Materializationa半连接物化、 FirstMatch executio strategy （首次匹配）等等，我们重点介绍一下表上拉：

Table pullout—表上拉

当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的 FROM 子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中，比如假设 s2 中存在这个一个 key2 列，列上有唯一性索引：

SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = ‘a’)

由于 key2 列是 s2 表的一个唯一性二级索引列，所以我们可以直接把 s2 表上拉到外层查询的 FROM子 句中，并且把子查询中的搜索条件合并到外层查询的搜索条件中，上拉之后的查询就是这样的：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = ‘a’

为啥当子查询的查询列表处只有主键或者唯一索引列时，就可以直接将子查询转换为连接查询呢？因为主键或者唯一索引列中的数据本身就是不重复的嘛！所以对于同一条 s1 表中的记录，你不可能找到两条以上的符合 s1.key2 = s2.key2 的记录。

IN 子查询转 EXISTS 子查询

并不是所有包含 IN 子查询的查询语句都可以转换为 semi-join，对于不能转换的。不管子查询是相关的还是不相关的，都可以把 IN 子查询尝试转为 EXISTS 子查询。还是以上面的查询语句为例：

SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2 where s1.order_note = s2.order_note) OR insert_time > ‘2021-03-22 18:28:28’

该查询可以转为：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 where s1.order_note = s2.order_note AND s2.order_no= s1.order_no) OR insert_time > ‘2021-03-22 18:28:28’

为啥要转换呢？这是因为不转换的话可能用不到索引，转为 EXISTS子 查询时便可能使用到 s2 表的 idx_order_no 索引了。

EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2 where s1.order_note = s2.order_note) OR insert_time > ‘2021-03-22 18:28:28’; show WARNINGS;

image.png
通过执行以下语句可以看到，MySQL 确实把这个子查询转换为了 exists 子查询。