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

太过爱你忘了你带给我的痛 2023-06-04 09:56 11阅读 0赞

在本系列的前一期文章中，我制作了一些图，突出显示了按表扫描执行大量删除操作和按索引范围扫描执行大量删除之间的主要区别。根据所涉及的数据模式，选择正确的策略可能对随机I/Os的数量、生成的undo的数量和排序所需的CPU数量产生显著影响——所有这些都可能影响执行删除所需的时间。

然而，这个简单的演示跟生产环境当中相比，生产环境当中这个情况更为复杂。所以，如果你面临着一项艰巨的任务，你需要仔细考虑如何对真正代表你要处理的系统的东西进行建模。实际上有两种不同的情况，这一点很重要。

\*当你在处理一个非常大的一次性任务时，你需要在第一时间就把它做好，一些关键性的特殊情况不要发现的太迟——尤其是如果你不允许把生产系统离线来完成这个任务任务，而且你的工作期限很紧的话。

\*当你有一份常规的、但不经常发生的、非常大的工作时，有必要了解一下哪些看起来不相关的小操作可能对运行时产生很大影响;而且，了解下一次升级可能会出现什么问题是值得的，这样您就可以预先解决任何问题。

当然，后者的一个简单例子是我对12c的简短评论，以及它通过索引快速全扫描来驱动删除的能力-这一功能在早期版本的Oracle中无法运行。在我的小示例中，一个测试将其执行计划从11g的索引全扫描更改为12c的索引快速全扫描，完成所需的时间是原来的两倍。

继续想一想——当你试图通过索引范围扫描来删除Oracle中的表或者索引时，您能想到多少事情，这可能会产生怎样的影响?

对于一个繁忙的系统，这个建议听起来不错。有时候，你会发现一个长时间运行的DML语句在运行时速度非常慢，因为事实上它涉及到数据中最近的部分，因此会受到当前变化的影响;从这一点来看，Oracle发现它必须读取undo段来获取undo数据，这使得创建与读取一致的数据块版本成为可能-它需要这样做，以便它可以检查当前和读取一致的版本的块同意哪些行应该删除。

我做的一个例子是通过“date\_open”索引删除数据-因此，如何强制索引进行降序范围扫描，以便首先检查最新的数据在它有很多(或任何)时间遭受其他DML的附带损害之前?

有一个非常快捷的方法可以检验这个想法的有效性。所以我们要做的就是检查排序的行数和删除的行数我们就能知道优化是否发生了。

我的测试数据集有1000000行和4个索引(主键client\_ref、date\_open和date\_closed索引)，所以在最好的情况下，我应该看到:“sort (rows)”= 4 \*行被删除。下面是我做的一个测试的总结，我想知道会发生什么：

delete /*+ index_desc(t1 t1_pk) */ from t1 where id <= 5e6
    
    
    5000000 rows deleted.
    
    Name                                 Value                       
    
    ----                                 -----
    sorts (rows)                            29

我们删除了500万行并(有效地)没有排序。当我们按降序遍历索引时，优化根本不适用—我确实检查了执行计划是否显示了我所指定的“索引范围递减扫描”。

create index t1_dt_open on t1(date_open desc) nologging tablespace test_8k_assm_2;
    delete /*+ index(t1 t1_dt_open) */ from t1 where date_open <= add_months(sysdate, -60);
    
    4999999 rows deleted.
    
    Name                                 Value
    ----                                 -----
    sorts (rows)                    20,003,449

在副作用很小的范围内，“sort (rows)”= 4 \*已删除的行:所以可以使用降序索引先尝试删除较新的数据——这很好，作为一个通用特性来记住可能很有用。

让我们想象一下其他可能出错的情况。

\*我在这个表上定义了一个主键——但是你可以使约束延迟，或者您可以简单地创建一个非惟一索引来保护惟一(或主键)约束。如果我们试图通过主键索引删除，会产生什么影响?

\*如果我们要考虑约束，我们可能要考虑外键约束的影响——我们有一个client\_ref列，在生产系统中，它可能是对clients表的外键引用。让我们创建这个表并添加外键约束。

\*当我们使事情变得更困难时——有一个众所周知的特性将数组处理转换为“逐行”处理——触发器。如果我们向表中添加行级触发器，会产生什么效果?什么类型的触发器(在之前/之后、插入/更新/删除)有什么区别吗?

以下是一些结果-首先，主键约束的非唯一索引：

alter table t1 drop primary key;
    
    alter table t1 add constraint t1_pk primary key(id)
    deferrable initially immediate
    using index nologging tablespace test_8k_assm_2
    ;
    
    delete /*+ index(t1 t1_pk) */ from t1 where id <= 5e6;
    
    5000000 rows deleted.
    
    Name                                 Value
    ----                                 -----
    sorts (rows)                     15,000,004

在这个例子中，Oracle将我的主键索引设置为非惟一，作为约束可延迟的副作用，但是即使约束不可延迟，并且您只是将索引创建为非惟一，其效果也是一样的。统计数据告诉我们，我们已经将优化应用于四个索引中的三个——快速检查一下v$segment\_statistics，就会发现它是主键索引，没有进行特殊处理，它受到了超过500万个“db块更改”的影响。在这一点上，有必要快速检查一下，看看通过其他索引驱动是否会改变这种情况——但是不会，这是惟一约束与非惟一索引结合的副作用。

其次，当大表是“子表”时，引用完整性的影响：

create table t2 (
            client_id,
            client_name
    ) as
    select
            distinct
                    client_ref,
                    rpad('x',100,'x')
    from
            t1
    ;
    alter table t2 add constraint t2_pk primary key(client_id);
    alter table t1 modify client_ref not null;
    alter table t1 add constraint t1_fk_t2 foreign key (client_ref) references t2(client_id);
    
    5000000 rows deleted.
    
    Name                                 Value
    ----                                 -----
    sorts (rows)                    15,002,849

我们已经排序了大约1500万行，而通常我们需要排序2000万行.同样，我们可以检查v$segment\_statistics来找出哪个索引遭受了500万的损失“db block changes”你可能不会对“外键”索引被逐行维护而感到惊讶-我们可能会猜测，这是某种先发制人的代码使得Oracle必须处理“外键锁定”威胁。

我们通过主键删除这个特定测试的后续操作是，考虑如果我们通过外键索引本身删除，或者甚至将约束修改为“on delete cascade”并删除一些父行，将会发生什么。通过client\_ref在t1上驱动delete仍然优化了其他三个索引，但是当您试图利用“on delete cascade”机制时，这个技巧根本没有机会产生大规模的效果。在幕后你会发现这样的事情：

delete from "TEST_USER"."T1"
    where
     "CLIENT_REF" = :1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        3      0.00       0.00          0          0          0           0
    Execute   3000      5.23      15.37      69349       9238     428052       32510
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     3003      5.23      15.37      69349       9238     428052       32510
    
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  T1 (cr=3 pr=22 pw=0 time=9672 us)
         7          8         11   INDEX RANGE SCAN T1_CLIENT (cr=3 pr=0 pw=0 time=125 us cost=3 size=594 card=22)(object id 150589)
    
    This output the consequence of a bulk delete of 3,000 rows from t2 – because of the “on delete cascade”, the delete operated row by row on t2 and for each row Oracle executed a delete statement against t1.

这个输出是t2批量删除3000行的结果——由于“on delete cascade”，delete在t2上逐行操作，对于每一行Oracle都对t1执行一条delete语句。

从技术上讲，基于数组的优化是有效的，由于索引范围扫描，它给我们带来了一点好处，但是数据的分散性是如此之大，以至于每次调用几乎没有给我们带来任何好处。在某个阶段，我们将不得不进一步探索这种父/子的关系。

最后是触发器。众所周知，行级触发器可以将数组处理转换为单行处理——Oracle的索引维护优化也会发生同样的事情吗？

create or replace trigger t1_brd
    before delete on t1
    for each row
    begin
        null;
    end;
    /
    
    
    delete /*+ index(t1 t1_pk) */ from t1 where id <= 5e6;
    
    5000000 rows deleted.
    
    Name                                 Value
    ----                                 -----
    sorts (rows)                         2,639

优化完全消失了。同样的事情也会发生在“为每一行删除后”触发器上，但是如果触发器是insert或update(行级)触发器，则不会发生这种情况。值得一提的是，索引优化也发生在索引列的值发生变化的更新上(请参阅本文)，因此留给感兴趣的读者一个练习，看看哪些(如果有的话)触发器类型允许优化在数组更新后继续存在。

**| 译者简介**

**汤健·沃趣科技数据库技术专家**

沃趣科技数据库工程师，多年Oracle数据库从业经验，深入理解Oracle数据库结构体系，现主要参与公司一体机产品安装、测试、优化，并负责电信行业数据库以及系统运维。