tidb 的子查询 -CSDN博客

1.tidb 的子查询
子查询是嵌套在另一个查询中的 SQL 表达式，借助子查询，可以在一个查询当中使用另外一个查询的查询结果。
#子查询语句
通常情况下，子查询语句分为如下几种形式：

标量子查询（Scalar Subquery），如 SELECT (SELECT s1 FROM t2) FROM t1。
派生表（Derived Tables），如 SELECT t1.s1 FROM (SELECT s1 FROM t2) t1。
存在性测试（Existential Test），如 WHERE NOT EXISTS(SELECT ... FROM t2)，WHERE t1.a IN (SELECT ... FROM t2)。
集合比较（Quantified Comparison），如 WHERE t1.a = ANY(SELECT ... FROM t2)。
作为比较运算符操作数的子查询，如 WHERE t1.a > (SELECT ... FROM t2)。

子查询的分类
一般来说，可以将子查询分为关联子查询（Correlated Subquery）和无关联子查询 (Self-contained Subquery) 两大类，
TiDB 对于这两类子查询的处理方式是不一样的。

判断是否为关联子查询的依据在于子查询当中是否引用了外层查询的列。
#无关联子查询
对于将子查询作为比较运算符 (> / >=/ < / <= / = / !=) 操作数的这类无关联子查询而言，内层子查询只需要进行一次查询，
TiDB 在生成执行计划阶段会将内层子查询改写为常量。

SELECT * FROM authors a1 WHERE (IFNULL(a1.death_year, YEAR(NOW())) - a1.birth_year) > (
    SELECT
        AVG(IFNULL(a2.death_year, YEAR(NOW())) - a2.birth_year) AS average_age
    FROM
        authors a2
);

#在 TiDB 执行上述查询的时候会先执行一次内层子查询：
SELECT AVG(IFNULL(a2.death_year, YEAR(NOW())) - a2.birth_year) AS average_age FROM authors a2;

#假设查询得到的结果为 34，即总体平均年龄为 34，34 将作为常量替换掉原来的子查询。

SELECT * FROM authors a1
WHERE (IFNULL(a1.death_year, YEAR(NOW())) - a1.birth_year) > 34;

对于存在性测试和集合比较两种情况下的无关联列子查询，TiDB 会将其进行改写和等价替换以获得更好的执行性能

#关联子查询
对于关联子查询而言，由于内层的子查询引用外层查询的列，子查询需要对外层查询得到的每一行都执行一遍，
也就是说假设外层查询得到一千万的结果，那么子查询也会被执行一千万次，这会导致查询需要消耗更多的时间和资源。
因此在处理过程中，TiDB 会尝试对关联子查询去关联，以从执行计划层面上提高查询效率。

SELECT * FROM authors a1 WHERE (IFNULL(a1.death_year, YEAR(NOW())) - a1.birth_year) > (
    SELECT
        AVG(
            IFNULL(a2.death_year, YEAR(NOW())) - IFNULL(a2.birth_year, YEAR(NOW()))
        ) AS average_age
    FROM
        authors a2
    WHERE a1.gender = a2.gender
);

关联子查询类似于使用NL_JOIN,嵌套循环连接,性能较差,改成HASH_JOIN;
#TiDB 在处理该 SQL 语句是会将其改写为等价的 Join 查询：

SELECT * FROM
    authors a1,
    (
        SELECT
            gender, AVG(
                IFNULL(a2.death_year, YEAR(NOW())) - IFNULL(a2.birth_year, YEAR(NOW()))
            ) AS average_age
        FROM
            authors a2
        GROUP BY gender
    ) a2
WHERE
    a1.gender = a2.gender
    AND (IFNULL(a1.death_year, YEAR(NOW())) - a1.birth_year) > a2.average_age;
	
#作为最佳实践，在实际开发当中，建议在明确知道有更好的等价写法时，尽量避免通过关联子查询来进行查询

子查询:
标量子查询 
派生表子查询 
存在性测试子查询
集合比较子查询
作为比较运算符操作数的子查询 

关联子查询和非关联子查询。