MySQLでインデックスを作成する手順と注意点

いつインデックスを作成するか？

MySQL5.6以上であれば、インデックス作成中に該当テーブルに対するSELECTやUPDATEが可能です。
しかし、MySQLにはメタロックという仕組みがあるため、メタロックが取得できない時間帯には
インデックスを作成してはいけません。
結果として、該当テーブルへのアクセスが少ない時間帯に実施する必要があります。
　※数千万件程度のレコード数のテーブルになってくると30分以上かかったりします。

メタロックのまとめ

ポイントだけ記載します
- ALTER TABLE文の開始時にメタロックを取得する。
- メタロックが取得できない場合、「Waiting for table metadata lock」となり、待ちになる。
- 「Waiting for table metadata lock」の間、該当テーブルへのアクセスがすべて待ちになるため、システム障害につながる可能性大。
- メタロックが取得できれば、インデックス作成が開始され、「altering table」になる。
- 「altering table」中は、SELECTやUPDATEが可能である。
- ALTER TABLE文の終了時にもメタロックを取得する。（ここで待ちになると水の泡・・）

メタロックの回避

• オンラインやバッチでそのテーブルにアクセスする長いトランザクションの処理がある場合、メタロック待ちになる。常駐バッチなどあれば停止しておくようにする。
• 手作業でも起きる。該当テーブルをSELECTしただけで暗黙的にそのトランザクションが開始されているため、ALTER TABLEしても、メタロック待ちになってしまいます。
• 以前、インデックス作成者自身が別セッションで作業前にSELECT COUNT(*)していたせいで、いつまでたっても、メタロックが取得できないということもありました。
• 周囲の仲間がSELECTしていたら、Exitしてもらうように伝えておきましょう。
• ロックしている人が見つからなければ、問題有無を確認した上でkillします。

実行手順の検討

1.「LOCK=NONE」は不要だが、付けていると何となく安心。

ALTER TABLE USER_TBL ADD INDEX IDX_USER_TBL_01
(MAIL_FLAG), LOCK=NONE;

　　
2.タイムアウトの設定
メタロック待ちになり、他のトランザクションが待ちになってしまうことを回避したい場合は設定する。ただし、インデックス作成完了後のメタロックが取得できない場合、やり直しになってしまうので、設定しない方がよい。

- 3秒メタロックを取得できない場合、キャンセルされる。
- 影響範囲は自セッションのみ
SET SESSION lock_wait_timeout = 3;

実行手順

1.セッションを2つ用意します。
　　セッションA：ALTER TABLE用
　　セッションB：監視チェック用

　　
2.ALTER TABLE文の実行（セッションA）

mysql> ALTER TABLE USER_TBL ADD INDEX IDX_USER_TBL_01 (MAIL_FLAG), LOCK=NONE;

　　
3.メタロック発生有無の確認（セッションB）

mysql> SELECT STATE
mysql> FROM INFORMATION_SCHEMA.PROCESSLIST
mysql> WHERE INFO LIKE 'ALTER TABLE%';

→メタロック待ちしていない場合は手順4,5はスキップ。
→メタロック待ちしている場合はALTERをキャンセルするか、メタロックを持っているプロセスを手順4､5でkillする。
　　
　　
4.メタロック待ちプロセスを探す（セッションB）
実行中のプロセスを開始順で取得します。
　※開始時間が最も早いものがロック中であることが多いです。
　※何度か実行して、継続しているものは特にあやしい。
　※メタロックが発生している場合のみ結果が出力されるようにしています。

SELECT trx_mysql_thread_id
FROM information_schema.innodb_trx
INNER JOIN INFORMATION_SCHEMA.PROCESSLIST
  ON INFORMATION_SCHEMA.PROCESSLIST.ID = information_schema.innodb_trx.trx_mysql_thread_id
WHERE information_schema.innodb_trx.trx_state = 'RUNNING'
  AND EXISTS
(SELECT 'X'
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE STATE = 'Waiting for table metadata lock'
  AND INFO LIKE 'ALTER TABLE%')
ORDER BY information_schema.innodb_trx.trx_started;

　
5.killする（セッションB）

kill MySQLスレッドID

- AWS Auroraの場合
CALL mysql.rds_kill(MySQLスレッドID);

　　
6.インデックス作成が完了（セッションA）

Query OK, 0 rows affected (51.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

7.インデックスが作成されていることを確認する。

show index from USER_TBL;

以上です！