重要

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

非トランザクション 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を追加し、クエリを手動で実行し、分割ステートメントと実行計画を確認します。次の点に注意する必要があります。
- Split ステートメントが、前のステートメントによって書き込まれた結果を読み取ることができるかどうか。これにより、異常が発生する可能性があります。
- インデックスの選択性。
- TiDB によって自動的に選択されたシャード列が変更されるかどうか。
非トランザクション DML ステートメントを実行します。
エラーが報告された場合は、エラーメッセージまたはログから特定の失敗したデータ範囲を取得し、再試行するか手動で処理します。

パラメータの説明

パラメータ	説明	デフォルト値	必須かどうか	推奨値
シャード列	バッチのシャーディングに使用される列 (上記の非トランザクション DML ステートメントの`id`列など) `BATCH ON id LIMIT 2 DELETE FROM t WHERE v < 6` 。	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をシャード列として使用します。ただし、クラスター化インデックスの主キーの最初の列が、非トランザクション 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モードで使用する必要があります。
バッチ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$ はフィルター条件です。

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

バッチdmlとの比較

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

ノート：
非推奨となったバッチ dml の使用は推奨されません。 Batch-DML 機能が適切に使用されていない場合、データインデックスの不整合が発生するリスクがあります。

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

パフォーマンス: シャード列が効率的である場合、非トランザクション DML ステートメントのパフォーマンスはバッチ DML のパフォーマンスに近くなります。シャード列の効率が低い場合、非トランザクション DML ステートメントのパフォーマンスはバッチ dml のパフォーマンスよりも大幅に低下します。
安定性: バッチ 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