インデックスの作成

本記事では、SQLステートメントを使用してインデックスを作成する方法について説明します。また、インデックスを作成するための前提条件、インデックスの概要、制限事項、推奨事項などを紹介し、いくつかの例を示します。

インデックスの概要

インデックスはセカンダリインデックスとも呼ばれ、任意に追加可能なテーブル構造です。OceanBaseデータベースは、クラスタ化インデックスモデルを採用しています。ユーザー指定の主キーには主キーインデックスが自動生成されます。それ以外にユーザーが作成するインデックスがセカンダリインデックスです。業務ニーズにもとづき、特定のフィールドにインデックスを作成することで、そのフィールドの検索速度を上げることができます。

OceanBaseデータベースのインデックスに関する詳細については、インデックスの概要を参照してください。

前提条件

インデックスを作成する前に、以下の点を確認する必要があります：

• OceanBaseクラスタをデプロイし、MySQLモードのテナントを作成していること。OceanBaseクラスタのデプロイに関する詳細については、デプロイの概要を参照してください。

• OceanBaseデータベースのMySQLテナントに接続していること。データベースへの接続に関する詳細については、接続方法の概要を参照してください。

• データベースを作成していること。データベースの作成に関する詳細については、データベースの作成を参照してください。

• テーブルを作成していること。テーブルの作成に関する詳細については、テーブルの作成を参照してください。

• 既に INDEX 権限を保有していること。現在のユーザー権限を確認する操作の詳細については、ユーザー権限の確認を参照してください。該当する権限を持っていない場合は、管理者に連絡し権限の付与を依頼してください。ユーザー権限に関する操作については、直接権限付与を参照してください。

インデックス作成の制限

• OceanBaseデータベースでは、インデックス名はデータベース（DataBase）の範囲内で一意である必要があります。

• インデックス名、64バイトを超えてはなりません。

• 一意インデックスの使用制限：

  • 1つのテーブルに複数の一意インデックスを作成できますが、各一意インデックスに対応する列の値がすべて一意である必要があります。

  • 主キー以外に、他の列の組み合わせにも表全体で一意性を持たせたい場合は、グローバル一意インデックスを使用する必要があります。

  • ローカル一意インデックスを使用する場合、そのインデックスには、テーブルのパーティションキーを構成する全ての列を含めなければなりません。

• グローバルインデックスを使用する際、グローバルインデックスのパーティションルールは、テーブルのパーティションルールと必ずしも完全に一致する必要はありません。

• 空間インデックスの使用制限：

  • 空間インデックスは、グローバルインデックスではなく、ローカルインデックスのみをサポートしています。

  • 空間インデックスを作成する列は、SRID プロパティで定義される必要があります。定義されていない場合、その列に追加された空間インデックスは後続のクエリで有効になりません。SRID に関する説明については、空間参照システム（SRS）を参照してください。

  • 空間データ型のデータ列に対してのみ、空間インデックスを作成できます。OceanBaseデータベースでサポートされている空間データ型については、空間データ型の概要を参照してください。

  • 空間インデックスを作成する列のプロパティは NOT NULL である必要があります。NOT NULL でない場合、ALTER TABLE ステートメントを使用して、まず該当列のプロパティを NOT NULL に変更し、その後、空間インデックスを追加することもできます。列プロパティの変更操作の詳細については、列の制約タイプの定義を参照してください。

  • OceanBaseデータベースでは、現時点で ALTER TABLE を使用して列の SRID プロパティを変更することはできません。そのため、空間インデックスを有効にするには、テーブル作成時に空間列の SRID プロパティを定義する必要があります。

• 全文インデックスの使用制限と注意事項

  非パーティションテーブルとパーティションテーブルには主キーがあってもなくても全文インデックスを作成できますが、全文インデックスの作成には以下の制限があります：

  • 全文インデックスは CHAR、VARCHAR および TEXT タイプの列にのみ適用されます。
  • 現在のバージョンは、ローカル（LOCAL）全文インデックスの作成のみをサポートしています。
  • 全文インデックスを作成する際に UNIQUE キーワードを指定することはできません。
  • 複数の列を含む全文インデックスを作成する場合は、これらの列が同じ文字セットを持っていることを確認する必要があります。
  • V4.3.5 BP3バージョンでは、カラムストアの全文インデックスの作成はサポートされていません。

インデックス作成の推奨事項

• インデックスが対象とする列や用途を簡潔に表す名前を使用することを推奨します。例えば idx_customer_name などです。詳細な命名に関する情報は、オブジェクト命名ルールの概要を参照してください。

• グローバルインデックスのパーティションルールと主テーブルのパーティションルールが同じで、パーティション数が同じ場合は、ローカルインデックスを作成することを推奨します。

• インデックスを作成するSQL文をパラレル実行する場合、その数はテナントのUnit仕様におけるCPUコアの上限を超えないようにしてください。テナントUnit仕様のコア数の上限を超えないようにすることを推奨します。例えば、テナントのUnit仕様が4コア（4C）の場合、同時に作成するインデックスの数は4つ以下にすることを推奨します。

• 頻繁に更新されるテーブルへの過度なインデックス作成は避け、頻繁にクエリ対象となる列にインデックスを作成すべきです。

• データ量の少ないテーブルにはインデックスを使用しないことを推奨します。データが少ない場合、全データをスキャンする方がインデックスを経由するよりも高速な場合があり、最適化の効果が得られない可能性があるためです。

• 検索性能よりも更新性能がはるかに重要である場合は、インデックスの作成を推奨しません。

• 効率的なインデックスを作成する：

  • インデックスには、クエリで使用されるすべての列を含める必要があります。これにより、テーブル本体へのアクセスを可能な限り減らすことができます。

  • 等価条件で使用する列をインデックスの先頭に配置します。

  • フィルタリングやソートの際、より多くのデータを絞り込める選択性の高い列をインデックスの前方に配置します。

コマンドラインを使用するインデックスの作成

CREATE INDEX ステートメントを使用してインデックスを作成してください。

例

例1：一意インデックスを作成する

インデックス列に重複する値が存在しないようにしたい場合は、一意インデックスを作成することができます。

以下のSQLステートメントを使用して、tbl1 という名前のテーブルを作成し、テーブル tbl1 に col2 列に基づく一意インデックスを作成します。

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

   obclient [test]> CREATE TABLE tbl1(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));
   

2. テーブル tbl1 に col2 列に基づいて、idx_tbl1_col2 という名前の一意インデックスを作成します。

   obclient [test]> CREATE UNIQUE INDEX idx_tbl1_col2 ON tbl1(col2);
   

3. テーブル tbl1 のインデックス情報を確認します。

   obclient [test]> SHOW INDEX FROM tbl1;
   

   実行結果は次のとおりです：

   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl1  |          0 | PRIMARY       |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl1  |          0 | idx_tbl1_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set
   

例2：非一意インデックスを作成する

以下のSQLステートメントを使用して、tbl2 という名前のテーブルを作成し、テーブル tbl2 に col2 列に基づくインデックスを作成します。

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

   obclient [test]> CREATE TABLE tbl2(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));
   

2. テーブル tbl2 に col2 列に基づいて、idx_tbl2_col2 という名前のインデックスを作成します。

   obclient [test]> CREATE INDEX idx_tbl2_col2 ON tbl2(col2);
   

3. テーブル tbl2 のインデックス情報を確認します。

   obclient [test]> SHOW INDEX FROM tbl2;
   

   実行結果は次のとおりです：

   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl2  |          0 | PRIMARY       |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl2  |          1 | idx_tbl2_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set
   

例3：ローカルインデックスを作成する

ローカルインデックスはパーティションインデックスとも呼ばれ、LOCAL キーワードを使用して作成します。ローカルインデックスのパーティションキーとパーティション数は、ベーステーブルのものと同一であり、テーブルと全く同じパーティショニングメカニズムを持ちます。通常のローカルインデックスと、ローカル一意インデックスの作成がサポートされています。データの唯一性を制約するためにローカル一意インデックスを使用する場合、そのインデックスにはテーブルのパーティションキーをすべて含める必要があります。

以下のSQLステートメントを使用して、tbl3_rl という名前のコンポジット・パーティションテーブルを作成し、テーブル tbl3_rl に col1 と col2 列に基づくローカル一意インデックスを作成します。

1. Range + Listコンポジット・パーティションテーブル tbl3_rl を作成します。

   obclient [test]> CREATE TABLE tbl3_rl(col1 INT,col2 INT) 
     PARTITION BY RANGE(col1)
     SUBPARTITION BY LIST(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES IN(1,3),
         SUBPARTITION sp1 VALUES IN(4,6),
         SUBPARTITION sp2 VALUES IN(7,9)),
       PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES IN(1,3),
         SUBPARTITION sp4 VALUES IN(4,6),
         SUBPARTITION sp5 VALUES IN(7,9))
       ); 
   

2. テーブル tbl3_rl に col1 と col2 列に基づいて、idx_tbl3_rl_col1_col2 という名前のインデックスを作成します。

   obclient [test]> CREATE UNIQUE INDEX idx_tbl3_rl_col1_col2 ON tbl3_rl(col1,col2) LOCAL;
   

3. テーブル tbl3_rl のインデックス情報を確認します。

   obclient [test]> SHOW INDEX FROM tbl3_rl;
   

   実行結果は次のとおりです：

   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table   | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl3_rl |          0 | idx_tbl3_rl_col1_col2 |            1 | col1        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   | tbl3_rl |          0 | idx_tbl3_rl_col1_col2 |            2 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set
   

例4：グローバルインデックスを作成する

グローバルインデックスを作成するためのキーワードは GLOBAL です。

以下のSQLステートメントを使用して、tbl4_h という名前のパーティションテーブルを作成し、テーブル tbl4_h に col2 列に基づくグローバルインデックスを作成します。

1. Hashパーティションによるパーティションテーブル tbl4_h を作成します。

   obclient [test]> CREATE TABLE tbl4_h(col1 INT PRIMARY KEY,col2 INT)
     PARTITION BY HASH(col1) PARTITIONS 5;
   

2. テーブル tbl4_h に、col2 列に基づくRangeパーティションインデックスである idx_tbl4_h_col2 という名前のグローバルインデックスを作成します。

   obclient [test]> CREATE INDEX idx_tbl4_h_col2 ON tbl4_h(col2) GLOBAL
     PARTITION BY RANGE(col2)
     (PARTITION p0 VALUES LESS THAN(100), 
       PARTITION p1 VALUES LESS THAN(200), 
       PARTITION p2 VALUES LESS THAN(300)
     );
   

3. テーブル tbl4_h のインデックス情報を確認します。

   obclient [test]> SHOW INDEX FROM tbl4_h;
   

   実行結果は次のとおりです：

   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table  | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl4_h |          0 | PRIMARY         |            1 | col1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
   | tbl4_h |          1 | idx_tbl4_h_col2 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   2 rows in set
   

例5：空間インデックスを作成する

空間インデックスは、空間データの処理と最適化を行うためのデータベースインデックスです。これは地理情報システム（GIS）や位置データのストレージおよびクエリに広く利用されています。OceanBaseデータベースでは、通常のインデックスを作成する際の構文を使用して空間インデックスを作成できますが、空間インデックスには SPATIAL キーワードを使用する必要があります。

以下のSQLステートメントを使用して、tbl5 という名前のテーブルを作成し、テーブル tbl5 に g 列に基づく空間インデックスを作成します。

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

   obclient [test]> CREATE TABLE tbl5(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 0);
   

2. テーブル tbl5 に g 列に基づいて、idx_tbl5_g という名前の空間インデックスを作成します。

   obclient [test]> CREATE INDEX idx_tbl5_g ON tbl5(g);
   

3. テーブル tbl5 のインデックス情報を確認します。

   obclient [test]> SHOW INDEX FROM tbl5;
   

   実行結果は次のとおりです：

   +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl5  |          1 | idx_tbl5_g |            1 | g           | A         |        NULL | NULL     | NULL   |      | SPATIAL    | available |               | YES     | NULL       |
   +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   1 row in set
   

例6：関数ベースのインデックスを作成する

テーブル内の一列または複数列の値に対する計算結果に基づいて作成されるインデックスを、関数ベースのインデックスと呼びます。関数ベースのインデックスは最適化技術の一つであり、これを使用することで、クエリ実行時に一致する関数の値を高速に特定でき、計算の繰り返しを避けることでクエリ効率を向上させます。

OceanBaseデータベースのMySQLモードでは、関数ベースのインデックスで使用できる式に制限があり、一部のシステム関数の式を関数ベースのインデックスとして使用することはできません。具体的な関数リストについては、関数ベースのインデックスでサポートされているシステム関数リスト と 関数ベースのインデックスでサポートされていないシステム関数リストを参照してください。

セッション変数の固定化メカニズム

関数ベースのインデックスのセッション変数の固定化メカニズムにより、インデックス作成時と使用時の計算セマンティクスが完全に一致し、セッション環境の変化による「インデックスはヒットするが結果が正しくない」状況を回避します。

仕組み

関数ベースのインデックスを作成する際、システムはインデックス式を自動分析し、その式に依存するセッション変数（例：time_zone、sql_mode、collation_connection など）を特定します。その後、これらの変数の現在値をインデックスメタデータに保存します。

クエリが関数ベースのインデックスにヒットした場合：

1. システムはインデックス式の local_session_var_id から、インデックス作成時に保存されたセッション変数のスナップショットを取得します。
2. 式の型推論や計算を行う際、現在のセッション変数の値ではなく、保存された変数の値を使用します。
3. 計算結果がインデックス作成時と完全に一致することを保証します。

サポートされるセッション変数

関数ベースのインデックスで固定化がサポートされるセッション変数は以下の通りです：

システムは、インデックス式に基づいて固定化が必要な変数を動的に推論します。例えば、インデックス式に NOW() などの時間関数が含まれている場合、time_zone を固定化します。文字列関数が含まれている場合は、collation_connection が固定化される可能性があります。

使用上の注意事項

変動しやすい式への依存を避ける

システムは自動的にセッション変数を固定化しますが、インデックス定義ではセッションの影響を受ける関数の使用を極力控え、決定的な列を使用することを推奨します。

-- ❌ 不推奨（time_zoneに依存している）
CREATE INDEX idx_now ON logs ((DATE(NOW())));

-- ✅ 推奨（決定的な列を使用している）
CREATE INDEX idx_log_date ON logs ((DATE(log_time)));

セッション変数の手動管理は不要

システムがセッション変数の一貫性を自動的に処理するため、現在のセッション変数の変更が、作成済みの関数ベースのインデックスの正確性に影響することを懸念する必要はありません。クエリ実行時には、インデックス作成時に保存された変数のスナップショットが自動的に使用されます。

以下のSQLステートメントを使用して、tbl6 という名前のテーブルを作成し、テーブル tbl6 に c_time 列に基づく関数ベースのインデックスを作成します。

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

   obclient [test]> CREATE TABLE tbl6(id INT, name VARCHAR(18), c_time DATE);
   

2. テーブル tbl6 に、c_time 列の年の部分に基づくインデックス idx_tbl6_c_time を作成します。

   obclient [test]> CREATE INDEX idx_tbl6_c_time ON tbl6((YEAR(c_time)));
   

3. 以下のSQLステートメントを使用して、作成された関数ベースのインデックスを確認できます。

   SHOW INDEX FROM tbl6;
   

   実行結果は次のとおりです：

   obclient [test]> SHOW INDEX FROM tbl6;
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   | Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression     |
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   | tbl6  |          1 | idx_tbl6_c_time |            1 | SYS_NC19$   | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | year(`c_time`) |
   +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+----------------+
   1 row in set
   

例7：カラムストア・インデックスを作成する

カラムストア・インデックスは、カラムストアデータベースに対するインデックス構造であり、主にカラムストアのクエリのパフォーマンスを最適化するために使用されます。

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

tbl7 テーブルを作成した後、カラムストア・インデックス idx1_tbl7_cg を作成します。

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

   CREATE TABLE tbl7(id INT, name VARCHAR(20), age INT);
   

2. テーブル tbl7 にカラムストア・インデックス idx1_tbl7_cg を作成し、インデックステーブルに age 列の冗長データを保存します。

   CREATE INDEX idx1_tbl7_cg ON tbl7(id) STORING(age) WITH COLUMN GROUP(each column);
   

3. 以下のSQLステートメントを使用して、作成された関数ベースのインデックスを確認できます。

   SHOW INDEX FROM tbl7;
   

   実行結果は次のとおりです：

   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl7  |          1 | idx1_tbl7_cg |            1 | id          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   

例9：全文インデックスを作成する

例：

tbl8 テーブルを作成した後、全文インデックス ft_idx1_tbl8 を作成します。

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

   CREATE TABLE tbl8(col1 INT, col2 VARCHAR(4096));
   

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

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

3. 以下のSQLステートメントを使用して、作成された関数ベースのインデックスを確認できます。

   SHOW INDEX FROM tbl8;
   

   実行結果は次のとおりです：

   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   | tbl8  |          1 | ft_idx1_tbl8 |            1 | col2        | A         |        NULL | NULL     | NULL   | YES  | FULLTEXT   | available |               | YES     | NULL       |
   +-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
   

次の操作

インデックスを作成した後、クエリのパフォーマンスを最適化する必要があるかもしれません。SQLチューニングに関する詳細については、SQLチューニングの概要を参照してください。

関連ドキュメント

• インデックスの確認に関する詳細については、インデックスの確認を参照してください。
• インデックスの管理に関する詳細については、DROP INDEX および インデックスの削除を参照してください。
• 関数ベースのインデックスがサポートするシステム関数の詳細については、関数ベースのインデックスでサポートされているシステム関数リストを参照してください。