mysqlインデックスの詳しい説明（概要）

上記「mysql実行プロセスの分析」では主にサーバー層でのSQL文の実行プロセスを紹介しました

それでは分析してみましょうもう一度、エンジン層での特定のステートメントの実行手順を見てみましょう。CRUD 操作はすべてインデックスに関連しています。まずインデックスを理解しましょう

index

インデックスの出現は実際には改善するためのものです データ クエリの効率は本の目次のようなものです

データ構造

一般的なデータ構造には、順序付けされたハッシュ テーブルが含まれます配列と検索ツリー

ハッシュ テーブルは、キーと値の形式でデータを格納する構造です。検索する値 (キー) を入力するだけで、対応する値を見つけることができます。それが値です。ハッシュの考え方は非常に簡単です。値を配列に入れ、ハッシュ関数を使用してキーを位置に変換し、その値を配列の対応する位置に入れます。

必然的に、複数のキー値が通過する ハッシュ関数を変換すると同じ値が現れます。この状況に対処する 1 つの方法は、リンク リストを取り出すことです。

ハッシュ テーブルのこの構造は、同等のクエリのみが存在するシナリオに適しています。

順序付けされた配列はin 等価クエリと範囲クエリのシナリオのパフォーマンスは非常に優れています

クエリの効率だけを見ると、順序付けされた配列は非常に優れています。ただし、データを更新する必要がある場合は面倒です。途中にレコードを挿入すると、後続のレコードをすべて移動する必要があります。コストがかかりすぎます。

順序付けされた配列のインデックスのみが適しています静的ストレージ エンジンの場合

二分探索ツリーの特性は次のとおりです。各ノードの左の子ノードは親ノードより小さく、親ノードは右の子ノードより小さい

もちろん、クエリの複雑さを O(log(N)) に維持するには、ツリーをバランスの取れたバイナリ ツリーに保つ必要があります。これを保証するために、更新の時間計算量も O(log(N))

バイナリ ツリーが最も効率的な検索ですが、実際には、ほとんどのデータベース ストレージはバイナリ ツリーを使用しません。その理由は、インデックスがメモリ内に存在するだけでなく、ディスクにも書き込まれるためです。

クエリで読み取るディスクの数をできるだけ少なくするには、クエリ プロセスがアクセスするデータ ブロックをできるだけ少なくする必要があります。したがって、二分木ではなく、「N 分木」を使用する必要があります。ここで、「N 分」ツリーの「N」はデータ ブロックのサイズによって異なります。

N 分ツリーは、読み取り、書き込み、およびデータ ブロックへの適応におけるパフォーマンス上の利点により、データベースで広く使用されています。ディスクのアクセス モード。エンジンはヒットします。

InnoDB のインデックス モデル

InnoDB では、テーブルは主キーの順序に基づいてインデックスの形式で保存されます。この格納方法はインデックス構成テーブルと呼ばれます。 InnoDB は B ツリー インデックス モデルを使用するため、データは B ツリーに保存されます。

各インデックスは InnoDB の B ツリーに対応します。

リーフ ノードの内容によると、インデックス タイプは、主キー インデックスと非主キー インデックスに分けられます。

主キー インデックスのリーフ ノードには、データ行全体が格納されます。 InnoDB では、主キー インデックスはクラスター化インデックスとも呼ばれます。

非主キー インデックスのリーフ ノードの内容が主キーの値です。 InnoDB では、非主キー インデックスはセカンダリ インデックスとも呼ばれます。

非主キー インデックスに基づくクエリは、もう 1 つのインデックス ツリー (テーブル リターン) をスキャンする必要があります。したがって、アプリケーション

#インデックスのメンテナンス

B ツリーで主キー クエリを使用するように最善を尽くす必要があります。インデックスの順序を維持するには、新しい値を挿入する必要があります必要なメンテナンスを行います

新しく挿入した ID 値が元の ID 値より小さい場合は、比較的面倒です。後続のデータを論理的に移動してスペースを空ける必要があります

さらに悪いことに、データ ページがいっぱいの場合は、B ツリー アルゴリズムに従って、新しいデータ ページを申請して、そこにデータを移動する必要があります。このプロセスはページ分割と呼ばれます。この場合、当然ながらパフォーマンスは低下します。

ページ分割操作は、パフォーマンスに加えて、データ ページの使用率にも影響します。当初 1 ページに配置されていたデータが 2 ページに分割され、全体のスペース使用率が約 50% 削減されました。

当然、分割や合併もあるでしょう。データが削除されたために隣接する 2 つのページの使用率が低下した場合、データ ページはマージされます。マージ プロセスは、分割プロセスの逆のプロセスと考えることができます。

自動増加主キーの挿入データ モードは、前述した増分挿入シナリオと一致しています。新しいレコードが挿入されるたびに、それは追加操作となり、他のレコードの移動やリーフ ノードの分割のトリガーは含まれません。

ビジネス ロジックを含むフィールドが主キーとして使用される場合、多くの場合、順序どおりに挿入することが容易ではないため、データの書き込みコストが比較的高くなります。

フィールドの長さが小さいほど、データの書き込みコストが比較的高くなります。主キーが小さいほど、通常のインデックスのリーフ ノードが小さくなり、通常のインデックスが占めるスペースも小さくなります

したがって、パフォーマンスとストレージ スペースの観点から、自動インクリメント多くの場合、主キーの方が合理的な選択です。

ビジネス フィールドを主キーとして直接使用するのに適したシナリオはありますか? まだいくつかあります。たとえば、一部のビジネス シナリオの要件は次のとおりです:

1. インデックスは 1 つだけです;

2. インデックスは一意のインデックスである必要があります。

これは典型的な KV シナリオです

カバーインデックス

実行されたステートメントが select ID from t の場合、ID の値を確認するだけでよく、ID の値はすでに k インデックス ツリーに存在するため、テーブルに返さずにクエリ結果を直接提供できます。 。つまり、このクエリでは、インデックス k がクエリ要件を「カバー」しています。これをカバー インデックスと呼びます。

カバー インデックスによりツリー検索の数が減り、クエリのパフォーマンスが大幅に向上するためです。したがって、カバリング インデックスの使用が一般的なパフォーマンス最適化方法です。

インデックス プッシュダウン

左端のプレフィックスの原則が満たされている場合、左端のプレフィックスを使用できます。インデックスに記録します。このとき、左端のプレフィックスと一致しない部分はどうなるのかと疑問に思うかもしれません。

MySQL 5.6 で導入されたインデックス プッシュダウンの最適化は、インデックスの走査プロセス中に最初にインデックスに含まれるフィールドを判断し、条件を満たさないレコードを直接除外し、テーブルの戻り数を減らすことができます

左端のプレフィックスの原則

インデックスの定義全体だけでなく、左端のプレフィックスが満たされている限り、インデックスを使用して検索を高速化できます

結合インデックスを作成する場合、インデックス内のフィールドの順序をどのように調整しますか?

ここでの評価基準は、インデックスの再利用可能性です。左端のプレフィックスがサポートされるため、(a, b) の結合インデックスが既に存在する場合、通常、a に別のインデックスを作成する必要はありません。したがって、最初の原則は、順序を調整することでインデックスを 1 つ少なく維持できる場合、多くの場合、この順序を優先する必要があるということです。

#プレフィックス インデックス

最も多く使用する 左プレフィックスの原則により、文字列の一部をインデックスとして定義できます。デフォルトでは、インデックスを作成するステートメントでプレフィックス長が指定されていない場合、インデックスには文字列全体が含まれます

ただし、これによってもたらされるペナルティは、インデックスが無効になるため、追加のレコード スキャンの数が増加する可能性があることです。同じです さらに比較が必要です。

プレフィックス インデックスを使用して長さを定義すると、追加のクエリ コストを追加することなくスペースを節約できます。

統計インデックスを使用できます。いくつプレフィックスを使用する期間を決定するためにさまざまな値があり、それによってスキャンの数が削減されます

カバーインデックスに対するプレフィックス インデックスの影響

プレフィックスを使用するインデックス インデックスをカバーしないと、クエリのパフォーマンスが最適化されます。これは、プレフィックス インデックスを使用するかどうかを選択するときに考慮する必要がある要素でもあります。

逆順ストレージとハッシュ ストレージ

電子メールなどのフィールドの場合は、プレフィックス インデックスを使用するとうまく機能する場合があります。しかし、プレフィックスの区別が十分ではない状況に遭遇した場合はどうすればよいでしょうか?

最初の方法は、逆順ストレージを使用することです。 ID 番号を保存する場合は、上下を逆にして保存してください。

2 番目の方法は、ハッシュ フィールドを使用する方法です。 ID カードの検証コードを保存するためにテーブルに別の整数フィールドを作成し、このフィールドにインデックスを作成できます。

無料学習ビデオ チュートリアルの推奨事項:

mysql ビデオ チュートリアル

以上がmysqlインデックスの詳しい説明（概要）の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。