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

发布时间：2020-08-07 20:36:42 作者：沃趣科技
来源：ITPUB博客阅读：147

在这个简短系列的第1部分中，我提供了两个场景的非正式描述，在这些场景中，我们可以从表中进行大规模删除。没有一个具体的例子,很难想象删除数据的性质和可用的访问路径会产生大数据量删除操作对系统的性能影响,所以我要把大部分的时间花在本文讨论的两个测试生成的数据集。这篇文章似乎有点长但相当多的空间会被表格占用。

简单的数据集

随着硬件的能力和规模的不断增长，我们越来越难以就 “ 大表 ” 或 “ 大规模删除 ” 的含义达成一致，对于一个人来说， 100 万行似乎很大，而对于另一个人来说， 1 亿行似乎相当普通。

我将使用一个折中方案，用1000 万行表示一个投资系统，该系统10 年来以每年100 万行的速度增长，并且已经达到了1.6GB 的段大小。

当然，这个表只是组成整个系统的几个表中的一个，在某个时候我们会对所需要的数据担心，但是，目前，我们只考虑这个表，只考虑表本身和表上的 4 个索引。

下面是生成数据集的代码 :

execute dbms_random.seed(0)
create table t1 (
    id      not null,
    date_open,  date_closed,
    deal_type,  client_ref,
    small_vc,   padding
)
nologging
as
with generator as (
    select  /*+ materialize cardinality(1e4) */
    rownum  id 
    from    dual
    connect by
        rownum <= 1e4
)
select
    1e4 * (g1.id - 1) + g2.id
id,
    trunc(
        add_months(sysdate, - 120) + 
            (1e4 * (g1.id - 1) + g2.id)* 3652 / 1e7
    )                               
date_open,
    trunc(
        add_months(
            add_months(sysdate, - 120) + 
                (1e4 * (g1.id - 1) + g2.id) * 3652 / 1e7,
            12 * trunc(dbms_random.value(1,6))
        )
    )                               
date_closed,
    cast(dbms_random.string('U',1) as varchar2(1))  deal_type,
    cast(dbms_random.string('U',4) as varchar2(4))  client_ref,
    lpad(1e4 * (g1.id - 1) + g2.id,10)      small_vc,
    rpad('x',100,'x')               padding
from
    generator   g1,
    generator   g2
where
    g1.id <= 1e3
and     g2.id <= 1e4
;
execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1')
alter table t1 add constraint t1_pk primary key(id) using index nologging;

date_open ：从过去的120 个月(10 年3652 天) 开始，用于增加值的算法意味着最近的条目在当前日期。

date_closed ：是添加到date_open( 该表是记录定期投资的简单模型) 的1 到5 年( 包括5 年) 之间的整数。

deal_type ：是随机生成的单个大写字符—— 生成26 个不同的值，这些值具有相同的数据量;

client_ref ：是随机生成的一个固定长度的字符串，由4 个大写字母组成，每个组合提供大约50 万个组合和20 行。

note ：作为补充说明 - 已经生成的数据集没有使用 rownum 在任何地方的高容量选择 ; 这将使我能够使用并行执行更快地生成数据 (“level” 和 “rownum” 伪列都限制了 Oracle 使用并行执行的能力 ) 。但是在本例中，因为我希望 id 列对按到达顺序存储的按顺序生成的值进行建模，所以我是按顺序运行代码的。

规模

我的笔记本电脑上是在 Linux 5 VM 上运行了 database 12.1.0.2 ，我得到了创建数据、收集统计数据和创建索引所花费的时间如下 :

表创建:7:06.40

数据收集:0:10.54

PK 主键:0:10.94

创建索引:0:10.79 (date_open)

创建索引:0:12.17 (date_closed)

创建索引:0:13.65 (client_ref)

当然，这就要我们开始提一个很现实问题，即不同的系统可能会有不同的时间消耗结果。

虚拟机分配4 gb 的内存(1.6 gb 是留出memory_target) 和一个四核CPU 2.8 ghz 的CPU, 但可能最重要的是机器1 tb 的固态盘, 所以不会失去太多时间在物理I / O 。

数据库配置了3 个重做日志组，每个重做日志组的大小为200MB( 为了日志文件检查点和日志文件切换等待出现一些延迟) ，日志是重复的，但是实例没有在archivelog 模式下运行。

在 stats 收集之后，大多数块中的表块计数大约为 204,000 个块，每个块有 49 行， PK 索引和 client_ref 索引大约有 22,000 个叶块，两个日期索引大约有 26,500 个叶块。

Quality

当使用这样的模型来质疑它们与现实生产中有多接近时是非常重要的。到目前来看，在我所的的准备工作中，你能发现其中存在哪些问题呢？

首先，表中的Id 列太完美了，id 列在表中的顺序从小到大排列的非常有序，然而在现实当中，并发性的插入会有一点都抖动，一定范围内连续性的值可能分布在少量的块上，这可能不是很重要，重要的是我是在创建表之后插入数据才创建的索引，这意味着索引在物理上来看是没有什么问题。（每个块中有10% 的自由空间），我应该先创建一张空的表，然后在表上建立索引，在这之后再运行几个并发性的脚本使用序列进行单行插入来生成id ，但是我上次这样创建的时候，所需要的时间增加了40 倍。同样的，这可能也不是很重要，我记得在生产系统中索引的叶块中平均可用空间在任何时候都接近30% 。随着块与块之间明显的变化差异，我想时不时的通过基于叶块状态的检查，尤其是date_open 这个索引。

场景

尽管任何时间消耗都取决于机器的配置和资源的分配，并且这个模型过于简单化，但是我们任然可以从一些基本的测试当中获取一些有意思的信息。让我们从几个与业务相关的的场景开始：

a 、删除所有5 年前完成的交易

b 、删除client_ref 以“A”-“E” 开头的所有交易

c 、删除所有5 年以上的交易

a 项可能在删除前已经做了一次最基本要求的归档，也可能已经cpye 到另一张表中了。

b 项可能告诉我们，client_ref 已经(ab) 用于在第一个字母中为引用编码一些重要的分类，我们将数据分成两个处理集。

c项可能是按照date_open 对数据进行分区的过程的一部分。（虽然我不确定在这种情况下分区是不是一个好方法），在做任何对于数据库来说影响比较大的操作之前，最好看看时刻能够可视化的知道oracle 将要做什么？执行的步骤是什么，以及工作负载会出现在哪里？这些场景都是相同的吗？如果不是，他们有什么不同？如果你不知道你的数据以及你删除数据的影响，你可以从数据库中寻求答案- 举个例子：

 select
        rows_in_block,
        count(*)                                     blocks,
        rows_in_block * count(*)                     row_count,
        sum(count(*)) over (order by rows_in_block)                 running_blocks,
        sum(rows_in_block * count(*)) over (order by rows_in_block) running_rows
 from
        (
        select
                dbms_rowid.rowid_relative_fno(rowid),
                dbms_rowid.rowid_block_number(rowid),
                 count(*)                                 rows_in_block
        from
                 t1
 --
 --     where   date_open >=add_months(sysdate, -60)
 --     where   date_open <  add_months(sysdate, -60)
 --
 --     where   date_closed >=add_months(sysdate, -60)
 --     where   date_closed <  add_months(sysdate, -60)
 --
 --     where  substr(client_ref,2,1)  >= 'F'
 --     where  substr(client_ref,2,1)  < 'F'
 --
        group by
                dbms_rowid.rowid_relative_fno(rowid),
                 dbms_rowid.rowid_block_number(rowid)
        )
 group by
        rows_in_block
 order by
        rows_in_block
 ;

您将注意到，在这个查询中，我有六个注释谓词 ( 在三个互补对中 ) 。这个查询的基本目的是让我总结一下有多少块可以容纳多少行。但是每对谓词都让我对每种场景的效果有了一些想法 - 每一对中的一个告诉我关于将要删除的数据量和模式的一些信息。下面是 sql*plus

中执行如上查询的输出：

                                              Blocks            Rows
Rows per block   Blocks         Rows   Running total   Running total
-------------- -------- ------------   -------------   -------------
           27        1           27               1              27
           49  203,877    9,989,973         203,878       9,990,000
           50      200       10,000         204,078      10,000,000
               --------
sum             204,078

下面的输出显示了如果删除了5 年以上打开的数据行，留下来的数据将会是什么样子?( 也就是说，使用谓词date_open >= add_months(sysdate ， -60))

                                             Blocks          Rows
Rows per block   Blocks          Rows Running total Running total
-------------- -------- ------------- ------------- -------------
            27        1            27             1            27
            42        1            42             2            69
            49  102,014     4,998,686       102,016     4,998,755
               --------
sum             102,016

这看起来相当不错 -- 粗略的来说我们已经将表一半的块清空了，另一半没有动。如果我们现在尝试 ‘ 收缩空间 ’ ，那么我们只需要将表的下半部分复制到表的上半部分。我们会生成大量的 undo 数据和 redo 日志。但是任何索引的任何聚簇因子可能没有一点改变。另一种选择是，如果我们决定让空白空间保持原样，那么任何新数据都会非常有效地开始填充空白空间（几乎就想是重新分配区一样），同样的我们也会看到任何聚簇的因子也没有什么改变。将此结果与删除所有 5 年前关闭的行所带来的结果进行比较，（也就是说，如果我们使用谓词 date_closed>= add_months(sysdate ， -60) ，会看到什么 ? ）这个结果集会大很多。

                                             Blocks           Rows
Rows per block  Blocks           Rows Running total  Running total
------------- -------- -------------- ------------- --------------
            1        5              5             5              5
            2       22             44            27             49
            3      113            339           140            388
            4      281          1,124           421          1,512
            5      680          3,400         1,101          4,912
            6    1,256          7,536         2,357         12,448
            7    1,856         12,992         4,213         25,440
            8    2,508         20,064         6,721         45,504
            9    2,875         25,875         9,596         71,379
           10    2,961         29,610        12,557        100,989
           11    2,621         28,831        15,178        129,820
           12    2,222         26,664        17,400        156,484
           13    1,812         23,556        19,212        180,040
           14    1,550         21,700        20,762        201,740
           15    1,543         23,145        22,305        224,885
           16    1,611         25,776        23,916        250,661
           17    1,976         33,592        25,892        284,253
           18    2,168         39,024        28,060        323,277
           19    2,416         45,904        30,476        369,181
           20    2,317         46,340        32,793        415,521
           21    2,310         48,510        35,103        464,031
           22    2,080         45,760        37,183        509,791
           23    1,833         42,159        39,016        551,950
           24    1,696         40,704        40,712        592,654
           25    1,769         44,225        42,481        636,879
           26    1,799         46,774        44,280        683,653
           27    2,138         57,726        46,418        741,379
           28    2,251         63,028        48,669        804,407
           29    2,448         70,992        51,117        875,399
           30    2,339         70,170        53,456        945,569
           31    2,286         70,866        55,742      1,016,435
           32    1,864         59,648        57,606      1,076,083
           33    1,704         56,232        59,310      1,132,315
           34    1,566         53,244        60,876      1,185,559
           35   1,556         54,460        62,432      1,240,019
           36    1,850         66,600        64,282      1,306,619
           37    2,131         78,847        66,413      1,385,466
           38    2,583         98,154        68,996      1,483,620
           39    2,966        115,674        71,962      1,599,294
           40    2,891        115,640        74,853      1,714,934
           41    2,441        100,081        77,294      1,815,015
           42    1,932         81,144       79,226      1,896,159
           43    1,300         55,900        80,526      1,952,059
           44      683         30,052        81,209      1,982,111
           45      291         13,095        81,500      1,995,206
           46      107          4,922        81,607      2,000,128
           47       32          1,504        81,639      2,001,632
           48        3            144        81,642      2,001,776
           49  122,412      5,998,188       204,054      7,999,964
              --------
sum            204,054

在这种情况下，大约有 60% 的 blocks 依然每个块持有原来的 49 行，但是表中的其他块几乎没有被删除，而是被完全清空。（如果您将第一个输出中的总块数与第一个报告中的总块数进行比较，您会注意到现在肯定有几个块 (24 个块 ) 是完全空的）现在有多少块可用来插入 ? 这里有一个快速的计算，我们的大部分块有 49 行，占了 90% （ default pctree = 10 ），因此，一个块将下降到 75% 的标记 ( 即当 ASSM 将其标记为有空闲空间时 ) ，当它少于 41 行时 (49 * 75 /90) ，在 204,000 个块中，大约 75,000 个符合这个标准 ( 检查 “ 运行的块总数 ” 列 )

索引空间

上一节展示了一些简单的SQL ，让您了解了表中将如何显示空间( 或数据将如何保留)- 我们可以对索引做类似的事情吗？答案必然是肯定的。但是，回答“ 在删除匹配谓词X 的数据之后，索引会是什么样子” 这个问题的代码运行起来要比运行表的代码开销更大。首先，这里有一段简单的代码来检查索引的当前内容:

select
       rows_per_leaf, count(*) leaf_blocks
from    (
       select
               /*+ index_ffs(t1(client_ref)) */
               sys_op_lbid(94255, 'L', t1.rowid)      leaf_block,
               count(*)                               rows_per_leaf
        from
               t1
        where
               client_ref is not null
        groupby
               sys_op_lbid(94255, 'L', t1.rowid)
        )
group by
       rows_per_leaf
order by
       rows_per_leaf
;

对于‘SYS_OP_LBID() 的调用是将一个表rowid 作为它的输入之一，并返回一些类似于块的第一行的rowid 的内容，而该块的地址是索引叶块的地址，索引块持有表rowid 所提供的索引条目。另外两个参数是索引object_id( 如果索是分区的，则是分区或者是子分区) 和一个表示函数的特定用法的标志。在这个例子中是“L” 。hint 在目标索引上使用快速索引扫描是必要的，任何其他路径都可能返回错误的出结果-‘client_ref’ 不为空是必要的。以确保查询可以有效的使用index_ffs 路径。

对于我的初始化数据集，索引在每个块中都有448 个索引条目，除了一个（大概是最后一个，192 行）。即使这是简单的查询也要为了每个索引的要求而精心设计- 因为索引快速扫描需要得到正确的结果，这就是我们不得不做一些不同寻常的删除操作，看看我们大量删除会怎么影响索引。下面是一个例子，展示我们如何找出试图删除5 年多前打开的行对client_ref 索引产生什么影响。

select
       rows_per_leaf,
       count(*)                                      blocks,
       rows_per_leaf * count(*)                          row_count,
       sum(count(*)) over (order by rows_per_leaf)                 running_blocks,
       sum(rows_per_leaf * count(*)) over (order by rows_per_leaf) running_rows
from   (
       select
                /*+ leading(v1 t1) use_hash(t1)*/
                leaf_block, count(*) rows_per_leaf
       from    (
                select
                        /*+ no_mergeindex_ffs(t1(client_ref)) */
                        sys_op_lbid(94255, 'L',t1.rowid)       leaf_block,
                        t1.rowid                                rid
                from
                        t1
                where
                        client_ref is not null
                )       v1,
                t1
       where
                t1.rowid = v1.rid
        and    date_open < add_months(sysdate, -60)
       group by
                leaf_block
       )
group by
       rows_per_leaf
order by
       rows_per_leaf
;

正如您所看到的，我们从一个内联视（暗示不可合并）图开始将索引块id 附加每个表的rowid 上，然后将这组行id 连接回表- 通过rowid 连接并强制进行散列连接。我已经暗示了散列连接，因为它( 可能) 是最有效的策略，但是尽管我引入了一个leading() 提示，但我没有包含关于交换( 或不) 连接输入的提示- 我将让优化器决定这两个数据集中哪个更小，由此来更适合的构建哈希表。

在这种特殊的情况下优化器能够使用一个仅索引的访问路径来查找date_open 比五年前跟早行的所有rowid 。尽管如此( 部分原因是我的pga_aggregate_target 相对较小，散列连接溢出到( 固态) 磁盘) ，查询耗时3 分15 秒，而上一个查询在缓存整个索引时恰好运行了1.5 秒。以下是输出的摘录:

                                             Blocks           Rows
Rows_per_leaf   Blocks           Rows Running total  Running total
------------- --------  ------------- ------------- --------------
         181        2            362             3            458
         186        2            372             5            830
         187        2            374             7          1,204
         188        1            188             8          1,392
...
         210      346         72,660         2,312        474,882
         211      401         84,611         2,713        559,493
...
         221      808        178,568         8,989      1,921,410
         222      851        188,922         9,840      2,110,332
         223      832        185,536        10,672      2,295,868
...
         242      216         52,272        21,320      4,756,575
         243      173         42,039        21,493      4,798,614
         244      156         38,064        21,649      4,836,678
...
         265        1            265        22,321      5,003,718
         266        1            266        22,322      5,003,984

我们要修改 22322 个叶块 —— 这是索引中的每一个叶块 ; 我们从一个叶块中删除的行数从 1 到 266 不等。我一次从 83 行输出中选择了几行，但是您可能仍然可以看到该模式似乎遵循正态分布，以 222(50%) 为中心。

如果这样删除我们应该很清楚，我们将花费大量的精力来更新这个索引；即使这样，“每个叶块删除多少行”这个简单的数字也不能告诉我们要做的工作的全部内容。我们不知道我们是否会( 例如) 在同一时间删除所有266 个索引条目从最后一块上面显示删除完成，我们将非常随机地在索引周围跳跃式来回，并发现自己不断地重新访问该块，以便一次删除一个索引条目。因此在下一期中，我们将研究需要考虑工作负载的哪些方面，以及不同的删除策略会对工作负载产生怎样的影响。

原作者 : Jonathan Lewis

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

| 译者简介

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

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

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

相关阅读