MySQL複合インデックスの詳細な研究

MySQL複合インデックスの詳細な研究

複合インデックス (結合インデックスとも呼ばれます) は、複数の列に対して作成されるインデックスです。複合インデックスを作成するときに最も重要なことは、列の順序の選択です。これは、インデックスを使用できるかどうか、またはインデックスを使用できる述語条件の数に影響します。複合インデックスの使用は、左端の一致の原則に従います。インデックスの左側の列が一致した場合にのみ、後続の列が一致し続けます。この記事では、主に複合インデックスの作成順序と使用方法について説明します。

1. 複合指数の概念

単一の列に作成されたインデックスは単一列インデックスと呼ばれ、2 つ以上の列に作成されたインデックスは複合インデックスと呼ばれます。単一の列にインデックスを作成するのは比較的簡単です。通常は、列の選択性のみを考慮する必要があります。選択性が優れているほど、データが分散され、作成されたインデックスのパフォーマンスが向上します。通常、列の選択性を計算する式は次のとおりです。
選択性 = 述語条件を適用した後に返されるレコードの数 / 述語条件を適用せずに返されるレコードの数。選択性の値の範囲は (0,1] です。値が小さいほど、選択性は高くなります。
複合インデックス (結合インデックスとも呼ばれます) は、複数の列に対して作成されるインデックスです。複合インデックスを作成するときに最も重要なことは、列の順序の選択です。これは、インデックスを使用できるかどうか、またはインデックスを使用できる述語条件の数に影響します。複合インデックスの使用は、左端の一致の原則に従います。インデックスの左側の列が一致した場合にのみ、後続の列が一致し続けます。

2. 複合インデックス列を使用する場合

複合インデックスは、左端一致の原則に従います。インデックスの左端の列が一致する場合にのみ、次の列が一致します。左の列が等しくない値のクエリに使用される場合、インデックスの右側の列はクエリや並べ替えに使用されません。

実験: 複合インデックスを使用するタイミング

複合インデックスのどのフィールドが使用されるかは、私たちが非常に懸念している問題です。インターネット上の典型的な例:

-- テストテーブルを作成する CREATE TABLE t1(
c1 CHAR(1) NULLではない、
c2 CHAR(1) NULLではない、
c3 CHAR(1) NULLではない、
c4 CHAR(1) NULLではない、
c5 CHAR(1) ヌルではない
)ENGINE innodb CHARSET UTF8;

-- インデックスを追加します。alter table t1 add index idx_c1234(c1,c2,c3,c4);

--テストデータを挿入するinsert into t1 values('1','1','1','1','1'),('2','2','2','2','2','2'),
('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5','5');

次のクエリ ステートメントのうちどれがインデックス idx_c1234 を使用し、どのインデックス フィールドが使用されているかを調べる必要があります。

(A) ここで c1=? かつ c2=? かつ c4>? かつ c3=?

(B) c1=? かつ c2=? かつ c4=? の場合、c3 の順序で

(C) c1=? かつ c4=? の場合、c3,c2 でグループ化

(D) c1=? かつ c5=? の場合、c2、c3 の順

(E) c1=? かつ c2=? かつ c5=? の場合、c2、c3 の順

(F) ここで、c1>? かつ c2=? かつ c4>? かつ c3=?

オプションA:

mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c4>'1' and c3='2';
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
| 1 | SIMPLE | t1 | NULL | 範囲 | idx_c1234 | idx_c1234 | 12 | NULL | 1 | 100.00 | インデックス条件を使用 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+

使用されるインデックスの長さは 12 です。つまり、4 つのフィールドすべてがインデックスを使用します。 c1、c2、c3 はすべて等しい値のクエリであるため、次の c4 列も使用できます。

注: UTF8 エンコーディングでは、インデックスの長さは 3 です。ここで、12 は 4 つのフィールドすべてがこのインデックスを使用することを意味します。

オプションB:

mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c4='2' order by c3;
+----+-------------+----------+------------+----------+---------------+--------------+-----------+-----------+-----------+-----------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+------------+----------+---------------+--------------+-----------+-----------+-----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 1 | 20.00 | インデックス条件の使用 |
+----+-------------+----------+------------+----------+---------------+--------------+-----------+-----------+-----------+-----------------------+

使用されるインデックスの長さは 6 です。つまり、2 つのフィールドがインデックスを使用します。左端の使用原則によれば、c1 と c2 はインデックスを使用します。クエリには c3 述語条件がないため、c2 を使用した後にインデックス値が中断され、c1 列と c2 列のみが使用されます。ここでの SQL は order by sorting を使用していますが、実行プランの Extra 部分には filesort キーワードがないため、インデックス内の c3 フィールドの順序でデータを読み取ることができます。

インデックスの c3 フィールドはインデックスの末尾に配置されていませんが、実行プランの Extra 部分に "fileasort" キーワードが表示されないため、インデックスの c2 フィールドの順序付けされた性質が実際に使用されていることに注意してください。これはなぜでしょうか?ここでは、MySQL 5.6 で導入された Index Condition Pushdown (ICP) 最適化が使用されます。基本的な考え方は、インデックス内のフィールドを使用してデータをフィルタリングすることです。 ICP を使用しない場合と ICP を使用する場合の違いを整理してみましょう。

ICP 最適化を使用しない場合、SQL 実行手順は次のようになります。

1. インデックス列 c1 と c2 を使用して、条件を満たす行データを取得します。ここで、c1='2'、c2='2'

2. テーブルに戻ってデータをクエリし、where c4='2' を使用してデータをフィルタリングします。

3. データをソートして出力する

ICP 最適化を使用する場合、SQL 実行手順は次のようになります。

1. インデックス列 c1 と c2 を使用して、条件を満たす行データを取得します。ここで、c1='2'、c2='2'

2. インデックス内の where c4='2' を使用してデータをフィルタリングする

3. データは順序付けられているので、条件に合うデータを順番に直接取得する

オプションC:

mysql> explain select c2,c3 from t1 where c1='2' and c4='2' group by c3,c2;
+----+-------------+--------+-----------+---------+---------------+------------+-------+-------+-------+--------+---------+---------------------------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+---------------+------------+-------+-------+-------+--------+---------+---------------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | where の使用; index の使用; temporary の使用; filesort の使用 |
+----+-------------+--------+-----------+---------+---------------+------------+-------+-------+-------+--------+---------+---------------------------------------------------------+

使用されるインデックスの長さは 3 です。つまり、1 つのフィールドがインデックスを使用します。左端の使用原則によれば、c1 はインデックスを使用します。クエリには c2 述語条件がないため、c1 を使用した後にインデックス値が中断され、c1 列のみが使用されます。 SQL 実行プロセスは次のとおりです。

1. 列c1のインデックスを使用して、c1='2'であるすべての行を検索し、テーブルに戻ってc4='2'を使用して一致しないデータを除外します。
2. 前のステップの結果に基づいて、結果内の c3 と c2 をソートして、継続的に変化するデータを取得します。同時に、データベース内にグループ化の結果を格納する一時テーブルを作成します。

C オプション拡張:

mysql> explain select c2,c3 from t1 where c1='2' and c4='2' group by c2,c3;
+----+-------------+--------+-----------+---------+---------------+------------+-------+-------+-------+---------+----------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+---------------+------------+-------+-------+-------+---------+----------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | where の使用; index の使用 |
+----+-------------+--------+-----------+---------+---------------+------------+-------+-------+-------+---------+----------------------------+

使用されるインデックスの長さは 3 です。つまり、1 つのフィールドがインデックスを使用します。左端の使用原則によれば、c1 はインデックスを使用します。

オプションD:

mysql> explain select c2,c3 from t1 where c1='2' and c5='2' order by c2,c3;
+----+-------------+--------+-----------+---------+---------------+------------+-------+-------+-------+---------+---------+------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+---------+---------------+------------+-------+-------+-------+---------+---------+------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | インデックス条件の使用; where の使用 |
+----+-------------+--------+-----------+---------+---------------+------------+-------+-------+-------+---------+---------+------------------------------------+

使用されるインデックスの長さは 3 です。つまり、すべてのフィールドでインデックスが使用されることになります。左端の使用原則によれば、c1 はインデックスを使用します。クエリには c2 述語条件がないため、c1 を使用した後にインデックス値が中断され、c1 列のみが使用されます。

Dオプション拡張:

mysql> explain select c2,c3 from t1 where c1='2' and c5='2' order by c3,c2;
+----+-------------+--------+-----------+--------+---------------+------------+-------+-------+-------+---------+----------+----------------------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+-----------+--------+---------------+------------+-------+-------+-------+---------+----------+----------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 3 | const | 2 | 14.29 | インデックス条件の使用; where の使用; filesort の使用 |
+----+-------------+--------+-----------+--------+---------------+------------+-------+-------+-------+---------+----------+----------------------------------------------------+

使用されるインデックスの長さは 3 です。つまり、すべてのフィールドでインデックスが使用されることになります。左端の使用原則によれば、c1 はインデックスを使用します。クエリには c2 述語条件がないため、c1 を使用した後にインデックス値が中断され、c1 列のみが使用されます。

オプションE:

mysql> explain select c1,c2,c3,c4,c5 from t1 where c1='2' and c2='2' and c5='2' order by c2,c3;
+----+-------------+--------+------------+----------+---------------+--------------+------------+-----------+-----------+-----------+------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+--------+------------+----------+---------------+------------+-----------+-----------+-----------+------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1234 | idx_c1234 | 6 | const,const | 2 | 14.29 | インデックス条件の使用; where の使用 |
+----+-------------+--------+------------+---------+---------------+------------+-----------+-----------+-----------+------------------------------------+

使用されるインデックスの長さは 6 です。つまり、両方のフィールドでインデックスが使用されることになります。左端の使用原則によれば、c1 と c2 はインデックスを使用します。ここで、SQL は order by sorting を使用しますが、実行プランの Extra 部分には filesort キーワードがありません。つまり、データはインデックスの c3 フィールドの順序で読み取ることができます (c2 は定数)。

オプション F:

mysql> explain select c1,c2,c3,c4,c5 from t1 where c1>'4' and c2='2' and c3='2' and c4='1';
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+
| 1 | SIMPLE | t1 | NULL | 範囲 | idx_c1234 | idx_c1234 | 3 | NULL | 1 | 20.00 | インデックス条件を使用 |
+----+-------------+----------+-----------+---------+---------------+------------+-------+-------+---------+-----------------------+

使用されるインデックスの長さは 3 です。つまり、すべてのフィールドでインデックスが使用されることになります。左端の使用原則によれば、c1 はインデックスを使用します。ここで、c1 は不等式クエリを使用しているため、後続の c2 クエリはインデックスを使用できません。このケースは非常に注目に値します。述語条件に等号クエリと範囲クエリが含まれている場合、範囲クエリがインデックスの前にあると、等号クエリはインデックスを使用できません。等号クエリが前にあり、範囲クエリが後ろにある場合は、両方ともインデックスを使用できます。

（III）複合インデックスの作成方法

複合インデックスを作成する際の難しさは、フィールド順序の選択にあります。私の見解は次のとおりです。

  • 等値クエリとソートがある場合は、複合インデックスを作成するときに、等値クエリ フィールドを最初に置き、ソート フィールドを最後に置きます。
  • 等しい値のクエリが複数ある場合は、選択性が高いクエリが最初に配置され、選択性が低いクエリが最後に配置されます。
  • 等値クエリ、範囲クエリ、並べ替えがある場合。等値クエリが最初に配置され、範囲クエリと並べ替えでは実際の状況に基づいてインデックスの順序を決定する必要があります。

さらに、Alibaba Java開発マニュアル - 2020最新Songshan版には、複合インデックスに関する仕様がいくつかあります。見てみましょう：

1. 順序付けのシナリオがある場合は、インデックスの順序性を活用してください。 order by の後のフィールドは複合インデックスの一部であり、ファイルソートを回避してクエリのパフォーマンスに影響を与えるために複合インデックスの最後に配置されます。

正の例: where a=? b=? order by c; index a_b_c

反例: インデックスに範囲クエリがある場合、インデックス順序は使用できません。たとえば、where a>10 order by b; インデックス a_b はソートできません。

2. 複合インデックスを構築する場合、最も識別力の高いインデックスは左端に配置されます。a=? かつ b=? の場合、列 a の値がほぼ一意であるため、単一列インデックス idx_a のみを構築する必要があります。

注意：等号と非等号の判定条件が混在する場合は、インデックス作成時に等号条件の列を先頭に置いてください。例えば、c>?かつd=?の場合、cの判別が

さらに高い場合は、インデックスの先頭に d を配置する必要があります。つまり、インデックス idx_d_c を作成します。

実験: 複合インデックスの作成方法

いくつかのドキュメントでは、複合インデックスを作成するためのルールについて説明しています。ESR 原則: 完全一致 (Equal) のフィールドを先頭に、並べ替え (Sort) 条件を中央に、範囲一致 (Range) のフィールドを末尾に配置します。次に、このアプローチが正しいかどうかを検討してみましょう。

例: 従業員テーブルemployeesがある

mysql> show テーブル employees を作成します。
+-----------+------------------------------
| テーブル | テーブルの作成                                                                   
+-----------+-------------------------------------
| 従業員 | テーブル `従業員` を作成する (
 `emp_no` int(11) NULLではない、
 `birth_date` 日付がNULLではない、
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `性別` enum('M','F') NOT NULL,
 `hire_date` 日付がNULLではない、
 主キー (`emp_no`)
) エンジン=InnoDB デフォルト文字セット=latin1 |
+-----------+-------------------------------------

-- データ量は約 300,000 行ですmysql> select count(*) from employees;
+----------+
| カウント(*) |
+----------+
| 300024 |
+----------+

ここで、first_name が「Ebbe」で、1998 年以降に入社した従業員を照会し、生年月日の昇順で並べ替える必要があります。

SQL ステートメントは次のとおりです。

従業員番号、生年月日、名、姓、性別、雇用日を選択してください 
従業員から 
雇用日 >= '1998-01-01' の場合
そしてfirst_name = 'Ebbe'
birth_date で並べ替えます。

この SQL ステートメントのパフォーマンスを最適化するには、テーブルにインデックスを作成する必要があります。where と order by の両方でインデックスが使用されるようにするには、複合インデックスを作成することにしました。作成順序は次のとおりです。

（A）入社日、名、生年月日

（B）入社日、生年月日、名前

（C）名、入社日、生年月日

（D）名、生年月日、入社日

（E）生年月日、名、入社日

(F) 生年月日、入社日、名

インデックスを作成するのに最適な順序を決定します。

注記：

1. 日付型は 3 バイトのスペースを占有し、hire_date と birth_date はどちらも 3 バイトのスペースを占有します。

2. first_name は可変長フィールドで、さらに 2 バイトを使用します。NULL 値が許可されている場合は、さらに 1 バイトが必要となり、16 バイトを占有します。

オプション A: 入社日、名、生年月日

employees(hire_date,first_name,birth_date) にインデックス idx_a を作成します。

実行計画は以下のとおりです。

+----+--------------+------------+-----------+---------+-------+-------+-------+--------+----------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+------------+-----------+---------+-------+-------+-------+--------+----------------------------------------+
| 1 | SIMPLE | employees | NULL | range | idx_a | idx_a | 19 | NULL | 5678 | 10.00 | インデックス条件の使用; filesort の使用 |
+----+--------------+------------+-----------+---------+-------+-------+-------+--------+----------------------------------------+

ここで、key_len の長さは 19 ですが、これは不可解です。hire_date は等しくない値のクエリなので、理論的には key_len は 3 である必要があります。MySQL ワークベンチを使用して実行プランを表示すると、インデックスが hire_date 列のみを使用していることもわかります (以下を参照)。なぜ3ではなく19なのでしょうか?本当に不可解です。長い間考えてきましたが、まだわかりません。答えを知っている方がいらっしゃいましたら、専門家の皆さんが答えてくださることを願っています。

オプション B: 雇用日、生年月日、名

干渉を避けるため、上記で作成したインデックス idx_a を削除してから、idx_b を作成します。

employees(hire_date,birth_date,first_name) にインデックス idx_b を作成します。

実行計画は以下のとおりです。

+----+--------------+------------+-----------+---------+-------+-------+-------+--------+----------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+------------+-----------+---------+-------+-------+-------+--------+----------------------------------------+
| 1 | SIMPLE | employees | NULL | range | idx_b | idx_b | 3 | NULL | 5682 | 10.00 | インデックス条件の使用; filesort の使用 |
+----+--------------+------------+-----------+---------+-------+-------+-------+--------+----------------------------------------+

ここで、key_len の長さは 3 であり、hire_date は等しくない値のクエリであるため、後続のインデックス列は使用できません。

オプション C: first_name、hire_date、birth_date

干渉を避けるため、上記で作成したインデックス idx_b を削除してから、idx_c を作成します。

employees(first_name,hire_date,birth_date); にインデックス idx_c を作成します。

実行計画は以下のとおりです。

+----+--------------+------------+-----------+---------+-------+-------+-------+--------+----------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+------------+-----------+---------+-------+-------+-------+--------+----------------------------------------+
| 1 | SIMPLE | employees | NULL | range | idx_c | idx_c | 19 | NULL | 5 | 100.00 | インデックス条件を使用; filesort を使用 |
+----+--------------+------------+-----------+---------+-------+-------+-------+--------+----------------------------------------+

ここで、key_len の長さは 19 で、first_name は等値クエリです。hire_date 列は引き続き使用できます。ただし、hire_date 列は非等値クエリであるため、インデックスで birth_date を引き続き使用することはできません。

オプション D: 名、生年月日、入社日

干渉を避けるため、上記で作成したインデックス idx_c を削除してから idx_d を作成します。

employees(first_name,birth_date,hire_date) にインデックス idx_d を作成します。

実行計画は以下のとおりです。

+----+--------------+------------+-----------+--------+---------------+--------+--------+-------+---------+------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+------------+-----------+--------+---------------+--------+--------+-------+---------+------------------------+
| 1 | SIMPLE | employees | NULL | ref | idx_d | idx_d | 16 | const | 190 | 33.33 | インデックス条件の使用 |
+----+--------------+------------+-----------+--------+---------------+--------+--------+-------+---------+------------------------+

ここで、key_lenの長さは16、first_nameは等値クエリ、述語フィルタリングではbirth_dateは使用されず、上位インデックスを使用するのはfirst_name列のみになりますが、ソートにはbirth_date列が使用されます。上記の実行プランは、SQLが最終的にソートされていないことを示しており、データがbirth_dateに従ってインデックスから順番に取得されることを示しています。

オプション E: 生年月日、名、入社日

干渉を避けるため、上記で作成したインデックス idx_d を削除してから、idx_e を作成します。

employees(birth_date,first_name,hire_date) にインデックス idx_e を作成します。

実行計画は以下のとおりです。

+----+-------------+------------+-----------+---------+--------+--------+---------+----------+-----------------------------+------+--------+----------+----------+-----------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+------------+-----------+---------+--------+--------+---------+----------+-----------------------------+------+--------+----------+----------+-----------------------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 3.33 | where の使用; filesort の使用 |
+----+-------------+------------+-----------+---------+--------+--------+---------+----------+-----------------------------+------+--------+----------+----------+-----------------------------+

ここではインデックスは使用されません。つまり、複合インデックスの先頭にソート列を配置すると、そのソート列は使用できません。

オプション F: 生年月日、入社日、名

干渉を避けるため、上記で作成したインデックス idx_e を削除してから、idx_f を作成します。

employees(birth_date,hire_date,first_name) にインデックス idx_f を作成します。

実行計画は以下のとおりです。

+----+-------------+------------+-----------+---------+--------+--------+---------+----------+-----------------------------+------+--------+----------+----------+-----------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+------------+-----------+---------+--------+--------+---------+----------+-----------------------------+------+--------+----------+----------+-----------------------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 3.33 | where の使用; filesort の使用 |
+----+-------------+------------+-----------+---------+--------+--------+---------+----------+-----------------------------+------+--------+----------+----------+-----------------------------+

オプション E と同様に、ここではインデックスは使用されません。つまり、ソート列は複合インデックスの先頭に配置されている場合は使用できません。

上記の 6 つのインデックス テストを通じて、等号クエリ列と範囲クエリ列を複合インデックスの前に配置すると、複合インデックスは使用できますが、使用される列が異なる可能性があることがわかりました。インデックスを作成する最適な方法は何ですか? MySQL のクエリ オプティマイザは、コストに基づいて最適な実行プランを選択します。上記の 6 つのインデックスの実行コストを見てみましょう。

インデックスコスト
---------- ------------
idx_a 8518
idx_b 8524
idx_c 13
idx_d 228
idx_e 78083
idx_f 78083

上記の費用から、次のことがわかります。

  • idx_a および idx_b: インデックスは範囲クエリ フィールドの先頭を使用するため、インデックスは最初の列のみを使用でき、ソートを排除できず、オーバーヘッドが高くなります。
  • idx_c および idx_d: インデックスは等しい値のクエリ フィールドから始まり、範囲クエリと並べ替えは最後に行われるため、オーバーヘッドは最小限に抑えられます。
  • idx_e および idx_f: インデックスはソート フィールドから始まるため、インデックスが使用できなくなり、テーブル全体のスキャンが必要になり、コストが非常に高くなります。

さらに、idx_c と idx_d をどのように選択すればよいでしょうか? idx_c はインデックスを使用して等値クエリ + 範囲クエリを実行し、データをソートします。idx_d はインデックスを使用して等値クエリ + インデックス条件プッシュダウンクエリを実行し、データを直接取得して順番に取得します。どちらの方法にもそれぞれ長所と短所があります。別の例を見てみましょう。

上記の 6 つのインデックスをテーブルに追加し、次の SQL でどのインデックスが選択されるかを確認します。

mysql> 従業員からのインデックスを表示します。
+-----------+------------+-----------+--------------+--------------+--------------+-------------+---------+----------+-----------+-----------+------------+------------+
| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント |
+-----------+------------+-----------+--------------+--------------+--------------+-------------+---------+----------+-----------+-----------+------------+------------+
| 従業員 | 0 | プライマリ | 1 | 従業員番号 | A | 299468 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_a | 1 | 採用日 | A | 5355 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_a | 2 | first_name | A | 290745 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_a | 3 | 生年月日 | A | 299468 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_b | 1 | 採用日 | A | 6237 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_b | 2 | 生年月日 | A | 297591 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_b | 3 | first_name | A | 299468 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_c | 1 | first_name | A | 1260 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_c | 2 | 採用日 | A | 293517 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_c | 3 | 生年月日 | A | 299468 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_d | 1 | first_name | A | 1218 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_d | 2 | 生年月日 | A | 294525 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_d | 3 | 採用日 | A | 298095 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_e | 1 | 生年月日 | A | 4767 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_e | 2 | first_name | A | 292761 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_e | 3 | 採用日 | A | 299468 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_f | 1 | 生年月日 | A | 4767 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_f | 2 | 採用日 | A | 297864 | NULL | NULL | | BTREE | | |
| 従業員 | 1 | idx_f | 3 | first_name | A | 299468 | NULL | NULL | | BTREE | | |
+-----------+------------+-----------+--------------+--------------+--------------+-------------+---------+----------+-----------+-----------+------------+------------+

SQL1

mysql> emp_no、birth_date、first_name、last_name、gender、hire_date を選択して説明してください 
従業員から 
雇用日 >= '1998-01-01' の場合
そしてfirst_name = 'Ebbe'
birth_date で並べ替えます。
+----+--------------+------------+-----------+---------+-------------------------+-------+-------+-------+---------+----------------------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+------------+-----------+---------+-------------------------+-------+-------+-------+---------+----------------------------------------+
| 1 | SIMPLE | 従業員 | NULL | 範囲 | idx_a、idx_b、idx_c、idx_d | idx_c | 19 | NULL | 5 | 100.00 | インデックス条件を使用; ファイルソートを使用 |
+----+--------------+------------+-----------+---------+-------------------------+-------+-------+-------+---------+----------------------------------------+

ここで、MySQL は idx_c を自動的に選択します。これは、first_name+hire_date の 2 つのフィールドによってデータが 5 行のみにフィルタリングされているためです。データが小さいため、ソートは非常に高速です。逆に、idx_d を選択した場合は、まず first_name フィールドを通じて条件を満たす 190 行のデータをフィルタリングし、次に hire_date を使用してデータをフィルタリングする必要があり、これは非常に手間がかかります。

SQL2

mysql> emp_no、birth_date、first_name、last_name、gender、hire_date を選択して説明してください 
従業員から 
雇用日 >= '1980-01-01' の場合
そしてfirst_name = 'Ebbe'
birth_date で並べ替えます。
+----+--------------+------------+------------+---------+-------------------------+--------+--------+--------+---------+------------------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+--------------+------------+------------+---------+-------------------------+--------+--------+--------+---------+------------------------+
| 1 | SIMPLE | 従業員 | NULL | ref | idx_a、idx_b、idx_c、idx_d | idx_d | 16 | const | 190 | 50.00 | インデックス条件を使用 |
+----+--------------+------------+------------+---------+-------------------------+--------+--------+--------+------------------------+

idx_c を選択した場合、first_name+hire_date フィールドがインデックスを介してデータをフィルター処理した後、データ量が多くなり、ソートが非常に遅くなります。 MySQLは自動的にidx_dを選択し、インデックスのfirst_name列でデータをフィルタリングし、インデックス条件を押し下げてhire_dateフィールドをフィルタリングし、インデックスから順番にデータを取得します。相対的に言えば、idx_dはソートが不要なので速度は速くなります。

（IV）複合指数の概要

1. 複合インデックスを作成するときに、等しい値のクエリが複数ある場合は、選択性の良い列を先頭に、選択性の悪い列を末尾に配置します。

2. 複合インデックスを作成するときに、等値クエリと範囲クエリが含まれる場合、非等値クエリの列の選択性がどれほど優れていても、等値クエリのフィールドを非等値クエリの前に配置する必要があります。

3. 複合インデックスを作成するときに、等値クエリ、範囲クエリ、並べ替え (order by、group by) が含まれる場合は、等値クエリをインデックスの先頭に配置する必要があります。範囲クエリと並べ替えの順序は、実際のシナリオに基づいて決定する必要があります。範囲クエリが最初に来る場合、インデックスの順序は使用できず、filesort が必要です。これは、結果が少ないほどソートのオーバーヘッドが少なくなるため、結果が少ない SQL に適しています。ソートが最初に来る場合、インデックスの順序を使用できますが、データをクエリするためにテーブルに戻る (またはインデックス条件をプッシュダウンする) 必要があります。これは、ソートする必要がなく、データを直接取得できるため、結果が多い SQL に適しています。

4. 複合インデックスを作成するときは、クエリ内で where 句が常に order by 句の前に実行されるため、order by および group by の列をインデックスの先頭に置かないでください。

5. 範囲クエリにインデックスを使用すると、後続のインデックス フィールドが使用できなくなります。ソートがある場合、filesort ソートは削除できません。例: a_b_c インデックス、ここで a>? かつ b = ? order by c の場合、a は使用できますが、b は使用できず、c フィールドにはファイルソートが必要です。

要約する

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

以下もご興味があるかもしれません:
  • MySQL複合インデックスの概要
  • MySQL ジョイントインデックス（複合インデックス）の実装
  • Mysqlの制限を最適化し、100万から1000万までの高速ページングの複合インデックスを参照し、軽量フレームワークに適用します。
  • Mysqlの制限を最適化し、100万から1000万までの高速ページングの複合インデックスを参照し、軽量フレームワークに適用します。
  • MySQL の複合インデックスはどのように機能しますか?

<<:  Echatsチャートの大画面適応を実装する方法

>>:  遭遇したいくつかのブラウザ互換性の問題について簡単に説明します

推薦する

bash スクリプトで ssh/scp コマンドにパスワードを渡す方法の詳細な説明

SSHPASSをインストールする最新のオペレーティング システムでは、sshpass パッケージはデ...

LayUI+Shiroは動的なメニューを実装し、メニュー拡張の例を記憶します

目次1. Maven 依存関係2. メニュー関連クラス1. メインメニュー2. サブメニュー3. S...

Vue の関連ページへのマルチレベルジャンプ (ページドリルダウン) 機能の完全な例

背景プロジェクト開発プロセスでは、前のページから次のページにジャンプする必要に迫られることがよくあり...

Vueで親子コンポーネント通信を実装する方法

目次1. 親コンポーネントと子コンポーネントの関係2. 小道具3. $エミット4. $親V. 結論 ...

MySQL 8.0.25 のインストールと設定方法のグラフィックチュートリアル

MySQL 8.0.25の最新のダウンロードとインストールのチュートリアルは参考になります。具体的な...

node.js チュートリアルの Util モジュールの例の詳細な説明

目次タイプ判定から始める厳格な平等エラーファーストと約束デバッグと出力タイプ判定から始めるJavaS...

Navicat 経由で MySQL にリモート接続する方法

Navicat を使用して IP 経由で直接接続すると、次のようなさまざまなエラーが報告されます: ...

WeChatミニプログラムユーザー認証による携帯電話番号の取得（getPhoneNumber）

序文ミニプログラムには、ユーザーを取得するための非常に便利な API があり、getPhoneNum...

ブリージングカルーセルを実装するネイティブJS

今日は、ネイティブ JS で実装されたブリージング カルーセルを紹介します。効果は次のとおりです。 ...

JavaScript の便利な配列トリック 12 選

目次アレイ重複排除1. from() を新しい Set() メソッドに重ねる2. スプレッド演算子 ...

MySQLオンラインデッドロック分析練習

序文MySQL を学習する際に、MySQL のロック メカニズムについて簡単に理解したことがあると思...

HTML の色に関する参考資料

HTML では、色は 2 つの方法で表現されます。 1 つは、青の場合は blue のように色の名前...

Linux仮想メモリについての簡単な説明

目次起源仮想メモリページングとページテーブルメモリのアドレス指定と割り当て関数プロセスメモリ管理デー...

Vue.js フロントエンドプロジェクト向け多言語ソリューションのアイデアと実践

目次1. 通常どのようなコンテンツを処理する必要があるか2. 基本的な考え方3. 具体的な実践の詳細...

MySQL 8.0.23 のインストールと設定方法のグラフィックチュートリアル (Win10 の場合)

この記事では、MySQL 8.0.23のインストールと設定方法を参考までに紹介します。具体的な内容は...