MySQL子查询原理的深入分析
01前言
子查询，通俗解释就是查询语句中嵌套着另外一个查询语句。相信平常工作中接触到 MySQL 的同学都了解或使用过子查询，但是具体它是怎样实现的呢? 查询效力如何? 这些恐怕好多人就不太清楚了，下面我们就围绕这两个问题共同探索一下。
02准备内容
这里我们需要用到3个表，这3个表都有一个主键索引 id 和一个索引 a，字段 b 上无索引。存储进程 idata() 往表 t1 里插入的是 100 行数据，表 t2、t3 里插入了 1000 行数据。建表语句以下：
`id` INT ( 11 ) NOT NULL,
`t1_a` INT ( 11 ) DEFAULT NULL,
`t1_b` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_a` ( `t1_a` )) ENGINE = INNODB;
CREATE TABLE `t2` (
`id` INT ( 11 ) NOT NULL,
`t2_a` INT ( 11 ) DEFAULT NULL,
`t2_b` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_a` ( `t2_a` )) ENGINE = INNODB;
CREATE TABLE `t3` (
`id` INT ( 11 ) NOT NULL,
`t3_a` INT ( 11 ) DEFAULT NULL,
`t3_b` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `idx_a` ( `t3_a` )) ENGINE = INNODB;
— 向t1添加100条数据
— drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t1 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
— 向t2添加1000条数据
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=101;
while(i<=1100)do
insert into t2 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
— 向t2添加1000条数据，且t3_a列的值为倒叙
drop procedure idata;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=101;
while(i<=1100)do
insert into t3 values(i, 1101-i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
03子查询的语法情势和分类
3.1 语法情势
子查询的语法规定，子查询可以在一个外层查询的各种位置出现，这里我们只介绍经常使用的几个：
3.1.1 FROM子句中
如 SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;
这个例子中的子查询是：(SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2)，这个放在FROM子句中的子查询相当于一个表，但又和我们平常使用的表有点儿不一样，这类由子查询结果集组成的表称之为派生表。
3.1.2 WHERE或IN子句中
如：SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
其他的还有 SELECT 子句中，ORDER BY 子句中，GROUP BY 子句中，虽然语法支持，但没啥意义，就不唠叨这些情况了。
3.2 分类
3.2.1 按返回的结果集辨别
标量子查询，只返回一个单一值的子查询称之为标量子查询，比如：
SELECT * FROM t1 WHERE m1 = (SELECT m1 FROM t1 LIMIT 1);
行子查询，就是只返回一条记录的子查询，不过这条记录需要包括多个列（只包括一个列就成了标量子查询了）。比如：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 m2, n2 FROM t2) 就是一个表子查询，这里需要和行子查询对照一下，行子查询中我们用了 LIMIT 1 来保证子查询的结果只有一条记录。
3.2.2 按与外层查询关系来辨别
不相关子查询，就是子查询可以单独运行出结果，而不依赖于外层查询的值，我们就能够把这个子查询称之为不相关子查询。
相关子查询，就是需要依赖于外层查询的值的子查询称之为相关子查询。比如：SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);
04子查询在MySQL中是怎样履行的
4.1 标量子查询、行子查询的履行方式
4.1.1 不相关子查询
以下边这个查询语句：
+—-+————-+——-+——-+—————+——-+———+——–+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——-+———+——–+——+————-+
| 1 | PRIMARY | t1 | ref | idx_a | idx_a | 5 | const | 1 | Using where |
| 2 | SUBQUERY | t2 | index | <null> | idx_a | 5 | <null> | 1000 | Using index |
+—-+————-+——-+——-+—————+——-+———+——–+——+————-+
它的履行方式：
先单独履行 (select t2_a from t2 limit 1) 这个子查询。
然后在将上一步子查询得到的结果当作外层查询的参数再履行外层查询 select * from t1 where t1_a = …。
也就是说，对包括不相关的标量子查询或行子查询的查询语句来讲，MySQL 会分别独立的履行外层查询和子查询，就当作两个单表查询就行了。
4.1.2 相关的子查询
比以下边这个查询：
+—-+——————–+——-+——+—————+——–+———+——–+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+——-+——+—————+——–+———+——–+——+————-+
| 1 | PRIMARY | t1 | ALL | <null> | <null> | <null> | <null> | 100 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ALL | <null> | <null> | <null> | <null> | 1000 | Using where |
+—-+——————–+——-+——+—————+——–+———+——–+——+————-+
它的履行方式就是这样的：
先从外层查询中获得一条记录，本例中也就是先从 t1 表中获得一条记录。
然后从上一步骤中获得的那条记录中找出子查询中触及到的值，就是 t1 表中找出 t1.t1_b 列的值，然后履行子查询。
最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件会不会成立，如果成立，就把外层查询的那条记录加入到结果集，否则就抛弃。
然后重复以上步骤，直到 t1 中的记录全部匹配完。
4.2 IN子查询
4.2.1 物化
如果子查询的结果集中的记录条数很少，那末把子查询和外层查询分别看成两个单独的单表查询效力或者蛮高的，但是如果单独履行子查询后的结果集太多的话，就会致使这些问题：
结果集太多，可能内存中都放不下～
对外层查询来讲，如果子查询的结果集太多，那就意味着 IN 子句中的参数特别多，这就致使：
1）没法有效的使用索引，只能对外层查询进行全表扫描。
2）在对外层查询履行全表扫描时，由于 IN 子句中的参数太多，这会致使检测一条记录会不会符合和 IN 子句中的参数匹配花费的时间太长。
因而就有：不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的进程是这样的：
该临时表的列就是子查询结果集中的列。
写入临时表的记录会被去重，让临时表变得更小，更省地方。
一般情况下子查询结果集不大时，就会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。
如果子查询的结果集非常大，超过了系统变量 tmp_table_size或 max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变成 B+ 树索引。
这个将子查询结果集中的记录保存到临时表的进程称之为物化（Materialize）。为了方便起见，我们就把那个存储子查询结果集的临时表称之为物化表。正由于物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有 B+ 树索引），通过索引履行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。
+—-+————–+————-+——–+—————+————+———+————–+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————–+————-+——–+—————+————+———+————–+——+————-+
| 1 | SIMPLE | t3 | ALL | idx_a | <null> | <null> | <null> | 1000 | Using where |
| 1 | SIMPLE | <subquery2> | eq_ref | <auto_key> | <auto_key> | 5 | test.t3.t3_a | 1 | <null> |
| 2 | MATERIALIZED | t2 | index | idx_a | idx_a | 5 | <null> | 1000 | Using index |
+—-+————–+————-+——–+—————+————+———+————–+——+————-+
其实上边的查询就相当于表 t3 和子查询物化表进行内连接：
+—-+————-+——-+——+—————+——–+———+————–+——+——–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——–+———+————–+——+——–+
| 1 | SIMPLE | t3 | ALL | <null> | <null> | <null> | <null> | 1000 | <null> |
| 1 | SIMPLE | t2 | ref | idx_a | idx_a | 5 | test.t3.t3_a | 1 | <null> |
+—-+————-+——-+——+—————+——–+———+————–+——+——–+
此时 MySQL 查询优化器会通过运算来选择本钱更低的方案来履行查询。
虽然，上面通过物化表的方式，将IN子查询转换成了联接查询，但或者会有建立临时表的本钱，能不能不进行物化操作直接把子查询转换为连接呢？直接转换肯定不行。
— 这里我们先构造了3条记录，其实也是构造不唯一的普通索引
| id | t2_a | t2_b |
+——+——+——+
| 1100 | 1000 | 1000 |
| 1101 | 1000 | 1000 |
| 1102 | 1000 | 1000 |
+——+——+——+
— 加限制条件where t2.id>=1100是为了减少要显示的数据
mysql root@localhost:test> select * from t3 where t3_a in (select t2_a from t2 where t2.id>=1100);
+—–+——+——+
| id | t3_a | t3_b |
+—–+——+——+
| 101 | 1000 | 101 |
+—–+——+——+
1 row in set
Time: 0.016s
mysql root@localhost:test> select * from t3 left join t2 on t3.t3_a=t2.t2_a where t2.id>=1100;
+—–+——+——+——+——+——+
| id | t3_a | t3_b | id | t2_a | t2_b |
+—–+——+——+——+——+——+
| 101 | 1000 | 101 | 1100 | 1000 | 1000 |
| 101 | 1000 | 101 | 1101 | 1000 | 1000 |
| 101 | 1000 | 101 | 1102 | 1000 | 1000 |
+—–+——+——+——+——+——+
3 rows in set
Time: 0.018s
所以说 IN 子查询和表联接之间其实不完全等价。而我们需要的是另外一种叫做半联接 (semi-join) 的联接方式 ：对 t3 表的某条记录来讲，我们只关心在 t2 表中会不会存在与之匹配的记录，而不关心具体有多少条记录与之匹配，终究的结果集中也只保存 t3 表的记录。
注意：semi-join 只是在 MySQL 内部采取的一种履行子查询的方式，MySQL 并没有提供面向用户的 semi-join 语法。
4.2.2 半联接的实现：
- Table pullout （子查询中的表上拉）
当子查询的查询列表处只有主键或唯一索引列时，可以直接把子查询中的表上拉到外层查询的 FROM 子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中，比如这个：
+—–+——+——+
| id | t3_a | t3_b |
+—–+——+——+
| 102 | 999 | 102 |
+—–+——+——+
1 row in set
Time: 0.024s
mysql root@localhost:test> select * from t3 join t2 on t3.t3_a=t2.t2_a where t2.id=999;
+—–+——+——+—–+——+——+
| id | t3_a | t3_b | id | t2_a | t2_b |
+—–+——+——+—–+——+——+
| 102 | 999 | 102 | 999 | 999 | 999 |
+—–+——+——+—–+——+——+
1 row in set
Time: 0.028s
mysql root@localhost:test> explain select * from t3 where t3_a in (select t2_a from t2 where t2.id=999)
+—-+————-+——-+——-+—————+———+———+——-+——+——–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+——–+
| 1 | SIMPLE | t2 | const | PRIMARY,idx_a | PRIMARY | 4 | const | 1 | <null> |
| 1 | SIMPLE | t3 | ref | idx_a | idx_a | 5 | const | 1 | <null> |
+—-+————-+——-+——-+—————+———+———+——-+——+——–+
- FirstMatch execution strategy （首次匹配）
FirstMatch 是一种最原始的半连接履行方式，跟相关子查询的履行方式是一样的，就是说先取一条外层查询的中的记录，然后到子查询的表中寻觅符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入终究的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录抛弃掉。然后再开始取下一条外层查询中的记录，重复上边这个进程。
+—-+————-+——-+——+—————+——-+———+——-+——+—————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——-+———+——-+——+—————————–+
| 1 | SIMPLE | t3 | ref | idx_a | idx_a | 5 | const | 1 | <null> |
| 1 | SIMPLE | t2 | ref | idx_a | idx_a | 5 | const | 4 | Using index; FirstMatch(t3) |
+—-+————-+——-+——+—————+——-+———+——-+——+—————————–+
- DuplicateWeedout execution strategy （重复值消除）
转换为半连接查询后，t3 表中的某条记录可能在 t2 表中有多条匹配的记录，所以该条记录可能屡次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表，并设置主键id，每当某条 t3 表中的记录要加入结果集时，就首先把这条记录的id值加入到这个临时表里，如果添加成功，说明之前这条 t2 表中的记录并没有加入终究的结果集，是一条需要的结果；如果添加失败，说明之前这条 s1 表中的记录已加入过终究的结果集，直接把它抛弃。
- LooseScan execution strategy （疏松扫描）
这类虽然是扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为疏松扫描。
4.2.3 半联接的适用条件
固然，其实不是所有包括IN子查询的查询语句都可以转换为 semi-join，只有形如这样的查询才可以被转换为 semi-join：
WHERE expr IN (SELECT … FROM inner_tables …) AND …
— 或这样的情势也能够：
SELECT … FROM outer_tables
WHERE (oe1, oe2, …) IN (SELECT ie1, ie2, … FROM inner_tables …) AND …
用文字总结一下，只有符合下边这些条件的子查询才可以被转换为 semi-join：
- 该子查询一定要是和IN语句组成的布尔表达式，并且在外层查询的 WHERE 或 ON 子句中出现
- 外层查询也能够有其他的搜索条件，只不过和 IN 子查询的搜索条件一定要使用AND 连接起来
- 该子查询一定要是一个单一的查询，不能是由若干查询由 UNION 连接起来的情势
- 该子查询不能包括 GROUP BY 或 HAVING 语句或聚集函数
4.2.4 转为 EXISTS 子查询
不管子查询是相关的或者不相关的，都可以把 IN 子查询尝试转为 EXISTS子查询。其实对任意一个 IN 子查询来讲，都可以被转为 EXISTS 子查询，通用的例子以下：
— 可以被转换为：
EXISTS (SELECT inner_expr FROM … WHERE subquery_where AND outer_expr=inner_expr)
固然这个进程中有一些特殊情况，比如在 outer_expr 或 inner_expr 值为 NULL 的情况下就比较特殊。由于有 NULL 值作为操作数的表达式结果常常是 NULL，比方说：
+——————-+
| NULL IN (1, 2, 3) |
+——————-+
| <null> |
+——————-+
1 row in set
而 EXISTS 子查询的结果肯定是 TRUE 或 FASLE 。但是现实中我们大部份使用 IN 子查询的场景是把它放在 WHERE 或 ON 子句中，而 WHERE 或 ON 子句是不辨别 NULL 和 FALSE 的，比方说：
+—+
| 1 |
+—+
0 rows in set
Time: 0.016s
mysql root@localhost:test> SELECT 1 FROM s1 WHERE FALSE;
+—+
| 1 |
+—+
0 rows in set
Time: 0.033s
所以只要我们的IN子查询是放在 WHERE 或 ON 子句中的，那末 IN -> EXISTS 的转换就是没问题的。说了这么多，为啥要转换呢？这是由于不转换的话可能用不到索引，比方说下边这个查询：
+—-+————-+——-+——-+—————+——–+———+——–+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——–+———+——–+——+————————–+
| 1 | PRIMARY | t3 | ALL | <null> | <null> | <null> | <null> | 1000 | Using where |
| 2 | SUBQUERY | t2 | range | idx_a | idx_a | 5 | <null> | 107 | Using where; Using index |
+—-+————-+——-+——-+—————+——–+———+——–+——+————————–+
但是将它转为 EXISTS 子查询后却可使用到索引：
+—-+——————–+——-+——+—————+——–+———+————–+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+——-+——+—————+——–+———+————–+——+————————–+
| 1 | PRIMARY | t3 | ALL | <null> | <null> | <null> | <null> | 1000 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ref | idx_a | idx_a | 5 | test.t3.t3_a | 1 | Using where; Using index |
+—-+——————–+——-+——+—————+——–+———+————–+——+————————–+
需要注意的是，如果 IN 子查询不满足转换为 semi-join 的条件，又不能转换为物化表或转换为物化表的本钱太大，那末它就会被转换为 EXISTS 查询。或转换为物化表的本钱太大，那末它就会被转换为 EXISTS 查询。
05总结
1. 如果IN子查询符合转换为 semi-join 的条件，查询优化器会优先把该子查询转换为 semi-join，然后再斟酌下边履行半连接的策略中哪一个本钱最低，
1）Table pullout
2）DuplicateWeedout
3）LooseScan
4）FirstMatch
选择本钱最低的那种履行策略来履行子查询。
2. 如果IN子查询不符合转换为 semi-join 的条件，那末查询优化器会从下边两种策略中找出一种本钱更低的方式履行子查询：
1）先将子查询物化以后再履行查询
2）履行 IN to EXISTS 转换
到此这篇关于MySQL子查询原理的文章就介绍到这了,更多相关MySQL子查询原理内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！
文章来源：丸子建站
文章标题：MySQL子查询原理的深入分析
https://www.wanzijz.com/view/41891.html
