mysql innodb インデックス原理の詳細な紹介 (コード例)-mysql チュートリアル-php.cn

この記事では、mysql innodb インデックスの原則について詳しく紹介 (コード例) しています。一定の参考値があります。必要な友人は参照できます。お役に立てれば幸いです。

クラスター化インデックス (クラスター化インデックス)

innodb ストレージエンジンテーブルはインデックス構成テーブルであり、テーブル内のデータは主キーの順序で格納されます。クラスター化インデックスは各テーブルの主キー順にBツリーを構築し、そのリーフノードにテーブル全体の行レコードデータが格納され、このリーフノードがデータページとなります。 (関連する推奨事項: MySQL チュートリアル )

クラスター化インデックスのストレージは物理的に連続ではありませんが、論理的には連続です。リーフノードは主キーの順序に従ってソートされ、次の順序で接続されます。双方向リンクリスト。ほとんどの場合、クエリオプティマイザーはクラスター化インデックスを使用する傾向があります。これは、クラスター化インデックスはリーフノードでデータを直接検索でき、データの論理的順序が定義されているため、範囲値のクエリに非常に迅速にアクセスできるためです。

クラスター化インデックスのこの機能により、インデックス構成表内のデータもインデックスの一部であることが決定されます。テーブル内のデータは B ツリーに従ってのみソートできるため、テーブルには 1 つのクラスタードインデックスのみを含めることができます。

Innodb では、クラスター化インデックスがデフォルトで主キーインデックスになります。主キーがない場合は、次のルールに従ってクラスター化インデックスを構築します。

主キーがない場合、空ではない一意のインデックス列が主キーとして使用され、このテーブルのクラスター化インデックス;
そのようなインデックスがない場合、InnoDB は暗黙的に主キーをクラスター化インデックスとして定義します。

主キーはクラスター化インデックスを使用しているため、主キーが自動インクリメントされるIDの場合、対応するデータもディスク上に隣接して格納され、書き込み性能が高くなります。 uuid などの文字列形式の場合、頻繁に挿入すると innodb が頻繁にディスクブロックを移動するため、書き込みパフォーマンスが比較的低下します。

B ツリー (マルチパスバランス検索ツリー)

innodb エンジンのインデックスが B ツリー構造を使用していることはわかっていますが、バイナリツリーなどの他のタイプのツリー構造を使用しないのはなぜでしょうか。 ?

コンピューターがデータを保存する場合、人民元の流通の最小単位がセントであるのと同様に、コンピューターには最小記憶単位があります。ファイルシステムの最小単位はブロックです。ブロックのサイズは 4K です (この値はシステムによって異なり、設定可能です)。また、InnoDB ストレージエンジンには独自の最小ストレージ単位であるページ (Page) があります。ページのサイズは 16K です (この値も構成可能です)。

ファイルシステム内のファイルのサイズはわずか 1 バイトですが、ディスク上で 4KB のスペースを占有する必要があります。同様に、innodb 内のすべてのデータファイルのサイズは、常に 16384 (16k) の整数倍になります。

mysql innodb インデックス原理の詳細な紹介 (コード例)

つまり、MySQL では、インデックスを保存するブロックノードが 16K を占有し、MySQL の各 IO 操作はシステムの先読み機能を使用して一度に 16K をロードします。このように、このノードにインデックス値を 1 つだけ配置するのは非常に無駄です。1 回の IO で取得できるインデックス値は 1 つだけなので、バイナリツリーは使用できません。

B ツリーはマルチパス検索ツリーであり、1 つのノードに n 個の値 (n = 16K / 各インデックス値のサイズ) を保持できます。
たとえば、インデックスフィールドサイズが 1Kb の場合、各ノードは理論的には 16 個のインデックス値を保存できます。この場合、バイナリツリーは IO ごとに 1 つのインデックス値のみをロードできますが、B ツリーは 16 個のインデックス値をロードできます。

B ツリー内のウェイの数は n 1 で、n は各ノードに存在する値の数です。たとえば、各ノードに 16 個の値が格納されている場合、ツリーには 17 ウェイがあります。

ここから、B ツリーノードは複数の値を格納できるため、B ツリーインデックスは特定のキー値を持つ特定の行を見つけることができないこともわかります。 B ツリーは、データ行が保存されている特定のページのみを見つけて、そのページをメモリに読み取り、メモリ内の指定されたデータを検索します。

添付: B ツリーと B ツリーの違いは、B ツリーの非リーフノードにはナビゲーション情報のみが含まれ、実際の値は含まれていないことです。すべてのリーフノードと接続されたノードは、リンクされたノードを使用して接続されます。区間の検索と横断を容易にするためのリスト。

補助インデックス

は、非クラスター化インデックスとも呼ばれます。そのリーフノードには、行レコードのすべてのデータが含まれるわけではありません。リーフノードには、キー値に加えて、インデックスが含まれます。各リーフノードの行。対応する行のクラスター化インデックスキーであるブックマークも含まれます。

次の図は、補助インデックスとクラスター化インデックスの関係を示しています (画像はインターネットからのものです。一般的な意味を見てください):

mysql innodb インデックス原理の詳細な紹介 (コード例)

補助インデックスを使用する場合データを検索するとき、innodb ストレージエンジンは補助インデックスのリーフノードを通じて主キーインデックスのみが必要な主キーを取得し、主キーインデックスを通じて完全な行レコードを検索します。

たとえば、高さ 3 の補助インデックスツリーでデータを検索する場合、指定された主キーを見つけるために補助インデックスツリーで 3 回の IO を実行する必要があります。クラスター化インデックスツリーも 3 の場合、完全な行データが配置されているページを最終的に見つけるにはクラスター化インデックスツリーを 3 回検索する必要があるため、最終データページを取得するには合計 6 回の IO アクセスが必要になります。

ジョイントインデックス、一意インデックスなど、作成されるインデックスはすべて非クラスター化インデックスです。

ジョイントインデックス

ジョイントインデックスとは、テーブル上の複数の列にインデックスを付けることを指します。結合インデックスも B ツリーですが、結合インデックス内のキー値の数が 1 ではなく 2 以上である点が異なります。

たとえば、フィールド id、年齢、および名前を持つユーザーテーブルがあります。次の 2 つの SQL が最も頻繁に使用されることがわかります:

Select * from user where age = ？ ;
Select * from user where age = ? and name = ?;

ログイン後にコピー

現時点では、年齢と名前用に 2 つの別々のインデックスを構築する必要があります。次の結合インデックスを構築するだけで済みます:

create index idx_age_name on user(age, name)

ログイン後にコピー

結合インデックスのもう 1 つの利点は、2 番目のキー値がソートされていることです。これにより、追加のインデックスが回避される場合があります。並べ替え操作。

カバリングインデックス

カバリングインデックスとは、クラスター化インデックス内のレコードをクエリせずに、クエリに必要なすべてのフィールド値を補助インデックスから取得できることを意味します。カバーリングインデックスの利点は、補助インデックスには行レコード全体のすべての情報が含まれていないため、そのサイズがクラスター化インデックスよりもはるかに小さいため、大量の IO 操作を削減できることです。

例えば、上記に結合インデックス(年齢、名前)がある場合、

select age，name from user where age=？

ログイン後にコピー

の場合、カバーインデックスを使用できます。

インデックスをカバーすることのもう 1 つの利点は、次のような統計的な問題に対してです。

select count(*) from user

ログイン後にコピー

innodb ストレージエンジンは、クラスター化インデックスをクエリして統計を実行することを選択しません。ユーザーテーブルには補助インデックスがあり、補助インデックスはクラスター化インデックスよりもはるかに小さいため、補助インデックスを選択すると IO 操作を減らすことができます。

注意事項

冗長なインデックスではなく、適切なインデックスのみを構築してください。

データが追加または削除されるたびに、B ツリーを調整する必要があるためです。複数のインデックスを作成する場合は、複数の B ツリーを調整する必要があります。ツリーの数が増え、構造が大きくなるほど、この調整には時間とリソースが多くかかります。これらの不要なインデックスを削減すると、ディスク使用量が大幅に削減される可能性があります。