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

在最近一篇關於從表中刪除列的文章裡，我留下了一個懸而未決的問題，刪除列之後你應該/可能會做什麼？因為刪除列只不過是“大量刪除”的特殊情況。在這篇文章中，我計劃為我們如何思考“大量刪除”及產生的後果打下一個基礎。

概    述

在您能夠找到大量刪除的方案和流程之前，您必須處理好一些戰略性（長期）和策略性（短期）問題。

在戰略層面您會有這樣的問題：您為什麼要刪除？您希望從中得到什麼？如果您達到了初始的目標，接下來的策略（如果有）是什麼？您有什麼樣的證據能夠證明它值得你付出努力（人和機器）？您有沒有仔細想過即使修復了舊的問題也可能帶來新的問題？

在策略層面您可能會問決定採用的工作流程的一些細節問題：有哪些資源？您是否允許長時間中斷服務或者短時間的中斷服務？在或者根本不允許中斷任何服務？如果應用層序必須在刪除任務執行階段執行，那麼,它是否可以減少部分功能或者降低一下執行效能？您對您的系統是否足夠了解呢？您是否檢視過Oracle最近有哪些特性或者增強可用幫助您安全（和快速的）完成工作？

讓我們看幾個我最近參與的幾次線上交談的一些想法：

設想 A

在OTN論壇中最近有一個貼子描述了“大量刪除”的一個極端例子，使用者有一個4tb的普通堆表，其中保留了3年資料，現在想將資料減少到每天分割槽並保留15天曆史資料。可能促使人們大量刪除資料是為了清理大量的歷史資料，當然，最好的策略是以這樣的目標設計系統，將刪除資料變成簡單的“刪除分割槽”，這樣可以做到幾乎沒有開銷。

在這個特殊的例子中，使用者（在我看來）是非常幸運的，因為他們想清除大部分資料並且只保留一小部分資料。他們需要花費一些時間去計劃和測試所有相關細節（參照完整性和索引等），但是所有的這些都需要建立一個合適的範圍分割槽表，將此表作為交換後的表，然後每天開始進行分割槽，之後等待16天，在刪除最後的分割槽以清除最近三年的資料。

另外一些人可能沒有那麼幸運，我常常看到類似一張表中有幾年的資料，而且需要按照周或者月進行分割槽，然後保留兩年或者三年的資料，“交換一次等待三年”的方式並不可取，但是刪除幾年或者複製幾年資料帶來的開銷同樣是不可取的。

設想 B

不久之前我收到的一個問題是某人來詢問關於大量資料刪除的策略，因為根據他們之前經驗，快速刪除大量資料前先刪除全部索引，並在之後重建索引，最近他們測試一個案例，儘管這種方法和“僅僅刪除它”的時間差異非常小，但似乎採用稍微複雜(刪除索引在重建/因此有風險)的方式並沒有很大好處。

這就提出了一個有趣的問題：多大的資料量刪除才算是“大量資料”？這個人刪除了2500w行資料，這聽起來相當大，但是它僅僅是表中的4%，所以它並不是那麼的龐大(相對而言);此外表已經被分割槽，這就降低了幾分風險，另外一方面，它至少包含一個全域性唯一索引，這就有點讓他討厭了，然而這臺伺服器可以將該任務並行加到16，因此在絕對值上來說，每個並行任務約為150w行資料，所以可能它並不是真的很大。 

事實上，無論採用什麼方法，完成任務的時間大約為17分30秒，但值得注意的是，如果我們用簡單的刪除策略，在任務期間其他使用者仍然可以使用該表，由於併發使用該表，刪除操作可能需要更長時間，由於爭用和讀一致性，要求使用者活動可能會更慢(注：按照特定的順序一次刪除一個分割槽有什麼好處麼？)，並且始終存在鎖和死鎖威脅而導致的災難，刪除這4%的資料大概要多久一次，可能它的資料量大致相當於兩年內中的一個月的資料，所以可能每個月定期清理一次，但可能不會有人介意因為"drop/delete/rebuild"失去訪問許可權15分鐘，這些操作總是有一些好處的，大多數的索引在刪除資料之後可以更加高效的執行。

注意事項

當"大資料量刪除"浮現在你的腦海中時，我希望這兩個例子可以讓你知道需要考慮些什麼？因此，在我們開始"怎樣"之前，先讓我們來對可能出現的情況和與之相關的想法進行分類。 

我想我過去遇到過三種基本刪除模式和兩種刪除原因。

刪除原因非常簡單：

1.提升效能。

2.回收空間 - 希望可能是資料庫或者特定表空間的空間;它最終可能是資料庫之外的磁碟空間。

常見刪除模式有：

1.根據時間來對錶中的資料進行刪除。

2.根據表中資料處理完成時間來進行刪除。

3.從表中刪除一類資料(這可能意味著我們要建立兩張表，或者分割槽表(列表分割槽)，或許非分割槽表)。 

一旦我們找出原因，我們就會提出一些關鍵問題--如何刪除資料才能提高效能？我們如何通過其他的方式來提高效率(例如改進索引)？通過刪除資料釋放的空間是否可以立即使用，或者還必須做些其他操作？刪除的帶來的負面影響是什麼？我們可能採取的進一步措施帶來的負面影響又是什麼？我們是否有真實的平臺？我們可以對預測的停機時間進行驗證，執行相應的任務，測試不可以預測的負面影響有哪些？ 

理解模式非常重要，但在使用資料庫時卻經常被忽略。當你刪除資料時，在表塊中和索引塊中釋放出相應的空間，當新資料出現時可能會重新使用該空間。但由於這種方式表中釋放的空閒空間意味著新資料的物理分佈與當前其他資料所遵循的分佈模式不同，這意味著隨著時間的推移，因為模式的不同查詢(a)可能變得非常低效，優化器(b)可能認定某個索引不在是最好的選擇，因為資料分佈模式的改變導致索引的"clustering_factor"出現了變化。 

我提出的三種主要的刪除模式，是基於他們對效能的威脅程度。如果假設你是第一次進行大資料刪除，那麼最容易考慮這些模式。有些時候，只有你進行了幾次刪除週期後威脅才會出現。如果按照資料的原始到達日期刪除，很可能會在表段的開頭(前幾個區)留下很多的空閒塊，這就意味著新插入的資料可能會插入到表段開頭的一組區中，而不是表段的末尾。具體來說，假設有一個包含100000個塊的表，你剛剛刪除該表中前5000個塊中的資料，接下來插入的幾十萬行資料將插入到1-5000的塊中，而不是100001-105000；儘管表中的絕對位置已改變，但資料的模式不會改變。 

如果是根據"處理完成"日期進行刪除，那麼初始刪除模式可能有所不同 - 也許前1000個資料塊實際上是空的，接下來1000個塊的使用量下降到20%，在接下來2000個塊使用量下降到40%，在接下來4000個塊使用量下降到70%。隨著時間的推移，新的資料將分佈在比以往更多的資料塊中(也許你刪除的塊中有一些不允許被重用直到你進行下一次大量的刪除操作)。如果不參考實際應用，很難想象當大量刪除發生時，為什麼任何人的資料可能顯示這種"衰減"模式 - 但你可能會想到一個應用獲得了1、2、3或者5年的借貸協議。

在最後一種模式中 - 刪除整個資料類別，"借貸"可能是很好的一個例子。出於某些原因我們可能決定為5年貸款建立一張單獨的表，因為貸款已經成為業務的重要部分 - 所以我們必須從當前的貸款表中刪除他們。當然，這種就是剛剛刪除表中每個塊10%-30%資料的模式。我們可能發現這些塊均沒有出現在空閒空間中，或者我們發現在接下來的九個月裡，我們在表的每個塊中插入了少數幾行資料，而人們會抱怨“2016年的效能非常的差”。

索    引

當然，我們在研究資料模式時還應該考慮索引中的模式(和副作用)。因為我們從少數相鄰塊中刪除所有行，那即使其中的一個場景也意味著我們可以高效的從表中刪除資料，我們還需要考慮表中每個索引都會發生什麼事情。非常緊湊的表刪除可能導致非常分散的索引刪除，因為隨機I/O - 讀(通過會話)和寫(資料庫寫入)，可能需要很長的時間，可能不會給我們任何後續空間和效能好處。

考慮從"股票價格"表中刪除2001年4月1日的資料：所有的行都將一起到達，所以我們可以清空表中連續的幾百個塊 - 如果我們有一個索引(報價_日期，股票_程式碼)，我們將清空索引中的幾百個連續的塊，如果這是我們驅動刪除的索引，則不會產生過多的I/O；如果我們有一個索引(股票_程式碼，報價_日期) - 我們很可能會不得不訪問幾千個索引葉塊來刪除每個索引條目！因為要執行大量的隨機I/O，刪除可能非常緩慢。OTN中關於插入和刪除最常見的抱怨之一就是"db file sequential read"等待；執行計劃中不會告訴我們關於索引維護的開銷，所以很容易忘記一個大的刪除操作會導致非常緩慢的隨機I/O。(有趣的是SQL Server會告訴你刪除操作會維護哪些索引)。

索引維護對於大的刪除操作影響如此之大 - 而且會產生持久的後果 - 這一點確實值得我們思考。實際上，我們可以設計一種策略，根據每個索引的定義和實際使用情況，對單個表上的索引進行不同的處理。對於給定的表，我們可以刪除(或者標記不可以)和重建一些索引，與此同時保留一部分索引，在刪除後進行重建索引或者合併索引。

總    結

大的刪除操作並不是一個平常的操作，不應該在沒有經過深思熟慮的情況下進行。我們為什麼要刪除大量的資料？我們是否知道在刪除操作完成後我們又需要如何驗證我們是否到達最終目標？我們可以使用哪些方法來最小化的執行刪除所帶來的影響以及刪除完成之後我們還需要做什麼才能達到最終的目標？

何種效率及工作量將由我們要刪除的資料模式決定，首先在表中，然後在索引(可能更重要的是索引)中。

在下一期中我們將討論"大規模"刪除的一些技術問題

原作者: Jonathan Lewis

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

| 譯者簡介

楊禹航·沃趣科技高階資料庫技術專家

熟悉Oracle資料庫內部機制，豐富的資料庫及RAC叢集層故障診斷、效能調優、OWI、資料庫備份恢復及遷移經驗。

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