删除数据

此页面将使用 DELETE SQL 语句,对 TiDB 中的数据进行删除。

在开始之前

在阅读本页面之前,你需要准备以下事项:

SQL 语法

在 SQL 中,DELETE 语句一般为以下形式:

DELETE FROM {table} WHERE {filter}
参数描述
{table}表名
{filter}过滤器匹配条件

此处仅展示 DELETE 的简单用法,详细文档可参考 TiDB 的 DELETE 语法

最佳实践

以下是删除行时需要遵循的一些最佳实践:

  • 始终在删除语句中指定 WHERE 子句。如果 DELETE 没有 WHERE 子句,TiDB 将删除这个表内的所有行
  • 需要删除大量行(数万或更多)的时候,使用批量删除,这是因为 TiDB 单个事务大小限制为 txn-total-size-limit(默认为 100MB)。
  • 如果你需要删除表内的所有数据,请勿使用 DELETE 语句,而应该使用 TRUNCATE 语句。
  • 查看 性能注意事项
  • 在需要大批量删除数据的场景下,非事务批量删除对性能的提升十分明显。但与之相对的,这将丢失删除的事务性,因此无法进行回滚,请务必正确进行操作选择。

例子

假设在开发中发现在特定时间段内,发生了业务错误,需要删除这期间内的所有 rating 的数据,例如,2022-04-15 00:00:002022-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";
  • 若返回数量大于 1 万条,请参考批量删除
  • 若返回数量小于 1 万条,可参考下面的示例进行删除:
  • SQL
  • Java
  • Golang

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

性能注意事项

TiDB GC 机制

DELETE 语句运行之后 TiDB 并非立刻删除数据,而是将这些数据标记为可删除。然后等待 TiDB GC (Garbage Collection) 来清理不再需要的旧数据。因此,你的 DELETE 语句并不会立即减少磁盘用量。

GC 在默认配置中,为 10 分钟触发一次,每次 GC 都会计算出一个名为 safe_point 的时间点,这个时间点前的数据,都不会再被使用到,因此,TiDB 可以安全的对数据进行清除。

GC 的具体实现方案和细节此处不再展开,请参考 GC 机制简介 了解更详细的 GC 说明。

更新统计信息

TiDB 使用统计信息来决定索引的选择,因此,在大批量的数据删除之后,很有可能会导致索引选择不准确的情况发生。你可以使用手动收集的办法,更新统计信息。用以给 TiDB 优化器以更准确的统计信息来提供 SQL 性能优化。

批量删除

需要删除表中多行的数据,可选择 DELETE 示例,并使用 WHERE 子句过滤需要删除的数据。

但如果你需要删除大量行(数万或更多)的时候,建议使用一个迭代,每次都只删除一部分数据,直到删除全部完成。这是因为 TiDB 单个事务大小限制为 txn-total-size-limit(默认为 100MB)。你可以在程序或脚本中使用循环来完成操作。

本页提供了编写脚本来处理循环删除的示例,该示例演示了应如何进行 SELECTDELETE 的组合,完成循环删除。

编写批量删除循环

在你的应用或脚本的循环中,编写一个 DELETE 语句,使用 WHERE 子句过滤需要删除的行,并使用 LIMIT 限制单次删除的数据条数。

批量删除例子

假设发现在特定时间段内,发生了业务错误,需要删除这期间内的所有 rating 的数据,例如,2022-04-15 00:00:002022-04-15 00:15:00 的数据。并且在 15 分钟内,有大于 1 万条数据被写入,此时请使用循环删除的方式进行删除:

  • Java
  • Golang

在 Java 中,批量删除程序类似于以下内容:

package com.pingcap.bulkDelete;

import com.mysql.cj.jdbc.MysqlDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
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("");

        Integer updateCount = -1;
        while (updateCount != 0) {
            updateCount = batchDelete(mysqlDataSource);
        }
    }

    public static Integer 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");

            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return -1;
    }
}

每次迭代中,DELETE 最多删除 1000 行时间段为 2022-04-15 00:00:002022-04-15 00:15:00 的数据。

在 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 最多删除 1000 行时间段为 2022-04-15 00:00:002022-04-15 00:15:00 的数据。

非事务批量删除

使用前提

在使用非事务批量删除前,请先仔细阅读非事务 DML 语句。非事务批量删除,本质是以牺牲事务的原子性、隔离性为代价,增强批量数据处理场景下的性能和易用性。

因此在使用过程中,需要极为小心,否则,因为操作的非事务特性,在误操作时会导致严重的后果(如数据丢失等)。

非事务批量删除 SQL 语法

非事务批量删除的 SQL 语法如下:

BATCH ON {shard_column} LIMIT {batch_size} {delete_statement};
参数描述
{shard_column}非事务批量删除的划分列
{batch_size}非事务批量删除的每批大小
{delete_statement}删除语句

此处仅展示非事务批量删除的简单用法,详细文档可参考 TiDB 的非事务 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";
下载 PDF文档反馈社区交流
文档内容是否有帮助?
产品
TiDB
学习
客户案例
PingCAP Education
TiDB in Action
© 2022 PingCAP. All Rights Reserved.