データを削除

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

始める前に

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

SQL構文

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

DELETE FROM {table} WHERE {filter}

パラメータ名	説明
`{table}`	テーブル名
`{filter}`	フィルターの一致条件

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

ベストプラクティス

データを削除する際に従うべきベストプラクティスを次に示します。

DELETEステートメントでは必ずWHERE句を指定します。5 句が指定WHEREれていない場合、TiDB はテーブル内のすべての行を削除します。

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 では、例は次のようになります。

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タイプはタイムスタンプを格納するため、異なるタイムゾーンに異なる時間文字列が表示されます。

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

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

一括削除ループを書く

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

一括削除の例

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

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";

ヘルプが必要ですか?

不和またはスラック、またはサポートチケットを送信するについてコミュニティに質問してください。

データを削除

始める前に

SQL構文

ベストプラクティス

例

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

TiDB GC メカニズム

統計情報を更新する

一括削除

一括削除ループを書く

一括削除の例

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

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

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

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

ヘルプが必要ですか?

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