TiDB Self-Managed
Learning Center
お問い合わせ

データの削除

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

始める前に

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

SQL 構文

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

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

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

ベストプラクティス

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

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

ノート：

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)。プログラムまたはスクリプトでループを使用して、このような操作を実行できます。

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

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

アプリケーションまたはスクリプトのループに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();
        }
    }
}

各反復で、 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 行を削除します。