インデックスの作成

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

インデックスの概要

インデックスは、セカンダリインデックスとも呼ばれ、オプションのテーブル構造です。OceanBaseデータベースは集約インデックステーブルモデルを採用しており、ユーザーが指定した主キーに対しては、システムが自動的に主キーインデックスを生成します。一方、ユーザーが作成するその他のインデックスは、セカンダリインデックスとなります。業務ニーズに応じて、特定のフィールドにインデックスを作成することで、それらのフィールドに関するクエリの実行速度を向上させることができます。

OceanBaseデータベースのインデックスに関する詳細情報については、インデックスの概要に関するドキュメントをご参照ください。

前提条件

インデックスを作成する前に、以下の事項を確認してください：

• OceanBaseクラスタをデプロイし、MySQLモードのテナントを作成していること。詳細な操作手順については、クラスタインスタンスの作成およびテナントの作成をご参照ください。

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

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

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

• INDEX 権限を持っていること。現在のユーザー権限を確認する操作については、テナントアカウント管理をご参照ください。

インデックス作成の制限

• OceanBaseデータベースでは、インデックス名はテーブル内で一意である必要があります。

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

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

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

  • 主キー以外の列の組み合わせでグローバルな一意性が求められる場合は、グローバル唯一インデックスを使用して実現する必要があります。

  • ローカル唯一インデックスを使用する場合、インデックスにはテーブルのパーティション関数に含まれるすべての列を含める必要があります。

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

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

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

  • 空間インデックスを作成する列は、SRID 属性を定義している必要があります。そうでない場合、その列に追加された空間インデックスは、後続のクエリで有効になりません。SRID に関する詳細については、空間参照システム(SRS)に関するドキュメントをご参照ください。

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

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

  • OceanBaseデータベースは、ALTER TABLE を使用して列の SRID 属性を変更することを現在サポートしていないため、空間インデックスを有効にするには、テーブル作成時に空間列の SRID 属性を定義する必要があります。

インデックス作成に関する推奨事項

• インデックスが対象とする列と用途を簡潔に表す名前の使用を推奨します。例：idx_customer_name。その他の命名規則については、オブジェクト名付け規則の概要に関するドキュメントをご参照ください。

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

• インデックス作成用SQLステートメントの並列実行数は、テナントのUnit仕様に定められたCPUコア数の上限を超えないようにすることを推奨します。例えば、テナントの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：関数インデックスを作成する

テーブルの1列または複数列の値に基づいて計算された結果に基づいて作成されるインデックスを関数インデックスと呼びます。関数インデックスは最適化技術の一種であり、クエリ時に一致する関数値を迅速に特定し、重複計算を回避してクエリ効率を向上させることができます。

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

以下の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
   

次のステップ

インデックスを作成した後、クエリのパフォーマンス最適化が必要になる場合があります。SQLチューニングの詳細については、SQLチューニングの概要に関するドキュメントをご参照ください。

関連ドキュメント

• インデックスの表示に関する詳細は、インデックスの表示に関するドキュメントをご参照ください。
• インデックスの管理に関する詳細は、DROP INDEXおよびインデックスの削除に関するドキュメントをご参照ください。
• 関数インデックスがサポートするシステム関数の詳細については、関数インデックスがサポートするシステム関数のリストに関するドキュメントをご参照ください。