MySQL テーブル バック クエリとインデックス カバレッジの違いは何ですか?

テーブル クエリに戻る

InnoDB インデックスは 2 つのカテゴリに分類されます。1 つはクラスター化インデックス (クラスター化インデックス)、もう 1 つは非クラスター化インデックス (セカンダリ インデックス)です。

クラスター化インデックス (クラスタ化インデックス) インデックス): リーフ ノードには、データの行全体が格納されます。インデックスが見つかると、データも見つかります。インデックスはデータです。テーブル内の行の物理的な順序は、論理的な順序と同じです (インデックス) キー値の順序。テーブルにはクラスター化インデックスを 1 つだけ含めることができます。インデックス（ディレクトリ）は一方向にしかソートできないためです。

非クラスター化インデックス (通常のインデックス、非クラスター化インデックス、セカンダリ インデックス): 非クラスター化インデックスの btree リーフ ノードには、データの現在の行の PK (主キー) が格納されます。たとえば、MYISAM はまず key_buffer を通じてインデックスをメモリにキャッシュします。データにアクセスする必要があるとき (インデックスを通じてアクセスされるデータ)、インデックスはメモリ内で直接検索され、その後、ディスク上の対応するデータがインデックスを通じて見つかります。 . これがインデックスがキーバッファにヒットしない理由であり、速度が遅い原因です。

非主キー インデックス構造のリーフ ノードに主キー値が格納されるのはなぜですか?

行の移動やデータ ページの分割が発生した場合のセカンダリ インデックスのメンテナンス作業を軽減します (データを更新する必要がある場合、セカンダリ インデックスを変更する必要はなく、クラスタード インデックスのみを変更する必要があります) 、1 つのテーブルのみが必要です。クラスタ化インデックスが存在する可能性があり、他のテーブルはセカンダリ インデックスであるため、クラスタ化インデックスを変更するだけでよく、セカンダリ インデックスを再構築する必要はありません)

When using a非クラスター化インデックスでは、特定のデータを取得するには、主キーによってクラスター化インデックスに戻り、データをクエリする必要があります。次に、テーブル クエリをコールバックします。インデックス ツリーは 2 回スキャンされました。したがって、効率は比較的低いです。

インデックス カバレッジ

インデックス カバレッジは、テーブル クエリのソリューションです。名前が示すように、クエリのすべての列は、使用されるインデックス列によってカバーされます (単一列インデックスまたは結合インデックス、通常は結合インデックスのいずれかになります。単一列インデックスですべての列をカバーすることは困難です)クエリの)。

インデックスにはクエリ対象のフィールドの値が既に含まれているため、クエリ時にインデックス内のフィールド値を直接返すだけで十分です。テーブルを再度クエリする必要はなく、二次的な再クエリを回避できます。主キーインデックスの検査、クエリの効率も向上します。

id ​​はクラスター化インデックス、name は非クラスター化インデックスです:

select name, age from t where name = 'lcc';

テーブルにクエリを戻す必要があります

インデックス カバレッジ:

SQL フィールドのクエリ名のみ。このようにして、名前のインデックスはすべてのクエリ列をカバーします。

select name  from t where name = 'lcc';

name のインデックスを結合インデックス (name, age) に変更し、select name, age from t where name = 'lcc' を実行します。これはすべてのクエリ列も対象となります。
カバリング インデックスはインデックス列の値を格納する必要がありますが、ハッシュ インデックス、空間インデックス、およびフルテキスト インデックスはインデックス列の値を格納しないため、B ツリー インデックスを使用するデータのみがカバリング インデックスとして使用できます。

インデックス カバレッジ クエリを実行すると、Explain (実行プラン) の Extra 列に [Using Index] 情報が表示されます。

インデックス カバーリングの利点

• #インデックスのカバーには読み取りだけが必要なため、通常、インデックス エントリはデータ行のサイズよりもはるかに小さくなります。インデックスを使用することで、データへのアクセス数が大幅に削減されます。

• インデックスは列値の順序で格納され、IO 集中型の範囲検索は、ディスクからデータの各行をランダムに読み取る IO よりもはるかに小さくなります。

• MyISAM などの一部のストレージ エンジンはインデックスをメモリにキャッシュするだけであり、データはオペレーティング システムのキャッシュに依存します。そのため、データにアクセスするにはシステム コールが必要です。カバリング インデックスを使用すると、これを回避できます。 。

• InnoDB エンジンのデータベース テーブルの場合、InnoDB のクラスター化インデックスがあるため、インデックスをカバーすることは非常に実用的です。 InnoDB のセカンダリ インデックスはリーフ ノードの行の主キー値を保存するため、セカンダリ インデックスでクエリをカバーできれば、主キー インデックスのセカンダリ クエリが回避されます。

インデックス カバレッジを使用して SQL を最適化するのに適したシナリオは次のとおりです。

• レコードの行全体をクエリする必要がない場合。

• 全テーブル数クエリの最適化;

• ページング クエリを制限;

どのような状況でインデックスを作成しないでください

• テーブル レコードが少なすぎます

• 頻繁に追加、削除、または変更されるテーブルまたはフィールド (例:ユーザー残高)

• #Where 条件で使用されていないフィールドのインデックスを作成しないでください

##フィルタリングはインデックス作成には適していません (例:性別として)
インデックス プッシュダウン

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

結合インデックスの作成:

KEY `username` (`name`,`age`) )

実行:

select * from user2 where name like 'j%' and age=99;

上記のクエリ SQLインデックスの左端のプレフィックスの原則に準拠しているため、ユーザー名インデックスが使用されます。

5.5 での上記の SQL の実行フローは次のとおりです。

まず、 MySQL のサーバー層はストレージ エンジンを呼び出して、j で始まる最初のユーザー名を取得します。
ストレージ エンジンが username=‘j’ の最初のレコードを見つけた後、主キー ID が B ツリーのリーフ ノードに保存されます。このとき、主キーはテーブルの戻り操作によって削除され、レコードの完全なデータがインデックス内で検出され、サーバー層に返されます。

• サーバー層はデータを取得した後、レコードの年齢が 99 であるかどうかを判断します。年齢 = 99 の場合、レコードをクライアントに返します。年齢!=99 の場合、破棄します。記録。

5.6 での上記の SQL の実行プロセスは次のとおりです。

• MySQL のサーバー層は、まずストレージ エンジンを呼び出して、最初のユーザー名は j で始まります。

• レコードを見つけた後、ストレージ エンジンは急いでテーブルに戻りませんが、このレコードの年齢が 99 に等しいかどうかを判断し続けます。年齢 = 99 の場合age が 99 に等しくない場合、テーブルは返されず、次のレコードが直接読み取られます。

以上がMySQL テーブル バック クエリとインデックス カバレッジの違いは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。