データの削除

このドキュメントでは、 消去 SQL ステートメントを使用して TiDB 内のデータを削除する方法について説明します。

始める前に

このドキュメントを読む前に、次の準備が必要です。

SQL 構文

DELETEステートメントは通常、次の形式です。

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

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

ベストプラクティス

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

  • DELETE文には必ずWHERE節を指定してください。 WHERE句が指定されていない場合、TiDB はテーブル内のすべての行を削除します。
  • 多数の行 (たとえば、1 万行以上) を削除する場合は一括削除を使用します。これは、TiDB が 1 つのトランザクションのサイズを制限しているためです (デフォルトではtxn-合計サイズ制限 MB)。
  • TiDB はデフォルトで 1 つのトランザクションのサイズを 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

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) } }

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

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

ノート:

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

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

TiDB GC メカニズム

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

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

詳細については、 GC メカニズムを参照してください。

統計情報の更新

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

一括削除

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

ただし、多数の行 (1 万行以上) を削除する必要がある場合は、データを繰り返し削除することをお勧めします。つまり、削除が完了するまで繰り返しごとにデータの一部を削除します。これは、TiDB がtxn-total-size-limitつのトランザクションのサイズを制限しているためです (デフォルトでは 1、100 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

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(); } } }

各反復で、 DELETE2022-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 }

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

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