概要

1ヶ月ほど前、「6,500万件ほどのレコードがあるテーブルのカラムAにインデックスをはる」作業が必要になった。カラムAはTEXT型のnullableなデータで、インデックスは貼られていない。 6,500万件もレコードがあるため、単純にWHERE カラムA = xxxで検索するだけで1~2分かかってしまう...

→ 運用上の様々な観点も踏まえ、「インデックスを貼りましょう！」となった。

調べたこと(一部抜粋)

対象時間帯のDB Connection
インデックスを貼る( CREATE INDEX する)際、どのようなテーブル操作がなされるのか
6,500万件のレコードがあるテーブルに ALTER TABLE を実行した際の待ちの影響

→ 調べたところ、MySQLの CREATE INDEX は「オンラインDDL」なる機能で比較的安全に実行できるらしい

まずはオンラインDDLについて調査

以下、Gemini先生に聞いてみた概要です。

MySQL のオンライン DDL とは、データベースサーバーを停止させずに実行できる DDL（Data Definition Language）操作のことです。テーブルのメタデータ（構造）を変更する ALTER TABLE などの DDL 操作中に、テーブルへの DML（Data Manipulation Language：データの読み書き）操作やクエリの実行を継続できるため、稼働中のデータベースの可用性と応答性を高めます。これは、従来の DDL 操作で発生していたテーブル全体のロックを防ぎ、ディスク使用量とディスク I/O のオーバーヘッドを削減する機能です。

※ 実際の調査時にはもちろんMySQL公式ドキュメントも参照しましたのでご安心を。

そもそもDDL is なに？

DDL（Data Definition Language）とは

日本語訳すると「データ定義言語」の略で、 SQL文で表現すると CREATE, ALTER, DROP, TRUNCATE の4つ。

テーブル定義を変更するクエリが該当し、テーブル作成・削除やindexの作成、カラムの追加・定義変更などの操作のことを指す。レコード自体を操作するものは後述のDMLに該当する。

番外編: DDL以外の分類

1. DML（Data Manipulation Language）

日本語訳すると「データ操作言語」の略で、SQL文では SELECT, UPDATE, INSERT, DELETE。

「SQL」と言われたまず思いつくもの（個人差あり...?）がDMLで、格納されたデータを扱うための操作。

2. DCL（Data Control Language）

日本語訳すると「データ制御言語」となり、アクセス権を付与するGRANTや権限の取り消しREVOKEなどが含まれる。

個人的にはあまり馴染みはない...

3. その他のクエリ

実行計画を出力するEXPLAINやさまざまなパラメータを取得するSHOW xxxなどは一般的には「ユーティリティコマンド」や「管理コマンド」と呼ばれるらしく、上記のどれにも該当しない。

テスト環境で実施してみる

7000万件くらいのデータを作成し、以下を実行

CREATE INDEX index_name ON table_name (column_A(100));

大体2分くらいで完了し、その間のDML操作も遅延なく実行できた

注意した点

オンラインDDLの実行時、最初と最後に排他メタデータロック(排他MDL)が実行される。

故に、オンラインDDLを開始する前にそのテーブルに対する既存の長時間トランザクションが完了していない場合排他MDLに待ちが発生 -> 後続処理も待ちとなり timeoutエラーを起こす可能性がある。

実行する時間帯を調整し、バッチ処理などの長時間トランザクションが起こりうる時間帯を避ける必要がある

6,500万件のテーブルにindexを貼る

概要