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

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 、またはDELETEキーワードに続く/*+ ... */ UPDATEコメント内で指定されます。オプティマイザーヒントは現在、 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つのサブクエリは、名前がそれぞれsel_2とsel_3である2番目と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は、TiDB3.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は、TiDB3.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は、TiDB3.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 ...]])ヒントは、特定のストレージエンジンから特定のテーブルを読み取るようにオプティマイザに指示します。現在、このヒントは2つのストレージエンジンパラメータ（ TIKVとTIFLASH ）をサポートしています。テーブルにエイリアスがある場合は、エイリアスを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のヒントのHASH_JOINかが同時に使用されINL_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）

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)は、メモリ使用量が1024MBに制限されていることを意味します。

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（）

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)は主にテストに使用され、それ以降のバージョンでは互換性が保証されません。このヒントは注意して使用してください。