インデックス設計

本記事では、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モードではデフォルトでローカルインデックス（local）が作成されます。

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

  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=? のようなクエリ条件が存在する場合、a、bフィールドにそれぞれidx_a(a)、idx_b(b)の2つのインデックスを作成する代わりに、組み合わせインデックスidx_ab(a,b)を使用します。後者では2つのインデックスを同時に利用できません。

• 複数のシナリオでのクエリを満たしつつ、インデックスの冗長性を削減するために、組み合わせインデックスを適切に設計します。

  • 複数のステートメントで同一のインデックスを共有できます。そのうちの一部のステートメントでは、インデックスのプレフィックスをカバーするだけで済みます。例えば、a = ? and b = ? と b = ? の2つのステートメントがある場合、組み合わせインデックスidx_ba(b,a)を使用すれば、idx_ab(a,b)と idx_b(b)の2つのインデックスを個別に作成する必要はありません。

  • 不要な場合はグローバルインデックスを使用しないでください。特に、NDV値が小さく、使用頻度も低いグローバルインデックスは避けるべきです。

  • WHERE条件にパーティションキー列が含まれている場合、パーティションキー列と条件内の他の列に結合ローカルインデックスを作成できます。

  • グローバルパーティションインデックスは、クエリで返されるデータ量が少ないシナリオに適しています。データ量が多いシナリオでは、グローバルインデックスとローカルインデックスの間で比較テストを行うことを推奨します。

  • NDV値が大きいフィールドをグローバルパーティションインデックスのパーティションキーとして選択します。

  • パーティションテーブルのパーティションキー、およびグローバルパーティションインデックスのパーティションキーを持つフィールドに対しては、更新操作を行わないことを推奨します。業務上必要不可欠な場合は、パーティションテーブルの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操作によりグローバルインデックスが無効になることにご注意ください。