针对一张20亿的大业务表，想清理删除业务上作废的数据（涉及9000w+）-释放表空间，有什么较好的办法吗？

🪐 TiDB 技术问题
1

【 TiDB 使用环境】生产环境
【 TiDB 版本】v6.5.0
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】针对一张20亿的大业务表，想清理删除业务上作废的数据（涉及9000w+）-释放表空间，有什么较好的办法吗？
1、普通的delete操作，我印象中，是无法回收表空间的。
2、这张表做过分区裁剪，可以使用分区键字段。
3、以前没有测试过这么大量数据的删除操作，tidb主库里还有ticdc对下游从库集群的同步，如果进行9000w的delete操作，即使是分批进行，ticdc能否扛住主从之间的数据延迟。
4、会不会影响到其他正常业务表到下游的数据同步呢

【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】

2

如果你是整个分区不要了 可以truncate table分区。 如果delete 建议用官方的分批删除
https://docs.pingcap.com/tidb/stable/dev-guide-paginate-results

SELECT
    floor((t.row_num - 1) / 1000) + 1 AS page_num,
    min(t.id) AS start_key,
    max(t.id) AS end_key,
    count(*) AS page_size
FROM (
    SELECT id, row_number() OVER (ORDER BY id) AS row_num
    FROM books
) t
GROUP BY page_num
ORDER BY page_num;

The result is as follows:

+----------+------------+------------+-----------+
| page_num | start_key  | end_key    | page_size |
+----------+------------+------------+-----------+
|        1 |     268996 |  213168525 |      1000 |
|        2 |  213210359 |  430012226 |      1000 |
|        3 |  430137681 |  647846033 |      1000 |
|        4 |  647998334 |  848878952 |      1000 |
|        5 |  848899254 | 1040978080 |      1000 |
...
|       20 | 4077418867 | 4294004213 |      1000 |
+----------+------------+------------+-----------+

DELETE FROM books
WHERE
id BETWEEN 268996 AND 213168525
ORDER BY id;

3 个赞
3

9000W不算多，你加where条件筛选按主键， 分割成多个delete语句，每个delete语句一次删除10万条，一条条执行就可以了。

普通的delete操作，gc不能释放空间，compact会释放，自动compact会释放非常慢，删除完得几天可能才能释放出来。

2 个赞
4

写个shell脚本循环删除就行

delete_db_sql=" delete from xxx a where a.create_time < '2023-01-01 00:00:00'  limit 50000;"
echo $delete_db_sql
i=0

while ((++i)); do
    a=$(/bin/mysql -uroot -pxxx -A xxx -h10.10.10.xx -P 4000 --comments -e "${delete_db_sql}" -vvv|grep "Query OK" |awk '{print $3}')
    echo $a
    if (($a<1)); then
        break 1
    fi
sleep 1
printf "%-4d" $((i))
done

delete后，空间释放可以手动compaction

image
image1076×163 15.7 KB

1 个赞
5

这个只能通过脚本小步快跑了，建议搞成分区表，直接一个 drop partiton 对线上冲击比较小

1 个赞
6

create table asset_flow1;
rename table asset_flow to asset_flowold;
rename table asset_flow1 to asset_flow;
backup asset_flowold to s3;
truncate table asset_flowold;

7

此张大表，本身在之前，已经进行过分区裁剪。目前，研发要删除的数据为每分区表中的部分数据，相当于9000w的数据，分布在每一个分区子表中。所以，还是不能直接truncate子表这么暴力。

8

直接删除吧，写一个脚本慢慢删，我们都是这样删的

9

如果你需要删除大量行（数万或更多）的时候，建议使用一个迭代，每次都只删除一部分数据，直到删除全部完成。这是因为 TiDB 单个事务大小限制为 txn-total-size-limit（默认为 100MB）。你可以在程序或脚本中使用循环来完成操作。

https://docs.pingcap.com/zh/tidb/stable/dev-guide-delete-data#批量删除

1 个赞
10

这张大表的主键采用的是类似雪花算法生成的字符串

11

非事务DML方式小批量上，后台跑的 https://docs.pingcap.com/zh/tidb/stable/non-transactional-dml

delete后低峰抽空对标做个compact; 脚本: ops/tidb-compact.py · wencycool/something_for_tidb - Gitee.com

12

搞个shell脚本分批删除，delete一次 sleep一下，全部删除完再回收表空间

13

试一下非事务删除https://docs.pingcap.com/zh/tidb/v6.5/non-transactional-dml#执行时出现报错-failed-to-restore-the-delete-statement-probably-because-of-unsupported-type-of-the-shard-column

14

有分区基于分区删除，没有基于主键批量删除写个脚本

15

delete循环删，但是要注意如果下游有cdc，要把delete屏蔽掉

16

目前来看，只能根据分区键字段，匹配数据条目，设置每个delete语句。

17

下游是不允许屏蔽delete的操作的，因为，有可能会把一些正常的业务delete操作屏蔽掉。

18

好的，我参考试下

19

分区表直接drop分区不就行了

20

上面已经有建议了，小批量多次删除，应该对业务没有大的影响