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

背景

最近、遅い SQL ステートメントによってシステム パフォーマンスの問題が頻繁に発生するようになったため、インデックスを最適化することにしました。一部のテーブル構造にはすでに多くのインデックスがあります。インデックスを追加し続けると、必然的にデータ挿入のパフォーマンスに影響します。では、この目標を達成するために複合インデックスを使用することは可能でしょうか?この記事で調べてみましょう。

複合インデックスを理解する

where 条件で複数のフィールドが使用され、複数のフィールドにインデックスを付ける必要がある場合は、複合インデックスの使用を検討できます。たとえば、住所を検索する場合、都道府県と市区町村を入力する必要があります。この場合、都道府県と市区町村にインデックスを作成すると、データ量が多いときにクエリ速度が大幅に向上します。

複合インデックスの利点は何ですか?

• クエリのオーバーヘッドを削減: 複合インデックス (c1、c2、c3) を作成することは、実際には 3 つのインデックス (c1)、(c1、c2)、(c1、c2、c3) を作成することと同じです。大きなテーブルの場合、これによりオーバーヘッドが大幅に削減されます。
• カバーリング インデックス: MySQL はテーブルに戻らずにインデックスをトラバースしてデータを直接取得できるため、ランダム IO 操作が大幅に削減されます。
• 高効率: インデックス列の数が多いほど、インデックスを通じてフィルターされるデータが少なくなり、クエリの効率が向上します。

欠点:

• インデックス フィールドの数が増えると、作成されるインデックスの数も増え、インデックスごとにディスク領域のオーバーヘッドが増加します。
• インデックスの数が多いほど、クエリの効率は高くなります。ただし、インデックスの更新を必要とする追加、削除、変更操作の効率には影響します。

複合インデックスの使用に関する推奨事項: 1 つのテーブルに複数の複合インデックスを含めないでください。また、1 つの複合インデックスに 3 つ以上のフィールドを含めないでください。それを超過した場合は、必要性と他の代替手段があるかどうかを検討する必要があります。

左端一致原則

複合インデックスは、左端一致の原則に従います。名前が示すように、複合インデックスでは、左端のフィールドが最初に一致します。したがって、複合インデックスを作成するときは、where 句で最も頻繁に使用されるフィールドが複合インデックスの左端に配置されます。

補助インデックスはB+ツリーで実装されています。複数の列を指定できますが、各列の比較優先順位は異なり、前に書いた方が優先順位が高くなります。一度省略が発生すると、B+ ツリー上で検索を続行することができないため (ギャップを埋めるなどの手段で解決しない限り)、最も左の連続一致に従って検索が実行されます。検索は B+ ツリー上で行われるため、条件の比較には当然、完全一致 (つまり、「=」と「IN」) が必要になります。

where 句では 2 つのフィールド c1 と c2 が使用されています。インデックスを作成するとき、2 つのフィールドの順序は (c1, c2) とすべきでしょうか、それとも (c2, c1) とすべきでしょうか。

正しいアプローチは、重複値が最も少ないものを最初に置くことです。たとえば、値の 95% が一意である場合は、それらを先頭に配置することを検討できます。

フィールド順序の影響

複合インデックスは左端一致の原則に従いますが、where クエリ条件のフィールドもインデックスの順序で記述する必要がありますか?
たとえば、複合インデックスが (c1、c2、c3) の場合、次の 2 つのクエリ条件はインデックスに影響しますか?

c1 = 1 かつ c2 = 4 の場合、t_user から * を選択します。
c2 = 4 かつ c1 = 1 の場合、t_user から * を選択します。

最初の SQL ステートメントの方が効率的であると示唆する記事を見ました。これは信頼できるでしょうか? 2 つのクエリ メソッドの条件は同じであり、結果も同じになるはずです。通常、MySQL では同じインデックスが使用されます。

MySQL のクエリ オプティマイザ explain を使用して上記の 2 つのステートメントを分析すると、実行プランがまったく同じであることがわかります。つまり、SQL ステートメント内のフィールドの順序は複合インデックス フィールドの順序と一致する必要はなく、クエリ オプティマイザーによって順序が自動的に調整されます。

効率に影響があるとすれば、それはおそらくクエリ オプティマイザの修正順序の影響であり、これはほとんど無視できる程度です。

単一のフィールドでインデックスをトリガーできますか?

複合インデックス (c1,c2,c3) は、3 つのインデックス (c1)、(c1,c2)、(c1,c2,c3) に相当しますが、クエリ条件に c1 のみが含まれている場合、インデックスが使用されることは明らかです。
しかし、where 条件が次のようになっている場合はどうなるでしょうか。

c2 = 4 の場合の t_user から;

上記の記述はインデックスを通過しますか?これはいくつかの状況で説明できます。
条件として explan query c1 を使用して SQL ステートメントを実行します。

c1 = 1 の場合の t_user からの * を選択します。

上記のステートメントのインデックス タイプは ref です。 ref タイプは、MySQL がインデックス内のすべてのデータをスキャンして判断することなく、特定のアルゴリズムに基づいて条件を満たすインデックスをすばやく見つけることを意味します。データをすばやく見つけるには、このタイプのインデックスが特定のデータ構造を満たしている必要があります。
条件として explan query c2 を使用して SQL ステートメントを実行します。

c2 = 4 の場合、t_user から c2 を選択します。

上記のステートメントのインデックス タイプは index です。インデックス タイプは、MySQL がインデックス全体をスキャンすることを意味します。インデックスまたはインデックスの一部である限り、MySQL はそれをインデックス タイプでスキャンする可能性があります。この方法ではデータを 1 つずつ検索するため、パフォーマンスはあまり高くありません。

この例では、クエリ対象のフィールドに特定の要件があります。 where の条件は c2 であり、select でクエリ対象のフィールドは c2 のみであるため、インデックス タイプ index が使用されます。

c2 が * または他のフィールドに置き換えられた場合:

c2 = 4 の場合の select * from t_user の説明

上記のステートメントでは、インデックスが使用されなくなったことがわかりますが、テーブル全体のスキャンが実行されます。これは、MySQL が左端一致の原則に従う理由も説明しています。
結論は次のようになります。単一のフィールドが複合インデックスの最初のフィールドである場合、インデックスは通常どおり使用されます。単一のフィールドが複合インデックスの別のフィールドであり、選択後にこのフィールドのみが表示される場合は、インデックス タイプ インデックスが使用されます。それ以外の場合は、フル テーブル スキャンが使用されます。

複合インデックスは単一のインデックスを置き換えることができますか?

単一インデックス: (c1)、複合インデックス: (c1, c2)。

クエリ条件として c1 を使用すると、単一インデックスのクエリ速度は複合インデックスのクエリ速度とほぼ同じか、複合インデックスよりもわずかに速くなります。
複合クラスター化インデックスの開始列以外の列 (c2) のみをクエリ条件として使用した場合、複合インデックスは効果がありません。
テーブルに複合インデックス (c1、c2) がある場合は、単一のインデックス (c1) を作成する必要はありません。
単一のインデックス (c1) がすでに存在する場合は、クエリで必要な場合に複合インデックス (c1、c2) を追加して効率を向上させることができます。

まとめ

この記事では、MySQL 複合インデックスを使用する際に注意すべきいくつかの知識ポイントをまとめています。使用時には、explain を使用して、SQL 文がインデックスを使用しているかどうか、またどのインデックスを使用しているかを確認できます。
ただし、Mysql の実行プランとクエリの実際の実行プロセスは完全には一致しないことを理解する必要があります。
なぜ私がこれを知っているのかと聞かないでください。実際にこれに遭遇したからです。同じ SQL ステートメントでも、クエリ条件に応じてインデックスが使用される場合と使用されない場合があります。

MySQL 複合インデックスの仕組みについての記事はこれで終わりです。MySQL 複合インデックスの詳細については、123WORDPRESS.COM の過去の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。