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

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

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

通常、メモリを大量に消費するトランザクションは、トランザクションサイズ制限を回避するために複数のSQL文に分割する必要があります。非トランザクションDML文は、このプロセスをTiDBカーネルに統合することで、同様の効果を実現します。SQL文を分割することで、非トランザクションDML文の効果を理解するのに役立ちますDRY RUN構文を使用すると、分割された文をプレビューできます。

非トランザクション DML ステートメントには次のものがあります。

INSERT INTO ... SELECT
REPLACE INTO .. SELECT
UPDATE
DELETE

詳細な構文についてはBATCH参照してください。

注記：
非トランザクション DML ステートメントは、ステートメントの原子性と分離性を保証せず、元の DML ステートメントと同等ではありません。
DML ステートメントが非トランザクション DML ステートメントに書き換えられた後は、その動作が元のステートメントの動作と一致しているとは想定できません。
非トランザクション DML を使用する前に、分割されたステートメントが相互に影響を与えるかどうかを分析する必要があります。

使用シナリオ

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

バッチデータ処理では、オンラインアプリケーションの処理と時間やデータが重複することがほとんどありません。同時実行操作が存在しない場合は、分離性（ ACIDのI）は不要です。また、バルクデータ処理がべき等性を持つか、簡単に再試行できる場合は、原子性も不要です。アプリケーションでデータの分離性も原子性も必要ない場合は、非トランザクションDML文の使用を検討してください。

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

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

前提条件

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

このステートメントはアトミック性を必要としないため、実行結果で一部の行を変更し、一部の行を変更しないままにすることができます。
この文はべき等であるか、エラーメッセージに従ってデータの一部に対して再試行する準備ができています。システム変数がtidb_redact_log = 1とtidb_nontransactional_ignore_error = 1に設定されている場合、この文はべき等である必要があります。そうでない場合、文が部分的に失敗した場合、失敗した部分を正確に特定できません。
操作対象のデータには他の同時書き込みが存在しない、つまり他のステートメントによって同時に更新されない状態です。そうでない場合、書き込みの欠落、誤った書き込み、同じ行の複数回の変更など、予期しない結果が発生する可能性があります。
このステートメントは、自身で読み取るデータを変更しません。変更しない場合、後続のバッチは前のバッチで書き込まれたデータを読み取ってしまい、予期しない結果が発生しやすくなります。
- 非トランザクションINSERT INTO ... SELECTステートメント内で同じテーブルから選択して変更する場合は、シャード列を変更しないでください。そうしないと、複数のバッチが同じ行を読み取り、データを複数回挿入する可能性があります。
  - BATCH ON test.t.id LIMIT 10000 INSERT INTO t SELECT id+1, value FROM t;使用はお勧めしません。
  - BATCH ON test.t.id LIMIT 10000 INSERT INTO t SELECT id, value FROM t;使用することをお勧めします。
  - シャード列id AUTO_INCREMENT属性がある場合は、 BATCH ON test.t.id LIMIT 10000 INSERT INTO t(value) SELECT value FROM t;使用することをお勧めします。
- 非トランザクションUPDATE 、 INSERT ... ON DUPLICATE KEY UPDATE 、またはREPLACE INTOステートメントでシャード列を更新しないでください。
  - 例えば、非トランザクションのUPDATE文の場合、分割されたSQL文は順番に実行されます。前のバッチの変更は、前のバッチがコミットされた後に次のバッチに読み込まれるため、同じデータ行が複数回変更されることになります。
  - これらのステートメントはBATCH ON test.t.id LIMIT 10000 UPDATE t SET test.t.id = test.t.id-1;サポートしていません。
  - BATCH ON test.t.id LIMIT 1 INSERT INTO t SELECT id+1, value FROM t ON DUPLICATE KEY UPDATE id = id + 1;使用はお勧めしません。
- シャード列は結合キーとして使用しないでください。例えば、次の例ではシャード列test.t.id結合キーとして使用しているため、非トランザクションのUPDATE文が同じ行を複数回変更することになります。
```
CREATE TABLE t(id int, v int, key(id));
CREATE TABLE t2(id int, v int, key(id));
INSERT INTO t VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t2 VALUES (1, 1), (2, 2), (4, 4);
BATCH ON test.t.id LIMIT 1 UPDATE t JOIN t2 ON t.id = t2.id SET t2.id = t2.id+1;
SELECT * FROM t2; -- (4, 1) (4, 2) (4, 4)
```
この記述は制限満たしています。
この DML ステートメントによって読み取りまたは書き込みが行われるテーブルに対して同時 DDL 操作を実行することは推奨されません。

警告

tidb_redact_logとtidb_nontransactional_ignore_error同時に有効にすると、各バッチの完全なエラー情報を取得できない可能性があり、失敗したバッチのみを再試行できなくなります。したがって、両方のシステム変数が有効になっている場合、非トランザクションDML文はべき等である必要があります。

使用例

非トランザクション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

次の例では、複数のテーブルを結合する方法を説明します。まず、テーブルt2を作成し、データを挿入します。

CREATE TABLE t2(id int, v int, key(id));
INSERT INTO t2 VALUES (1,1), (3,3), (5,5);

次に、テーブルtとt2結合してテーブルt2のデータを更新します。完全なデータベース名、テーブル名、列名（ test.t.id ）に加えて、シャード列も指定する必要があることに注意してください。

BATCH ON test.t._tidb_rowid LIMIT 1 UPDATE t JOIN t2 ON t.id = t2.id SET t2.id = t2.id+1;

結果を照会します。

SELECT * FROM t2;

+----+---+
| id | v |
+----+---+
| 1  | 1 |
| 3  | 3 |
| 6  | 5 |
+----+---+

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

非トランザクション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 ステートメントを使用するには、次の手順をお勧めします。

適切な破片の列選択します。整数型または文字列型が推奨されます。
非トランザクション DML 文にDRY RUN QUERY追加し、クエリを手動で実行して、DML 文の影響を受けるデータ範囲がおおよそ正しいかどうかを確認します。
非トランザクションDML文にDRY RUN加算し、クエリを手動で実行して、分割文と実行プランを確認します。以下の点に注意する必要があります。
- 分割ステートメントが、前のステートメントによって書き込まれた結果を読み取ることができるかどうか。これにより、異常が発生する可能性があります。
- インデックスの選択性。
- TiDB によって自動的に選択されたシャード列が変更されるかどうか。
非トランザクション DML ステートメントを実行します。
エラーが報告された場合は、エラーメッセージまたはログから特定の失敗したデータ範囲を取得し、再試行するか手動で処理します。

パラメータの説明

パラメータ	説明	デフォルト値	必須かどうか	推奨値
シャード列	上記の非トランザクション DML ステートメント`BATCH ON id LIMIT 2 DELETE FROM t WHERE v < 6`の`id`列など、バッチをシャーディングするために使用される列。	TiDB はシャード列を自動的に選択しようとします (推奨されません)。	いいえ	最も効率的に`WHERE`条件を満たす列を選択します。
バッチサイズ	各バッチのサイズを制御するために使用されます。バッチ数は、DML操作を分割するSQL文の数です。例えば、上記の非トランザクションDML文`BATCH ON id LIMIT 2 DELETE FROM t WHERE v < 6`では`LIMIT 2`です。バッチ数が多いほど、バッチサイズは小さくなります。	該当なし	はい	1000～1000000。バッチが小さすぎたり大きすぎたりすると、パフォーマンスが低下します。

シャード列の選択方法

非トランザクションDML文は、データバッチ処理のベースとして列（シャード列）を使用します。実行効率を高めるには、シャード列でインデックスを使用する必要があります。異なるインデックスとシャード列によって、実行効率は数十倍も異なる可能性があります。シャード列を選択する際には、以下の点を考慮してください。

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

シャード列を指定しないことも可能です。その場合、TiDBはデフォルトでhandle列目をシャード列として使用します。ただし、クラスター化インデックスの主キーの1列目がBIT ENUM JSON場合、TiDBはエラーを報告します。アプリケーションのニーズに応じて適切なシャード列SET選択できます。

バッチサイズの設定方法

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

各バッチの情報はメモリに保存されるため、バッチ数が多すぎるとメモリ消費量が大幅に増加する可能性があります。そのため、バッチサイズは小さすぎることはできません。非トランザクションステートメントがバッチ情報を格納するために消費するメモリの上限はtidb_mem_quota_queryであり、この上限を超えた場合に実行されるアクションは設定項目tidb_mem_oom_actionによって決定されます。

制限

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

DML ステートメントにはORDER BYまたはLIMIT句を含めることはできません。
サブクエリまたはセット演算はサポートされていません。
シャード列にはインデックスが必要です。インデックスは単一列インデックス、または結合インデックスの最初の列にすることができます。
autocommitモードで使用する必要があります。
バッチ dml が有効な場合は使用できません。
tidb_snapshot設定されている場合は使用できません。
prepareステートメントでは使用できません。
ENUM 、 BIT 、 SET 、 JSONタイプはシャード列としてサポートされていません。
一時テーブルにはサポートされていません。
共通テーブル式はサポートされていません。

制御バッチ実行の失敗

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

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

仕組み

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

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

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

バッチDMLとの比較

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

注記：
非推奨となったbatch-dmlの使用は推奨されません。batch-dml機能を適切に使用しないと、データインデックスの不整合が発生するリスクがあります。

非トランザクションDML文は、まだすべてのバッチDML使用シナリオの代替となるわけではありません。主な違いは次のとおりです。

パフォーマンス：破片の列が効率的な場合、非トランザクションDMLステートメントのパフォーマンスはバッチDMLのパフォーマンスに近づきます。シャード列の効率が低い場合、非トランザクションDMLステートメントのパフォーマンスはバッチDMLよりも大幅に低下します。
安定性：batch-dmlは、不適切な使用によりデータインデックスの不整合が発生しやすい傾向があります。非トランザクションDML文はデータインデックスの不整合を引き起こしません。ただし、不適切に使用された場合、非トランザクションDML文は元の文と同等ではなくなり、アプリケーションで予期しない動作が発生する可能性があります。詳細はよくある問題のセクション参照してください。

よくある問題

複数のテーブル結合ステートメントを実行すると`Unknown column xxx in 'where clause'`エラーが発生します。

このエラーは、クエリ内で連結されたWHERE節が、破片の列定義されているテーブル以外のテーブルに関係する場合に発生します。例えば、次のSQL文では、シャード列はt2.idで、テーブルt2に定義されていますが、 WHERE節はテーブルt2とt3に関係しています。

BATCH ON test.t2.id LIMIT 1 
INSERT INTO t 
SELECT t2.id, t2.v, t3.id FROM t2, t3 WHERE t2.id = t3.id

(1054, "Unknown column 't3.id' in 'where clause'")

エラーが発生した場合は、 DRY RUN QUERYを使用して確認用のクエリステートメントを出力できます。例:

BATCH ON test.t2.id LIMIT 1 
DRY RUN QUERY INSERT INTO t 
SELECT t2.id, t2.v, t3.id FROM t2, t3 WHERE t2.id = t3.id

このエラーを回避するには、 WHERE句にある他のテーブルに関連する条件を、 JOIN番目の句にあるON条件に移動することができます。例：

BATCH ON test.t2.id LIMIT 1 
INSERT INTO t 
SELECT t2.id, t2.v, t3.id FROM t2 JOIN t3 ON t2.id = t3.id

+----------------+---------------+
| number of jobs | job status    |
+----------------+---------------+
| 0              | all succeeded |
+----------------+---------------+

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

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

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

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

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

シャード列はENUM 、 BIT 、 SET 、 JSON型をサポートしていません。新しいシャード列を指定してください。整数型または文字列型の列を使用することをお勧めします。

選択したシャード列がこれらのサポートされていないタイプのいずれでもないときにエラーが発生する場合は、 TiDB Cloudサポートにお問い合わせください。

非トランザクション`DELETE`は、通常の`DELETE`とは異なる「例外的な」動作があります。

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

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

MySQLの互換性

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