大資料量刪除的思考（二）

沃趣科技發表於2019-10-07
大資料
在這個簡短系列的第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

原文地址：


| 譯者簡介

湯健·沃趣科技資料庫技術專家

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


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2658982/，如需轉載，請註明出處，否則將追究法律責任。

相關文章