InnoDBのインデックスページ構造、挿入バッファ、適応ハッシュインデックスについての簡単な説明

InnoDB インデックスの物理構造

すべての InnoDB インデックスは Btree インデックスであり、インデックスレコードはリーフに格納され、デフォルトのインデックスページサイズは 16K です。新しいレコードが挿入されると、InnoDB は将来の挿入および更新操作のために空きページサイズの 1/16 を残しておこうとします。

インデックスレコードがインデックスレコードサイズの順序どおりに挿入された場合、インデックスはページ全体のサイズの 15/16 も埋めます。挿入順序が完全にランダムである場合、インデックスページは基本的に 1/2 から 15/16 まで自己構築されて埋められます。フィルファクターが 1/2 未満の場合、InnoDB は b ツリーの再構築を試みます。

MySQL 5.6 以降では、innodb_page_size パラメータを使用して、現在のインスタンス内の各インデックスページのサイズを設定できます。一度設定すると、元に戻すことはできません。推奨される構成は通常、16K、8K、または 4K です。さらに、MySQLインスタンスがデフォルト値とは異なるinnodb_page_size Aに設定されている場合、Aとは異なる値を持つ他のインスタンス上のファイルを使用できなくなります（たとえば、物理的なバックアップと復元を行うため）。

バッファリングを挿入

データベースアプリケーションは通常、主キーの順序でデータを挿入します。この場合、クラスター化インデックスの順序は主キー値の順序とまったく同じなので、挿入操作によってランダム IO が大幅に削減されます。

一方、セカンダリインデックスは通常は一意ではないため、データは比較的ランダムな順序でセカンダリインデックスに挿入されます。同様に、削除および更新操作がデータページに影響する場合、セカンダリインデックス上で互いに隣接していないインデックスへの変更が伴います。これにより、ランダム IO が大量に発生します。

一意でないセカンダリインデックスにレコードを挿入したり、レコードを削除したりする場合、InnoDB はまずセカンダリインデックスページがバッファープール内にあるかどうかを確認します。バッファプール内にある場合、InnoDB はメモリ内で直接インデックスページを変更します。インデックスがバッファプール内にもない場合、InnoDB は挿入バッファに変更を記録します。挿入バッファは通常は小さいため、バッファプール内に完全に保持され、頻繁に更新されます。この変更プロセスは変更バッファリングと呼ばれます（通常、挿入操作にのみ影響するため、挿入バッファリングとも呼ばれ、データ構造は挿入バッファです）。

挿入バッファをフラッシュするためのディスクI/O

では、挿入バッファリングによってランダム IO はどのように削減されるのでしょうか?時々、挿入バッファは挿入バッファ内のセカンダリ非一意インデックスをマージします。通常、N 個の変更を同じ Btree インデックスのインデックスページにマージするため、多くの IO 操作が節約されます。テストの結果、insertbuffer は挿入速度を 15 倍に向上させることができました。

トランザクションがコミットされた後も、挿入バッファは書き込みをマージしている可能性があります。したがって、DB が異常に再起動された場合、リカバリフェーズで大量のセカンダリインデックスを更新または挿入する必要があるときに、挿入バッファーに数時間かかることもあります。このフェーズでは、ディスク IO が増加し、ディスクバウンドクエリのパフォーマンスが大幅に低下します。

適応ハッシュインデックス

アダプティブハッシュインデックス (AHI) により、バッファープールに十分なメモリと特定のワークロードがある場合、トランザクション機能と安定性を犠牲にすることなく、InnoDB はメモリ内データベースのように見えます。この機能は、動的パラメータである innodb_adaptive_hash_index によって制御されます。デフォルト値はオンで、これはアダプティブハッシュインデックスがオンになっていることを意味します。AHI をオフにすると、組み込みハッシュテーブルはすぐにクリアされ、B-TREE インデックスに直接アクセスしながら、通常の操作を続行できます。 AHI を再度有効にすると、ハッシュテーブルが再構築されます。

検索パターンを観察することにより、MySQL はインデックスキーのプレフィックスを使用してハッシュインデックスを作成します。このプレフィックスは任意の長さにすることができ、B ツリー全体ではなく、B ツリー上の一部の値だけになることもあります。ハッシュインデックスが検出され、頻繁にアクセスされるインデックスページにハッシュインデックスが作成されます。

テーブルの大部分がバッファプール内にある場合、ハッシュインデックスを作成すると、Btree インデックス値をソートされたポインタに変換することで等価クエリを高速化できます。 Innodb には、インデックスの検索状況を監視できるメカニズムがあります。ハッシュインデックスを作成することでクエリを最適化できることに気付いた場合は、自動的にハッシュインデックスを作成するため、「適応型」です。

一部のワークロードでは、ハッシュインデックス検索によって得られるパフォーマンスの向上が、インデックス検索の監視とハッシュテーブル構造の維持による追加のオーバーヘッドを上回ります。ただし、高負荷の状況では、アダプティブハッシュインデックスに追加された読み取り/書き込みロックによって、同時実行性の高い結合操作などの競合が発生することもあります。 Like 演算子と % ワイルドカード文字も AHI には適用されません。ワークロードが AHI に適していない場合は、不要なパフォーマンスのオーバーヘッドを回避するために AHI をオフにすることをお勧めします。 MySQL 内の特定の状況で AHI が適切かどうかを予測することは難しいため、実際のワークロード (AHI ありとなし) でストレステストを実行することをお勧めします。 5.6 以降のバージョンでは、現在はデフォルトで有効になっていますが、より多くのワークロードでアダプティブハッシュインデックスを無効にすることが最適であると見なされるようになります。

ハッシュインデックスの作成は、多くの場合、既存の B ツリーに基づいて行われます。InnoDB は、B ツリーの検索状況を観察し、任意の長さの B ツリーインデックスプレフィックスを確立することで、ハッシュインデックスを作成できます。ハッシュインデックスは、B ツリーインデックスの最も頻繁にアクセスされるページのみを含む部分的なインデックスにすることができます。

show engine innodb status の結果の SEMAPHORES セクションを観察することで、アダプティブハッシュインデックスを使用するかどうかを決定できます。 btr0sea.c ファイルで作成された rw-latch を待機しているスレッドが多数ある場合は、アダプティブハッシュインデックスをオフにすることをお勧めします。以下は私が遭遇したケースのスクリーンショットです。これは高同時実行モードでの AHI 競合の典型的なケースです。AHI をオフにする必要があります。