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

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

このドキュメントでは、TiDBでの非トランザクションDMLステートメントの使用シナリオ、使用方法、および制限について説明します。さらに、実装の原則と一般的な問題についても説明します。

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

非トランザクションDMLステートメントにはINSERT 、およびUPDATEが含まれ、そのうちTiDBは現在DELETEのみをサポートしていDELETE 。詳細な構文については、 BATCHを参照してください。

ノート:

非トランザクションDMLステートメントは、ステートメントのアトミック性と分離を保証するものではなく、元のDMLステートメントと同等ではありません。

使用シナリオ

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

バッチデータ処理では、多くの場合、時間やデータがオンラインアプリケーションの操作と重複することはありません。並行操作が存在しない場合、分離( ACIDのI)は不要です。バルクデータ操作がべき等であるか、簡単に再試行できる場合も、アトミシティは不要です。アプリケーションがデータ分離も原子性も必要としない場合は、非トランザクションDMLステートメントの使用を検討できます。

非トランザクションDMLステートメントは、特定のシナリオで大規模なトランザクションのサイズ制限をバイパスするために使用されます。 1つのステートメントは、トランザクションを手動で分割する必要があるタスクを完了するために使用され、実行効率が高く、リソース消費が少なくなります。

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

前提条件

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

  • このステートメントはアトミック性を必要としません。これにより、実行結果で一部の行を変更し、一部の行を変更しないままにすることができます。
  • ステートメントがべき等であるか、エラーメッセージに従ってデータの一部を再試行する準備ができています。システム変数がtidb_redact_log = 1tidb_nontransactional_ignore_error = 1に設定されている場合、このステートメントはべき等である必要があります。そうしないと、ステートメントが部分的に失敗したときに、失敗した部分を正確に特定できません。
  • 操作対象のデータには、他の同時書き込みはありません。つまり、他のステートメントによって同時に更新されることはありません。そうしないと、削除の欠落や誤った削除などの予期しない結果が発生する可能性があります。
  • ステートメントは、ステートメント自体によって読み取られるデータを変更しません。そうしないと、次のバッチが前のバッチによって書き込まれたデータを読み取り、予期しない結果を簡単に引き起こします。
  • ステートメントは制限を満たしています。
  • このDMLステートメントによって読み書きされるテーブルに対して同時DDL操作を実行することはお勧めしません。
警告

tidb_redact_logtidb_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

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

非トランザクション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を使用できます。次に、TiDBは、現在実行されているバッチの後にすべてのバッチをキャンセルします。ログから実行結果を取得できます。

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

非トランザクション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を追加し、クエリを手動で実行し、分割ステートメントと実行プランを確認します。インデックス選択の効率に注意を払う必要があります。
  4. 非トランザクションDMLステートメントを実行します。
  5. エラーが報告された場合は、エラーメッセージまたはログから特定の失敗したデータ範囲を取得し、手動で再試行または処理してください。

パラメータの説明

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

分割列の選び方

非トランザクションDMLステートメントは、データバッチ処理の基礎として列を使用します。これは分割列です。実行効率を高めるには、インデックスを使用するための分割列が必要です。異なるインデックスと分割列によってもたらされる実行効率は、数十回異なる場合があります。分割列を選択するときは、次の提案を考慮してください。

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

分割列を指定しないこともできます。次に、TiDBはデフォルトでhandleの最初の列を分割列として使用します。ただし、クラスター化インデックスの主キーの最初の列が非トランザクションDMLステートメント( ENUM )でサポートされてBITないデータ型である場合、 SETはエラーを報告しJSON 。アプリケーションのニーズに応じて、適切な分割列を選択できます。

バッチサイズの設定方法

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

各バッチの情報はメモリに保存されるため、バッチが多すぎるとメモリ消費量が大幅に増加する可能性があります。これは、バッチサイズが小さすぎない理由を説明しています。バッチ情報を格納するための非トランザクションステートメントによって消費されるメモリの上限はtidb_mem_quota_queryと同じであり、この制限を超えたときにトリガーされるアクションは、構成項目tidb_mem_oom_actionによって決定されます。

制限

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

  • 1つのテーブルのみを操作できます。マルチテーブル結合は現在サポートされていません。
  • DMLステートメントにORDER BYつまたはLIMITの句を含めることはできません。
  • 分割列にはインデックスを付ける必要があります。インデックスは、単一列のインデックス、または結合インデックスの最初の列にすることができます。
  • autocommitモードで使用する必要があります。
  • batch-dmlが有効になっている場合は使用できません。
  • [ tidb_snapshot ](/ read-historical-data.md#operation flow)が設定されている場合は使用できません。
  • prepareステートメントでは使用できません。
  • ENUM BITSET JSONとしてサポートされていません。
  • 一時テーブルではサポートされていません。

バッチ実行の失敗を制御する

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

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

使い方

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

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

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

batch-dmlとの比較

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

ノート:

batch-dmlの使用はお勧めしません。 batch-dml機能が適切に使用されていない場合、データインデックスの不整合のリスクがあります。 batch-dmlは、TiDBの今後のリリースで非推奨になります。

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

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

も参照してください