mysql中子查询的语法和执行过程

最新推荐文章于 2024-08-19 16:45:00 发布

韩朝洋

最新推荐文章于 2024-08-19 16:45:00 发布

阅读量1.1k

点赞数 9

分类专栏： # 从根儿上理解MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_41534540/article/details/139336381

版权

从根儿上理解MySQL 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

大家好。我们在日常开发过程中，肯定都经常用到了子查询。今天我们就来聊一下mysql中子查询的一些语法以及子查询的执行过程。

一、子查询的语法

首先在开讲之前，我们先创建t1、t2两张表，并分别在表中插入三条数据，方便我们下面内容的讲解。

在这里插入图片描述

下面我们来了解一下什么是子查询：

在一个查询语句里的某个位置可以有另一个查询语句，这个出现在某个查询语句的某个位置中的查询就被称为子查询。

子查询可以在外层查询的各个位置出现，下面我们来分别说一下这几个位置：

1. 在select子句中

在这里插入图片描述

上图sql中（select m1 from t1 limit 1）就是在这条sql select子句中的子查询。

2. 在from子句中

在这里插入图片描述
上图sql中（SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2）就是在FROM子句中的子查询。我们可以把子查询的查询结果当作是一个表，子查询后边的AS t表明这个子查询的结果就相当于一个名称为t的表，这个名叫t的表的列就是子查询结果中的列。这种由子查询结果集组成的表称之为派生表。

3. WHERE 或 ON 子句中

在这里插入图片描述

上图sql中（SELECT m2 FROM t2）)这个子查询的结果作为外层查询的IN语句参数。

4. ORDER BY 子句中

这种情况下的子查询意义不大，这里我们不说了。

5. GROUP BY 子句中

这种情况下的子查询意义也不大，这里我们也不说了。

1. 按返回的结果集区分子查询

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

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

SELECT (SELECT m1 FROM t1 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 m2, n2 FROM t2 LIMIT 1）就是一个行子查询，整条语句的含义就是要从t1表中找一些记录，这些记录的m1和n2列分别等于子查询结果中的m2和n2列。

列子查询： 列子查询就是查询出一个列的数据，不过这个列的数据需要包含多条记录。比如：

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

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

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

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

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

2. 按与外层查询关系来区分子查询

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

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

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

例子中的子查询是(SELECT m2 FROM t2 WHERE n1 = n2) ，而n1 是表t1的列，也就是外层查询的列，也就是说子查询的执行需要依赖于外层查询的值，所以这个子查询就是一个相关子查询。

3. 子查询在布尔表达式中的使用

我们平时用子查询最多的地方就是把它作为布尔表达式的一部分来作为搜索条件用在WHERE子句或者ON子句里。我们这里来总结一下子查询在布尔表达式中的使用场景。

1. 使用=、>、<、>=、<=、<>、!=、<=>作为布尔表达式的操作符**

我们就把这些操作符称为comparison_operator，所以子查询组成的布尔表达式就长这样：

操作数 comparison_operator (子查询)

这里的操作数可以是某个列名，或者是一个常量，或者是一个更复杂的表达式，甚至可以是另一个子查询。注意：这里的子查询只能是标量子查询或者行子查询，也就是子查询的结果只能返回一个单一的值或者只能是一条记录。比如：

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

2. [NOT] IN/ANY/SOME/ALL子查询

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

操作数 [NOT] IN (子查询)

这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中，比如：

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

3. ANY/SOME（ ANY 和 SOME 是同义词）

具体的语法形式如下：

操作数 comparison_operator ANY/SOME(子查询)

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

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

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

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

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

4. ALL

具体的语法形式如下：

操作数 comparison_operator ALL(子查询)

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

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

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

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

5. EXISTS子查询

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

[NOT] EXISTS (子查询)

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

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

4. 子查询语法注意事项

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

不扩起来的子查询是非法的，比如：

在这里插入图片描述

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

如果子查询结果集中有多个列或者多个行，都不允许放在SELECT子句中。比如：
在这里插入图片描述

3. 想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1语句来限制记录数量。

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

比如这样是非法的：在这里插入图片描述

正因为[NOT] IN/ANY/SOME/ALL 子查询不支持LIMIT 语句，所以在子查询中使用ORDER BY子句、DISTINCT子句以及没有聚焦函数和HAVING子句的GROUP BY子句是毫无意义的。比如：

SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 ORDER BY m2);
SELECT * FROM t1 WHERE m1 IN (SELECT DISTINCT m2 FROM t2); 
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 GROUP BY m2);

这些sql中的冗余的语句，查询优化器在一开始就把它们给干掉了。

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

比如这样是非法的：
在这里插入图片描述

二、子查询的执行过程

在讲执行过程之前，我们依旧先创建s1和s2两张表，并在表里插入10000条数据。s1和s2的表结构相同，如下所示：

CREATE TABLE single_table ( 
   id INT NOT NULL AUTO_INCREMENT, 
   key1 VARCHAR(100), 
   key2 INT, 
   key3 VARCHAR(100), 
   key_part1 VARCHAR(100), 
   key_part2 VARCHAR(100), 
   key_part3 VARCHAR(100), 
   common_field VARCHAR(100), 
   PRIMARY KEY (id), 
   KEY idx_key1 (key1), 
   UNIQUE KEY idx_key2 (key2), 
   KEY idx_key3 (key3), 
   KEY idx_key_part(key_part1, key_part2, key_part3) 
) Engine=InnoDB CHARSET=utf8;

下面我们就来通过s1和s2两张表来聊一下子查询的执行过程。

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

我们经常在下边两个场景中使用到标量子查询或者行子查询：

1. SELECT 子句中：我们前边说过的在查询列表中的子查询必须是标量子查询。
2. 子查询使用=、>、<、>=、<=、<>、!=、<=>等操作符和某个操作数组成一个布尔表达式：这样的子查询必须是标量子查询或者行子查询。

对于上述两种场景中的不相关标量子查询或者行子查询来说，它们的执行方式是简单的，比方：

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

它的执行方式是：先单独执行(SELECT common_field FROM s2 WHERE key3 = ‘a’ LIMIT 1) 这个子查询。然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 = … 。

也就是说，对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询。

对于相关的标量子查询或者行子查询来说，比如：

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

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

2. IN子查询的执行方式

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

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

2. 对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，导致无法有效的使用索引，只能对外层查询进行全表扫描，而在对外层查询执行全表扫描时，由于IN子句中的参数太多，又会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。

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

1. 该临时表的列就是子查询结果集中的列。

2. 写入临时表的记录会被去重。

3. 一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。

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

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

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')；

拿上述sql为例：当我们把子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val ，那么这个查询其实可以从下边两种角度来看待：

从表s1 的角度来看待，整个查询的意思其实是：对于s1表中的每条记录来说，如果该记录的key1列的值在子查询对应的物化表中，则该记录会被加入最终的结果集。画个图表示一下就是这样：

在这里插入图片描述

从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在s1表中找到对应的key1 列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。画个图表示一下就是这样：

在这里插入图片描述

也就是说其实上边的查询就相当于表s1和子查询物化表materialized_table进行内连接：

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

转化成内连接之后，查询优化器评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。

我们分析一下上述查询中使用外层查询的表s1和物化表materialized_table 进行内连接的成本都是由哪几部分组成的：

如果使用s1 表作为驱动表的话，总查询成本由下边几个部分组成：

物化子查询时需要的成本。
扫描s1 表时的成本。
s1表中的记录数量 × 通过 m_val = xxx 对 materialized_table 表进行单表访问的成本。

如果使用materialized_table 表作为驱动表的话，总查询成本由下边几个部分组成：

物化子查询时需要的成本。
扫描物化表时的成本。
物化表中的记录数量 × 通过 key1 = xxx 对 s1 表进行单表访问的成本。

MySQL 查询优化器会通过运算来选择上述成本更低的方案来执行查询。

好了，到这里我们就讲完了，大家有什么想法欢迎留言讨论。也希望大家能给作者点个关注，谢谢大家！最后依旧是请各位老板有钱的捧个人场，没钱的也捧个人场，谢谢各位老板！

韩朝洋

关注

9
点赞
踩
28

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql中子查询的语法和执行过程

大家好。我们在日常开发过程中，肯定都经常用到了子查询。今天我们就来聊一下mysql中子查询的一些语法以及子查询的执行过程。
复制链接

扫一扫

专栏目录