はじめに
今回も「達人に学ぶ DB 設計徹底指南書 第 2 版」の 6 章の演習問題で調べた「インデックスの再編成方法」についてまとめました！
MySQL と PostgreSQL の２つだけ調べました。
何故、再編成が必要なのか？
B-tree は平衡木（balanced tree）と呼ばれ、どのリーフノード（実データのポインタを持つノード）もルートノードからの距離が同じになる。このため、どんなキー値でも同じ速度で結果を得られるという利点がある。
ただ、長期間の運用で、削除、更新、挿入処理など繰り返されることで、インデックスの構造が崩れ、非平衡木になってしまいバランスが悪くなり、検索時間にばらつきが生まれたり、パフォーマンスが低下していく。
これを解決するために定期的にインデックスの再編成を行う必要がある。
インデックスの再編成
MySQL のインデックスの再編成
OPTIMIZE ステートメントを使用する
OPTIMIZE TABLE <テーブル名>;
処理内容
- InnoDB エンジンの場合、このコマンドはテーブルのデータを物理的にコピーし、元のテーブルを削除して新しい最適化されたテーブルに置き換える。
- B-tree インデックスも再構築され、断片化が解消される。
- 一時的にテーブル全体をロックするため、クエリや更新のパフォーマンスに影響を与える場合があるので、負荷の低い時間に実行するのが望ましい
使用するタイミング
- テーブルに大量の削除や更新が行われた場合。
- クエリパフォーマンスが低下していると感じたとき。
Postgresql のインデックスの再編成
REINDEX INDEX コマンドを使用
インデックスに対して実行
REINDEX INDEX <インデックス名>;
テーブルに対して実行
REINDEX TABLE <テーブル名>
データベースに対して実行
REINDEX DATABASE <データベース名>
REINDEX コマンドの動作
- 既存のインデックスを再作成し、断片化したインデックス構造を最適化する。
- テーブルやインデックスをロックするため、大規模なテーブルに対して使用する際は、負荷の低い時間帯に実行することが推奨される。
使用するタイミング
- インデックスが大きく断片化している場合（VACUUM ANALYZE などで断片化を確認できる）。
- 大量のデータの削除や更新が行われた場合。
- クエリパフォーマンスが著しく低下した場合。
まとめ
以上、MySQLとPostgreSQLのインデックスの再編成についてでした。
実際に実務、個人開発含め、再編成処理を自分が担当する機会はあまりないと思いますが、運用する中で、異変に気付いたり、手立ての1つとして知っておくことは大切だなと感じました。
参考書籍・サイト