PolarDB：消除冗餘的GroupBy/OrderBy

PolarDB叢集參數在控制台與會話中修改方式存在差異，詳細區別如下：

• 在PolarDB控制台上修改：

  • 相容性說明：部分叢集參數在PolarDB控制台上均已添加MySQL設定檔的相容性首碼loose_。

  • 操作方法：找到並修改這些帶loose_首碼的參數。

• 在資料庫會話中修改（使用命令列或用戶端）：

  • 操作方法：當您串連到資料庫，使用SET命令修改參數時，請去掉loose_首碼，直接使用參數的原始名稱進行修改。

情境一：根據主鍵或唯一鍵分組

當GROUP BY子句中包含了表的主鍵或唯一鍵時，由於主鍵已能唯一確定一行資料，因此對其他列進行分組是多餘的。

• 業務情境：統計每個使用者的訂單總額，同時展示使用者名稱。

• 原始SQL：

  -- user表的user_id是主鍵，可以唯一確定user_name
  SELECT
      u.user_id,
      u.user_name,
      COUNT(o.order_id)
  FROM user u
  JOIN orders o ON u.user_id = o.user_id
  GROUP BY
      u.user_id,
      u.user_name;

• 最佳化分析：因為user_id是主鍵，它唯一決定了user_name的值。最佳化器識別到user_id -> user_name的函數依賴關係，會自動將GROUP BY u.user_id, u.user_name簡化為GROUP BY u.user_id，從而消除對user_name的不必要分組操作。

情境二：根據常量分組

當GROUP BY子句中的所有列都是常量時，整個分組操作是冗餘的，因為結果集最多隻有一行。

• 業務情境：查詢某個特定使用者的特定狀態資訊（一種不常見的SQL寫法，但最佳化器能處理）。

• 原始SQL：

  SELECT a, b FROM t1 WHERE a = 1 AND b = 1 GROUP BY a, b;

• 最佳化分析：最佳化器識別到GROUP BY的列a和b在WHERE子句中已被指定為常量，因此會直接消除GROUP BY運算元，並添加LIMIT 1。

  SELECT a, b FROM t1 WHERE a = 1 AND b = 1 LIMIT 1;

場景三：多表串連下的複雜分組（TPC-H Q10）

在多表JOIN的複雜查詢中，函數依賴關係可以通過串連條件進行傳導，最佳化器能夠利用這種傳導性來消除更複雜的冗餘分組。

• 原始SQL（TPC-H Q10）：

  SELECT
      c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment,
      SUM(l_extendedprice * (1 - l_discount)) AS revenue
  FROM customer, orders, lineitem, nation
  WHERE c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND c_nationkey = n_nationkey
    -- 其他過濾條件...
  GROUP BY
      c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
  ORDER BY revenue DESC
  LIMIT 20;

• 最佳化分析：

  1. 主鍵依賴：在customer表中，c_custkey是主鍵，因此它唯一決定了c_name、c_acctbal、c_phone、c_address、c_comment、c_nationkey等所有其他列。

  2. 依賴傳導：由於JOIN條件c_nationkey = n_nationkey，並且n_nationkey是nation表的主鍵（決定n_name），最佳化器可以推匯出c_custkey也唯一決定了n_name。

  3. 最終簡化：綜上，GROUP BY子句中的所有列（c_name、c_acctbal等）都函數依賴於c_custkey。因此，最佳化器可以將複雜的GROUP BY列表直接簡化為GROUP BY c_custkey，大幅減少了分組計算的複雜度。

  SELECT 
     c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment,
     SUM(l_extendedprice * (1 - l_discount)) AS revenue
  FROM customer, orders, lineitem, nation
  WHERE c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND c_nationkey = n_nationkey
    -- 其他過濾條件...
  GROUP BY c_custkey
  ORDER BY revenue DESC
  LIMIT 20;

情境四：Order By情境

當ORDER BY子句中的列存在函數依賴關係時，最佳化器可以簡化排序操作，減少參與排序的列數。

• 業務情境：對錶進行排序，其中某些列由其他列唯一確定。

• 表結構：

  CREATE TABLE t1 (
    a int,
    b int,
    c int as (a + b) unique not null
  );

• 原始SQL：

  EXPLAIN SELECT a, b FROM t1 ORDER BY a,b,c;

• 最佳化分析 ：由於列c是由a和b計算得出的（c = a + b)，並且c被定義為unique not null，因此存在函數依賴關係：(a, b) -> c。最佳化器識別到這一依賴關係後，會自動將ORDER BY a, b, c簡化為ORDER BY a, b，從而減少排序操作的複雜度。

  SHOW warnings;
  +-------+------+-------------------------------------------------------------------------------------------------------------------------------+
  | Level | Code | Message                                                                                                                       |
  +-------+------+-------------------------------------------------------------------------------------------------------------------------------+
  | Note  | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` |
  +-------+------+-------------------------------------------------------------------------------------------------------------------------------+