MySQL子查询原理分析

01

前言

子查询，通俗解释就是查询语句中嵌套着另一个查询语句。相信日常工作中接触到 MySQL 的同学都了解或使用过子查询，但是具体它是怎样实现的呢? 查询效率如何? 这些恐怕好多人就不太清楚了，下面咱们就围绕这两个问题共同探索一下。

02

准备内容

这里我们需要用到3个表，这3个表都有一个主键索引 id 和一个索引 a，字段 b 上无索引。存储过程 idata() 往表 t1 里插入的是 100 行数据，表 t2、t3 里插入了 1000 行数据。建表语句如下：

CREATE TABLE `t1` (
    `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 按返回的结果集区分

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

   SELECT * FROM t1 WHERE m1 = (SELECT m1 FROM t1 LIMIT 1);

2. 行子查询，就是只返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。比如：SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

3. 列子查询，就是只返回一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。比如：SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);

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

   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 标量子查询、行子查询的执行方式