TDSQL Boundless 子查询

一、子查询的分类

在 TDSQL Boundless 中，子查询通常有以下几种形式：

二、标量子查询

标量子查询返回单行单列的值，可以出现在 SELECT 列表、WHERE 条件等任何需要单个值的位置。其关键特征是子查询的结果等价于一个常量值。

SELECT c_name, c_acctbal, (SELECT AVG(c_acctbal) FROM customer) AS avg_balanceFROM customerLIMIT 5;

三、派生表

派生表是放在 FROM 子句中的子查询，作为一个临时表参与后续查询。其关键特征是子查询必须用括号包裹并指定别名。

SELECT seg.c_mktsegment, seg.cntFROM ( SELECT c_mktsegment, COUNT(*) AS cnt FROM customer GROUP BY c_mktsegment) segORDER BY seg.cnt DESC;

四、存在性子查询

通过 EXISTS、NOT EXISTS、IN、NOT IN 等关键字判断子查询是否返回数据，结果是布尔值。其关键特征是不关心子查询返回的具体值，只关心是否有行存在。

— EXISTS：判断是否存在匹配行SELECT c_name FROM customer cWHERE EXISTS ( SELECT 1 FROM orders o WHERE o.o_custkey = c.c_custkey);– IN：判断值是否在结果集中SELECT c_name FROM customerWHERE c_nationkey IN (SELECT n_nationkey FROM nation WHERE n_name = 'CHINA');

五、集合比较子查询

使用 ANY、ALL、SOME 关键字将一个值与子查询返回的结果集进行比较。其关键特征是比较运算符（=、>、< 等）与 ANY/ALL 组合使用。

— = ANY 等价于 INSELECT c_name, c_acctbal FROM customerWHERE c_acctbal > ANY ( SELECT o_totalprice FROM orders WHERE o_orderstatus = 'F');– > ALL：大于子查询返回的所有值SELECT c_name, c_acctbal FROM customerWHERE c_acctbal > ALL ( SELECT AVG(c_acctbal) FROM customer GROUP BY c_mktsegment);

六、作为比较运算符操作数

子查询直接作为比较运算符（>、<、=、>=、<=、<>）的一侧操作数。其关键特征是子查询必须返回单行单列（即标量），与标量子查询的区别在于它出现在 WHERE/HAVING 的比较条件中。

SELECT o_orderkey, o_totalpriceFROM ordersWHERE o_totalprice > ( SELECT AVG(o_totalprice) FROM orders);

七、子查询的相关性

根据子查询是否引用了外层查询的列，可分为非关联子查询和关联子查询两类。

八、非关联子查询

非关联子查询不引用外层查询的任何列，其结果独立于外层查询。TDSQL Boundless 会先执行内层子查询，将结果作为常量代入外层查询。

查询账户余额高于所有客户平均余额的客户：

SELECT c_custkey, c_name, c_acctbalFROM customerWHERE c_acctbal > ( SELECT AVG(c_acctbal) FROM customer);

TDSQL Boundless 在处理该查询时，会先执行内层子查询：

SELECT AVG(c_acctbal) FROM customer;

假设计算结果为 4990.51，则外层查询等价于：

SELECT c_custkey, c_name, c_acctbalFROM customerWHERE c_acctbal > 4990.51;

使用 IN 子查询查找有过订单的客户：

SELECT c_custkey, c_name, c_mktsegmentFROM customerWHERE c_custkey IN ( SELECT DISTINCT o_custkey FROM orders WHERE o_orderdate >= '1995-01-01');

内层子查询独立执行，返回一组 o_custkey 值，外层查询在这组值中进行匹配。

九、关联子查询

关联子查询引用了外层查询的列，因此内层查询的结果依赖于外层查询当前正在处理的行。从逻辑上看，关联子查询需要对外层的每一行都重新执行一次内层查询。

查询每个客户中金额最大的订单：

SELECT o_orderkey, o_custkey, o_totalprice, o_orderdateFROM orders o1WHERE o_totalprice = ( SELECT MAX(o2.o_totalprice) FROM orders o2 WHERE o2.o_custkey = o1.o_custkey);

内层子查询引用了外层的 o1.o_custkey，对于外层每一行，子查询计算该客户的最大订单金额，然后只保留金额等于最大值的行。

查询账户余额高于同市场细分客户平均余额的客户：

SELECT c1.c_custkey, c1.c_name, c1.c_mktsegment, c1.c_acctbalFROM customer c1WHERE c1.c_acctbal > ( SELECT AVG(c2.c_acctbal) FROM customer c2 WHERE c2.c_mktsegment = c1.c_mktsegment);

TDSQL Boundless 优化器会尝试对关联子查询进行去关联（Unnesting）优化，将其改写为等价的 JOIN 查询以提升性能。例如上述查询可能被改写为：

SELECT c1.c_custkey, c1.c_name, c1.c_mktsegment, c1.c_acctbalFROM customer c1INNER JOIN ( SELECT c_mktsegment, AVG(c_acctbal) AS avg_acctbal FROM customer GROUP BY c_mktsegment) c2 ON c1.c_mktsegment = c2.c_mktsegmentWHERE c1.c_acctbal > c2.avg_acctbal;

改写后的查询只需对 customer 表扫描两次（一次聚合、一次连接），而不是对每个客户都执行一次子查询，性能显著提升。

十、优化器的去关联优化

TDSQL Boundless 优化器提供了多种自动去关联优化机制，将关联子查询改写为等价的非关联形式，减少子查询的重复执行次数。以下介绍三种主要的去关联优化路径。

十一、Semi-Join 去关联

对于 WHERE 或 INNER JOIN ON 中的 IN/EXISTS 关联子查询，优化器将其转换为 Semi-Join（半连接），子查询只执行一次而非逐行执行。这是最常用的去关联路径，受 optimizer_switch 中的 semijoin 开关控制（默认开启）。

— 原始关联子查询SELECT * FROM orders WHERE o_custkey IN (SELECT c_custkey FROM customer WHERE c_nationkey = 1);– 优化器自动改写为 Semi-Join 后，EXPLAIN 中不再出现 DEPENDENT SUBQUERYEXPLAIN SELECT * FROM orders WHERE o_custkey IN (SELECT c_custkey FROM customer WHERE c_nationkey = 1);– id: 1 select_type: SIMPLE table: orders– id: 1 select_type: SIMPLE table: customer Extra: FirstMatch(orders)

十二、物化解关联优化

当关联子查询位于 OR 条件中、或子查询含 GROUP BY/HAVING 等原生 Semi-Join 无法处理的场景时，优化器可通过物化解关联规则将关联 EXISTS/IN 子查询改写为非关联 IN 子查询，使其可独立物化执行。

该优化受 transformer_switch 中的 subquery_to_materialize 开关控制（默认开启），并要求 optimizer_switch 中的 materialization 也为开启状态。可通过 SUBQUERY(MATERIALIZATION) 和 SUBQUERY(INTOEXISTS) Hint 分别强制启用或禁用。

改写机制：优化器从子查询的 WHERE/HAVING 中提取形如 inner_col = outer_expr 的等值关联谓词，将其从子查询中剥离，并将关联内侧列追加到子查询的 SELECT 列表。改写后子查询不再引用外层列，变为非关联 IN 子查询，可被独立物化。

— 改写前：关联 EXISTS 位于 OR 条件中，Semi-Join 无法处理SELECT * FROM t1WHERE t1.a = 1 OR EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.b AND t2.c > 10);– 改写后：EXISTS 转为非关联 IN，子查询独立物化执行SELECT * FROM t1WHERE t1.a = 1 OR t1.b IN (SELECT t2.b FROM t2 WHERE t2.c > 10 GROUP BY t2.b);

改写后，EXPLAIN 输出中原来的 DEPENDENT SUBQUERY 标记变为 MATERIALIZED，表示子查询已去关联并被物化执行。

适用场景：该优化主要面向原生 Semi-Join 无法处理的场景，包括 OR 条件中的 EXISTS/IN 子查询、含 GROUP BY/HAVING 的关联子查询等。优化器基于代价在物化执行和 EXISTS 回退两种路径间选择最优方案，不会导致性能退化。

限制：不支持 NOT IN/NOT EXISTS（NULL 三值逻辑语义不同）、含窗口函数的子查询、含 OFFSET 的子查询、含 RAND() 等非确定性函数的子查询等场景，此时静默跳过，不影响查询正确性。

十三、subquery_to_derived 变换

TDSQL Boundless 支持将 WHERE/ON 中的关联 IN/EXISTS 子查询转换为派生表（derived table）JOIN 形式，使子查询结果作为临时表一次性计算，主表通过 JOIN 匹配。该变换受 transformer_switch 中的 subquery_to_derived 开关控制（默认关闭），与 optimizer_switch 中的同名开关为"或"关系（任一开启即生效）。如需启用，执行 SET transformer_switch = 'subquery_to_derived=on'，session 级别即时生效。

— 原始：关联子查询，外表每行都触发一次子查询执行SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 WHERE t2.b = t1.b);– 变换后等价形式：子查询结果作为派生表一次性计算，主表通过 JOIN 匹配SELECT t1.* FROM t1 JOIN (SELECT DISTINCT t2.a, t2.b FROM t2) AS derived ON t1.a = derived.a AND t1.b = derived.b;

变换生效后，EXPLAIN 输出中会出现 derived 标识（如 derived2），表示子查询已被转换为派生表。通过该标识可判断变换是否生效。

注意：说明：

注意：当变换条件不满足时（例如关联条件为非等值比较、子查询含 RAND() 等非确定性函数、子查询含 OFFSET 等），TDSQL Boundless 会静默回退到原始执行方式，查询正常执行且结果不变。这与社区 MySQL 的行为不同：社区 MySQL 在上述场景下会返回 ER_SUBQUERY_TRANSFORM_REJECTED 错误导致查询失败。

启用变换：该变换默认关闭，如需启用，执行 SET transformer_switch = 'subquery_to_derived=on'，session 级别即时生效。也可通过 optimizer_switch 设置 subquery_to_derived=on 达到相同效果。

十四、长 IN 列表转 Semi-Join

当 WHERE col IN (c1, c2, …, cN) 形式的 IN 列表元素数量超过阈值时，优化器可将其改写为基于 Hash 物化派生表的 Semi-Join，将匹配复杂度从 O(N) 降为 O(1)。该优化受 transformer_switch 中的 inlist_to_join 开关控制（默认开启），触发阈值为系统变量 qt_inlist_to_join_threshold（默认5000，范围2 – 1048576）。

改写机制：

— 改写前SELECT * FROM t WHERE col IN (c1, c2, …, cN);– 改写后（语义等价）SELECT * FROM t WHERE col IN ( SELECT _col_1 FROM (VALUES ROW(c1), ROW(c2), …, ROW(cN)) AS tvc_0);

改写后，IN 列表被转换为 Table Value Constructor（TVC）派生表，物化后建立 auto_key 索引实现 O(1) Hash 查找。可通过 INLIST_TO_JOIN(@qb_name) 和 NO_INLIST_TO_JOIN(@qb_name) Hint 分别强制启用或禁用。

验证方式：使用 EXPLAIN FORMAT=TREE 查看执行计划，出现 Materialize + scan on in-list: N rows 节点表示转换成功。

— 未触发转换（IN 列表元素少于阈值）EXPLAIN FORMAT=TREE SELECT * FROM t WHERE a IN (1,2,3,5);– -> Filter: (t.a IN (1,2,3,5))– -> Table scan on t– 触发转换（通过 Hint 强制或元素数超过阈值）EXPLAIN FORMAT=TREE SELECT /*+ INLIST_TO_JOIN(@`select#1`) */ * FROM t WHERE a IN (1,2,3,5);– -> Nested loop semijoin– -> Filter: (t.a IS NOT NULL)– -> Table scan on t– -> Filter: (t.a = tvc_0._col_1)– -> Index lookup on tvc_0 using <auto_key0> (_col_1=t.a)– -> Materialize– -> scan on in-list: 5 rows

注意：长 IN 列表转 Semi-Join 存在以下限制：仅适用于 SELECT 语句；LHS 不支持 BLOB、JSON、GEOMETRY 类型；必须在 AND 上下文中（OR 嵌套中的 IN 不做转换）；外连接（LEFT/RIGHT JOIN）的 ON 条件中的 IN 不做转换；NOT IN 默认不转换；RHS 必须全部为常量值且不含 NULL；optimizer_switch 中 semijoin=off 时不转换。

低基数列的潜在回退：对于 DATE/DATETIME/TIMESTAMP 等低基数时间类型列，转换后可能丧失存储引擎级并行扫描优势导致性能回退，因此默认对该类列不转换（可通过 INLIST_TO_JOIN Hint 绕过）。

关闭变换：执行 SET transformer_switch = 'inlist_to_join=off' 可关闭该优化，session 级别即时生效。

十五、子查询消除优化

除了去关联改写外，TDSQL Boundless 优化器在 SQL 解析绑定阶段还会尝试直接判定谓词子查询（EXISTS、IN、ANY、ALL）的真值。若子查询的结果在此阶段即可确定，优化器会将整个子查询谓词折叠为布尔常量 TRUE 或 FALSE，并完全移除该子查询，从而避免后续的查询优化与执行开销。该能力受 transformer_switch 中的 subquery_elimination 开关控制（默认开启）。

十六、消除场景

TDSQL Boundless 支持以下三类消除场景：

空表查询示例：EXISTS 子查询未引用任何表，恒定返回一行，因此恒为 TRUE。

SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM dual) ORDER BY a;– 等价于：SELECT a FROM t1 ORDER BY a;

标量聚合查询示例：聚合函数在隐式分组（无 GROUP BY）下必然返回一行，因此 EXISTS 恒为 TRUE。

SELECT a FROM t1 WHERE EXISTS (SELECT COUNT(*) FROM t2) ORDER BY a;– 等价于：SELECT a FROM t1 ORDER BY a;

常量空结果查询示例：子查询的 WHERE 条件在优化阶段可确定为常量 FALSE，结果集必为空。

— IN 谓词：空结果集折叠为 FALSESELECT a FROM t1 WHERE a IN (SELECT a FROM t2 WHERE FALSE) ORDER BY a;– 等价于：SELECT a FROM t1 WHERE FALSE ORDER BY a;（返回 0 行）– ALL 谓词：对空结果集恒为 TRUE（无反例）SELECT a FROM t1 WHERE a < ALL (SELECT a FROM t2 WHERE FALSE) ORDER BY a;– 等价于：SELECT a FROM t1 ORDER BY a;

十七、使用限制

以下场景不会被消除，子查询将保留原有执行路径：

·子查询包含 UNION、INTERSECT、EXCEPT。

·子查询包含窗口函数，或使用 WITH ROLLUP。

·子查询的 LIMIT 不是正整数常量，或 OFFSET 不为 0。

·子查询涉及加锁读，例如 FOR UPDATE、LOCK IN SHARE MODE。

·子查询中包含可能有副作用或存在运行时报错风险的表达式，例如 JSON 函数、空间函数、正则函数、DIV/MOD 运算、用户变量赋值、存储函数调用等。

·ANY/ALL 左侧操作数为行值表达式（例如 (a, b) > ALL (…)），仅支持标量左操作数。

·IN/ANY/ALL 左侧表达式包含不安全或可能报错的内容。

十八、验证与关闭

可通过 optimizer_trace 查看子查询消除的执行详情：

SET optimizer_trace = 'enabled=on';SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM dual);SELECT trace FROM information_schema.OPTIMIZER_TRACE;

命中消除时，trace 中的 subquery_elimination 节点包含 "transformed": true 及折叠结果 "type": true（折叠为 TRUE）或 "type": false（折叠为 FALSE）；未命中时包含 "transformed": false 及具体的 rejection_reason。

如需关闭该优化，执行以下语句，session 级别即时生效：

SET SESSION transformer_switch = 'subquery_elimination=off';

20、EXISTS 子查询

EXISTS 用于判断子查询是否返回了至少一行数据，常用于存在性检查。

查询有过高金额订单（金额 > 300000）的客户：

SELECT c.c_custkey, c.c_name, c.c_acctbalFROM customer cWHERE EXISTS ( SELECT 1 FROM orders o WHERE o.o_custkey = c.c_custkey AND o.o_totalprice > 300000);

查询没有下过订单的客户（NOT EXISTS）：

SELECT c.c_custkey, c.c_name, c.c_phoneFROM customer cWHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.o_custkey = c.c_custkey);

NOT EXISTS 在语义上等价于 LEFT JOIN … WHERE … IS NULL，但在某些场景下两者的执行效率不同，可通过 EXPLAIN 进行比较。

21、IN 子查询

IN 用于判断某个值是否在子查询返回的结果集中。

查询来自 ASIA 区域国家的客户：

SELECT c_custkey, c_name, c_nationkeyFROM customerWHERE c_nationkey IN ( SELECT n_nationkey FROM nation WHERE n_regionkey IN ( SELECT r_regionkey FROM region WHERE r_name = 'ASIA' ));

IN 与 EXISTS 的选择：当子查询返回的结果集较小时，IN 和 EXISTS 性能差异不大；当外层表较小而子查询结果集较大时，EXISTS 通常更高效。

22、标量子查询

标量子查询返回单个值，可以出现在 SELECT 列表、WHERE 条件等位置。

在 SELECT 列表中使用标量子查询 — 查询每个订单及其客户名称：

SELECT o_orderkey, o_totalprice, o_orderdate, (SELECT c_name FROM customer WHERE c_custkey = o_custkey) AS customer_nameFROM ordersWHERE o_orderdate = '1995-03-15';

SELECT 列表中的标量子查询在逻辑上会对每一行执行一次，当外层行数较多时建议改写为 JOIN：

SELECT o.o_orderkey, o.o_totalprice, o.o_orderdate, c.c_name AS customer_nameFROM orders oINNER JOIN customer c ON o.o_custkey = c.c_custkeyWHERE o.o_orderdate = '1995-03-15';

23、相关文档

并行子查询执行策略