Mysql の主キー インデックスと非主キー インデックスの違いについて簡単に説明します。

インデックスとは何か

MySql インデックスの公式定義: インデックスは、MySql がデータを効率的に取得するのに役立つデータ構造です。インデックスの目的は、辞書と同様に、クエリの効率を向上させることです。実際、インデックスは、主キーとインデックス フィールドを格納し、エンティティ テーブルのレコードを指すテーブルでもあります。インデックス列もスペースを占有します。

主キーインデックスと通常のインデックスの違い

1. 主キー インデックスはデータをインデックスしますが、通常のインデックスは主キー ID 値をインデックスします (これは InnoDB の場合ですが、myisam の場合は主キー インデックスと通常のインデックスに違いはなく、どちらもデータを直接インデックスします)
2. where id=xでクエリを実行する場合、対応するデータを取得するために主キーインデックスを一度スキャンするだけで済みます。ただし、通常のインデックスでクエリを実行する場合は、最初に通常のインデックスをスキャンして主キー値を取得し、次に主キーインデックスを再度スキャンして必要なデータを取得します。このプロセスはテーブルバックと呼ばれます。

インデックスにはどのデータ構造が使用されますか?

MySQL には、ハッシュ インデックスと B+​​ ツリー インデックスという 2 つの一般的な構造があります。InnoDB エンジンを使用しており、デフォルトは B+ ツリーです。

InnoDB は B+ ツリー インデックス モデルを使用しますが、なぜ B+ ツリーを使用するのでしょうか?ハッシュインデックスと比較した利点と欠点は何ですか?

B+ツリーインデックスとハッシュインデックスの違い ハッシュインデックスは等価値クエリに適していますが、範囲クエリは実行できません ハッシュインデックスはインデックスを使用してソートを完了できません ハッシュインデックスは、複数列のジョイントインデックスの左端一致ルールをサポートしていません 重複するキー値が多数ある場合、ハッシュ衝突問題のため、ハッシュインデックスの効率は非常に低くなります

B+ ツリーのリーフ ノードには何を格納できますか?

InnoDB では、データの行全体を格納するインデックス B+ ツリーのリーフ ノードが主キー インデックス (クラスター化インデックスとも呼ばれます) です。主キー値を格納するインデックス B+ ツリーのリーフ ノードは、非主キー インデックス (非クラスター化インデックスとも呼ばれます) です。

データをクエリするときに、クラスター化インデックスと非クラスター化インデックスの間に違いはありますか?

クラスター化インデックス クエリは、主キー インデックス ツリーのリーフ ノードがクエリ対象のデータ行全体であるため、高速です。非主キー インデックスのリーフ ノードは主キーの値です。主キーの値を見つけたら、主キーの値を使用して再度クエリを実行する必要があります。このプロセスはテーブル リターンと呼ばれます。
非主キー インデックスは複数回クエリされますか?
カバー インデックスも 1 回だけクエリできます。カバー インデックスとは、クエリ ステートメントの実行をデータ テーブルから読み取らずに、インデックスからのみ取得できることを意味します。インデックスカバレッジが達成されているとも言えます。 クエリ ステートメントがカバー インデックスの条件を満たす場合、MySQL はインデックスを使用してクエリに必要なデータを返すだけでよいため、インデックスを見つけた後にテーブルに戻る必要がなくなり、I/O が削減され、効率が向上します。 たとえば、covering_index_sample テーブルには共通インデックス idx_key1_key2(key1, key2) があります。 SQL ステートメント「select key2 from coverage_index_sample where key1 = 'keytest';」を使用すると、テーブルに戻らずにカバーリング インデックスを介してクエリを実行できます。

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

MySQL 5.6 では、インデックス プッシュダウン最適化が導入されました。これはデフォルトで有効になっており、SET optimizer_switch = 'index_condition_pushdown=off'; を使用して無効にすることができます。公式ドキュメントに記載されている例と説明は次のとおりです。peopleテーブルでは、(zipcode、lastname、firstname)がインデックスを構成します。

zipcode='95054' かつ lastname LIKE '%etrunia%' かつ address LIKE '%Main Street%';

インデックス プッシュダウン テクノロジが使用されていない場合、MySQL は zipcode='95054' を通じてストレージ エンジンから対応するデータを照会し、それを MySQL サーバーに返します。次に、MySQL サーバーは、lastname LIKE '%etrunia%' と address LIKE '%Main Street%' に基づいて、データが条件を満たしているかどうかを判断します。 インデックス プッシュダウン テクノロジを使用すると、MYSQL は最初に zipcode='95054' に一致するインデックスを返し、次に lastname LIKE '%etrunia%' に基づいて条件を満たすインデックスをフィルターして MySQL サーバーに返します。次に、MySQL サーバーは、データが address LIKE '%Main Street%' に基づいて条件を満たすかどうかを判断するため、MySQL サーバーに返されるインデックスの数が削減されます。インデックス プッシュダウンの最適化により、類似条件クエリがある場合にテーブル返送数を削減できます。

クエリオプティマイザー

SQL ステートメント クエリには、複数の実行プランが存在する場合があります。最終的にどのプランが選択されるかについては、実行コストが最も低いプランを選択するためにオプティマイザによって選択される必要があります。 単一テーブルのクエリ ステートメントが実際に実行される前に、MySQL クエリ オプティマイザーはステートメントを実行するためのすべての可能なソリューションを検索し、比較後にコストが最も低いソリューションを検索します。この最も低コストのソリューションは、いわゆる実行プランです。 最適化のプロセスは次のとおりです。1. 検索条件に基づいてすべての可能なインデックスを検索します。2. フルテーブルスキャンのコストを計算します。3. 異なるインデックスを使用してクエリを実行するコストを計算します。4. さまざまな実行プランのコストを比較し、コストが最も低いプランを見つけます。

インデックスに関する質問

テーブル `geek` を作成します (
  `a` int(11) NULLではない、
  `b` int(11) NULLではない、
  `c` int(11) NULLではない、
  `d` int(11) NULLではない、
  主キー (`a`,`b`)、
  キー `c` (`c`)、
  キー `ca` (`c`,`a`)、
  キー `cb` (`c`,`b`)
)ENGINE=InnoDB;

歴史的な理由により、このテーブルでは a と b を共同主キーとして必要とします。
つまり、フィールド c のみにインデックスを作成すると、すでに 3 つのフィールドが含まれることになります。なぜ 2 つのインデックス「ca」と「cb」を作成する必要があるのでしょうか。

c=N で geek から * を選択し、制限 1 で順序付けします。
c=N で geek から * を選択し、b で順序を制限し、1 にします。

質問: この同僚の説明は正しいでしょうか? どのインデックスが不要で、削除できるのでしょうか?

答え：

（１）主キーaとbのクラスター化インデックスの構成順序は、order by a,bと同等です。つまり、最初にaでソートし、次にbでソートしますが、cは順序が正しくありません。

–a--|–b--|–c--|–d--
1 2 3日
1 3 2日
1 4 3日
2 1 3日
2 2 2日
2 3 4日

（２）インデックスcaの構成は、まずcでソートし、次にaでソートし、同時に主キーを記録することである。

–c--|–a--|–主キー部分 b-- (注意: ここでは ab ではなく、b のみです)
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3

上記のインデックス ca のデータは、インデックス c のものとまったく同じです。

（３）インデックスcbの構成は、まずcでソートし、次にbでソートし、同時に主キーを記録する。

–c--|–b--|–主キー部分 a-- (上記と同じ)
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2

したがって、結論としては、ca を削除して cb を保持できることになります。

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