オプティマイザヒント
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