データの削除

このドキュメントでは、 消去 SQL ステートメントを使用して TiDB 内のデータを削除する方法について説明します。期限切れのデータを定期的に削除する必要がある場合は、 有効期間機能を使用してください。

始める前に

このドキュメントを読む前に、以下を準備する必要があります。

SQL 構文

DELETEステートメントは通常、次の形式になります。

DELETE FROM {table} WHERE {filter}
パラメータ名説明
{table}テーブル名
{filter}フィルターのマッチング条件

この例では、 DELETEの単純な使用例のみを示します。詳細については、 DELETE 構文を参照してください。

ベストプラクティス

データを削除するときに従うべきいくつかのベスト プラクティスを次に示します。

  • DELETEステートメントには必ずWHERE句を指定します。 WHERE句が指定されていない場合、TiDB はテーブル内のすべての行を削除します。
  • 大量の行 (たとえば、1 万行以上) を削除する場合は、 一括削除を使用します。これは、TiDB が単一トランザクションのサイズを制限しているためです (デフォルトではtxn 合計サイズ制限 MB)。
  • TiDB は単一トランザクションのサイズをデフォルトで 100 MB に制限しているため、大量の行 (たとえば、1 万行以上) を削除する場合は一括削除使用します。
  • テーブル内のすべてのデータを削除する場合は、 DELETEステートメントを使用しないでください。代わりに、 TRUNCATEステートメントを使用してください。
  • パフォーマンスに関する考慮事項については、 パフォーマンスに関する考慮事項を参照してください。
  • 大量のデータを削除する必要があるシナリオでは、 非トランザクション一括削除指定するとパフォーマンスが大幅に向上します。ただし、これにより削除のトランザクションが失われるため、ロールバックできません。正しい操作を選択していることを確認してください。

特定の期間内にアプリケーション エラーが見つかり、この期間内の評価のすべてのデータ (たとえば2022-04-15 00:00:00から2022-04-15 00:15:00を削除する必要があるとします。この場合、 SELECTステートメントを使用して、削除されるレコードの数を確認できます。

SELECT COUNT(*) FROM `ratings` WHERE `rated_at` >= "2022-04-15 00:00:00" AND `rated_at` <= "2022-04-15 00:15:00";

10,000 を超えるレコードが返された場合は、 一括削除を使用して削除します。

返されるレコードが 10,000 未満の場合は、次の例を使用してレコードを削除します。

  • SQL
  • Java
  • Golang
  • Python

SQL の例は次のとおりです。

DELETE FROM `ratings` WHERE `rated_at` >= "2022-04-15 00:00:00" AND `rated_at` <= "2022-04-15 00:15:00";

Javaの例は次のとおりです。

// ds is an entity of com.mysql.cj.jdbc.MysqlDataSource try (Connection connection = ds.getConnection()) { String sql = "DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= ? AND `rated_at` <= ?"; PreparedStatement preparedStatement = connection.prepareStatement(sql); Calendar calendar = Calendar.getInstance(); calendar.set(Calendar.MILLISECOND, 0); calendar.set(2022, Calendar.APRIL, 15, 0, 0, 0); preparedStatement.setTimestamp(1, new Timestamp(calendar.getTimeInMillis())); calendar.set(2022, Calendar.APRIL, 15, 0, 15, 0); preparedStatement.setTimestamp(2, new Timestamp(calendar.getTimeInMillis())); preparedStatement.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }

Golangの例は次のとおりです。

package main import ( "database/sql" "fmt" "time" _ "github.com/go-sql-driver/mysql" ) func main() { db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:4000)/bookshop") if err != nil { panic(err) } defer db.Close() startTime := time.Date(2022, 04, 15, 0, 0, 0, 0, time.UTC) endTime := time.Date(2022, 04, 15, 0, 15, 0, 0, time.UTC) bulkUpdateSql := fmt.Sprintf("DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= ? AND `rated_at` <= ?") result, err := db.Exec(bulkUpdateSql, startTime, endTime) if err != nil { panic(err) } _, err = result.RowsAffected() if err != nil { panic(err) } }

Python の例は次のとおりです。

import MySQLdb import datetime import time connection = MySQLdb.connect( host="127.0.0.1", port=4000, user="root", password="", database="bookshop", autocommit=True ) with connection: with connection.cursor() as cursor: start_time = datetime.datetime(2022, 4, 15) end_time = datetime.datetime(2022, 4, 15, 0, 15) delete_sql = "DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= %s AND `rated_at` <= %s" affect_rows = cursor.execute(delete_sql, (start_time, end_time)) print(f'delete {affect_rows} data')

rated_atフィールドは日付と時刻のタイプDATETIMEタイプです。タイムゾーンに関係なく、TiDB にリテラル数量として保存されていると想定できます。一方、 TIMESTAMPタイプはタイムスタンプを格納するため、異なるタイムゾーンで異なる時刻文字列を表示します。

rated_atフィールドは日付と時刻のタイプDATETIMEタイプです。タイムゾーンに関係なく、TiDB にリテラル数量として保存されていると想定できます。一方、 TIMESTAMPタイプはタイムスタンプを格納するため、タイムゾーンが異なると異なる時刻文字列を表示します。

注記:

MySQL と同様に、 TIMESTAMPデータ型は2038年問題の影響を受けます。 2038 より大きい値を保存する場合は、 DATETIMEタイプを使用することをお勧めします。

パフォーマンスに関する考慮事項

TiDB GC メカニズム

TiDB は、 DELETEステートメントを実行した直後にはデータを削除しません。代わりに、データを削除準備完了としてマークします。次に、TiDB GC (ガベージ コレクション) が古いデータをクリーンアップするのを待ちます。したがって、 DELETEステートメントによってディスク使用量がすぐに削減されるわけではありません

GC はデフォルトで 10 分ごとにトリガーされます。各 GC は、 safe_pointと呼ばれる時点を計算します。この時点より前のデータは再使用されないため、TiDB は安全にクリーンアップできます。

詳細については、 GC機構を参照してください。

統計情報を更新する

TiDB は統計情報を使用してインデックスの選択を決定します。大量のデータが削除された後は、インデックスが正しく選択されない可能性が高くなります。 手動収集使用して統計を更新できます。これは、SQL パフォーマンスを最適化するためのより正確な統計情報を TiDB オプティマイザーに提供します。

一括削除

テーブルから複数のデータ行を削除する必要がある場合は、 DELETEを選択し、 WHERE句を使用して、削除する必要があるデータをフィルタリングできます。

ただし、大量の行 (1 万行を超える) を削除する必要がある場合は、データを反復的に削除することをお勧めします。つまり、削除が完了するまで反復ごとにデータの一部を削除します。これは、TiDB が単一トランザクションのサイズを制限しているためです (デフォルトではtxn-total-size-limit MB)。プログラムまたはスクリプトでループを使用して、このような操作を実行できます。

ただし、大量の行 (1 万行を超える) を削除する必要がある場合は、データを反復的に削除することをお勧めします。つまり、削除が完了するまで反復ごとにデータの一部を削除します。これは、TiDB がデフォルトで 1 つのトランザクションのサイズを 100 MB に制限しているためです。プログラムまたはスクリプトでループを使用して、このような操作を実行できます。

このセクションでは、反復削除操作を処理するスクリプトの作成例を示し、一括削除を完了するためにSELECTDELETEを組み合わせて実行する方法を示します。

一括削除ループを作成する

アプリケーションまたはスクリプトのループにDELETEステートメントを記述し、 WHERE句を使用してデータをフィルタリングし、 LIMIT句を使用して 1 つのステートメントで削除する行数を制限できます。

一括削除の例

特定の期間内にアプリケーション エラーが見つかったとします。この期間内に評価のデータをすべて削除する必要があります (たとえば、 2022-04-15 00:00:00から2022-04-15 00:15:00まで)。15 分間に 10,000 件を超えるレコードが書き込まれます。以下のように実行できます。

  • Java
  • Golang
  • Python

Javaでの一括削除の例は次のとおりです。

package com.pingcap.bulkDelete; import com.mysql.cj.jdbc.MysqlDataSource; import java.sql.*; import java.util.*; import java.util.concurrent.TimeUnit; public class BatchDeleteExample { public static void main(String[] args) throws InterruptedException { // Configure the example database connection. // Create a mysql data source instance. MysqlDataSource mysqlDataSource = new MysqlDataSource(); // Set server name, port, database name, username and password. mysqlDataSource.setServerName("localhost"); mysqlDataSource.setPortNumber(4000); mysqlDataSource.setDatabaseName("bookshop"); mysqlDataSource.setUser("root"); mysqlDataSource.setPassword(""); while (true) { batchDelete(mysqlDataSource); TimeUnit.SECONDS.sleep(1); } } public static void batchDelete (MysqlDataSource ds) { try (Connection connection = ds.getConnection()) { String sql = "DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= ? AND `rated_at` <= ? LIMIT 1000"; PreparedStatement preparedStatement = connection.prepareStatement(sql); Calendar calendar = Calendar.getInstance(); calendar.set(Calendar.MILLISECOND, 0); calendar.set(2022, Calendar.APRIL, 15, 0, 0, 0); preparedStatement.setTimestamp(1, new Timestamp(calendar.getTimeInMillis())); calendar.set(2022, Calendar.APRIL, 15, 0, 15, 0); preparedStatement.setTimestamp(2, new Timestamp(calendar.getTimeInMillis())); int count = preparedStatement.executeUpdate(); System.out.println("delete " + count + " data"); } catch (SQLException e) { e.printStackTrace(); } } }

各反復で、 DELETE 2022-04-15 00:00:00から2022-04-15 00:15:00までの最大 1000 行を削除します。

Golangでの一括削除の例は次のとおりです。

package main import ( "database/sql" "fmt" "time" _ "github.com/go-sql-driver/mysql" ) func main() { db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:4000)/bookshop") if err != nil { panic(err) } defer db.Close() affectedRows := int64(-1) startTime := time.Date(2022, 04, 15, 0, 0, 0, 0, time.UTC) endTime := time.Date(2022, 04, 15, 0, 15, 0, 0, time.UTC) for affectedRows != 0 { affectedRows, err = deleteBatch(db, startTime, endTime) if err != nil { panic(err) } } } // deleteBatch delete at most 1000 lines per batch func deleteBatch(db *sql.DB, startTime, endTime time.Time) (int64, error) { bulkUpdateSql := fmt.Sprintf("DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= ? AND `rated_at` <= ? LIMIT 1000") result, err := db.Exec(bulkUpdateSql, startTime, endTime) if err != nil { return -1, err } affectedRows, err := result.RowsAffected() if err != nil { return -1, err } fmt.Printf("delete %d data\n", affectedRows) return affectedRows, nil }

各反復で、 DELETE 2022-04-15 00:00:00から2022-04-15 00:15:00までの最大 1000 行を削除します。

Python での一括削除の例は次のとおりです。

import MySQLdb import datetime import time connection = MySQLdb.connect( host="127.0.0.1", port=4000, user="root", password="", database="bookshop", autocommit=True ) with connection: with connection.cursor() as cursor: start_time = datetime.datetime(2022, 4, 15) end_time = datetime.datetime(2022, 4, 15, 0, 15) affect_rows = -1 while affect_rows != 0: delete_sql = "DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= %s AND `rated_at` <= %s LIMIT 1000" affect_rows = cursor.execute(delete_sql, (start_time, end_time)) print(f'delete {affect_rows} data') time.sleep(1)

各反復で、 DELETE 2022-04-15 00:00:00から2022-04-15 00:15:00までの最大 1000 行を削除します。

非トランザクション一括削除

注記:

v6.1.0 以降、TiDB は非トランザクション DML ステートメントをサポートします。この機能は、TiDB v6.1.0 より前のバージョンでは使用できません。

非トランザクション一括削除の前提条件

非トランザクション一括削除を使用する前に、必ず最初に非トランザクション DML ステートメントのドキュメントをお読みください。非トランザクション一括削除により、バッチ データ処理シナリオのパフォーマンスと使いやすさが向上しますが、トランザクションの原子性と分離性が損なわれます。

したがって、誤った取り扱いによる重大な結果 (データ損失など) を避けるために、慎重に使用する必要があります。

非トランザクション一括削除の SQL 構文

非トランザクション一括削除ステートメントの SQL 構文は次のとおりです。

BATCH ON {shard_column} LIMIT {batch_size} {delete_statement};
パラメータ名説明
{shard_column}バッチを分割するために使用されるカラム。
{batch_size}各バッチのサイズを制御します。
{delete_statement}DELETEステートメント。

前述の例は、非トランザクション一括削除ステートメントの単純な使用例のみを示しています。詳細については、 非トランザクション DML ステートメントを参照してください。

非トランザクション一括削除の例

一括削除の例と同じシナリオで、次の SQL ステートメントは非トランザクション一括削除を実行する方法を示しています。

BATCH ON `rated_at` LIMIT 1000 DELETE FROM `ratings` WHERE `rated_at` >= "2022-04-15 00:00:00" AND `rated_at` <= "2022-04-15 00:15:00";

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

Playground
登録なしで TiDB の機能をワンストップでインタラクティブに体験できます。
製品
TiDB Dedicated
TiDB Serverless
TiDB
価格
PoC お問い合わせ
エコシステム
TiKV
TiFlash
OSS Insight
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.