Delete Data

This document describes how to use the DELETE SQL statement to delete the data in TiDB. If you need to periodically delete expired data, use the time to live feature.

Before you start

Before reading this document, you need to prepare the following:

SQL syntax

The DELETE statement is generally in the following form:

  1. DELETE FROM {table} WHERE {filter}
Parameter NameDescription
{table}Table name
{filter}Matching conditions of the filter

This example only shows a simple use case of DELETE. For detailed information, see DELETE syntax.

Best practices

The following are some best practices to follow when you delete data:

  • Always specify the WHERE clause in the DELETE statement. If the WHERE clause is not specified, TiDB will delete ALL ROWS in the table.

  • Use bulk-delete when you delete a large number of rows (for example, more than ten thousand), because TiDB limits the size of a single transaction (txn-total-size-limit, 100 MB by default).

  • Use bulk-delete when you delete a large number of rows (for example, more than ten thousand), because TiDB limits the size of a single transaction to 100 MB by default.

  • If you delete all the data in a table, do not use the DELETE statement. Instead, use the TRUNCATE statement.

  • For performance considerations, see Performance Considerations.
  • In scenarios where large batches of data need to be deleted, Non-Transactional bulk-delete can significantly improve performance. However, this will lose the transactional of the deletion and therefore CANNOT be rolled back. Make sure that you select the correct operation.

Example

Suppose you find an application error within a specific time period and you need to delete all the data for the ratings within this period, for example, from 2022-04-15 00:00:00 to 2022-04-15 00:15:00. In this case, you can use the SELECT statement to check the number of records to be deleted.

  1. SELECT COUNT(*) FROM `ratings` WHERE `rated_at` >= "2022-04-15 00:00:00" AND `rated_at` <= "2022-04-15 00:15:00";

If more than 10,000 records are returned, use Bulk-Delete to delete them.

If fewer than 10,000 records are returned, use the following example to delete them.

  • SQL
  • Java
  • Golang
  • Python

In SQL, the example is as follows:

  1. DELETE FROM `ratings` WHERE `rated_at` >= "2022-04-15 00:00:00" AND `rated_at` <= "2022-04-15 00:15:00";

In Java, the example is as follows:

  1. // ds is an entity of com.mysql.cj.jdbc.MysqlDataSource
  3. try (Connection connection = ds.getConnection()) {
  4.     String sql = "DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= ? AND `rated_at` <= ?";
  5.     PreparedStatement preparedStatement = connection.prepareStatement(sql);
  6.     Calendar calendar = Calendar.getInstance();
  7.     calendar.set(Calendar.MILLISECOND, 0);
  9.     calendar.set(2022, Calendar.APRIL, 15, 0, 0, 0);
  10.     preparedStatement.setTimestamp(1, new Timestamp(calendar.getTimeInMillis()));
  12.     calendar.set(2022, Calendar.APRIL, 15, 0, 15, 0);
  13.     preparedStatement.setTimestamp(2, new Timestamp(calendar.getTimeInMillis()));
  15.     preparedStatement.executeUpdate();
  16. } catch (SQLException e) {
  17.     e.printStackTrace();
  18. }

In Golang, the example is as follows:

  1. package main
  3. import (
  4.     "database/sql"
  5.     "fmt"
  6.     "time"
  8.     _ "github.com/go-sql-driver/mysql"
  9. )
  11. func main() {
  12.     db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:4000)/bookshop")
  13.     if err != nil {
  14.         panic(err)
  15.     }
  16.     defer db.Close()
  18.     startTime := time.Date(2022, 04, 15, 0, 0, 0, 0, time.UTC)
  19.     endTime := time.Date(2022, 04, 15, 0, 15, 0, 0, time.UTC)
  21.     bulkUpdateSql := fmt.Sprintf("DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= ? AND `rated_at` <= ?")
  22.     result, err := db.Exec(bulkUpdateSql, startTime, endTime)
  23.     if err != nil {
  24.         panic(err)
  25.     }
  26.     _, err = result.RowsAffected()
  27.     if err != nil {
  28.         panic(err)
  29.     }
  30. }

In Python, the example is as follows:

  1. import MySQLdb
  2. import datetime
  3. import time
  4. connection = MySQLdb.connect(
  5.     host="127.0.0.1",
  6.     port=4000,
  7.     user="root",
  8.     password="",
  9.     database="bookshop",
  10.     autocommit=True
  11. )
  12. with connection:
  13.     with connection.cursor() as cursor:
  14.         start_time = datetime.datetime(2022, 4, 15)
  15.         end_time = datetime.datetime(2022, 4, 15, 0, 15)
  16.         delete_sql = "DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= %s AND `rated_at` <= %s"
  17.         affect_rows = cursor.execute(delete_sql, (start_time, end_time))
  18.         print(f'delete {affect_rows} data')

The rated_at field is of the DATETIME type in Date and Time Types. You can assume that it is stored as a literal quantity in TiDB, independent of the time zone. On the other hand, the TIMESTAMP type stores a timestamp and thus displays a different time string in a different time zone.

The rated_at field is of the DATETIME type in Date and Time Types. You can assume that it is stored as a literal quantity in TiDB, independent of the time zone. On the other hand, the TIMESTAMP type stores a timestamp and thus displays a different time string in a different time zone.

Delete Data - 图1

Note

Like MySQL, the TIMESTAMP data type is affected by the year 2038 problem. It is recommended to use the DATETIME type if you store values larger than 2038.

Performance considerations

TiDB GC mechanism

TiDB does not delete the data immediately after you run the DELETE statement. Instead, it marks the data as ready for deletion. Then it waits for TiDB GC (Garbage Collection) to clean up the outdated data. Therefore, the DELETE statement DOES NOT immediately reduce disk usage.

GC is triggered once every 10 minutes by default. Each GC calculates a time point called safe_point. Any data earlier than this time point will not be used again, so TiDB can safely clean it up.

For more information, see GC mechanism.

Update statistical information

TiDB uses statistical information to determine index selection. There is a high risk that the index is not correctly selected after a large volume of data is deleted. You can use manual collection to update the statistics. It provides the TiDB optimizer with more accurate statistical information for SQL performance optimization.

Bulk-delete

When you need to delete multiple rows of data from a table, you can choose the DELETE example and use the WHERE clause to filter the data that needs to be deleted.

However, if you need to delete a large number of rows (more than ten thousand), it is recommended that you delete the data in an iterative way, that is, deleting a portion of the data at each iteration until the deletion is completed. This is because TiDB limits the size of a single transaction (txn-total-size-limit, 100 MB by default). You can use loops in your programs or scripts to perform such operations.

However, if you need to delete a large number of rows (more than ten thousand), it is recommended that you delete the data in an iterative way, that is, deleting a portion of the data at each iteration until the deletion is completed. This is because TiDB limits the size of a single transaction to 100 MB by default. You can use loops in your programs or scripts to perform such operations.

This section provides an example of writing a script to handle an iterative delete operation that demonstrates how you should do a combination of SELECT and DELETE to complete a bulk-delete.

Write a bulk-delete loop

You can write a DELETE statement in the loop of your application or script, use the WHERE clause to filter data, and use LIMIT to constrain the number of rows to be deleted in a single statement.

Bulk-delete example

Suppose you find an application error within a specific time period. You need to delete all the data for the rating within this period, for example, from 2022-04-15 00:00:00 to 2022-04-15 00:15:00, and more than 10,000 records are written in 15 minutes. You can perform as follows.

  • Java
  • Golang
  • Python

In Java, the bulk-delete example is as follows:

  1. package com.pingcap.bulkDelete;
  3. import com.mysql.cj.jdbc.MysqlDataSource;
  5. import java.sql.*;
  6. import java.util.*;
  7. import java.util.concurrent.TimeUnit;
  9. public class BatchDeleteExample
  10. {
  11.     public static void main(String[] args) throws InterruptedException {
  12.         // Configure the example database connection.
  14.         // Create a mysql data source instance.
  15.         MysqlDataSource mysqlDataSource = new MysqlDataSource();
  17.         // Set server name, port, database name, username and password.
  18.         mysqlDataSource.setServerName("localhost");
  19.         mysqlDataSource.setPortNumber(4000);
  20.         mysqlDataSource.setDatabaseName("bookshop");
  21.         mysqlDataSource.setUser("root");
  22.         mysqlDataSource.setPassword("");
  24.         while (true) {
  25.             batchDelete(mysqlDataSource);
  26.             TimeUnit.SECONDS.sleep(1);
  27.         }
  28.     }
  30.     public static void batchDelete (MysqlDataSource ds) {
  31.         try (Connection connection = ds.getConnection()) {
  32.             String sql = "DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= ? AND `rated_at` <= ? LIMIT 1000";
  33.             PreparedStatement preparedStatement = connection.prepareStatement(sql);
  34.             Calendar calendar = Calendar.getInstance();
  35.             calendar.set(Calendar.MILLISECOND, 0);
  37.             calendar.set(2022, Calendar.APRIL, 15, 0, 0, 0);
  38.             preparedStatement.setTimestamp(1, new Timestamp(calendar.getTimeInMillis()));
  40.             calendar.set(2022, Calendar.APRIL, 15, 0, 15, 0);
  41.             preparedStatement.setTimestamp(2, new Timestamp(calendar.getTimeInMillis()));
  43.             int count = preparedStatement.executeUpdate();
  44.             System.out.println("delete " + count + " data");
  45.         } catch (SQLException e) {
  46.             e.printStackTrace();
  47.         }
  48.     }
  49. }

In each iteration, DELETE deletes up to 1000 rows from 2022-04-15 00:00:00 to 2022-04-15 00:15:00.

In Golang, the bulk-delete example is as follows:

  1. package main
  3. import (
  4.     "database/sql"
  5.     "fmt"
  6.     "time"
  8.     _ "github.com/go-sql-driver/mysql"
  9. )
  11. func main() {
  12.     db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:4000)/bookshop")
  13.     if err != nil {
  14.         panic(err)
  15.     }
  16.     defer db.Close()
  18.     affectedRows := int64(-1)
  19.     startTime := time.Date(2022, 04, 15, 0, 0, 0, 0, time.UTC)
  20.     endTime := time.Date(2022, 04, 15, 0, 15, 0, 0, time.UTC)
  22.     for affectedRows != 0 {
  23.         affectedRows, err = deleteBatch(db, startTime, endTime)
  24.         if err != nil {
  25.             panic(err)
  26.         }
  27.     }
  28. }
  30. // deleteBatch delete at most 1000 lines per batch
  31. func deleteBatch(db *sql.DB, startTime, endTime time.Time) (int64, error) {
  32.     bulkUpdateSql := fmt.Sprintf("DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= ? AND `rated_at` <= ? LIMIT 1000")
  33.     result, err := db.Exec(bulkUpdateSql, startTime, endTime)
  34.     if err != nil {
  35.         return -1, err
  36.     }
  37.     affectedRows, err := result.RowsAffected()
  38.     if err != nil {
  39.         return -1, err
  40.     }
  42.     fmt.Printf("delete %d data\n", affectedRows)
  43.     return affectedRows, nil
  44. }

In each iteration, DELETE deletes up to 1000 rows from 2022-04-15 00:00:00 to 2022-04-15 00:15:00.

In Python, the bulk-delete example is as follows:

  1. import MySQLdb
  2. import datetime
  3. import time
  4. connection = MySQLdb.connect(
  5.     host="127.0.0.1",
  6.     port=4000,
  7.     user="root",
  8.     password="",
  9.     database="bookshop",
  10.     autocommit=True
  11. )
  12. with connection:
  13.     with connection.cursor() as cursor:
  14.         start_time = datetime.datetime(2022, 4, 15)
  15.         end_time = datetime.datetime(2022, 4, 15, 0, 15)
  16.         affect_rows = -1
  17.         while affect_rows != 0:
  18.             delete_sql = "DELETE FROM `bookshop`.`ratings` WHERE `rated_at` >= %s AND  `rated_at` <= %s LIMIT 1000"
  19.             affect_rows = cursor.execute(delete_sql, (start_time, end_time))
  20.             print(f'delete {affect_rows} data')
  21.             time.sleep(1)

In each iteration, DELETE deletes up to 1000 rows from 2022-04-15 00:00:00 to 2022-04-15 00:15:00.

Non-transactional bulk-delete

Delete Data - 图2

Note

Since v6.1.0, TiDB supports the non-transactional DML statements. This feature is not available for versions earlier than TiDB v6.1.0.

Prerequisites of non-transactional bulk-delete

Before using the non-transactional bulk-delete, make sure you have read the Non-Transactional DML statements documentation first. The non-transactional bulk-delete improves the performance and ease of use in batch data processing scenarios but compromises transactional atomicity and isolation.

Therefore, you should use it carefully to avoid serious consequences (such as data loss) due to mishandling.

SQL syntax for non-transactional bulk-delete

The SQL syntax for non-transactional bulk-delete statement is as follows:

  1. BATCH ON {shard_column} LIMIT {batch_size} {delete_statement};
Parameter NameDescription
{shard_column}The column used to divide batches.
{batch_size}Control the size of each batch.
{delete_statement}The DELETE statement.

The preceding example only shows a simple use case of a non-transactional bulk-delete statement. For detailed information, see Non-transactional DML Statements.

Example of non-transactional bulk-delete

In the same scenario as the Bulk-delete example, the following SQL statement shows how to perform a non-transactional bulk-delete:

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