ABAP大表数据删除实战：一次性DELETE为何翻车？分批删除与表重建方案

前阵子接到一个运维需求一张自建的ZLOG表攒了差不多6000万行客户要求把两年前的历史数据清掉给数据库腾点空间。第一版方案很简单写一条DELETE FROM zlog WHERE created_at 20230101放后台作业里跑。半小时后数据库组的同事打来电话UNDO表空间快满了生产库的锁等待已经飙到报警线业务端一堆事务在排队。这种翻车现场只要是做过ABAP大量删除大表数据的人应该都不陌生。这篇文章是我自己反复清理大表后的复盘总结把一次性删除为什么容易出事、批次删除该怎么拆、表重建法和分区表的适用场景讲清楚顺便给出可以直接拿去改的ABAP代码和一份避坑清单。如果你现在面对的是一张几万行的表那怎么删都行不需要看这些。真正需要这篇文章的是单表行数到了百万级、千万级删除量占全表一大半而且系统不能停、不能锁、不能把日志空间写爆——这个前提下删除动作就不再是一句话的事而是一个需要设计的技术方案。1. 为什么一次DELETE会让数据库“窒息”三个隐形杀手很多人写大表删除代码时心里想的是“数据库只要执行一条DELETE就能扫掉这些行”实际上去掉语法层面的简单底层要做的事情远超想象。先把这三个最容易被忽略的杀手说清楚后面的方案选择就都好理解了。1.1 日志与UNDO数据库要先记一本“流水账”才敢动你的数据数据库的ACID特性要求一条DELETE没提交前系统必须能回滚所以每条被删的行都要先写入UNDO/REDO日志。Oracle里叫UNDO和REDOSQL Server叫事务日志HANA也有自己的commit log机制。无论叫什么本质都是在真正删除数据之前数据库先把“删除前是什么样、删除后变成什么样”完整记下来。一次性删除3000万行意味着数据库要写3000万份这样的撤销记录。以Oracle为例UNDO表空间会飞速膨胀如果配置不够大等待你的就是快照太旧或者表空间无法扩展即便UNDO够大写日志本身也是巨大的IO开销DELETE语句执行期间数据库的redo log switch会变得非常频繁整个实例都可能被拖慢。我在一个客户的Oracle生产库里见过一次失败案例3000万行的DELETE跑了40分钟还没结束最后手动回滚回滚过程又花了1个多小时等于一个下午整个库都在处理这一条SQL的善后。1.2 锁等待大事务一天不提交其他人就得排队一天第二条DELETE影响的是并发的业务事务。数据库里删除行是要加行锁的在事务提交之前这些锁不会释放。一次删几千万行就等于锁住了表里一大片数据区域。对这个时间窗口内正好要访问这些数据的业务事务来说等锁是必然的。如果运气不好部分业务更新语句的访问路径要经过这些行所在的索引页等待范围还会被进一步放大。Oracle和其他主流数据库的锁等待还有超时机制超出后会报锁超时错误。SAP应用层对数据库锁超时的处理各有不同但结果通常是用户看到程序挂起、SM50里进程长时间卡在“数据库锁”状态最后要么等、要么用户手工终止。生产系统如果出现这种情况比删除慢更麻烦因为你不能轻易终止一个已经执行了一半的大事务——一终止就是回滚回滚又是一次灾难。1.3 索引维护删除动作的“隐藏税”DELETE不是只把表里的行抹掉那么简单。表上有几个二级索引每个被删的行都要同步去索引里删除对应的索引键值。索引越多删除操作需要维护的结构就越多IO同步放大几倍很正常。所以有些大表删除代码会想先删索引再删数据但生产环境里删索引往往需要维护窗口不是随便能做的。除此之外行存数据库还有一个高水位问题DELETE之后表占用的物理空间和段高水位并不会自动收缩。即使删掉了90%的数据SELECT COUNT(*) 也可能仍然扫描那么大一片空间后续新插入数据也不会自动复用所有碎片空间。这一点很重要因为很多人的目标不是“删完数据”而是“释放空间”。如果只靠DELETE空间释放效果通常远达不到预期。这个问题要跟客户提前对齐你是只要业务查询看不到历史数据还是数据库物理文件真的变小两种目标对应的技术方案完全不同。1.4 ABAP侧的额外开销也不容小视ABAP程序通过Open SQL与数据库交互中间还有应用服务器这一层。一条DELETE如果写成逐行删除比如在LOOP里反复调用DELETE那么几百万行就是几百万次应用服务器到数据库的往返。每一次往返都有网络开销、SQL解析和游标管理累计起来非常惊人。即便用内表批量DELETE FROM TABLE也要注意内表容量和应用服务器内存一次性把3000万行读进内存更是危险。所以ABAP侧永远要把“数据不要一次性堆在应用服务器上”作为一个基本设计原则。2. 分批删除的工程化写法从“一把梭”到“流水线”一次性删除容易出事解决办法就是把一个大事务拆成多个小事务。每批只删几千到几万行删完立刻COMMIT让锁、日志、UNDO都能被及时释放和重用。整体耗时可能会比一条大语句更久但对生产系统是安全的。下面是我实际用过的三种拆分方式。2.1 按主键RANGE切批最简单可靠的方案如果大表的主键是可比较的数值类型或类似连续编号比如订单号、自增ID那么按主键范围切批是最简单的。DATA: lv_min TYPE zlog-id, lv_max TYPE zlog-id, lv_batch TYPE zlog-id, lv_batch_end TYPE zlog-id, lv_cutoff TYPE d. lv_cutoff 20230101. SELECT MIN( id ) FROM zlog INTO lv_min WHERE created_at lv_cutoff. SELECT MAX( id ) FROM zlog INTO lv_max WHERE created_at lv_cutoff. lv_batch 10000. WHILE lv_min lv_max. lv_batch_end lv_min lv_batch - 1. IF lv_batch_end lv_max. lv_batch_end lv_max. ENDIF. DELETE FROM zlog WHERE id BETWEEN lv_min AND lv_batch_end AND created_at lv_cutoff. COMMIT WORK. lv_min lv_batch_end 1. ENDWHILE.这里有个细节容易踩坑主键范围是用“要删除的历史数据”算出来的但范围覆盖到的区间里可能有created_at大于等于截止日期的新数据夹在里面。所以DELETE的条件必须是主键范围 AND created_at cutoff两个条件都带不能只写主键范围。切批的单位建议不要超过1万到2万因为每批要删的行数不总是恰好等于范围大小锁的规模和日志量都跟着这个数走。2.2 按时间字段切批符合数据特征的推荐方案大表清理最常见的情形就是按日期归档比如删除2023年之前的凭证。这种情况下按时间切批最贴合业务特征代码也直观。DATA: lv_start_date TYPE d VALUE 20200101, lv_end_date TYPE d, lv_cutoff TYPE d. lv_cutoff 20230101. WHILE lv_start_date lv_cutoff. lv_end_date lv_start_date 13. 每批处理14天 IF lv_end_date lv_cutoff. lv_end_date lv_cutoff. ENDIF. DELETE FROM zlog WHERE created_at BETWEEN lv_start_date AND lv_end_date. COMMIT WORK. lv_start_date lv_end_date 1. ENDWHILE.每批的时间跨度要结合单日数据量设计。如果一天50万行14天就是700万行每批还是太大建议先用SELECT COUNT(*)摸清每天行数再决定批跨度。一般把每批行数控制在一万左右最稳宁可多分几个批次。2.3 游标式分批不依赖连续值域的兜底方案不是所有大表都有理想的连续主键。有些表的键是GUID字符串有些是复合主键很难直接按数值区间切分。这时候可以用“每次取一批主键/整行删完这批从下一批起点继续”的游标式写法。DATA: lt_data TYPE TABLE OF zlog, lv_last_id TYPE zlog-id VALUE 0, lv_cutoff TYPE d. lv_cutoff 20230101. DO. CLEAR lt_data. SELECT * UP TO 5000 ROWS INTO TABLE lt_data FROM zlog WHERE created_at lv_cutoff AND id lv_last_id ORDER BY id. IF sy-subrc 0. EXIT. ENDIF. DELETE zlog FROM TABLE lt_data. COMMIT WORK. SORT lt_data BY id ASCENDING. READ TABLE lt_data INTO DATA(ls_last) INDEX lines( lt_data ). lv_last_id ls_last-id. ENDDO.游标式写法的关键是每次SELECT的条件都要带上id lv_last_id用上一批最后一条的主键作为本批的起点避免重复处理也避免漏掉。如果主键是GUID或复合键把这个逻辑改成“记录上一批最后一条的完整主键下一次条件用主键组合大于它”写法类似但条件会更长。这里还有个性能层面的思考DELETE zlog FROM TABLE lt_data这种写法让ABAP把内表传给数据库做批量删除比在LOOP里逐行DELETE省掉大量的应用服务器往返但如果你的SAP版本或数据库对批量DML支持不好也可以退而求其次把内表行数控制在500到2000再逐行删总之不要真的去逐行删几十万条。2.4 批次大小、COMMIT节奏与运行窗口批次大小没有绝对标准但有一个可复用的经验区间单批1000到10000行。5000行是我在Oracle和HANA上都比较常用的默认值。批次太小COMMIT次数太多总耗时会明显拉长批次太大事务日志和锁窗口又会回到危险区。选参数的时候可以先用开发机上的一份历史数据跑几分钟看每批执行时间。如果单批稳定在几百毫秒到几秒之间这个规模就可以接受如果单批已经超过30秒要果断调小。COMMIT WORK在SAP里同时标记了数据库LUW边界。一批DELETE加一个COMMIT能让数据库的redo/undo及时清理。不要把COMMIT放在循环内部靠近每条DELETE的位置那等于把“大批小批”变成“逐行提交”性能会急剧恶化。另外大表删除程序尽量放SM37后台作业用低峰期窗口运行不要挂在对话进程里让用户等。如果删除量实在太大宁可分多个深夜窗口分几次跑每次跑完记录一下已删除的边界保证可断点续跑。3. 表重建法当数据清理变成一次“换血手术”如果删除的数据占全表比例极高比如5000万行里只保留500万行那么“DELETE掉4500万行”听起来就不太对了——为什么不是“只保留500万行”呢这就是表重建法的思路不把所有历史数据删除而是把需要留下的数据搬到新表然后换掉原表。3.1 什么场景才值得用表重建法表重建法的适用条件很苛刻主要看两个指标。第一保留数据占比要足够低。通常我建议低于20%再考虑。如果保留数据占50%搬数据的花销差不多等于删数据没必要折腾。第二系统允许一定时间的写入停止或者至少允许在维护窗口内短时间切换表。因为“搬出去、删原表、换新表”这个过程中必须保证没有新的业务写入否则过程中的增量数据会丢。第三原表没有大量的外键、视图、AMDP、增强等强依赖。依赖越多换表代价越高。遇到全公司都在引用的标准表绝对不要走这个方案自建业务表可以考虑。3.2 用临时表倒腾数据的完整步骤以后缀_TMP的新表为例完整流程大致是这样在SE11复制原表结构创建ZLOG_TMP。复制时顺便把主键、索引、货币/数量字段的参考单位、搜索帮助这些一并带上。用一条INSERT FROM SELECT把需要保留的数据搬进新表INSERT zlog_tmp FROM ( SELECT * FROM zlog WHERE created_at lv_cutoff ).这条语句在ABAP 7.40以后的版本可用它直接在数据库内部迁移数据不走应用服务器内存。老版本数据库和SAP版本如果不支持这个写法只能分批SELECT到内表再分批INSERT那样总时间会明显增加。核对行数对ZLOG和ZLOG_TMP分别执行COUNT确认ZLOG_TMP行数等于原表保留行数最好再抽查几个业务关键日期段的明细。在低峰窗口停止相关业务程序写入确认没有进行中的批次任务。清空原表。如果条件允许让DBA用数据库层面的快速清空手段例如传统行存数据库的TRUNCATE一次性清掉比逐批DELETE快得多如果只能由ABAP程序删也要分批COMMIT不要一条DELETE硬扛。把ZLOG_TMP的数据搬回ZLOG同样用INSERT FROM SELECT然后删除ZLOG_TMP。重建或刷新统计信息让优化器拿到新的表和索引数据分布。这里说的“搬回原表”和真正意义上的表重命名替换有差别。如果要彻底换表名SAP数据字典里还涉及DDIC对象、授权对象、缓冲设置等一系列元数据问题那个必须由BASIS配合做完整变更不要指望用一段ABAP程序搞定。我上面这个版本是“临时表暂存原表清空数据回流”好处是不动数据字典里的表名很多自建表项目直接用这个套路。3.3 换血之后必须复查的依赖对象清单表重建法最怕的不是搬数据慢而是搬完以后别人告诉你某个功能坏了。动手之前把所有依赖对象拉一遍清单索引新表要确认主键索引和二级索引都建好了特别是原来用于查询性能的关键索引。外键如果其他表有外键引用原表主键清空原表前必须先处理外键约束否则数据库级别删不动。视图/投影同步视图、数据库视图、维护视图在DDIC里是否还指向有效表结构。搜索帮助附着在字段上的搜索帮助一般是跟着数据元素走的通常不受影响但建议验证。授权表授权对象是否需要为新表单独做。程序引用所有直接引用ZLOG的表工作区、类型、INCLUDE结构在DDIC变更后要重新激活。表缓冲如果原表开了SAP表缓冲清空和回流后缓冲一致性由ABAP运行时管理但新表不要忘了设置。这些检查听着琐碎但漏掉哪一个都可能在生产环境变成事故。我见过一次表重建后一个查询视图没激活导致相关报表直接报运行时错误最后花了半天重建视图。3.4 表重建法和分批DELETE的实测对比用一个参考量级说明一下两者的差别。假设ZLOG表5000万行保留500万行单行大约500字节分批DELETE 4500万行每批5000行大约9000个批次。按每批1秒算光删除就要约2.5小时考虑到索引维护和日志IO实际跑到5到7小时不奇怪。表重建法INSERT FROM SELECT搬500万行在数据库内部执行通常十几分钟到半小时TRUNCATE清空原表几秒到几十秒再把500万行搬回又半小时。总窗口通常在1小时以内。代价是表重建需要停机窗口和依赖检查。所以在可以停写、保留数据量很少的情况下表重建法优势明显反过来如果系统完全不能停、要边删边接入业务那就只能用分批DELETE慢慢磨。4. 大数据量删除前夜检查清单与数据库层面的配合方案定了、代码写好了不要急着跑。大量删除是少数几个看起来简单、炸起来要命的数据库操作上生产前最好把下面这些点全部过一遍。4.1 删除条件能不能吃到索引先做执行计划体检很多DELETE炸掉不是因为条件写错而是因为WHERE条件在表上没有可用索引数据库只能全表扫描。全表扫描除了慢还会把这个表的所有数据页都拉进缓冲池把其他热表的数据挤出去。用ST04或者DBACOCKPIT看一下执行计划确认删除条件上的列有合适的索引。有一种情况要特别注意如果删除数据占全表比例超过20%数据库优化器可能觉得“反正都要删这么多全表扫描比索引访问更划算”这是正常的。但你要观察的不只是执行计划还有锁的范围和日志量。如果删除占比很高且不能松口走表重建法至少要让SELECT批次扫数据的时候吃到索引这样每批的定位不会太慢。4.2 分区表“删分区”才是大表数据管理的长期解如果是新设计的大表或者表还有重新组织的机会强烈建议考虑按时间分区。按分区键把每月或每年一个区清理历史数据时就再也不是“DELETE几十万行”而是直接“TRUNCATE PARTITION”或“DROP PARTITION”秒级完成而且不产生海量行级日志。ABAP侧不用为分区表写任何特殊代码Open SQL照常访问。但分区的定义和维护通常要在数据库层面做SAP的DDIC和BASIS要一起介入。如果现有大表没有分区、又经常要清理建议把“分区化改造”作为一个独立的专项列入技术债清单。这个思路治本与其每次清理的时候想“怎么删得快点”不如让删除这个动作本身变得微不足道。4.3 和DBA、BASIS协作的关键沟通点跨部门协作的大表删除启动前至少要确认四件事备份策略删除前确认备份已经完成最好能锁定一个可以闪回或恢复的时间点。日志空间数据库组的同事需要监控UNDO/REDO、事务日志的剩余空间确认计划内删除产生的量不会打爆空间。锁与并发删除程序运行窗口内哪些业务程序会同时跑能不能错峰必要的话让DBA临时调整相关任务的时间。统计信息与空间收缩DELETE跑完后表的高水位、索引碎片、统计信息都需要重新处理。这部分经常被遗忘导致删除后性能反而更差。和DBA沟通时我最常说的是“我要分5000行一批提交总删除量大约4500万行预计这个窗口内产生多少日志、锁的窗口会有多长你们帮我看会不会影响别的任务”。把话语权交给监控数据比让对方“放心吧我控制了批次”可信得多。5. 几种方案怎么选一张速查表与我的建议写到这里方案其实已经清楚了但很多人还是会纠结具体场景用哪个。我直接给一张速查表按自己的经验排列优先级。5.1 方案适用场景速查表场景首选方案原因删除量占全表30%系统不能停分批DELETE按主键或时间切批窗口灵活锁可控保留数据20%允许短时间停写表重建法总耗时短物理空间释放彻底表按月/年有清晰边界未来还要持续清理改造为分区表按分区清空根治删除秒级完成删除条件列没有索引先建索引或调整条件再分批删避免全表扫描拖垮缓冲池只是想让业务查询看不到旧数据先考虑归档或软删除物理删除不是唯一路径这个表里的优先级不是死的。比如“不能停系统”和“保留数据少”同时出现时我会优先选分批DELETE因为表重建法虽然快但切换窗口的不可控风险更高。风险优先级永远高于性能优先级。5.2 如果业务允许先考虑软删除有时候“删除”只是业务上的诉求用户不想再看到旧数据不一定是数据库必须物理删除。这种情况下加一个DELETED标志位查询条件里天然过滤掉是成本最低、最安全的方案。但软删除有个明显的副作用表数据量不会降空间不会释放查询性能可能更差。所以软删除只适合“数据量可控、保留价值高、空间压力小”的业务。如果表已经几千万行还在每天涨软删除解决不了根本问题。另外SAP标准体系里做历史数据清理的正规路子是归档对象事务码SARA。归档能把历史数据导出成文件并允许必要时重载同时也能释放数据库空间。自建表同样可以创建归档对象。如果这事要长期做别只会写DELETE脚本认真评估一下归档方案。这不是绕路反而是更符合SAP治理习惯的做法。5.3 最后几句肺腑之言清大表数据这件事代码往往很简单难的是对数据库行为有敬畏感。我自己的习惯是任何超过百万行的物理删除必须先写一份短文说明白三件事——数据从哪里来、删完怎么验证、出问题怎么回滚然后拉上DBA和BASIS过一遍再执行。批次大小、运行窗口、停止写入的范围每个参数都写成可配置项别把硬编码塞进生产程序。最后每次删完我都要亲手执行几个COUNT和抽查SQL确认目标行数完全符合预期绝不依赖“应该删完了吧”这种感觉。这个经验尤其是分批和表重建的选择逻辑我后来在好几个项目里复用基本都能平稳落地。现在每次听到别人说“就一条DELETE为什么不能跑”我都能理解但也知道他们还没经历过大表删除的第一次翻车。希望你读完这篇之后不用经历翻车也能避开这些坑。