MySQLカバーインデックスの詳しい説明

コンセプト

インデックスにクエリ要件を満たすすべてのデータが含まれている場合、それはカバーリング インデックスと呼ばれ、テーブルに戻る必要がないことを意味します。

判断基準

explain を使用する場合は、出力の追加列で判断できます。インデックス カバーリング クエリの場合は、using index と表示されます。MySQL クエリ オプティマイザは、クエリを実行する前に、インデックス カバーリング クエリがあるかどうかを判断します。

知らせ

1. カバーリング インデックスはどのインデックス タイプにも適用できません。インデックスには列の値を格納する必要があります。

2. ハッシュインデックスとフルテキストインデックスは値を保存しないため、MySQLではB-TREEのみを使用できます。

3. ストレージエンジンによってカバーインデックスの実装が異なる

4. すべてのストレージエンジンがサポートしているわけではない

5. カバーリング インデックスを使用する場合は、必要な列を抽出するために SELECT リストの値に注意する必要があります。SELECT * は使用できません。すべてのフィールドをまとめてインデックスすると、インデックス ファイルが大きくなりすぎて、クエリのパフォーマンスが低下するためです。カバーリング インデックスを使用するためだけにこれを行うことはできません。

インデックスにクエリする必要があるすべてのフィールドの値が含まれている（またはカバーしている）場合、そのインデックスは「カバーリング インデックス」と呼ばれます。つまり、テーブルに戻らずにインデックスをスキャンするだけで済みます。

テーブルに戻らずにインデックスのみをスキャンする利点:

1. インデックス エントリは通常、データ行のサイズよりもはるかに小さく、インデックスのみを読み取る必要があるため、MySQL ではデータ アクセスの量が大幅に削減されます。

2. インデックスは列の値の順序で保存されるため、IO 集約型の範囲検索では、ディスクから各データ行をランダムに読み取る場合よりも IO が大幅に少なくなります。

3. MyISAMなどの一部のストレージエンジンは、インデックスをメモリにキャッシュするだけで、データのキャッシュはオペレーティングシステムに依存しているため、データにアクセスするにはシステムコールが必要です。

4. Innodb のクラスター化インデックスとカバーリング インデックスは、Innodb テーブルに特に役立ちます。 (InnoDBのセカンダリインデックスは、行の主キー値をリーフノードに格納するため、セカンダリ主キーでクエリをカバーできる場合は、主キーインデックスのセカンダリクエリを回避できます)

カバーリング インデックスはインデックス列の値を格納する必要がありますが、ハッシュ インデックス、空間インデックス、およびフルテキスト インデックスはインデックス列の値を格納しないため、MySQL ではカバーリング インデックスとして B ツリー インデックスのみを使用できます。

インデックス カバレッジ クエリが開始されると、使用しているインデックスの情報が explain の追加列に表示されます。

カバーインデックスの落とし穴: MySQL クエリ オプティマイザは、クエリを実行する前に、そのクエリをカバーできるインデックスがあるかどうかを判断します。インデックスが where 条件のフィールドをカバーしているが、クエリ全体に含まれるフィールドをカバーしていないと仮定すると、MySQL 5.5 以前のバージョンでは、この行は不要で、最終的にはフィルター処理されるにもかかわらず、テーブルに戻ってデータ行を取得します。

上の図に示すように、カバレッジ クエリは次の理由により使用できません。

1. このインデックスをカバーできるインデックスはありません。クエリはテーブルからすべての列を選択し、すべての列をカバーするインデックスが存在しないためです。

2.MySQL はインデックスに対して LIK 操作を実行できません。 MySQL はインデックス内の左端のプレフィックス一致で LIKE 比較を実行できますが、LIKE クエリがワイルドカードで始まる場合、ストレージ エンジンは比較一致を実行できません。この場合、MySQLは比較のためにインデックス値ではなくデータ行の値のみを抽出できます。

最適化された SQL: インデックス (アーティスト、タイトル、prod_id) を追加し、遅延関連付け (列への遅延アクセス) を使用します。

注: クエリの最初の段階では、カバーリング インデックスを使用して、from 句のサブクエリで一致する prod_id を見つけ、次に外側のレイヤーをクエリして、prod_id 値に基づいて必要なすべての値を一致させて取得することができます。

5.5 では、API 設計により、MySQL はフィルタリング条件をストレージ エンジン レイヤーに渡すことができません (ストレージ エンジンからサーバー レイヤーにデータをプルし、条件に従ってフィルタリングします)。5.6 以降では、ICP 機能によりクエリ実行方法が改善されています。

MySQL がソートにインデックスを使用できない場合、独自のソート アルゴリズム (クイック ソート アルゴリズム) を使用してメモリ (ソート バッファ) 内のデータをソートします。メモリに収まらない場合は、ディスク上のデータをブロックに分割し、各データ ブロックをソートしてから、各ブロックを順序付けられた結果セットにマージします (実際には外部ソート)。

ファイルソートの場合、MySQLには2つのソートアルゴリズムがある。

1. 2つのパス

実装方法は、まずソートするフィールドと、関連する行データを直接見つけることができるポインタ情報を取得し、次に設定されたメモリ（パラメータ sort_buffer_size で設定）内でソートします。ソートが完了したら、行ポインタ情報を通じて必要な列を再度取得します。

注: このアルゴリズムは 4.1 より前に使用されています。データに 2 回アクセスする必要があります。特に、2 回目の読み取り操作では、多数のランダム I/O 操作が発生します。一方、メモリオーバーヘッドは小さい

2. シングルパスアルゴリズム

このアルゴリズムは、必要なすべての列を一度に取り出し、メモリ内で並べ替えて、結果を直接出力します。注: このアルゴリズムは、MySQL 4.1 以降で使用されています。 I/O 操作の回数が減り、効率が向上しますが、大きなメモリ オーバーヘッドも発生します。不要な列を削除すると、ソート処理に必要なメモリが大幅に浪費されます。 MySQL 4.1 以降のバージョンでは、max_length_for_sort_data パラメータを設定することで、MySQL が最初のソート アルゴリズムを選択するか、2 番目のソート アルゴリズムを選択するかを制御できます。取得されたすべての大きなフィールドの合計サイズが max_length_for_sort_data 設定より大きい場合、MySQL は最初のソート アルゴリズムを使用することを選択します。それ以外の場合は、2 番目のソート アルゴリズムを選択します。ソートのパフォーマンスを最大限に向上させるには、当然のことながら 2 番目のソート アルゴリズムを使用することをお勧めします。そのため、クエリでは必要な列のみを取得することが非常に重要です。

結合操作をソートするときに、ORDER BY が最初のテーブルの列のみを参照する場合、MySQL はテーブルに対してファイルソート操作を実行してから結合を実行します。この場合、EXPLAIN は「Using filesort」を出力します。それ以外の場合、MySQL はクエリ結果セット用に一時テーブルを生成し、結合が完了した後にファイルソート操作を実行する必要があります。この場合、EXPLAIN は「Using temporary; Using filesort」を出力します。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。