8.2.1.4 インデックスマージの最適化

[+/-]

8.2.1.4.1 インデックスマージ共通集合アクセスアルゴリズム

8.2.1.4.2 インデックスマージ和集合アクセスアルゴリズム

8.2.1.4.3 インデックスマージソート和集合アクセスアルゴリズム

インデックスマージメソッドは、複数の range スキャンによって、行を取得しそれらの結果を 1 つにマージするために使用されます。このマージによって、その基盤となるスキャンの和集合、共通集合、または共通集合の和集合を生成できます。このアクセスメソッドは、1 つのテーブルからのインデックススキャンをマージします。複数のテーブルにわたるスキャンはマージしません。

EXPLAIN 出力では、インデックスマージメソッドは type カラムに index_merge と表示されます。この場合、key カラムには使用されたインデックスのリストが含まれ、key_len にはそれらのインデックスの最長のキーパートのリストが含まれます。

例:

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key=30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1=t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1=1
  AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);

インデックスマージメソッドにはいくつかのアクセスアルゴリズムがあります (EXPLAIN 出力の Extra フィールドで確認されます)。

• Using intersect(...)

• Using union(...)

• Using sort_union(...)

次のセクションでは、これらのメソッドについて詳しく説明します。

注記

インデックスマージ最適化アルゴリズムには次の既知の不具合があります。

• クエリーに AND/OR の深いネストのある複雑な WHERE 句があり、MySQL が最適なプランを選択しない場合、次の同一律を使用して、項を分配してみてください。

  (x AND y) OR z = (x OR z) AND (y OR z)
  (x OR y) AND z = (x AND z) OR (y AND z)

• インデックスマージは全文インデックスには適用できません。将来の MySQL リリースでこれらを扱うように、それを拡張する予定です。

• MySQL 5.6.6 より前では、一部のキーに対して範囲スキャンが使用可能な場合、オプティマイザはインデックスマージ和集合またはインデックスマージソート和集合アルゴリズムを使用することを考慮しません。たとえば、次のクエリーを考慮します。

  SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

  このクエリーでは、2 つのプランが使用可能です。

  • (goodkey1 < 10 OR goodkey2 < 20) 条件を使用したインデックスマージスキャン。

  • badkey < 30 条件を使用した範囲スキャン。

  ただし、オプティマイザは 2 つめのプランしか考慮しません。

インデックスマージアクセスメソッドの可能性のあるさまざまなバリアントとその他のアクセスメソッドとの選択は、使用可能な各種オプションのコスト見積もりに基づきます。

8.2.1.4.1 インデックスマージ共通集合アクセスアルゴリズム

このアクセスアルゴリズムは、WHERE 句が、AND で結合されたさまざまなキーに対する複数の範囲条件に変換され、各条件が次のいずれかである場合に採用できます。

• この形式では、インデックスには正確に N 個のパートがあります (つまり、すべてのインデックスパートがカバーされます)。

  key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

• InnoDB テーブルの主キーに対する範囲条件。

例:

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name
  WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

インデックスマージ共通集合アルゴリズムは、使用されたすべてのインデックスの同時スキャンを実行し、マージされたインデックススキャンから受け取る行シーケンスの共通集合を生成します。

クエリーに使用されているすべてのカラムが、使用されるインデックスによってカバーされている場合、完全なテーブル行は取得されません (この場合、EXPLAIN 出力の Extra フィールドに Using index が含まれます)。次はそのようなクエリーの例です。

SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;

使用されるインデックスで、クエリーに使用されているすべてのカラムがカバーされない場合、使用されているすべてのキーの範囲条件が満たされている場合にのみ、完全な行が取得されます。

マージされた条件のいずれかが InnoDB テーブルの主キーに対する条件である場合、それは行の取得には使用されませんが、ほかの条件を使用して取得された行をフィルタ処理するために使用されます。

8.2.1.4.2 インデックスマージ和集合アクセスアルゴリズム

このアルゴリズムの適用基準はインデックスマージメソッド共通集合アルゴリズムの場合と似ています。このアルゴリズムは、テーブルの WHERE 句が、OR で組み合わされたさまざまなキーに対する複数の範囲条件に変換されており、各条件が次のいずれかである場合に採用できます。

• この形式では、インデックスには正確に N 個のパートがあります (つまり、すべてのインデックスパートがカバーされます)。

  key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

• InnoDB テーブルの主キーに対する範囲条件。

• インデックスマージメソッド共通集合アルゴリズムを適用できる条件。

例:

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
  (key3='foo' AND key4='bar') AND key5=5;

8.2.1.4.3 インデックスマージソート和集合アクセスアルゴリズム

このアクセスアルゴリズムは、WHERE 句が、OR で組み合わされた複数の範囲条件に変換されているが、インデックスマージメソッド和集合アルゴリズムを適用できない場合に採用されます。

例:

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

ソート和集合アルゴリズムと和集合アルゴリズムの違いは、ソート和集合アルゴリズムでは、行を返す前にまずすべての行の行 ID をフェッチし、それらをソートする必要があることです。