SYSTEM
嵌套查询
src/content/notes/system/嵌套查询.md 嵌套查询
查询分类
查询语句中嵌套了查询语句,将嵌套查询称为子查询,FROM 子句后面的子查询的结果集称为派生表
根据结果分类:
-
结果是单行单列:可以将查询的结果作为另一条语句的查询条件,使用运算符判断
SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名/聚合函数(列名) FROM 表名 [WHERE 条件]); -
结果是多行单列:可以作为条件,使用运算符 IN 或 NOT IN 进行判断
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); -
结果是多行多列:查询的结果可以作为一张虚拟表参与查询
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件]; -- 查询订单表orderlist中id大于4的订单信息和所属用户USER信息 SELECT * FROM USER u, (SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;
相关性分类:
- 不相关子查询:子查询不依赖外层查询的值,可以单独运行出结果
- 相关子查询:子查询的执行需要依赖外层查询的值
查询优化
不相关子查询的结果集会被写入一个临时表,并且在写入时去重,该过程称为物化,存储结果集的临时表称为物化表
系统变量 tmp_table_size 或者 max_heap_table_size 为表的最值
- 小于系统变量时,内存中可以保存,会为建立基于内存的 MEMORY 存储引擎的临时表,并建立哈希索引
- 大于任意一个系统变量时,物化表会使用基于磁盘的 InnoDB 存储引擎来保存结果集中的记录,索引类型为 B+ 树
物化后,嵌套查询就相当于外层查询的表和物化表进行内连接查询,然后经过优化器选择成本最小的表连接顺序执行查询
子查询物化会产生建立临时表的成本,但是将子查询转化为连接查询可以充分发挥优化器的作用,所以引入:半连接
- t1 和 t2 表进行半连接,对于 t1 表中的某条记录,只需要关心在 t2 表中是否存在,而不需要关心有多少条记录与之匹配,最终结果集只保留 t1 的记录
- 半连接只是执行子查询的一种方式,MySQL 并没有提供面向用户的半连接语法
参考书籍:https://book.douban.com/subject/35231266/
联合查询
UNION 是取这两个子查询结果的并集,并进行去重,同时进行默认规则的排序(union 是行加起来,join 是列加起来)
UNION ALL 是对两个结果集进行并集操作不进行去重,不进行排序
(select 1000 as f) union (select id from t1 order by id desc limit 2); #t1表中包含id 为 1-1000 的数据
语句的执行流程:
- 创建一个内存临时表,这个临时表只有一个整型字段 f,并且 f 是主键字段
- 执行第一个子查询,得到 1000 这个值,并存入临时表中
- 执行第二个子查询,拿到第一行 id=1000,试图插入临时表中,但由于 1000 这个值已经存在于临时表了,违反了唯一性约束,所以插入失败,然后继续执行
- 取到第二行 id=999,插入临时表成功
- 从临时表中按行取出数据,返回结果并删除临时表,结果中包含两行数据分别是 1000 和 999