PolarDB:列ストアインデックスのソートキーを設定する

はじめに

列ストアインデックスデータは、行グループによって編成されます。デフォルトでは、各行グループには 64,000 行が含まれます。各行グループ内で、異なる列が列データブロックにパッケージ化されます。これらのブロックは、元のローストアデータのプライマリキーの順序に基づいて並列に構築されます。更新されたデータは追加順に書き込まれるため、データは通常ソートされていません。

列ストアインデックスは、ラフインデックスをサポートしています。各列データブロックのメタデータには、ブロック内のすべてのデータの最小値や最大値などの情報が含まれています。データをクエリすると、通常、指定された列のすべての列データブロックが走査されます。IMCI プルーナーを有効にすると、すべての列データブロックは、クエリ条件とメタデータに基づいて、関連、関連の可能性あり、無関係の 3 つのカテゴリに分類されます。関連および関連の可能性のある列データブロックのみが読み取られます。列データブロックは異なる順序で編成できるため、列データブロックコレクションの組み合わせが異なり、IMCI プルーナーのフィルタリング効率に影響します。したがって、クエリ条件に基づいて列データブロックの配置を変更して、クエリパフォーマンスを向上させることができます。

上の図に示すように、次の SQL 文が順序付けられていないデータブロックのセットに対して実行されると、すべての列データブロックをロードして処理する必要があります。順序付けられたデータブロックのセットの場合、最初の列データブロックは、そのメタデータの最大値と最小値に基づいて除外できます。2 番目の列データブロックのみを処理する必要があります。

SELECT * FROM t WHERE c >= 8;

適用性

• 新しい列ストアインデックスの作成時のデータソート機能を使用するには、次のいずれかの要件を満たす Enterprise Edition クラスターが必要です。

  • PolarDB for MySQL 8.0.1、リビジョンバージョン 8.0.1.1.32 以降。

  • PolarDB for MySQL 8.0.2、リビジョンバージョン 8.0.2.2.12 以降。

• 増分データソート機能を使用するには、次のいずれかの要件を満たす Enterprise Edition クラスターが必要です。

  • PolarDB for MySQL 8.0.1、リビジョンバージョン 8.0.1.1.39.1 以降。

  • PolarDB for MySQL 8.0.2、リビジョンバージョン 8.0.2.2.20.1 以降。

クラスターのバージョンを確認できます。詳細については、「バージョン番号のクエリ」をご参照ください。

注意事項

• BLOB、JSON、または GEOMETRY データ型の列は、ソートキーとして使用できません。

• 増分データソート機能は、符号なし整数または Decimal データ型のソートキーをサポートしていません。

• 増分ソートは、ソートキーの最初の列にのみ基づいて順序を維持します。

• 増分データソートは、特定量のリソースを消費します。したがって、クラスターの書き込みワークロードが高い場合、増分データソートの速度が低下し、データ書き込み操作のためにより多くのリソースが解放されます。

ソートプロシージャ

• 新しい列ストアインデックスを作成するときのデータソートプロシージャ

  列ストアインデックスのデータソートは、データ定義言語 (DDL) プロセス中のセカンダリインデックスのソートアルゴリズムと同様の方法で実装されます。シングルスレッドソートとマルチスレッドソートの両方がサポートされています。シングルスレッドソートでは、標準の 2 方向マージソートを使用します。マルチスレッドソートでは、ルーザー木を使用した k 方向外部マージソートを使用し、サンプリングソートポリシーをサポートします。全体的なプロセスは次のとおりです。

  1. プライマリキーインデックスに基づいてデータを走査し、読み取られた完全なデータをデータファイルに保存します。次に、ソートキー列をソートバッファーに追加します。各スレッドは異なるデータファイルを使用し、特定の量が蓄積された後にデータを書き込みます。

  2. ソートバッファーにデータを継続的に走査して挿入します。ソートバッファーがいっぱいになると、ソートキーの組み合わせに基づいてメモリ内のデータをソートし、データをマージファイルに保存します。

  3. 走査が完了したら、マージファイルをセグメントごとにペアでソートします。ソートされたデータを一時ファイルに保存します。次に、マージファイルを一時ファイルに置き換えます。

  4. マージファイルがソートされるまでステップ 3 を繰り返します。次に、マージファイル内の各レコードを読み取り、オフセット値に基づいてデータファイルから対応するレコードを取得し、列ストアインデックスに追加します。

• 増分データソートプロシージャ

  増分データのソートプロセスは段階的であり、データが完全にソートされることを保証するものではありません。全体的なプロセスは次のとおりです。

  1. すべてのデータブロックをペアでグループ化し、タイムスタンプ範囲の重複度が高いデータブロックの複数のグループを選択します。

  2. データブロックの各グループでマージソートを実行して、2 つの新しいソート済みデータブロックを生成します。

  3. すべてのデータブロックがソートされるまでステップ 2 を繰り返します。

パラメーターの説明

列ストアインデックスのソート機能を有効または無効にし、必要に応じてスレッド数を構成するには、データベースで次の表のパラメーターを設定する必要があります。

使用上の注意

次の手順に従って、列ストアインデックスデータソート機能を使用できます。

1. 列ストアインデックスのソート機能を有効にします。

   imci_enable_pack_order_key パラメーターを ON に設定して、新しい列ストアインデックスの作成時のデータソート機能を有効にします。

2. 次の SQL 文の comment に order_key プロパティを追加して、ソートされた列ストアインデックスデータを構築します。

   ALTER TABLE table_name COMMENT 'columnar=1 order_key=column_name[,column_name]';

   パラメーターの説明:

   パラメーター	説明
   table_name	テーブル名。
   column_name	列名。複数の列名を設定できます。複数の列名はコンマ (,) で区切ります。

   INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS テーブルで IMCI 作成の進捗状況をモニターできます。INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS テーブルの詳細については、「IMCI の DDL 実行速度とビルド進捗の表示」をご参照ください。

SELECT * FROM INFORMATION_SCHEMA.IMCI_ASYNC_DDL_STATS;

列ストアインデックスデータソートと DDL ソートの違い

列ストアインデックスデータのソートは、本質的には指定されたキーの組み合わせによるソートであり、他のセカンダリインデックスの DDL ソートプロセスに似ています。ただし、次の点で異なります。

• 列ストアインデックスのソートでは、インデックスキー列をソートキーとして使用しません。代わりに、列の任意の組み合わせをソートキーとして指定できます。

• 列ストアインデックスのソート後、完全なデータを読み取る必要があります。セカンダリインデックス DDL の場合、データのインデックス部分のみを保存する必要があります。たとえば、VARCHAR フィールドの場合、プレフィックスのみがインデックスデータとして保存されます。

ソートされた列ストアインデックスデータの構築時間とクエリ時間の比較

この例では、100 GB の TPC-H データセットを使用して、ソートされた列ストアインデックスデータの構築とクエリに必要な時間をテストします。

• ソートされた列ストアインデックスデータの構築時間をテストします。

  この例では、16 個の並列スレッドを使用して lineitem テーブルのソート済み列ストアインデックスデータを構築します。以下はサンプル文です。

  ALTER TABLE lineitem COMMENT='columnar=1 order_key=l_receiptdate,l_shipmode';

  構築時間は次のとおりです。

  順序なしデータセット	順序付きデータセット
  6 分	35 分

• ソートされた列ストアインデックスデータのクエリ時間をテストします。

  この例では、TPC-H Q12 クエリを実行します。最近使用されていない (LRU) キャッシュとエグゼキュータメモリは両方とも 10 GB に設定されています。以下はクエリ文です。

  SELECT
      l_shipmode,
      SUM(CASE
          WHEN o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH'
          THEN 1
          ELSE 0
          END) AS high_line_count,
      SUM(CASE
          WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH'
          THEN 1
          ELSE 0
          END) AS low_line_count
      FROM
          orders,
          lineitem
      WHERE
          o_orderkey = l_orderkey
          AND l_shipmode in ('MAIL', 'SHIP')
          AND l_commitdate < l_receiptdate
          AND l_shipdate < l_commitdate
          AND l_receiptdate >= date '1994-01-01'
          AND l_receiptdate < date '1994-01-01' + interval '1' year
      GROUP BY
          l_shipmode
      ORDER BY
          l_shipmode;

  クエリ時間は次のとおりです。

  順序なしデータセット	順序付きデータセット
  7.47s	1.25s

ソートキーとパーティションテーブルを使用したクエリ時間の比較

このテストでは、32 コアと 256 GB のメモリを搭載したノードで 1 TB の TPC-H データセットを使用します。列ストアインデックス機能が有効になっている場合 (列ストア) と、パーティションとソートキー列で列ストアインデックス機能が有効になっている場合のクエリパフォーマンスをテストします。

テストには、次の標準テーブル作成ステートメントが使用されます。

CREATE TABLE region ( r_regionkey  BIGINT NOT NULL,
                      r_name       CHAR(25) NOT NULL,
                      r_comment    VARCHAR(152)) COMMENT 'COLUMNAR=1';

CREATE TABLE nation ( n_nationkey  BIGINT NOT NULL,
                      n_name       CHAR(25) NOT NULL,
                      n_regionkey  BIGINT NOT NULL,
                      n_comment    VARCHAR(152)) COMMENT 'COLUMNAR=1';

CREATE TABLE part ( p_partkey     BIGINT NOT NULL,
                    p_name        VARCHAR(55) NOT NULL,
                    p_mfgr        CHAR(25) NOT NULL,
                    p_brand       CHAR(10) NOT NULL,
                    p_type        VARCHAR(25) NOT NULL,
                    p_size        BIGINT NOT NULL,
                    p_container   CHAR(10) NOT NULL,
                    p_retailprice DECIMAL(15,2) NOT NULL,
                    p_comment     VARCHAR(23) NOT NULL) COMMENT 'COLUMNAR=1';

CREATE TABLE supplier ( s_suppkey     BIGINT NOT NULL,
                        s_name        CHAR(25) NOT NULL,
                        s_address     VARCHAR(40) NOT NULL,
                        s_nationkey   BIGINT NOT NULL,
                        s_phone       CHAR(15) NOT NULL,
                        s_acctbal     DECIMAL(15,2) NOT NULL,
                        s_comment     VARCHAR(101) NOT NULL) COMMENT 'COLUMNAR=1';

CREATE TABLE partsupp ( ps_partkey     BIGINT NOT NULL,
                        ps_suppkey     BIGINT NOT NULL,
                        ps_availqty    BIGINT NOT NULL,
                        ps_supplycost  DECIMAL(15,2)  NOT NULL,
                        ps_comment     VARCHAR(199) NOT NULL) COMMENT 'COLUMNAR=1';

CREATE TABLE customer ( c_custkey     BIGINT NOT NULL,
                        c_name        VARCHAR(25) NOT NULL,
                        c_address     VARCHAR(40) NOT NULL,
                        c_nationkey   BIGINT NOT NULL,
                        c_phone       CHAR(15) NOT NULL,
                        c_acctbal     DECIMAL(15,2)   NOT NULL,
                        c_mktsegment  CHAR(10) NOT NULL,
                        c_comment     VARCHAR(117) NOT NULL) COMMENT 'COLUMNAR=1';

CREATE TABLE orders ( o_orderkey       BIGINT NOT NULL,
                      o_custkey        BIGINT NOT NULL,
                      o_orderstatus    CHAR(1) NOT NULL,
                      o_totalprice     DECIMAL(15,2) NOT NULL,
                      o_orderdate      DATE NOT NULL,
                      o_orderpriority  CHAR(15) NOT NULL,
                      o_clerk          CHAR(15) NOT NULL,
                      o_shippriority   BIGINT NOT NULL,
                      o_comment        VARCHAR(79) NOT NULL) COMMENT 'COLUMNAR=1'
                      PARTITION BY RANGE (year(`o_orderdate`))
                      (PARTITION p0 VALUES LESS THAN (1992) ENGINE = InnoDB,
                       PARTITION p1 VALUES LESS THAN (1993) ENGINE = InnoDB,
                       PARTITION p2 VALUES LESS THAN (1994) ENGINE = InnoDB,
                       PARTITION p3 VALUES LESS THAN (1995) ENGINE = InnoDB,
                       PARTITION p4 VALUES LESS THAN (1996) ENGINE = InnoDB,
                       PARTITION p5 VALUES LESS THAN (1997) ENGINE = InnoDB,
                       PARTITION p6 VALUES LESS THAN (1998) ENGINE = InnoDB,
                       PARTITION p7 VALUES LESS THAN (1999) ENGINE = InnoDB);

CREATE TABLE lineitem ( l_orderkey       BIGINT NOT NULL,
                        l_partkey        BIGINT NOT NULL,
                        l_suppkey        BIGINT NOT NULL,
                        l_linenumber     BIGINT NOT NULL,
                        l_quantity       DECIMAL(15,2) NOT NULL,
                        l_extendedprice  DECIMAL(15,2) NOT NULL,
                        l_discount       DECIMAL(15,2) NOT NULL,
                        l_tax            DECIMAL(15,2) NOT NULL,
                        l_returnflag     CHAR(1) NOT NULL,
                        l_linestatus     CHAR(1) NOT NULL,
                        l_shipdate       DATE NOT NULL,
                        l_commitdate     DATE NOT NULL,
                        l_receiptdate    DATE NOT NULL,
                        l_shipinstruct   CHAR(25) NOT NULL,
                        l_shipmode       CHAR(10) NOT NULL,
                        l_comment        VARCHAR(44) NOT NULL) COMMENT 'COLUMNAR=1'
                        PARTITION BY RANGE (year(`l_shipdate`))
                        (PARTITION p0 VALUES LESS THAN (1992) ENGINE = InnoDB,
                         PARTITION p1 VALUES LESS THAN (1993) ENGINE = InnoDB,
                         PARTITION p2 VALUES LESS THAN (1994) ENGINE = InnoDB,
                         PARTITION p3 VALUES LESS THAN (1995) ENGINE = InnoDB,
                         PARTITION p4 VALUES LESS THAN (1996) ENGINE = InnoDB,
                         PARTITION p5 VALUES LESS THAN (1997) ENGINE = InnoDB,
                         PARTITION p6 VALUES LESS THAN (1998) ENGINE = InnoDB,
                         PARTITION p7 VALUES LESS THAN (1999) ENGINE = InnoDB);

データがインポートされた後、テーブルのソートキー列を設定します。詳細については、「列ストアインデックスのソートキーを設定する」をご参照ください。

ALTER TABLE customer COMMENT='COLUMNAR=1 order_key=c_mktsegment';
ALTER TABLE nation COMMENT='COLUMNAR=1 order_key=n_name';
ALTER TABLE part COMMENT='COLUMNAR=1 order_key=p_brand,p_container,p_type';
ALTER TABLE region COMMENT='COLUMNAR=1 order_key=r_name';
ALTER TABLE orders COMMENT='COLUMNAR=1 order_key=o_orderkey,o_custkey,o_orderdate';
ALTER TABLE lineitem COMMENT='COLUMNAR=1 order_key=l_orderkey,l_linenumber,l_receiptdate,l_shipdate,l_partkey';

テストには、TPC-H クエリ文のセレクションが使用されます。クエリ時間を次の表に示します。