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

この記事では、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 では、インデックスを保存するブロック ノードが 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 ツリーの非リーフ ノードにはナビゲーション情報のみが含まれ、実際の値は含まれていないことです。すべてのリーフ ノードと接続されたノードは、リンクされたノードを使用して接続されます。区間の検索と横断を容易にするためのリスト。

補助インデックス

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

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

補助インデックスを使用する場合 データを検索するとき、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 ツリーを調整する必要があります。ツリーの数が増え、構造が大きくなるほど、この調整には時間とリソースが多くかかります。これらの不要なインデックスを削減すると、ディスク使用量が大幅に削減される可能性があります。

• インデックス列のデータ長は、できるだけ短くすることができます。

インデックスのデータ長が小さいほど、各ブロックに格納されるインデックスの数が多くなり、1回のIOでより多くの値を取得できます。

• 一致する列のプレフィックスは、インデックス内で使用できます (9999% など)、�99% など、�99 などはインデックスを使用できません;
• in および の Where 条件or can use theindex , not in and 操作ではインデックスを使用できません;

B ツリーに面した、in または がない場合、エンジンは認識しませんどのノードから開始するか。

• 範囲値の一致、順序付けによるインデックス付けも可能;
• 指定された列クエリをより頻繁に使用し、考えられるデータ列のみを返し、select * を使用以下;

無駄なフィールドをクエリする必要はなく、* を使用しない場合でも、カバー インデックスにヒットする可能性があります。ジョイント インデックス内のインデックスの左端の列から検索を開始しないでください。インデックスを使用することはできません。

左端の一致原則;

ジョイント内でインデックスを使用すると、先頭の左端の列を正確に一致させ、別の列を範囲一致させるために使用できます。

結合インデックスでは、クエリ内の特定の列に範囲クエリがある場合、右側のすべての列が一致します。そのうちのインデックスを作成できません

以上がmysql innodb インデックス原理の詳細な紹介 (コード例)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。