大數據量刪除的思考（二）

發布時間：2020-08-07 20:36:42 來源：ITPUB博客閱讀：146 作者：沃趣科技欄目：關系型數據庫

在這個簡短系列的第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數據庫結構體系，現主要參與公司一體機產品安裝、測試、優化，并負責電信行業數據庫以及系統運維。

向AI問一下細節

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

大數據量刪除的思考（二）

湯健·沃趣科技數據庫技術專家

沃趣科技數據庫工程師，多年Oracle數據庫從業經驗，深入理解Oracle數據庫結構體系，現主要參與公司一體機產品安裝、測試、優化，并負責電信行業數據庫以及系統運維。

猜你喜歡

亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

大數據量刪除的思考（二）

湯健·沃趣科技數據庫技術專家

沃趣科技數據庫工程師，多年Oracle數據庫從業經驗，深入理解Oracle數據庫結構體系，現主要參與公司一體機產品安裝、測試、優化，并負責電信行業數據庫以及系統運維。

猜你喜歡

最新資訊

相關推薦

相關標簽