部分インデックスの作成方法 - PolarDB - Alibaba Cloud ドキュメントセンター

部分インデックスは、パーティションテーブル全体ではなく、指定したパーティションのみを対象とします。これにより、インデックスがコンパクトに保たれ、クエリが特定のパーティション（例：直近の数か月や特定のテナントグループなど）をターゲットとする場合に有効です。一方で、その他のデータは頻繁にアクセスされない、あるいはまったくアクセスされない場合があります。

部分インデックスを利用するタイミング

クエリワークロードが特定のパーティションのサブセットに集中する場合に、部分インデックスを利用します。

ホットデータ／コールドデータの分離：直近のパーティションにはクエリトラフィックが集中しますが、古いパーティションはほとんどクエリされません。コールドデータに対するインデックスサイズおよび書き込みオーバーヘッドを削減するため、直近のパーティションのみを対象にインデックスを作成します。
テナント固有のクエリ：マルチテナントテーブルでは、クエリが特定のテナントグループに限定されることが一般的です。フルテーブルインデックスの維持を回避するため、関連するパーティションのみを対象にインデックスを作成します。
選択的な範囲クエリ：常に既知のパーティション範囲でフィルター処理を行うレポートの場合、該当パーティションに部分インデックスを設定すると、グローバルインデックスと比較してサイズが小さく、スキャン速度も向上します。

クエリが定期的にすべてのパーティションを横断する場合、またはテーブルが十分に小さいためフルインデックスの管理が容易な場合は、グローバルインデックスの方が適している可能性があります。

パーティションテーブル作成時に部分インデックスを作成する

テーブル作成時に部分インデックスを定義するには、partial_partition_option 句を CREATE TABLE 文内に追加します。

構文

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
    [(create_definition,...)]
    [table_options]
    partition_options
     ...

create_definitionは、次のとおりです。

{
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] [partial_partition_option]
      ...
}

partial_partition_option は、次のとおりです。

([PARTITION partition_name0[(SUBPARTITION subpartition_name0[, ...])]][,...]
 )

その他のすべてのパラメーターについては、「CREATE TABLE」をご参照ください。

パラメーター

パラメーター	説明
`table_name`	テーブル名。
`col_name`	列名。
`partition_name0`	パーティション名。複数のパーティション名はカンマ (`,`) で区切ります。
`subpartition_name0`	サブパーティション名。複数のサブパーティション名はカンマ (`,`) で区切ります。

例

当月のパーティションのみを対象にインデックスを作成する

以下の例では、月単位でパーティション化された orders テーブルを作成し、RANGE(month(date)) を使用して 2 つの部分インデックスを定義します：

o_ind_dp(dept_no, part_no) — 当月の書き込みおよびターゲットクエリが集中する orders_202212 パーティションのみを対象とします。
o_ind_amout(amount, order_id) — 過去データに基づく金額レポートをサポートするため、11 個の履歴パーティション（orders_202201 ～ orders_202211）を対象とします。

CREATE TABLE orders
(
  order_id    INT,
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  Primary Key(order_id),
  KEY o_ind_dp(dept_no, part_no) (partition orders_202212),
  KEY o_ind_amout(amount, order_id)
   (partition orders_202201,
    partition orders_202202,
    partition orders_202203,
    partition orders_202204,
    partition orders_202205,
    partition orders_202206,
    partition orders_202207,
    partition orders_202208,
    partition orders_202209,
    partition orders_202210,
    partition orders_202211
   )
)
PARTITION BY RANGE(month(date))
(
  PARTITION orders_202201 VALUES LESS THAN(2),
  PARTITION orders_202202 VALUES LESS THAN(3),
  PARTITION orders_202203 VALUES LESS THAN(4),
  PARTITION orders_202204 VALUES LESS THAN(5),
  PARTITION orders_202205 VALUES LESS THAN(6),
  PARTITION orders_202206 VALUES LESS THAN(7),
  PARTITION orders_202207 VALUES LESS THAN(8),
  PARTITION orders_202208 VALUES LESS THAN(9),
  PARTITION orders_202209 VALUES LESS THAN(10),
  PARTITION orders_202210 VALUES LESS THAN(11),
  PARTITION orders_202211 VALUES LESS THAN(12),
  PARTITION orders_202212 VALUES LESS THAN(13)
);

マルチテナントテーブルで選択したサブパーティションにインデックスを作成する

以下の例では、LIST-RANGE サブパーティション分割された tenants テーブルを作成し、2 つの部分インデックスを定義しています。

ind_id(id) — p0 のすべてのサブパーティション、および p1 のサブパーティション p1_1、p1_2、p1_3 を対象とします。
ind_date(date) — p0 のサブパーティション p0_1、p0_2、および p1 のサブパーティション p1_1、p1_2 を対象とします。

CREATE TABLE tenants (
  id INT,
  date DATE,
  count INT,
  KEY ind_id (id)  (
    partition p0,
    partition p1 (subpartition p1_1, subpartition p1_2, subpartition p1_3)
  ),
  KEY ind_date (date) (
    partition p0 (subpartition p0_1,  subpartition p0_2),
    partition p1 (subpartition p1_1, subpartition p1_2)
  )
) PARTITION BY LIST COLUMNS(id)
  SUBPARTITION BY RANGE (month(date))(
  PARTITION p0 VALUES IN (1, 2, 3, 4, 5) (
    SUBPARTITION p0_1 VALUES LESS THAN(4),
    SUBPARTITION p0_2 VALUES LESS THAN(7),
    SUBPARTITION p0_3 VALUES LESS THAN(10),
    SUBPARTITION p0_4 VALUES LESS THAN(13)
  ),
  PARTITION p1 VALUES IN (11, 12, 13, 14, 15) (
    SUBPARTITION p1_1 VALUES LESS THAN(4),
    SUBPARTITION p1_2 VALUES LESS THAN(7),
    SUBPARTITION p1_3 VALUES LESS THAN(10),
    SUBPARTITION p1_4 VALUES LESS THAN(13)
  )
);

既存のパーティションテーブルに部分インデックスを作成する

すでに存在するテーブルに部分インデックスを追加するには、CREATE INDEX 文または ALTER TABLE ADD KEY 文を使用します。

構文

CREATE [UNIQUE] INDEX index_name
    [index_type]
    ON table_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option | partial_partition_option] ...

partial_partition_option は：

([PARTITION partition_name0[(SUBPARTITION subpartition_name0[, ...])]][,...]
  )

その他のすべてのパラメーターについては、「CREATE INDEX」をご参照ください。

パラメーター

パラメーター	説明
`index_name`	インデックス名。
`table_name`	テーブル名。
`partition_name0`	パーティション名。複数のパーティション名はカンマ (`,`) で区切ります。
`subpartition_name0`	サブパーティション名。複数のサブパーティション名はカンマ (`,`) で区切ります。

例

単一パーティションに部分インデックスを追加する

以下の例では、既存の orders テーブルの orders_202201 パーティションに部分インデックス o_part_id を追加します。

CREATE INDEX o_part_id ON orders(part_no, order_id) (partition orders_202201);

また、ALTER TABLE ADD KEY を使用することもできます。

ALTER TABLE orders ADD KEY o_part_id(part_no, order_id) (partition orders_202201);

複数のパーティションおよびサブパーティションに部分インデックスを追加する

以下の例では、既存の tenants テーブルの p0 のすべてのサブパーティションおよび p1 のサブパーティション p1_1 に部分インデックス ind_count を追加します。

CREATE INDEX ind_count ON tenants(count) (partition p0, partition p1 (subpartition p1_1));

また、ALTER TABLE ADD KEY を使用することもできます。

ALTER TABLE tenants ADD KEY ind_count(count) (partition p0, partition p1 (subpartition p1_1));