オプティマイザヒントは、様々な有効範囲レベルで適用されます:

次のテーブルに、使用可能なオプティマイザヒント、それらが影響するオプティマイザ戦略、およびそれらが適用される有効範囲をまとめます。 詳細は後で説明します。

最適化を無効にすると、オプティマイザで使用できなくなります。 最適化を有効にすることは、オプティマイザがステートメントの実行に適用する場合に、オプティマイザが戦略を自由に使用できることを意味します。オプティマイザが使用する必要はありません。

MySQL では、セクション9.7「コメント」 で説明されているように、SQL ステートメントのコメントがサポートされます。 オプティマイザヒントは、/*+ ... */コメント内で指定する必要があります。 つまり、オプティマイザヒントでは、/* コメントのオープン順序の後に + 文字が付いた/* ... */ C 形式のコメント構文のバリアントが使用されます。 例:

/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */

+ 文字の後には空白を使用できます。

パーサーは、SELECT, UPDATE, INSERT, REPLACE ステートメントおよび DELETE ステートメントの最初のキーワードの後にオプティマイザヒントコメントを認識します。 ヒントは、次のコンテキストで使用できます:

ヒントコメントには複数のヒントを含めることができますが、クエリーブロックに複数のヒントコメントを含めることはできません。 これは有効です:

SELECT /*+ BNL(t1) BKA(t2) */ ...

ただし、これは無効です:

SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...

ヒントコメントに複数のヒントが含まれている場合、重複および競合の可能性があります。 次の一般的なガイドラインが適用されます。 特定のヒントタイプについては、ヒントの説明に示されているように、追加のルールが適用される場合があります。

クエリーブロック名は識別子であり、有効な名前とその引用符の方法に関する通常のルールに従います (セクション9.2「スキーマオブジェクト名」 を参照)。

ヒント名、クエリーブロック名および方針名では、大文字と小文字は区別されません。 テーブル名およびインデックス名への参照は、通常の識別子の大 /小文字区別ルールに従います (セクション9.2.3「識別子の大文字と小文字の区別」 を参照)。

結合順序ヒントは、オプティマイザがテーブルを結合する順序に影響します。

JOIN_FIXED_ORDER ヒントの構文:

hint_name([@query_block_name])

その他の結合順序ヒントの構文は、次のとおりです:

hint_name([@query_block_name] tbl_name [, tbl_name] ...)
hint_name(tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)

構文は、次の用語を指します:

例:

SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
    JOIN_ORDER(t4@subq1, t3)
    JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
           WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
             AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);

ヒントは、外部クエリーブロックにマージされる準結合テーブルの動作を制御します。 サブクエリー subq1 および subq2 が準結合に変換されると、テーブル t4@subq1 および t5@subq2 が外部クエリーブロックにマージされます。 この場合、外部クエリーブロックで指定されたヒントによって、t4@subq1、t5@subq2 テーブルの動作が制御されます。

オプティマイザは、次の原則に従って結合順序のヒントを解決します:

テーブルレベルのヒントは次のものに影響します:

これらのヒントタイプは、特定のテーブルまたはクエリーブロック内のすべてのテーブルに適用されます。

テーブルレベルのヒントの構文:

hint_name([@query_block_name] [tbl_name [, tbl_name] ...])
hint_name([tbl_name@query_block_name [, tbl_name@query_block_name] ...])

構文は、次の用語を指します:

例:

SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;

テーブルレベルのヒントは、送信者テーブルではなく、前のテーブルからレコードを受信するテーブルに適用されます。 次のステートメントがあるとします。

SELECT /*+ BNL(t2) */ FROM t1, t2;

オプティマイザが最初に t1 を処理することを選択した場合、t2 からの読取りを開始する前に t1 から行をバッファリングすることで、ブロックネストループ結合が t2 に適用されます。 オプティマイザが最初に t2 を処理することを選択した場合、t2 は送信者テーブルであるため、ヒントは効果がありません。

MERGE ヒントおよび NO_MERGE ヒントには、次の優先順位ルールが適用されます:

インデックスレベルのヒントは、オプティマイザが特定のテーブルまたはインデックスに使用するインデックス処理戦略に影響します。 これらのヒントタイプは、インデックス条件プッシュダウン (ICP)、マルチレンジ読取り (MRR)、インデックスマージおよび範囲最適化の使用に影響します (セクション8.2.1「SELECT ステートメントの最適化」 を参照)。

インデックスレベルのヒントの構文:

hint_name([@query_block_name] tbl_name [index_name [, index_name] ...])
hint_name(tbl_name@query_block_name [index_name [, index_name] ...])

構文は、次の用語を指します:

例:

SELECT /*+ INDEX_MERGE(t1 f3, PRIMARY) */ f2 FROM t1
  WHERE f1 = 'o' AND f2 = f3 AND f3 <= 4;
SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
INSERT INTO t3(f1, f2, f3)
  (SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
   WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1
   AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
SELECT /*+ SKIP_SCAN(t1 PRIMARY) */ f1, f2
  FROM t1 WHERE f2 > 40;

次の例ではインデックスマージヒントを使用しますが、他のインデックスレベルのヒントは、optimizer_switch システム変数またはインデックスヒントに関して、オプティマイザヒントの無視と優先順位に関して同じ原則に従います。

t1 テーブルに a, b, c および d カラムがあり、i_a、i_b および i_c という名前のインデックスが a、b、および c にそれぞれ存在するとします:

SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1
  WHERE a = 1 AND b = 2 AND c = 3 AND d = 4;

この場合、(i_a, i_b, i_c) にはインデックスマージが使用されます。

SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1
  WHERE b = 1 AND c = 2 AND d = 3;

この場合、(i_b, i_c) にはインデックスマージが使用されます。

/*+ INDEX_MERGE(t1 i_a, i_b) NO_INDEX_MERGE(t1 i_b) */

同じテーブルに先行するヒントがあるため、NO_INDEX_MERGE は無視されます。

/*+ NO_INDEX_MERGE(t1 i_a, i_b) INDEX_MERGE(t1 i_b) */

同じテーブルに先行するヒントがあるため、INDEX_MERGE は無視されます。

INDEX_MERGE および NO_INDEX_MERGE オプティマイザヒントには、次の優先順位ルールが適用されます:

サブクエリーヒントは、準結合変換を使用するかどうか、許可する準結合戦略、および準結合を使用しない場合はサブクエリー実体化と IN から EXISTS への変換のどちらを使用するかに影響します。 これらの最適化の詳細は、セクション8.2.2「サブクエリー、導出テーブル、ビュー参照および共通テーブル式の最適化」 を参照してください。

準結合戦略に影響するヒントの構文は、次のとおりです:

hint_name([@query_block_name] [strategy [, strategy] ...])

構文は、次の用語を指します:

あるサブクエリーが別のサブクエリー内にネストされ、その両方が外部クエリーの準結合にマージされる場合、最も内側のクエリーに対する準結合戦略の指定は無視されます。 SEMIJOIN および NO_SEMIJOIN ヒントを使用して、このようなネストしたサブクエリーの準結合変換を有効化または無効化できます。

DUPSWEEDOUT が無効になっている場合、オプティマイザによって最適ではないクエリー計画が生成されることがあります。 これは、最長一致検索中のヒューリスティックプルーニングが原因で発生します。これは、optimizer_prune_level=0 を設定することで回避できます。

例:

SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

サブクエリー実体化または IN から EXISTS への変換のどちらを使用するかに影響するヒントの構文は、次のとおりです:

SUBQUERY([@query_block_name] strategy)

ヒント名は常に SUBQUERY です。

SUBQUERY ヒントの場合、これらの strategy 値は許可されます: INTOEXISTS、MATERIALIZATION。

例:

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);

準結合および SUBQUERY ヒントの場合、先頭の@query_block_name でヒントが適用されるクエリーブロックを指定します。 ヒントに先行する@query_block_name が含まれていない場合、ヒントは発生したクエリーブロックに適用されます。 クエリーブロックに名前を割り当てるには、クエリーブロックのネーミングのためのオプティマイザヒント を参照してください。

ヒントコメントに複数のサブクエリーヒントが含まれている場合は、最初のヒントが使用されます。 そのタイプの他の後続のヒントがある場合は、警告が生成されます。 他のタイプの次のヒントは、暗黙的に無視されます。

MAX_EXECUTION_TIME ヒントは、SELECT ステートメントでのみ使用できます。 サーバーがステートメントを終了するまでに、ステートメントの実行が許可される期間に制限 N (ミリ秒単位のタイムアウト値) を設定します:

MAX_EXECUTION_TIME(N)

タイムアウトが 1 秒 (1000 ミリ秒) の例:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

MAX_EXECUTION_TIME(N) ヒントは、N ミリ秒のステートメント実行タイムアウトを設定します。 このオプションが指定されていないか、N が 0 の場合、max_execution_time システム変数によって設定されたステートメントタイムアウトが適用されます。

MAX_EXECUTION_TIME ヒントは次のように適用できます:

SET_VAR ヒントは、システム変数のセッション値を一時的に設定します (単一のステートメントの実行中)。 例:

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;

SET_VAR ヒントの構文:

SET_VAR(var_name = value)

var_name は、セッション値を持つシステム変数に名前を付けます (ただし、後で説明するように、そのようなすべての変数に名前を付けることはできません)。value は変数に割り当てる値で、値はスカラーである必要があります。

次のステートメントで示すように、SET_VAR では一時変数が変更されます:

mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+
mysql> SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               0 |
+-----------------+
mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+

SET_VAR では、変数値を保存およびリストアする必要はありません。 これにより、複数のステートメントを単一のステートメントで置き換えることができます。 次の一連のステートメントについて考えてみます:

SET @saved_val = @@SESSION.var_name;
SET @@SESSION.var_name = value;
SELECT ...
SET @@SESSION.var_name = @saved_val;

順序は、次の単一のステートメントで置換できます:

SELECT /*+ SET_VAR(var_name = value) ...

スタンドアロンの SET ステートメントでは、セッション変数のネーミングに次の構文を使用できます:

SET SESSION var_name = value;
SET @@SESSION.var_name = value;
SET @@.var_name = value;

SET_VAR ヒントはセッション変数にのみ適用されるため、セッションスコープは暗黙的であり、SESSION、@@SESSION. および@@は必要なく、許可されません。 明示的なセッションインジケータ構文を含めると、SET_VAR ヒントは無視され、警告が表示されます。

すべてのセッション変数を SET_VAR で使用できるわけではありません。 個々のシステム変数の説明は、各変数がヒント可能かどうかを示します。セクション5.1.8「サーバーシステム変数」 を参照してください。 また、システム変数を SET_VAR で使用して、実行時にチェックすることもできます。 変数がヒント可能でない場合は、警告が発生します:

mysql> SELECT /*+ SET_VAR(collation_server = 'utf8') */ 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 4537
Message: Variable 'collation_server' cannot be set using SET_VAR hint.

SET_VAR 構文では単一の変数のみを設定できますが、複数のヒントを指定して複数の変数を設定できます:

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off')
           SET_VAR(max_heap_table_size = 1G) */ 1;

同じ変数名を持つ複数のヒントが同じステートメントに出現すると、最初のヒントが適用され、他のヒントは警告付きで無視されます:

SELECT /*+ SET_VAR(max_heap_table_size = 1G)
           SET_VAR(max_heap_table_size = 3G) */ 1;

この場合、2 番目のヒントは無視され、競合しているという警告が表示されます。

指定された名前を持つシステム変数がない場合、または変数値が正しくない場合、SET_VAR ヒントは警告付きで無視されます:

SELECT /*+ SET_VAR(max_size = 1G) */ 1;
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;

最初のステートメントには、max_size 変数はありません。 2 番目のステートメントでは、mrr_cost_based は on または off の値を取るため、yes に設定しようとする試みは正しくありません。 いずれの場合も、ヒントは警告付きで無視されます。

SET_VAR ヒントはステートメントレベルでのみ使用できます。 サブクエリーで使用する場合、ヒントは無視され、警告が表示されます。

レプリカは、レプリケートされたステートメントの SET_VAR ヒントを無視して、セキュリティの問題が発生する可能性を回避します。

RESOURCE_GROUP オプティマイザヒントは、リソースグループの管理に使用されます (セクション5.1.16「リソースグループ」 を参照)。 このヒントは、ステートメントを実行するスレッドを名前付きリソースグループに一時的に割り当てます (ステートメントの期間中)。 RESOURCE_GROUP_ADMIN または RESOURCE_GROUP_USER 権限が必要です。

例:

SELECT /*+ RESOURCE_GROUP(USR_default) */ name FROM people ORDER BY name;
INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);

RESOURCE_GROUP ヒントの構文:

RESOURCE_GROUP(group_name)

group_name は、ステートメントの実行中にスレッドを割り当てるリソースグループを示します。 グループが存在しない場合、警告が発生し、ヒントは無視されます。

RESOURCE_GROUP ヒントは、最初のステートメントキーワード (SELECT, INSERT, REPLACE, UPDATE または DELETE) の後に指定する必要があります。

RESOURCE_GROUP の代替手段として、一時的にスレッドをリソースグループに割り当てる SET RESOURCE GROUP ステートメントがあります。 セクション13.7.2.4「SET RESOURCE GROUP ステートメント」を参照してください。

テーブルレベル、インデックスレベルおよびサブクエリーオプティマイザヒントでは、特定のクエリーブロックに引数構文の一部として名前を付けることができます。 これらの名前を作成するには、QB_NAME ヒントを使用します。これにより、名前が発生したクエリーブロックに名前が割り当てられます:

QB_NAME(name)

QB_NAME ヒントを使用すると、他のヒントが適用されるクエリーブロックを明確にすることができます。 また、複雑なステートメントを理解しやすくするために、すべての非クエリーブロック名ヒントを単一のヒントコメント内に指定することもできます。 次のステートメントについて考えてみます:

SELECT ...
  FROM (SELECT ...
  FROM (SELECT ... FROM ...)) ...

QB_NAME ヒントは、ステートメントのクエリーブロックに名前を割り当てます:

SELECT /*+ QB_NAME(qb1) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

他のヒントでは、これらの名前を使用して適切なクエリーブロックを参照できます:

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

その結果、次のようになります:

クエリーブロック名は識別子であり、有効な名前とその引用符の方法に関する通常のルールに従います (セクション9.2「スキーマオブジェクト名」 を参照)。 たとえば、空白を含むクエリーブロック名は引用符で囲む必要があります。引用符はバックティックを使用して使用できます:

SELECT /*+ BKA(@`my hint name`) */ ...
  FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...

ANSI_QUOTES SQL モードが有効な場合は、クエリーブロック名を二重引用符で囲むこともできます:

SELECT /*+ BKA(@"my hint name") */ ...
  FROM (SELECT /*+ QB_NAME("my hint name") */ ...) ...