mysql クラスター化インデックスの欠点は何ですか?

クラスター化インデックスは別個のインデックス タイプではなく、データ ストレージ方式 (データ構造ではなくストレージ構造) です。具体的な詳細は実装によって異なりますが、innodb のクラスター化インデックスは実際には btree インデックスと同じものです。データ行は 1 つの構造に保存されます。

テーブルにインデックスがある場合、そのデータ行は実際にはインデックスのリーフページに格納されます。これは、データ行をインデックスのリーフページに格納することができないため、データ行と隣接するキー値が一緒にコンパクトに格納されることを意味します。同時に 2 つが異なる場所にあるため、テーブルには 1 つのクラスター化インデックスのみを含めることができます。ストレージ エンジンはインデックスの実装を担当するため、すべてのストレージ エンジンがクラスター化インデックスをサポートしているわけではありません。以下では主に innodb を紹介しますが、以下で説明する原則はクラスター化インデックスをサポートするすべてのエンジンに適用できます:

リーフ ページには行のすべてのデータが含まれますが、ノード ページにはインデックス列 (または非リーフ ノードのノード ページ) のみが含まれます。これらのノード ページにはインデックス列から抽出された値が含まれているため、インデックス値のインデックス)。

主キーが定義されていない場合、Innodb は主キーを介してデータを集計し、空でない一意のインデックスがない場合、Innodb は暗黙的に 6 バイトの ROWID を定義します。主キーをクラスター化インデックスとして使用します。 InnoDB は同じページ内のレコードのみを集約するため、隣接するキー値を含むページは遠く離れている場合があります。

注意: クラスター化された主キーはパフォーマンスを向上させる可能性がありますが、特にテーブルのストレージ エンジンが innodb から別のエンジンに変換される場合、深刻なパフォーマンスの問題を引き起こす可能性もあります。

集約データにはいくつかの重要な利点があります:

A: たとえば、電子メールを実装する場合、データをユーザー ID に基づいて集約できるため、少数のデータ ページのみを読み取る必要があります。クラスター化インデックスが使用されていない場合、各メールでディスク IO が発生する可能性があります。B: データ アクセスは、インデックスとデータを同じ btree に保存するため、より高速になります。クラスター化インデックス データの取得は、通常、非クラスター化インデックスで検索するよりも高速です

C: カバーインデックススキャンを使用したクエリは、ページノードの主キー値を直接使用できます

クラスター化インデックスの欠点:

A:クラスター化されたデータは IO 集中型のアプリケーションのパフォーマンスを向上させますが、すべてのデータがメモリーに配置される場合、アクセス順序はそれほど重要ではないため、クラスター化インデックスには利点がありません。B: 挿入速度は挿入順序に大きく依存します。主キーに従って順次挿入するのが innodb テーブルにデータをロードする最速の方法ですが、データが主キーの順序でロードされていない場合は、ロード完了後にテーブルの最適化コマンドを使用してテーブルを再編成するのが最善です

C: クラスター化インデックス列の更新は、innodb に更新された各行を新しい場所に強制的に移動させるため、非常に高コストです

D: クラスター化インデックスに基づくテーブルは、新しい行が挿入されるとき、またはプライマリ行が挿入されるときにページ分割の問題に直面する可能性がありますキーが更新され、行を移動する必要がある場合、行の主キー値により行をページ全体に挿入する必要がある場合、ストレージ エンジンは行を収容するためにページを 2 つのページに分割します。これがページ分割です。ページ分割によりテーブルが占有するディスク領域が増加します

E: クラスター化インデックスは、特に行がまばらである場合、またはページ分割によりデータ ストレージが不連続である場合に、テーブル全体のスキャンが遅くなる可能性があります

F: セカンダリ インデックスが使用される可能性があります。セカンダリ インデックスのリーフ ノードには、参照される行の主キー列が含まれるため、想像よりも大きくなります。

G: セカンダリ インデックス アクセスには、1 つではなく 2 つのインデックス ルックアップが必要です

セカンダリ インデックスのリーフ ノードに格納されるのは、行の物理的な場所へのポインタではなく、行の主キー値であるためです。つまり、セカンダリ インデックスを通じて行を検索する場合、ストレージ エンジンはセカンダリ インデックスのリーフ ノードを見つけて対応する主キー値を取得し、この主キー値を使用してクラスタード インデックス内の対応する行を見つける必要があります。ここでは、繰り返しの作業が 1 回ではなく 2 回行われます。innodb の場合、適応ハッシュ インデックスにより、このような繰り返しの作業が軽減されます。

innodb と myisam 物理ストレージ間のデータ分散の比較:

Myisam:

myisam では、主キー インデックスとセカンダリ インデックスの間に構造的な違いはありません。主キー インデックスは、primary という名前の一意の非 null インデックスです。

Innodb:

Innodb はクラスター化インデックスをサポートしているため、同じデータを格納するのに非常に異なる方法が使用されます。Innodb ではクラスター化インデックスはインデックスだけでなくテーブル全体のデータを含みます。 myisam のように別の行ストアが必要です。クラスター化インデックスの各リーフ ノードには、主キー値、トランザクション ID、トランザクションと MVCC のロールバック ポインター、および残りのすべての列の値が含まれます。主キーが列プレフィックス インデックスの場合、InnoDB には完全な主キーも含まれます。列と残りの列の値。

myisam とのもう 1 つの違いは、innodb のセカンダリ インデックスがクラスタード インデックスとは大きく異なることです。innodb のセカンダリ インデックスのリーフ ノードは行ポインタではなく主キーの値を格納し、これをプライマリ キーの値として使用します。この戦略では、行の移動時やデータ ページの分割時にセカンダリ インデックスのメンテナンス作業が軽減され、innodb が占有するスペースが増えるという利点があります。行の移動 セカンダリ インデックス内のこのポインタを更新する必要はありません。

Innodb テーブルを主キーの順に挿入します。Innodb テーブルを使用していて、集計するデータがない場合は、主キーとしてサロゲート キーを定義できます。最も簡単な方法は auto_increment を使用することです。列を自動インクリメントすると、データ行が順番に挿入され、主キーに基づく関連付け操作のパフォーマンスが向上します。

UUID をクラスター化インデックスとして使用しないでください。そうしないと、クラスター化インデックスの挿入が完全にランダムになり、データがクラスター化特性を持たなくなるため、パフォーマンスが非常に悪くなります。 UUID を主キーとして行を挿入するため、時間がかかるだけでなく、インデックスも大きくなります。これは、間違いなく、主キー フィールドが長くなったことが原因です。ページ分割と断片化によるインデックス変更が原因です。主キーの値は連続しているため、Innodb はページの最大フィル ファクターに達すると (InnoDB のデフォルトの最大フィル ファクターはページ サイズの 15/16 であり、一部を解放するために) 各レコードを前のレコードの後に​​保存します。データがこの順序でロードされると、次のレコードが新しいページに書き込まれ、主キー ページはほぼ予想通りのレコードで埋められます。セカンダリ インデックス ページは異なる場合があります)。

UUID 主キーの下では、新しく挿入された行の主キー値が前の値より必ずしも大きいとは限らないため、innodb は常に新しい行をインデックスの最後に挿入するだけではなく、適切な場所を見つける必要があります。新しい行は通常、既存のデータの中間位置であり、新しいスペースが割り当てられるため、多くの追加作業が追加され、データ分散の最適化が低下します。 UUID を主キーとして使用する場合には、次のような欠点があります。

A: 書き込み対象のページがディスクにフラッシュされてキャッシュから削除されているか、キャッシュにロードされていない可能性があります。InnoDB は、挿入する前にターゲット ページを検索してディスクからメモリに読み取る必要があり、その結果、大量のランダム IO

B: 書き込みの順序が狂っているため、innodb は新しい行にスペースを割り当てるためにページ分割操作を頻繁に実行する必要があります。ページ分割により、少なくとも 3 ページが移動される必要があります。 1 ページではなく、挿入用に変更されます

C : 頻繁なページ分割により、ページがまばらになり、不規則に埋められるため、最終的なデータは断片化されます

これらのランダムな値をクラスター化インデックスにロードした後、テーブルの最適化を実行してテーブルを再構築し、ページ充填を最適化する必要がある場合があります。 InnoDB を使用する場合は、できる限り主キーの順序でデータを挿入し、可能な限りクラスタリング キーの値の単純な増分を使用して新しい行を挿入する必要があります。

注: 連続した主キーにより悪い結果が生じるのはどのような場合ですか?

同時実行性の高いワークロードの場合、Innodb で主キーの順序で挿入すると明らかな競合が発生する可能性があります。主キーの上限はホットスポットと呼ばれます。これは、すべての挿入がここで行われるため、同時挿入により別のギャップ ロック競合が発生する可能性があります。ホット スポットは auto_increment ロック メカニズムである可能性があります。この問題が発生した場合は、テーブルまたはアプリケーションを再設計するか、innodb_autoinc_lock_mode 構成を変更する必要がある可能性があります。

以上がmysql クラスター化インデックスの欠点は何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。