MySQL テーブルバッククエリとインデックスカバレッジの違いは何ですか?-mysql チュートリアル-php.cn

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

王林

リリース： 2023-06-03 14:22:23

転載

1087 人が閲覧しました

テーブルクエリに戻る

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

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

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

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

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

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

インデックスカバレッジ

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

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

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

select name, age from t where name = &#39;lcc&#39;;

ログイン後にコピー

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

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

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

select name  from t where name = &#39;lcc&#39;;

ログイン後にコピー

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 &#39;j%&#39; and age=99;

ログイン後にコピー

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

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

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

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

MySQL のサーバー層は、まずストレージエンジンを呼び出して、最初のユーザー名は j で始まります。
レコードを見つけた後、ストレージエンジンは急いでテーブルに戻りませんが、このレコードの年齢が 99 に等しいかどうかを判断し続けます。年齢 = 99 の場合age が 99 に等しくない場合、テーブルは返されず、次のレコードが直接読み取られます。

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