10
6
5
0
专栏/.../

绕过 MVCC 影响的 TiDB Delete 数据方法

 WalterWj  发表于  2024-03-19
原创7.x 实践管理与运维

背景

因为 Delete 语法会造成 MVCC，因此 delete limit 这类方法会越删除越慢。因此需要一些方法来绕过其影响。

新版本推荐：batch DML 语法和 TTL 功能来清理数据

删除方法解析

简单来说，比如 sbtest2 表结构为：

CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `insert_time` date DEFAULT NULL,
  PRIMARY KEY (`id`,`k`) /*T![clustered_index] CLUSTERED */,
  KEY `k_2` (`k`),
  KEY `ind1` (`insert_time`),
  KEY `idx_tmp` (`c`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=510022

如果我根据 insert_time 字段进行删除 10 亿数据。如果我单纯使用 delete from sbtest2 where insert_time < xxx limit 1000; 来循环删除，会发现删除越来越慢的现象。

这个时候，我们可以使用找入口和出口的方式来进行删除：

  1. 找到需要删除数据的入口：
(root@127.0.0.1) [test]>select count(1) from sbtest2;
+----------+
| count(1) |
+----------+
|   418295 |
+----------+
1 row in set (0.00 sec)

(root@127.0.0.1) [test]>select count(1) from sbtest2 where insert_time <='2023-06-07';
+----------+
| count(1) |
+----------+
|   157590 |
+----------+
1 row in set (0.13 sec)

# 获取入口：
(root@127.0.0.1) [test]>select id from sbtest2 where insert_time <='2023-06-07' order by id limit 1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)

(root@127.0.0.1) [test]>explain select id from sbtest2 where insert_time <='2023-06-07' order by id limit 1;
+--------------------------------+---------+-----------+---------------+----------------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                            |
+--------------------------------+---------+-----------+---------------+----------------------------------------------------------+
| Projection_7                   | 1.00    | root      |               | test.sbtest2.id                                          |
| └─Limit_11                     | 1.00    | root      |               | offset:0, count:1                                        |
|   └─TableReader_19             | 1.00    | root      |               | data:Limit_18                                            |
|     └─Limit_18                 | 1.00    | cop[tikv] |               | offset:0, count:1                                        |
|       └─Selection_17           | 1.00    | cop[tikv] |               | le(test.sbtest2.insert_time, 2023-06-07 00:00:00.000000) |
|         └─TableFullScan_16     | 2.68    | cop[tikv] | table:sbtest2 | keep order:true                                          |
+--------------------------------+---------+-----------+---------------+----------------------------------------------------------+
6 rows in set (0.00 sec)

第一步可能需要耗时较久，因为要先找到入口，需要做一次全扫，当然有 limit 下推，因此没有那么慢。

  1. 基于入口，找到一个批次的出口
(root@127.0.0.1) [test]>select id from(select id from sbtest2 where insert_time <='2023-06-07' and id>1 order by id limit 1000) as a order by a.id desc limit 1;
+------+
| id   |
+------+
| 3006 |
+------+
1 row in set (0.02 sec)

(root@127.0.0.1) [test]>explain select id from(select id from sbtest2 where insert_time <='2023-06-07' and id>1 order by id limit 1000) as a order by a.id desc limit 1;
+-----------------------------------+---------+-----------+---------------+----------------------------------------------------------+
| id                                | estRows | task      | access object | operator info                                            |
+-----------------------------------+---------+-----------+---------------+----------------------------------------------------------+
| Projection_11                     | 1.00    | root      |               | test.sbtest2.id                                          |
| └─TopN_14                         | 1.00    | root      |               | test.sbtest2.id:desc, offset:0, count:1                  |
|   └─Limit_23                      | 1000.00 | root      |               | offset:0, count:1000                                     |
|     └─TableReader_41              | 1000.00 | root      |               | data:Limit_40                                            |
|       └─Limit_40                  | 1000.00 | cop[tikv] |               | offset:0, count:1000                                     |
|         └─Selection_39            | 1000.00 | cop[tikv] |               | le(test.sbtest2.insert_time, 2023-06-07 00:00:00.000000) |
|           └─TableRangeScan_38     | 2677.82 | cop[tikv] | table:sbtest2 | range:(1,+inf], keep order:true                          |
+-----------------------------------+---------+-----------+---------------+----------------------------------------------------------+
7 rows in set (0.00 sec)

查询 SQL 中：

  • id>1：id 为表的行号，1 为找的入口值，这里是 1.
  • Limit 1000：代表一个批次是 1000

这样我们就找到 id 为 1-3006 范围的时候，且符合 insert_time 字段的条件，为 1000 行。

这里看执行计划，就知道，SQL 是 TableRangeScan 算法，本身还有 limit 下推优化，这步不会受到 MVCC 的影响。

  1. 根据主键来进行数据删除
(root@127.0.0.1) [test]>select count(1) from sbtest2 where insert_time <='2023-06-07' and id >= 1 and id < 3006 order by id;
+----------+
| count(1) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)

(root@127.0.0.1) [test]>explain delete from sbtest2 where insert_time <='2023-06-07' and id >= 1 and id < 3006 order by id;
+-----------------------------+---------+-----------+---------------+----------------------------------------------------------+
| id                          | estRows | task      | access object | operator info                                            |
+-----------------------------+---------+-----------+---------------+----------------------------------------------------------+
| Delete_5                    | N/A     | root      |               | N/A                                                      |
| └─TableReader_18            | 1017.67 | root      |               | data:Selection_17                                        |
|   └─Selection_17            | 1017.67 | cop[tikv] |               | le(test.sbtest2.insert_time, 2023-06-07 00:00:00.000000) |
|     └─TableRangeScan_16     | 2704.02 | cop[tikv] | table:sbtest2 | range:[1,3006), keep order:true                          |
+-----------------------------+---------+-----------+---------------+----------------------------------------------------------+
4 rows in set (0.01 sec)

可以看到 Delete 的执行计划，是按照 TableRangeScan 算子来进行数据扫描，扫的范围为 range:[1,3006)，排序也已经优化keep order:true

因此避免了 MVCC 影响，导致 Delete 越删越慢问题。

后续只要将本次的 id 最大边界值，作为下个循环的入口，逻辑一样的方法，来进行循环删除即可。

当然这个方法也有一定局限性，比如 clustered 的表，主键第一列过滤性较差。（这种情况比较少见，而且不推荐如此设计）

脚本化

#!/bin/bash

# 获取时间戳(毫秒)
function getTimestamp() {
    datetime=`date "+%Y-%m-%d %H:%M:%S"` # 获取当前时间, 例如: 2015-03-11 12:33:41
    seconds=`date -d "$datetime" +%s` # 把当前时间转为时间戳(秒)
    milliseconds=$((seconds*1000+10#`date "+%N"`/1000000)) # 把current转为时间戳(毫秒)
    echo "${milliseconds}"
}

# 获取当前小时
current_hour=$(date "+%H")
current_hourMin=$(date "+%H%M")
# MySQL连接信息
DB_HOST="172.16.201.18"
DB_PORT="4100"
DB_USER="root"
DB_PASSWORD="root"
DB_NAME="test"
TB_NAME="sbtest1"
PRI_NAME="id"
whereList="where insert_time<='2023-10-10'"
log_file="/root/scripts/delete${current_hourMin}.log"
deleteLimit=1000
scriptStartTime=11
scriptEndTime=13

# 判断当前时间是否在晚上 22:00 到凌晨 5:00 之间
if [ "$current_hour" -ge ${scriptStartTime} ] && [ "$current_hour" -lt ${scriptEndTime} ]; then
    # 查询数据总行数
    select_sql="SELECT COUNT(*) FROM \`${DB_NAME}\`.\`${TB_NAME}\` ${whereList};"
    counter=$(mysql -u"$DB_USER" -P"$DB_PORT" -h"$DB_HOST" -p"$DB_PASSWORD" -e "$select_sql" -N)
    
    echo "delete data start, $(date "+%Y-%m-%d %H:%M:%S")" >> "$log_file"
    # 获取最小的`${PRI_NAME}`做为入口
    firstPriSql="SELECT \`${PRI_NAME}\` FROM \`${DB_NAME}\`.\`${TB_NAME}\` ${whereList} ORDER BY \`${PRI_NAME}\` LIMIT 1"
    echo $firstPriSql &>> "$log_file"
    first_pri=$(mysql -u"$DB_USER" -P"$DB_PORT" -h"$DB_HOST" -p"$DB_PASSWORD" -D"$DB_NAME" -N -s -e "SELECT \`${PRI_NAME}\` FROM \`${DB_NAME}\`.\`${TB_NAME}\` ${whereList} ORDER BY \`${PRI_NAME}\` LIMIT 1") &>> "$log_file"
    echo "需要删除数据量：${counter}" &>> "$log_file"
    # 脚本运行前给一个初始值
    second_pri=${first_pri}
    while [ "$counter" -gt 0 ]; do
        echo "批次开始时间: "`date +"%Y-%m-%d %H:%M:%S"` &>> "$log_file"
        startTime=$(getTimestamp)
        # 检查当前时间是否在指定范围内
        current_hour=$(date "+%H")
        if [ "$current_hour" -ge ${scriptStartTime} ] && [ "$current_hour" -lt ${scriptEndTime} ]; then
            if [ -n "$first_pri" ] && [ -n "$second_pri" ]; then
                secondPriSQL="SELECT \`${PRI_NAME}\` FROM (SELECT \`${PRI_NAME}\` FROM \`${DB_NAME}\`.\`${TB_NAME}\` ${whereList} AND \`${PRI_NAME}\` >= $first_pri ORDER BY \`${PRI_NAME}\` limit ${deleteLimit}) AS a ORDER BY a.\`${PRI_NAME}\` DESC LIMIT 1"
                second_pri=$(mysql -u"$DB_USER" -P"$DB_PORT" -h"$DB_HOST" -p"$DB_PASSWORD" -D"$DB_NAME" -N -s -e "$secondPriSQL") &>> "$log_file"
                echo "获取 end key: ${second_pri}" &>> "$log_file"
            fi
            # 删除数据
            if [ -n "$first_pri" ] && [ -n "$second_pri" ]; then
                mysql -u"$DB_USER" -P"$DB_PORT" -h"$DB_HOST" -p"$DB_PASSWORD" -D"$DB_NAME" -e "DELETE FROM \`${DB_NAME}\`.\`${TB_NAME}\` ${whereList} AND \`${PRI_NAME}\` >= $first_pri AND \`${PRI_NAME}\` < $second_pri ORDER by \`${PRI_NAME}\`;" &>> "$log_file"
                # 更新counter
                counter=$((counter - ${deleteLimit}))
                echo "删除 ${deleteLimit} 行成功,数据剩余 ${counter}"  &>> "$log_file"
            elif [ -n "$first_pri" ]; then
                mysql -u"$DB_USER" -P"$DB_PORT" -h"$DB_HOST" -p"$DB_PASSWORD" -D"$DB_NAME" -e "DELETE FROM \`${DB_NAME}\`.\`${TB_NAME}\` ${whereList} AND \`${PRI_NAME}\` >= $first_pri ORDER by \`${PRI_NAME}\`;" &>> "$log_file"
                counter=$((counter - 1))
                echo "删除结束"  &>> "$log_file"
            else
                break
            fi
            # 每次循环，将入口设置为本次循环的出口
            first_pri=${second_pri}
        else
            echo "Current time is not within the specified range (22:00 to 5:00). Exiting script." &>> "$log_file"
            exit 1
        fi
        endTime=$(getTimestamp)
        echo "cost time is $((${endTime}-${startTime})) ms" &>> "$log_file"
        # 循环 sleep，推荐去掉
        sleep 0.01
    done

    echo "delete data end, $(date "+%Y-%m-%d %H:%M:%S")" &>> "$log_file"
else
    echo "Current time is not within the specified range (${scriptStartTime}:00 to ${scriptEndTime}:00)." &>> "$log_file"
fi

脚本使用说明

以下是该Shell脚本的使用说明：

  1. 脚本功能：
  1. 该脚本用于在指定时间范围内执行定时删除 TiDB 数据库中指定表的数据。具体操作包括查询数据总行数、获取最小的主键值作为入口，然后按照指定的删除限制逐批次删除数据。

2. 使用前准备：

  • 确保脚本文件有执行权限：chmod +x script_name.sh
  • 请根据实际情况修改MySQL连接信息（DB_HOST、DB_PORT、DB_USER、DB_PASSWORD、DB_NAME等），表信息（TB_NAME、PRI_NAME等），以及删除条件（whereList）。
  • 设置脚本执行时间范围（scriptStartTime和scriptEndTime）。

3. 脚本使用方法：

执行脚本：./script_name.sh

4. 日志输出：

执行过程中的日志将输出到指定的日志文件中，文件路径为 "/root/scripts/delete${current_hourMin}.log"。

5. 注意事项：

  • 在执行脚本之前，请确保已经备份好可能会被删除的数据，以免造成不可挽回的损失。
  • 请谨慎修改脚本中的关键信息，确保数据库连接信息和删除条件正确。
  • 在指定的时间范围内运行脚本，以免影响正常业务操作。

6. 定时调度：

您可以使用Linux系统自带的定时任务工具（cron）将该脚本定时执行。例如，将脚本每天凌晨3点执行，可添加如下定时任务：

0 3 * * * /path/to/script_name.sh

这将在每天凌晨3点自动执行脚本。

7. 退出情况：

  • 如果当前时间不在指定范围内，脚本将输出错误信息并退出。
  • 执行完毕后，脚本将输出结束信息到日志中。

请根据您的具体需求和环境，调整脚本中的参数和配置。

10
6
5
0

版权声明：本文为 TiDB 社区用户原创文章，遵循 CC BY-NC-SA 4.0 版权协议，转载请附上原文出处链接和本声明。

评论
暂无评论
背景删除方法解析脚本化脚本使用说明