SQL子查询优化

背景：
在此研究下SQL子查询的相关知识 ~ 以下内容主要参考《数据库查询优化器的艺术》一书

---

一、子查询介绍

概念：当一个查询是另一个查询的子部分时，称之为子查询（查询语句中嵌套有查询语句）。

子查询出现的位置有：

a）目标列位置：子查询如果位于目标列，则只能是标量子查询，否则数据库可能返回类似“错误: 子查询必须只能返回一个字段”的提示。

注：标量子查询 - SQL允许子查询出现在返回单个值的表达式能够出现的任何地方，只要该子查询只返回包含单个属性的单个元组；这样的子查询称为标量子查询（scalar subquery）。

错误示范：

正确写法（标量子查询）：

但是在 Hive 和 Impala 中测试甚至连标量子查询都不支持！：

b）FROM子句位置： 相关子查询 出现在FROM子句中，数据库可能返回类似“在FROM子句中的子查询无法参考相同查询级别中的关系”的提示，所以相关子查询不能出现在FROM子句中；非相关子查询出现在FROM子句中，可上拉子查询到父层，在多表连接时统一考虑连接代价然后择优。
注：此处我想了下，好像没有from后跟相关子查询的使用情形，这种错误基本不可能犯的。。

c）WHERE子句位置：出现在WHERE子句中的子查询，是一个条件表达式的一部分，而表达式可以分解为操作符和操作数；根据参与运算的不同的数据类型，操作符也不尽相同，如INT型有“>、<、=、<>”等操作，这对子查询均有一定的要求（如INT型的等值操作，要求子查询必须是标量子查询）。另外，子查询出现在WHERE子句中的格式，也有用谓词指定的一些操作，如IN、BETWEEN、EXISTS等。

d）JOIN/ON子句位置： JOIN/ON子句可以拆分为两部分，一是JOIN块类似于FROM子句，二是ON子句块类似于WHERE子句，这两部分都可以出现子查询。子查询的处理方式同FROM子句和WHERE子句。

e）GROUP BY子句位置： 目标列必须和GROUPBY关联。可将子查询写在GROUPBY位置处，但子查询用在GROUPBY处没有实用意义。

f）ORDER BY子句位置： 可将子查询写在ORDERBY位置处。但ORDERBY操作是作用在整条SQL语句上的，子查询用在ORDERBY处没有实用意义。

以下这种情况还是有实用的：

SELECT * FROM `user` AS u ORDER BY (SELECT DATA FROM `customer` c WHERE c.id = u.user_id )

---

二、子查询分类

从对象间的关系看：

a）相关子查询

子查询的执行依赖于外层父查询的一些属性值。子查询因依赖于父查询的参数，当父查询的参数改变时，子查询需要根据新参数值重新执行（查询优化器对相关子查询进行优化有一定意义），如：

SELECT * FROM t1 WHERE col_1 = ANY (SELECT col_1 FROM t2 WHERE t2.col_2 = t1.col_2);
# 子查询语句中存在父查询的t1表的col_2列

注：

ANY关键词可以理解为“对于子查询返回的列中的任一数值，如果比较结果为true，则返回true”。
ALL 的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”

b）非相关子查询

子查询的执行，不依赖于外层父查询的任何属性值。这样子查询具有独立性，可独自求解，形成一个子查询计划先于外层的查询求解，如：

SELECT * FROM t1 WHERE col_1 = ANY

(SELECT col_1 FROM t2 WHERE t2.col_2 = 10);

//子查询语句中（t2）不存在父查询（t1）的属性

从特定谓词看：

a）[NOT] IN/ALL/ANY/SOME子查询

语义相近，表示“[取反] 存在/所有/任何/任何”，左面是操作数，右面是子查询，是最常见的子查询类型之一。

b）[NOT] EXISTS子查询

半连接语义，表示“[取反] 存在”，没有左操作数，右面是子查询，也是最常见的子查询类型之一。

c）其他子查询

除了上述两种外的所有子查询。

从语句的构成复杂程度看：

a）SPJ子查询

由选择、连接、投影操作组成的查询。

b）GROUPBY子查询

SPJ子查询加上分组、聚集操作组成的查询。

c）其他子查询

GROUPBY子查询中加上其他子句如Top-N 、LIMIT/OFFSET、集合、排序等操作。

后两种子查询有时合称非SPJ子查询。

从结果的角度看：

a）标量子查询

子查询返回的结果集类型是一个简单值。

b）单行单列子查询

子查询返回的结果集类型是零条或一条单元组。相似于标量子查询,但可能返回零条元组。

c）多行单列子查询

子查询返回的结果集类型是多条元组但只有一个简单列。

d）表子查询

子查询返回的结果集类型是一个表（多行多列）。

---

三、子查询优化

a）子查询合并（Subquery Coalescing）

在某些条件下（语义等价：两个查询块产生同样的结果集），多个子查询能够合并成一个子查询（合并后还是子查询，以后可以通过其他技术消除掉子查询）。这样可以把多次表扫描、多次连接减少为单次表扫描和单次连接，如：

SELECT * FROM t1 WHERE a1<10 AND (
    EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR 
    EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2) 
);

可优化为：

SELECT * FROM t1 WHERE a1<10 AND (
    EXISTS (SELECT a2 FROM t2 WHERE t2.a2<5 AND(t2.b2=1 OR t2.b2=2) 
    # 两个ESISTS子句合并为一个，条件也进行了合并
);

b）子查询展开（Subquery Unnesting）

又称子查询反嵌套，又称为子查询上拉。把一些子查询置于外层的父查询中，作为连接关系与外层父查询并列，其实质是把某些子查询重写为等价的多表连接操作（展开后，子查询不存在了，外部查询变成了多表连接）。带来的好处是，有关的访问路径、连接方法和连接顺序可能被有效使用，使得查询语句的层次尽可能的减少。

常见的IN/ANY/SOME/ALL/EXISTS依据情况转换为半连接（SEMI JOIN）、普通类型的子查询消除等情况属于此类，如：

SELECT * FROM t1, (SELECT * FROM t2 WHERE t2.a2 >10) v_t2 
WHERE t1.a1<10 AND v_t2.a2<20;

可优化为：

SELECT * FROM t1, t2 WHERE t1.a1<10 AND t2.a2<20 AND t2.a2 >10; 

/* 子查询变为了t1、t2表的连接操作，相当于把t2表从子查询中上拉了一层 */

子查询展开的条件：

a）如果子查询中出现了聚集、GROUPBY、DISTINCT子句，则子查询只能单独求解，不可以上拉到外层。

b）如果子查询只是一个简单格式的（SPJ格式）查询语句，则可以上拉子查询到外层，这样往往能提高查询效率。子查询上拉，讨论的就是这种格式，这也是子查询展开技术处理的范围。

把子查询上拉到上层查询，前提是上拉（展开）后的结果不能带来多余的元组，所以子查询展开需要遵循如下规则：

a）如果上层查询的结果没有重复（即SELECT子句中包含主码），则可以展开其子查询。并且展开后的查询的SELECT子句前应加上DISTINCT标志。

b）如果上层查询的SELECT语句中有DISTINCT标志，可以直接进行子查询展开。

如果内层查询结果没有重复元组，则可以展开。

子查询展开的具体步骤：

a）将子查询和外层查询的FROM子句连接为同一个FROM子句，并且修改相应的运行参数。

b）将子查询的谓词符号进行相应修改(如：“IN”修改为“=”)。

c）将子查询的WHERE条件作为一个整体与外层查询的WHERE条件合并，并用AND条件连接词连接，从而保证新生成的谓词与原旧谓词的上下文意思相同，且成为一个整体。

c）聚集子查询消除（Aggregate Subquery Elimination）

通常，一些系统支持的是标量聚集子查询消除。如：

SELECT * FROM t1 WHERE t1.a1 > (SELECT avg(t2.a2) FROM t2);