大数据量删除的思考（一）

小灰灰 2024-04-17 06:23 78阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70][]

在最近一篇关于从表中删除列的文章里，我留下了一个悬而未决的问题，删除列之后你应该/可能会做什么？因为删除列只不过是“大量删除”的特殊情况。在这篇文章中，我计划为我们如何思考“大量删除”及产生的后果打下一个基础。

### **概    述** ###

在您能够找到大量删除的方案和流程之前，您必须处理好一些战略性（长期）和策略性（短期）问题。

在战略层面您会有这样的问题：您为什么要删除？您希望从中得到什么？如果您达到了初始的目标，接下来的策略（如果有）是什么？您有什么样的证据能够证明它值得你付出努力（人和机器）？您有没有仔细想过即使修复了旧的问题也可能带来新的问题？

在策略层面您可能会问决定采用的工作流程的一些细节问题：有哪些资源？您是否允许长时间中断服务或者短时间的中断服务？在或者根本不允许中断任何服务？如果应用层序必须在删除任务执行阶段运行，那么,它是否可以减少部分功能或者降低一下执行性能？您对您的系统是否足够了解呢？您是否查看过Oracle最近有哪些特性或者增强可用帮助您安全（和快速的）完成工作？

让我们看几个我最近参与的几次在线交谈的一些想法：

### **设想 A** ###

在OTN论坛中最近有一个贴子描述了“大量删除”的一个极端例子，用户有一个4tb的普通堆表，其中保留了3年数据，现在想将数据减少到每天分区并保留15天历史数据。可能促使人们大量删除数据是为了清理大量的历史数据，当然，最好的策略是以这样的目标设计系统，将删除数据变成简单的“删除分区”，这样可以做到几乎没有开销。

在这个特殊的例子中，用户（在我看来）是非常幸运的，因为他们想清除大部分数据并且只保留一小部分数据。他们需要花费一些时间去计划和测试所有相关细节（参照完整性和索引等），但是所有的这些都需要创建一个合适的范围分区表，将此表作为交换后的表，然后每天开始进行分区，之后等待16天，在删除最后的分区以清除最近三年的数据。

另外一些人可能没有那么幸运，我常常看到类似一张表中有几年的数据，而且需要按照周或者月进行分区，然后保留两年或者三年的数据，“交换一次等待三年”的方式并不可取，但是删除几年或者复制几年数据带来的开销同样是不可取的。

### **设想 B** ###

不久之前我收到的一个问题是某人来询问关于大量数据删除的策略，因为根据他们之前经验，快速删除大量数据前先删除全部索引，并在之后重建索引，最近他们测试一个案例，尽管这种方法和“仅仅删除它”的时间差异非常小，但似乎采用稍微复杂(删除索引在重建/因此有风险)的方式并没有很大好处。

这就提出了一个有趣的问题：多大的数据量删除才算是“大量数据”？这个人删除了2500w行数据，这听起来相当大，但是它仅仅是表中的4%，所以它并不是那么的庞大(相对而言);此外表已经被分区，这就降低了几分风险，另外一方面，它至少包含一个全局唯一索引，这就有点让他讨厌了，然而这台服务器可以将该任务并行加到16，因此在绝对值上来说，每个并行任务约为150w行数据，所以可能它并不是真的很大。

事实上，无论采用什么方法，完成任务的时间大约为17分30秒，但值得注意的是，如果我们用简单的删除策略，在任务期间其他用户仍然可以使用该表，由于并发使用该表，删除操作可能需要更长时间，由于争用和读一致性，要求用户活动可能会更慢(注：按照特定的顺序一次删除一个分区有什么好处么？)，并且始终存在锁和死锁威胁而导致的灾难，删除这4%的数据大概要多久一次，可能它的数据量大致相当于两年内中的一个月的数据，所以可能每个月定期清理一次，但可能不会有人介意因为"drop/delete/rebuild"失去访问权限15分钟，这些操作总是有一些好处的，大多数的索引在删除数据之后可以更加高效的运行。

### **注意事项** ###

当"大数据量删除"浮现在你的脑海中时，我希望这两个例子可以让你知道需要考虑些什么？因此，在我们开始"怎样"之前，先让我们来对可能出现的情况和与之相关的想法进行分类。

我想我过去遇到过三种基本删除模式和两种删除原因。

**删除原因非常简单：**

1.提升性能。

2.回收空间 - 希望可能是数据库或者特定表空间的空间;它最终可能是数据库之外的磁盘空间。

**常见删除模式有：**

1.根据时间来对表中的数据进行删除。

2.根据表中数据处理完成时间来进行删除。

3.从表中删除一类数据(这可能意味着我们要创建两张表，或者分区表(列表分区)，或许非分区表)。

一旦我们找出原因，我们就会提出一些关键问题--如何删除数据才能提高性能？我们如何通过其他的方式来提高效率(例如改进索引)？通过删除数据释放的空间是否可以立即使用，或者还必须做些其他操作？删除的带来的负面影响是什么？我们可能采取的进一步措施带来的负面影响又是什么？我们是否有真实的平台？我们可以对预测的停机时间进行验证，执行相应的任务，测试不可以预测的负面影响有哪些？

理解模式非常重要，但在使用数据库时却经常被忽略。当你删除数据时，在表块中和索引块中释放出相应的空间，当新数据出现时可能会重新使用该空间。但由于这种方式表中释放的空闲空间意味着新数据的物理分布与当前其他数据所遵循的分布模式不同，这意味着随着时间的推移，因为模式的不同查询(a)可能变得非常低效，优化器(b)可能认定某个索引不在是最好的选择，因为数据分布模式的改变导致索引的"clustering\_factor"出现了变化。

我提出的三种主要的删除模式，是基于他们对性能的威胁程度。如果假设你是第一次进行大数据删除，那么最容易考虑这些模式。有些时候，只有你进行了几次删除周期后威胁才会出现。如果按照数据的原始到达日期删除，很可能会在表段的开头(前几个区)留下很多的空闲块，这就意味着新插入的数据可能会插入到表段开头的一组区中，而不是表段的末尾。具体来说，假设有一个包含100000个块的表，你刚刚删除该表中前5000个块中的数据，接下来插入的几十万行数据将插入到1-5000的块中，而不是100001-105000；尽管表中的绝对位置已改变，但数据的模式不会改变。

如果是根据"处理完成"日期进行删除，那么初始删除模式可能有所不同 - 也许前1000个数据块实际上是空的，接下来1000个块的使用量下降到20%，在接下来2000个块使用量下降到40%，在接下来4000个块使用量下降到70%。随着时间的推移，新的数据将分布在比以往更多的数据块中(也许你删除的块中有一些不允许被重用直到你进行下一次大量的删除操作)。如果不参考实际应用，很难想象当大量删除发生时，为什么任何人的数据可能显示这种"衰减"模式 - 但你可能会想到一个应用获得了1、2、3或者5年的借贷协议。

在最后一种模式中 - 删除整个数据类别，"借贷"可能是很好的一个例子。出于某些原因我们可能决定为5年贷款创建一张单独的表，因为贷款已经成为业务的重要部分 - 所以我们必须从当前的贷款表中删除他们。当然，这种就是刚刚删除表中每个块10%-30%数据的模式。我们可能发现这些块均没有出现在空闲空间中，或者我们发现在接下来的九个月里，我们在表的每个块中插入了少数几行数据，而人们会抱怨“2016年的性能非常的差”。

### **索    引** ###

当然，我们在研究数据模式时还应该考虑索引中的模式(和副作用)。因为我们从少数相邻块中删除所有行，那即使其中的一个场景也意味着我们可以高效的从表中删除数据，我们还需要考虑表中每个索引都会发生什么事情。非常紧凑的表删除可能导致非常分散的索引删除，因为随机I/O - 读(通过会话)和写(数据库写入)，可能需要很长的时间，可能不会给我们任何后续空间和性能好处。

考虑从"股票价格"表中删除2001年4月1日的数据：所有的行都将一起到达，所以我们可以清空表中连续的几百个块 - 如果我们有一个索引(报价\_日期，股票\_代码)，我们将清空索引中的几百个连续的块，如果这是我们驱动删除的索引，则不会产生过多的I/O；如果我们有一个索引(股票\_代码，报价\_日期) - 我们很可能会不得不访问几千个索引叶块来删除每个索引条目！因为要执行大量的随机I/O，删除可能非常缓慢。OTN中关于插入和删除最常见的抱怨之一就是"db file sequential read"等待；执行计划中不会告诉我们关于索引维护的开销，所以很容易忘记一个大的删除操作会导致非常缓慢的随机I/O。(有趣的是SQL Server会告诉你删除操作会维护哪些索引)。

索引维护对于大的删除操作影响如此之大 - 而且会产生持久的后果 - 这一点确实值得我们思考。实际上，我们可以设计一种策略，根据每个索引的定义和实际使用情况，对单个表上的索引进行不同的处理。对于给定的表，我们可以删除(或者标记不可以)和重建一些索引，与此同时保留一部分索引，在删除后进行重建索引或者合并索引。

### 总    结 ###

何种效率及工作量将由我们要删除的数据模式决定，首先在表中，然后在索引(可能更重要的是索引)中。

**大的删除操作并不是一个平常的操作，不应该在没有经过深思熟虑的情况下进行。我们为什么要删除大量的数据？我们是否知道在删除操作完成后我们又需要如何验证我们是否到达最终目标？我们可以使用哪些方法来最小化的执行删除所带来的影响以及删除完成之后我们还需要做什么才能达到最终的目标？**在下一期中我们将讨论"大规模"删除的一些技术问题

原作者: Jonathan Lewis

原文地址：https://www.red-gate.com/simple-talk/sql/oracle/massive-deletes-part-1/

### **| 译者简介**  ###

### **杨禹航·沃趣科技高级数据库技术专家** ###

熟悉Oracle数据库内部机制，丰富的数据库及RAC集群层故障诊断、性能调优、OWI、数据库备份恢复及迁移经验。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dvcXV0ZWNodGVhbQ_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/5dd719d93e264d77aabdb4a5cfed71b9.jpeg