非トランザクション 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 操作を実行することはお勧めしません。
使用例
非トランザクション 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$
の場合、 は分割に使用される列、 はバッチ サイズ、 はフィルター条件です。
- 元のステートメントのフィルター条件 と指定された分割列 に従って、TiDB は を満たすすべての をクエリします。 TiDB はこれらの を に従ってグループ に分類します。すべての のそれぞれについて、TiDB はその最初と最後の を と として保持します。このステップで実行されたクエリ ステートメントは、
DRY RUN QUERY
で表示できます。 - に含まれるデータは、: BETWEEN AND を満たすサブセットです。 を使用して、各バッチで処理する必要があるデータの範囲を絞り込むことができます。
- の場合、TiDB は上記の条件を元のステートメントの
WHERE
条件に埋め込み、WHERE () AND () を作成します。このステップの実行結果は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 またはコミュニティから支持を得ます .
選択したシャード列がこれらのサポートされていないタイプのいずれでもないときにエラーが発生した場合は、 TiDB Cloudサポートに連絡する .
非トランザクションDELETE
は、通常のDELETE
と同等ではない「例外的な」動作があります。
非トランザクション DML ステートメントは、この DML ステートメントの元の形式と同等ではありません。これには、次の理由が考えられます。
- 他の同時書き込みがあります。
- 非トランザクション DML ステートメントは、ステートメント自体が読み取る値を変更します。
- 各バッチで実行される SQL ステートメントは、
WHERE
条件が変更されるため、実行計画と式の計算順序が異なる可能性があります。したがって、実行結果は元のステートメントとは異なる場合があります。 - DML ステートメントに非決定論的操作が含まれています。
MySQL の互換性
非トランザクション ステートメントは TiDB 固有であり、MySQL と互換性がありません。