PolarDB：串連（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，這確保了子表中的每一行在父表中都有確切的對應行。