子查询优化

PolarDB集群参数在控制台与会话中修改方式存在差异，详细区别如下：

• 在PolarDB控制台上修改：

  • 兼容性说明：部分集群参数在PolarDB控制台上均已添加MySQL配置文件的兼容性前缀loose_。

  • 操作方法：找到并修改这些带loose_前缀的参数。

• 在数据库会话中修改（使用命令行或客户端）：

  • 操作方法：当您连接到数据库，使用SET命令修改参数时，请去掉loose_前缀，直接使用参数的原始名称进行修改。

场景一：消除冗余的SELECT嵌套

场当子查询仅仅是为了包裹一个聚合函数或表达式，而没有其他复杂逻辑时（例如SELECT (SELECT SUM(a) FROM t2) FROM dual），优化器会剥离掉外层的SELECT，直接执行核心的表达式。

-- 出现在投影列
SELECT (SELECT SUM(a) FROM t2) FROM dual;
-- 优化后
SELECT SUM(`test`.`t2`.`a`) AS `sum(a)` FROM `test`.`t2`

-- 出现在HAVING子句中
SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));
-- 优化后
SELECT SUM(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` HAVING (0 <> count(`testdb`.`t2`.`b`))

1. 准备测试数据：

   DROP TABLE IF EXISTS t2;
   CREATE TABLE t2 (
       id INT PRIMARY KEY AUTO_INCREMENT,
       a INT,
       b INT
   );
   INSERT INTO t2 (a, b) VALUES (10, 100), (20, NULL), (50, 200), (120, NULL);

2. 关闭优化进行查询：首先，在会话中关闭子查询优化功能，以观察优化前的执行计划。

   SET simplify_subq_mode = 'OFF';
   EXPLAIN SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));

   SHOW warnings;
   
   -- 返回结果
   /* select#1 */ select sum(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` having (0 <> (/* select#2 */ select (/* select#3 */ select (/* select#4 */ select count(`testdb`.`t2`.`b`)))))

3. 开启优化进行查询：现在，在会话中子查询优化功能，并执行相同的查询。

   SET simplify_subq_mode = 'ON';
   EXPLAIN SELECT SUM(a) FROM t2 HAVING (SELECT(SELECT(SELECT count(b))));

   SHOW warnings;
   
   -- 返回结果
   /* select#1 */ select sum(`testdb`.`t2`.`a`) AS `SUM(a)` from `testdb`.`t2` having (0 <> count(`testdb`.`t2`.`b`))

场景二：提前判断[NOT] EXISTS子查询

当[NOT] EXISTS子查询的条件可以被静态推断为始终为真（结果非空）或始终为假（结果为空）时，优化器会直接将[NOT] EXISTS子句替换为TRUE或FALSE，从而避免执行子查询。

-- 非空集合
SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);
-- 优化后
SELECT * FROM t1

-- 空集合 WHERE/HAVING子句=false, 或者LIMIT 0
SELECT * FROM t1 WHERE EXISTS(SELECT max(a) FROM t2 HAVING 1=2 );
-- 优化后
SELECT * FROM t1 WHERE false

1. 准备测试数据：

   DROP TABLE IF EXISTS t1;
   DROP TABLE IF EXISTS t2;
   CREATE TABLE t1 (id INT);
   CREATE TABLE t2 (val INT);
   INSERT INTO t1 VALUES (1), (2);

2. 关闭优化进行查询：首先，在会话中关闭子查询优化功能，以观察优化前的执行计划。

   SET simplify_subq_mode = 'OFF';
   EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);

   SHOW warnings;
   
   -- 返回结果
   /* select#1 */ select `testdb`.`t1`.`id` AS `id` from `testdb`.`t1` where exists(/* select#2 */ select max(`testdb`.`t1`.`id`) from `testdb`.`t2`)

3. 开启优化进行查询：现在，在会话中子查询优化功能，并执行相同的查询。

   SET simplify_subq_mode = 'ON';
   EXPLAIN SELECT * FROM t1 WHERE EXISTS(SELECT MAX(a) FROM t2);

   SHOW warnings;
   
   -- 返回结果
   /* select#1 */ select `testdb`.`t1`.`id` AS `id` from `testdb`.`t1`

场景三：为ANY/ALL子查询的常量投影添加LIMIT 1

当ANY或ALL子句中的子查询只查询常量（不涉及任何列）时，获取多行相同的值是没有意义的。优化器会自动为这类子查询添加LIMIT 1，避免对子查询的表进行不必要的全表扫描。

-- 优化前
SELECT * FROM t1 WHERE a > ANY (SELECT 1 FROM t2);
-- 优化后
SELECT * FROM t1 WHERE a > ANY (SELECT 1 FROM t2 LIMIT 1);