PingKai Logo下载
搜索

删除数据

本文档介绍如何使用 DELETE SQL 语句在 TiDB 中删除数据。如果你需要定期删除过期数据，可以使用 time to live 功能。

开始之前

在阅读本文档之前，你需要准备以下内容：

SQL 语法

DELETE 语句通常的格式如下：

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

此示例仅展示了 DELETE 的简单用法。有关详细信息，请参见 DELETE 语法。

最佳实践

删除数据时应遵循以下一些最佳实践：

  • 始终在 DELETE 语句中指定 WHERE 子句。如果不指定，TiDB 将删除表中的 所有行。
  • 当你删除大量行（例如超过一万行）时，建议使用 bulk-delete，因为 TiDB 默认为限制单个事务的大小为 100 MB。
  • 如果要删除表中的所有数据，不要使用 DELETE 语句。应改用 TRUNCATE 语句。
  • 出于性能考虑，详见 性能注意事项。
  • 在需要删除大量数据的场景下，非事务性批量删除 可以显著提升性能，但会丧失删除的事务性，因此 不能 回滚。请确保操作正确。

示例

假设你在某个时间段内发现应用程序出现错误，需要删除该时间段内所有的 ratings 数据，例如，从 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 条，建议使用 Bulk-Delete 进行删除。

如果返回的记录数少于 10,000 条，可以使用以下示例进行删除。

在 SQL 中，示例如下：

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

rated_at 字段为 日期和时间类型 中的 DATETIME 类型。你可以假设它在 TiDB 中以字面值存储，与时区无关。另一方面，TIMESTAMP 类型存储时间戳，因此在不同的时区显示的时间字符串会不同。

Note

和 MySQL 一样，TIMESTAMP 数据类型受到 2038 年问题 的影响。如果你存储的值大于 2038，建议使用 DATETIME 类型。

性能注意事项

TiDB GC 机制

TiDB 在你运行 DELETE 语句后，并不会立即删除数据，而是将数据标记为准备删除状态，然后等待 TiDB GC（垃圾回收）清理过期数据。因此，DELETE 语句 不会 立即减少磁盘空间。

GC 默认每 10 分钟触发一次。每次 GC 会计算一个时间点，称为 safe_point。在此时间点之前的任何数据将不再使用，TiDB 可以安全地将其清理。

更多信息，请参见 GC 机制。

更新统计信息

TiDB 使用 统计信息 来决定索引选择。在删除大量数据后，索引可能会被错误选择。你可以使用 手动收集 来更新统计信息，为 SQL 性能优化提供更准确的统计数据。

批量删除

当你需要从表中删除多行数据时，可以选择 示例 中的 DELETE，并使用 WHERE 子句过滤需要删除的数据。

然而，如果你需要删除大量行（超过一万行），建议采用迭代方式删除，即每次删除一部分数据，直到删除完成。这是因为 TiDB 默认为限制单个事务的大小为 100 MB。你可以在程序或脚本中使用循环执行此类操作。

本节提供了一个编写脚本实现迭代删除的示例，演示如何结合 SELECT 和 DELETE 完成批量删除。

编写批量删除循环

你可以在应用或脚本的循环中写入 DELETE 语句，使用 WHERE 子句过滤数据，并用 LIMIT 限制单次删除的行数。

批量删除示例

假设你在某个时间段内发现应用程序出现错误，需要删除该时间段内所有的 rating 数据，例如，从 2022-04-15 00:00:00 到 2022-04-15 00:15:00，且在 15 分钟内写入了超过 1 万条记录。可以按如下方式操作。

在 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 {
        // 配置示例数据库连接。

        // 创建 MysqlDataSource 实例。
        MysqlDataSource mysqlDataSource = new MysqlDataSource();

        // 设置服务器名、端口、数据库名、用户名和密码。
        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 行。

非事务性批量删除

Note

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

需要帮助？

在 Discord 或 Slack 上向社区提问，或 提交支持工单。