13.2.10.10 サブクエリーの最適化

開発が進行中であるため、長期にわたって信頼できる最適化のヒントはありません。次のリストは、試してみる価値のある、興味深いいくつかのコツを示しています。

• サブクエリーで、行の数や順序に影響を与えるサブクエリー句を使用します。例:

  SELECT * FROM t1 WHERE t1.column1 IN
    (SELECT column1 FROM t2 ORDER BY column1);
  SELECT * FROM t1 WHERE t1.column1 IN
    (SELECT DISTINCT column1 FROM t2);
  SELECT * FROM t1 WHERE EXISTS
    (SELECT * FROM t2 LIMIT 1);

• 結合をサブクエリーに置き換えます。たとえば、次を試してみてください:

  SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
    SELECT column1 FROM t2);

  次の代替として:

  SELECT DISTINCT t1.column1 FROM t1, t2
    WHERE t1.column1 = t2.column1;

• サブクエリーをサポートしていない古いバージョンの MySQL との互換性のために、一部のサブクエリーを結合に変換できます。ただし、場合によっては、サブクエリーを結合に変換するとパフォーマンスが向上することがあります。セクション13.2.10.11「サブクエリーの結合としての書き換え」を参照してください。

• 句をサブクエリーの外部から内部に移動します。たとえば、次のクエリーを使用してください:

  SELECT * FROM t1
    WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

  次のクエリーの代替として:

  SELECT * FROM t1
    WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

  別の例として、このクエリーを使用してください:

  SELECT (SELECT column1 + 5 FROM t1) FROM t2;

  次のクエリーの代替として:

  SELECT (SELECT column1 FROM t1) + 5 FROM t2;

• 相関サブクエリーの代わりに行サブクエリーを使用します。たとえば、次のクエリーを使用してください:

  SELECT * FROM t1
    WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

  次のクエリーの代替として:

  SELECT * FROM t1
    WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
                  AND t2.column2=t1.column2);

• a <> ALL (...) ではなく、NOT (a = ANY (...)) を使用します。

• x=1 OR x=2 ではなく、x = ANY (table containing (1,2)) を使用します。

• EXISTS ではなく、= ANY を使用します。

• 常に 1 行を返す非相関サブクエリーの場合、IN は常に = より低速です。たとえば、次のクエリーを使用してください:

  SELECT * FROM t1
    WHERE t1.col_name = (SELECT a FROM t2 WHERE b = some_const);

  次のクエリーの代替として:

  SELECT * FROM t1
    WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

これらのコツにより、プログラムは速くなる場合も遅くなる場合もあります。BENCHMARK() 関数などの MySQL 機能を使用すると、現在の状況に何が役立つかについてのアイデアを得ることができます。セクション12.14「情報関数」を参照してください。

MySQL 自体が行ういくつかの最適化を次に示します。

• MySQL は、非相関サブクエリーを 1 回だけ実行します。特定のサブクエリーが実際に非相関になるようにするには、EXPLAIN を使用します。

• MySQL は、サブクエリー内の選択リストカラムにインデックスが設定される可能性を利用しようとして、IN、ALL、ANY、および SOME サブクエリーを書き換えます。

• MySQL は、次の形式のサブクエリーをインデックス検索関数に置き換えます。この関数は、EXPLAIN によって特殊な結合型 (unique_subquery または index_subquery) として記述されます。

  ... IN (SELECT indexed_column FROM single_table ...)

• MySQL は次の形式の式を、NULL 値または空のセットが含まれていないかぎり、MIN() または MAX() を含む式に拡張します。

  value {ALL|ANY|SOME} {> | < | >= | <=} (uncorrelated subquery)

  たとえば、次の WHERE 句

  WHERE 5 > ALL (SELECT x FROM t)

  は、オプティマイザによって次のように処理される可能性があります。

  WHERE 5 > (SELECT MAX(x) FROM t)

「MySQL Internals: How MySQL Transforms Subqueries」も参照してください。