TiDB Cloud
TiDB Self-Managed
Learning Center
お問い合わせ

オプティマイザーのヒント

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

ノート：

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

構文

オプティマイザーのヒントは、大文字と小文字が区別されず、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_2は 2 番目のSELECTクエリ ブロックの無効な名前になります。

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をお勧めします。

INL_JOIN(t1_name [, tl_name ...])

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

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

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をお勧めします。

INL_HASH_JOIN

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

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をお勧めします。

HASH_AGG()

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

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

STREAM_AGG()

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

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

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;

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

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

ノート：

オプティマイザで別のスキーマのテーブルを使用する場合は、スキーマ名を明示的に指定する必要があります。例えば：

SELECT /*+ READ_FROM_STORAGE(TIFLASH[test1.t1,test2.t2]) */ t1.a FROM test1.t t1, test2.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 ...])ヒントは、インデックス マージ メソッドを使用して特定のテーブルにアクセスするようオプティマイザに指示します。指定されたインデックスのリストはオプションのパラメーターです。リストを明示的に指定すると、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()ヒントは既に存在します。
  • クエリに外部結合が含まれています。
  • MERGE_JOIN 、 INL_JOIN 、 INL_HASH_JOIN 、およびHASH_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 、 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です。 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を設定すると、この機能を有効にするかどうかも制御されます。

MAX_EXECUTION_TIME(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システム変数はステートメントの実行時間を制限することもできます。

MEMORY_QUOTA(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ステートメントを処理するときに Plan Cache を使用しないようにオプティマイザーに通知します。

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

次の例では、 prepareステートメントの実行時に Plan Cache が強制的に無効にされます。

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

STRAIGHT_JOIN()

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番目の物理的な計画を選択することを思い出させます。 Nは正の整数でなければなりません。

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

このヒントは、カスケード プランナーが有効になっている場合は有効になりません。

次の例では、オプティマイザーは、物理的な最適化中に見つかった 3 番目の物理的な計画を選択するように強制されます。

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

ノート：

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

このページは役に立ちましたか？