非トランザクション DML ステートメント

このドキュメントでは、TiDB における非トランザクション DML ステートメントの使用シナリオ、使用方法、および制限事項について説明します。さらに、実装原理と共通の問題についても説明します。

非トランザクション DML ステートメントは、順番に実行される複数の SQL ステートメント (つまり、複数のバッチ) に分割された DML ステートメントです。トランザクションの原子性と分離性を犠牲にして、バッチ データ処理のパフォーマンスと使いやすさを向上させます。

非トランザクション DML ステートメントにはINSERT 、 UPDATE 、およびDELETEが含まれますが、現在 TiDB がサポートしているのはDELETEのみです。詳細な構文については、 BATCHを参照してください。

ノート：

非トランザクション DML ステートメントは、ステートメントの原子性と分離を保証せず、元の DML ステートメントと同等ではありません。

使用シナリオ

大規模なデータ処理のシナリオでは、多くの場合、大量のデータ バッチに対して同じ操作を実行する必要があります。単一の SQL ステートメントを使用して直接操作を実行すると、トランザクション サイズが制限を超え、実行パフォーマンスに影響を与える可能性があります。

バッチ データ処理では、多くの場合、オンライン アプリケーション操作と時間またはデータが重複しません。同時操作が存在しない場合、分離 ( ACIDの I ) は不要です。バルク データ操作がべき等であるか、簡単に再試行できる場合も、原子性は不要です。アプリケーションがデータの分離も原子性も必要としない場合は、非トランザクション DML ステートメントの使用を検討できます。

非トランザクション DML ステートメントは、特定のシナリオで大規模なトランザクションのサイズ制限を回避するために使用されます。 1 つのステートメントを使用して、トランザクションを手動で分割する必要があるタスクを完了し、実行効率を高め、リソース消費を抑えます。

たとえば、期限切れのデータを削除する場合、アプリケーションが期限切れのデータにアクセスしないようにする場合は、非トランザクション DML ステートメントを使用してDELETEのパフォーマンスを向上させることができます。

前提条件

非トランザクション DML ステートメントを使用する前に、次の条件が満たされていることを確認してください。

• このステートメントは原子性を必要としないため、実行結果で一部の行を変更し、一部の行を変更しないままにすることができます。
• ステートメントがべき等であるか、エラー メッセージに従ってデータの一部を再試行する準備ができています。システム変数がtidb_redact_log = 1およびtidb_nontransactional_ignore_error = 1に設定されている場合、このステートメントはべき等でなければなりません。そうしないと、ステートメントが部分的に失敗したときに、失敗した部分を正確に特定できなくなります。
• 操作対象のデータには、他の同時書き込みはありません。つまり、他のステートメントによって同時に更新されることはありません。そうしないと、削除の欠落や誤った削除など、予期しない結果が生じる可能性があります。
• ステートメントは、ステートメント自体によって読み取られるデータを変更しません。そうしないと、次のバッチが前のバッチで書き込まれたデータを読み取るため、予期しない結果が生じやすくなります。
• ステートメントは制限を満たしています。
• この DML ステートメントで読み取りまたは書き込みを行うテーブルに対して、同時 DDL 操作を実行することはお勧めしません。

使用例

非トランザクション DML ステートメントを使用する

次のセクションでは、非トランザクション DML ステートメントの使用について例を挙げて説明します。

次のスキーマでテーブルtを作成します。

CREATE TABLE t (id INT, v INT, KEY(id));

Query OK, 0 rows affected

テーブルtにデータを挿入します。

INSERT INTO t VALUES (1, 2), (2, 3), (3, 4), (4, 5), (5, 6);

Query OK, 5 rows affected

次の操作では、非トランザクション DML ステートメントを使用して、テーブルtの列vの整数 6 より小さい値を持つ行を削除します。このステートメントは、バッチ サイズが 2 の 2 つの SQL ステートメントに分割され、 id列で分割されて実行されます。

BATCH ON id LIMIT 2 DELETE FROM t WHERE v < 6;

+----------------+---------------+
| number of jobs | job status    |
+----------------+---------------+
| 2              | all succeeded |
+----------------+---------------+
1 row in set

上記の非トランザクション DML ステートメントの削除結果を確認します。

SELECT * FROM t;

+----+---+
| id | v |
+----+---+
| 5  | 6 |
+----+---+
1 row in set

実行の進行状況を確認する

非トランザクション DML ステートメントの実行中に、 SHOW PROCESSLISTを使用して進行状況を表示できます。返された結果のTimeフィールドは、現在のバッチ実行の消費時間を示します。ログとスロー ログには、非トランザクション DML 実行中の各分割ステートメントの進行状況も記録されます。例えば：

SHOW PROCESSLIST;

+------+------+--------------------+--------+---------+------+------------+----------------------------------------------------------------------------------------------------+
| Id   | User | Host               | db     | Command | Time | State      | Info                                                                                               |
+------+------+--------------------+--------+---------+------+------------+----------------------------------------------------------------------------------------------------+
| 1203 | root | 100.64.10.62:52711 | test   | Query   | 0    | autocommit | /* job 506/500000 */ DELETE FROM `test`.`t1` WHERE `test`.`t1`.`_tidb_rowid` BETWEEN 2271 AND 2273 |
| 1209 | root | 100.64.10.62:52735 | <null> | Query   | 0    | autocommit | show full processlist                                                                              |
+------+------+--------------------+--------+---------+------+------------+----------------------------------------------------------------------------------------------------+

非トランザクション DML ステートメントを終了する

非トランザクション DML ステートメントを終了するには、 KILL TIDB <processlist_id>を使用できます。その後、TiDB は現在実行中のバッチ以降のすべてのバッチをキャンセルします。ログから実行結果を取得できます。

KILL TIDBの詳細については、リファレンスKILLを参照してください。

バッチ分割ステートメントを照会する

非トランザクション DML ステートメントの実行中に、DML ステートメントを複数のバッチに分割するためにステートメントが内部的に使用されます。このバッチ分割ステートメントをクエリするには、この非トランザクション DML ステートメントにDRY RUN QUERYを追加します。その後、TiDB はこのクエリと後続の DML 操作を実行しません。

次のステートメントは、 BATCH ON id LIMIT 2 DELETE FROM t WHERE v < 6の実行中にバッチ分割ステートメントをクエリします。

BATCH ON id LIMIT 2 DRY RUN QUERY DELETE FROM t WHERE v < 6;

+--------------------------------------------------------------------------------+
| query statement                                                                |
+--------------------------------------------------------------------------------+
| SELECT `id` FROM `test`.`t` WHERE (`v` < 6) ORDER BY IF(ISNULL(`id`),0,1),`id` |
+--------------------------------------------------------------------------------+
1 row in set

最初と最後のバッチに対応するステートメントをクエリします

非トランザクション DML ステートメントの最初と最後のバッチに対応する実際の DML ステートメントをクエリするには、この非トランザクション DML ステートメントにDRY RUNを追加します。そして、TiDB はバッチを分割するだけで、これらの SQL ステートメントを実行しません。多くのバッチが存在する可能性があるため、すべてのバッチが表示されるわけではなく、最初のバッチと最後のバッチのみが表示されます。

BATCH ON id LIMIT 2 DRY RUN DELETE FROM t WHERE v < 6;

+-------------------------------------------------------------------+
| split statement examples                                          |
+-------------------------------------------------------------------+
| DELETE FROM `test`.`t` WHERE (`id` BETWEEN 1 AND 2 AND (`v` < 6)) |
| DELETE FROM `test`.`t` WHERE (`id` BETWEEN 3 AND 4 AND (`v` < 6)) |
+-------------------------------------------------------------------+
2 rows in set

オプティマイザーのヒントを使用する

オプティマイザ ヒントが最初にDELETEステートメントでサポートされている場合、オプティマイザ ヒントは非トランザクションDELETEステートメントでもサポートされます。ヒントの位置は、通常のDELETEステートメントと同じです。

BATCH ON id LIMIT 2 DELETE /*+ USE_INDEX(t)*/ FROM t WHERE v < 6;

ベストプラクティス

非トランザクション DML ステートメントを使用するには、次の手順をお勧めします。

1. 適切な分割列を選択します。整数型または文字列型をお勧めします。
2. (オプション) 非トランザクション DML ステートメントにDRY RUN QUERYを追加し、クエリを手動で実行して、DML ステートメントの影響を受けるデータ範囲がほぼ正しいかどうかを確認します。
3. (オプション) 非トランザクション DML ステートメントにDRY RUNを追加し、クエリを手動で実行して、分割ステートメントと実行プランを確認します。インデックスの選択効率に注意する必要があります。
4. 非トランザクション DML ステートメントを実行します。
5. エラーが報告された場合は、エラー メッセージまたはログから特定の失敗したデータ範囲を取得し、再試行するか手動で処理します。

パラメータの説明

分割列の選択方法

非トランザクション DML ステートメントは、分割列であるデータ バッチ処理の基礎として列を使用します。実行効率を高めるために、インデックスを使用するには分割列が必要です。異なるインデックスと分割列によってもたらされる実行効率は、何十倍も異なる場合があります。分割列を選択するときは、次の提案を考慮してください。

• アプリケーションデータの分布がわかっている場合は、 WHEREの条件に従って、バッチ処理後にデータをより狭い範囲で分割する列を選択します。
  • 理想的には、条件WHEREは、分割列のインデックスを利用して、バッチごとにスキャンされるデータの量を減らすことができます。たとえば、各トランザクションの開始時刻と終了時刻を記録するトランザクション テーブルがあり、終了時刻が 1 か月より前のすべてのトランザクション レコードを削除したいとします。トランザクションの開始時間にインデックスがあり、トランザクションの開始時間と終了時間が比較的近い場合は、開始時間列を分割列として選択できます。
  • 理想的とは言えないケースでは、分割列のデータ分布はWHERE条件から完全に独立しており、分割列のインデックスを使用してデータ スキャンの範囲を縮小することはできません。
• クラスタ化インデックスが存在する場合、実行効率が高くなるように、主キー ( INT主キーと_tidb_rowidを含む) を分割列として使用することをお勧めします。
• 重複値が少ない列を選択します。

分割列を指定しないことも選択できます。次に、TiDB はデフォルトでhandleの最初の列を分割列として使用します。ただし、クラスター化インデックスの主キーの最初の列が、非トランザクション DML ステートメント ( ENUM 、 BIT 、 SET 、 JSON ) でサポートされていないデータ型である場合、TiDB はエラーを報告します。アプリケーションのニーズに応じて、適切な分割列を選択できます。

バッチサイズの設定方法

非トランザクション DML ステートメントでは、バッチ サイズが大きいほど、分割される SQL ステートメントが少なくなり、各 SQL ステートメントの実行が遅くなります。最適なバッチ サイズはワークロードによって異なります。 50000 から開始することをお勧めします。バッチ サイズが小さすぎても大きすぎても、実行効率が低下します。

各バッチの情報はメモリに保存されるため、バッチが多すぎるとメモリ消費量が大幅に増加する可能性があります。これは、バッチ サイズが小さすぎてはならない理由を説明しています。バッチ情報を格納するための非トランザクション ステートメントによって消費されるメモリの上限はtidb_mem_quota_queryと同じであり、この制限を超えたときにトリガーされるアクションは、構成項目tidb_mem_oom_actionによって決定されます。

制限

以下は、非トランザクション DML ステートメントに対する厳しい制限です。これらの制限が満たされていない場合、TiDB はエラーを報告します。

• 単一のテーブルでのみ操作できます。マルチテーブル結合は現在サポートされていません。
• DML ステートメントにORDER BYつまたはLIMITの句を含めることはできません。
• 分割列には索引を付ける必要があります。インデックスは、単一列のインデックスにすることも、結合インデックスの最初の列にすることもできます。
• autocommitモードで使用する必要があります。
• batch-dml が有効な場合は使用できません。
• tidb_snapshot が設定されている場合は使用できません。
• prepareステートメントでは使用できません。
• ENUM 、 BIT 、 SET 、 JSON型は分割列としてサポートされていません。
• 一時テーブルではサポートされていません。
• 共通テーブル式はサポートされていません。

バッチ実行失敗の制御

非トランザクション DML ステートメントは原子性を満たしません。成功するバッチもあれば、失敗するバッチもあります。システム変数tidb_nontransactional_ignore_errorは、非トランザクション DML ステートメントがエラーを処理する方法を制御します。

例外として、最初のバッチが失敗した場合、ステートメント自体が間違っている可能性が高くなります。この場合、非トランザクション ステートメント全体が直接エラーを返します。

使い方

非トランザクション DML ステートメントの動作原理は、SQL ステートメントの自動分割を TiDB に組み込むことです。非トランザクション DML ステートメントがない場合は、SQL ステートメントを手動で分割する必要があります。非トランザクション DML ステートメントの動作を理解するには、次のタスクを実行するユーザー スクリプトと考えてください。

非トランザクション DML BATCH ON $C$ LIMIT $N$ DELETE FROM ... WHERE $P$の場合、$C$ は分割に使用される列、$N$ はバッチ サイズ、$P$ はフィルター条件です。

1. 元のステートメントのフィルター条件 $P$ と指定された分割列 $C$ に従って、TiDB は $P$ を満たすすべての $C$ をクエリします。 TiDB はこれらの $C$ を $N$ に従ってグループ $B_1 \dots B_k$ に分類します。すべての $B_i$ のそれぞれについて、TiDB はその最初と最後の $C$ を $S_i$ と $E_i$ として保持します。このステップで実行されたクエリ ステートメントは、 DRY RUN QUERYで表示できます。
2. $B_i$ に含まれるデータは、$P_i$: $C$ BETWEEN $S_i$ AND $E_i$ を満たすサブセットです。 $P_i$ を使用して、各バッチで処理する必要があるデータの範囲を絞り込むことができます。
3. $B_i$ の場合、TiDB は上記の条件を元のステートメントのWHERE条件に埋め込み、WHERE ($P_i$) AND ($P$) を作成します。このステップの実行結果はDRY RUNで確認できます。
4. すべてのバッチについて、新しいステートメントを順番に実行します。各グループ化のエラーが収集および結合され、すべてのグループ化が完了した後、非トランザクション DML ステートメント全体の結果として返されます。

batch-dml との比較

batch-dml は、DML ステートメントの実行中にトランザクションを複数のトランザクション コミットに分割するためのメカニズムです。

ノート：

廃止された batch-dml を使用することはお勧めしません。 batch-dml 機能が適切に使用されていない場合、データ インデックスの不整合が発生するリスクがあります。

非トランザクション DML ステートメントは、まだすべてのバッチ dml 使用シナリオに取って代わるものではありません。主な違いは次のとおりです。

• パフォーマンス: 分割列が効率的である場合、非トランザクション DML ステートメントのパフォーマンスは、batch-dml のパフォーマンスに近くなります。分割列の効率が悪い場合、非トランザクション DML ステートメントのパフォーマンスは、batch-dml のパフォーマンスよりも大幅に低下します。

• 安定性: batch-dml は、不適切な使用により、データ インデックスの不整合が発生する傾向があります。非トランザクション DML ステートメントは、データ インデックスの不整合を引き起こしません。ただし、不適切に使用すると、非トランザクション DML ステートメントは元のステートメントと同等ではなくなり、アプリケーションで予期しない動作が発生する可能性があります。詳細は一般的な問題のセクションを参照してください。

一般的な問題

実際のバッチ サイズは、指定されたバッチ サイズと同じではありません

非トランザクション DML ステートメントの実行中に、最後のバッチで処理されるデータのサイズが、指定されたバッチ サイズよりも小さくなる場合があります。

分割列に重複した値が存在する場合、各バッチには、このバッチの分割列の最後の要素のすべての重複値が含まれます。したがって、このバッチの行数は、指定されたバッチ サイズよりも大きくなる可能性があります。

さらに、他の同時書き込みが発生すると、各バッチで処理される行数が、指定されたバッチ サイズと異なる場合があります。

Failed to restore the delete statement, probably because of unsupported type of the shard column実行中にエラーが発生する

分割列はENUM 、 BIT 、 SET 、 JSON型には対応していません。新しい分割列を指定してみてください。整数型または文字列型の列を使用することをお勧めします。

選択した分割列がこれらのサポートされていないタイプのいずれでもないときにエラーが発生した場合は、PingCAP テクニカル サポートに連絡してください。

非トランザクションDELETEには、通常のDELETEと同等ではない「例外的な」動作があります。

非トランザクション DML ステートメントは、この DML ステートメントの元の形式と同等ではありません。これには、次の理由が考えられます。

• 他の同時書き込みがあります。
• 非トランザクション DML ステートメントは、ステートメント自体が読み取る値を変更します。
• 各バッチで実行される SQL ステートメントは、 WHEREの条件が変更されるため、実行計画と式の計算順序が異なる可能性があります。したがって、実行結果は元のステートメントとは異なる場合があります。
• DML ステートメントに非決定論的操作が含まれています。

MySQL の互換性

非トランザクション ステートメントは TiDB 固有であり、MySQL と互換性がありません。

こちらもご覧ください

• BATCH構文
• tidb_nontransactional_ignore_error