#そもそも中身ちゃんと理解してる？
日々DBへデータが追加されるごとに、どんどんDBのレスポンスが悪くなってくる。
なんとなくインデックスの再構成だったり、再構築をやってみるみたいなことが結構あると思ってます。
自分自身もとりあえずやろう精神がないとは言えないので、詳細な情報をまとめてみました。
#インデックスの断片化って何？？
なんでおそくなるの？
インデックスが断片化してるんじゃない？
####断片化ってなんやねん。
DBは日々更新や削除が行われ、そのたびにインデックスが自動で変更されます。
DBが正常な状態であれば、インデックスのキー値に準拠した順番と物理的なメモリの位置の順序が一致しているはず。
しかし、データの更新で既存のデータを分割して新しいキーを挿入する場合もある、このような変更が大量に行われると、インデックスのキー値に準拠した順番と物理的なメモリの位置の順序が一致しないデータが発生し、断片化している状態になってします。
####なんで断片化すると検索が遅くなるの？
インデックスの断片化が大きい状態では、インデックスが指示しているデータをと検索するために、より多くのI/Oが必要になるため物理的に遅くなる。
####今のDBの断片化状態の確認方法
--クエリでの確認方法
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'DbName'), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 30
じゃぁどうやって改善するのかっていうのが、本題のインデックスの再構成、再構築
#インデックスの再構成と再構築の違いってなんやねん。
インデックスの断片化を解消するために利用されるのが、インデックスの再構築、再構成。
インデックスの再構築 (rebuild) と再構成 (reorganize) の違い
| 再構築 (rebuild) | 再構成 (reorganize) | |
|---|---|---|
| 処理対象 | インデックス全体 | インデックスのリーフレベルのみ |
| 同時実行性 | 処理中は処理対象インデックスは使用不可。インデックス全体がロックされる。 | 処理中も処理対象インデックスは使用可。処理しているページに対して、そのページの処理中のみロックが保持される |
| 処理を途中でキャンセルした場合 | ロールバック | キャンセルした時点までの処理は有効。 |
| 生成されるトランザクションログレコードの量 | 断片化の度合にほとんど影響されない | 断片化の度合が大きいと多くなる。 |
| 使用するデータファイル内の領域 | 多い | 少ない |
| 処理完了までに必要な時間 | 断片化の度合にほとんど影響されない | 断片化の度合が大きいと長くなる |
状況にもよるが、先ほどの断片化状態の確認で断片化が30%以上の場合は再構築が望ましい。
また、再構築の場合はインデックスを再作成する。しかし、再作成中は既存のインデックスは削除しない。
そのため一時的にインデックスの容量が2倍になるため、領域を確保する必要がある。
上記のような差があるため、その場にあった対処法を決めたい。
特にロックの範囲が違うので、しっかり同時実行性を担保して実行したいですね。
#最後に
簡単にまとめるとこんな感じ。
ちゃんと理解して使おう。