← 返回学习路线

SYSTEM

嵌套查询

Java基础嵌套查询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