CREATE INDEX

説明

このステートメントは、インデックスを作成するために使用されます。インデックスはテーブル上に作成され、データベーステーブルの1列または複数列の値をソートする構造です。その主な役割はクエリの速度を向上させ、データベースシステムのパフォーマンスオーバヘッドを低減することです。

OceanBaseデータベースの現在のバージョンでサポートされているインデックスタイプには、一意インデックスと非一意インデックスが含まれます。インデックスを作成する際には、STORING(column_name,...) を使用してインデックステーブル上の冗長列を指定できます。インデックステーブルの冗長列には、ユーザーが追加を指定した列に加えて、主キーテーブルの主キー列も自動的に追加されます（主キーテーブルがない場合はHidden PKが含まれます）。インデックスに追加の列を格納することで、データベースオプティマイザーにより多くの選択肢を提供できます。

使用上の制限と注意事項

• テーブル内のすべてのインデックス列を削除すると、作成したインデックスは無効になります。
• フルテキストインデックスは、CHAR、VARCHAR、および TEXT 型の列にのみ適用できます。
• 現在のバージョンでは、ローカル（LOCAL）フルテキストインデックスのみの作成がサポートされています。
• フルテキストインデックスを作成する際には、UNIQUE キーワードを指定できません。
• 複数の列にまたがるフルテキストインデックスを作成する場合は、これらの列が同じ文字セットを持っていることを確認する必要があります。
• 分かち書き辞書を指定するには、WITH PARSER {SPACE | NGRAM | BENG | IK} を使用して、フルテキストインデックス作成時にのみ指定できます。

権限要件

CREATE INDEX ステートメントを実行してインデックスを作成するには、現在のユーザーが対象オブジェクトに対する少なくともINDEX権限を持っている必要があります。OceanBaseデータベースの権限の詳細については、MySQLモードの権限分類を参照してください。

構文

CREATE [hint_options] [index_type] INDEX [IF NOT EXISTS] index_name
    [USING BTREE] ON table_name (sort_column_key [, sort_column_key... ])
    [WITH PARSER tokenizer_option]
    [PARSER_PROPERTIES[=](parser_properties_list)]
    [index_option...] [partition_option] [index_column_group_option]

index_type:
    SPATIAL
    | UNIQUE
    | FULLTEXT

sort_column_key:
      column_name [(integer)] [ASC] [ID id]
    | (index_expr) [ASC] [ID id]

tokenizer_option:
    SPACE
    | NGRAM
    | BENG
    | IK
    | NGRAM2

parser_properties_list:
    parser_properties, [parser_properties]

parser_properties:
    min_token_size = int_value
    | max_token_size = int_value
    | ngram_token_size = int_value
    | ik_mode = 'char_value'
    | min_ngram_size = int_value
    | max_ngram_size = int_value

index_option:
      GLOBAL
    | LOCAL
    | BLOCK_SIZE integer
    | COMMENT STRING_VALUE
    | STORING (column_name [, column_name...])
    | WITH_ROWID
    | WITH PARSER STRING_VALUE
    | index_using_algorithm
    | visibility_option
    | DATA_TABLE_ID data_table_id
    | INDEX_TABLE_ID index_table_id
    | VIRTUAL_COLUMN_ID virtual_column_id
    | MAX_USED_PART_ID used_part_id
    | STORAGE_CACHE_POLICY(storage_cache_policy_option)

index_column_group_option：
    WITH COLUMN GROUP([all columns, ]each column)

storage_cache_policy_option:
    GLOBAL = {"hot"|"auto"|"none"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    [, BOUNDARY_COLUMN_UNIT = {"s"|"ms"}]
    [, HOT_RETENTION = intnum retention_time_unit]

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

パラメータ説明

index_type

• SPATIAL：このキーワードは、空間インデックスを作成することを指定します。
• UNIQUE：このキーワードは、一意のインデックスを作成することを指定します。
• FULLTEXT：このキーワードは、全文インデックスを作成することを指定します。

さまざまな種類のインデックスの作成方法については、インデックスの作成を参照してください。

sort_column_key

• column_name [(integer)] [ASC] [ID id]：ソートキーとして列名を指定します。

  • column_name：ソートする列名です。

  • integer：オプションです。ソートキーの長さの上限を指定します。

  • ASC：オプションです。昇順でソートします。現在、降順（DESC）の並べ替えはサポートされていません。

  • ID id：オプションです。ソートキーのID番号を指定します。

  例えば、t3 という名前のテーブルに index3 という名前のインデックスを作成し、そのインデックスが c1 列に基づいて昇順でソートされるように指定します。

  CREATE INDEX index3 ON t3 (c1 ASC);
  

• (index_expr) [ASC] [ID id]：ソートキーとしてインデックス式を指定します。インデックス式は式や関数を使用して定義できます。以下のオプションを含むことができます：

  • (index_expr)：インデックス式を表します。ブール式（例：c1=c1）も可能です。OceanBaseデータベースの現在のバージョンでは、生成列に対する関数インデックスの作成は禁止されています。関数インデックスでサポートされている式については、関数インデックスでサポートされているシステム関数のリストを参照してください。

  • ASC：オプションです。昇順でソートします。現在、降順（DESC）の並べ替えはサポートされていません。

  • ID id：オプションです。ソートキーのID番号を指定します。

  例えば、t4 という名前のテーブルに index4 という名前のインデックスを作成し、c1+c2 をインデックス式として指定して、昇順でソートします。

  CREATE INDEX index4 ON t4 ((c1 + c2) ASC);
  

インデックス作成時に複数のソート列を指定できます。複数のソート列は半角カンマ（,）で区切ります。例えば、t5 という名前のテーブルに index5 という名前のインデックスを作成し、c1 列と c2+c3 式をインデックスのソートキーとして指定します。

CREATE INDEX index5 ON t5 (c1, (c2+c3));

tokenizer_option

• SPACE：デフォルト値。スペースによる分かち書きを表します。以下のプロパティを指定できます：

  プロパティ	値の範囲
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• NGRAM：N-Gram（中国語）に基づく分かち書き方式を表します。以下のプロパティを指定できます：

  プロパティ	値の範囲
  ngram_token_size	[1, 10]

• NGRAM2：テキストを min_ngram_size ～ max_ngram_size の範囲の連続する文字に分割します。以下のプロパティを指定できます：

  プロパティ	値の範囲
  min_ngram_size	[1, 16]
  max_ngram_size	[1, 16]

• BENG：Beng（基本英語）に基づく分かち書き方式を表します。以下のプロパティを指定できます：

  プロパティ	値の範囲
  min_token_size	[1, 16]
  max_token_size	[10, 84]

• IK：IK（中国語）に基づく分かち書き方式を表します。現在は utf-8 文字セットのみをサポートしています。以下のプロパティを指定できます：

  プロパティ	値の範囲
  ik_mode	smart max_word

TOKENIZE 関数を使用して、指定されたトークナイザーとJSON形式のパラメータに従ってテキストを分かち書いた結果を確認できます。

parser_properties

• min_token_size：最小分かち書き長を表します。デフォルト値は3で、値の範囲は1から16です。

• max_token_size：最大分かち書き長を表します。デフォルト値は84で、値の範囲は10から84です。

• ngram_token_size：NGRAM の分かち書き長を表します。NGRAM トークナイザーでのみ有効です。デフォルト値は2で、値の範囲は1から10です。

• ik_mode: IK トークナイザーの分かち書きモードを表します。取り得る値は以下のとおりです：

  • smart：デフォルト値。辞書内の単語が分かち書きの精度向上に使用され、辞書内の単語境界が優先的に考慮されるため、不必要な拡張が削減される可能性があります。
  • max_word：辞書で定義された単語は認識されますが、分かち書きの最大拡張には影響しません。辞書に定義があっても、max_word モードはテキストをより多くの単語に分割しようと試みます。

例：

1. テーブル tbl1 を作成します。

   CREATE TABLE tbl1 (col1 VARCHAR(200), col2 TEXT);
   

2. テーブル tbl1 の列 col2 に対して全文インデックスを作成し、IK トークナイザーの使用を指定します。同時に PARSER_PROPERTIES を使用してトークナイザーのプロパティを設定します。

   CREATE FULLTEXT INDEX fidx_tbl1 ON tbl1(col2) WITH PARSER IK PARSER_PROPERTIES=(ik_mode='max_word');
   

index_option

• GLOBAL：グローバルインデックスを作成します。

• LOCAL：ローカルインデックスを作成します。

• BLOCK_SIZE integer：インデックスブロックのサイズ、つまり各インデックスブロック内のバイト数を指定します。

• COMMENT STRING_VALUE：インデックスにコメントを追加します。

• STORING (column_name [, column_name...])：インデックスに格納する列を指定します。複数の列は半角カンマ（,）で区切ります。

• WITH_ROWID：行識別子を含むインデックスを作成します。

• WITH PARSER STRING_VALUE：インデックスに必要なパーサーを指定します。

• index_using_algorithm：インデックスで使用するアルゴリズムを指定します。USING BTREEまたはUSING HASHを指定できます。ただし、USING HASHでは複数値インデックスを作成できません。

• visibility_option：インデックスの可視性を指定します。

• DATA_TABLE_ID data_table_id：インデックスが属するデータテーブルのIDを指定します。

• INDEX_TABLE_ID index_table_id：インデックステーブルのIDを指定します。

• VIRTUAL_COLUMN_ID virtual_column_id：仮想列のIDを指定します。

• MAX_USED_PART_ID used_part_id：インデックスの最大使用パーティションIDを指定します。

index_column_group_option

• WITH COLUMN GROUP(all columns, each column)：行ストアとカラムストアの冗長なインデックスを作成することを指定します。
• WITH COLUMN GROUP(each column)：カラムストアインデックスを作成することを指定します。

storage_cache_policy

インデックステーブルに対するホットキャッシュポリシーの設定をサポートします：

• ローカルインデックスの場合、テーブルレベルの STORAGE_CACHE_POLICY を指定できます。テーブルレベルの STORAGE_CACHE_POLICY が指定されていない場合、デフォルトは none であり、データテーブルの STORAGE_CACHE_POLICY 設定を使用します。ローカルインデックスパーティションのホットキャッシュポリシーは、データテーブルのパーティションルールに従います。

• グローバルインデックスの場合、テーブルレベルの STORAGE_CACHE_POLICY を指定するほか、インデックステーブルにパーティションがある場合は、インデックステーブルのパーティションレベルの STORAGE_CACHE_POLICY も指定できます。グローバルインデックスでテーブルレベルの STORAGE_CACHE_POLICY が指定されていない場合、デフォルトは none です。

storage_cache_policy_option の各プロパティは、テーブル tbl1 の列 c1 にローカルインデックス idx1_tbl1 をKey-Value形式で作成する例として示されています。各プロパティの意味は以下のとおりです：

• GLOBAL = {"hot" | "auto" | "none"}：インデックスのデータがホットデータかどうかを指定します。値は以下のとおりです：
  • hot：指定されたインデックスをホットストレージとして設定します。キャッシュディスク容量に余裕がある場合、インデックステーブルのすべてのデータがローカルクラウドディスクにキャッシュされます。
  • auto：インデックステーブルのホットデータをシステムが自動的に認識することを指定します。
  • none：デフォルト値で、このインデックスのポリシーがメインテーブルの STORAGE_CACHE_POLICY の値に従うことを示します。
• timeline_strategy_list：タイムライン戦略パラメータリストを表し、各パラメータは半角カンマで区切られます。ホットキャッシュタイムライン戦略は、時間を用いてパーティションデータがホットデータかどうかを判断する仕組みをサポートします。システムは設定された戦略に基づき、ローカルキャッシュディスク上のパーティションデータを自動的に調整します。このパラメータはグローバルインデックスでのみ設定可能です。
  • BOUNDARY_COLUMN = column_name：ホットデータの列を判定するために使用されます。整数型（BIGINT または INT 型、Unixタイムスタンプ形式）と時刻型（TIMESTAMP、DATE、DATETIME または YEAR 型）をサポートします。BOUNDARY_COLUMN が整数型の場合、テーブルのパーティショニング方式は Range または Range Columns パーティションタイプをサポートします。BOUNDARY_COLUMN の型が時刻型の場合、パーティショニング方式は Range Columns のみを使用できます。
  • BOUNDARY_COLUMN_UNIT = {"s" | "us" | "ms"}：パラメータ BOUNDARY_COLUMN の時間単位を指定するために使用されます。このパラメータは BOUNDARY_COLUMN が整数型の場合にのみ設定できます。値は以下のとおりです：
    • パーティション列が INT 型の場合、BOUNDARY_COLUMN_UNIT の値は s のみとなります。
    • パーティション列が BIGINT 型の場合、BOUNDARY_COLUMN_UNIT の値は s、ms となります。
  • HOT_RETENTION = intnum retention_time_unit：ホットデータの時間範囲を設定するために使用されます。MINUTE / HOUR / DAY / WEEK / MONTH / YEAR で設定できます。

例

例1

以下のSQLステートメントを使用して、テーブルにカラムストアインデックスを作成します。

1. 以下のSQLステートメントを使用して、テーブル test_tbl1 を作成します。

   CREATE TABLE test_tbl1 (col1 INT, col2 VARCHAR(50));
   

2. test_tbl1 テーブルに col1 列を参照するカラムストアインデックス idx1_test_tbl1 を作成します。

   CREATE INDEX idx1_test_tbl1 ON test_tbl1 (col1) WITH COLUMN GROUP(each column);
   

例2

共有ストレージモードで、以下のSQLステートメントを使用して、インデックステーブルのホットキャッシュポリシーを作成します。

• インデックス作成時に、インデックステーブル idx1_tbl1 のデータをホットデータとして指定します。

  CREATE INDEX idx1_tbl1 ON tbl1 (c1) LOCAL
    STORAGE_CACHE_POLICY (GLOBAL = "hot");
  

• テーブル tbl2 を作成し、tbl2 テーブル上のインデックステーブル idx2 のデータをホットデータとして設定します。

  CREATE TABLE tbl2 (col1 INT, col2 INT,
    INDEX idx2 ((col1 + 1)) STORAGE_CACHE_POLICY (GLOBAL = "hot"), UNIQUE KEY ((col1 + col2)));
  

• グローバルインデックスを作成する際に、グローバルインデックスパーティションのキャッシュポリシーとタイムラインポリシーを設定します。

  CREATE INDEX idx_global ON tbl1 (c1) GLOBAL
    PARTITION BY RANGE (c1)
    (PARTITION p1 VALUES LESS THAN (100),
     PARTITION p2 VALUES LESS THAN (200),
     PARTITION p3 VALUES LESS THAN MAXVALUE);
  

関連ドキュメント

• インデックスの概要

• インデックスの作成

• インデックスの確認

• インデックスの削除