CREATE INDEXを使いこなす

検索時には大変お世話になっているインデックス。
使われなくても文句も言わずに更新され続けるインデックス。
ずっと放置して運用しているといつの間にか肥大化しているインデックス。

今回は、そんなインデックスを再構築する際に気をつけることをまとめてみました。

まずは、インデックスを作成、または再作成するやり方について簡単に。

普通にインデックスを作成。
他のトランザクションはテーブルを読み取ることはできるが、対象テーブル内の行を挿入、更新、削除しようとすると、
インデックス作成が完了するまでブロックされる。

挿入、更新、削除をブロックしないインデックスの作成方法。
テーブルを2回スキャンする必要があり、潜在的にそのインデックスを使用する可能性がある、実行中のすべての
トランザクションが終わるまで待機する必要がある。
通常よりも総作業時間がかかり、また、完了するまでの時間が非常に長くなる。

インデックスの再構築。
インデックスの元となるテーブルの書き込みをロックしますが、読み込みはロックしません。
また、処理中のインデックスに対する排他ロックを取得するので、そのインデックスを使用する読み込みはブロックされる。

上記の内容を簡単にまとめると下表のようになります。

方式	読み込みブロック	更新ブロック	備考
CREATE INDEX	無し	有り	普通
CREATE INDEX CONCURRENTLY	無し	無し	作業時間が長い
REINDEX INDEX	一部有り	有り	reindex中のindexを使用する読み込みは待機

実際、運用中にインデックスの再構築を行う必要がある場合（読み込み、書き込みともに停止できない）は、

こんな感じでやるのが、良いのではないでしょうか。

夜間等、使用されない時間帯が決まっている場合、定期的にメンテナンスの時間が確保できる場合は、
reindexを行う方が簡単なのは間違いないです。