MySQL インデックスの使用方法 (単一列インデックスと複数列インデックス)

1. 単一列インデックス

どの列にインデックスを作成するかを選択することは、パフォーマンス最適化プロセスにおける最も重要なステップの 1 つです。インデックス作成に考慮できる列には、WHERE 句に表示される列と JOIN 句に表示される列の 2 つの主な種類があります。次のクエリを考えてみましょう。

年齢を選択 ## 人からのインデックスは使用しない where firstname='Mike' ## インデックスの使用を検討 AND lastname='Sullivan ## インデックスの使用を検討

このクエリは前のクエリとは少し異なりますが、それでも単純です。 age は Select 部分で参照されるため、MySQL は列選択操作を制限するために age を使用しません。したがって、このクエリでは、age 列にインデックスを作成する必要はありません。

より複雑な例を次に示します。

people.age を選択します。## インデックス town.name は使用しないでください。## インデックスは使用しないでください。FROM people LEFT JOIN town ON people.townid=town.townid ## インデックスの使用を検討してください。Where firstname='Mike' ## インデックスの使用を検討してください。AND lastname='Sullivan' ## インデックスの使用を検討してください。

前の例と同様に、firstname と lastname は Where 句に出現するため、これら 2 つの列にインデックスを作成する必要があります。さらに、town テーブルの townid 列が結合句に出現するため、この列にインデックスを作成することを検討する必要があります。

では、Where 句と Join 句に現れるすべての列にインデックスを付ける必要があると単純に考えればよいのでしょうか?ほぼそうですが、まだ完全ではありません。列を比較するために使用される演算子の種類も考慮する必要があります。 MySQL は、<、<=、=、>、>=、BETWEEN、IN、および場合によっては LIKE の演算子に対してのみインデックスを使用します。

他のオペランドがワイルドカード文字 (% または _) で始まらない場合、LIKE 操作でインデックスを使用できます。

例えば：

people から peopleid を選択 where firstname LIKE 'Mich%'

このクエリはインデックスを使用しますが、次のクエリはインデックスを使用しません。

people FROM people、firstname LIKE '%ike' の peopleid を選択します。

2. 複数列インデックス

インデックスは、単一列インデックスまたは複数列インデックスにすることができます。以下では、具体的な例を使用して、これら 2 つのインデックスの違いを説明します。次のような people テーブルがあるとします。

テーブル人を作成する（  
peopleid SMALLINT NOT NULL AUTO_INCREMENT、  
名 CHAR(50) NOT NULL,  
姓 CHAR(50) NOT NULL,  
年齢 SMALLINT NOT NULL、  
townid SMALLINT NOT NULL、  
主キー (peopleid) );

people テーブルに挿入したデータは次のとおりです。

このデータ スニペットには、「Mikes」という名前の人が 4 人 (姓が Sullivans が 2 人、姓が McConnells が 2 人)、年齢が 17 歳の人が 2 人、珍しい名前が Joe Smith の人が 1 人います。

このテーブルの主な目的は、指定されたユーザーの名、姓、年齢に基づいて、対応する peopleid を返すことです。たとえば、17 歳の Mike Sullivan というユーザーの peopleid を見つけたいとします。

人物IDを選択 
人々から  
firstname='Mike'の場合  
   AND 姓='Sullivan' AND 年齢=17;

クエリを実行するたびに MySQL がテーブル全体をスキャンしないようにするため、インデックスの使用を検討する必要があります。

まず、firstname、lastname、age などの単一の列にインデックスを作成することを検討できます。 firstname 列にインデックスを作成すると (Alter TABLE people ADD INDEX firstname (firstname);)、MySQL はすぐにこのインデックスを使用して、firstname='Mike' のレコードに検索範囲を制限し、次にこの「中間結果セット」に対して他の検索条件を実行します。まず、lastname が "Sullivan" と等しくないレコードを除外し、次に age が 17 と等しくないレコードを除外します。レコードがすべての検索条件を満たすと、MySQL は最終的な検索結果を返します。

firstname 列にはインデックスが付けられているため、MySQL はテーブル全体をスキャンするよりもはるかに効率的ですが、それでも MySQL では実際に必要な数よりもはるかに多くのレコードをスキャンする必要があります。 firstname 列のインデックスを削除して lastname 列または age 列のインデックスを作成することもできますが、全体的には、どの列にインデックスを作成しても検索効率は同様です。

検索効率を向上させるには、複数列のインデックスの使用を検討する必要があります。 firstname、lastname、age の複数列インデックスを作成すると、MySQL は 1 回の検索で正しい結果を見つけることができます。この複数列インデックスを作成するための SQL コマンドは次のとおりです。

テーブルを変える人々  
インデックス fname_lname_age (firstname,lastname,age); を追加します。

インデックス ファイルは B ツリー形式で保存されるため、MySQL はすぐに適切な名、適切な姓、最後に適切な年齢に移動できます。データ ファイル内のレコードをスキャンしなくても、MySQL はターゲット レコードを正しく検出しました。

では、firstname、lastname、age の 3 つの列にそれぞれ単一列のインデックスを作成した場合、firstname、lastname、age に複数列のインデックスを作成した場合と同じ効果が得られるでしょうか。

答えは「いいえ」です。この2つはまったく異なります。クエリを実行する場合、MySQL は 1 つのインデックスのみを使用できます。単一列インデックスが 3 つある場合、MySQL は最も制限の厳しいインデックスを選択しようとします。ただし、最も制限の厳しい単一列インデックスであっても、firstname、lastname、age 列の複数列インデックスよりははるかに制限が緩くなります。

3. 複数列インデックスの左端プレフィックス

複数列インデックスには、左端プレフィックスと呼ばれる概念に反映される別の利点もあります。前の例を引き続き検討すると、firstname、lastname、age 列に複数列のインデックスが作成され、このインデックスを fname_lname_age と呼びます。 MySQL は、検索条件が次の列の組み合わせである場合に fname_lname_age インデックスを使用します。

名、姓、年齢
名、姓
ファーストネーム

別の観点から見ると、これは (firstname, lastname, age)、(firstname, lastname)、(firstname) の組み合わせでインデックスを作成することと同じです。次のクエリはすべて fname_lname_age インデックスを使用できます。

peopleからpeopleidを選択  
firstname='Mike' AND lastname='Sullivan' AND age='17'; の場合、

peopleからpeopleidを選択  
firstname='Mike' かつ lastname='Sullivan' の場合、

peopleからpeopleidを選択  
firstname='Mike'; の場合

次のクエリではfname_lname_ageインデックスを使用できません。

peopleからpeopleidを選択  
lastname='Sullivan'; の場合

peopleからpeopleidを選択  
age='17'の場合

peopleからpeopleidを選択  
lastname='Sullivan' AND age='17'; の場合

この記事はこれで終わりです。後ほど、123WORDPRESS.COM の編集者が MySQL インデックスに関する記事をさらに紹介する予定です。