オプティマイザヒント

+8

12 Contributors

TiDBは、 MySQL 5.7で導入されたコメント形式の構文に基づいたオプティマイザヒントをサポートしています。例えば、一般的な構文の1つは/*+ HINT_NAME([t1_name [, t2_name] ...]) */です。TiDBオプティマイザがあまり最適ではないクエリプランを選択した場合は、オプティマイザヒントの使用が推奨されます。

ヒントが効かない場合は、 ヒントが効かない一般的な問題のトラブルシューティング参照してください。

構文

オプティマイザーヒントは大文字と小文字を区別せず、SQL ステートメントのSELECT 、 INSERT 、 UPDATE 、またはDELETEキーワードに続く/*+ ... */コメント内で指定されます。

複数のヒントはカンマで区切って指定できます。例えば、次のクエリでは3つの異なるヒントが使用されています。

SELECT /*+ USE_INDEX(t1, idx1), HASH_AGG(), HASH_JOIN(t1) */ count(*) FROM t t1, t t2 WHERE t1.a = t2.b;

オプティマイザーヒントがクエリ実行プランにどのように影響するかは、 EXPLAINとEXPLAIN ANALYZEの出力で確認できます。

ヒントが不正確または不完全な場合、ステートメントエラーは発生しません。これは、ヒントがクエリ実行に対する意味的なヒント（提案）としてのみ機能することを意図しているためです。同様に、TiDBはヒントが適用できない場合、せいぜい警告を返します。

注記：

指定されたキーワードの後にコメントが続かない場合、一般的なMySQLコメントとして扱われます。コメントは有効にならず、警告も表示されません。

現在、TiDBは2つのカテゴリのヒントをサポートしています。これらはスコープが異なります。最初のカテゴリのヒントはクエリブロックのスコープ（例： /*+ HASH_AGG() */ ）で有効になり、2番目のカテゴリのヒントはクエリ全体（例： /*+ MEMORY_QUOTA(1024 MB)*/で有効になります。

ステートメント内の各クエリまたはサブクエリは異なるクエリブロックに対応し、各クエリブロックには独自の名前が付けられます。例:

SELECT * FROM (SELECT * FROM t) t1, (SELECT * FROM t) t2;

上記のクエリ文には3つのクエリブロックがあります。最も外側のSELECT最初のクエリブロック（名前はsel_1に対応します。2つのSELECTサブクエリは2番目と3番目のクエリブロック（名前はそれぞれsel_2とsel_3 ）に対応します。番号の順序は、左から右へのSELECTの出現に基づいています。最初のSELECT DELETEまたはUPDATEに置き換えると、対応するクエリブロック名はdel_1またはupd_1なります。

クエリブロックで有効になるヒント

このカテゴリのヒントは、 SELECT 、 UPDATE 、またはDELETEキーワードに続けて指定できます。ヒントの有効範囲を制御するには、ヒント内でクエリブロック名を使用します。クエリ内の各テーブルを正確に識別することで、ヒントパラメータを明確にすることができます（テーブル名やエイリアスが重複している場合）。ヒント内でクエリブロックが指定されていない場合、ヒントはデフォルトで現在のブロックで有効になります。

例えば：

SELECT /*+ HASH_JOIN(@sel_1 t1@sel_1, t3) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;

このヒントはsel_1クエリ ブロックで有効になり、そのパラメータはsel_1のt1とt3テーブルです ( sel_2はt1テーブルも含まれます)。

前述のように、ヒント内のクエリ ブロックの名前は次の方法で指定できます。

• ヒントの最初のパラメータとしてクエリブロック名を設定し、他のパラメータとはスペースで区切ってください。このセクションにリストされているすべてのヒントには、 QB_NAMEに加えて、オプションの隠しパラメータ@QB_NAMEも存在します。このパラメータを使用することで、ヒントの有効範囲を指定できます。
• このテーブルがどのクエリ ブロックに属するかを明示的に指定するには、パラメータ内のテーブル名に@QB_NAME追加します。

注記：

ヒントは、ヒントが有効になるクエリブロック内またはその前に置く必要があります。ヒントをクエリブロックの後に置くと、ヒントは有効になりません。

QB_NAME

クエリ文が複数のネストされたクエリを含む複雑な文である場合、特定のクエリブロックのIDと名前が誤って識別される可能性があります。この点については、ヒントQB_NAME役立ちます。

QB_NAMEクエリブロック名を意味します。クエリブロックに新しい名前を指定できます。指定したQB_NAMEと以前のデフォルト名はどちらも有効です。例:

SELECT /*+ QB_NAME(QB1) */ * FROM (SELECT * FROM t) t1, (SELECT * FROM t) t2;

このヒントは、外側のSELECTクエリ ブロックの名前をQB1に指定し、 QB1とデフォルト名sel_1両方をクエリ ブロックに対して有効にします。

注記：

上記の例では、ヒントがQB_NAMEからsel_2指定し、元の 2 番目のクエリ ブロックSELECTに新しいQB_NAME指定していない場合、2 番目のクエリ ブロックSELECTに対してsel_2無効な名前になります。

SET_VAR(変数名=変数値)

SET_VAR(VAR_NAME=VAR_VALUE)ヒントを使用すると、文の実行中にシステム変数の値を一時的に変更できます。文の実行後、現在のセッションにおけるシステム変数の値は自動的に元の値に戻ります。このヒントは、オプティマイザとエグゼキュータに関連する一部のシステム変数を変更するために使用できます。このヒントを使用して変更できるシステム変数のリストについては、 システム変数を参照してください。

次に例を示します。

SELECT /*+ SET_VAR(MAX_EXECUTION_TIME=1234) */ @@MAX_EXECUTION_TIME;
SELECT @@MAX_EXECUTION_TIME;

上記のSQL文を実行すると、最初のクエリはデフォルト値MAX_EXECUTION_TIMEではなく、ヒントに設定された値1234を返します。2番目のクエリは変数のデフォルト値を返します。

+----------------------+
| @@MAX_EXECUTION_TIME |
+----------------------+
|                 1234 |
+----------------------+
1 row in set (0.00 sec)
+----------------------+
| @@MAX_EXECUTION_TIME |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

MERGE_JOIN(t1_name [, tl_name ...])

ヒントMERGE_JOIN(t1_name [, tl_name ...])は、指定されたテーブルに対してソートマージ結合アルゴリズムを使用するようオプティマイザに指示します。一般的に、このアルゴリズムはメモリ消費量が少なくなりますが、処理時間は長くなります。データ量が非常に多い場合やシステムメモリが不足している場合は、このヒントを使用することをお勧めします。例：

select /*+ MERGE_JOIN(t1, t2) */ * from t1, t2 where t1.id = t2.id;

注記：

TIDB_SMJは TiDB 3.0.x 以前のバージョンにおけるMERGE_JOINの別名です。これらのバージョンを使用している場合は、ヒントにTIDB_SMJ(t1_name [, tl_name ...])構文を適用する必要があります。TiDB のそれ以降のバージョンでは、ヒントの名前としてTIDB_SMJとMERGE_JOINどちらも有効ですが、 MERGE_JOIN使用を推奨します。

NO_MERGE_JOIN(t1_name [, tl_name ...])

ヒントNO_MERGE_JOIN(t1_name [, tl_name ...])は、指定されたテーブルに対してソートマージ結合アルゴリズムを使用しないようオプティマイザに指示します。例:

SELECT /*+ NO_MERGE_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

INL_JOIN(t1_name [, tl_name ...])

注記：

場合によっては、 INL_JOINヒントが機能しないことがあります。詳細については、 INL_JOINヒントは有効になりません参照してください。

ヒントINL_JOIN(t1_name [, tl_name ...])は、指定されたテーブルに対してインデックス・ネストループ結合アルゴリズムを使用するようオプティマイザに指示します。このアルゴリズムは、状況によってはシステムリソースの消費量が少なく、処理時間も短縮される可能性がありますが、状況によっては逆の結果になることもあります。外部テーブルがヒントWHEREでフィルタリングされた後、結果セットが10,000行未満の場合、このヒントを使用することをお勧めします。例：

SELECT /*+ INL_JOIN(t1, t2) */ * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;

上記のSQL文では、ヒントINL_JOIN(t1, t2)はオプティマイザに、 t1とt2に対してインデックス・ネスト・ループ結合アルゴリズムを使用するように指示しています。これは、 t1とt2間でインデックス・ネスト・ループ結合アルゴリズムが使用されることを意味しているわけではないことに注意してください。ヒントは、 t1とt2それぞれ別のテーブル（ t3 ）に対してインデックス・ネスト・ループ結合アルゴリズムを使用することを示しています。

INL_JOIN()で指定されたパラメータは、クエリプランを作成する際に内部テーブルとして使用される候補テーブルです。例えば、 INL_JOIN(t1) 、TiDB がクエリプランを作成する際に内部テーブルとしてt1を使用することを考慮していることを意味します。候補テーブルに別名がある場合は、 INL_JOIN()のパラメータとしてその別名を使用する必要があります。別名がない場合は、テーブルの元の名前をパラメータとして使用してください。例えば、 select /*+ INL_JOIN(t1) */ * from t t1, t t2 where t1.a = t2.b;クエリでは、 INL_JOIN()のパラメータとしてtではなく、 tテーブルの別名であるt1またはt2使用する必要があります。

注記：

TIDB_INLJは TiDB 3.0.x 以前のバージョンにおけるINL_JOINの別名です。これらのバージョンを使用している場合は、ヒントにTIDB_INLJ(t1_name [, tl_name ...])構文を適用する必要があります。TiDB のそれ以降のバージョンでは、ヒントの名前としてTIDB_INLJとINL_JOINどちらも有効ですが、 INL_JOIN使用を推奨します。

NO_INDEX_JOIN(t1_name [, tl_name ...])

ヒントNO_INDEX_JOIN(t1_name [, tl_name ...])は、指定されたテーブルに対してインデックス・ネストループ結合アルゴリズムを使用しないようオプティマイザに指示します。例えば、次のようになります。

SELECT /*+ NO_INDEX_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

INL_HASH_JOIN

ヒントINL_HASH_JOIN(t1_name [, tl_name])は、インデックス・ネストループ・ハッシュ結合アルゴリズムを使用するようにオプティマイザに指示します。このアルゴリズムを使用するための条件は、インデックス・ネストループ結合アルゴリズムを使用するための条件と同じです。2つのアルゴリズムの違いは、ヒントINL_JOIN結合された内部テーブルにハッシュテーブルを作成するのに対し、ヒントINL_HASH_JOIN結合された外部テーブルにハッシュテーブルを作成する点です。 INL_HASH_JOINメモリ使用量に制限がありますが、ヒントINL_JOINは内部テーブルで一致する行数に応じてメモリ使用量が異なります。

NO_INDEX_HASH_JOIN(t1_name [, tl_name ...])

ヒントNO_INDEX_HASH_JOIN(t1_name [, tl_name ...])は、指定されたテーブルに対してインデックス ネスト ループ ハッシュ結合アルゴリズムを使用しないようオプティマイザに指示します。

INL_MERGE_JOIN

ヒントINL_MERGE_JOIN(t1_name [, tl_name])は、インデックス・ネストループ・マージ結合アルゴリズムを使用するようにオプティマイザに指示します。このアルゴリズムを使用する条件は、インデックス・ネストループ結合アルゴリズムを使用する条件と同じです。

NO_INDEX_MERGE_JOIN(t1_name [, tl_name ...])

ヒントNO_INDEX_MERGE_JOIN(t1_name [, tl_name ...])は、指定されたテーブルに対してインデックス ネスト ループ マージ結合アルゴリズムを使用しないようオプティマイザに指示します。

HASH_JOIN(t1_name [, tl_name ...])

HASH_JOIN(t1_name [, tl_name ...])ヒントは、指定されたテーブルに対してハッシュ結合アルゴリズムを使用するようオプティマイザに指示します。このアルゴリズムにより、クエリを複数のスレッドで同時に実行できるため、処理速度は向上しますが、メモリ消費量は増加します。例：

select /*+ HASH_JOIN(t1, t2) */ * from t1, t2 where t1.id = t2.id;

注記：

TIDB_HJは TiDB 3.0.x 以前のバージョンにおけるHASH_JOINの別名です。これらのバージョンを使用している場合は、ヒントにTIDB_HJ(t1_name [, tl_name ...])構文を適用する必要があります。TiDB のそれ以降のバージョンでは、ヒントの名前としてTIDB_HJとHASH_JOINどちらも有効ですが、 HASH_JOIN使用を推奨します。

NO_HASH_JOIN(t1_name [, tl_name ...])

ヒントNO_HASH_JOIN(t1_name [, tl_name ...])は、指定されたテーブルに対してハッシュ結合アルゴリズムを使用しないようオプティマイザに指示します。例:

SELECT /*+ NO_HASH_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

HASH_JOIN_BUILD(t1_name [, tl_name ...])

HASH_JOIN_BUILD(t1_name [, tl_name ...])ヒントは、指定されたテーブルをビルド側として、ハッシュ結合アルゴリズムを使用するようオプティマイザに指示します。これにより、特定のテーブルを使用してハッシュテーブルを構築できます。例:

SELECT /*+ HASH_JOIN_BUILD(t1) */ * FROM t1, t2 WHERE t1.id = t2.id;

HASH_JOIN_PROBE(t1_name [, tl_name ...])

HASH_JOIN_PROBE(t1_name [, tl_name ...])ヒントは、指定されたテーブルをプローブ側としてハッシュ結合アルゴリズムを使用するようオプティマイザに指示します。これにより、特定のテーブルをプローブ側としてハッシュ結合アルゴリズムを実行できます。例：

SELECT /*+ HASH_JOIN_PROBE(t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

セミジョインリライト()

SEMI_JOIN_REWRITE()ヒントは、準結合クエリを通常の結合クエリに書き換えるようオプティマイザに指示します。現在、このヒントはEXISTSサブクエリに対してのみ機能します。

このヒントをクエリの書き換えに使用しない場合、実行プランでハッシュ結合が選択されると、準結合クエリはハッシュテーブルの構築にサブクエリのみを使用します。この場合、サブクエリの結果が外部クエリの結果よりも大きいと、実行速度が予想よりも遅くなる可能性があります。

同様に、実行プランでインデックス結合が選択されている場合、準結合クエリは駆動テーブルとして外部クエリのみを使用できます。この場合、サブクエリの結果が外部クエリの結果よりも小さい場合、実行速度が予想よりも遅くなる可能性があります。

SEMI_JOIN_REWRITE()使用してクエリを書き換えると、オプティマイザーは選択範囲を拡張して、より適切な実行プランを選択できます。

-- Does not use SEMI_JOIN_REWRITE() to rewrite the query.
EXPLAIN SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t.a);

+-----------------------------+---------+-----------+------------------------+---------------------------------------------------+
| id                          | estRows | task      | access object          | operator info                                     |
+-----------------------------+---------+-----------+------------------------+---------------------------------------------------+
| MergeJoin_9                 | 7992.00 | root      |                        | semi join, left key:test.t.a, right key:test.t1.a |
| ├─IndexReader_25(Build)     | 9990.00 | root      |                        | index:IndexFullScan_24                            |
| │ └─IndexFullScan_24        | 9990.00 | cop[tikv] | table:t1, index:idx(a) | keep order:true, stats:pseudo                     |
| └─IndexReader_23(Probe)     | 9990.00 | root      |                        | index:IndexFullScan_22                            |
|   └─IndexFullScan_22        | 9990.00 | cop[tikv] | table:t, index:idx(a)  | keep order:true, stats:pseudo                     |
+-----------------------------+---------+-----------+------------------------+---------------------------------------------------+

-- Uses SEMI_JOIN_REWRITE() to rewrite the query.
EXPLAIN SELECT * FROM t WHERE EXISTS (SELECT /*+ SEMI_JOIN_REWRITE() */ 1 FROM t1 WHERE t1.a = t.a);

+------------------------------+---------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------+
| id                           | estRows | task      | access object          | operator info                                                                                                 |
+------------------------------+---------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------+
| IndexJoin_16                 | 1.25    | root      |                        | inner join, inner:IndexReader_15, outer key:test.t1.a, inner key:test.t.a, equal cond:eq(test.t1.a, test.t.a) |
| ├─StreamAgg_39(Build)        | 1.00    | root      |                        | group by:test.t1.a, funcs:firstrow(test.t1.a)->test.t1.a                                                      |
| │ └─IndexReader_34           | 1.00    | root      |                        | index:IndexFullScan_33                                                                                        |
| │   └─IndexFullScan_33       | 1.00    | cop[tikv] | table:t1, index:idx(a) | keep order:true                                                                                               |
| └─IndexReader_15(Probe)      | 1.25    | root      |                        | index:Selection_14                                                                                            |
|   └─Selection_14             | 1.25    | cop[tikv] |                        | not(isnull(test.t.a))                                                                                         |
|     └─IndexRangeScan_13      | 1.25    | cop[tikv] | table:t, index:idx(a)  | range: decided by [eq(test.t.a, test.t1.a)], keep order:false, stats:pseudo                                   |
+------------------------------+---------+-----------+------------------------+---------------------------------------------------------------------------------------------------------------+

前述の例から、ヒントSEMI_JOIN_REWRITE()使用すると、TiDB は駆動テーブルt1に基づいて IndexJoin の実行方法を選択できることがわかります。

SHUFFLE_JOIN(t1_name [, tl_name ...])

ヒントSHUFFLE_JOIN(t1_name [, tl_name ...])は、指定されたテーブルに対してシャッフル結合アルゴリズムを使用するようオプティマイザに指示します。このヒントはMPPモードでのみ有効です。例：

SELECT /*+ SHUFFLE_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

注記：

• このヒントを使用する前に、現在のTiDBクラスタがクエリでTiFlash MPPモードの使用をサポートしていることを確認してください。詳細については、 TiFlash MPPモードを使用するを参照してください。
• このヒントは、 HASH_JOIN_BUILDヒントおよびHASH_JOIN_PROBEヒントと組み合わせて使用して、シャッフル結合アルゴリズムのビルド側とプローブ側を制御できます。

BROADCAST_JOIN(t1_name [, tl_name ...])

BROADCAST_JOIN(t1_name [, tl_name ...])ヒントは、指定されたテーブルに対してブロードキャスト結合アルゴリズムを使用するようオプティマイザに指示します。このヒントはMPPモードでのみ有効です。例：

SELECT /*+ BROADCAST_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id = t2.id;

注記：

• このヒントを使用する前に、現在のTiDBクラスタがクエリでTiFlash MPPモードの使用をサポートしていることを確認してください。詳細については、 TiFlash MPPモードを使用するを参照してください。
• このヒントは、 HASH_JOIN_BUILDヒントおよびHASH_JOIN_PROBEヒントと組み合わせて使用して、ブロードキャスト結合アルゴリズムのビルド側とプローブ側を制御できます。

NO_DECORRELATE()

NO_DECORRELATE()ヒントは、指定されたクエリブロック内の相関サブクエリに対して、相関解除を実行しないようにオプティマイザに指示します。このヒントは、 EXISTS 、 IN 、 ANY 、 ALL 、 SOMEサブクエリ、および相関列を含むスカラーサブクエリ（つまり、相関サブクエリ）に適用されます。

このヒントがクエリ ブロック内で使用される場合、オプティマイザーはサブクエリとその外側のクエリ ブロック間の相関列の非相関化を試行せず、常に Apply 演算子を使用してクエリを実行します。

デフォルトでは、TiDBは相関サブクエリに対して相関除去を実行する適用することで実行効率を高めようとします。しかし、 いくつかのシナリオでは、相関除去によって実行効率が低下する可能性があります。このような場合は、このヒントを使用してオプティマイザに相関除去を行わないよう手動で指示することができます。例えば、次のようになります。

create table t1(a int, b int);
create table t2(a int, b int, index idx(b));

-- Not using NO_DECORRELATE().
explain select * from t1 where t1.a < (select sum(t2.a) from t2 where t2.b = t1.b);

+----------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------+
| id                               | estRows  | task      | access object | operator info                                                                                                |
+----------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------+
| HashJoin_11                      | 9990.00  | root      |               | inner join, equal:[eq(test.t1.b, test.t2.b)], other cond:lt(cast(test.t1.a, decimal(10,0) BINARY), Column#7) |
| ├─HashAgg_23(Build)              | 7992.00  | root      |               | group by:test.t2.b, funcs:sum(Column#8)->Column#7, funcs:firstrow(test.t2.b)->test.t2.b                      |
| │ └─TableReader_24               | 7992.00  | root      |               | data:HashAgg_16                                                                                              |
| │   └─HashAgg_16                 | 7992.00  | cop[tikv] |               | group by:test.t2.b, funcs:sum(test.t2.a)->Column#8                                                           |
| │     └─Selection_22             | 9990.00  | cop[tikv] |               | not(isnull(test.t2.b))                                                                                       |
| │       └─TableFullScan_21       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                               |
| └─TableReader_15(Probe)          | 9990.00  | root      |               | data:Selection_14                                                                                            |
|   └─Selection_14                 | 9990.00  | cop[tikv] |               | not(isnull(test.t1.b))                                                                                       |
|     └─TableFullScan_13           | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                               |
+----------------------------------+----------+-----------+---------------+--------------------------------------------------------------------------------------------------------------+

上記の実行プランから、オプティマイザが自動的に非相関化を実行したことがわかります。非相関化された実行プランにはApply演算子がありません。代わりに、サブクエリと外側のクエリブロック間の結合操作がプランに含まれています。相関列を含む元のフィルタ条件 ( t2.b = t1.b ) は、通常の結合条件になります。

-- Using NO_DECORRELATE().
explain select * from t1 where t1.a < (select /*+ NO_DECORRELATE() */ sum(t2.a) from t2 where t2.b = t1.b);

+------------------------------------------+-----------+-----------+------------------------+--------------------------------------------------------------------------------------+
| id                                       | estRows   | task      | access object          | operator info                                                                        |
+------------------------------------------+-----------+-----------+------------------------+--------------------------------------------------------------------------------------+
| Projection_10                            | 10000.00  | root      |                        | test.t1.a, test.t1.b                                                                 |
| └─Apply_12                               | 10000.00  | root      |                        | CARTESIAN inner join, other cond:lt(cast(test.t1.a, decimal(10,0) BINARY), Column#7) |
|   ├─TableReader_14(Build)                | 10000.00  | root      |                        | data:TableFullScan_13                                                                |
|   │ └─TableFullScan_13                   | 10000.00  | cop[tikv] | table:t1               | keep order:false, stats:pseudo                                                       |
|   └─MaxOneRow_15(Probe)                  | 10000.00  | root      |                        |                                                                                      |
|     └─StreamAgg_20                       | 10000.00  | root      |                        | funcs:sum(Column#14)->Column#7                                                       |
|       └─Projection_45                    | 100000.00 | root      |                        | cast(test.t2.a, decimal(10,0) BINARY)->Column#14                                     |
|         └─IndexLookUp_44                 | 100000.00 | root      |                        |                                                                                      |
|           ├─IndexRangeScan_42(Build)     | 100000.00 | cop[tikv] | table:t2, index:idx(b) | range: decided by [eq(test.t2.b, test.t1.b)], keep order:false, stats:pseudo         |
|           └─TableRowIDScan_43(Probe)     | 100000.00 | cop[tikv] | table:t2               | keep order:false, stats:pseudo                                                       |
+------------------------------------------+-----------+-----------+------------------------+--------------------------------------------------------------------------------------+

上記の実行プランから、オプティマイザが相関除去を実行していないことがわかります。実行プランには依然としてApply演算子が含まれています。相関列を含むフィルタ条件（ t2.b = t1.b ）は、テーブルt2のアクセス時にもフィルタ条件として使用されます。

ハッシュ_AGG()

HASH_AGG()ヒントは、指定されたクエリブロック内のすべての集計関数でハッシュ集計アルゴリズムを使用するようにオプティマイザに指示します。このアルゴリズムにより、クエリを複数のスレッドで同時に実行できるため、処理速度は向上しますが、メモリ消費量は増加します。例：

select /*+ HASH_AGG() */ count(*) from t1, t2 where t1.a > 10 group by t1.id;

ストリーム_AGG()

ヒントSTREAM_AGG()は、指定されたクエリブロック内のすべての集計関数でストリーム集計アルゴリズムを使用するようにオプティマイザに指示します。通常、このアルゴリズムはメモリ消費量が少なくなりますが、処理時間は長くなります。データ量が非常に多い場合やシステムメモリが不足している場合は、このヒントを使用することをお勧めします。例：

select /*+ STREAM_AGG() */ count(*) from t1, t2 where t1.a > 10 group by t1.id;

MPP_1PHASE_AGG()

MPP_1PHASE_AGG()指定すると、指定されたクエリブロック内のすべての集計関数に対して、1フェーズ集計アルゴリズムを使用するようにオプティマイザに指示します。このヒントはMPPモードでのみ有効です。例：

SELECT /*+ MPP_1PHASE_AGG() */ COUNT(*) FROM t1, t2 WHERE t1.a > 10 GROUP BY t1.id;

注記：

このヒントを使用する前に、現在のTiDBクラスタがクエリでTiFlash MPPモードの使用をサポートしていることを確認してください。詳細については、 TiFlash MPPモードを使用するを参照してください。

MPP_2PHASE_AGG()

MPP_2PHASE_AGG()指定すると、指定されたクエリブロック内のすべての集計関数に対して2フェーズ集計アルゴリズムを使用するようにオプティマイザに指示します。このヒントはMPPモードでのみ有効です。例：

SELECT /*+ MPP_2PHASE_AGG() */ COUNT(*) FROM t1, t2 WHERE t1.a > 10 GROUP BY t1.id;

注記：

このヒントを使用する前に、現在のTiDBクラスタがクエリでTiFlash MPPモードの使用をサポートしていることを確認してください。詳細については、 TiFlash MPPモードを使用するを参照してください。

USE_INDEX(t1_name, idx1_name [, idx2_name ...])

USE_INDEX(t1_name, idx1_name [, idx2_name ...])のヒントは、指定されたt1_name番目のテーブルに対して、指定されたインデックスのみを使用するようにオプティマイザに指示します。例えば、次のヒントを適用すると、 select * from t t1 use index(idx1, idx2);文を実行するのと同じ効果が得られます。

SELECT /*+ USE_INDEX(t1, idx1, idx2) */ * FROM t1;

注記：

このヒントでテーブル名のみを指定し、インデックス名を指定しない場合は、実行時にインデックスは考慮されず、テーブル全体がスキャンされます。

FORCE_INDEX(t1_name, idx1_name [, idx2_name ...])

ヒントFORCE_INDEX(t1_name, idx1_name [, idx2_name ...])は、オプティマイザーに指定されたインデックスのみを使用するように指示します。

FORCE_INDEX(t1_name, idx1_name [, idx2_name ...])の使い方と効果はUSE_INDEX(t1_name, idx1_name [, idx2_name ...])の使い方と効果と同じです。

次の 4 つのクエリは同じ効果があります。

SELECT /*+ USE_INDEX(t, idx1) */ * FROM t;
SELECT /*+ FORCE_INDEX(t, idx1) */ * FROM t;
SELECT * FROM t use index(idx1);
SELECT * FROM t force index(idx1);

IGNORE_INDEX(t1_name, idx1_name [, idx2_name ...])

IGNORE_INDEX(t1_name, idx1_name [, idx2_name ...])のヒントは、指定されたt1_name番目のテーブルの指定されたインデックスを無視するようにオプティマイザに指示します。例えば、次のヒントを適用すると、 select * from t t1 ignore index(idx1, idx2);の文を実行するのと同じ効果が得られます。

select /*+ IGNORE_INDEX(t1, idx1, idx2) */ * from t t1;

ORDER_INDEX(t1_name, idx1_name [, idx2_name ...])

ヒントORDER_INDEX(t1_name, idx1_name [, idx2_name ...])は、指定されたテーブルに対して指定されたインデックスのみを使用し、指定されたインデックスを順番に読み取るようにオプティマイザーに指示します。

このヒントは通常、次のシナリオで適用されます。

CREATE TABLE t(a INT, b INT, key(a), key(b));
EXPLAIN SELECT /*+ ORDER_INDEX(t, a) */ a FROM t ORDER BY a LIMIT 10;

+----------------------------+---------+-----------+---------------------+-------------------------------+
| id                         | estRows | task      | access object       | operator info                 |
+----------------------------+---------+-----------+---------------------+-------------------------------+
| Limit_10                   | 10.00   | root      |                     | offset:0, count:10            |
| └─IndexReader_14           | 10.00   | root      |                     | index:Limit_13                |
|   └─Limit_13               | 10.00   | cop[tikv] |                     | offset:0, count:10            |
|     └─IndexFullScan_12     | 10.00   | cop[tikv] | table:t, index:a(a) | keep order:true, stats:pseudo |
+----------------------------+---------+-----------+---------------------+-------------------------------+

オプティマイザはこのクエリに対して2種類のプラン（ Limit + IndexScan(keep order: true)とTopN + IndexScan(keep order: false)を生成します。5ヒントORDER_INDEX使用される場合、オプティマイザはインデックスを順番に読み取る最初のプランを選択します。

注記：

• クエリ自体がインデックスを順番に読み取る必要がない場合（つまり、ヒントがない場合、オプティマイザはいかなる状況でもインデックスを順番に読み取るプランを生成しません）、ヒントORDER_INDEX使用するとエラーCan't find a proper physical plan for this queryが発生します。この場合、対応するヒントORDER_INDEX削除する必要があります。
• パーティションテーブルのインデックスは順番に読み取ることができないため、パーティションテーブルとその関連インデックスではORDER_INDEXヒントを使用しないでください。

NO_ORDER_INDEX(t1_name, idx1_name [, idx2_name ...])

NO_ORDER_INDEX(t1_name, idx1_name [, idx2_name ...])ヒントは、指定されたテーブルに対して指定されたインデックスのみを使用し、指定されたインデックスを順番に読み取らないようにオプティマイザに指示します。このヒントは通常、以下のシナリオに適用されます。

次の例は、クエリ ステートメントの効果がSELECT * FROM t t1 use index(idx1, idx2);と同等であることを示しています。

CREATE TABLE t(a INT, b INT, key(a), key(b));
EXPLAIN SELECT /*+ NO_ORDER_INDEX(t, a) */ a FROM t ORDER BY a LIMIT 10;

+----------------------------+----------+-----------+---------------------+--------------------------------+
| id                         | estRows  | task      | access object       | operator info                  |
+----------------------------+----------+-----------+---------------------+--------------------------------+
| TopN_7                     | 10.00    | root      |                     | test.t.a, offset:0, count:10   |
| └─IndexReader_14           | 10.00    | root      |                     | index:TopN_13                  |
|   └─TopN_13                | 10.00    | cop[tikv] |                     | test.t.a, offset:0, count:10   |
|     └─IndexFullScan_12     | 10000.00 | cop[tikv] | table:t, index:a(a) | keep order:false, stats:pseudo |
+----------------------------+----------+-----------+---------------------+--------------------------------+

ヒントORDER_INDEXの例と同様に、オプティマイザはこのクエリに対してLimit + IndexScan(keep order: true)とTopN + IndexScan(keep order: false) 2種類のプランを生成します。ヒントNO_ORDER_INDEXが使用されると、オプティマイザは後者のプランを選択し、インデックスを順不同で読み取ります。

AGG_TO_COP()

ヒントAGG_TO_COP()は、指定されたクエリブロック内の集計演算をコプロセッサにプッシュダウンするようオプティマイザに指示します。オプティマイザがプッシュダウンに適した集計関数をプッシュダウンしない場合は、このヒントを使用することをお勧めします。例：

select /*+ AGG_TO_COP() */ sum(t1.a) from t t1;

LIMIT_TO_COP()

LIMIT_TO_COP()ヒントは、指定されたクエリブロック内のLimitとTopN演算子をコプロセッサにプッシュダウンするようオプティマイザに指示します。オプティマイザがそのような操作を行わない場合は、このヒントを使用することをお勧めします。例：

SELECT /*+ LIMIT_TO_COP() */ * FROM t WHERE a = 1 AND b > 10 ORDER BY c LIMIT 1;

READ_FROM_STORAGE(TIFLASH[t1_name [, tl_name ...]], TIKV[t2_name [, tl_name ...]])

ヒントREAD_FROM_STORAGE(TIFLASH[t1_name [, tl_name ...]], TIKV[t2_name [, tl_name ...]])は、オプティマイザに特定のstorageエンジンから特定のテーブルを読み取るように指示します。現在、このヒントはTIKVとTIFLASH 2つのstorageエンジンパラメータをサポートしています。テーブルにエイリアスがある場合は、そのエイリアスをREAD_FROM_STORAGE()のパラメータとして使用します。テーブルにエイリアスがない場合は、テーブルの元の名前をパラメータとして使用します。例：

select /*+ READ_FROM_STORAGE(TIFLASH[t1], TIKV[t2]) */ t1.a from t t1, t t2 where t1.a = t2.a;

USE_INDEX_MERGE(t1_name, idx1_name [, idx2_name ...])

ヒントUSE_INDEX_MERGE(t1_name, idx1_name [, idx2_name ...])は、オプティマイザに特定のテーブルをインデックスマージ方式でアクセスするように指示します。インデックスマージには、交差型と結合型の2種類があります。詳細はインデックスマージを使用したステートメントの説明参照してください。

インデックスのリストを明示的に指定すると、TiDB はリストからインデックスを選択してインデックス マージを構築します。インデックスのリストを指定しないと、TiDB は利用可能なすべてのインデックスからインデックスを選択してインデックス マージを構築します。

交差型インデックスマージの場合、指定されたインデックスリストはヒントの必須パラメータです。和集合型インデックスマージの場合、指定されたインデックスリストはヒントのオプションパラメータです。次の例を参照してください。

SELECT /*+ USE_INDEX_MERGE(t1, idx_a, idx_b, idx_c) */ * FROM t1 WHERE t1.a > 10 OR t1.b > 10;

同じテーブルに複数のUSE_INDEX_MERGEヒントが作成されると、オプティマイザーはこれらのヒントによって指定されたインデックス セットの結合からインデックスを選択しようとします。

注記：

USE_INDEX_MERGEのパラメータは列名ではなくインデックス名を参照します。主キーのインデックス名はprimaryです。

LEADING(t1_name [, tl_name ...])

ヒントLEADING(t1_name [, tl_name ...])は、実行プランを生成する際に、ヒントで指定されたテーブル名の順序に従って複数テーブルの結合順序を決定するようオプティマイザに指示します。例:

SELECT /*+ LEADING(t1, t2) */ * FROM t1, t2, t3 WHERE t1.id = t2.id and t2.id = t3.id;

上記の複数テーブル結合クエリでは、結合順序はヒントLEADING()で指定されたテーブル名の順序によって決定されます。オプティマイザはまずt1とt2結合し、その結果をt3と結合します。このヒントはSTRAIGHT_JOINよりも汎用的です。

LEADINGヒントは次の状況では有効になりません。

• LEADINGヒントが複数指定されています。
• LEADINGヒントで指定されたテーブル名が存在しません。
• LEADINGヒントに重複したテーブル名が指定されています。
• オプティマイザーは、ヒントLEADINGで指定された順序に従って結合操作を実行できません。
• straight_join()ヒントがすでに存在します。
• クエリには、外部結合とカルテシアン積が含まれています。

上記の状況では、警告が生成されます。

-- Multiple `LEADING` hints are specified.
SELECT /*+ LEADING(t1, t2) LEADING(t3) */ * FROM t1, t2, t3 WHERE t1.id = t2.id and t2.id = t3.id;

-- To learn why the `LEADING` hint fails to take effect, execute `show warnings`.
SHOW WARNINGS;

+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                           |
+---------+------+-------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | We can only use one leading hint at most, when multiple leading hints are used, all leading hints will be invalid |
+---------+------+-------------------------------------------------------------------------------------------------------------------+

注記：

クエリ文に外部結合が含まれる場合、ヒントには結合順序を入れ替え可能なテーブルのみを指定できます。ヒントに結合順序を入れ替えられないテーブルが含まれている場合、ヒントは無効になります。例えば、 SELECT * FROM t1 LEFT JOIN (t2 JOIN t3 JOIN t4) ON t1.a = t2.a;でt2 t3のテーブルの結合順序を制御したい場合、 t4 LEADINGのヒントにt1指定することはできません。

マージ（）

共通テーブル式（CTE）を含むクエリでヒントMERGE()を使用すると、サブクエリのマテリアライゼーションを無効にし、サブクエリをCTEにインライン展開できます。このヒントは非再帰CTEにのみ適用されます。シナリオによっては、ヒントMERGE()使用すると、一時領域を割り当てるデフォルトの動作よりも実行効率が向上します。例えば、クエリ条件のプッシュダウンやCTEクエリのネストなどです。

-- Uses the hint to push down the predicate of the outer query.
WITH CTE AS (SELECT /*+ MERGE() */ * FROM tc WHERE tc.a < 60) SELECT * FROM CTE WHERE CTE.a < 18;

-- Uses the hint in a nested CTE query to expand a CTE inline into the outer query.
WITH CTE1 AS (SELECT * FROM t1), CTE2 AS (WITH CTE3 AS (SELECT /*+ MERGE() */ * FROM t2), CTE4 AS (SELECT * FROM t3) SELECT * FROM CTE3, CTE4) SELECT * FROM CTE1, CTE2;

注記：

MERGE()は単純な CTE クエリにのみ適用されます。以下の状況には適用されません。

• 再帰CTE
• 集計演算子、ウィンドウ関数、 DISTINCTなど、展開できないインラインを含むサブクエリ。

CTE 参照の数が多すぎると、クエリのパフォーマンスがデフォルトのマテリアライゼーション動作よりも低下する可能性があります。

世界中で効果を発揮するヒント

グローバルヒントはビューで機能します。グローバルヒントとして指定すると、クエリで定義されたヒントがビュー内で有効になります。グローバルヒントを指定するには、まずQB_NAMEヒントを使用してクエリブロック名を定義し、次にViewName@QueryBlockName形式で対象のヒントを追加します。

ステップ1: QB_NAMEヒントを使用してビューのクエリブロック名を定義する

QB_NAMEヒント 、ビューの各クエリブロックに新しい名前を定義するために使用します。ビューのQB_NAMEヒントの定義はクエリブロックと同じですが、構文がQB_NAME(QB)からQB_NAME(QB, ViewName@QueryBlockName [.ViewName@QueryBlockName .ViewName@QueryBlockName ...])に拡張されています。

注記：

@QueryBlockNameと直後の.ViewName@QueryBlockName間には空白があります。そうでない場合、 .ViewName@QueryBlockName QueryBlockNameの一部として扱われます。例えば、 QB_NAME(v2_1, v2@SEL_1 .@SEL_1)有効ですが、 QB_NAME(v2_1, v2@SEL_1.@SEL_1)正しく解析できません。

• 単一のビューとサブクエリのない単純なステートメントの場合、次の例では、ビューvの最初のクエリ ブロック名を指定します。

  SELECT /* Comment: The name of the current query block is the default @SEL_1 */ * FROM v;
  

  ビューv場合、クエリ文から始まるリスト（ ViewName@QueryBlockName [.ViewName@QueryBlockName .ViewName@QueryBlockName ...] ）の最初のビュー名はv@SEL_1です。ビューvの最初のクエリブロックはQB_NAME(v_1, v@SEL_1 .@SEL_1)と宣言するか、 QB_NAME(v_1, v)を省略して単に@SEL_1と記述できます。

  CREATE VIEW v AS SELECT /* Comment: The name of the current query block is the default @SEL_1 */ * FROM t;
  
  -- Specifies the global hint
  SELECT /*+ QB_NAME(v_1, v) USE_INDEX(t@v_1, idx) */ * FROM v;
  

• ネストされたビューとサブクエリを含む複雑なステートメントの場合、次の例では、ビューv1とv2 2 つのクエリ ブロックのそれぞれの名前を指定します。

  SELECT /* Comment: The name of the current query block is the default @SEL_1 */ * FROM v2 JOIN (
      SELECT /* Comment: The name of the current query block is the default @SEL_2 */ * FROM v2) vv;
  

  最初のビューv2場合、最初のクエリステートメントから始まるリストの最初のビュー名はv2@SEL_1です。2番目のビューv2場合、最初のビュー名はv2@SEL_2です。次の例では、最初のビューv2を考慮します。

  ビューv2の最初のクエリ ブロックはQB_NAME(v2_1, v2@SEL_1 .@SEL_1)として宣言でき、ビューv2の 2 番目のクエリ ブロックはQB_NAME(v2_2, v2@SEL_1 .@SEL_2)として宣言できます。

  CREATE VIEW v2 AS
      SELECT * FROM t JOIN /* Comment: For view v2, the name of the current query block is the default @SEL_1. So, the current query block view list is v2@SEL_1 .@SEL_1 */
      (
          SELECT COUNT(*) FROM t1 JOIN v1 /* Comment: For view v2, the name of the current query block is the default @SEL_2. So, the current query block view list is v2@SEL_1 .@SEL_2 */
      ) tt;
  

  ビューv1場合、前のステートメントから始まるリストの最初のビュー名はv2@SEL_1 .v1@SEL_2です。ビューv1の最初のクエリブロックはQB_NAME(v1_1, v2@SEL_1 .v1@SEL_2 .@SEL_1)として宣言でき、ビューv1の2番目のクエリブロックはQB_NAME(v1_2, v2@SEL_1 .v1@SEL_2 .@SEL_2)として宣言できます。

  CREATE VIEW v1 AS SELECT * FROM t JOIN /* Comment: For view `v1`, the name of the current query block is the default @SEL_1. So, the current query block view list is v2@SEL_1 .@SEL_2 .v1@SEL_1 */
      (
          SELECT COUNT(*) FROM t1 JOIN t2 /* Comment: For view `v1`, the name of the current query block is the default @SEL_2. So, the current query block view list is v2@SEL_1 .@SEL_2 .v1@SEL_2 */
      ) tt;
  

注記：

• ビューでグローバルヒントを使用するには、対応するヒントをビューにQB_NAME定義する必要があります。そうしないと、グローバルヒントは有効になりません。

• ヒントを使用してビュー内の複数のテーブル名を指定する場合、同じヒントに表示されるテーブル名が同じビューの同じクエリ ブロック内にあることを確認する必要があります。

• 最も外側のクエリ ブロックのビューでQB_NAMEヒントを定義すると、次のようになります。

  • QB_NAMEのビューリストの最初の項目について、 @SEL_明示的に宣言されていない場合、デフォルトはQB_NAMEが定義されているクエリブロックの位置と一致します。つまり、クエリSELECT /*+ QB_NAME(qb1, v2) */ * FROM v2 JOIN (SELECT /*+ QB_NAME(qb2, v2) */ * FROM v2) vv; SELECT /*+ QB_NAME(qb1, v2@SEL_1) */ * FROM v2 JOIN (SELECT /*+ QB_NAME(qb2, v2@SEL_2) */ * FROM v2) vv;と同等です。

  • QB_NAMEのビューリストの最初の項目以外の項目については、 @SEL_1のみを省略できます。つまり、現在のビューの最初のクエリブロックで@SEL_1宣言されている場合、 @SEL_1省略できます。それ以外の場合、 @SEL_省略できません。上記の例では、次のようになります。

    • ビューv2の最初のクエリ ブロックはQB_NAME(v2_1, v2)として宣言できます。
    • ビューv2の 2 番目のクエリ ブロックはQB_NAME(v2_2, v2.@SEL_2)として宣言できます。
    • ビューv1の最初のクエリ ブロックはQB_NAME(v1_1, v2.v1@SEL_2)として宣言できます。
    • ビューv1の 2 番目のクエリ ブロックはQB_NAME(v1_2, v2.v1@SEL_2 .@SEL_2)として宣言できます。

ステップ2: ターゲットヒントを追加する

ビューのクエリブロックにQB_NAMEヒントを定義した後、ビュー内で有効にするために、必要なクエリブロックで有効になるヒントのヒントをViewName@QueryBlockName形式で追加します。例：

• ビューv2の最初のクエリ ブロックにMERGE_JOIN()ヒントを指定します。

  SELECT /*+ QB_NAME(v2_1, v2) merge_join(t@v2_1) */ * FROM v2;
  

• ビューv2の 2 番目のクエリ ブロックにヒントMERGE_JOIN()とSTREAM_AGG()指定します。

  SELECT /*+ QB_NAME(v2_2, v2.@SEL_2) merge_join(t1@v2_2) stream_agg(@v2_2) */ * FROM v2;
  

• ビューv1の最初のクエリ ブロックにHASH_JOIN()ヒントを指定します。

  SELECT /*+ QB_NAME(v1_1, v2.v1@SEL_2) hash_join(t@v1_1) */ * FROM v2;
  

• ビューv1の 2 番目のクエリ ブロックにヒントHASH_JOIN()とHASH_AGG()指定します。

  SELECT /*+ QB_NAME(v1_2, v2.v1@SEL_2 .@SEL_2) hash_join(t1@v1_2) hash_agg(@v1_2) */ * FROM v2;
  

クエリ全体に影響するヒント

このカテゴリのヒントは、最初のSELECT 、 UPDATE 、またはDELETEキーワードの後にのみ指定できます。これは、このクエリの実行時に指定されたシステム変数の値を変更することと同等です。ヒントの優先順位は、既存のシステム変数の優先順位よりも高くなります。

注記：

このカテゴリのヒントにはオプションの隠し変数@QB_NAMEありますが、変数を指定した場合でもヒントはクエリ全体に適用されます。

NO_INDEX_MERGE()

ヒントNO_INDEX_MERGE()は、オプティマイザーのインデックス マージ機能を無効にします。

たとえば、次のクエリではインデックスのマージは使用されません。

select /*+ NO_INDEX_MERGE() */ * from t where t.a > 0 or t.b > 0;

このヒントに加えて、 tidb_enable_index_mergeシステム変数を設定することで、この機能を有効にするかどうかも制御できます。

注記：

• NO_INDEX_MERGEはUSE_INDEX_MERGEよりも優先度が高くなります。両方のヒントが使用されている場合、 USE_INDEX_MERGE効果がありません。
• サブクエリの場合、 NO_INDEX_MERGEサブクエリの最も外側のレベルに配置された場合にのみ有効になります。

USE_TOJA(ブール値)

boolean_valueパラメータはTRUEまたはFALSEです。7 ヒントは、オプティマイザがin条件（サブクエリを含む）を結合および集計演算に変換できるようにします。一方、 USE_TOJA(TRUE)ヒントUSE_TOJA(FALSE)この機能を無効にします。

たとえば、次のクエリはin (select t2.a from t2) subq対応する結合および集計操作に変換します。

select /*+ USE_TOJA(TRUE) */ t1.a, t1.b from t1 where t1.a in (select t2.a from t2) subq;

このヒントに加えて、 tidb_opt_insubq_to_join_and_aggシステム変数を設定することで、この機能を有効にするかどうかも制御できます。

最大実行時間(N)

MAX_EXECUTION_TIME(N)ヒントは、サーバーが文の実行を終了させるまでの制限時間N （ミリ秒単位のタイムアウト値）を設定します。次のヒントでは、 MAX_EXECUTION_TIME(1000)タイムアウトが 1000 ミリ秒（つまり 1 秒）であることを意味します。

select /*+ MAX_EXECUTION_TIME(1000) */ * from t1 inner join t2 where t1.id = t2.id;

このヒントに加えて、 global.max_execution_timeシステム変数はステートメントの実行時間を制限することもできます。

メモリクォータ(N)

MEMORY_QUOTA(N)ヒントは、文が使用できるメモリ量に制限N （MB または GB 単位のしきい値）を設定します。文のメモリ使用量がこの制限を超えると、TiDB は文の制限超過動作に基づいてログメッセージを生成するか、文を終了します。

次のヒントでは、 MEMORY_QUOTA(1024 MB)メモリ使用量が 1024 MB に制限されていることを意味します。

select /*+ MEMORY_QUOTA(1024 MB) */ * from t;

このヒントに加えて、 tidb_mem_quota_queryシステム変数を使用してステートメントのメモリ使用量を制限することもできます。

READ_CONSISTENT_REPLICA()

READ_CONSISTENT_REPLICA()ヒントは、TiKVフォロワーノードから一貫性のあるデータを読み取る機能を有効にします。例:

select /*+ READ_CONSISTENT_REPLICA() */ * from t;

このヒントに加えて、環境変数tidb_replica_read 'follower'または'leader'に設定することで、この機能を有効にするかどうかも制御できます。

IGNORE_PLAN_CACHE()

IGNORE_PLAN_CACHE()のヒントは、現在のprepareステートメントを処理するときにプラン キャッシュを使用しないようにオプティマイザーに通知します。

このヒントは、 プランキャッシュの準備が有効な場合に、特定の種類のクエリのプラン キャッシュを一時的に無効にするために使用されます。

次の例では、 prepareステートメントを実行するときにプラン キャッシュが強制的に無効になります。

prepare stmt from 'select  /*+ IGNORE_PLAN_CACHE() */ * from t where t.id = ?';

ストレート結合()

STRAIGHT_JOIN()のヒントは、結合プランを生成するときに、 FROM番目の句のテーブル名の順序でテーブルを結合するようにオプティマイザーに通知します。

SELECT /*+ STRAIGHT_JOIN() */ * FROM t t1, t t2 WHERE t1.a = t2.a;

注記：

• STRAIGHT_JOIN LEADINGよりも優先度が高くなります。両方のヒントが使用されている場合、 LEADING効果がありません。
• STRAIGHT_JOINヒントよりも一般的なLEADINGヒントを使用することをお勧めします。

NTH_PLAN(N)

ヒントNTH_PLAN(N)は、物理的な最適化中に見つかったN番目の物理プランを選択するようにオプティマイザーに通知します。5 N正の整数である必要があります。

指定されたN物理最適化の検索範囲を超える場合、TiDB は警告を返し、このヒントを無視する戦略に基づいて最適な物理プランを選択します。

カスケード プランナーが有効な場合、このヒントは効果がありません。

次の例では、オプティマイザーは物理的な最適化中に見つかった 3 番目の物理プランを強制的に選択します。

SELECT /*+ NTH_PLAN(3) */ count(*) from t where a > 5;

注記：

NTH_PLAN(N)は主にテスト用に使用されており、それ以降のバージョンとの互換性は保証されていません。このヒントは慎重に使用してください。

RESOURCE_GROUP(リソースグループ名)

RESOURCE_GROUP(resource_group_name) リソース管理でリソースを分離するために使用されます。このヒントは、指定されたリソースグループを使用して現在のステートメントを一時的に実行します。指定されたリソースグループが存在しない場合、このヒントは無視されます。

例：

SELECT /*+ RESOURCE_GROUP(rg1) */ * FROM t limit 10;

ヒントが効かない一般的な問題のトラブルシューティング

MySQLコマンドラインクライアントがヒントを削除するため、ヒントは有効になりません

MySQL 5.7.7より前のコマンドラインクライアントは、デフォルトでオプティマイザヒントを削除します。これらの以前のバージョンでヒント構文を使用する場合は、クライアントの起動時に--commentsオプションを追加してください。例： mysql -h 127.0.0.1 -P 4000 -uroot --comments

データベース名が指定されていないため、ヒントは有効になりません

接続の作成時にデータベース名を指定しないと、ヒントが機能しない可能性があります。例:

TiDB に接続するときは、 -Dオプションなしのmysql -h127.0.0.1 -P4000 -urootコマンドを使用し、次の SQL ステートメントを実行します。

SELECT /*+ use_index(t, a) */ a FROM test.t;
SHOW WARNINGS;

TiDB はテーブルtのデータベースを識別できないため、ヒントuse_index(t, a)は有効になりません。

+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1815 | use_index(.t, a) is inapplicable, check whether the table(.t) exists |
+---------+------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

クロステーブルクエリでデータベース名が明示的に指定されていないため、ヒントは有効になりません。

クロステーブルクエリを実行する際は、データベース名を明示的に指定する必要があります。そうしないと、ヒントが機能しない可能性があります。例：

USE test1;
CREATE TABLE t1(a INT, KEY(a));
USE test2;
CREATE TABLE t2(a INT, KEY(a));
SELECT /*+ use_index(t1, a) */ * FROM test1.t1, t2;
SHOW WARNINGS;

上記のステートメントでは、テーブルt1が現在のtest2データベースに存在しないため、 use_index(t1, a)ヒントは有効になりません。

+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1815 | use_index(test2.t1, a) is inapplicable, check whether the table(test2.t1) exists |
+---------+------+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

この場合、 use_index(t1, a)ではなくuse_index(test1.t1, a)使用してデータベース名を明示的に指定する必要があります。

ヒントは間違った場所に配置されているため、効果がありません

ヒントは、特定のキーワードの直後に配置されていない場合は効果を発揮しません。例:

SELECT * /*+ use_index(t, a) */ FROM t;
SHOW WARNINGS;

警告は次のとおりです。

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use [parser:8066]Optimizer hint can only be followed by certain keywords like SELECT, INSERT, etc. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

この場合、ヒントはSELECTキーワードの直後に配置する必要があります。詳細については、 構文番目のセクションをご覧ください。

INL_JOINヒントは有効になりません

INL_JOINヒントは、テーブル結合の列に組み込み関数が使用されている場合には効果がありません。

場合によっては、テーブルを結合する列で組み込み関数を使用すると、オプティマイザーがIndexJoinプランを選択できず、 INL_JOINのヒントも有効にならないことがあります。

たとえば、次のクエリは、テーブルを結合する列tnameで組み込み関数substr使用します。

CREATE TABLE t1 (id varchar(10) primary key, tname varchar(10));
CREATE TABLE t2 (id varchar(10) primary key, tname varchar(10));
EXPLAIN SELECT /*+ INL_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id=t2.id and SUBSTR(t1.tname,1,2)=SUBSTR(t2.tname,1,2);

実行プランは次のとおりです。

+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| id                           | estRows  | task      | access object | operator info                                                         |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
| HashJoin_12                  | 12500.00 | root      |               | inner join, equal:[eq(test.t1.id, test.t2.id) eq(Column#5, Column#6)] |
| ├─Projection_17(Build)       | 10000.00 | root      |               | test.t2.id, test.t2.tname, substr(test.t2.tname, 1, 2)->Column#6      |
| │ └─TableReader_19           | 10000.00 | root      |               | data:TableFullScan_18                                                 |
| │   └─TableFullScan_18       | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                        |
| └─Projection_14(Probe)       | 10000.00 | root      |               | test.t1.id, test.t1.tname, substr(test.t1.tname, 1, 2)->Column#5      |
|   └─TableReader_16           | 10000.00 | root      |               | data:TableFullScan_15                                                 |
|     └─TableFullScan_15       | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                        |
+------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------+
7 rows in set, 1 warning (0.01 sec)

SHOW WARNINGS;

+---------+------+------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                            |
+---------+------+------------------------------------------------------------------------------------+
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(t1, t2) */ or /*+ TIDB_INLJ(t1, t2) */ is inapplicable |
+---------+------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

前の例からわかるように、 INL_JOINヒントは効果がありません。これは、 IndexJoinのプローブ側としてProjectionまたはSelection演算子を使用できないというオプティマイザの制限によるものです。

TiDB v8.0.0 以降では、 tidb_enable_inl_join_inner_multi_patternをONに設定することでこの問題を回避できます。

SET @@tidb_enable_inl_join_inner_multi_pattern=ON;
Query OK, 0 rows affected (0.00 sec)

EXPLAIN SELECT /*+ INL_JOIN(t1, t2) */ * FROM t1, t2 WHERE t1.id=t2.id AND SUBSTR(t1.tname,1,2)=SUBSTR(t2.tname,1,2);
+------------------------------+--------------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| id                           | estRows      | task      | access object | operator info                                                                                                                              |
+------------------------------+--------------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_18                 | 12500.00     | root      |               | inner join, inner:Projection_14, outer key:test.t1.id, inner key:test.t2.id, equal cond:eq(Column#5, Column#6), eq(test.t1.id, test.t2.id) |
| ├─Projection_32(Build)       | 10000.00     | root      |               | test.t1.id, test.t1.tname, substr(test.t1.tname, 1, 2)->Column#5                                                                           |
| │ └─TableReader_34           | 10000.00     | root      |               | data:TableFullScan_33                                                                                                                      |
| │   └─TableFullScan_33       | 10000.00     | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                             |
| └─Projection_14(Probe)       | 100000000.00 | root      |               | test.t2.id, test.t2.tname, substr(test.t2.tname, 1, 2)->Column#6                                                                           |
|   └─TableReader_13           | 10000.00     | root      |               | data:TableRangeScan_12                                                                                                                     |
|     └─TableRangeScan_12      | 10000.00     | cop[tikv] | table:t2      | range: decided by [eq(test.t2.id, test.t1.id)], keep order:false, stats:pseudo                                                             |
+------------------------------+--------------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

INL_JOIN 、 INL_HASH_JOIN 、 INL_MERGE_JOINヒントは照合順序の非互換性のため有効になりません。

2つのテーブル間で結合キーの照合順序に互換性がない場合、 IndexJoin演算子を使用してクエリを実行することはできません。この場合、 INL_JOIN 、 INL_HASH_JOIN 、 INL_MERGE_JOINヒントは無効になります。例:

CREATE TABLE t1 (k varchar(8), key(k)) COLLATE=utf8mb4_general_ci;
CREATE TABLE t2 (k varchar(8), key(k)) COLLATE=utf8mb4_bin;
EXPLAIN SELECT /*+ tidb_inlj(t1) */ * FROM t1, t2 WHERE t1.k=t2.k;

実行プランは次のとおりです。

+-----------------------------+----------+-----------+----------------------+----------------------------------------------+
| id                          | estRows  | task      | access object        | operator info                                |
+-----------------------------+----------+-----------+----------------------+----------------------------------------------+
| HashJoin_19                 | 12487.50 | root      |                      | inner join, equal:[eq(test.t1.k, test.t2.k)] |
| ├─IndexReader_24(Build)     | 9990.00  | root      |                      | index:IndexFullScan_23                       |
| │ └─IndexFullScan_23        | 9990.00  | cop[tikv] | table:t2, index:k(k) | keep order:false, stats:pseudo               |
| └─IndexReader_22(Probe)     | 9990.00  | root      |                      | index:IndexFullScan_21                       |
|   └─IndexFullScan_21        | 9990.00  | cop[tikv] | table:t1, index:k(k) | keep order:false, stats:pseudo               |
+-----------------------------+----------+-----------+----------------------+----------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

上記のステートメントでは、 t1.kとt2.k照合順序に互換性がないため (それぞれutf8mb4_general_ciとutf8mb4_bin )、 INL_JOINまたはTIDB_INLJヒントは有効になりません。

SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------+
| Level   | Code | Message                                                                    |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(t1) */ or /*+ TIDB_INLJ(t1) */ is inapplicable |
+---------+------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

INL_JOINヒントは結合順序により有効になりません

INL_JOIN(t1, t2)またはTIDB_INLJ(t1, t2)ヒントは、 t1とt2 、 IndexJoin演算子を使用して直接結合するのではなく、 IndexJoin演算子内の内部テーブルとして動作させ、他のテーブルと結合することを意味的に指示します。例:

EXPLAIN SELECT /*+ inl_join(t1, t3) */ * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id AND t1.id = t3.id;
+---------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                              | estRows  | task      | access object | operator info                                                                                                                                                           |
+---------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_16                    | 15625.00 | root      |               | inner join, inner:TableReader_13, outer key:test.t2.id, test.t1.id, inner key:test.t3.id, test.t3.id, equal cond:eq(test.t1.id, test.t3.id), eq(test.t2.id, test.t3.id) |
| ├─IndexJoin_34(Build)           | 12500.00 | root      |               | inner join, inner:TableReader_31, outer key:test.t2.id, inner key:test.t1.id, equal cond:eq(test.t2.id, test.t1.id)                                                     |
| │ ├─TableReader_40(Build)       | 10000.00 | root      |               | data:TableFullScan_39                                                                                                                                                   |
| │ │ └─TableFullScan_39          | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                                                                          |
| │ └─TableReader_31(Probe)       | 10000.00 | root      |               | data:TableRangeScan_30                                                                                                                                                  |
| │   └─TableRangeScan_30         | 10000.00 | cop[tikv] | table:t1      | range: decided by [test.t2.id], keep order:false, stats:pseudo                                                                                                          |
| └─TableReader_13(Probe)         | 12500.00 | root      |               | data:TableRangeScan_12                                                                                                                                                  |
|   └─TableRangeScan_12           | 12500.00 | cop[tikv] | table:t3      | range: decided by [test.t2.id test.t1.id], keep order:false, stats:pseudo                                                                                               |
+---------------------------------+----------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

前の例では、 t1とt3 IndexJoinによって直接結合されていません。

t1とt3間で直接IndexJoin実行するには、まずLEADING(t1, t3)ヒント使用してt1とt3の結合順序を指定し、次にINL_JOINヒントを使用して結合アルゴリズムを指定します。例:

EXPLAIN SELECT /*+ leading(t1, t3), inl_join(t3) */ * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id AND t1.id = t3.id;
+---------------------------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------+
| id                              | estRows  | task      | access object | operator info                                                                                                       |
+---------------------------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------+
| Projection_12                   | 15625.00 | root      |               | test.t1.id, test.t1.name, test.t2.id, test.t2.name, test.t3.id, test.t3.name                                        |
| └─HashJoin_21                   | 15625.00 | root      |               | inner join, equal:[eq(test.t1.id, test.t2.id) eq(test.t3.id, test.t2.id)]                                           |
|   ├─TableReader_36(Build)       | 10000.00 | root      |               | data:TableFullScan_35                                                                                               |
|   │ └─TableFullScan_35          | 10000.00 | cop[tikv] | table:t2      | keep order:false, stats:pseudo                                                                                      |
|   └─IndexJoin_28(Probe)         | 12500.00 | root      |               | inner join, inner:TableReader_25, outer key:test.t1.id, inner key:test.t3.id, equal cond:eq(test.t1.id, test.t3.id) |
|     ├─TableReader_34(Build)     | 10000.00 | root      |               | data:TableFullScan_33                                                                                               |
|     │ └─TableFullScan_33        | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                      |
|     └─TableReader_25(Probe)     | 10000.00 | root      |               | data:TableRangeScan_24                                                                                              |
|       └─TableRangeScan_24       | 10000.00 | cop[tikv] | table:t3      | range: decided by [test.t1.id], keep order:false, stats:pseudo                                                      |
+---------------------------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)

ヒントを使用するとCan't find a proper physical plan for this query 」というエラーが発生します。

Can't find a proper physical plan for this queryエラーは次のシナリオで発生する可能性があります。

• クエリ自体はインデックスを順番に読み取る必要はありません。つまり、このクエリでは、ヒントを使用しない限り、オプティマイザはインデックスを順番に読み取るプランを生成しません。この場合、ヒントORDER_INDEX指定されていると、このエラーが発生します。この問題を解決するには、対応するヒントORDER_INDEXを削除してください。
• クエリは、 NO_JOIN関連するヒントを使用して、可能なすべての結合方法を除外します。

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
EXPLAIN SELECT /*+ NO_HASH_JOIN(t1), NO_MERGE_JOIN(t1) */ * FROM t1, t2 WHERE t1.a=t2.a;
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query

• システム変数tidb_opt_enable_hash_join OFFに設定され、他のすべての結合タイプも除外されます。

CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT);
set tidb_opt_enable_hash_join=off;
EXPLAIN SELECT /*+ NO_MERGE_JOIN(t1) */ * FROM t1, t2 WHERE t1.a=t2.a;
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query