データを削除
このドキュメントでは、 消去 SQL ステートメントを使用して TiDB 内のデータを削除する方法について説明します。期限切れのデータを定期的に削除する必要がある場合は、 有効期間機能を使用してください。
始める前に
このドキュメントを読む前に、次のものを準備する必要があります。
SQL構文
DELETE
ステートメントは通常、次の形式になります。
DELETE FROM {table} WHERE {filter}
パラメータ名 | 説明 |
---|---|
{table} | テーブル名 |
{filter} | フィルターの一致条件 |
この例では、 DELETE
の単純な使用例のみを示しています。詳細については、 DELETE構文を参照してください。
ベストプラクティス
データを削除する際に従うべきベスト プラクティスを次に示します。
DELETE
ステートメントでは必ずWHERE
句を指定WHERE
ます。5 句が指定されていない場合、TiDB はテーブル内のすべての行を削除します。
- TiDB では単一トランザクションのサイズが制限されるため (デフォルトではトランザクションの合計サイズ制限 、100 MB)、多数の行 (たとえば、1 万行以上) を削除する場合は一括削除使用します。
- TiDB では、デフォルトで 1 つのトランザクションのサイズが 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
- Java
- Golang
- Python
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
タイプはタイムスタンプを格納するため、異なるタイムゾーンに異なる時間文字列が表示されます。
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)。プログラムまたはスクリプトでループを使用して、このような操作を実行できます。
ただし、多数の行 (1 万行以上) を削除する必要がある場合は、データを繰り返し削除することをお勧めします。つまり、削除が完了するまで、各反復でデータの一部を削除します。これは、TiDB が 1 つのトランザクションのサイズをデフォルトで 100 MB に制限しているためです。プログラムまたはスクリプトでループを使用して、このような操作を実行できます。
このセクションでは、反復削除操作を処理するスクリプトの記述例を示し、一括削除を完了するためにSELECT
とDELETE
を組み合わせる方法を示します。
一括削除ループを書く
アプリケーションまたはスクリプトのループ内にDELETE
ステートメントを記述し、 WHERE
句を使用してデータをフィルター処理し、 LIMIT
を使用して単一のステートメントで削除する行数を制限できます。
一括削除の例
特定の期間内にアプリケーション エラーが見つかったとします。この期間内の評価のデータをすべて削除する必要があり、たとえば2022-04-15 00:00:00
から2022-04-15 00:15:00
まで、15 分間に 10,000 件を超えるレコードが書き込まれているとします。次のように実行できます。
- Java
- Golang
- Python
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";
助けが必要?
TiDB コミュニティ 、またはサポートチケットを作成するについて質問します。
TiDB コミュニティ 、またはサポートチケットを作成するについて質問します。