インデックス設計

本記事では、OceanBaseデータベースにおけるインデックス設計の推奨事項を紹介します。

インデックスの概要

OceanBase データベースは、主キーインデックス、一意インデックスをサポートしており、さらにセカンダリインデックスもサポートしています。これらのインデックスは、単一の列または複数の列（複合インデックス）で構成できます。OceanBase データベースでは、インデックスはローカルインデックスとグローバルインデックスの 2 種類に分けられます。両者の違いは、ローカルインデックスはパーティションデータとパーティションを共有するのに対し、グローバルインデックスは独立したパーティションを持つことです。

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モードのデフォルト設定ではローカルインデックスが作成されます。

• グローバルインデックスの作成

  obclient> CREATE INDEX idx_t1_name2 ON t1(name2) GLOBAL;
  

インデックス設計

単一値インデックスの要件と推奨事項

• インデックスの作成は、最左前接辞則を満たす必要があります。

• テーブル内のすべてのインデックス付きフィールドは、NOT NULL属性を持つことを推奨します。通常、業務に応じてDEFAULT値を定義することを推奨します。

• 業務上、一意の特性を持つフィールドは、組み合わせフィールドであっても、主キーとして作成することを推奨します。

  説明

  一意インデックスがINSERT速度に影響を与える場合でも、その速度損失は無視できるものです。一意インデックスによる検索速度の向上は顕著です。また、アプリケーション層で非常に完璧な検証と制御を行っていても、一意インデックスがない限り、マーフィーの法則により必ずダーティデータが発生します。

• JOINが必要なフィールドでは、データ型を統一する必要があります。複数テーブルの結合クエリ時には、結合されるフィールドにインデックスが作成されていることを確認してください。

  説明

  複数テーブルの結合（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 set
  

• ORDER 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) は両方のステートメントで同時に使用でき、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 INDEX ON (column, column) LOCAL;

• パーティションテーブルの主キーには、テーブルのパーティションキーを含める必要があります。

• パーティションテーブルのローカル一意キーの共通部分には、テーブルのパーティションキーを含める必要があります。

インデックス使用時の注意点

• 新規インデックスを作成した後、SQLを本番環境に投入する前に、新規インデックスが有効になっていることを確認してください。

• インデックスの変更手順は以下のとおりです：まず新規インデックスを作成し、新規インデックスが有効になった後、古いインデックスが不要であることを確認してから削除します。

• インデックスが多すぎる場合、不要なインデックスを削除し、インデックスが増え続けるのを防いでください。

  注意

  この操作はリスクが高いため、削除対象のインデックスが他のSQLで使用されていないことを確認する必要があります。

• ALTER TABLE ADD INDEX/DROP INDEX を1つのDDLで混在して使用することは禁止されています。

• インデックス作成時に、以下の誤解を避けてください：

  • 1つのクエリに対して1つのインデックスを作成する必要があると誤解すること。

  • インデックスはスペースを消費し、更新と追加の速度を大幅に遅らせると誤解すること。

  • 唯一インデックスは、アプリケーション層で「検索→挿入」の手順で解決する必要があると誤解すること。

• グローバルインデックスの使用に関する注意事項。

  PARTITION の運用保守を行う際は、DROP または TRUNCATE 操作によりグローバルインデックスが無効になる可能性があるため注意してください。