重要

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

データの削除

このドキュメントでは、消去 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
}