# 左连接消除
#### 操作场景
在业务系统中，ORM框架、视图展开、报表查询和通用SQL模板经常会生成包含多个LEFT JOIN的SQL。部分LEFT JOIN右侧表仅出现在连接条件中，查询结果并不需要读取该表的列；或者左侧表每行可能出现多次，而后续GROUP BY、DISTINCT会消除行数放大带来的影响。社区MySQL在执行这类查询时，通常仍会为这些LEFT JOIN生成访问路径并执行连接操作。对于复杂查询，这会带来额外的表扫描、索引查找、Join计算和中间结果处理开销，影响查询性能。
TaurusDB支持左连接消除功能。对于满足条件的查询，优化器可以在执行计划生成前移除结果无关的LEFT JOIN右侧表，使查询只访问真正需要的表，从而降低CPU、I/O和连接执行开销。
主要覆盖以下三类场景：
- 基于GROUP BY或DISTINCT的消除：LEFT JOIN右侧表只会放大左侧表行数，后续 GROUP BY或DISTINCT会消除重复，且不存在聚合函数等依赖行数的表达式时，可以移除右侧表。
- 基于唯一性的消除：ON 条件中的等值谓词可以确定右侧表唯一键的全部列时，每个左侧表行最多匹配一个右侧表行，可以移除右侧表。对于由GROUP BY 或 DISTINCT 形成唯一结果的派生表，也可以认为这些列具有唯一性。
- 基于EXISTS/IN等子查询语义的消除：当 LEFT JOIN 位于 EXISTS、IN、NOT EXISTS 或 NOT IN 等子查询中，且右侧表不影响子查询的布尔判断结果时，可以移除该 LEFT JOIN 右侧表。
 
#### 原理介绍
左连接消除是一种基于关系代数等价规则的查询改写。优化器在保证结果集不变的前提下，识别并删除冗余的LEFT JOIN。
对于如下查询：
```
SELECT t1.a
FROM t1
LEFT JOIN t2 ON t2.a = t1.b;
```
如果t2.a是唯一键，并且t2的列没有在 SELECT、WHERE、GROUP BY、HAVING、ORDER BY等位置被引用，则对 t1 的每一行，t2 最多只匹配一行。由于 LEFT JOIN不会过滤 t1 的行，且t2列不影响最终输出，该查询可等价改写为：
```
SELECT t1.a
FROM t1;
```
左连接消除在查询解析（resolver）阶段执行，位于 Join 结构简化和 Join 信息收集之后。优化器会先简化 Join 结构，再分析 LEFT JOIN 右侧表是否被外部引用、是否满足唯一性或去重条件。满足条件时，右侧表会从后续执行计划中移除。因此，开启功能后，被消除的表不会出现在 EXPLAIN 执行计划中；通过 optimizer_trace 的 join_preparation 步骤可以看到 join_elimination 节点及被消除表数量。
#### 转换过程
原查询：
```
SELECT t1.a
FROM t1
LEFT JOIN t2 ON t2.a > t1.b
GROUP BY t1.a;
```
转换后的查询：
```
SELECT t1.a
FROM t1
GROUP BY t1.a;
```
当 t2 仅在 LEFT JOIN 的 ON 条件中出现，且 ON t2.a \> t1.b 可能使 t1 每行出现多次匹配、GROUP BY t1.a 会消除 t2 带来的重复行时，优化器可以移除 t2 表的访问。
#### 前提条件
- TaurusDB内核版本为2.0.75.260300及以上的版本。内核版本的查询方法请参见[如何查看云数据库 TaurusDB实例的版本号](https://support.huaweicloud.com/taurusdb_faq/taurusdb_faq_0141.html)。
- 系统参数optimizer_switch中left_join_elimination为ON。详细内容请参见[修改TaurusDB实例参数](https://support.huaweicloud.com/usermanual-taurusdb/taurusdb_configuration.html)。
- SQL语句中存在LEFT JOIN/RIGHT JOIN，且满足 LEFT JOIN 消除的条件，消除后不影响查询结果。
 
#### 支持的查询语句
- SELECT
- INSERT ... SELECT
- REPLACE ... SELECT
- CREATE TABLE ... SELECT
- 查询语句中的子查询
- 视图、派生表和 Prepared Statement 中的符合条件查询
 
#### 约束限制
表1约束限制 
| 类别              | 约束项                     | 说明                                                                                                                                       |
|:---|:---|:---|
| 支持的连接类型         | LEFT JOIN               | 支持消除。                                                                                                                                    |
| 支持的连接类型         | RIGHT JOIN              | 在解析阶段会转换为等价LEFT JOIN，符合条件时可触发优化。                                                                                                         |
| 不支持的场景          | INNER JOIN              | 不支持内连接消除。                                                                                                                                |
| 不支持的场景          | 多表 UPDATE               | 不支持多表UPDATE 中的 LEFT JOIN 消除。                                                                                                             |
| 不支持的场景          | 多表 DELETE               | 不支持多表DELETE 中的 LEFT JOIN 消除。                                                                                                             |
| 字段引用限制          | SELECT                  | 一般情况：查询列引用右侧表字段，不会进行左连接消除。 EXISTS/IN子查询内：不受此限制，因为EXISTS仅关注是否有行返回，IN不关注右侧表的列值，引用右侧表字段不影响消除。 |
| 字段引用限制          | WHERE                   | 一般情况：过滤条件引用右侧表字段，不会进行左连接消除。 EXISTS/IN子查询内：不受此限制，因为EXISTS仅关注是否有行返回，IN不关注右侧表的列值，引用右侧表字段不影响消除。 |
| 字段引用限制          | GROUP BY                | 分组列中引用右侧表字段，不会进行左连接消除。                                                                                                                   |
| 字段引用限制          | HAVING                  | 分组过滤条件中引用右侧表字段，不会进行左连接消除。                                                                                                                |
| 字段引用限制          | ORDER BY                | 排序列中引用右侧表字段，不会进行左连接消除。                                                                                                                   |
| 字段引用限制          | 窗口函数                    | 窗口函数定义或使用中引用右侧表字段，不会进行左连接消除。                                                                                                             |
| 字段引用限制          | 其他 JOIN 的 ON 条件         | 其他连接的连接条件中引用右侧表字段，不会进行左连接消除。                                                                                                             |
| 字段引用限制          | ON DUPLICATE KEY UPDATE | 写入冲突更新中引用右侧表字段，不会进行左连接消除。                                                                                                                |
| 字段引用限制          | DEFAULT(col) 函数         | DEFAULT(col) 函数调用中引用右侧表字段，不会进行左连接消除。                                                                                                     |
| 聚合函数相关          | 包含聚合函数                  | 不会基于 GROUP BY 或 DISTINCT 规则进行左连接消除。                                                                                                      |
| 聚合函数相关          | 安全条件满足                  | 唯一性等其他安全条件满足时，仍可能进行左连接消除。                                                                                                                |
| DISTINCT + 窗口函数 | 仅依赖 DISTINCT 去重且包含窗口函数  | 不会基于DISTINCT规则消除右侧表。                                                                                                                     |
| DISTINCT + 窗口函数 | 同时存在 GROUP BY           | 优化器会按 GROUP BY 语义继续判断。                                                                                                                   |
| 唯一性判断限制         | OR 条件                   | SQL语句中包含OR的连接条件时，不符合左连接消除条件。                                                                                                             |
| 唯一性判断限制         | \<=\> 比较                | SQL语句中包含\<=\> 比较时，不符合左连接消除条件。                                                                                                            |
| 唯一性判断限制         | 非确定性表达式                 | SQL语句中包含RAND() 等，不符合左连接消除条件。                                                                                                             |
| 唯一性判断限制         | 字符串与非字符串比较              | SQL语句中对右侧字符串列与非字符串表达式比较，不符合左连接消除条件。                                                                                                      |
| 唯一性判断限制         | 排序规则不一致                 | SQL语句中两侧均为字符串但比较排序规则与右侧列排序规则不一致，不符合左连接消除条件。                                                                                              |
| 唯一性判断限制         | 其他情况                    | 即使唯一性消除被放弃，仍可能通过 GROUP BY/DISTINCT 或 EXISTS/IN 等子查询语义消除。                                                                                 |
   
#### 使用方法
您可以通过 optimizer_switch 参数设置 LEFT JOIN 消除功能。
表2参数说明 
| 参数名称             | 级别             | 描述                                                                                    |
|:---|:---|:---|
| optimizer_switch | Global、Session | 通过 left_join_elimination=on 开启 LEFT JOIN 消除功能，通过left_join_elimination=off 关闭该功能。默认关闭。 |
   
开启功能：
```
SET optimizer_switch='left_join_elimination=ON';
```
关闭功能：
```
SET optimizer_switch='left_join_elimination=OFF';
```
#### 使用示例
创建测试表：
```
CREATE TABLE t1(a INT PRIMARY KEY, b INT);
CREATE TABLE t2(a INT PRIMARY KEY, b INT);
INSERT INTO t1 VALUES(1,1),(2,3),(7,NULL);
INSERT INTO t2 VALUES(2,2),(4,5),(5,4);
ANALYZE TABLE t1,t2;
```
- [基于 GROUP BY 或 DISTINCT 的消除]
  先关闭 LEFT JOIN 消除：
  ```
  SET optimizer_switch='left_join_elimination=off';
  EXPLAIN FORMAT=TREE SELECT t1.a
  FROM t1
  LEFT JOIN t2 ON t2.a > t1.b 
  GROUP BY t1.a\G
  ```
  关闭 LEFT JOIN 消除后，执行计划中会保留 t2：
  ```
  *************************** 1. row ***************************
  EXPLAIN: -> Group (no aggregates)
      -> Nested loop left join  (cost=1.70 rows=9)
          -> Index scan on t1 using PRIMARY  (cost=0.55 rows=3)
          -> Filter: (t2.a > t1.b)  (cost=0.18 rows=3)
              -> Index range scan on t2 (re-planned for each iteration)  (cost=0.18 rows=3)
  ```
  再开启 LEFT JOIN 消除：
  ```
  SET optimizer_switch='left_join_elimination=on';
  EXPLAIN FORMAT=TREE SELECT t1.a
  FROM t1
  LEFT JOIN t2 ON t2.a > t1.b 
  GROUP BY t1.a\G
  ```
  开启左连接消除后，执行计划中只包含 t1，消除t2表：
  ```
  *************************** 1. row ***************************
  EXPLAIN: -> Table scan on t1  (cost=0.55 rows=3)
  ```
- [基于唯一性的消除]
  当 ON 条件绑定右侧表唯一键时，优化器可以证明每个左侧表行最多匹配一个右侧表行：
  ```
  SET optimizer_switch='left_join_elimination=off';
  EXPLAIN FORMAT=TREE SELECT t1.a
  FROM t1
  LEFT JOIN t2 ON t2.a = t1.b\G
  ```
  关闭左连接消除后，执行计划中会保留 t2，并通过主键进行 eq_ref 访问：
  ```
  *************************** 1. row ***************************
  EXPLAIN: -> Nested loop left join  (cost=1.60 rows=3)
      -> Table scan on t1  (cost=0.55 rows=3)
      -> Single-row index lookup on t2 using PRIMARY (a=t1.b)  (cost=0.28 rows=1)
  ```
  开启左连接消除后，执行计划中只包含 t1：
  ```
  SET optimizer_switch='left_join_elimination=on';
  EXPLAIN FORMAT=TREE SELECT t1.a
  FROM t1
  LEFT JOIN t2 ON t2.a = t1.b\G
  *************************** 1. row ***************************
  EXPLAIN: -> Table scan on t1  (cost=0.55 rows=3)
  ```
- [基于 EXISTS/IN 等子查询语义的消除]
  当LEFT JOIN位于EXISTS 子查询中，且右侧表不影响子查询的布尔判断结果时，可以移除该右侧表：
  ```
  SET optimizer_switch='left_join_elimination=off';
  EXPLAIN FORMAT=TREE SELECT t0.a
  FROM t1 AS t0
  WHERE EXISTS (
    SELECT t2.b
    FROM t1 LEFT JOIN t2 ON t2.a > t1.b
    WHERE t0.a = t1.b
  )\G
  ```
  关闭左连接消除后，t2 仍保留在子查询转换后的执行计划中：
  ```
  *************************** 1. row ***************************
  EXPLAIN: -> Remove duplicate t0 rows using temporary table (weedout)  (cost=2.75 rows=9)
      -> Nested loop left join  (cost=2.75 rows=9)
          -> Nested loop inner join  (cost=1.60 rows=3)
              -> Filter: (t1.b is not null)  (cost=0.55 rows=3)
                  -> Table scan on t1  (cost=0.55 rows=3)
              -> Single-row index lookup on t0 using PRIMARY (a=t1.b)  (cost=0.28 rows=1)
          -> Filter: (t2.a > t1.b)  (cost=0.55 rows=3)
              -> Index range scan on t2 (re-planned for each iteration)  (cost=0.55 rows=3)
  ```
  开启左连接消除后，t2 从子查询转换后的执行计划中移除：
  ```
  SET optimizer_switch='left_join_elimination=on';
  EXPLAIN FORMAT=TREE SELECT t0.a
  FROM t1 AS t0
  WHERE EXISTS (
    SELECT t2.b
    FROM t1 LEFT JOIN t2 ON t2.a > t1.b
    WHERE t0.a = t1.b
  )\G
  *************************** 1. row ***************************
  EXPLAIN: -> Nested loop inner join  (cost=1.15 rows=3)
      -> Filter: (`<subquery2>`.b is not null)  (cost=0.60 rows=3)
          -> Table scan on <subquery2>  (cost=0.60 rows=3)
              -> Materialize with deduplication  (cost=0.55 rows=3)
                  -> Filter: (t1.b is not null)  (cost=0.55 rows=3)
                      -> Table scan on t1  (cost=0.55 rows=3)
      -> Single-row index lookup on t0 using PRIMARY (a=`<subquery2>`.b)  (cost=0.35 rows=1)
  ```
  执行计划可以看出，子查询内的t2被消除，优化生效。
  NOT EXISTS 和 NOT IN 等否定子查询在满足同类条件时也可以触发该优化，也可以通过 optimizer_trace 确认是否发生消除。开启 trace 后，如果命中左连接消除，join_elimination 节点会出现在join_preparation 步骤中：
  ```
  {
    "steps": [
      {
        "join_preparation": {
          "steps": [
            {
              "join_elimination": {
                "number_of_eliminated_tables": 1,
                "expanded_query": "/* select#1 */ select `t0`.`a` AS `a` from `t1` `t0` semi join (`t1`) where ((`t0`.`a` = `t1`.`b`))"
              }
            }
          ]
        }
      }
    ]
  }
  ```