使用tidb-toolkit批量删除/更新数据

作者： realcp1018

一、背景简介

在日常使用TiDB的场景中经常会遇到此类问题：

1. 建表时未使用分区表，导致数据删除困难。
2. 想要快速的批量更新数据，但是每次都需要编写繁杂的跑批任务。
3. 想要在数亿、数十亿的大表中删除几千完或几百万数据但是却总是遇到“事务大小超出限制”的错误。

为解决此类问题我编写了一个通用的删除工具，用于处理此类“事务大小超出限制”的错误。 可类比mysql的oak-chunk-update工具。 这个工具在我司生产环境运行两年以来，高效的删除了大量数据，目前已趋于完善，因此写笔记分享出来。

项目地址：https://github.com/realcp1018/tidb-toolkit

二、环境要求

git clone https://github.com/realcp1018/tidb-toolkit.git

运行 "python3 -m pip install -r requirements.txt" 安装python3库依赖.

将项目目录添加至$PYTHONPATH：

# 假设项目被clone至/data目录下：  
export PYTHONPATH=$PYTHONPATH:/data/tidb-toolkit

三、使用示例

1. 使用tk_dml_byid对表执行 "delete from where ..." (表必须未设置auto_random或shard_rowid_bits，如果误在此类表上运行也关系，只是效率极底)

# 编辑 tidb.toml 的 [basic], [dml] 和 [dml.by_id] 部分，其他部分的设置不影响本次运行  
db = "test"  
table = "tb1kb_1"  
sql = "delete from tb1kb_1 where is_active=0;"  
execute = false  
# 运行:  
python3 scripts/tk_dml_byid.py -f conf/tidb.toml -l tb1kb_1.log  
# execute = false: 设置此项表示不实际进行数据删除，仅打印一个拆分后的示例SQL，适用于比较谨慎的场景  
# 确保输出的拆分SQL符合预期，然后可以修改为true实际运行

2. 使用tk_dml_by_time对表执行 "delete from where ..." (表已设置auto_random或shard_rowid_bits，或者仅仅想根据时间列删除极少部分数据)

# 编辑 tidb.toml 的 [basic], [dml] and [dml.by_time] 部分  
db = "test"  
table = "tb1kb_1"  
sql = "delete from tb1kb_1 where is_active=0;"  
# 假设 create_time 类型为 int(时间精度为ms)  
split_column = "create_time"  
split_column_precision = 3  
split_interval = 3600  
start_time = "2021-01-01 00:00:00"  
end_time = "2021-12-31 00:00:00"  
execute = false  
# 运行:  
python3 scripts/tk_dml_by_time.py -f conf/tidb.toml -l tb1kb_1.log

3. 使用tk_chunk_update对表执行 "delete from where ..." (可覆盖上述两种场景，无需考虑表是否设置auto_random或shard_rowid_bits)

# 编辑 tidb.toml 的 [basic], [dml] 和 [dml.chunk_update] 部分  
db = "test"  
table = "tb1kb_1"  
sql = "delete from tb1kb_1 where is_active=0;"  
execute = false  
# 运行:  
python3 scripts/tk_chunk_update.py -f conf/tidb.toml -l tb1kb_1.log  
# execute = false  
# 确保输出的拆分SQL符合预期，然后可以修改为true实际运行

四、常见问题

1. 支持哪些类型的SQL？

工具支持以下几种SQL类型：

1.delete from <table> where <...>  
2.update <table> set <...> where <...>  
3.insert into <target_table> select <...> from <source_table> where <...>

非DML类型会直接报错退出。

2. 如何在tk_chunk_update 和 tk_dml_by_id, tk_dml_by_time之间做出选择？

tk_chunk_update无需人为进行表的类型判断，适用性高，只要是tidb的表都可以使用。 相比tk_dml_by_id可以避免大量无效rowid扫描，相比tk_dml_by_time则单条SQL执行更快。

但tk_chunk_update有极底概率遇到性能衰退的情况：如果表包含大量空region(或gc缓慢)，那么在执行日志中你会看到如下记录：

ConnectionPool Monitor: Size 99以及:chunk xxx Done [split_time=0:00:00.523525] [duration=0:00:00.229860] [rows=1000] [sql=...]

可以看到 split_time 大于 duration,这意味着chunk的生产速度慢于消费速度, 而tk_chunk_update的要点之一是就是需要保证chunk的生产速度远大于消费速度。

其原理如下, 首先ChunkSpliter.split()通过如下查询条件获取chunk的右边界：

select max(rowid) from   
   (select rowid from table_name where rowid > current_rowid order by rowid limit 0,chunk_size) t

并以current_rowid作为chunk左边界，然后生成拆分后的SQL，输出一个Chunk同时将current_rowid推进至chunk右边界以便下一个chunk拆分使用，而split()是一个python生成器。

然后Executor遍历split()生成的chunk，调用其execute方法并将其作为一个future放入ThreadPoolExecutor中(执行并发度为max_workers)。

这个机制要求chunk的生成速度大于消费速度，否则会衰退为单线程执行，但是为目前了规避by_id的缺点又必须采用这种动态生成的方式，因此当你遇到此类性能衰退时, 请使用 tk_dml_by_id/tk_dml_by_time.

3. 关于 tk_dml_byid.py 和 tk_dml_bytime.py的拆分方式

By id:

• 默认使用rowid作为拆分列(如官网所示，数字类型主键就是rowid，其他情况则有一个内置的_tidb_rowid作为rowid)
• 如果表设置了(SHARD_ROW_ID_BITS 或 auto_random), 那么建议使用 tk_dml_bytime 或 tk_chunk_update.
• SQL的拆分方式很简单，直接按rowid累加batch_size拆分为无数个batch(例如rowid >= 1 and rowid < 1000), 并发执行度为 <max_workers>.

By time:

• 与by id的拆分方式相似，但是是通过时间列拆分为无数个task，拆分单位为配置文件中的 split_interval
• 执行方式与by id略有不同，因为按时间列拆分后的task内部可能包含的记录数扔超出事务限制，因此实际上在task内部会以batch_size为单位顺序执行同一条分页SQL直到影响行数为0
• 通过 grep Finished <log-name> | tail 可以看到有多少task已完成

3. 关于 tk_chunk_update.py 的执行进度查看

通过如下命令可以查看当前任务的执行进度：

tailf <log-file>|grep "write savepoint"

4. 关于 savepoint 和失败重试

tk_dml_by_id.py 和 tk_chunk_update.py 在执行过程中会生产检查点(如未设置savepoint配置则会生成一个默认的检查点文件)，检查点表示在这之前已经处理完毕的rowid。 无论是异常退出还是主动终止，再次运行时如果检查点文件存在则会跳过已处理的rowid.tk_dml_by_time.py 则 不会 产生检查点，如果任务失败建议查看执行日志手动设置一个start_time然后重跑，通常来说只需要选一个相对较早的时间即可。