データベース

データベース・インデックスの断片化と再編成：究極のガイド

インデックスの断片化 なぜなら、インデックスページの物理的な順序が論理的な順序と異なるため、I/O、CPU、待ち時間が増加するからです。このガイドでは 組織再編, リビルド、フィルファクター、そしてモニタリングが一体となって、フラグメンテーションを確実に認識し、持続的に解消していく。.

中心点

定義断片化されたB*ツリーは、より多くのI/Oを発生させ、より遅いスキャンを行う。.
原因ページの分割、削除、キー値のシフト。.
しきい値%を～5～30本から再編成し、%を～30本から再建する。.
MySQLフォーカスOPTIMIZE TABLEとフィルファクター。.
オートメーションスケジュールされたジョブ、オンライン操作、メトリクス。.

インデックスの断片化とは技術的にどういう意味か？

私は次のように呼んでいる。 フラグメンテーション B*木インデックスの論理的なキー列と物理的なページ連鎖の間の不一致。多くのINSERT、UPDATE、DELETEは、ギャップ、スプリット、未順序のリーフページとなり、より多くの読み取り操作を誘発する。その結果、スキャンはより頻繁にジャンプし、バッファキャッシュヒットは減少し、CPUコストは増加する。理想的なプランであっても、メモリが散らばったページをよりゆっくりと提供するため、苦しむことになる。したがって、私は常に ワークロード, データサイズとメモリレイアウト。.

フラグメンテーションの種類と症状

私は現実的な区別をしている：

論理的断片化リーフページがキー順に連結されなくなった。レンジスキャンは追加のジャンプを必要とし、リードアヘッドはあまり効果的ではない。.
内部の断片化ページには多くの未使用領域がある（充填レベルが低い）。結果行ごとに、より多くのページを読まなければならない。.
構造の断片化不適切なツリーの高さ、アンバランスなノード、転送されたレコードのあるヒープ（SQL Serverなど）。アクセスが間接的になる。.

これは、1行あたりの読み取りページ数の増加、範囲スキャンやオーダーバイ・スキャン時の待ち時間の増加、キャッシュヒット率の低下として測定することができる。ネットワークやストレージの問題との混乱を避けるため、私は常に待機統計とシグナルを関連付ける。.

原因挿入、更新、ページ分割

頻繁に挿入されると、ページの端まで埋まってしまう。 ページ分割, これは、半分埋まったページを2つ残す。削除はエントリーを削除するが、空きスペースは分散されたままであり、常に次の挿入でローカルに使用されるわけではない。キーカラムを変更する更新はレコードを移動させ、より多くのギャップを作る。GUIDのようなランダムなキーパターンはさらに分散を増やし、混乱を招く。私は フィル・ファクター を書き込み負荷に合わせる。.

パフォーマンスの損失を測定可能にする

フラグメンテーションを単独で測定するのではなく、クエリータイム、ログリード、ページリード、ウェイトクラスと組み合わせて測定している。範囲スキャンの平均待ち時間が増加し、クエリーあたりのCPUが増加した場合、私はまずインデックスの物理的なキー数値をチェックします。フラグメンテーションが高いと、同じ行数あたりの読み取りページ数が増加し、I/Oの待ち時間が圧縮される。再編成や再構築の前と後をきちんと比較することで、本当のメリットがわかります。ロック、プラン、ボトルネックに関する背景情報については、以下を参照されたい。データベースのパフォーマンス, 症状を正しく分類する.

メトリクス、待ち時間、ページ効率の詳細

実際、私も観察している：

スキャンごとのページ数典型的なエリアスキャンは何枚のリーフページを読み取るか？同じ結果量で値が増加する場合は、断片化または充填レベルが低すぎることを示しています。.
先読みヒット断片化されたチェーンはシーケンシャルなプリフェッチを妨害する。SSDでは影響は小さいが、CPU、ラッチ、キャッシュが被害を受け続けるため、ゼロではない。.
待機クラスPAGEIOLATCH/IO-Waits (SQL Server)、dbファイルのシーケンシャル/スキャッターリード(Oracle)、またはInnoDBのリードレイテンシの増加(MySQL)は、インデックスのジャンプが強くなると増加する。.
キャッシュの質バッファプールのヒット率がフラグメンテーションと並行して低下している場合、再構築はほとんどの場合、価値がある。.

フラグメンテーションの分析SQLサーバー、MySQL、オラクル

私はいつも、信頼性の高い分析から始める。 スナップショット インデックスの健全性を評価し、ページ使用率が統計的に変動する小さなインデックスをフィルタリングします。SQL Serverでは、sys.dm_db_index_physical_statsがフラグメンテーションの度合いをページ数と共に提供してくれるので、異常値を重み付けすることができます。5-30 %以上の値は再編成を示し、30 %以上の強い異常値は再構築を示し、特に大きなpage_countは再構築を示します。MySQLでは、SHOW TABLE STATUSまたはINFORMATION_SCHEMAビューをチェックし、経時的なデータとインデックスの長さを観察します。オラクルでは、オンライン・リビルドが可能かどうかもチェックする。 ダウンタイム を避けなければならない。

実践的なクエリーと重み付け

私はシンプルで再利用可能なクエリを使って作業し、ページのサイズと関連性に応じて優先順位をつけます：

SQLサーバー私はフラグメンテーションを判断し、小さなインデックスをフィルタリングする。.

SELECT DB_NAME() AS db, OBJECT_NAME(i.object_id) AS obj, i.name AS idx、
       ips.index_type_desc, ips.page_count, ips.avg_fragmentation_in_percent
FROM sys.indexes i
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), i.object_id, i.index_id, NULL, 'SAMPLED') ips
WHERE ips.page_count >= 100
ORDER BY ips.avg_fragmentation_in_percent DESC, ips.page_count DESC；;

MySQL (InnoDB)私はインデックスのサイズ、フリースペース、変化率に注目している。.
```
select table_schema、table_name、engine、index_length、data_free
FROM information_schema.TABLES
WHERE ENGINE = 'InnoDB'
  かつ index_length > 0
order by (data_free) desc；;
```
同時に、期間（例えば毎日）の値を比較し、本当のトレンドと異常値を分離する。統計については、ANALYZE TABLEは、オプティマイザが誤ったカーディナリティを仮定している場合には、控えめに使用します。.
オラクルセグメント統計（空き領域、エクステント）とREBUILD ONLINEの可用性をチェックして、メンテナンスウィンドウを予測しやすくしています。.

利用率の高いインデックスにのみ注目することが重要である。断片化されているが使われていないインデックスは、再編成よりも削除の候補となる可能性が高い。.

再編と再建：決断のマトリクス

の度合いに応じて方法を選んでいる。 フラグメンテーション すべての環境が集中的なI/Oピークに対応できるわけではないためである。再編成は、リーフページを再配置し、論理ジャンプを減らし、フィルファクターに圧縮し、通常はオンラインのままである。Rebuildはインデックスを再構築し、完全にクリーンアップし、メモリーを返却し、統計情報を更新するが、CPU、I/Oを必要とし、多くの場合ロック時間が長くなる。約100ページ以下の小さなインデックスでは大きな利益はほとんど得られないが、30 %フラグメンテーション以上の大きな構造では大きな利益が得られる。私は、その効果が理解しやすいように、そして メンテナンススケジュール フィットする。

方法	リソース要件	代表的な使用例	主な効果
組織再編	低～中	~5-30 % フラグメンテーション	再編成、充填率への圧縮
再構築	高い	> 30 % フラグメンテーション	完全リビルド、メモリ解放

オンライン・オプション、ロック、副作用

中断のない運用のために、私は、利用可能な場合は、次のものを使用している。 オンライン・リビルド にある。私はこのことに注意を払っている：

エディション/バージョンオンライン機能はデータベースやエディションによって異なります。それぞれの環境を個別にチェックしています。.
一時的なメタデータのロックオンライン」でも、通常、最初と最後にはブロックが必要だ。私は意図的に静かな時間帯にスケジュールを組んでいる。.
温度/作業範囲SORT_IN_TEMPDB（SQL Server）のようなオプションは、メイン・データ・ファイルの負荷を軽減しますが、追加の記憶領域を必要とします。.
レプリケーション再構築はログ量を増やす。私はレプリカのラグを監視し、遅延を避けるために必要であればスロットルを回す。.

SQLサーバーのヒープを考慮する 転送記録; ここで、テーブルの再構築がリダイレクトの削除に役立ちます。Oracleでは、REBUILD ONLINEまたはMOVE PARTITION（UPDATE INDEXESを使用）を使用してダウンタイムを短縮します。.

フィルファクター、ページ分割、メモリ

適切な フィル・ファクター 書き込みの多いテーブルには70～90の%を設定し、将来のインサートでローカルに空き領域を使えるようにしている。フィルファクターを下げすぎると、インデックスの成長が速くなり、より多くのメモリを占有するようになる。そこで、ページ使用率、書き込み負荷、インサートパターンの関係を数サイクルにわたって観察する。再構築については、データベース全体ではなく、インデックスごとに意図的にフィルファクターを定義している。定期的な監視により、当初は良好であった トレードオフ ヶ月後である。.

プラットフォームごとのフィルファクターの把握

SQLサーバーFILLFACTORはインデックス・プロパティで、再構築/作成時に有効になる。私は、非常に揮発性の高いセカンダリーインデックスには低い値を設定し、読み込みの多い構造には高い値を設定している。インデックスごとに選択した値を記録し、負荷プロファイルの変更後に再較正を行います。.
MySQL (InnoDB)と innodb_fill_factor 私は、InnoDBが（再）構築のために残す空き領域に影響を与えます。これは日常的なDMLには適用されませんが、OPTIMIZE/ALTERを使用することで、将来的な分割を抑制することができます。また、ホットスポット（単調なキー）を計画して、ラッチ競合や分割を減らすようにしています。.
オラクルとPostgreSQLSTORAGEパラメータまたは. フィルファクター (Postgresでは)ページの空き容量に余裕を持たせている。書き込みの多いテーブルでは、保守的なフィルレベルを使用し、余分なメモリとスキャン時間のバランスを取っている。.

MySQLとWordPressに特化

MySQLのヘルプ OPTIMIZE InnoDB の TABLE は、テーブルと関連インデックスを再編成し、空き領域を返します。削除が多く断片化が激しいワークロードでは、重要なセカンダリインデックスを定期的に作成することも効果的です。WordPressのインストールでは、最適化の前にリビジョンやスパムコメントのような雑多なものを減らし、並べ替えが必要なページが少なくなるようにする。私はこれらのステップを、wp_postmetaや同様のテーブルのクリーン・インデックス戦略と組み合わせている。実践的な紹介は WordPressのインデックスを最適化する, これは、典型的なつまずきの要因に対処するものである。.

MySQL実践: OPTIMIZE、パーティション、副作用

InnoDBにも注意を払っている：

OPTIMIZEテーブル はテーブル（とインデックス）を再構築し、バージョンによってはほぼ “インプレース ”で実行できるが、常にメタロックとログの空き領域を必要とする。このために専用のタイムウィンドウを計画している。.
パーティショニング ターゲットを絞ったメンテナンスが可能：ホットエリアや大量に消去されたエリアのみをOPTIMIZE PARTITIONにすることで、I/Oピークとランタイムを削減。.
レプリケーション大規模なリビルドはビンログボリュームを生成し、レプリカを遅らせる可能性がある。私はメンテナンスを数晩に分散するか、パーティションに分けて作業しています。.
アナライズテーブル オプティマイザがより良い計画を立てるために必要な統計情報を更新します。.

ワードプレスの環境では、あらかじめ 過渡現象, リビジョンと削除された投稿は、OPTIMIZEのデータ移動が少なくなるようにする。wp_postmetaについては、ブロードスキャンを避けるために、適切なコンポジットインデックスを経由してクエリが実行されるかどうかをチェックする。.

PostgreSQLの仕様概要

ここでは MySQL に焦点を当てているが、異機種環境も考慮している：

バキューム/オートバキューム は肥大化を防ぐが、B-tree構造が高度に断片化されている場合はREINDEXの代わりにはならない。.
同時インデックス これにより、新しいインデックスを、ブロックを制限しながら、ほぼオンラインで作成することができる。.
フィルファクター テーブル/インデックスごとに、将来のINSERT/UPDATEのための空き領域を制御します。書き込みの多いテーブルでは、より低い値が有効です。.
パーティション REINDEXは各パーティションごとに使用できます。.

自動メンテナンスとしきい値

私はロバストを使用して再編成と再構築を自動化している。 しきい値 そして、ノイズを避けるために十分なpage_countを持つインデックスだけをアクティブにする。ジョブはメンテナンスウィンドウで実行し、私はできるだけダウンタイムを少なくしてオンラインオプションで長いオペレーションを実行する。時間をずらしたアプローチは、大規模な再構築を閑散期に延期し、小規模な再構築をより頻繁に実行する。オプティマイザがより良いプランを迅速に選択できるように、大きな変更があった後は統計情報を更新しています。フラグメンテーションやレイテンシーが事前に定義された制限を超えるとすぐにアラートが発せられるので、ユーザーから苦情が出る前に対処することができます。.

ランブック持続可能な結果を得るための一連のステップ

特定するサイズとフラグメンテーションによる上位N個のインデックスのスナップショット。.
優先順位作業負荷の重要度、ページ数、スキャン負荷でソートする。.
プランニングしきい値、オンラインオプションの計算、一時停止/ログ要件に従って、再調整/再構築のスケジュールを設定します。.
出演ラージオブジェクトのスタッガード、I/Oスロットリング、レプリケーションラグの監視。.
統計再構築/OPTIMIZE後に統計情報を更新する（または、これが自動的に行われるようにする）。.
検証測定前／測定後：レイテンシー、読み込みページ数、待ち時間、キャッシュヒット率。.
キャリブレーション充填率と閾値をチェックし、学んだ教訓を文書化する。.

ホスティング・チューニング：実践的なルール

ホスティング環境では、私は分析を計画している。 ウィークリー, TempDB/redo/binlogパラメータとストレージメディアは、デフラグの効果に大きく影響する。TempDB/redo/binlogパラメータとストレージメディアは、デフラグの効果に大きく影響する。また、インデックスを追加するたびに書き込み作業が増え、断片化の可能性が高まるため、余計なインデックスがコストを生むだけかどうかも評価する。新しいインデックスを作成する前に、作業負荷のパターン、カーディナリティ、既存のカバレッジをチェックする。典型的なつまずきの概要は以下の通りである。 MySQLのインデックストラップ, 誤審を避けるためだ。.

コストと利益、そして意識的に何もしないとき

すべてのフラグメンテーションに維持する価値があるわけではない。私は意図的に維持しないようにしている：

対象が小さい (例えば、100ページ未満）、大きく変動する - これは利点が平坦でないところである。.
クエリーは選択式 (主にキーごとのルックアップ)であり、レンジスキャンは実行されていない。.
仕事量は一過性 (マイグレーションウィンドウ、間もなくアーカイブ）-その後、最終的なリビルドを計画するだけだ。.

その代わりに、より良いインデックス、より少ない冗長性、よりクリーンなキーの選択に投資し、将来的な分裂の頻度を減らす。.

いつ再編成し、いつ待つべきか？

をリリースする。 組織再編 フラグメンテーションの程度が中程度に増加し、十分なページが実際に影響を受ける場合。大量削除やアーカイブの後、整然と再分配することで、スキャンの効果が顕著になることが多い。深刻な異常値やストレージが必要な場合は、できればオンラインで再構築を計画し、業務の中断を最小限に抑える。100ページ未満の小さなインデックスは、レイアウトの変動が大きく、メリットが少ないため、そのままにしておくことが多い。将来のサイクルが計画しやすいように、ビフォー・アフターの数字とともに決定を文書化する。.

デザインによる長期予防

宜しい スキーム設計 キーの選択、データ型、正規化の一貫性を確保することで、最初のインサートの前でも断片化を減らすことができる。余分な幅のある行は避け、1ページあたりのデータレコード数を減らし、分割を促進する。パーティショニングによってコールドデータとホットデータを分離し、メンテナンスやバックアップ時の副作用を減らす。慎重にクエリを最適化することで、高価なスキャンへの依存を減らし、インデックスを実際のパターンに合わせる。ワークロードの変化に応じて、構造全体をその場限りで破棄するのではなく、インデックスの定義を段階的に調整する。.

キー選択と挿入パターン

主キーの選択は、分割の挙動に決定的な影響を与える：

単調なキー (AUTO_INCREMENTや時間ベースのIDなど）バンドルは右端に挿入し、散乱や分裂を減らすが、ホットスポットを作る可能性がある。私はバッファリング／バッチ処理でホットスポットを均等化する。.
ランダムキー (例えばGUID/UUID v4)は負荷を分散するが、分割確率を増加させる。シーケンシャルバリアント(例えばタイムベースUUID)は分散と順序のバランスが良い。.
ワイドキー インデックスが増え、必要なページ数が増える。無駄のない、選択的なキーはより持続可能である。.

加えて、行とページの圧縮は、1ページあたりのエントリ数に余裕があるため、分割率を下げる。しかし、私は圧縮を有効にする前に、CPUコストとライセンス/機能の有効性を常にチェックしている。.

簡単にまとめると効果のあるステップ

私はまず、集中することから始める。分析最大かつ最も断片化されたインデックスのうち、page_countと作業負荷の重要度に応じて優先順位をつける。そして、中程度のケースの再編成、重いケースの再構築、各インデックスのフィルファクターの再調整など、段階的な対策を実施する。自動化されたジョブは、常に手作業で介入することなく順序を維持し、異常値が発生した場合はアラートが確実に作動します。MySQLとWordPressの環境では、事前にデータの無駄を省き、有用なインデックスだけを残しておくと、顕著なメリットがあります。一貫したモニタリング、明確なしきい値、反復可能なプレイブックで パフォーマンス データが急増しているときでも、安定している。.