8.3.5 マルチカラムインデックス

MySQL は複合インデックス (つまり、複数のカラムに対するインデックス) を作成できます。インデックスは最大 16 カラムで構成できます。特定のデータ型では、カラムのプリフィクスにインデックスを設定できます (セクション8.3.4「カラムインデックス」を参照してください)。

MySQL では、インデックスで、すべてのカラムをテストするクエリーまたは最初のカラム、最初の 2 つのカラム、最初の 3 つのカラムというようにテストするクエリーにマルチカラムインデックスを使用できます。インデックス定義の正しい順序でカラムを指定する場合、単一の複合インデックスにより、同じテーブルへの複数の種類のクエリーを高速化できます。

マルチカラムインデックスは、インデックス設定されたカラムの値を連結して作成された値を格納する行である、ソート済みの配列とみなすことができます。

注記

複合インデックスの代わりに、ほかのカラムの情報に基づいて「ハッシュされた」カラムを導入できます。このカラムが短く、十分に一意で、インデックスが設定されている場合は、多数のカラムへの「広範な」インデックスより速くなる可能性があります。MySQL では、この追加カラムをきわめて簡単に使用できます。

SELECT * FROM tbl_name
  WHERE hash_col=MD5(CONCAT(val1,val2))
  AND col1=val1 AND col2=val2;

テーブルが次のような仕様であるとします。

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

name インデックスは、last_name カラムと first_name カラムに対するインデックスです。このインデックスは、last_name 値と first_name 値の組み合わせに既知の範囲の値を指定するクエリーで、ルックアップに使用できます。そのカラムはインデックスの左端のプリフィクスであるため、last_name 値だけを指定するクエリーにも使用できます (このセクションで後述するように)。そのため、name インデックスは、次のクエリーでのルックアップに使用されます。

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

ただし、name インデックスは次のクエリーでのルックアップには使用されません。

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

次の SELECT ステートメントを発行するとします。

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

col1 と col2 に対するマルチカラムインデックスが存在する場合、該当する行を直接フェッチできます。col1 および col2 に対して個別の単一カラムのインデックスが存在する場合、オプティマイザは、インデックスマージ最適化 (セクション8.2.1.4「インデックスマージの最適化」を参照してください) の使用を試みるか、またはより多くの行を除外するインデックスを判断して、そのインデックスを使用して行をフェッチすることで、もっとも制限の厳しいインデックスを見つけようとします。

テーブルにマルチカラムインデックスがある場合、オプティマイザは、インデックスの左端のプリフィクスを使用して行をルックアップできます。たとえば、(col1, col2, col3) に 3 カラムのインデックスがある場合、(col1)、(col1, col2)、および (col1, col2, col3) に対して、インデックス検索機能を使用できます。

カラムがインデックスの左端のプリフィクスを形成していない場合、MySQL はこのインデックスを使用してルックアップを実行できません。ここに示す SELECT ステートメントがあるとします。

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

(col1, col2, col3) にインデックスが存在する場合、最初の 2 つのクエリーだけがインデックスを使用します。3 つめと 4 つめのクエリーには、インデックス設定されたカラムがかかわりますが、(col2) と (col2, col3) は (col1, col2, col3) の左端のプリフィクスではありません。