黄东旭解析 TiDB 的核心优势
535
2024-03-25
因为 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;
来循环删除,会发现删除越来越慢的现象。
这个时候,我们可以使用找入口和出口的方式来进行删除:
找到需要删除数据的入口:
(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 下推,因此没有那么慢。
基于入口,找到一个批次的出口
(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 的影响。
根据主键来进行数据删除
(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=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. 退出情况:
如果当前时间不在指定范围内,脚本将输出错误信息并退出。
执行完毕后,脚本将输出结束信息到日志中。
请根据您的具体需求和环境,调整脚本中的参数和配置。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。