Documentation Home
MySQL 8.0 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 36.1Mb
PDF (A4) - 36.2Mb


このページは機械翻訳したものです。

8.9.3 オプティマイザヒント

オプティマイザ戦略を制御する方法の 1 つは、optimizer_switch システム変数を設定することです (セクション8.9.2「切り替え可能な最適化」 を参照)。 この変数を変更すると、後続のすべてのクエリーの実行に影響します。あるクエリーと別のクエリーに影響を与えるには、各クエリーの前に optimizer_switch を変更する必要があります。

オプティマイザを制御する別の方法は、オプティマイザヒントを使用することです。オプティマイザヒントは、個々のステートメント内で指定できます。 オプティマイザヒントはステートメントごとに適用されるため、optimizer_switch を使用して達成できるよりも詳細にステートメントの実行計画を制御できます。 たとえば、ステートメントのあるテーブルに対して最適化を有効にし、別のテーブルに対して最適化を無効にできます。 ステートメント内のヒントは、optimizer_switch フラグよりも優先されます。

例:

SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);

ここで説明するオプティマイザヒントは、セクション8.9.4「インデックスヒント」 で説明されているインデックスヒントとは異なります。 オプティマイザヒントとインデックスヒントは、別々に、または一緒に使用できます。

オプティマイザヒントの概要

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

  • グローバル: ヒントはステートメント全体に影響

  • クエリーブロック: ヒントは、ステートメント内の特定のクエリーブロックに影響

  • Table-level: ヒントは、クエリーブロック内の特定のテーブルに影響

  • Index-level: ヒントは、テーブル内の特定のインデックスに影響

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

表 8.2 使用可能なオプティマイザヒント

ヒント名 説明 適用可能なスコープ
BKA, NO_BKA バッチキーアクセス結合処理に影響 クエリーブロック、テーブル
BNL, NO_BNL MySQL 8.0.20 より前: Block Nested-Loop 結合処理 (MySQL 8.0.18 以降) に影響: ハッシュ結合の最適化にも影響します (MySQL 8.0.20 以降): ハッシュ結合の最適化にのみ影響 クエリーブロック、テーブル
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN 実体化導出テーブルに対する導出条件プッシュダウン最適化の使用または無視 (MySQL 8.0.22 で追加) クエリーブロック、テーブル
GROUP_INDEX, NO_GROUP_INDEX GROUP BY 操作でのインデックススキャンのための指定したインデックスの使用または無視 (MySQL 8.0.20 で追加) インデックス
HASH_JOIN, NO_HASH_JOIN ハッシュ結合の最適化に影響 (MySQL 8.0.18 のみ) クエリーブロック、テーブル
INDEX, NO_INDEX JOIN_INDEXGROUP_INDEX および ORDER_INDEX の組合せとして、または NO_JOIN_INDEXNO_GROUP_INDEX および NO_ORDER_INDEX (MySQL 8.0.20 に追加) の組合せとして機能 インデックス
INDEX_MERGE, NO_INDEX_MERGE インデックスマージの最適化に影響 Table、インデックス
JOIN_FIXED_ORDER 結合順序に FROM 句で指定されたテーブルの順序を使用 クエリーブロック
JOIN_INDEX, NO_JOIN_INDEX 任意のアクセス方法に指定されたインデックスを使用または無視します (MySQL 8.0.20 で追加) インデックス
JOIN_ORDER 結合順序のヒントに指定されたテーブルの順序を使用 クエリーブロック
JOIN_PREFIX 結合順序の最初のテーブルにヒントで指定されたテーブル順序を使用 クエリーブロック
JOIN_SUFFIX 結合順序の最後のテーブルにヒントで指定されたテーブル順序を使用 クエリーブロック
MAX_EXECUTION_TIME ステートメントの実行時間の制限 グローバル
MERGE, NO_MERGE 外部クエリーブロックへの導出テーブル/ビューのマージに影響 Table
MRR, NO_MRR マルチレンジ読取り最適化に影響 Table、インデックス
NO_ICP インデックス条件プッシュダウンの最適化に影響 Table、インデックス
NO_RANGE_OPTIMIZATION 範囲の最適化に影響 Table、インデックス
ORDER_INDEX, NO_ORDER_INDEX 指定したインデックスを使用または無視して行をソートします (MySQL 8.0.20 で追加) インデックス
QB_NAME クエリーブロックに名前を割り当てます クエリーブロック
RESOURCE_GROUP ステートメントの実行中にリソースグループを設定 グローバル
SEMIJOIN, NO_SEMIJOIN 準結合戦略に影響します。MySQL 8.0.17 以降、これはアンチ結合にも適用されます クエリーブロック
SKIP_SCAN, NO_SKIP_SCAN スキップスキャンの最適化に影響 Table、インデックス
SET_VAR ステートメントの実行中に変数を設定 グローバル
SUBQUERY 実体化、IN- から -EXISTS サブクエリー戦略に影響 クエリーブロック

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

オプティマイザヒントの構文

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

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

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

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

  • クエリーステートメントおよびデータ変更ステートメントの開始時:

    SELECT /*+ ... */ ...
    INSERT /*+ ... */ ...
    REPLACE /*+ ... */ ...
    UPDATE /*+ ... */ ...
    DELETE /*+ ... */ ...
  • クエリーブロックの先頭:

    (SELECT /*+ ... */ ... )
    (SELECT ... ) UNION (SELECT /*+ ... */ ... )
    (SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
    UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
    INSERT ... SELECT /*+ ... */ ...
  • EXPLAIN で始まるヒント可能ステートメント。 例:

    EXPLAIN SELECT /*+ ... */ ...
    EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

    これは、EXPLAIN を使用してオプティマイザヒントが実行計画に与える影響を確認できることを意味します。 EXPLAIN の直後に SHOW WARNINGS を使用して、ヒントの使用方法を確認します。 次の SHOW WARNINGS によって表示される拡張 EXPLAIN 出力は、使用されたヒントを示します。 無視されたヒントは表示されません。

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

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

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

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

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

  • ヒントの複製: /*+ MRR(idx1) MRR(idx1) */などのヒントの場合、MySQL では最初のヒントが使用され、重複ヒントに関する警告が発行されます。

  • 競合するヒント: /*+ MRR(idx1) NO_MRR(idx1) */などのヒントの場合、MySQL は最初のヒントを使用し、競合する 2 つ目のヒントに関する警告を発行します。

クエリーブロック名は識別子であり、有効な名前とその引用符の方法に関する通常のルールに従います (セクション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]] ...)

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

  • hint_name : 次のヒント名を使用できます:

    • JOIN_FIXED_ORDER: オプティマイザが、FROM 句に出現する順序を使用してテーブルを結合するように強制します。 これは、SELECT STRAIGHT_JOIN の指定と同じです。

    • JOIN_ORDER: 指定されたテーブルの順序を使用してテーブルを結合するようオプティマイザに指示します。 ヒントは、指定したテーブルに適用されます。 オプティマイザは、指定されたテーブルの間を含め、結合順序のどこにも名前が付いていないテーブルを配置できます。

    • JOIN_PREFIX: 結合実行計画の最初のテーブルに指定されたテーブル順序を使用してテーブルを結合するようオプティマイザに指示します。 ヒントは、指定したテーブルに適用されます。 オプティマイザは、他のすべてのテーブルを指定されたテーブルの後に配置します。

    • JOIN_SUFFIX: 結合実行計画の最後のテーブルに対して指定されたテーブルの順序を使用してテーブルを結合するようオプティマイザに指示します。 ヒントは、指定したテーブルに適用されます。 オプティマイザは、他のすべてのテーブルを名前付きテーブルの前に配置します。

  • tbl_name: ステートメントで使用されるテーブルの名前。 テーブルに名前を付けるヒントは、名前を付けるすべてのテーブルに適用されます。 JOIN_FIXED_ORDER ヒントでは、テーブルに名前が付けられず、クエリーブロックの FROM 句のすべてのテーブルに適用されます。

    テーブルにエイリアスがある場合、ヒントはテーブル名ではなくエイリアスを参照する必要があります。

    ヒントのテーブル名はスキーマ名で修飾できません。

  • query_block_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@subq1t5@subq2 テーブルの動作が制御されます。

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

  • 複数のヒントインスタンス

    各タイプの JOIN_PREFIX および JOIN_SUFFIX ヒントのみが適用されます。 同じタイプの後のヒントは無視され、警告が表示されます。 JOIN_ORDER は複数回指定できます。

    例:

    /*+ JOIN_PREFIX(t1) JOIN_PREFIX(t2) */

    2 番目の JOIN_PREFIX ヒントは無視され、警告が表示されます。

    /*+ JOIN_PREFIX(t1) JOIN_SUFFIX(t2) */

    両方のヒントが適用されます。 警告は発生しません。

    /*+ JOIN_ORDER(t1, t2) JOIN_ORDER(t2, t3) */

    両方のヒントが適用されます。 警告は発生しません。

  • 競合するヒント

    JOIN_ORDERJOIN_PREFIX に同時に適用できないテーブルの順序がある場合など、ヒントが競合することがあります:

    SELECT /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */ ... FROM t1, t2;

    この場合、最初に指定したヒントが適用され、後続の競合するヒントは警告なしで無視されます。 適用できない有効なヒントは、警告なしで暗黙的に無視されます。

  • 無視されたヒント

    ヒントで指定されたテーブルに循環依存性がある場合、ヒントは無視されます。

    例:

    /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */

    JOIN_ORDER ヒントは、t1 に依存するテーブル t2 を設定します。 テーブル t1t2 に依存できないため、JOIN_PREFIX ヒントは無視されます。 無視されたヒントは、拡張 EXPLAIN 出力には表示されません。

  • const テーブルとの相互作用

    MySQL オプティマイザは、const テーブルを結合順序の最初に配置し、const テーブルの位置はヒントの影響を受けません。 結合順序ヒント内の const テーブルへの参照は無視されますが、ヒントは引き続き適用可能です。 たとえば、これらは同等です:

    JOIN_ORDER(t1, const_tbl, t2)
    JOIN_ORDER(t1, t2)

    拡張 EXPLAIN 出力に表示される許容ヒントには、指定されたとおりに const テーブルが含まれます。

  • 結合操作のタイプとの相互作用

    MySQL では、複数のタイプの結合がサポートされています: LEFT, RIGHT, INNER, CROSS, STRAIGHT_JOIN。 指定したタイプの結合と競合するヒントは、警告なしで無視されます。

    例:

    SELECT /*+ JOIN_PREFIX(t1, t2) */FROM t2 LEFT JOIN t1;

    ここでは、ヒント内のリクエストされた結合順序と LEFT JOIN に必要な順序の間で競合が発生します。 ヒントは警告なしで無視されます。

テーブルレベルのオプティマイザヒント

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

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

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

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

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

  • hint_name : 次のヒント名を使用できます:

    • BKA, NO_BKA: 指定したテーブルに対するバッチングキーアクセスを有効または無効にします。

    • BNL, NO_BNL: 指定したテーブルのブロックネストループを有効または無効にします。 MySQL 8.0.18 以降では、これらのヒントによってハッシュ結合の最適化も有効化および無効化されます。

      注記

      ブロックネストループの最適化は MySQL 8.0.20 以降のリリースでは削除されていますが、ハッシュ結合の有効化および無効化のためにこれらのヒントは引き続きサポートされています。

    • DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN: 指定したテーブルに対する導出表条件プッシュダウンの使用を有効または無効にします (MySQL 8.0.22 で追加)。 詳細は、セクション8.2.2.5「導出条件プッシュダウン最適化」を参照してください。

    • HASH_JOIN, NO_HASH_JOIN: 指定したテーブルに対するハッシュ結合の使用を有効または無効にします (MySQL 8.0.18 のみ。MySQL 8.0.19 以降では無効です)。

    • MERGE, NO_MERGE: 指定したテーブル、ビュー参照または共通テーブル式のマージを有効にするか、マージを無効にしてかわりに実体化を使用してください。

    注記

    ブロックネストループまたはバッチキーアクセスヒントを使用して外部結合の内部テーブルの結合バッファリングを有効にするには、外部結合のすべての内部テーブルに対して結合バッファリングを有効にする必要があります。

  • tbl_name: ステートメントで使用されるテーブルの名前。 ヒントは、名前を付けるすべてのテーブルに適用されます。 ヒントにテーブルが指定されていない場合は、そのヒントが発生したクエリーブロックのすべてのテーブルに適用されます。

    テーブルにエイリアスがある場合、ヒントはテーブル名ではなくエイリアスを参照する必要があります。

    ヒントのテーブル名はスキーマ名で修飾できません。

  • query_block_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 ヒントには、次の優先順位ルールが適用されます:

  • ヒントは、技術的な制約ではないオプティマイザのヒューリスティックより優先されます。 (ヒントを提案として提供しても効果がない場合、オプティマイザはヒントを無視する理由があります。)

  • ヒントは、optimizer_switch システム変数の derived_merge フラグより優先されます。

  • ビュー参照の場合、ビュー定義の ALGORITHM={MERGE|TEMPTABLE} 句は、ビューを参照するクエリーで指定されたヒントよりも優先されます。

インデックスレベルのオプティマイザヒント

インデックスレベルのヒントは、オプティマイザが特定のテーブルまたはインデックスに使用するインデックス処理戦略に影響します。 これらのヒントタイプは、インデックス条件プッシュダウン (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] ...])

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

  • hint_name : 次のヒント名を使用できます:

    • GROUP_INDEX, NO_GROUP_INDEX: GROUP BY 操作のインデックススキャンに対して、指定したインデックスを有効または無効にします。 インデックスヒント FORCE INDEX FOR GROUP BYIGNORE INDEX FOR GROUP BY と同等です。 MySQL 8.0.20 以降で使用できます。

    • INDEX, NO_INDEX: JOIN_INDEXGROUP_INDEX および ORDER_INDEX の組合せとして機能し、指定されたインデックスを任意のスコープおよびすべてのスコープに強制的に使用するか、NO_JOIN_INDEXNO_GROUP_INDEX および NO_ORDER_INDEX の組合せとして使用します。これにより、サーバーは、任意のスコープおよびすべてのスコープに指定されたインデックスを無視します。 FORCE INDEXIGNORE INDEX と同等です。 MySQL 8.0.20 以降で使用可能です。

    • INDEX_MERGE, NO_INDEX_MERGE: 指定したテーブルまたはインデックスのインデックスマージアクセス方法を有効または無効にします。 このアクセス方法の詳細は、セクション8.2.1.3「インデックスマージの最適化」 を参照してください。 これらのヒントは、3 つのすべてのインデックスマージアルゴリズムに適用されます。

      INDEX_MERGE ヒントでは、オプティマイザは、指定されたインデックスセットを使用して、指定されたテーブルに対してインデックスマージを強制的に使用します。 インデックスが指定されていない場合、オプティマイザは考えられるすべてのインデックスの組合せを考慮し、最もコストの低いものを選択します。 インデックスの組合せが特定のステートメントに適用できない場合、ヒントは無視されることがあります。

      NO_INDEX_MERGE ヒントは、指定されたインデックスのいずれかを含むインデックスマージの組合せを無効にします。 ヒントにインデックスが指定されていない場合、テーブルに対するインデックスのマージは許可されません。

    • JOIN_INDEX, NO_JOIN_INDEX: MySQL で、ref, range, index_merge などのアクセス方法に対して指定されたインデックスを強制的に使用または無視します。 FORCE INDEX FOR JOINIGNORE INDEX FOR JOIN と同等です。 MySQL 8.0.20 以降で使用できます。

    • MRR, NO_MRR: 指定したテーブルまたはインデックスの MRR を有効または無効にします。 MRR ヒントは、InnoDB および MyISAM テーブルにのみ適用されます。 このアクセス方法の詳細は、セクション8.2.1.11「Multi-Range Read の最適化」 を参照してください。

    • NO_ICP: 指定したテーブルまたはインデックスの ICP を無効にします。 デフォルトでは、ICP は最適化戦略の候補であるため、有効にするヒントはありません。 このアクセス方法の詳細は、セクション8.2.1.6「インデックスコンディションプッシュダウンの最適化」 を参照してください。

    • NO_RANGE_OPTIMIZATION: 指定したテーブルまたはインデックスのインデックス範囲アクセスを無効にします。 このヒントは、テーブルまたはインデックスのインデックスマージおよびインデックスのループスキャンも無効にします。 デフォルトでは、範囲アクセスは最適化戦略の候補であるため、有効にするヒントはありません。

      このヒントは、範囲の数が多く、範囲の最適化に多くのリソースが必要な場合に役立ちます。

    • ORDER_INDEX, NO_ORDER_INDEX: MySQL で、指定されたインデックスを使用または無視して行をソートします。 FORCE INDEX FOR ORDER BYIGNORE INDEX FOR ORDER BY と同等です。 MySQL 8.0.20 以降で使用可能です。

    • SKIP_SCAN, NO_SKIP_SCAN: 指定したテーブルまたはインデックスのスキャンのスキップアクセス方法を有効または無効にします。 このアクセス方法の詳細は、スキャン範囲アクセス方法のスキップ を参照してください。 これらのヒントは、MySQL 8.0.13 の時点で使用できます。

      SKIP_SCAN ヒントでは、オプティマイザは、指定されたインデックスセットを使用して、指定されたテーブルに対してスキップスキャンを強制的に使用します。 インデックスが指定されていない場合、オプティマイザは考えられるすべてのインデックスを考慮し、最もコストの低いインデックスを選択します。 インデックスが特定のステートメントに適用できない場合、ヒントは無視されることがあります。

      NO_SKIP_SCAN ヒントは、指定されたインデックスのスキップスキャンを無効にします。 ヒントにインデックスが指定されていない場合、テーブルに対してスキップスキャンは許可されません。

  • tbl_name: ヒントが適用されるテーブル。

  • index_name: 指定したテーブル内のインデックスの名前。 ヒントは、名前を付けるすべてのインデックスに適用されます。 ヒントにインデックスが指定されていない場合は、テーブル内のすべてのインデックスに適用されます。

    主キーを参照するには、PRIMARY という名前を使用します。 テーブルのインデックス名を表示するには、SHOW INDEX を使用します。

  • query_block_name: ヒントが適用されるクエリーブロック。 ヒントに先行する@query_block_name が含まれていない場合、ヒントは発生したクエリーブロックに適用されます。 tbl_name@query_block_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_ai_b および i_c という名前のインデックスが ab、および 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 オプティマイザヒントには、次の優先順位ルールが適用されます:

  • オプティマイザヒントが指定され、適用可能な場合は、optimizer_switch システム変数のインデックスマージ関連フラグよりも優先されます。

    SET optimizer_switch='index_merge_intersection=off';
    SELECT /*+ INDEX_MERGE(t1 i_b, i_c) */ * FROM t1
    WHERE b = 1 AND c = 2 AND d = 3;

    ヒントは optimizer_switch よりも優先されます。 この場合、(i_b, i_c) にはインデックスマージが使用されます。

    SET optimizer_switch='index_merge_intersection=on';
    SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
    WHERE b = 1 AND c = 2 AND d = 3;

    ヒントには 1 つのインデックスのみが指定されているため、適用できず、optimizer_switch フラグ (on) が適用されます。 インデックスマージは、オプティマイザがコスト効率に優れていると評価した場合に使用されます。

    SET optimizer_switch='index_merge_intersection=off';
    SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
    WHERE b = 1 AND c = 2 AND d = 3;

    ヒントには 1 つのインデックスのみが指定されているため、適用できず、optimizer_switch フラグ (off) が適用されます。 インデックスマージは使用されません。

  • インデックスレベルのオプティマイザヒント GROUP_INDEX, INDEX, JOIN_INDEX および ORDER_INDEX はすべて、同等の FORCE INDEX ヒントより優先されます。つまり、FORCE INDEX ヒントは無視されます。 同様に、NO_GROUP_INDEX, NO_INDEX, NO_JOIN_INDEX および NO_ORDER_INDEX のヒントはすべて IGNORE INDEX の同等のヒントよりも優先され、無視されます。

    インデックスレベルのオプティマイザヒント GROUP_INDEX, NO_GROUP_INDEX, INDEXNO_INDEXJOIN_INDEXNO_JOIN_INDEXORDER_INDEX および NO_ORDER_INDEX ヒントは、他のすべてのオプティマイザヒント (他のインデックスレベルのオプティマイザヒントを含む) よりも優先されます。 その他のオプティマイザヒントは、これらによって許可されるインデックスにのみ適用されます。

    GROUP_INDEX, INDEX, JOIN_INDEX および ORDER_INDEX のヒントはすべて FORCE INDEX と同等であり、USE INDEX とは同等ではありません。 これは、これらのヒントの 1 つ以上を使用することは、いずれかの名前付きインデックスを使用してテーブル内の行を検索する方法がない場合にのみテーブルスキャンが使用されることを意味するためです。 MySQL が USE INDEX の特定のインスタンスと同じインデックスまたはインデックスのセットを使用するようにするには、NO_INDEX, NO_JOIN_INDEX, NO_GROUP_INDEX, NO_ORDER_INDEX またはこれらの組合せを使用できます。

    USE INDEX がクエリー SELECT a,c FROM t1 USE INDEX FOR ORDER BY (i_a) ORDER BY a に与える影響をレプリケートするには、NO_ORDER_INDEX オプティマイザヒントを使用して、次のようなものを除くテーブルのすべてのインデックスをカバーできます:

    SELECT /*+ NO_ORDER_INDEX(t1 i_b,i_c) */ a,c
        FROM t1
        ORDER BY a;

    次に示すように、NO_ORDER_BY によって USE INDEX が無視されるため、テーブル全体の NO_ORDER_INDEXUSE INDEX FOR ORDER BY と組み合せようとしても機能しません:

    mysql> EXPLAIN SELECT /*+ NO_ORDER_INDEX(t1) */ a,c FROM t1
        ->     USE INDEX FOR ORDER BY (i_a) ORDER BY a\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t1
       partitions: NULL
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 256
         filtered: 100.00
            Extra: Using filesort
  • USE INDEXFORCE INDEX および IGNORE INDEX のインデックスヒントは、INDEX_MERGE および NO_INDEX_MERGE のオプティマイザヒントよりも優先度が高くなります。

    /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ ... IGNORE INDEX i_a

    IGNORE INDEXINDEX_MERGE よりも優先されるため、インデックス i_a はインデックスマージで使用可能な範囲から除外されます。

    /*+ NO_INDEX_MERGE(t1 i_a, i_b) */ ... FORCE INDEX i_a, i_b

    FORCE INDEX のため、i_a, i_b ではインデックスマージは許可されていませんが、オプティマイザは range または ref のアクセスに i_a または i_b のいずれかを使用するように強制されます。 競合はありません。両方のヒントを適用できます。

  • IGNORE INDEX ヒントで複数のインデックスが指定されている場合、それらのインデックスはインデックスマージに使用できません。

  • FORCE INDEX ヒントおよび USE INDEX ヒントでは、名前付きインデックスのみをインデックスマージに使用できます。

    SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ a FROM t1
    FORCE INDEX (i_a, i_b) WHERE c = 'h' AND a = 2 AND b = 'b';

    インデックスマージ交差アクセスアルゴリズムは、(i_a, i_b) に使用されます。 FORCE INDEXUSE INDEX に変更された場合も同様です。

サブクエリーオプティマイザヒント

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

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

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

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

  • hint_name : 次のヒント名を使用できます:

    • SEMIJOIN, NO_SEMIJOIN: 名前付き準結合戦略を有効または無効にします。

  • strategy: 有効または無効にする準結合戦略。 これらの戦略名は許可されます: DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION

    SEMIJOIN ヒントでは、戦略に名前が付けられていない場合、可能であれば、optimizer_switch システム変数に従って有効化された戦略に基づいて準結合が使用されます。 戦略に名前が付けられているが、ステートメントには適用できない場合は、DUPSWEEDOUT が使用されます。

    NO_SEMIJOIN ヒントでは、戦略に名前が付いていない場合、準結合は使用されません。 ステートメントに適用可能なすべての戦略を除外する戦略に名前が付けられている場合は、DUPSWEEDOUT が使用されます。

あるサブクエリーが別のサブクエリー内にネストされ、その両方が外部クエリーの準結合にマージされる場合、最も内側のクエリーに対する準結合戦略の指定は無視されます。 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 値は許可されます: INTOEXISTSMATERIALIZATION

例:

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 ヒントは次のように適用できます:

  • UNION やサブクエリーを含むステートメントなど、複数の SELECT キーワードを持つステートメントの場合、MAX_EXECUTION_TIME はステートメント全体に適用され、最初の SELECT の後に出現する必要があります。

  • 読取り専用の SELECT ステートメントに適用されます。 読み取り専用でないステートメントは、副作用としてデータを変更するストアドファンクションを呼び出すステートメントです。

  • ストアドプログラムの SELECT ステートメントには適用されず、無視されます。

可変設定のヒント構文

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_basedon または 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 ...)) ...

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

  • MRR(@qb1 t1) は、クエリーブロック qb1 のテーブル t1 に適用されます。

  • BKA(@qb2) は、クエリーブロック qb2 に適用されます。

  • NO_MRR(@qb3 t1 idx1, id2) は、クエリーブロック qb3t1 テーブルのインデックス idx1 および idx2 に適用されます。

クエリーブロック名は識別子であり、有効な名前とその引用符の方法に関する通常のルールに従います (セクション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") */ ...) ...