Delete Data

This document describes how to use the DELETE SQL statement to delete the data in TiDB.

Before you start

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

• Build a TiDB Cluster in TiDB Cloud (Serverless Tier)
• Read Schema Design Overview, Create a Database, Create a Table, and Create Secondary Indexes
• Insert Data

SQL syntax

The DELETE statement is generally in the following form:

DELETE FROM {table} WHERE {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).

• 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.

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

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

// 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();
}

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)
    }
}

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.

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.

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

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();
        }
    }
}

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
}

Non-transactional bulk-delete

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:

BATCH ON {shard_column} LIMIT {batch_size} {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:

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