连接（JOIN）消除

消除条件

LEFT JOIN的ON条件恒为FALSE的表达式（例如1=0或FALSE）。

场景示例

1. 准备环境。

   DROP TABLE IF EXISTS orders;
   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       order_date DATE
   );
   DROP TABLE IF EXISTS customers;
   CREATE TABLE customers (
       customer_id INT PRIMARY KEY,
       customer_name VARCHAR(100)
   );
   INSERT INTO orders VALUES (1, 101, '2023-10-01');

2. 优化前：关闭JOIN消除，执行一个ON FALSE的查询。

   SET SESSION join_elimination_mode = 'OFF';
   EXPLAIN SELECT o.*, c.* FROM orders o LEFT JOIN customers c ON FALSE;

   SHOW warnings;
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Level | Code | Message                                                                                                                                                                                                                                                                                                                          |
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Note  | 1003 | /* select#1 */ select `testdb`.`o`.`order_id` AS `order_id`,`testdb`.`o`.`customer_id` AS `customer_id`,`testdb`.`o`.`order_date` AS `order_date`,`testdb`.`c`.`customer_id` AS `customer_id`,`testdb`.`c`.`customer_name` AS `customer_name` from `testdb`.`orders` `o` left join `testdb`.`customers` `c` on(false) where true |
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3. 优化后：开启JOIN消除，执行相同的查询。

   SET SESSION join_elimination_mode = 'ON';
   EXPLAIN SELECT o.*, c.* FROM orders o LEFT JOIN customers c ON FALSE;

   SHOW warnings;
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Level | Code | Message                                                                                                                                                                                                                    |
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Note  | 1003 | /* select#1 */ select `testdb`.`o`.`order_id` AS `order_id`,`testdb`.`o`.`customer_id` AS `customer_id`,`testdb`.`o`.`order_date` AS `order_date`,NULL AS `customer_id`,NULL AS `customer_name` from `testdb`.`orders` `o` |
   +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

   执行计划显示customers表的访问被消除。

消除条件

• 内表未被引用：在LEFT JOIN及其ON条件以外的地方，都没有引用内表（即ti1, ti2, ..., tin）的任何列。

• 连接不影响外表基数或结果：需满足以下条件之一。

  • 连接具有唯一性：对于外表的每一行，经过左连接后有且仅能匹配到内表的一行数据。

  • 连接产生的重复行对结果无影响：即使连接导致外表的行被复制，但查询的后续操作会消除这些重复行的影响。常见情况包括：

    • 连接位于一个EXISTS或IN子句（即半连接，SEMI JOIN）中。

    • 子查询中包含了GROUP BY、LIMIT或窗口函数等限制条件。

    • 递归使用上述两项。

消除条件

• 子集关系：source表的结果集需是target表结果集的子集。

• 列可用性：查询中所有被引用的targe表的列，在source表中也需存在。简单来说，被保留的source表需包含查询所需的所有列。

• 唯一键连接：JOIN的连接条件需是基于target表中的一个唯一键（UNIQUE KEY）或主键（PRIMARY KEY）的等值比较。

  • 这个条件是关键，它保证了source表中的每一行最多只能匹配到target表中的一行。这确保了消除target表不会改变查询结果的行数和逻辑。

  • 特殊情况：如果连接条件中没有基于唯一键的等值比较，那么只有当target表的结果集本身只有0行或1行时，才可能进行消除。

消除条件

• 子集关系：source表的结果集需是target表结果集的子集。

• 列可用性：查询中所有被引用的targe表的列，在source表中也需存在。简单来说，被保留的source表需包含查询所需的所有列。

• 还需满足以下条件之一：

  • 唯一键连接：JOIN的连接条件需是基于target表中的一个唯一键（UNIQUE KEY）或主键（PRIMARY KEY）的等值比较。

    • 这个条件是关键，它保证了source表中的每一行最多只能匹配到target表中的一行。这确保了消除target表不会改变查询结果的行数和逻辑。

    • 特殊情况：如果连接条件中没有基于唯一键的等值比较，那么只有当target表的结果集本身只有0行或1行时，才可能进行消除。

  • 所有连接条件均为等值（即$$target.c_{a_k}=source.c_{i_{a_k}}^{{}^{\prime }}$$）连接，且与其他添加为AND连接。

消除条件

• 父表引用可替代：查询所引用的所有父表列，都可以通过子表的对应外键列来替代（通常意味着只引用了父表的连接键本身）。

• 连接条件基于外键关系：JOIN的条件是子表的外键列与父表的主键/唯一键列的等值比较。

• 外键非空保证：子表中的外键列被定义为NOT NULL，这确保了子表中的每一行在父表中都有确切的对应行。

消除条件

• 父表引用可替代：查询所引用的所有父表列，都可以通过子表的对应外键列来替代（通常意味着只引用了父表的连接键本身）。

• 连接条件基于外键关系：JOIN的条件是子表的外键列与父表的主键/唯一键列的等值比较。

• 外键非空保证：子表中的外键列被定义为NOT NULL，这确保了子表中的每一行在父表中都有确切的对应行。