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

古城微笑少年丶 2023-06-04 09:53 147阅读 0赞

在本系列的上一部分中，我们研究了在大数据删除表和索引和案例。在这一部分中，我们将继续讨论不同案例所隐含的工作量，然后考虑删除策略，或者需要制定减少工作量的策略。

### **基本开销部分** ###

除了时间本身，我们还要关注其它三个指标，即大数据删除的过程中：生成的undo和生成的redo以及发生的I/ O量。这些因素并不是相互独立的，这在优化删除的一般性讨论中引入了很多复杂性，并且删除操作本身可能会影响同时运行的其他会话，并且同时其他会话也可能影响大数据删除。

**Redo **：对每个数据块的更改都会生成redo，描述如何修改块以及几十个字节的开销：如果表上有四个索引，那么可以认为是删除的每一行更改5个块（一个表块，四个索引叶块）。

**Undo **：每次删除行时，通过在undo段中写入如何重新插入该行的说明以及如何“撤销删除”索引条目来“保留”该行。每个undo条目都有几十个字节的开销，每个undo条目都是对数据块的更改，因此生成更多redo，结合两个重做源，单行删除和四个受影响的索引可以产生大约1KB的重做，加上删除的行和索引条目的大小。

**I / O **：将数据块读入缓冲区缓存中以删除一行，同时必须读入缓冲区缓存每个受删除行影响的索引叶块。在某个阶段，oracle必须构造出已更改的数据（和索引）块，其中包括一直在更改的undo块，这个时不得不面对的等待，但幸运的是，写入可能会在后台处理，并且可能不会影响删除的速率; 但另一方面，对于一个非常大的删除，我们可能会发现生成的undo量是如此之大，并且表和索引的读取如此随机，以至于数据库不断的刷新buffer cache进而导致写入极为缓慢。

**并发 (1)  **：即使只有一个只读会话，undo和redo日志也不会停止产生日志，如果其他会话必须检查已修改（但尚未提交）的块，那么必须使用undo块来检查提交时间并生成已更改的块的读取一致版本，以便可以看到我们要删除的行。这有两个影响，第一其他会话最终可能会物理读取undo块（增加I / O负载），然后强制数据库刷新buffer写入磁盘，以使buffer可用于它们要创建的读取一致性副本（进一步增加I/ O负载），第二，如果另一个会话必须从磁盘读取我们已经更改的块，那么它将要做的第一件事就是准备应用”延迟块清除”对于它将在那里找到的未提交的更改（即将的删除的数据）, 即使发现它不需要进行清除oracle仍然会生成60个字节的undo，并且每次从盘中读取这样的块，读取会话将生成另外60个字节，直到我们最终提交并且下一个读取块的会话执行“适当的”延迟块清除。大数据的删除运行运行时间越长速度越慢，这些并发效应可能是其中一个重要原因。

**并发 (2) ** ：那么并发性问题可能会更严重——甚至忽略了另一个会话可能正在锁定我们要删除的行的可能性，并让会话等待TX锁。即使没有其他会话更改我们尝试删除的任何行，它们也可能更改与要删除的行共享块的行，并且我们必须创建这些块的读取一致性副本，以检查开始删除时当前是否存在数据，以及当我们开始的时候就在那里的数据还没有消失——我们需要做一个“写一致性”删除：最好这意味着我们可以做大量的工作检查，最坏的情况下，这意味着我们可能会发现我们的会话发现一个它无法处理的不一致，然后回滚并重新启动自动删除。后者可能有点罕见，前者是导致大型删除在运行过程中变得越来越慢的两个关键原因之一。

### **一些有趣的数据** ###

在上一部分中，我们提供了一些代码来创建一个包含四个索引的表，以作为一些思想实验的基础，现在使用该数据来生成一些性能数据。在我这样做之前，值得一提的是优化器如何计算删除的成本，其实非常简单：成本是等效“selectrowid from ...”查询的成本，用于标识要删除的行的rowid。优化器不允许访问表的成本删除行，也不允许维护索引的成本。

在一个简单的删除案例，如 “deletefrom t1 where client\_ref < ‘F’” 这意味着oracle将会在三个执行计划中选择一个，可能是全表扫描t1和索引扫描client\_ref或者采用索引快速全扫描client\_ref，实际上，直达12c优化器才会选择indexfast full scan，这一点可以说是oracle的一个bug，直到12.1.0.2生版本才被修复。

下面我们看看两个删除语句的几个数字：第一个将删除date\_open超过五年前的所有行（即最旧的50％的数据），第二个将删除带有引用的客户端以字母A到E开头的代码（小于20％的数据）。

delete from t1
    where date_open < add_months(sysdate, -60);
    5000000 rows deleted.

我们假设六种不同的场景，当没有索引时，从表中删除，第二个场景只有主键索引，第三个场景中有主键和client\_ref索引，在所有这三种情况下，删除将遵循完整的表扫描。

最后三个场景将包含所有四个索引（主键，date\_open，date\_closed和client\_ref）; 第一个方案将使用表扫描，第二个将使用索引快速全扫描的date\_open索引-在默认情况下出现的，事实上，与12C的路径-最后将使用索引范围扫描的date\_open索引。

从v $ sesstat视图我们可以看到redo条目的数量，redo大小和undo更改向量大小，每次删除的实际执行时间，这里需要注意的是，执行时间对于一般情况来说并不是一个好的指标，因为假如使用的是固态磁盘，所以任何I/ O都会非常快，而且运行时可以与访问和修改的块数相比，缓冲区缓存的大小受到的影响最大。

另外一点需要明确说明 - 在每次测试之后都使用drop table purge方式并重新创建表，因此每次的测试结果与先前的测试是无关的，具体测试结果如下：

![format_png][]

从前三个结果可以看出，随着索引数量的增量，redo和undo以及时间都是随索引数量增加的。

从后面三种测试结果可以看出，tablescan或索引快速全扫描删除的情况 - 尽管资源使用有微小的变化，两者之间的时间差异很小。最后，当我们通过date\_open上的索引驱动删除时，我们得到了一个非凡的变化，重做条目的数量急剧下降 - 几乎回到“无索引”删除 - GB中的撤消和重做大小以及删除时间半，发生了什么变化？

在这里Oracle使用一种完全不同的策略，对于tablescan / index快速完整扫描，Oracle从表中删除一行，然后依次更新每个索引，然后再继续删除下一行。但在这里，对于索引范围扫描/完全扫描，Oracle删除表行只记录需要更新的每个索引的rowid和键值 - 然后继续到下一行而不更新任何索引。当表删除完成时，Oracle按索引按键排序它所累积的所有数据，并使用批量更新索引进行延迟维护。由于批量更新导致每个block一个undo记录和一个redo更改向量更新（而不是每行更新一次）redo条目的数量可以显着下降，。在我们的示例中，我们先是保存了每个索引22,000个块（22,000个撤消/重做条目）然后进行集中更新，而不是每个索引500万行。

但是有一对重要的统计数据仅出现在范围扫描的删除中：

sorts(disk)                              4

sorts(rows)                     20,017,391

排序的20M行是5M行x4个索引。随着内存大小调整排序溢出到磁盘，因此，可以选择执行大型删除的机制，并选择何时使用可能会显着影响工作负载出现位置以及影响程度的资源。

事实上存在一个变化，我的第一个例子显示了索引驱动方法的巨大好处，并不意味着通过索引驱动是最好的事情。您仍然需要考虑数据表架构。在的第二个例子 -包含所有四个索引并显示三个可能的执行计划（ablescan,index range scan, index fast full scan），在这种情况下，对索引的数据再次执行删除操作，数据在表中均匀分布，而不是按顺序排列在表的开头，删除的数据行大约在1.9M：

delete from t1 where client_ref < 'F'

![format_png 1][]

我们再次看到通过适当的索引驱动删除导致redo条目的最小数量和最小undo大小; 然而Tablescan尽管产生了大量更多的undo和redo，但是可以在不到一半的时间内执行; 索引快速全扫描，尽管生成几乎相同的undo和redo数据量，但速度比索引范围扫描慢近3.4倍。

要理解为什么这个删除的最佳（以时间为中心）策略与前一个删除的最佳策略相反，我们需要找到时间花在哪里，如果我们想要更多细节，可以通过（v $ segstat / v $segment\_statistics）进行统计查询：

Rangescan:       1,275M db file sequential read(80 seconds)
    Fast fullscan:   2,860M db file sequential read (369 seconds)
    Tablescan:          95K db file sequential read, 1600 db file scattered read (total 18seconds)

检查物理I / O发生的位置（哪些段）有助于我们了解发生了什么，当我们通过索引范围扫描时，我们可以非常有效地延迟维护4个索引，但是client\_ref值范围的数据模式意味着我们不断跳过表，不断刷新内存然后重新启动，读取我们需要更新的块。在1.275M单块读取中，1.19M是表中的块 -表中只有204,000块; 我们已经读了五次每个块（对于从'A'到'E'的每个字母有效一次），当我们做一个快速全扫描上的索引，我们有同样的问题加上我们在同一时间更新索引一行和其他三个指标跳来跳去，在一个相当随机的方式，这意味着我们最终会有更少的缓冲区来保持表的缓存，并在表上执行更多的重新读取，以及重新读取索引块。快速全扫描方法的2.86M读取包括来自表的1.75M读取，来自主键的274K读取和来自两个基于日期的索引的430K读取，而我们正在使用的client\_ref索引仅读取25,000个读取驱动索引快速全扫描。

当我们执行tablescan时，我们只读取一次每个表块，没有跳转和重新读取，并顺利地遍历两个索引（主键和date\_open），同时在date\_closed索引上稍微随机跳转并关注client\_ref索引的一小部分（5/26），因此可以很方便地保持高速缓存。索引上的随机读取次数分别为22K，26K，40K和4.5K。

我们所看到的只是我们对索引和旧问题的思考方式的另一个微小变化：“为什么Oracle不使用我的索引？”。每当我们查看一个索引时，我们会问两个问题：“我们将访问多少数据？”，“数据的分散程度如何？”使用索引或使用表扫描来驱动删除之间的选择实际上只是另一个变体。这是优化器在决定如何从表中选择数据时必须做出的选择。

### **方案的选择** ###

最终我们倾向于有两个选择，我们是通过索引范围扫描还是通过tablescan删除。（对于更复杂的删除，选择可能会消失，简单的索引范围扫描，具有微小的变化，似乎是唯一的特殊情况）。需要依次考虑两个问题：

第一：如果我使用tablescan，我该如何绕过这个索引，从这个索引的缓存中获得多少好处。此索引是否对随机I/O构成威胁，因此在执行删除操作时需要考虑删除（或禁用）它。如果删除这个索引（可能还有其他一些索引），那么在删除时，其余的索引将保持缓存状态。

第二：如果使用这个索引来驱动删除，该如何在表中跳转，将从延迟索引维护功能中获得多少好处，有多少表的随机I/O和表块缓存命中失败。随机表I/O带来的威胁是可以忽略的，因为redo日志记录、redo日志归档等的减少。要做多少工作来排序相关的索引项，这些索引项最终将被删除。

正如我们经常看到的那样，做出最佳选择并不总是那么容易，但最终可以做出选择。作为一个非常初步的指导原则 -如果您可以通过其后面具有良好聚簇数据的索引来驱动您的删除，那么这可能是索引路径将比tablescan路径更好的选择。但是，与所有指南一样，还有其他需要考虑的陷阱 - 我们将在以后的文章中寻找这些陷阱。

有一个特别重要但简单的细节你应该记住，当你升级到12c时，优化器有一个简单删的第三种访问路径选择 index fast full scan，这条路径可能比目前发生的任何事情都要糟糕，但优化器会选择它，因为它是 “select rowid from table…”.的成本最低的访问路径。

*原作者: **Jonathan Lewis*

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

**| 译者简介**

**王显伟·沃趣科技高级数据库工程师**

沃趣科技高级数据库工程师，专注ORACLE数据库八年，精通Oracle体系结构、丰富的数据库故障诊断、性能调优、OWI、数据库备份恢复及数据迁移经验。

[format_png]: /images/20230601/ac8787fcecc34c36a57e36f42926e4e1.png
[format_png 1]: /images/20230601/46e3ad19146b4e79957f473e2cfb3b9c.png