データ削除
このドキュメントでは、消去SQL ステートメントを使用して TiDB 内のデータを削除する方法について説明します。期限切れのデータを定期的に削除する必要がある場合は、生きる時間機能を使用してください。
始める前に
この文書を読む前に、以下のものを準備してください。
SQL構文
DELETEステートメントは、一般的に次の形式になります。
DELETE FROM {table} WHERE {filter}
この例はDELETEの簡単な使用例のみを示しています。詳細については、 DELETE構文参照してください。 。
ベストプラクティス
データを削除する際に従うべきベストプラクティスを以下に示します。
WHEREステートメントには、必ずDELETE句を指定してください。WHERE句が指定されていない場合、TiDB はテーブル内のすべての行を削除します。TiDB では 1 つのトランザクションのサイズが制限されているため (たとえば、1 万行以上)、大量の行を削除する場合は一括削除を使用します (段階トランザクションの合計サイズ制限、デフォルトでは 100 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における例は以下のとおりです。
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')
注記：
パフォーマンスに関する考慮事項
TiDB GCメカニズム
TiDB はDELETEステートメントを実行した直後にデータを削除するわけではありません。代わりに、削除準備完了としてデータをマークします。その後、TiDB GC (ガベージ コレクション) が古いデータをクリーンアップするまで待機します。したがって、 DELETEステートメントを実行しても、ディスク使用量はすぐには削減されません。
デフォルトでは、GC（ガベージコレクション）は10分ごとに実行されます。各GCでは、 safe_pointと呼ばれる時点が計算されます。この時点より前のデータは再利用されないため、TiDBは安全にクリーンアップできます。
詳細については、 GCメカニズムを参照してください。
統計情報を更新する
TiDBは統計情報を使用してインデックスの選択を決定します。大量のデータが削除された後、インデックスが正しく選択されないリスクが高くなります。この問題を解決するには、統計情報を更新してください。これにより、手動収集オプティマイザはSQLパフォーマンス最適化のためのより正確な統計情報を取得できます。
一括削除
テーブルから複数のデータ行を削除する必要がある場合は、 DELETE例選択し、 WHERE句を使用して削除する必要のあるデータをフィルタリングできます。
ただし、大量の行（1万行以上）を削除する必要がある場合は、反復的にデータを削除することをお勧めします。つまり、削除が完了するまで、各反復処理でデータの一部を削除していく方法です。これは、TiDBが単一トランザクションのサイズを制限しているためです（ txn-total-size-limit 、デフォルトでは100MB）。このような操作を実行するには、プログラムやスクリプトでループを使用できます。
このセクションでは、反復削除操作を処理するスクリプトの書き方の例を示し、 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};
前述の例は、非トランザクション一括削除ステートメントの単純な使用例のみを示しています。詳細については、非トランザクション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";
お困りですか？
- 不和or スラックコミュニティに質問してください。
- TiDB Cloudのサポートチケットを送信してください
- TiDB Self-Managedのサポートチケットを送信してください