本記事では、SQLステートメントを使用してインデックスを作成する方法について説明します。また、インデックス作成の前提条件、インデックスの概要、制限事項および推奨事項などを紹介し、いくつかの例も示します。
説明
本記事では主に CREATE INDEX ステートメントを使用したインデックスの作成方法について説明します。その他のインデックス作成方法については、CREATE TABLE または ALTER TABLE ステートメントを参照してください。
インデックスの概要
インデックスは、セカンダリインデックスとも呼ばれ、オプションのテーブル構造です。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 ステートメントを使用してインデックスを作成してください。
説明
SHOW INDEX FROM table_name; ステートメントを使用して、テーブル内のインデックス情報を確認できます。ここで、table_name はテーブル名です。
例
例1：一意インデックスを作成する
インデックス列に重複する値が存在しないようにしたい場合は、一意インデックスを作成できます。
以下のSQLステートメントを使用して、tbl1 という名前のテーブルを作成し、テーブル tbl1 の col2 列に基づく一意インデックスを作成します。
テーブル
tbl1を作成します。obclient [test]> CREATE TABLE tbl1(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));テーブル
tbl1のcol2列に基づいて、idx_tbl1_col2という名前の一意インデックスを作成します。obclient [test]> CREATE UNIQUE INDEX idx_tbl1_col2 ON tbl1(col2);テーブル
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 列に基づくインデックスを作成します。
テーブル
tbl2を作成します。obclient [test]> CREATE TABLE tbl2(col1 INT, col2 INT, col3 VARCHAR(50), PRIMARY KEY (col1));テーブル
tbl2のcol2列に基づいて、idx_tbl2_col2という名前のインデックスを作成します。obclient [test]> CREATE INDEX idx_tbl2_col2 ON tbl2(col2);テーブル
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 列に基づくローカル一意インデックスを作成します。
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)) );テーブル
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;テーブル
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 列に基づくグローバルインデックスを作成します。
Hashパーティションのパーティションテーブル
tbl4_hを作成します。obclient [test]> CREATE TABLE tbl4_h(col1 INT PRIMARY KEY,col2 INT) PARTITION BY HASH(col1) PARTITIONS 5;テーブル
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) );テーブル
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 列に基づく空間インデックスを作成します。
テーブル
tbl5を作成します。obclient [test]> CREATE TABLE tbl5(id INT,name VARCHAR(20),g GEOMETRY NOT NULL SRID 0);テーブル
tbl5のg列に基づいて、idx_tbl5_gという名前の空間インデックスを作成します。obclient [test]> CREATE INDEX idx_tbl5_g ON tbl5(g);テーブル
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 列に基づく関数インデックスを作成します。
テーブル
tbl6を作成します。obclient [test]> CREATE TABLE tbl6(id INT, name VARCHAR(18), c_time DATE);テーブル
tbl6にc_time列の年部分に基づいて、idx_tbl6_c_timeという名前のインデックスを作成します。obclient [test]> CREATE INDEX idx_tbl6_c_time ON tbl6((YEAR(c_time)));以下の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チューニングの概要を参照してください。