本記事では、OceanBaseデータベースにおけるインデックス設計の推奨事項を紹介します。
インデックスの概要
OceanBaseデータベースは、主キーインデックス、一意インデックスをサポートしており、さらにセカンダリインデックスもサポートしています。これらのインデックスは、単一の列または複数の列（複合インデックス）で構成できます。OceanBaseデータベースでは、インデックスはローカルインデックスとグローバルインデックスの2種類に分類されます。両者の違いは、ローカルインデックスがパーティションデータとパーティションを共有するのに対し、グローバルインデックスは独立したパーティションを持つことです。
説明
OceanBase 4.0バージョンでは、MySQLモードのデフォルト設定ではローカルインデックス（local）が作成され、Oracleモードのデフォルト設定ではグローバルインデックス（global）が作成されます。
MySQLモードでインデックスを作成する例を以下に示します：
ローカルインデックスの作成
obclient> CREATE TABLE t1(id NUMBER PRIMARY KEY,name1 VARCHAR(10),name2 VARCHAR(10)); obclient> CREATE INDEX idx_t1_name1 ON t1(name1);説明
LOCALまたはGLOBALを指定しない場合、MySQLモードのデフォルト設定ではローカルインデックス（local）が作成されます。グローバルインデックスの作成
obclient> CREATE INDEX idx_t1_name2 ON t1(name2) GLOBAL;
インデックス設計
単一値インデックスの要件と推奨事項
インデックスの作成は、最左前接辞則を満たす必要があります。
テーブル内のすべてのインデックス付きフィールドは、NOT NULL属性を持つことを推奨します。通常、業務ニーズに応じてDEFAULT値を定義することを推奨します。
業務上、一意の特性を持つフィールドは、組み合わせフィールドであっても、主キーとして作成することを推奨します。
説明
一意インデックスがINSERT速度に影響を与える場合でも、その速度低下は無視できるものです。一意インデックスによる検索速度の向上は顕著です。また、アプリケーション層で非常に完璧な検証と制御が行われていても、一意インデックスが存在しない限り、マーフィーの法則により必ずダーティデータが発生します。
ジョインするフィールドのデータ型は一致させてください。複数テーブルを結合するクエリでは、結合されるフィールドにインデックスがあることを確認してください。
説明
複数テーブルの結合（JOIN）シナリオにおいても、テーブルインデックスの使用はSQLパフォーマンスを大幅に向上させることができます。
ページ検索では、可能な限り左端のあいまい検索や完全一致検索は避けてください。必要な場合は、検索エンジンを利用して解決してください。同時に、フィルタリング後のフィールドを入力条件として使用することで、バックグラウンドで結果セットに対する大量の検索を回避できます。
説明
インデックスファイルはB-Treeの左端一致マッチング特性を持っています。左側の値が未定の場合、このインデックスを使用することはできません。
逆例：テーブルにインデックス idx_t2_abc(a,b,c) が存在しますが、WHERE 条件が
b = ? and c = ?の場合、条件に a が含まれていないため、このインデックスを使用できません。t2 テーブルとインデックス idx_t2_abc(a,b,c) の作成ステートメントは以下のとおりです。obclient> CREATE TABLE t2(a NUMBER PRIMARY KEY, b INT, c VARCHAR(10)); obclient> CREATE INDEX idx_t2_abc ON t2(a,b,c);では、WHERE 条件が
b = ? and c = ?のステートメントの実行計画を見てみましょう。実行計画から明らかに、name 列は t2 であり、このステートメントはそのインデックスを使用せず、全表スキャンを行っており、コストは 409 です。obclient> EXPLAIN SELECT a,b,c FROM t2 WHERE b=8889 AND c='a(mbmtwm'; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|t2 |1 |409 | =================================== Outputs & filters: ------------------------------------- 0 - output([t2.a], [t2.b], [t2.c]), filter([t2.b = 8889], [t2.c = 'a(mbmtwm']), access([t2.b], [t2.c], [t2.a]), partitions(p0) | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)肯定例：上記のインデックスでフィールドの順序を idx_bca(b,c,a) または idx_cba(c,b,a) に調整し、フィルタ条件が引き続き
b = ? and c = ?の場合、この複合インデックスが使用されます。t2 テーブルとインデックス idx_t2_bca(b,c,a) の作成ステートメントは以下のとおりです。obclient> CREATE TABLE t2(a NUMBER PRIMARY KEY, b INT, c VARCHAR(10)); obclient> CREATE INDEX idx_t2_bca ON t2(b,c,a) ;では、WHERE 条件が
b = ? and c = ?のステートメントの実行計画を見てみましょう。実行計画から明らかに、name 列は t2(idx_t2_bca) であり、このステートメントは正しいインデックスを使用しており、コストは 46 で、明らかに大幅に低下しています。obclient> EXPLAIN SELECT a,b,c FROM t2 WHERE b=8889 AND c='a(mbmtwm'; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================= |ID|OPERATOR |NAME |EST. ROWS|COST| --------------------------------------------- |0 |TABLE SCAN|t2(idx_t2_bca)|1 |46 | ============================================= Outputs & filters: ------------------------------------- 0 - output([t2.a], [t2.b], [t2.c]), filter(nil), access([t2.b], [t2.c], [t2.a]), partitions(p0) | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in setORDER BY を使用するシナリオがある場合、インデックスの順序性を活用し、file_sort を回避してください。ORDER BY の最後のフィールドは組み合わせインデックスの一部であり、インデックスの組み合わせ順序の最後に配置することで、file_sort が発生し、クエリパフォーマンスに影響を与えるのを防ぎます。
肯定例：
where a=? and b=? order by c;インデックス：idx_t3_abc(a,b,c)。t3 テーブルとインデックス idx_t3_abc(a,b,c) の作成ステートメントは以下のとおりです。obclient> CREATE TABLE t3(a NUMBER PRIMARY KEY, b INT, c INT); obclient> CREATE INDEX idx_t3_abc ON t3(a,b,c);では、
where a=? and b=? order by c;ステートメントの実行計画を見てみましょう。OPERATOR は TABLE GET、COST は 46 です。obclient> EXPLAIN SELECT a,b,c FROM t3 WHERE a=117 AND b=67176 ORDER BY c; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ---------------------------------- |0 |TABLE GET|t3 |1 |46 | ================================== Outputs & filters: ------------------------------------- 0 - output([t3.a], [t3.b], [t3.c]), filter([t3.b = 67176]), access([t3.a], [t3.b], [t3.c]), partitions(p0) | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set逆例：インデックス内に範囲検索がある場合、インデックスの順序性を利用できません。例：
WHERE a>10 ORDER BY b;インデックス a_b はソートできません。インデックス内には昇順に並べられた以下のデータが含まれている可能性があるためです： {a = 11, b = 2} {a = 11, b = 3} {a = 12, b = 1} この場合、直接順序通りに出力すると、b=1 が b=2 と b=3 の後に来てしまい、順序が間違ってしまいます。カバーインデックスを利用したクエリ操作を行うことで、テーブルへの再アクセスを回避できます。
組み合わせインデックスの要件と推奨事項
インデックスに複数の列が含まれる場合は、列の順序を慎重に選択します。一般的に、インデックスの最初の列は高カードナリティ列（カードナリティ：データ列に含まれる異なる値の数）であるべきで、つまり最も識別度の高い列を左端に配置します。
例：
where a=? and b=?のようなクエリ条件があり、列aの値がほぼ一意である場合、idx_a インデックスを単独で作成するだけで済みます。ORDER BY、GROUP BY、DISTINCT 句で頻繁に使用される列をインデックスの後半に追加し、カバーインデックスを形成してテーブルへの再アクセスを回避します。
where a=? and b=?のようなクエリ条件が存在する場合、組み合わせインデックス idx_ab(a,b) を使用し、a フィールドと b フィールドにそれぞれ idx_a(a)、idx_b(b) の2つのインデックスを個別に作成しないでください。後者の方法では、2つのインデックスを同時に利用できません。複数のシナリオのクエリを同時に満たすように組み合わせインデックスを合理的に設計し、インデックスの冗長性を削減します：
複数のステートメントで同じインデックスを共有できます。そのうちの一部のステートメントでは、インデックスの先頭部分だけをカバーすればよいです。例えば、
a = ? and b = ?とb = ?の2つのステートメントがある場合、組み合わせインデックス idx_ba(b,a) は2つのステートメントで同時に使用でき、idx_ab(a,b) と idx_b(b) のインデックスを個別に作成する必要はありません。不要な場合はグローバルインデックスを使用しないでください。特に、NDV値が低く使用頻度も高くないグローバルインデックスは作成しないでください。
WHERE句にパーティションキー列が含まれる場合、パーティションキー列と条件に含まれる他の列に対して、結合ローカルインデックスを作成できます。
グローバルパーティションインデックスは、クエリで返されるデータ量が少ないシナリオに適しています。データ量が多いシナリオでは、グローバルインデックスとローカルインデックスで比較テストを行うことをお勧めします。
NDV値が高い列をグローバルパーティションインデックスのパーティションキーとして選択します。
パーティションテーブルのパーティションキー、グローバルパーティションインデックスのパーティションキーを持つフィールドに対するUPDATE操作は避けてください。業務上やむを得ない場合は、パーティションテーブルのROW MOVEMENT機能を有効にしてください。
obclient> alter table XXX enable row movement;重複インデックスを避けてください：冗長なインデックスはデータの追加、削除、変更の効率に影響を与え、ストレージコストも無駄に消費します。
主キーにはデフォルトでインデックスと一意性制約が作成されます。
インデックス idx_abc(a,b,c) が既に作成されている場合、インデックス
idx_a(a)とidx_ab(a,b)を再度作成する必要はありません。
パーティションテーブルのインデックスに関する推奨事項
ローカルインデックス -> グローバルパーティションインデックス -> グローバルインデックスの順に選択し、不要な場合はグローバルインデックスの使用は推奨されません。
不要なグローバルインデックスの定義を削減します。
グローバルインデックスのメンテナンスコストは非常に高く、データの追加、削除、変更ごとにグローバルインデックスをメンテナンスする必要があります。 大量に使用するのは適しておらず、データのクエリでは可能な限り主キーを使用するか、データがパーティション内で一意であることが保証されれば、グローバルインデックスを使用する必要はありません。
説明
グローバルインデックスはDMLのパフォーマンスを低下させ、分散トランザクションが発生する可能性があります。パーティションテーブルのインデックス定義において、デフォルトでlocalキーワードを指定しない場合はグローバルインデックスとして定義されるため、ローカルインデックスを定義する際の構文は
CREATE INDEXON (column, column) LOCAL; パーティションテーブルの主キーには、テーブルのパーティションキーを含める必要があります。
パーティションテーブルのローカル一意キーの共通部分には、テーブルのパーティションキーを含める必要があります。
インデックス使用時の注意点
SQLを本番環境に投入する前に、新規作成したインデックスが有効になっていることを確認してください。
インデックスの変更手順は以下のとおりです：まず新規インデックスを作成し、新インデックスが有効になった後、古いインデックスが不要であることを確認してから削除します。
インデックスが多すぎる場合は、不要なインデックスを必要に応じて削除し、インデックスが増え続けるのを防いでください。
注意
この操作はリスクが高いため、削除対象のインデックスが他のSQLで使用されていないことを必ず確認してください。
ALTER TABLE ADD INDEX/DROP INDEXを1つのDDL文で混在させることは禁止されています。インデックス作成時の極端な誤解を避けてください：
1つのクエリに対して1つのインデックスを作成する必要があると誤解すること。
インデックスはスペースを消費し、更新や追加の速度を大幅に低下させると誤解すること。
唯一インデックスは常にアプリケーション層で「検索後挿入」の方式で解決する必要があると誤解すること。
グローバルインデックスの使用上の注意点。
PARTITIONの運用保守を行う際は、DROPまたはTRUNCATE操作によりグローバルインデックスが無効になる可能性があるため注意してください。