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

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

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

通常、メモリを消費するトランザクションは、トランザクション サイズの制限を回避するために複数の SQL ステートメントに分割する必要があります。非トランザクション DML ステートメントは、このプロセスを TiDB カーネルに統合して、同じ効果を実現します。SQL ステートメントを分割することで、非トランザクション DML ステートメントの効果を理解するのに役立ちます。1 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 ステートメントを使用するには、次の手順をお勧めします。

1. 適切な破片の列選択します。整数型または文字列型が推奨されます。

2. 非トランザクション DML ステートメントにDRY RUN QUERYを追加し、クエリを手動で実行して、DML ステートメントの影響を受けるデータ範囲がおおよそ正しいかどうかを確認します。

3. 非トランザクション DML ステートメントにDRY RUNを追加し、クエリを手動で実行して、分割ステートメントと実行プランを確認します。次の点に注意する必要があります。

   • 分割ステートメントが前のステートメントによって書き込まれた結果を読み取ることができるかどうか。これにより異常が発生する可能性があります。
   • インデックスの選択性。
   • TiDB によって自動的に選択されたシャード列が変更されるかどうか。

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 ステートメントの動作原理は、TiDB に SQL ステートメントの自動分割を組み込むことです。非トランザクション 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 ステートメントは元のステートメントと同等ではなくなり、アプリケーションで予期しない動作が発生する可能性があります。詳細については、 一般的な問題セクションを参照してください。

よくある問題

複数のテーブル結合ステートメントを実行すると、 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条件が変更されるため実行プランや式の計算順序が異なり、実行結果が元の文と異なる可能性があります。
• DML ステートメントには非決定論的な操作が含まれています。

MySQL 互換性

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

参照

• BATCH構文
• tidb_nontransactional_ignore_error