作者： WalterWj 原文来源：https://tidb.net/blog/6fd1515e
背景
因为 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; 来循环删除，会发现删除越来越慢的现象。
这个时候，我们可以使用找入口和出口的方式来进行删除：
找到需要删除数据的入口：
第一步可能需要耗时较久，因为要先找到入口，需要做一次全扫，当然有 limit 下推，因此没有那么慢。
基于入口，找到一个批次的出口
查询 SQL 中：
这样我们就找到 id 为 1-3006 范围的时候，且符合 insert_time 字段的条件，为 1000 行。
这里看执行计划，就知道，SQL 是 TableRangeScan 算法，本身还有 limit 下推优化，这步不会受到 MVCC 的影响。
根据主键来进行数据删除
可以看到 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=1000scriptStartTime=11scriptEndTime=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 脚本的使用说明：
脚本功能：
该脚本用于在指定时间范围内执行定时删除 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. 退出情况：
请根据您的具体需求和环境，调整脚本中的参数和配置。
评论