イントロダクション#
複数列に基づくインデックスは結合インデックスになります。
例えば、商品テーブルの product_no と name フィールドを組み合わせて結合インデックス (product_no, name) を作成します。
CREATE INDEX index_product_no_name ON product(product_no, name);
下の図は結合インデックス（product_no, name）の B+Tree の概略図です（葉ノードは単方向リストで描かれていますが、実際には双方向です、画像に誤りがあります）：
結合インデックスの非葉ノードは、2 つのフィールドの値を B+Tree のキー値として使用します。
結合インデックスでデータを照会する際は、まず product_no を比較し、product_no が同じ場合に name 列を比較します。つまり、結合インデックスでの B+Tree の照会は、まず product_no に基づいてソートされ、その後 product_no が同じ場合に name フィールドでソートされます。
したがって、結合インデックスを使用する際には、最左一致の原則が存在します：最も左側のフィールドを優先してインデックスの一致を行います。これに従わない場合、結合インデックスは無効になります。
例えば、(a,b,c) の結合インデックスを作成し、以下の条件で照会すると、結合インデックスに一致します：
- where a=1;
- where a=1 and b=2 and c=3;
- where b=2 and a=1;
注意が必要なのは、クエリオプティマイザがあるため、where句の中の a フィールドの順序は重要ではありません。
しかし、以下の条件では、[最左一致の原則] に従わないため、結合インデックスに一致しません：
- where b=2;
- where c=3;
- where b=2 and c=3;
上記の**クエリ条件が無効になるのは、(a, b, c) の結合インデックスが、まず a でソートされ、次に a が同じ場合に b でソートされ、さらに b が同じ場合に c でソートされるためです。**
したがって、b と c は全体的には無秩序で、局所的には相対的に秩序があります。このため、最左一致の原則に従わない場合、インデックスを利用することはできません。
ここで、結合インデックス（a, b）の B+Tree の例を示します（葉ノードは単方向リストで描かれていますが、実際には双方向です、画像に誤りがあります）：
見ると、a は全体的に秩序があります（1, 2, 2, 3, 4, 5, 6, 7, 8）、一方 b は全体的には無秩序です（12, 7, 8, 2, 3, 8, 10, 5, 2）。したがって、where b = 2 のようなクエリ条件では結合インデックスを利用することはできません。インデックスを利用するための前提は、インデックス内のキーが秩序があることです。
a が同じ場合にのみ、b は秩序があります。例えば、a が 2 のとき、b の値は（7, 8）であり、このときは秩序があります。この秩序の状態は局所的なものであり、したがって、where a = 2 and b = 7 を実行すると、a と b フィールドは結合インデックスを利用でき、結合インデックスが有効になります。
結合インデックスの範囲照会#
結合インデックスにはいくつかの特殊な状況があり、照会プロセスで結合インデックスを使用したからといって、結合インデックス内のすべてのフィールドが結合インデックスを使用して照会されるわけではありません。一部のフィールドが結合インデックスの B+Tree を使用し、一部のフィールドが使用されない場合があります。この特殊な状況は範囲照会で発生します。
結合インデックスの最左一致の原則は、右に一致し続け、「範囲照会」に出会うと一致を停止します。つまり、範囲照会のフィールドは結合インデックスを使用できますが、範囲照会フィールドの後のフィールドは結合インデックスを使用できません。
結合インデックスの最左一致の原則が一致を停止させる範囲照会#
Q1: select * from t_table where a > 1 and b = 2、結合インデックス (a, b) のどのフィールドが結合インデックスの B+Tree を使用しましたか？#
a フィールドのみが結合インデックスを使用して照会され、b フィールドは使用されていません。
a > 1 条件に一致する二次インデックスの範囲内では、b フィールドの値は無秩序です。したがって、b = 2 に基づいてスキャンする必要のある範囲をさらに減らすことはできません。
実行計画内の key（使用されたインデックス）と key_len（インデックス内のいくつのフィールドが使用されたか）を通じて、オプティマイザが具体的にいくつのフィールドの検索条件を使用してスキャン範囲の境界条件を形成したかを知ることができます。
EXPLAIN select * from t_table where a > 1 and b = 2
a と b はどちらも int で NULL ではなく、int は 4 バイトを占めます（フィールドが NULL を許可する場合は、フィールドタイプのバイト数に 1 を加え、つまり 5 になります）。下の図の key_len は 4 で、a フィールドのみが結合インデックスを使用していることを示しています：
Q2：select * from t_table where a ≥ 1 and b = 2、結合インデックス (a, b) のどのフィールドが結合インデックスの B+Tree を使用しましたか？#
a >= 1 条件に一致する二次インデックスの記録の範囲内では、b フィールドの値は「無秩序」ですが、a = 1 に一致する二次インデックスの記録の範囲内では、b フィールドの値は「秩序」です（結合インデックスでは、まず a フィールドの値でソートされ、次に a フィールドの値が同じ場合に b フィールドの値でソートされます）。つまり、a = 1 and b = 2 条件に一致する最初の記録からスキャンを開始し、最初の a フィールドの値が 1 の記録からスキャンを開始する必要はありません。
したがって、Q2 のこのクエリ文では a と b フィールドの両方が結合インデックスを使用して照会されます。
実行計画内の key_len から知ることができます。
Q3：select * from t_table where a between 2 and 8 and b =2、結合インデックス (a, b) のどのフィールドが結合インデックスの B+Tree を使用しましたか？#
MySQL では、BETWEEN は value1 と value2 の境界値を含み、これは >= and <= に似ています。一部のデータベースでは、value1 と value2 の境界値を含まない場合があります（> and < に似ています）。
MySQL の BETWEEN は value1 と value2 の境界値を含むため、Q2 のクエリ文に似ています。したがって、Q3 のこのクエリ文では a と b フィールドの両方が結合インデックスを使用して照会されます。
実行計画内の key_len から知ることができます。
Q4：select * from t_user where name like ‘j%’ and age = 22、結合インデックス (name, age) のどのフィールドが結合インデックスの B+Tree を使用しましたか？#
a フィールドは結合インデックスの B+Tree で照会され、形成されるスキャン範囲は ['j','k') です。注意してください、j は閉区間（つまり ≥=j and <k の区間）です。下の図のように：
前缶が 'j' である name フィールドの二次インデックスの記録の範囲内では、age フィールドの値は「無秩序」ですが、name = j に一致する二次インデックスの記録の範囲内では、age フィールドの値は「秩序」です（結合インデックスでは、まず name フィールドの値でソートされ、次に name フィールドの値が同じ場合に age フィールドの値でソートされます）。
つまり、name = 'j' and age = 22 条件に一致する最初の記録からスキャンを開始し、最初の name = 'j' の記録からスキャンを開始する必要はありません。下の図の右側のように：
したがって、Q4 のこのクエリ文では a と b フィールドの両方が結合インデックスを使用して照会されます。
実行計画内の key_len から知ることができます：
-
name フィールドのタイプは varchar (30) で NULL ではなく、データベーステーブルは utf8mb4 文字セットを使用しており、1 文字は 4 バイトです。したがって、name は最大で 120 バイト（30*4）を占め、name は可変長タイプのフィールドであるため、さらに 2 バイト（このフィールドの実際のデータの長さを格納するため）を加える必要があります。最終的に name の key_len は 122 になります。
-
age フィールドのタイプは int で NULL ではなく、key_len は 4 です。
TIP
「name が可変長タイプのフィールドであるため、さらに 2 バイトを加える必要があります」。以前のこの **記事 (opens new window)** では「可変長フィールドが許可する最大バイト数が 255 バイト以下の場合、可変長フィールドの長さを 1 バイトで表現します」と述べられていますが、ここでなぜ 2 バイトなのですか？
key_len の表示は比較的特殊で、行形式は innodb ストレージエンジンによって実装されており、実行計画はサーバーレイヤーで生成されるため、innodb ストレージエンジンの可変フィールドの長さが何バイトを占めるかを尋ねることはなく、関係なく 2 バイトで可変フィールドの長さを表現します。
結局、key_len の目的は、インデックス照会でどのインデックスフィールドが使用されたかを知らせることであり、このフィールドがどれだけのバイトスペースを占めるかを正確に伝えることではありません。
Q4 のクエリ文の実行計画は以下の通りで、key_len が 126 バイトであることがわかります。name の key_len は 122、age の key_len は 4 で、オプティマイザがスキャン範囲の境界条件を形成するために 2 つのフィールドの検索条件を使用したことを示しています。つまり、name と age フィールドの両方が結合インデックスを使用して照会されます。
以上のことから、結合インデックスの最左一致の原則は、範囲照会（>、< など）に出会うと一致を停止します。つまり、範囲照会のフィールドは結合インデックスを使用できますが、その後のフィールドは使用できません。注意してください、≥、≤、between、"like" プレフィックス一致の範囲照会では一致が停止しません。
インデックスプッシュダウン#
結合インデックス (a, b) に対して、select * from table where a > 1 and b = 2; 文を実行する際、a フィールドのみがインデックスを使用でき、結合インデックスの B+Tree で条件を満たす最初の主キー値（ID が 2）を見つけた後、他の条件が満たされているかどうかを判断する必要があります（つまり b = 2）。このとき、結合インデックス内で判断しますか、それとも主キーインデックスに戻って判断しますか？
- MySQL 5.6 以前は、ID2（主キー値）から一つずつ戻って、[主キーインデックス] でデータ行を見つけ、b フィールドの値を比較する必要があります。
- **MySQL 5.6 で導入されたインデックスプッシュダウン最適化** により、結合インデックスを走査する過程で、結合インデックスに含まれるフィールド（そのフィールドが結合インデックスの列にある）で先に判断（b=2) を行い、条件を満たさないレコードを直接フィルタリングし、フィルタリングされたデータに基づいて一つずつ戻ってスキャンすることで、戻る回数を減らすことができます。
クエリ文の実行計画に Extra が Using index condition と表示されると、インデックスプッシュダウンの最適化が使用されたことを示します。
インデックスの区別度#
結合インデックスを作成する際のフィールドの順序は、インデックスの効率にも大きな影響を与えます。前方にあるフィールドはインデックスフィルタリングに使用される確率が高くなります。実際の開発作業では、結合インデックスを作成する際には、区別度の高いフィールドを前に配置することで、区別度の高いフィールドがより多くの SQL で使用される可能性が高くなります。
区別度とは、特定のフィールド column の異なる値の数を「表の総行数」で割ったものです。計算式は以下の通りです：
例えば、性別の区別度は非常に小さく、インデックスを作成するのには適していないか、結合インデックス列の前方に配置するのには適していませんが、UUID のようなフィールドはインデックスを作成するのに適しているか、結合インデックス列の前方に配置するのに適しています。
インデックスの区別度が非常に小さい場合、フィールドの ** 値が均等に分布していると仮定すると、どの値を検索しても半分のデータを得る可能性があります。このような場合、インデックスを使用しない方が良いです。なぜなら、MySQL にはクエリオプティマイザがあり、クエリオプティマイザは特定の値が表のデータ行に出現する割合（一般的な割合の境界は「30%」）が非常に高いとき、通常はインデックスを無視して全表スキャンを行うからです。
結合インデックスによるソート#
select * from order where status = 1 order by create_time asc
上記の SQL に対して、(status, create_time) の結合インデックスを作成することで、MySQL データベースでファイルソート（filesort）が発生するのを回避できます。
照会時に、status のインデックスのみが使用される場合でも、この文は create_time をソートする必要があります。この場合、ファイルソートが必要になります。つまり、SQL 実行計画内の Extra 列に Using filesort が表示されます。
したがって、インデックスの有序性を利用するために、status と create_time 列に結合インデックスを作成することで、status に基づいてフィルタリングされたデータが create_time に基づいてソートされ、再度ファイルソートを回避し、クエリ効率を向上させます。
要約すると：using filesort を避けるためには、カバリングインデックスを使用します。