MySQLのインデックス

序文

早速本題に入りましょう。これからお話しするのは次のマインドマップです。まずは印象をつかんでください。

• 一般的なインデックスの種類（実装レベル）
• インデックス タイプ (アプリケーション レベル)
• クラスター化インデックスと非クラスター化インデックス
• カバーインデックス
• 最適なインデックス使用戦略

1. 一般的なインデックスの種類（実装レベル）

まず、MySQL がインデックスをどのように実装しているかについては話しません。それについては後で話しましょう。データベースのインデックスを設計するように求められた場合、どのように設計すればよいでしょうか。

まず、このインデックスがどのような効果を達成しようとしているのか考えてみましょう。実際、データをすばやく見つけるための戦略を実装したいだけなので、インデックスの実装は本質的に検索アルゴリズムです。

しかし、私たちのデータには次のような特徴があるため、通常の検索とは異なります。

1. 保存されるデータの量は非常に大きい

2. そしてそれは常に動的に変化している

したがって、インデックスを実装する際には、これら 2 つの特性を考慮する必要があります。検索機能を実装するには、最も適したデータ構造アルゴリズムを見つける必要があります。

以下に示すように、一般的な検索戦略を見てみましょう。

上記の 2 つの特性により、まず静的検索アルゴリズムを除外します。

検索ツリーに関しては、バイナリツリーとマルチツリーの 2 つの選択肢があります。

バイナリ ツリー: バイナリ ツリーを選択した場合、データの量が膨大になるため、バイナリ ツリーの深さが非常に大きくなり、インデックス ツリーが巨大なツリーになり、各クエリで大量のディスク IO が発生します。

マルチブランチ ツリー: マルチブランチ ツリーはツリーの深さが大きいという問題を解決します。B ツリーと B+ ツリーのどちらを選択すればよいでしょうか?

Wikipediazh.wikipedia.org/wiki/B%2Btree の B ツリー

Wikipediazh.wikipedia.org/wiki/B%2Btree の B+ ツリー

上図から、B+ツリーのリーフノードにはすべてのインデックス値が格納されており、リーフノードはリンクリストの形式で相互接続されているため、左端のリンクリストからトラバースするだけですべての値を見つけることができることがわかります。最も一般的な用途は範囲検索ですが、Bツリーはこの範囲検索を満たしていないか、実装が特に複雑であるため、MySQLは最終的にB+ツリーを使用してこの機能を実装することを選択しました。

1.1 B ツリー インデックス (B+ ツリー)

まず、MySQL では正式には B-Tree インデックスと呼ばれていますが、B+ ツリー データ構造を使用します。

B ツリー インデックスを使用すると、データへのアクセスを高速化できます。テーブル全体をスキャンする必要はありません。代わりに、インデックス ツリーのルート ノードからレイヤーごとに下方向に検索します。ルート ノードには、インデックス値と次のノードへのポインタが格納されます。

単一列インデックスのデータがどのように構成されているかを見てみましょう。

テーブルユーザーを作成(
`name` varchar(50) が null ではない、
`uid` int(4) が null ではない、
`gender` int(2) が null ではない、
 キー(`uid`)
);

上記の User テーブルは、uid 列のインデックスを作成します。では、テーブルに uid (96-102) を挿入するときに、ストレージ エンジンはインデックスをどのように管理するのでしょうか。下のインデックスツリーを見てください

1. すべてのインデックス値をリーフノードに格納します。非リーフノード値は、ターゲット値を含むリーフノードをより迅速に見つけるために使用されます。

2. リーフノードの値は順序付けられる

3. リーフノードはリンクリストの形式で関連付けられる

次に、複数列（結合）インデックスのデータがどのように構成されているかを見てみましょう。

テーブルユーザーを作成(
`name` varchar(50) が null ではない、
`uid` int(4) が null ではない、
`gender` int(2) が null ではない、
 キー(`uid`,`name`)
);

User テーブルに共同インデックス キー (uid、name) が作成されます。この場合、そのインデックス ツリーは次の図のようになります。

特性は単一列インデックスと同じですが、違いはそのソートにあります。最初のフィールドが同じ場合は、2 番目のインデックス フィールドによってソートされます。

B ツリーを使用してデータをすばやく見つけるにはどうすればよいでしょうか?

InnoDb ストレージ エンジンの B ツリー インデックスの場合、インデックスを通じて行データを検索するには、次の手順を実行します。

• クラスター化インデックス (主キー) が使用されている場合、リーフ ノードには行データが含まれ、直接返すことができます。
• 非クラスター化インデックス（通常のインデックス）を使用する場合、主キーはリーフノードに格納され、主キーに基づいて上位のクラスター化インデックスが照会され、最終的にデータが返されます。

MyISAM ストレージ エンジンの B ツリー インデックスの場合、インデックスを通じて行データを検索するには、次の手順を実行します。

• MyISAM インデックス ツリーのリーフ ノードには、インデックス値以外の主キーや行データは保存されません。代わりに、行データへのポインターが保存され、このポインターに基づいてテーブル ファイルからデータがクエリされます。

1.2 ハッシュインデックス（ハッシュテーブル）

ハッシュ インデックスはハッシュ テーブルに基づいて実装され、すべての列が完全に一致する場合にのみ有効になります。

つまり、ハッシュ インデックス キー (col1、col2) がある場合、毎回 col1 と col2 の両方が使用される場合にのみ有効になります。ハッシュ インデックスが生成される場合、ハッシュ関数に基づいてすべてのインデックス列のハッシュ値を取得することによって実装されるためです。

下の図に示すように、ハッシュインデックスキー（名前）があります

mysql> select * from User where name='張三'；ハッシュインデックスを使用してすばやく検索するにはどうすればよいですか?

1. 最初のステップはハッシュ値を計算することです。hash(Zhang San) = 1287
2. 2番目のステップは行番号を見つけることです。たとえば、key=1287は行番号3に対応します。
3. 3 番目のステップは、指定された行を見つけて、名前列の値を比較し、それが Zhang San であるかどうかを確認することです。

2. 一般的なインデックスの種類（アプリケーションレベル）

主キーインデックス

テーブルユーザーを作成(
`name` varchar(50) が null ではない、
`uid` int(4) が null ではない、
`gender` int(2) が null ではない、
 主キー(`uid`)
);

主キー インデックスは一意であり、通常はテーブル ID で設定されます。テーブルには主キー インデックスを 1 つだけ設定できます。これが主キー インデックスと一意のインデックスの差です。

ユニークインデックス

テーブルユーザーを作成(
`name` varchar(50) が null ではない、
`uid` int(4) が null ではない、
`gender` int(2) が null ではない、
 ユニークキー(`name`)
);

ユニーク インデックスは、主にビジネスにおけるユニーク制約に使用されます。 ユニーク インデックスと主キー インデックスの違いは、テーブルに複数のユニーク インデックスを設定できることです。

単一列インデックス

テーブルユーザーを作成(
`name` varchar(50) が null ではない、
`uid` int(4) が null ではない、
`gender` int(2) が null ではない、
 キー(`名前`)
);

フィールドのインデックス作成

共同インデックス

テーブルユーザーを作成(
`name` varchar(50) が null ではない、
`uid` int(4) が null ではない、
`gender` int(2) が null ではない、
 キー(`name`,`uid`)
);

2 つ以上のフィールドが結合されてインデックスが形成されます。使用する際は、左端一致の原則に注意する必要があります。

他にもあまり使われていないものもありますが紹介しません〜

3. クラスター化インデックスと非クラスター化インデックス

クラスター化インデックスとは何ですか?

クラスター化インデックスは、インデックスと行データが一緒に保存されるインデックスです。つまり、B+ ツリーのリーフ ノードに格納されるのは、そのインデックス値だけでなく、対応する行のデータも含まれます。後で写真を見ると分かります。

クラスター化インデックスはインデックスではなく、データストレージを整理する方法です。 ！ ！

テーブルテストを作成する(
 col1 int が null ではない、
 col2 int が null ではない、
 主キー(col1)、
 キー(列2)
);

上記のように、テーブル test には主キー col1 と共通インデックス col2 の 2 つのインデックスがあります。では、これら 2 つのインデックスとクラスター化インデックスおよび非クラスター化インデックスの関係は何でしょうか?

クラスター化インデックスと非クラスター化インデックス (セカンダリ インデックス) が生成され、2 つのインデックス ツリーが構成されます。主キー インデックスは、クラスター化インデックスのツリーと、インデックスとして col2 を持つ非クラスター化インデックスのツリーを生成します。

InnoDb は、主キーを通じてクラスター化インデックスを実装します。主キーがない場合は、一意の空でないインデックスが選択されて実装されます。一意の空でないインデックスがない場合、主キーが暗黙的に生成されます。

クラスター化インデックスと非クラスター化インデックスがインデックスツリー上でデータをどのように分散するかを見てみましょう。画像は「High Performance Nysql」から引用したものです。

次の図は、クラスター化インデックスのデータがどのように構成されているかを示しています。 Col1は主キーインデックスのクラスター化インデックスツリーです

インデックス列は主キーcol1です

リーフノードには、インデックス値列col1（3〜99〜4700）が格納されているだけでなく、列col2（92〜8〜13）などの他の列の値も格納されていることがわかります。他の列がある場合は、それらも格納されます。つまり、クラスター化インデックスツリーは、特定のインデックス値に対応するデータ行をリーフノードに格納します。

次の図は、非クラスター化インデックス (セカンダリ インデックス) のデータ構成を示しています。

インデックス列はcol2です

クラスター化インデックスとは異なり、非クラスター化インデックスはツリーのリーフ ノード上のインデックス値に加えて主キー値のみを格納します。クラスター化インデックスにはデータの行が格納されます。

SQL ステートメントselect * from test where col2=93;がある場合

上記のステートメントは、インデックスツリー検索プロセスを2回実行します。

1. 最初のステップは、非クラスタ化インデックスのインデックスツリーからcol2=93を含むリーフノードを見つけ、行の主キー3を見つけることです。

2. 2 番目のステップでは、主キー 3 に基づいてクラスター化インデックス内の主キー = 3 を含むリーフ ノードを検索し、すべての行データを返します。

上記は InnoDb ストレージ エンジンに基づいています。MyISAM は、データ ファイルとインデックス ファイルが互いに独立して保存されるため、クラスター化インデックスをサポートしていません。MyISAM ストレージ エンジンのインデックス ツリーのリーフ ノードには、主キー値ではなく、対応する行へのアドレスまたはポインターが格納され、次の図に示すように、テーブル データ ファイルから検索されます。

結論は：

• クラスター化インデックス:

通常、主キーまたは空でない一意のインデックスによって実装され、リーフノードはデータの行全体を保存します。

• 非クラスター化インデックス:

セカンダリ インデックスとも呼ばれ、よく使用される一般的なインデックスです。リーフ ノードには、インデックス値と主キー値が格納されます。

4. カバーインデックス

カバーリング インデックスとは、クエリする必要があるすべてのフィールドがインデックスに含まれていることを意味します。

テーブルユーザーを作成(
`name` varchar(50) が null ではない、
`uid` int(4) が null ではない、
`gender` int(2) が null ではない、
 キー(`uid`,`name`)
);

テーブルUserにUser（名前、uid、性別）の3つのフィールドがあり、結合インデックスキー（名前、uid）がある場合、

カバーリング インデックスは、次の SQL クエリを実行するときに使用されます。

名前が ('a','b') かつ uid >= 98 かつ uid <=100 の場合、User から name,uid を選択します。

上記の SQL ステートメントでは、結合インデックス キー (name、uid) が使用され、name フィールドと uid フィールドのみを検索する必要があるため、カバー インデックスが使用されます。インデックスをカバーすることの利点は何ですか?まず次の写真を見てください

上の図は、結合インデックス キー (name、uid) に対応するインデックス ツリーです。この図から、2 つのフィールド (name、uid) のみをクエリする必要がある場合、クエリに必要なデータをインデックス ツリーから取得できることがわかります。インデックス値を見つけて、テーブル データ ファイルから対応する行データを探す必要はありません。

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

1. 主キーインデックスの二次クエリを避ける（クラスタリング）

2. テーブルをクエリする必要がないため（テーブルデータファイルから）、MySQLキャッシュの負荷が大幅に改善されます。

つまり、データの読み取りパフォーマンスが大幅に向上します

5. 最適なインデックス使用戦略

最後に、インデックスを使用する際の落とし穴回避ガイドについて説明します。

独立した列

独立した列は、単一列のインデックスを指すのではなく、インデックス列が式または関数の一部になることができないという事実を指します。

select * FROM test where col1 + 1 = 100; // 式の一部にはできません select * FROM test where ABS(col1) = 100; // 関数の一部にはできません

左端一致原則

共同インデックスキー (col1、col2) があるとします。次のクエリは無効なインデックスです

col2 = 3 の場合、テストから * を選択します。
col1 が '%3' のようなテストから * を選択します。

左端一致原則については、B+ ツリーのリーフ ノード間の関連付けについて考えると、B+ のリーフ ノードがリンク リストの形式で左から右に関連付けられているため、左端一致原則が必要な理由がほぼ理解できます。インデックスをクエリする場合は、範囲クエリを使用するか、左側に明確な開始インデックス値を設定する必要があります。「%XYZ」などのあいまいなクエリをスキップしたり使用したりすることはできません。

インデックス値は null にできません

単一列インデックスにNULL値が含まれていると、インデックスが無効になります。

いずれかの列にNULL値がある場合、複数列インデックスは無効になります。

クラスター化インデックスとカバーリングインデックスを使用して読み取りパフォーマンスを大幅に向上します

必要なフィールドはクラスター化インデックスとカバーリング インデックスのインデックス ツリーですでに使用可能であるため、クエリのためにテーブル ファイルに戻る必要がなくなり、クエリ速度が向上します。

短いインデックスの使用

長い文字列を検索する場合は、プレフィックスの長さを一致させるだけでよいため、インデックス スペースを大幅に節約できます。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。