オプティマイザーのヒント
TiDB は、 MySQL 5.7で導入されたコメントのような構文に基づいたオプティマイザヒントをサポートしています。たとえば、一般的な構文の 1 つは/*+ HINT_NAME([t1_name [, t2_name] ...]) */です。TiDB オプティマイザがあまり最適でないクエリプランを選択する場合は、オプティマイザヒントの使用が推奨されます。
ヒントが有効にならない状況に遭遇した場合は、 ヒントが効かない一般的な問題のトラブルシューティング参照してください。
構文
オプティマイザ ヒントは大文字と小文字を区別せず、SQL ステートメントのSELECT 、 UPDATE 、またはDELETEキーワードに続く/*+ ... */コメント内で指定されます。オプティマイザ ヒントは現在、 INSERTステートメントではサポートされていません。
複数のヒントは、コンマで区切って指定できます。たとえば、次のクエリでは 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 、またはDELETE UPDATEキーワードの後に続くことができます。ヒントの有効範囲を制御するには、ヒントでクエリ ブロックの名前を使用します。クエリ内の各テーブルを正確に識別することで、ヒント パラメータを明確にすることができます (テーブル名またはエイリアスが重複している場合)。ヒントでクエリ ブロックが指定されていない場合、ヒントはデフォルトで現在のブロックで有効になります。
例えば：
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を指定していない場合、sel_22 番目のクエリ ブロックSELECTに対して無効な名前になります。
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結合された外部テーブルにハッシュ テーブルを作成することです。7 INL_HASH_JOINはメモリ使用量の制限が固定されていますが、 INL_JOINで使用されるメモリは内部テーブルで一致する行数によって異なります。
NO_INDEX_HASH_JOIN(t1_name [, tl_name ...])
ヒントNO_INDEX_HASH_JOIN(t1_name [, tl_name ...])は、指定されたテーブルに対してインデックス ネスト ループ ハッシュ結合アルゴリズムを使用しないようにオプティマイザに指示します。
INL_マージ結合
ヒント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 ...])ヒントは、指定されたテーブルで Shuffle Join アルゴリズムを使用するようにオプティマイザに指示します。このヒントは 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テーブルにアクセスするときのフィルタ条件のままです。
HASH_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 |
+----------------------------+---------+-----------+---------------------+-------------------------------+
オプティマイザは、このクエリに対してLimit + IndexScan(keep order: true)とTopN + IndexScan(keep order: false) 2 種類のプランを生成します。 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)として宣言するか、@SEL_1を省略して単にQB_NAME(v_1, v)と記述できます。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()は、オプティマイザのインデックス マージ機能を無効にします。
たとえば、次のクエリではインデックス マージは使用されません。
select /*+ NO_INDEX_MERGE() */ * from t where t.a > 0 or t.b > 0;
このヒントに加えて、 tidb_enable_index_mergeシステム変数を設定することで、この機能を有効にするかどうかも制御できます。
注記：
NO_INDEX_MERGEUSE_INDEX_MERGEよりも優先度が高くなります。両方のヒントが使用される場合、USE_INDEX_MERGE効果がありません。- サブクエリの場合、
NO_INDEX_MERGEサブクエリの最も外側のレベルに配置された場合にのみ有効になります。
USE_TOJA(ブール値)
boolean_valueパラメータはTRUEまたはFALSEになります。 USE_TOJA(TRUE)ヒントにより、オプティマイザはin条件 (サブクエリを含む) を結合および集計操作に変換できます。比較すると、 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コマンドラインクライアントがヒントを削除するため、ヒントは有効になりません。
5.7.7 より前の MySQL コマンドライン クライアントは、デフォルトでオプティマイザ ヒントを削除します。これらの以前のバージョンでヒント構文を使用する場合は、クライアントの起動時に--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演算子を使用できないというオプティマイザーの制限によるものです。
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)ヒントは、 IndexJoin演算子を使用して直接結合するのではなく、 t1とt2 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