リードレプリカのインデックスを作成して管理する

このページでは、Cloud SQL リードレプリカでインデックスを追加または削除する方法について説明します。通常、レプリカは読み取り専用ですが、レポート作成用としてテーブルにセカンダリ インデックスを作成する必要が生じることがあります。Cloud SQL では、これらのインデックスを管理するための一連のストアド プロシージャが用意されています。

用語

  • クラスタ化インデックス。ディスク上の行を物理的に並べ替える MySQL テーブルのメイン インデックス。テーブルに主キーを定義すると、MySQL はそれをクラスタ化インデックスとして使用します。1 つのテーブルに存在できるインデックスは 1 つだけです。
  • セカンダリ インデックス。MySQL テーブルに対する追加のインデックスで、クエリのパフォーマンスを最適化します。

インデックスのストアド プロシージャ

Cloud SQL には、mysql スキーマに 2 つのストアド プロシージャが含まれています。これにより、MySQL リードレプリカにセカンダリ インデックスの追加またはドロップを行うことができます。これらのプロシージャはリードレプリカ用に設計されていますが、プライマリ ソース インスタンスでも実行できます。

mysql.addSecondaryIdxOnReplica
データベースにセカンダリ インデックスを追加します。このストアド プロシージャは、CREATE INDEX DDL ステートメントのラッパーです。

パラメータ:

  • idxType - 作成するインデックスのタイプ。たとえば、一意のインデックスを作成するには UNIQUE を渡します。
  • idxName - インデックスの名前。
  • tableName - テーブルの名前（schema.name の形式）。
  • idxDefinition - インデックスの定義。外側のかっこは含めないでください。
  • idxOption - インデックス作成時に渡す追加のオプション。たとえば、MySQL 8.0 では、非表示のインデックスに INVISIBLE を渡すことができます。

構文:

mysql.addSecondaryIdxOnReplica(idxType, idxName, tableName, idxDefinition, idxOption)
mysql.dropSecondaryIdxOnReplica
データベースのセカンダリ インデックスを削除します。このストアド プロシージャは、DROP INDEX DDL ステートメントのラッパーです。

パラメータ:

  • idxName - インデックスの名前。
  • tableName - テーブルの名前（schema.name の形式）。
  • idxOption - インデックスの削除時に渡す追加のオプション。たとえば、INPLACE などのアルゴリズム オプション。

構文:

mysql.dropSecondaryIdxOnReplica(idxName, tableName, idxOption)

idxType パラメータと idxOption パラメータについては、Cloud SQL インスタンスで実行される MySQL のメジャー バージョンのドキュメントをご覧ください。

例

正しい使用方法

プロシージャの呼び出し例を次に示します。次のような定義を持つテーブルがあるとします。

CREATE TABLE sampletest.t1(
   id int(10) unsigned NOT NULL AUTO_INCREMENT,
   first_name varchar(64) NOT NULL,
   last_name varchar(64) NOT NULL,
   license_id int NOT NULL,
   PRIMARY KEY (id),
   KEY idx_fname (first_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

first_name 列と last_name 列で t1_fname_lname という名前の通常インデックスを作成するには、次のコマンドを実行します。

call mysql.addSecondaryIdxOnReplica('', 't1_fname_lname', 'sampletest.t1', 'first_name, last_name', '')

license_id 列に t1_license_id という一意のインデックスを作成し、「unique license id」というコメントも作成するには、以下を実行します。

call mysql.addSecondaryIdxOnReplica('unique', 't1_license_id', 'sampletest.t1', 'license_id', 'comment \"unique license id\"')

t1_fname_lname インデックスをドロップするには、次のコマンドを実行します。

call mysql.dropSecondaryIdxOnReplica('t1_fname_lname', 'sampletest.t1', '')

不適切な使用

idxDefinition パラメータの外側のかっこがあるため、first_name 列と last_name 列にインデックスを作成しようとすると失敗します。

call mysql.addSecondaryIdxOnReplica('', 't1_extra_parenthesis', 'sampletest.t1', '(first_name, last_name)', '')

インデックスは、ユーザーが作成したテーブルにのみ追加できます。mysql.servers テーブルの host 列にインデックスを作成しようとしても失敗します。

call mysql.addSecondaryIdxOnReplica('', 'idx_invalid', 'mysql.servers', 'host', '')

dropSecondaryIdxOnReplica プロシージャを使用できるのは、以前に addSecondaryIdxOnReplica プロシージャを使用して作成したインデックスをドロップする場合のみです。たとえば、次の呼び出しでは既存の idx_fname インデックスをドロップできません。

call mysql.dropSecondaryIdxOnReplica('idx_fname', 'sampletest.t1', '')

これらのプロシージャ呼び出しの SQL インジェクションは失敗します。たとえば、コメント シーケンスを含む次の SQL インジェクションは失敗します。

call mysql.addSecondaryIdxOnReplica(\"user 'a'@'%' --\", 'idx_fname', 'sampletest.t1', 'first_name', '')

同様に、区切り文字を含む次の SQL インジェクションも失敗します。

call mysql.addSecondaryIdxOnReplica('', 'idx_fname', 'sampletest.t1', 'first_name', ';flush status')

リードレプリカの再作成

Cloud SQL では、問題が発生した場合にインスタンスをすばやく復元するため、プライマリ ソースからリードレプリカが再作成される場合があります。再作成オペレーションの前にリードレプリカに作成されたインデックスは保持されません。リードレプリカでこれらのストアド プロシージャを使用する場合は、こうしたインデックスをユーザーの責任で再作成する必要があります。

次のステップ