batch ddl job执行时间差距巨大，原因几何？大佬速来

🪐 TiDB 技术问题
21

要不你试试窗口函数，看看呢，效率应该会很高

22

窗口函数删历史数据比这样高吗？
我是看群里军军大佬说现在版本推荐用batch ddl删的

23

也可以啊，方式很多，batch on 我也用过，挺快的

24

能发下那个原始sql不

25

看这个执行计划，感觉是个auto_increment+聚簇索引表的组合。

https://docs.pingcap.com/zh/tidb/stable/clustered-indexes#聚簇索引

这个组合在批量写入的时候，是容易发生热点问题的。

删除对LSMtree也是一种写入。感觉是这个原因。

26

batch on postid limit 50000 delete from xpost where posttime < ‘2022-09-6 00:00:00’;

27

那我这batch ddl 该怎么删合理呀

28

喊表妹让研发看看吧，这个id拆分不知道按啥来估算的，按理说应该是按posttime查询出postid后 再把postid根据limit拆分，但这个看着好像不是

29

posttime 上有索引嘛？是否可以尝试用这个字段分一下看看是否会快一些。

batch on posttime limit 50000 delete from xpost where posttime < ‘2022-09-6 00:00:00’;

另外按时间分区，或者ttl来清理这种过期数据也是一个比较好的方案。

https://docs.pingcap.com/zh/tidb/stable/time-to-live

30

pistid 和posttime 都是一直递增 插入后数据不变化吗？逻辑应该是这样: 先按time列扫出符合条件的id来，然后把id 按50000来拆分，由于有time的限制虽然数量上符合要求，但是这个id有的范围比较大，也就是time+id才能保证5000的批次，由于没有time的索引所以执行计划选择了先走id列（id列的聚簇表）,如果是非id单列主建要是在time+id有个组合索引就快了，或者time列索引

1 个赞
31

是不是有锁冲突导致大量backoff

1 个赞
32

是否删除越来越慢取决于你是否用limit了，用了limit就会越来越慢

1 个赞
33

底层数据分布的问题，当有 shard_rowid_bit 是更容易遇到，要么就这样慢慢搞，要么重新设计下，防止这种情况出现 :smiley_cat:

1 个赞
34

这和是否使用limit没有关系吧？一个这不是分页列表查询，offset变大，扫描的数据量越大导致查询变慢；另外batch on非事务SQL本身就需要使用limit。

35

这种方式有一个弊端需要注意下，就是如果分页逻辑中如果有过滤条件并且分页的字段值分布比较分散，假设你每页分5万，在实际执行分页DML的时候扫描的key有可能会远远大于5万。

36

where 如果能按主键区间查就不会有offset的问题，楼主那个应该是一次删除的太多了，一般一次1万到10万都行

37

也可能不是说你的分页id不合理，之所以说出现你按postid分页删除5万数据，但是批量删除5万的DML却扫描了远远超过5万，是因为posttime < ‘2022-09-6 00:00:00’导致需要扫描的数据量不稳定，比如第1页需要扫描30万数据就找到了满足posttime < ‘2022-09-6 00:00:00’的5万条数据，第2页需要扫描10万数据就可以找到满足posttime < ‘2022-09-6 00:00:00’的5万条数据…第n页可能需要扫描1000万数据才能找到满足posttime < ‘2022-09-6 00:00:00’的5万条数据。扫描的数量越大，扫描的region也就可能越多，所以决定你批量删除的速度的是你这一页扫描的数据量的大小，扫描的数量越大，速度一般越慢。

1 个赞
38

where条件里只按主键区间查是不会有扫描数据量波动的问题，一般批量DML扫描的数据是比较稳定的；而如果用了其他过滤条件，扫描的数据量可能会因为符合过滤条件的数据分布不均的原因波动比较大。
楼主这种非事务删除的语句

batch on postid limit 50000 delete from xpost where posttime < ‘2022-09-6 00:00:00’;

这种非事务删除的SQL可以理解为两段SQL的组合，第一段是分页查询SQL

select 
min(t.postid) AS start_key,
max(t.postid) AS end_key,
floor((t.row_num - 1) / 50000) AS page_postid
FROM
(
    SELECT
        t1.postid,
        ROW_NUMBER () OVER (ORDER BY postid) AS row_num
    FROM
        xpost t1
    where posttime<'2014-09-12 06:19:11'
) t
group by page_postid
ORDER BY
start_key;

第二段是实际分页删除的SQL

DELETE FROM xpost t1 WHERE t1.postid BETWEEN #{start_key} AND #{end_key};

而这种情况下不同的页的start_key和end_key范围扫描的数据量会可能因为数据分布不均等导致波动比较大。

1 个赞
39

where条件里只有主键区间查询也有一种问题，就是扫描的数据量是比较稳定的，但是实际删除的数据量是不稳定的；
就比如如下这种
第一段分页SQL：

select 
min(t.postid) AS start_key,
max(t.postid) AS end_key,
floor((t.row_num - 1) / 50000) AS page_postid
FROM
(
    SELECT
        t1.postid,
        ROW_NUMBER () OVER (ORDER BY postid) AS row_num
    FROM
        xpost t1  
) t
group by page_postid
ORDER BY
start_key;

第二段实际分页删除的SQL：

DELETE FROM xpost t1 WHERE t1.postid BETWEEN #{start_key} AND #{end_key} AND  posttime<'2014-09-12 06:19:11';

这种扫描的数据量是比较稳定的，但是每页删除的数据量就不稳定了，有可能有一页删除了4万，另一页则只删除了几千条的情况出现。
但这种方式相对而言，更不容易出现不同分页删除的耗时波动很大的问题；也比较安全一些，不容易出现极端情况下分页扫描数据量过大，而导致节点OOM。

1 个赞
40

是的，这个字段也有索引，最后用这个字段分区删着 速度稳定了，但删除速度没有变快 不像用postid这样时间差距很大。

1 个赞