重要

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

非トランザクション 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 ステートメント`BATCH ON id LIMIT 2 DELETE FROM t WHERE v < 6`の`LIMIT 2`のように、DML 操作が分割される SQL ステートメントの数です。バッチが多いほど、バッチサイズは小さくなります。	なし	はい	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モードで使用する必要があります。
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$ はフィルター条件です。

元のステートメントのフィルター条件 $P$ と指定された分割列 $C$ に従って、TiDB は $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 ステートメント全体の結果として返されます。

batch-dml との比較

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

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

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

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

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

使用シナリオ

前提条件

使用例

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

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

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

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

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

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

ベストプラクティス

パラメータの説明

シャード列の選択方法

バッチサイズの設定方法

制限

バッチ実行失敗の制御

使い方

batch-dml との比較

一般的な問題

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

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

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

MySQL の互換性

こちらもご覧ください

このページは役に立ちましたか？

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

使用シナリオ

前提条件

使用例

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

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

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

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

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

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

ベストプラクティス

パラメータの説明

シャード列の選択方法

バッチサイズの設定方法

制限

バッチ実行失敗の制御

使い方

batch-dml との比較

一般的な問題

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

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

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

MySQL の互換性

こちらもご覧ください

このページは役に立ちましたか？

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

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

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