重要
このページは英語版のページを機械翻訳しています。原文はこちらからご覧ください。

SQL準備実行プランキャッシュ

TiDBは、 PrepareおよびExecuteのクエリの実行プランキャッシュをサポートします。これには、準備されたステートメントの両方の形式が含まれます。

  • COM_STMT_PREPAREおよびCOM_STMT_EXECUTEプロトコル機能を使用します。
  • SQLステートメントPREPAREおよびEXECUTEの使用。

TiDBオプティマイザーは、これら2つのタイプのクエリを同じ方法で処理します。準備時に、パラメーター化されたクエリがAST(抽象構文ツリー)に解析され、キャッシュされます。後の実行では、保存されたASTと特定のパラメータ値に基づいて実行プランが生成されます。

実行プランキャッシュが有効になっている場合、最初の実行でPrepareステートメントごとに、現在のクエリが実行プランキャッシュを使用できるかどうかを確認し、クエリがそれを使用できる場合は、生成された実行プランをLRUによって実装されたキャッシュに配置します(最近使用済み)リンクリスト。後続のExecuteのクエリでは、実行プランがキャッシュから取得され、可用性がチェックされます。チェックが成功した場合、実行プランを生成するステップはスキップされます。それ以外の場合、実行プランは再生成され、キャッシュに保存されます。

現在のバージョンのTiDBでは、 Prepareのステートメントが次の条件のいずれかを満たしている場合、クエリまたはプランはキャッシュされません。

  • Unionには、 SELECT 、およびUPDATE ExceptのSQL Intersectが含まれてINSERT DELETE
  • クエリは、パーティションテーブルまたは一時テーブル、あるいは生成された列を含むテーブルにアクセスします。
  • クエリには、 select * from t where a > (select ...)などのサブクエリが含まれています。
  • クエリには、 select /*+ ignore_plan_cache() */ * from tなどのignore_plan_cacheのヒントが含まれています。
  • クエリには、 select * from t where a>? and b>@xなどの?以外の変数(システム変数またはユーザー定義変数を含む)が含まれています。
  • row_countには、キャッシュできない関数がversionれてcurrent_role current_user last_insert_id database() like connection_id user
  • クエリには、 Limit ?Limit 10, ?など、 Limitの後に?が含まれます。 ?の特定の値はクエリのパフォーマンスに大きな影響を与えるため、このようなクエリはキャッシュされません。
  • クエリには、 Order By ?などのOrder Byの後に?が含まれます。このようなクエリは、 ?で指定された列に基づいてデータを並べ替えます。異なる列を対象とするクエリが同じ実行プランを使用している場合、結果は正しくありません。したがって、このようなクエリはキャッシュされません。ただし、クエリがOrder By a+?などの一般的なクエリである場合は、キャッシュされます。
  • クエリには、 Group By?などのGroup Byの後に?が含まれます。このようなクエリは、 ?で指定された列に基づいてデータをグループ化します。異なる列を対象とするクエリが同じ実行プランを使用している場合、結果は正しくありません。したがって、このようなクエリはキャッシュされません。ただし、クエリがGroup By a+?などの一般的なクエリである場合は、キャッシュされます。
  • クエリには、 (partition by year order by sale rows ? preceding)などのWindow Frameウィンドウ関数の定義に?が含まれています。ウィンドウ関数の他の場所に?が表示されている場合、クエリはキャッシュされます。
  • クエリには、 c_int >= ?c_int in (?, ?)などのintstringを比較するためのパラメータが含まれています。ここで、 ?set @x='123'などの文字列型を示します。クエリ結果がMySQLと互換性があることを確認するには、各クエリでパラメータを調整して、そのようなクエリがキャッシュされないようにする必要があります。
  • プランはTiFlashにアクセスしようとします。
  • ほとんどの場合、現在のPrepareステートメントにパラメーターがない場合を除き、 TableDualを含むプランはキャッシュされません。

Prepareはセッション間で実行できないため、LRUリンクリストはセッションレベルのキャッシュとして設計されていExecute 。 LRUリストの各要素は、キーと値のペアです。値は実行プランであり、キーは次の部分で構成されています。

  • Executeが実行されるデータベースの名前
  • PrepareステートメントのID、つまりPREPAREキーワードの後の名前
  • 現在のスキーマバージョン。これは、DDLステートメントが正常に実行されるたびに更新されます。
  • Execute実行時のSQLモード
  • time_zoneのシステム変数の値である現在のタイムゾーン
  • sql_select_limitシステム変数の値

上記の情報の変更(たとえば、データベースの切り替え、 Prepareステートメントの名前変更、DDLステートメントの実行、SQLモード/ time_zoneの値の変更)、またはLRUキャッシュ除去メカニズムにより、実行時に実行プランのキャッシュミスが発生します。

実行プランキャッシュがキャッシュから取得された後、TiDBは最初に実行プランがまだ有効かどうかをチェックします。現在のExecuteステートメントが明示的なトランザクションで実行され、参照されたテーブルがトランザクションの事前注文ステートメントで変更された場合、このテーブルにアクセスするキャッシュされた実行プランにUnionScan演算子が含まれていないため、実行できません。

検証テストに合格すると、実行プランのスキャン範囲が現在のパラメータ値に従って調整され、データクエリの実行に使用されます。

実行プランのキャッシュとクエリのパフォーマンスについては、注目に値する点がいくつかあります。

  • 実行プランがキャッシュされているかどうかに関係なく、SQLバインディングの影響を受けます。キャッシュされていない実行プラン(最初のExecute )の場合、これらのプランは既存のSQLバインディングの影響を受けます。キャッシュされた実行プランの場合、新しいSQLバインディングが作成されると、これらのプランは無効になります。
  • キャッシュされたプランは、統計の変更、最適化ルール、および式によるブロックリストのプッシュダウンの影響を受けません。
  • Executeのパラメーターが異なることを考慮すると、実行プランキャッシュは、適応性を確保するために特定のパラメーター値に密接に関連するいくつかの積極的なクエリ最適化メソッドを禁止します。これにより、クエリプランが特定のパラメータ値に対して最適でない場合があります。たとえば、クエリのフィルター条件はwhere a > ? And a < ?で、最初のExecuteステートメントのパラメーターはそれぞれ21です。これらの2つのパラメーターが次の実行時に12になる可能性があることを考慮すると、オプティマイザーは現在のパラメーター値に固有の最適TableDual実行プランを生成しません。
  • キャッシュの無効化と削除が考慮されていない場合、実行プランキャッシュはさまざまなパラメータ値に適用され、理論的には特定の値に対して最適でない実行プランが発生します。たとえば、フィルター条件がwhere a < ?で、最初の実行に使用されるパラメーター値が1の場合、オプティマイザーは最適なIndexScan実行プランを生成し、それをキャッシュに入れます。その後の実行で、値が10000になった場合は、 TableScanプランの方が適している可能性があります。ただし、実行プランキャッシュのため、以前に生成されたIndexScanが実行に使用されます。したがって、実行プランキャッシュは、クエリが単純で(コンパイルの比率が高い)、実行プランが比較的固定されているアプリケーションシナリオに適しています。

v6.1.0以降、実行プランキャッシュはデフォルトで有効になっています。準備された計画キャッシュは、システム変数tidb_enable_prepared_plan_cacheを介して制御できます。

ノート:

実行プランのキャッシュ機能は、 Prepare / Executeクエリにのみ適用され、通常のクエリでは有効になりません。

実行プランのキャッシュ機能を有効にした後、セッションレベルのシステム変数last_plan_from_cacheを使用して、前のExecuteのステートメントがキャッシュされた実行プランを使用したかどうかを確認できます。次に例を示します。

MySQL [test]> create table t(a int);
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> prepare stmt from 'select * from t where a = ?';
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> set @a = 1;
Query OK, 0 rows affected (0.00 sec)

-- The first execution generates an execution plan and saves it in the cache.
MySQL [test]> execute stmt using @a;
Empty set (0.00 sec)
MySQL [test]> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
| 0                      |
+------------------------+
1 row in set (0.00 sec)

-- The second execution hits the cache.
MySQL [test]> execute stmt using @a;
Empty set (0.00 sec)
MySQL [test]> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
| 1                      |
+------------------------+
1 row in set (0.00 sec)

実行プランキャッシュが原因でPrepareの特定のセットに予期しない動作が見られる場合は、 Execute SQLヒントを使用して、現在のステートメントの実行プランキャッシュの使用をスキップできignore_plan_cache() 。それでも、例として上記のステートメントを使用してください。

MySQL [test]> prepare stmt from 'select /*+ ignore_plan_cache() */ * from t where a = ?';
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> set @a = 1;
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> execute stmt using @a;
Empty set (0.00 sec)
MySQL [test]> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
| 0                      |
+------------------------+
1 row in set (0.00 sec)
MySQL [test]> execute stmt using @a;
Empty set (0.00 sec)
MySQL [test]> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
| 0                      |
+------------------------+
1 row in set (0.00 sec)

プリペアドプランキャッシュのメモリ管理

プリペアドプランキャッシュを使用すると、メモリのオーバーヘッドが発生します。内部テストでは、キャッシュされた各プランは平均100KiBのメモリを消費します。プランキャッシュは現在SESSIONレベルであるため、合計メモリ消費量は約the number of sessions * the average number of cached plans in a session * 100 KiBです。

たとえば、現在のTiDBインスタンスには50の同時実行セッションがあり、各セッションには約100のキャッシュされたプランがあります。合計メモリ消費量は約50 * 100 * 100 KiB = 512 MBです。

システム変数tidb_prepared_plan_cache_sizeを構成することにより、各セッションでキャッシュできるプランの最大数を制御できます。さまざまな環境での推奨値は次のとおりです。

  • TiDBサーバーインスタンスのメモリしきい値が<=64GiBの場合、 tidb_prepared_plan_cache_size50に設定します。
  • TiDBサーバーインスタンスのメモリしきい値が>64GiBの場合、 tidb_prepared_plan_cache_size100に設定します。

TiDBサーバーの未使用メモリが特定のしきい値未満になると、プランキャッシュのメモリ保護メカニズムがトリガーされ、キャッシュされたプランの一部が削除されます。

システム変数tidb_prepared_plan_cache_memory_guard_ratioを構成することにより、しきい値を制御できます。デフォルトのしきい値は0.1です。これは、TiDBサーバーの未使用メモリが合計メモリの10%未満(メモリの90%が使用されている)になると、メモリ保護メカニズムがトリガーされることを意味します。

メモリ制限により、プランキャッシュが失われる場合があります。 GrafanaダッシュボードでPlan Cache Miss OPSメトリックを表示すると、ステータスを確認できます。

メモリ制限により、プランキャッシュが失われる場合があります。

実行プランのキャッシュをクリアする

ADMIN FLUSH [SESSION | INSTANCE] PLAN_CACHEステートメントを実行すると、実行プランのキャッシュをクリアできます。

このステートメントで、 [SESSION | INSTANCE]は、現在のセッションまたはTiDBインスタンス全体のプランキャッシュをクリアするかどうかを指定します。スコープが指定されていない場合、上記のステートメントはデフォルトでSESSIONキャッシュに適用されます。

以下は、 SESSIONの実行プランのキャッシュをクリアする例です。

MySQL [test]> create table t (a int);
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> prepare stmt from 'select * from t';
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt;
Empty set (0.00 sec)

MySQL [test]> execute stmt;
Empty set (0.00 sec)

MySQL [test]> select @@last_plan_from_cache; -- Select the cached plan
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

MySQL [test]> admin flush session plan_cache; -- Clear the cached plan of the current session
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt;
Empty set (0.00 sec)

MySQL [test]> select @@last_plan_from_cache; -- The cached plan cannot be selected again, because it has been cleared
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

現在、TiDBはGLOBALの実行プランキャッシュのクリアをサポートしていません。つまり、TiDBクラスタ全体のキャッシュされたプランをクリアすることはできません。 GLOBAL実行プランのキャッシュをクリアしようとすると、次のエラーが報告されます。

MySQL [test]> admin flush global plan_cache;
ERROR 1105 (HY000): Do not support the 'admin flush global scope.'

COM_STMT_CLOSEコマンドとDEALLOCATEPREPAREステートメントを無視しDEALLOCATE PREPARE

SQLステートメントの構文解析コストを削減するために、 deallocate prepareを実行する前にprepare stmt回実行し、次にexecute stmt回実行することをお勧めします。

MySQL [test]> prepare stmt from '...'; -- Prepare once
MySQL [test]> execute stmt using ...;  -- Execute once
MySQL [test]> ...
MySQL [test]> execute stmt using ...;  -- Execute multiple times
MySQL [test]> deallocate prepare stmt; -- Release the prepared statement

実際には、以下に示すように、 execute stmtを実行した後、毎回deallocate prepareを実行することに慣れている場合があります。

MySQL [test]> prepare stmt from '...'; -- Prepare once
MySQL [test]> execute stmt using ...;
MySQL [test]> deallocate prepare stmt; -- Release the prepared statement
MySQL [test]> prepare stmt from '...'; -- Prepare twice
MySQL [test]> execute stmt using ...;
MySQL [test]> deallocate prepare stmt; -- Release the prepared statement

このような場合、最初に実行されたステートメントによって取得されたプランは、2番目に実行されたステートメントによって再利用することはできません。

この問題に対処するには、システム変数をtidb_ignore_prepared_cache_close_stmtからONに設定して、TiDBがprepare stmtを閉じるコマンドを無視するようにします。

mysql> set @@tidb_ignore_prepared_cache_close_stmt=1;  -- Enable the variable
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from 'select * from t'; -- Prepare once
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt;                        -- Execute once
Empty set (0.00 sec)

mysql> deallocate prepare stmt;             -- Release after the first execute
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from 'select * from t'; -- Prepare twice
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt;                        -- Execute twice
Empty set (0.00 sec)

mysql> select @@last_plan_from_cache;       -- Reuse the last plan
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)