PDF (US Ltr)
- 36.1Mb
PDF (A4)
- 36.2Mb
このページは機械翻訳したものです。
オプティマイザ戦略を制御する方法の 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_INDEX、GROUP_INDEX および ORDER_INDEX の組合せとして、または NO_JOIN_INDEX、NO_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_nametbl_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 テーブルの動作が制御されます。
オプティマイザは、次の原則に従って結合順序のヒントを解決します:
-
複数のヒントインスタンス
各タイプの
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_ORDERとJOIN_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を設定します。 テーブルt1はt2に依存できないため、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に必要な順序の間で競合が発生します。 ヒントは警告なしで無視されます。
テーブルレベルのヒントは次のものに影響します:
Block Nested-Loop (BNL) および Batched Key Access (BKA) 結合処理アルゴリズムの使用 (セクション8.2.1.12「Block Nested Loop 結合と Batched Key Access 結合」 を参照)。
導出テーブル、ビュー参照または共通テーブル式を外部クエリーブロックにマージするか、内部一時テーブルを使用して実体化するか。
導出テーブル条件プッシュダウン最適化の使用 (MySQL 8.0.22 で追加)。 セクション8.2.2.5「導出条件プッシュダウン最適化」を参照してください。
これらのヒントタイプは、特定のテーブルまたはクエリーブロック内のすべてのテーブルに適用されます。
テーブルレベルのヒントの構文:
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_nametbl_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 BY、IGNORE INDEX FOR GROUP BYと同等です。 MySQL 8.0.20 以降で使用できます。INDEX,NO_INDEX:JOIN_INDEX、GROUP_INDEXおよびORDER_INDEXの組合せとして機能し、指定されたインデックスを任意のスコープおよびすべてのスコープに強制的に使用するか、NO_JOIN_INDEX、NO_GROUP_INDEXおよびNO_ORDER_INDEXの組合せとして使用します。これにより、サーバーは、任意のスコープおよびすべてのスコープに指定されたインデックスを無視します。FORCE INDEX、IGNORE 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 JOIN、IGNORE 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 BY、IGNORE 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_nametbl_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_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 オプティマイザヒントには、次の優先順位ルールが適用されます:
-
オプティマイザヒントが指定され、適用可能な場合は、
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,INDEX、NO_INDEX、JOIN_INDEX、NO_JOIN_INDEX、ORDER_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_INDEXをUSE 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 INDEX、FORCE INDEXおよびIGNORE INDEXのインデックスヒントは、INDEX_MERGEおよびNO_INDEX_MERGEのオプティマイザヒントよりも優先度が高くなります。/*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ ... IGNORE INDEX i_aIGNORE INDEXはINDEX_MERGEよりも優先されるため、インデックスi_aはインデックスマージで使用可能な範囲から除外されます。/*+ NO_INDEX_MERGE(t1 i_a, i_b) */ ... FORCE INDEX i_a, i_bFORCE 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 INDEXがUSE 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 値は許可されます: 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 ヒントは次のように適用できます:
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_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 ...)) ...その結果、次のようになります:
MRR(@qb1 t1)は、クエリーブロックqb1のテーブルt1に適用されます。BKA(@qb2)は、クエリーブロックqb2に適用されます。NO_MRR(@qb3 t1 idx1, id2)は、クエリーブロックqb3のt1テーブルのインデックス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") */ ...) ...