インデックスの​断片化が​引き起こした​問題の​事例紹介

１. ​はじめに

データベースを扱うシステム開発を行っていると、避けて通れないのが「インデックス」の存在です。
検索性能を大きく左右する重要な仕組みであり、設計や運用を誤るとパフォーマンス低下やメモリ使用効率の悪化といった問題を引き起こすこともあります。

本記事では、実際に発生したトラブル事例をもとに、インデックスがどのような問題を引き起こしたのか、そしてどのような対策を取ったのか紹介していきます。

２. トラブル事例

Microsoft SQL Server（以降はSQL Server）を用いたWebアプリケーションを開発していますが、ある日突然 SQL Server に対してレコードの INSERT に失敗する事象が発生しました。

データベースのサイズに上限のある SQL Server Express Editionを活用しており、データサイズの監視も行っていましたが、データベースのサイズがその上限に引っかかっていることが原因で INSERT に失敗していました。
（データサイズの監視では、まだ余裕があるように見えていました）

データサイズだけでなくインデックスのサイズも加わるため、その分データベースのサイズは大きくなりますが、ここまで膨らむ要因には何があるのか、調査が必要になりました。

3. データベースの​サイズが​増加した​１要因

データベースのサイズが膨らんだ要因は複数見つかりましたが、インデックスのサイズが想定以上に膨らんでおり、これは「内部断片化」によるものでした。
この「内部断片化」について以降で説明していきます。

SQL Server では、「ページ」と呼ばれる固定サイズ（8KB）を最小単位としてデータを管理しています。
インデックスもこのページ単位で管理されますが、DELETE や UPDATE などの操作によりインデックスの削除・縮小が繰り返され、ページ内部に空きが多くなってしまう現象が発生してしまいます。

このような状態でデータの INSERT が発生した場合、インデックスはページ内部に空きがあるからといって、必ず空き領域にデータが格納されるわけでは無く、新しいページにインデックスを格納する場合があります。

その結果、実際のデータ量は少ないにも関わらずページ数が増え、全体としてデータベースのサイズが大きくなっている状況になっていました。

このように、ページ内部に空き容量が多くなる現象を「内部断片化」と呼びます。
ページ内部はこんなイメージです。

• 内部断片化無し
  |████████████████| ← ページ内に空きがない
• 内部断片化あり
  |░░░░░░██░░░██░░░| ← ページ内に空きが多い
  ░は未使用部分を表現しており、これが増えていくと断片化率が高くなっていきます。

４. 実施した​対策

発生した断片化に対し "再構築" を行い、断片化を解消しました。
再構築はインデックスの構造を一から作りなおすことで、断片化を完全に解消する方法です。
ただ、この再構築は対象のテーブル全体に修正ロックをかけるため、サービス運用中に再構築を行うとユーザがシステムを利用できなくなるなどのデメリットもあり、再構築はメンテナンスで実施する必要があります。

上記対策以外に、再構築と比べると少し効果は下がりますが "再構成" という方法もあります。
再構成は近くに配置されているインデックスを並べ替えることで断片化を部分的に解消する方法です。
修正ロックを部分的にかけて行うため、再構築と比べるとサービスへの影響も軽微で済みますが、部分的な修正ロック、またリソース消費観点からパフォーマンスへの影響も０ではありませんので、こちらも特別な理由が無ければ、サービスの運用を停止し、メンテナンス作業として実施することをお勧めします。

再構築と再構成を比較すると、表の様になります。

５. まとめ

データベースにおけるインデックスは検索性能を大きく左右する重要な仕組みですが、断片化発生時にはメモリ使用効率を著しく低下させる場合もある、ということをご説明しました。

断片化発生時の対処として、再構築、再構成は非常に有効な手段です。
ただ、それぞれの仕組みを理解しないとユーザへの影響も発生してしまいます。
本記事が適切なメンテナンス作業の役に立てば幸いです。