PolarDB:冗長な GROUP BY/ORDER BY の削除

PolarDB クラスターのパラメーターを変更する方法は、コンソールとデータベースセッションで異なります。違いは次のとおりです：

• PolarDB コンソール

  • 互換性：MySQL 設定ファイルとの互換性のため、PolarDB コンソールの一部のクラスターパラメーターには loose_ プレフィックスが付いています。

  • 手順：loose_ プレフィックスが付いているパラメーターを見つけて変更します。

• データベースセッション (コマンドラインまたはクライアントを使用)

  • 手順：SET コマンドを使用してデータベースセッションでパラメーターを変更する場合は、loose_ プレフィックスを削除し、元のパラメーター名を使用します。

シナリオ 1：プライマリキーまたはユニークキーによるグループ化

GROUP BY 句にテーブルのプライマリキーまたは一意キーが含まれている場合、そのキーによってすでに一意の行が識別されるため、他の列でグループ化することは冗長です。

• ビジネスシナリオ：各ユーザーの合計注文額を計算し、そのユーザー名を表示します。

• 元の SQL：

  -- user_id は user テーブルのプライマリキーであり、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 の値を一意に決定します。オプティマイザーは FD user_id -> user_name を検出し、GROUP BY u.user_id, u.user_name を GROUP BY u.user_id に自動的に簡略化します。これにより、user_name に対する不要なグループ化操作が排除されます。

シナリオ 2：定数によるグループ化

GROUP BY 句のすべてのカラムが定数である場合、結果セットに含まれる行は最大で 1 行になるため、グループ化操作全体が冗長になります。

• ビジネスシナリオ：特定のユーザーの特定のステータス情報をクエリします。これは一般的ではない 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;

シナリオ 3：複数テーブル結合における複雑なグループ化 (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, and 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;

シナリオ 4：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)、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` |
  +-------+------+-------------------------------------------------------------------------------------------------------------------------------+