データを削除

このドキュメントでは、消去 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分ごとに実行されます。各GCはsafe_pointと呼ばれる時点を計算します。この時点より前のデータは再利用されないため、TiDBは安全にクリーンアップできます。

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

統計情報を更新する

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

一括削除

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

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

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

一括削除ループを書く

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

一括削除の例

特定の期間内にアプリケーションエラーが発生したとします。この期間（例えば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 行を削除します。

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

注記：
TiDBはv6.1.0以降、非トランザクション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構文

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

ヘルプが必要ですか?

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