MySQL インデックス失敗の原理

1. インデックス失敗の理由

まず、検索でインデックスの秩序性を活用できない状況を見てみましょう。

テーブル テストに 4 つのフィールド a、b、c、d があり、c が主キーであるとします。

フィールド a と b にジョイント インデックス (a,b) を作成しますCREATE index idx_a_b on test(a,b) test(a,b); B+ tree join index.JPG

得られるルールは次のようになります:最初にフィールド a を小さいものから大きいものの順に並べ替え、フィールド a が等しい場合は、フィールド b を小さいものから大きいものの順に並べ替えます。

インデックスが無効になるかどうか、および無効になる理由を判断するには、次の状況を分析します。

条件にはbフィールドのみが含まれます

b=2 の場合、テストから * を選択します。

インデックス失敗:

当然ですが、進むときに全文がスキャンされ、インデックスは使用されません。フィールド b のインデックス (2、4、1、3、4、5) のみを参照するため、インデックスの順序性を利用してデータをすばやく見つけることはできません。

フィールド a の範囲を照会します。

a>1かつb=2の場合、テストから*を選択します。

インデックス失敗:

インデックスは完全に無効ではないことがわかりますが、インデックスは最初に a の位置を特定するために使用されます。ここでの key_len は 4 であり、結合インデックスの key_len は 8 であるためです。

フィールド a の等しい値とフィールド b の範囲を照会します。

インデックス失敗:

using index 、 key_lenが 8 であることがわかります。つまり、両方のフィールドのインデックスが使用されています。これは、結合インデックス配置のルールにも対応しています。つまり、a フィールドが同じ場合、b フィールドは順番に配置されます。

上記の状況をまとめると、左端のプレフィックス一致の原則に従わないとインデックスが失敗することになります。

最も左に一致するプレフィックスにより、インデックスのソート順序を活用できます。等号クエリを前に置き、範囲クエリを後ろに配置することで、[プレフィックス フィールドが等しい場合、後続のインデックス フィールドが順序付けされる] という機能を活用できます。これは、特別な意味での最も左のプレフィックス一致の原則です。

2. インデックスの秩序が崩れる状況を見てみましょう。

- インデックスフィールドに対して関数操作を実行する

y=f(x),などのインデックス フィールドに対して関数操作を実行しても、取得された y の値が依然として正しい順序になっていることは保証されません。この場合、オプティマイザはツリー検索機能を放棄します。ただし、インデックス ツリーが主キー インデックスよりもはるかに小さいことがわかった場合、オプティマイザがこのインデックスをスキャンすることを選択する可能性は排除されません。

- 暗黙的な型変換

MySQL では、文字列と数値を比較する場合、文字列は数値に変換されます。暗黙的な型変換の本質は、インデックス フィールドで CAST() 関数を使用することです。原理は上記と同じです。

- 暗黙的な文字エンコード変換

文字列エンコーディング変換の本質はCONVERT()関数を使用することです。

3. まとめ

インデックスが失敗する理由は、オプティマイザがインデックスの秩序性を活用できないと判断することです。したがって、インデックスを使用するときは、左端のプレフィックス一致の原則を満たすようにし、範囲クエリを最後に置き、 %likeや%like%などのあいまいクエリを使用しないようにする必要があります。これは、インデックスの秩序性を最大限に活用するためです。ただし、場合によっては、オプティマイザがインデックスツリーの検索機能を放棄し、インデックスのスキャンを選択することもあります。

これで、MySQL インデックス無効化の原理に関するこの記事は終了です。MySQL インデックス無効化に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。