MySQLインデックスはどのように機能しますか?

2010年08月26日に質問されました。  ·  閲覧回数 200k回  ·  ソース

good_evening picture
2010年08月26日

MySQLインデックスがどのように機能するか、より具体的には、テーブル全体をスキャンせずに要求されたデータを返す方法に本当に興味がありますか?

話題から外れているとは思いますが、これを詳しく説明してくれる人がいたら、とてもありがたいです。

回答

Piskvor left the building picture
2010年08月26日
519

基本的に、テーブルのインデックスは本のインデックスのように機能します(名前の由来はここにあります)。

データベースに関する本があり、たとえばストレージに関する情報を見つけたいとします。 インデックスがないと(目次などの他の補助がないと仮定して)、トピック( full table scan )が見つかるまで、ページを1つずつ確認する必要があります。 一方、インデックスにはキーワードのリストがあるので、インデックスを調べて、 storageが113〜120、231、および354ページに記載されていることを確認します。次に、検索せずにこれらのページに直接移動できます。 (これはインデックスを使用した検索で、やや高速です)。

もちろん、インデックスがどれほど役立つかは、多くのことに依存します-上記の直喩を使用したいくつかの例:

  • データベースに関する本を持っていて、「データベース」という単語にインデックスを付けた場合、1-59、61-290、および292〜400ページに記載されていることがわかります。このような場合、インデックスはあまり役に立ちません。ページを1つずつ確認する方が速い(データベースでは、これは「選択性が低い」)。
  • 10ページの本の場合、インデックスを作成することは意味がありません。10ページの本の前に5ページのインデックスが付いている場合がありますが、これはばかげています。10ページをスキャンして、それで完了します。 。
  • インデックスも有用である必要があります。たとえば、ページごとの文字「L」の頻度など、インデックスを作成する意味はありません。
clarete picture
2013年01月11日
264

最初に知っておく必要があるのは、インデックスは、探している結果を得るためにテーブル全体をスキャンすることを回避する方法であるということです。

インデックスにはさまざまな種類があり、ストレージレイヤーに実装されているため、インデックス間に標準はなく、使用しているストレージエンジンによっても異なります。

InnoDBとB + Treeインデックス

InnoDBの場合、最も一般的なインデックスタイプはB + Treeベースのインデックスであり、要素を並べ替えられた順序で格納します。 また、インデックス付きの値を取得するために実際のテーブルにアクセスする必要がないため、クエリの戻りがはるかに速くなります。

このインデックスタイプの「問題」は、インデックスを使用するために左端の値をクエリする必要があることです。 したがって、インデックスにlast_nameとfirst_nameの2つの列がある場合、これらのフィールドにクエリを実行する順序は非常に重要です。

したがって、次の表が与えられます。

CREATE TABLE person (
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    INDEX (last_name, first_name)
);

このクエリはインデックスを利用します:

SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"

しかし、次のものはしません

SELECT last_name, first_name FROM person WHERE first_name = "Constantine"

最初にfirst_name列をクエリしていて、それがインデックスの左端の列ではないためです。

この最後の例はさらに悪いです:

SELECT last_name, first_name FROM person WHERE first_name LIKE "%Constantine"

これは、インデックスの右端のフィールドの右端の部分を比較しているためです。

ハッシュインデックス

これは、残念ながら、メモリバックエンドのみがサポートする別のインデックスタイプです。 これは非常に高速ですが、完全なルックアップにのみ役立ちます。つまり、 ><LIKEなどの操作には使用できません。

これはメモリバックエンドでのみ機能するため、おそらくあまり使用しないでしょう。 私が今考えることができる主なケースは、別の選択からの結果のセットを使用してメモリに一時テーブルを作成し、ハッシュインデックスを使用してこの一時テーブルで他の多くの選択を実行する場合です。

大きなVARCHARフィールドがある場合は、別の列を作成し、その列に大きな値のハッシュを保存することで、Bツリーを使用するときにハッシュインデックスの使用を「エミュレート」できます。 フィールドにURLを格納していて、値が非常に大きいとします。 url_hashという整数フィールドを作成し、 CRC32などのハッシュ関数またはその他のハッシュ関数を使用してURLを挿入することもできます。 そして、この値を照会する必要がある場合は、次のようにすることができます。

SELECT url FROM url_table WHERE url_hash=CRC32("http://gnu.org");

上記の例の問題は、 CRC32関数が非常に小さなハッシュを生成するため、ハッシュされた値で多くの衝突が発生することです。 正確な値が必要な場合は、次の手順でこの問題を修正できます。

SELECT url FROM url_table 
WHERE url_hash=CRC32("http://gnu.org") AND url="http://gnu.org";

衝突数が多い場合でも、ハッシュを繰り返す価値はあります。これは、繰り返されるハッシュに対して2番目の比較(文字列1)のみを実行するためです。

残念ながら、この手法を使用すると、 urlフィールドを比較するためにテーブルをヒットする必要があります。

要約

あなたが最適化について話したいと思うたびにあなたが考慮するかもしれないいくつかの事実:

  1. 整数の比較は、文字列の比較よりもはるかに高速です。 InnoDBのハッシュインデックスのエミュレーションに関する例で説明できます。

  2. たぶん、プロセスにステップを追加すると、遅くなるのではなく、速くなります。 SELECTを2つのステップに分割し、最初のステップで新しく作成されたメモリ内テーブルに値を格納してから、この2番目のテーブルでより重いクエリを実行することで最適化できるという事実によって説明できます。 。

MySQLには他のインデックスもありますが、B + Treeのものがこれまでで最も使用されており、ハッシュのものは知っておくとよいと思いますが、他のインデックスはMySQLのドキュメントにあり

「HighPerformanceMySQL」の本を読むことを強くお勧めします。上記の答えは、間違いなくインデックスに関する章に基づいています。

Joshua picture
2010年08月26日
46

基本的に、インデックスは、順番に並べ替えられたすべてのキーのマップです。 リストを順番に並べると、すべてのキーをチェックする代わりに、次のようなことができます。

1:リストの真ん中に移動します-私が探しているものよりも高いですか、それとも低いですか?

2:高い場合は中央と下の中間点に移動し、低い場合は中央と上部に移動します

3:高いですか、低いですか? 再び中間点にジャンプするなど。

そのロジックを使用すると、すべての項目をチェックする代わりに、約7ステップでソート済みリスト内の要素を見つけることができます。

明らかに複雑さがありますが、それはあなたに基本的な考えを与えます。

Abe Miessler picture
2010年08月26日
4

このリンクを見てください: http

それらがどのように機能するかは、1つのSO投稿でカバーするには主題が広すぎます。

ここでは、私が見てきたインデックスの最高の説明の一つです。 残念ながら、これはSQL Server用であり、MySQL用ではありません。 2つがどれほど似ているかわかりません...

shahirnana picture
2017年04月19日
3

インデックス作成の詳細については、このビデオをご覧ください

単純なインデックス作成テーブルに一意のインデックスを作成できます。 一意のインデックスは、2つの行が同じインデックス値を持つことができないことを意味します。 テーブルにインデックスを作成するための構文は次のとおりです

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

1つ以上の列を使用してインデックスを作成できます。 たとえば、tutorial_authorを使用してtutorials_tblインデックスを作成できます。

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

テーブルに簡単なインデックスを作成できます。 クエリからUNIQUEキーワードを省略するだけで、単純なインデックスが作成されます。 単純なインデックスでは、テーブル内の値を重複させることができます。

列の値に降順でインデックスを付ける場合は、列名の後に予約語DESCを追加できます。

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)
sendon1982 picture
2019年08月24日
2

MySQL InnoDBには、2種類のインデックスがあります。

  1. クラスター化インデックスと呼ばれる主キー。 インデックスキーワードは、実際のレコードデータとともにB + Treeリーフノードに保存されます。

  2. 非クラスター化インデックスである2次キー。 これらのインデックスは、B + Treeリーフノードに、プライマリキーのキーワードと独自のインデックスキーワードのみを格納します。 したがって、セカンダリインデックスから検索する場合、最初にプライマリキーインデックスキーワードを見つけ、プライマリキーB + Treeをスキャンして実際のデータレコードを見つけます。 これにより、プライマリインデックスの検索に比べてセカンダリインデックスの速度が低下します。 ただし、 select列がすべてセカンダリインデックスにある場合は、プライマリインデックスB + Treeを再度検索する必要はありません。 これはカバーリングインデックスと呼ばれます。

WoodrowShigeru picture
2019年07月24日
1

2セント追加したいです。 私はデータベースの専門家にはほど遠いですが、最近このトピックについて少し読みました。 私がELI5を試してみるのに十分です。 だから、ここに素人の説明があります。


インデックスはテーブルのミニミラーのようなものであり、連想配列のようなものだと私は理解しています。 一致するキーを入力すると、1つの「コマンド」でその行にジャンプできます。

ただし、そのインデックス/配列がない場合、クエリインタープリターはforループを使用してすべての行を調べ、一致するかどうかを確認する必要があります(全表スキャン)。

インデックスを持つことには、コンテンツをより速く検索するという「利点」と引き換えに、(そのミニミラーの)追加のストレージの「欠点」があります。

(データベースエンジンに応じて)プライマリキー、外部キー、または一意キーを作成すると、それぞれのインデックスも自動的に設定されることに注意してください。 同じ原則が、基本的にこれらのキーが機能する理由と方法です。

Anurag Sharma picture
2019年08月21日
1

回答のリストに視覚的表現を追加します。enter image description here

MySQLは、追加の間接層を使用します。セカンダリインデックスレコードはプライマリインデックスレコードを指し、プライマリインデックス自体がディスク上の行の場所を保持します。 行オフセットが変更された場合は、プライマリインデックスのみを更新する必要があります。

警告:ディスクデータ構造は図ではフラットに見えますが、実際にはB +ツリーです。

出典:リンク

saint_sharan picture
2020年09月28日
0

あなたが本、おそらく小説、読むべきものがたくさんある、したがって言葉がたくさんある厚い本を持っているとしましょう。 さて、仮に、小説で少なくとも1回は、使用される単語のみで構成される2つの辞書を持ってきました。 その2つの辞書のすべての単語は、通常のアルファベット順に保存されます。 仮想辞書Aでは、単語は1回だけ印刷されますが、仮想辞書Bでは、単語は小説で印刷された回数だけ印刷されます。 単語は両方の辞書でアルファベット順にソートされていることを忘れないでください。 今、あなたは小説を読んでいる間にある時点で立ち往生し、それらの架空の辞書の誰かからその単語の意味を見つける必要があります。 あなたは何をしますか? 確かに、あなたはその意味を見つけるためにいくつかのステップでその単語にジャンプするでしょう、むしろ小説の各単語の意味を最初からそのバグのある単語に到達するまで探してください。

これは、SQLでのインデックスの動作方法です。 辞書Aをプライマリインデックス、辞書Bをキー/セカンダリインデックス、そして単語の意味をQUERY / SELECTSTATEMENTとして取得したいという要望を考えてみてください。 インデックスは、非常に高速でデータをフェッチするのに役立ちます。 インデックスがないと、最初からデータを探す必要があり、不必要に時間とコストのかかるタスクになります。

インデックスとタイプの詳細については、こちらをご覧ください