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

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

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

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

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

TiDB は、 Prepare / Executeステートメントと同様に、一部の非PREPAREステートメントの実行プラン キャッシュもサポートしています。詳細については、 準備されていないプラン キャッシュを参照してください。

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

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

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

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

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

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

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

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

• 実行プランがキャッシュされるかどうかに関係なく、SQL バインディングの影響を受けます。キャッシュされていない実行プラン (最初のExecute ) の場合、これらのプランは既存の SQL バインディングの影響を受けます。キャッシュされた実行プランの場合、新しい SQL バインディングが作成されると、これらのプランは無効になります。
• キャッシュされたプランは、統計、最適化ルール、式によるブロックリストのプッシュダウンの変更の影響を受けません。
• Executeのパラメータが異なることを考慮して、実行プラン キャッシュは、適応性を確保するために、特定のパラメータ値に密接に関連する一部の積極的なクエリ最適化方法を禁止します。これにより、クエリ プランが特定のパラメーター値に対して最適ではなくなる可能性があります。たとえば、クエリのフィルター条件はwhere a > ? And a < ?で、最初のExecuteステートメントのパラメーターはそれぞれ2と1です。これら 2 つのパラメータが次回の実行時に1と2になる可能性がある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の特定のセットが予期しない動作をしていることが判明した場合は、 ignore_plan_cache() SQL ヒントを使用して、現在のステートメントに対する実行プラン キャッシュの使用をスキップできます。ただし、前述のステートメントを例として使用します。

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)

プリペアドプランキャッシュの診断

一部のクエリまたはプランはキャッシュできません。 SHOW WARNINGSステートメントを使用して、クエリまたはプランがキャッシュされているかどうかを確認できます。キャッシュされていない場合は、結果で失敗の理由を確認できます。例えば：

mysql> PREPARE st FROM 'SELECT * FROM t WHERE a > (SELECT MAX(a) FROM t)';  -- The query contains a subquery and cannot be cached.

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;  -- Checks the reason why the query plan cannot be cached.

+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1105 | skip plan-cache: sub-queries are un-cacheable |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> prepare st from 'select * from t where a<?';

Query OK, 0 rows affected (0.00 sec)

mysql> set @a='1';

Query OK, 0 rows affected (0.00 sec)

mysql> execute st using @a;  -- The optimization converts a non-INT type to an INT type, and the execution plan might change with the change of the parameter, so TiDB does not cache the plan.

Empty set, 1 warning (0.01 sec)

mysql> show warnings;

+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1105 | skip plan-cache: '1' may be converted to INT |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

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

プリペアドプランキャッシュを使用すると、メモリオーバーヘッドが発生します。各 TiDB インスタンスのすべてのセッションのキャッシュされた実行プランによる合計メモリ消費量を表示するには、Grafana の「プラン・キャッシュ・メモリー使用量」モニター・パネルを使用できます。

ノート：

Golangのメモリ再利用メカニズムと一部のカウントされていないメモリ構造のため、Grafana に表示されるメモリは実際のヒープメモリ使用量と等しくありません。 Grafana で表示されるメモリと実際のヒープメモリ使用量の間には、±20% 程度の偏差があることがテストされています。

各 TiDB インスタンスにキャッシュされた実行プランの合計数を表示するには、Grafana の「プランキャッシュプラン番号」パネルを使用できます。

以下は、Grafana の[プラン キャッシュ メモリ使用量]パネルと[プラン キャッシュ プラン数]パネルの例です。

v7.1.0 以降、システム変数tidb_session_plan_cache_sizeを構成することで、各セッションでキャッシュできるプランの最大数を制御できます。さまざまな環境での推奨値は次のとおりで、監視パネルに応じて調整できます。

• TiDBサーバーインスタンスのメモリしきい値が 64 GiB 以下の場合は、 tidb_session_plan_cache_size ～ 50を設定します。
• TiDBサーバーインスタンスのメモリしきい値が 64 GiB を超える場合は、 tidb_session_plan_cache_size ～ 100を設定します。

v7.1.0 以降、システム変数tidb_plan_cache_max_plan_sizeを使用してキャッシュできるプランの最大サイズを制御できます。デフォルト値は 2 MB です。プランのサイズがこの値を超える場合、プランはキャッシュされません。

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コマンドとDEALLOCATE PREPAREステートメントを無視します。

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

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)

モニタリング

TiDB ページのExecutorセクションのGrafana ダッシュボードには、「プラン キャッシュ OPS を使用したクエリ」グラフと「プラン キャッシュ ミス OPS」グラフがあります。これらのグラフを使用して、TiDB とアプリケーションの両方が SQL プラン キャッシュが正しく動作できるように正しく構成されているかどうかを確認できます。同じページのサーバーセクションには、「プリペアドステートメント数」グラフが表示されます。アプリケーションがプリペアド ステートメントを使用している場合、このグラフはゼロ以外の値を示します。これは、SQL プラン キャッシュが正しく機能するために必要です。