【MySQL本番環境】巨大テーブルにロックをかけずにインデックスを作成する方法とは？

2024-06-21

MySQL本番環境の巨大テーブルにロックをかけずにインデックスを作成する方法

オンラインDDLを利用する

MySQL 5.6以降では、オンラインDDLと呼ばれる機能が導入され、インデックス作成などのスキーマ変更をロックをかけずに実行できるようになりました。具体的には、ALTER TABLE ステートメントに ALGORITHM=INPLACE オプションを指定することで、既存のテーブルデータをコピーしながら新しいインデックスを構築します。

利点:

テーブルロックが発生しないため、読み書き操作への影響が最小限に抑えられる
ダウンタイムなしでインデックスを作成できる

データ量が多いほど処理時間が長くなる
古いバージョンのMySQLでは利用できない

パーティショニングを利用する

テーブルを複数のパーティションに分割し、各パーティションに対して個別にインデックスを作成する方法です。パーティションごとにロックをかけるため、全体へのロックは発生しません。

オンラインDDLよりも高速にインデックスを作成できる
大規模なテーブルでも効率的に処理できる

テーブル設計が複雑になる
パーティショニング管理のオーバーヘッドが発生する

どちらのアプローチを選択すべきか

最適なアプローチは、テーブルのサイズ、データ量、パフォーマンス要件などによって異なります。

比較的小規模なテーブルで、処理時間の短縮が優先される場合は、オンラインDDLが適しています。
大規模なテーブルで、高速なインデックス作成が優先される場合は、パーティショニングが適しています。

その他の考慮事項

いずれのアプローチを選択する場合も、事前に十分なテストを行い、本番環境への影響を評価することが重要です。
インデックスを作成する前に、テーブルの統計情報を確認し、適切なインデックスを選択することが重要です。
インデックスはクエリのパフォーマンスを向上させるだけでなく、書き込みのパフォーマンスを低下させる可能性があることに注意する必要があります。

上記の情報に加えて、以下の点にも注意する必要があります。

使用しているMySQLのバージョン
テーブルのスキーマ
使用しているワークロード

これらの要因は、最適なアプローチを選択する際に考慮する必要があります。

-- オンラインDDLを利用する場合

ALTER TABLE your_table_name
  ALGORITHM=INPLACE
  ADD INDEX idx_name (column_name1, column_name2);

-- パーティショニングを利用する場合

CREATE TABLE your_table_name (
  column_name1 data_type,
  column_name2 data_type,
  ...
)
PARTITION BY
  (column_name1)
(
  PARTITION p1 VALUES LESS THAN (100),
  PARTITION p2 VALUES LESS THAN (200),
  PARTITION p3 VALUES LESS THAN (300)
);

ALTER TABLE your_table_name
  ADD INDEX idx_name_p1 (column_name2)
  PARTITION p1;

ALTER TABLE your_table_name
  ADD INDEX idx_name_p2 (column_name2)
  PARTITION p2;

ALTER TABLE your_table_name
  ADD INDEX idx_name_p3 (column_name2)
  PARTITION p3;