大數(shù)據(jù)量刪除的思考-2
在這個簡短系列的第1部分中，我提供了兩個場景的非正式描述，在這些場景中，我們可以從表中進行大規(guī)模刪除。沒有一個具體的例子,很難想象刪除數(shù)據(jù)的性質(zhì)和可用的訪問路徑會產(chǎn)生大量刪除操作對系統(tǒng)的性能影響,所以我要把大部分的時間花在本文討論的兩個測試生成的數(shù)據(jù)集。這篇文章似乎有點長但相當(dāng)多的空間會被表格占用。
創(chuàng)新互聯(lián)建站主要從事網(wǎng)站制作、做網(wǎng)站、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)博樂,10多年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18980820575
簡單的數(shù)據(jù)集
隨著硬件的能力和規(guī)模的不斷增長，我們越來越難以就“大表”或“大規(guī)模刪除”的含義達成一致——對于一個人來說，100萬行似乎很大，而對于另一個人來說，1億行似乎相當(dāng)普通。
我將使用一個折中方案，用1000萬行表示一個投資系統(tǒng)，該系統(tǒng)10年來以每年100萬行的速度增長，并且已經(jīng)達到了1.6GB的段大小。
當(dāng)然，這個表只是組成整個系統(tǒng)的幾個表中的一個，在某個時候我們會對所需要的數(shù)據(jù)擔(dān)心，但是，目前，我們只考慮這個表，只考慮表本身和表上的4個索引。
下面是生成數(shù)據(jù)集的代碼:
execute dbms_random.seed(0)
create table t1 (
idnot null,
date_open, date_closed,
deal_type,client_ref,
small_vc,padding
)
nologging
as
with generator as (
select/*+ materialize cardinality(1e4) */
rownumid
fromdual
connect by
rownum <= 1e4
)
select
1e4 * (g1.id - 1) + g2.idid,
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
generatorg1,
generatorg2
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;
create index t1_dt_open on t1(date_open) nologging;
create index t1_dt_closed on t1(date_closed) nologging;
create index t1_client on t1(client_ref) nologging;
上面看起來不是很明顯，但是代碼生成了10000萬行；
date_open：從過去的120個月(10年3652天)開始，用于增加值的算法意味著最近的條目在當(dāng)前日期。
date_closed：是添加到date_open(該表是記錄定期投資的簡單模型)的1到5年(包括5年)之間的整數(shù)。
deal_type：是隨機生成的單個大寫字符——生成26個不同的值，這些值具有相同的數(shù)據(jù)量;
client_ref：是隨機生成的一個固定長度的字符串，由4個大寫字母組成，每個組合提供大約50萬個組合和20行。
note：作為補充說明-已經(jīng)生成的數(shù)據(jù)集沒有使用rownum在任何地方的高容量選擇;這將使我能夠使用并行執(zhí)行更快地生成數(shù)據(jù)(“l(fā)evel”和“rownum”偽列都限制了Oracle使用并行執(zhí)行的能力)。但是在本例中，因為我希望id列對按到達順序存儲的按順序生成的值進行建模，所以我是按順序運行代碼的。
我的筆記本電腦上，在Linux 5 VM上運行了database 12.1.0.2，我得到了創(chuàng)建數(shù)據(jù)、收集統(tǒng)計數(shù)據(jù)和創(chuàng)建索引所花費的時間如下:
表創(chuàng)建:7:06.40 數(shù)據(jù)收集:0:10.54 PK主鍵:0:10.94 創(chuàng)建索引:0:10.79 (date_open) 創(chuàng)建索引:0:12.17 (date_closed) 創(chuàng)建索引:0:13.65 (client_ref)
當(dāng)然，這就要我們開始提一個很現(xiàn)實問題，即不同的系統(tǒng)可能會有不同的時間消耗結(jié)果。
虛擬機分配4 gb的內(nèi)存(1.6 gb是留出memory_target)和一個四核CPU 2.8 ghz 的CPU,但可能最重要的是機器1 tb的固態(tài)盤,所以不會失去太多時間在物理I / O。
數(shù)據(jù)庫配置了3個重做日志組，每個重做日志組的大小為200MB(為了日志文件檢查點和日志文件切換等待出現(xiàn)一些延遲)，日志是重復(fù)的，但是實例沒有在archivelog模式下運行。
在stats收集之后，大多數(shù)塊中的表塊計數(shù)大約為204,000個塊，每個塊有49行，PK索引和client_ref索引大約有22,000個葉塊，兩個日期索引大約有26,500個葉塊。
Quality
當(dāng)使用這樣的模型來質(zhì)疑它們與現(xiàn)實生產(chǎn)中有多接近時是非常重要的。到目前來看，在我所的的準(zhǔn)備工作中，你能發(fā)現(xiàn)其中存在哪些問題呢？
首先，表中的Id列太完美了，id列在表中的順序從小到大排列的非常有序，然而在現(xiàn)實當(dāng)中，并發(fā)性的插入會有一點都抖動，一定范圍內(nèi)連續(xù)性的值可能分布在少量的塊上，這可能不是很重要，重要的是我是在創(chuàng)建表之后插入數(shù)據(jù)才創(chuàng)建的索引，這意味著索引在物理上來看是沒有什么問題。（每個塊中有10%的自由空間），我應(yīng)該先創(chuàng)建一張空的表，然后在表上建立索引，在這之后再運行幾個并發(fā)性的腳本使用序列進行單行插入來生成id，但是我上次這樣創(chuàng)建的時候，所需要的時間增加了40倍。同樣的，這可能也不是很重要，我記得在生產(chǎn)系統(tǒng)中索引的葉塊中平均可用空間在任何時候都接近30%。 隨著塊與塊之間明顯的變化差異，我想時不時的通過基于葉塊狀態(tài)的檢查，尤其是date_open這個索引。
Scenarios（場景）
盡管任何時間消耗都取決于機器的配置和資源的分配，并且這個模型過于簡單化，但是我們?nèi)稳豢梢詮囊恍┗镜臏y試當(dāng)中獲取一些有意思的信息。讓我們從幾個與業(yè)務(wù)相關(guān)的的場景開始：
a、刪除所有5年前完成的交易 b、刪除client_ref以“A”-“E”開頭的所有交易 c、刪除所有5年以上的交易
A 項可能在刪除前已經(jīng)做了一次最基本要求的歸檔，也可能已經(jīng)cpye 到另一張表中了。
B 項可能告訴我們，client_ref已經(jīng)(ab)用于在第一個字母中為引用編碼一些重要的分類，我們將數(shù)據(jù)分成兩個處理集
C 項可能是按照date_open 對數(shù)據(jù)進行分區(qū)的過程的一部分。（雖然我不確定在這種情況下分區(qū)是不是一個好方法），在做任何對于數(shù)據(jù)庫來說影響比較大的操作之前，最好看看時刻能夠可視化的知道oracle將要做什么？執(zhí)行的步驟是什么，以及工作負(fù)載會出現(xiàn)在哪里？這些場景都是相同的嗎？如果不是，他們有什么不同？如果你不知道你的數(shù)據(jù)以及你刪除數(shù)據(jù)的影響，你可以從數(shù)據(jù)庫中尋求答案-舉個例子：
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 ;
您將注意到，在這個查詢中，我有六個注釋謂詞(在三個互補對中)。這個查詢的基本目的是讓我總結(jié)一下有多少塊可以容納多少行。但是每對謂詞都讓我對每種場景的效果有了一些想法-每一對中的一個告訴我關(guān)于將要刪除的數(shù)據(jù)量和模式的一些信息。下面是sql*plus中執(zhí)行如上查詢的輸出：
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年以上打開的數(shù)據(jù)行，留下來的數(shù)據(jù)將會是什么樣子?(也就是說，使用謂詞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
這相當(dāng)不錯--粗略的來說我們已經(jīng)將表一半的塊清空了，另一半沒有動。如果我們現(xiàn)在嘗試‘收縮空間’，那么我們只需要將表的下半部分復(fù)制到表的上半部分。我們會生成大量的undo數(shù)據(jù)和redo日志。但是任何索引的任何聚簇因子可能沒有一點改變。另一種選擇是，如果我們決定讓空白空間保持原樣，那么任何新數(shù)據(jù)都會非常有效地開始填充空白空間（幾乎就想是重新分配區(qū)一樣），同樣的我們也會看到任何聚簇的因子也沒有什么改變。將此結(jié)果與刪除所有5年前關(guān)閉的行所帶來的結(jié)果進行比較，（也就是說，如果我們使用謂詞date_closed >= add_months(sysdate， -60)，會看到什么?）這個結(jié)果集.會大很多。
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行，但是表中的其他塊幾乎沒有被刪除，而是被完全清空。（如果您將第一個輸出中的總塊數(shù)與第一個報告中的總塊數(shù)進行比較，您會注意到現(xiàn)在肯定有幾個塊(24個塊)是完全空的）現(xiàn)在有多少塊可用來插入?這里有一個快速的計算，我們的大部分塊有49行，占了90%（default pctree = 10），因此，一個塊將下降到75%的標(biāo)記(即當(dāng)ASSM將其標(biāo)記為有空閑空間時)，當(dāng)它少于41行時(49 * 75 / 90)，在204,000個塊中，大約75,000個符合這個標(biāo)準(zhǔn)(檢查“運行的塊總數(shù)”列)
索引空間
上一節(jié)展示了一些簡單的SQL，讓您了解了表中將如何顯示空間(或數(shù)據(jù)將如何保留)-我們可以對索引做類似的事情嗎？答案必然是肯定的。但是，回答“在刪除匹配謂詞X的數(shù)據(jù)之后，索引會是什么樣子”這個問題的代碼運行起來要比運行表的代碼開銷更大。首先，這里有一段簡單的代碼來檢查索引的當(dāng)前內(nèi)容:
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 group by sys_op_lbid(94255, 'L', t1.rowid) ) group by rows_per_leaf order by rows_per_leaf ;
對于‘SYS_OP_LBID()’的調(diào)用將一個表rowid作為它的如數(shù)之一，并返回一些類似于塊的第一行的rowid的內(nèi)容，而該塊的地址是索引葉塊的地址，索引你塊持有表rowid所提供的索引條目。另外兩個參數(shù)是索引object_id(如果索是分區(qū)的，則是分區(qū)或者是子分區(qū))和一個表示函數(shù)的特定用法的標(biāo)志。在這個例子中是“L”。hint在目標(biāo)索引上使用快速索引掃描是必要的-任何其他路徑都可能返回錯誤的出結(jié)果-‘client_ref’不為空是必要的。以確保查詢可以有效的使用index_ffs路徑。
對于我的初始化數(shù)據(jù)集，索引在每個塊中都有448個索引條目，除了一個（大概是最后一個，192行）。即使這是簡單的查詢也要為了每個索引的要求而精心設(shè)計-因為索引快速掃描需要得到正確的結(jié)果，這就是我們不得不做一些不同尋常的刪除操作，看看我們大量刪除會怎么影響索引。下面是一個例子，展示我們?nèi)绾握页鲈噲D刪除5年多前打開的行對client_ref索引產(chǎn)生什么影響。
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_merge index_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 ;
正如您所看到的，我們從一個內(nèi)聯(lián)視（按時不可合并）圖開始將索引塊id附加每個表的rowid上，然后將這組行id連接回表-通過rowid連接并強制進行散列連接。我已經(jīng)暗示了散列連接，因為它(可能)是最有效的策略，但是盡管我引入了一個leading()提示，但我沒有包含關(guān)于交換(或不)連接輸入的提示-我將讓優(yōu)化器決定這兩個數(shù)據(jù)集中哪個更小，由此來更適合的構(gòu)建哈希表。
在這種特殊的情況下優(yōu)化器能夠使用一個僅索引的訪問路徑來查找date_open 比五年前跟早行的所有rowid。盡管如此(部分原因是我的pga_aggregate_target相對較小，散列連接溢出到(固態(tài))磁盤)，查詢耗時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個葉塊——這是索引中的每一個葉塊;我們從一個葉塊中刪除的行數(shù)從1到266不等。我一次從83行輸出中選擇了幾行，但是您可能仍然可以看到該模式似乎遵循正態(tài)分布，以222(50%)為中心。
如果這樣刪除我們應(yīng)該很清楚，我們將花費大量的精力來更新這個索引；即使這樣，“每個葉塊刪除多少行”這個簡單的數(shù)字也不能告訴我們要做的工作的全部內(nèi)容。我們不知道我們是否會(例如)在同一時間刪除所有266個索引條目從最后一塊上面顯示刪除完成，我們將非常隨機地在索引周圍跳躍式來回，并發(fā)現(xiàn)自己不斷地重新訪問該塊，以便一次刪除一個索引條目。因此在下一期中，我們將研究需要考慮工作負(fù)載的哪些方面，以及不同的刪除策略如何對工作負(fù)載產(chǎn)生重大影響。
譯者: 湯建 原作者: Jonathan Lewis 原文地址：https://www.red-gate.com/simple-talk/sql/oracle/massive-deletes-part-2/
網(wǎng)頁標(biāo)題：大數(shù)據(jù)量刪除的思考-2
當(dāng)前鏈接：http://muchs.cn/article22/igescc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供電子商務(wù)、App開發(fā)、移動網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計公司、網(wǎng)站內(nèi)鏈、用戶體驗
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 成都做網(wǎng)站:"價值推廣五策略"! 2017-01-01
- 做網(wǎng)站設(shè)計中搜索框的幾種設(shè)計方法 2021-05-22
- 做網(wǎng)站建設(shè)應(yīng)如何設(shè)置圖片內(nèi)容? 2023-04-17
- 高端定制網(wǎng)站貴在哪里 2016-10-11
- 做網(wǎng)站SEO優(yōu)化需不需要原創(chuàng)文章？ 2013-08-14
- 做網(wǎng)站去哪里 目前常見的方式介紹 2021-05-01
- 做網(wǎng)站的關(guān)鍵點在于體驗方面 2022-08-18
- 鄂爾多斯公司網(wǎng)站建設(shè)：做網(wǎng)站時有哪些特別重要的點？ 2021-10-15
- 創(chuàng)新互聯(lián)給你年前做網(wǎng)站和營銷推廣的8個理由 2013-05-05
- SEO做網(wǎng)站優(yōu)化的最忌諱的幾點都在這 2021-09-12
- 做網(wǎng)站公司是怎么做網(wǎng)站的 2016-11-04
- 濰坊網(wǎng)站排名做網(wǎng)站優(yōu)化要注意的方面(SEO優(yōu)化的技巧) 2023-01-27