mysql中select子查(select中的select子查询)询探索

九转成圣

发布于 2024-05-24 03:20:56

47000

代码可运行

文章被收录于专栏：csdncsdn

运行总次数：0

代码可运行

mysql中select子查询探索

表结构

emp

+--------------+---------------+------+-----+-------------------+-----------------------------+
| Field        | Type          | Null | Key | Default           | Extra                       |
+--------------+---------------+------+-----+-------------------+-----------------------------+
| empno        | int(10)       | NO   | PRI | NULL              | auto_increment              |
| ename        | varchar(50)   | YES  | MUL | NULL              |                             |
| job          | varchar(100)  | YES  |     | NULL              |                             |
| mgr          | int(10)       | YES  | MUL | NULL              |                             |
| hiredate     | datetime      | YES  | MUL | NULL              |                             |
| sal          | decimal(10,2) | YES  | MUL | NULL              |                             |
| comm         | decimal(10,2) | YES  |     | NULL              |                             |
| deptno       | int(11)       | YES  |     | NULL              |                             |
| created_time | datetime      | YES  |     | CURRENT_TIMESTAMP |                             |
| updated_time | datetime      | YES  |     | NULL              | on update CURRENT_TIMESTAMP |
| is_deleted   | tinyint(1)    | YES  | MUL | 0                 |                             |
| version      | int(1)        | YES  |     | 1                 |                             |
+--------------+---------------+------+-----+-------------------+-----------------------------+

dept

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| deptno | int(11)     | NO   | PRI | NULL    | auto_increment |
| dname  | varchar(10) | YES  |     | NULL    |                |
| loc    | varchar(50) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

数据

mysql> select * from emp where empno =1;
+-------+--------+------+------+---------------------+------+-------+--------+
| empno | ename  | job  | mgr  | hiredate            | sal  | comm  | deptno |
+-------+--------+------+------+---------------------+------+-------+--------+
|     1 | 张无忌 | 教主 | NULL | 1981-11-17 00:00:00 | NULL | 20.00 |      4 |
+-------+--------+------+------+---------------------+------+-------+--------+
1 row in set (0.24 sec)

mysql> select * from dept where deptno =4;
+--------+-------+------+
| deptno | dname | loc  |
+--------+-------+------+
|      4 | 运营  | 杭州 |
+--------+-------+------+
1 row in set (0.08 sec)

select 中的子查询

mysql>  select ename,(select dname from dept d where e.deptno = d.deptno) as dname from emp e where empno = 1;
+--------+-------+
| ename  | dname |
+--------+-------+
| 张无忌 | 运营  |
+--------+-------+
1 row in set (0.07 sec)

执行计划

mysql> explain select ename,(select dname from dept d where e.deptno = d.deptno) as dname from emp e where empno = 1;
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY            | e     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  2 | DEPENDENT SUBQUERY | d     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set (0.24 sec)

疑惑

ChatGPT给出的执行过程

功能是查询员工编号为1的员工姓名和所在部门名称。它的执行过程如下：

1. 从emp表中查询员工编号为1的员工记录。
2. 对于查询结果中的每一条记录，都会执行一个子查询，查询该员工所在的部门名称。这个子查询使用了dept表，通过员工表和部门表的deptno字段关联，查询出员工所在部门的名称。由于是子查询，所以需要在执行每一个子查询的时候都扫描一遍dept表，效率相对较低。
3. 将员工姓名和部门名称返回给用户。

总体来说，这条SQL语句虽然可以实现查询员工姓名和部门名称的功能，但是由于使用了子查询，效率相对较低，没有充分利用索引，而且在数据量较大时容易出现性能问题。如果想要优化这条SQL语句，可以考虑使用JOIN操作来代替子查询，这样可以更好地利用索引，提高查询效率

思考

通过执行计划可以看出,先执行的是DEPENDENT SUBQUERY这部分(id大的优先执行),也就是select dname from dept d where e.deptno = d.deptno但是这部分是不能单独执行的,所以猜测mysql对这部分做了处理,处理成类似这种select d.dname,e.deptno from dept d join emp e on d.deptno = e,deptno,生成了一个临时表,然后再执行主表和临时表的连表查询(临时表的意思是啥?比如dept表有很多列,同时又很多行,其中还有一大部分不满足d.deptno = e,deptno这个条件,此时临时表相对于对大表做了一个精简)

当我把以为告诉ChatGPT的时候他是这么说的

非常抱歉，我的回答可能存在一些歧义。实际上，第一条SQL语句的执行顺序是这样的：

执行子查询，查询出员工所在的部门名称。这个子查询使用了dept表，通过员工表和部门表的deptno字段关联，查询出员工所在部门的名称。

执行主查询，查询员工姓名和部门名称。这个查询使用了emp表，通过员工表的empno字段和where条件过滤，查询出员工姓名和部门名称。

在执行子查询的时候，子查询中的e.deptno是来自于主查询中的emp表，是通过where条件过滤出来的，所以子查询中的e.deptno是一个固定的值。子查询的结果会作为一个临时表，与主查询中的emp表进行连接查询，最终得到员工姓名和部门名称的查询结果。

总的来说，第一条SQL语句使用了子查询，虽然可以实现查询员工姓名和部门名称的功能，但是效率不高，不够优化。而第二条SQL语句使用了JOIN操作，可以更好地利用索引，提高查询效率。

到这里对于select子查询的执行顺序更迷惑了,不知道DEPENDENT SUBQUERY到底时怎么执行的,到底有没有生产临时表,但是可以明确这种子查询的效率不如join好

注意事项

在select子查询中,子查询不能返回多行数据

mysql> select * from emp where deptno = 3;
+-------+----------+----------+-----+---------------------+------+------+--------+
| empno | ename    | job      | mgr | hiredate            | sal  | comm | deptno | 
+-------+----------+----------+-----+---------------------+------+------+--------+
|     2 | 紫衫龙王 | 护教法王 |   1 | 1981-06-09 00:00:00 | NULL | NULL |      3 | 
|     3 | 白眉鹰王 | 护教法王 |   1 | 1981-04-02 00:00:00 | NULL | NULL |      3 | 
|     4 | 金毛狮王 | 护教法王 |   1 | 1981-05-01 00:00:00 | NULL | NULL |      3 | 
|     5 | 青翼蝙王 | 护教法王 |   1 | 1980-12-17 00:00:00 | NULL | NULL |      3 | 
+-------+----------+----------+-----+---------------------+------+------+--------+
4 rows in set (0.08 sec)

子查询返回多条数据的情况

mysql> select d.dname,(select e.ename from emp e where e.deptno = d.deptno ) from dept d where d.deptno = 3;
Subquery returns more than 1 row

子查询中的limit

mysql> select d.dname,(select e.ename from emp e where e.deptno = d.deptno limit 1) from dept d where d.deptno = 3;
+-------+---------------------------------------------------------------+
| dname | (select e.ename from emp e where e.deptno = d.deptno limit 1) |
+-------+---------------------------------------------------------------+
| 市场  | 紫衫龙王                                                      |
+-------+---------------------------------------------------------------+
1 row in set (0.26 sec)

select子查询中除了使用limit还可以使用order by,根据某种条件排序返回第一个或者最后一个

mysql> select d.dname,(select e.ename from emp e where e.deptno = d.deptno order by e.empno desc limit 1) from dept d where d.deptno = 3;
+-------+-------------------------------------------------------------------------------------+
| dname | (select e.ename from emp e where e.deptno = d.deptno order by e.empno desc limit 1) |
+-------+-------------------------------------------------------------------------------------+
| 市场  | 青翼蝙王                                                                            |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

select子查询可能的使用场景

带统计的查询

查询部门名称,地点,和部门人数

mysql> select dname,loc,(select count(empno) from emp e where e.deptno = d.deptno) as count from dept d;
+-------+------+-------+
| dname | loc  | count |
+-------+------+-------+
| 开发  | 北京 |    10 |
| 测试  | 上海 |     3 |
| 市场  | 广州 |     4 |
| 运营  | 杭州 |     1 |
+-------+------+-------+
4 rows in set (0.09 sec)

主查询对应的子查询是多行,主查询只需要一行,例如查询部门名称,所在地,和部门中id最大的一个人的名称

mysql> select d.dname,(select e.ename from emp e where e.deptno = d.deptno order by e.empno desc limit 1) from dept d where d.deptno = 3;
+-------+-------------------------------------------------------------------------------------+
| dname | (select e.ename from emp e where e.deptno = d.deptno order by e.empno desc limit 1) |
+-------+-------------------------------------------------------------------------------------+
| 市场  | 青翼蝙王                                                                            |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.09 sec)

其他暂时也没想到,工作里面一般能join就不select子查询,之所以想到探索select子查询,是因为公司里面不少这种写法,当初第一感觉挺新奇,是不是这种效率更好?于是就有了select子查询探索之旅,后续继续在完善select子查询的执行流程,也不知道是我误导了ChatGPT还是他迷糊了我,总觉得他是墙头草,说的不靠谱

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2024-05-16，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql