云原生数据库 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` |
  +-------+------+-------------------------------------------------------------------------------------------------------------------------------+