大數(shù)據(jù)量刪除的思考 - 1

    在最近一篇關(guān)于從表中刪除列的文章里，我留下了一個(gè)懸而未決的問(wèn)題，刪除列之后你應(yīng)該/可能會(huì)做什么？因?yàn)閯h除列只不過(guò)是“大量刪除”的特殊情況。在這篇文章中，我計(jì)劃為我們?nèi)绾嗡伎肌按罅縿h除”及產(chǎn)生的后果打下一個(gè)基礎(chǔ)。

概述

    在您能夠找到大量刪除的方案和流程之前，您必須處理好一些戰(zhàn)略性（長(zhǎng)期）和"戰(zhàn).術(shù)"性（短期）問(wèn)題。

    在戰(zhàn)略層面您會(huì)有這樣的問(wèn)題：您為什么要?jiǎng)h除？您希望從中得到什么？如果您達(dá)到了初始的目標(biāo)，接下來(lái)的策略（如果有）是什么？您有什么樣的證據(jù)能夠證明它值得你付出努力（人和機(jī)器）？您有沒(méi)有仔細(xì)想過(guò)即使修復(fù)了舊的問(wèn)題也可能帶來(lái)新的問(wèn)題？

    在"戰(zhàn).術(shù)"層面您可能會(huì)問(wèn)決定采用的工作流程的一些細(xì)節(jié)問(wèn)題：有哪些資源？您是否允許長(zhǎng)時(shí)間中斷服務(wù)或者短時(shí)間的中斷服務(wù)？在或者根本不允許中斷任何服務(wù)？如果應(yīng)用層序必須在刪除任務(wù)執(zhí)行階段運(yùn)行，那么,它是否可以減少部分功能或者降低一下執(zhí)行性能？您對(duì)您的系統(tǒng)是否足夠了解呢？您是否查看過(guò)Oracle最近有哪些特性或者增強(qiáng)可用幫助您安全（和快速的）完成工作？

    讓我們看幾個(gè)我最近參與的幾次在線交談的一些想法：

設(shè)想A

    在OTN論壇中最近有一個(gè)貼子描述了“大量刪除”的一個(gè)極端例子，用戶有一個(gè)4tb的普通堆表，其中保留了3年數(shù)據(jù)，現(xiàn)在想將數(shù)據(jù)減少到每天分區(qū)并保留15天歷史數(shù)據(jù)?？赡艽偈谷藗兇罅縿h除數(shù)據(jù)是為了清理大量的歷史數(shù)據(jù)，當(dāng)然，最好的策略是以這樣的目標(biāo)設(shè)計(jì)系統(tǒng)，將刪除數(shù)據(jù)變成簡(jiǎn)單的“刪除分區(qū)”，這樣可以做到幾乎沒(méi)有開(kāi)銷。

    在這個(gè)特殊的例子中，用戶（在我看來(lái)）是非常幸運(yùn)的，因?yàn)樗麄兿肭宄蟛糠謹(jǐn)?shù)據(jù)并且只保留一小部分?jǐn)?shù)據(jù)。他們需要花費(fèi)一些時(shí)間去計(jì)劃和測(cè)試所有相關(guān)細(xì)節(jié)（參照完整性和索引等），但是所有的這些都需要?jiǎng)?chuàng)建一個(gè)合適的范圍分區(qū)表，將此表作為交換后的表，然后每天開(kāi)始進(jìn)行分區(qū)，之后等待16天，在刪除最后的分區(qū)以清除最近三年的數(shù)據(jù)。

    另外一些人可能沒(méi)有那么幸運(yùn)，我常?？吹筋愃埔粡埍碇杏袔啄甑臄?shù)據(jù)，而且需要按照周或者月進(jìn)行分區(qū)，然后保留兩年或者三年的數(shù)據(jù)，“交換一次等待三年”的方式并不可取，但是刪除幾年或者復(fù)制幾年數(shù)據(jù)帶來(lái)的開(kāi)銷同樣是不可取的。

設(shè)想B

    不久之前我收到的一個(gè)問(wèn)題是某人來(lái)詢問(wèn)關(guān)于大量數(shù)據(jù)刪除的策略，因?yàn)楦鶕?jù)他們之前經(jīng)驗(yàn)，快速刪除大量數(shù)據(jù)前先刪除全部索引，并在之后重建索引，最近他們測(cè)試一個(gè)案例，盡管這種方法和“僅僅刪除它”的時(shí)間差異非常小，但似乎采用稍微復(fù)雜(刪除索引在重建/因此有風(fēng)險(xiǎn))的方式并沒(méi)有很大好處。

    這就提出了一個(gè)有趣的問(wèn)題：多大的數(shù)據(jù)量刪除才算是“大量數(shù)據(jù)”？這個(gè)人刪除了2500w行數(shù)據(jù)，這聽(tīng)起來(lái)相當(dāng)大，但是它僅僅是表中的4%，所以它并不是那么的龐大(相對(duì)而言);此外表已經(jīng)被分區(qū)，這就降低了幾分風(fēng)險(xiǎn)，另外一方面，它至少包含一個(gè)全局唯一索引，這就有點(diǎn)讓他討厭了，然而這臺(tái)服務(wù)器可以將該任務(wù)并行加到16，因此在絕對(duì)值上來(lái)說(shuō)，每個(gè)并行任務(wù)約為150w行數(shù)據(jù)，所以可能它并不是真的很大。

    事實(shí)上，無(wú)論采用什么方法，完成任務(wù)的時(shí)間大約為17分30秒，但值得注意的是，如果我們用簡(jiǎn)單的刪除策略，在任務(wù)期間其他用戶仍然可以使用該表，由于并發(fā)使用該表，刪除操作可能需要更長(zhǎng)時(shí)間，由于爭(zhēng)用和讀一致性，要求用戶活動(dòng)可能會(huì)更慢(注：按照特定的順序一次刪除一個(gè)分區(qū)有什么好處么？)，并且始終存在鎖和死鎖威脅而導(dǎo)致的災(zāi)難，刪除這4%的數(shù)據(jù)大概要多久一次，可能它的數(shù)據(jù)量大致相當(dāng)于兩年內(nèi)中的一個(gè)月的數(shù)據(jù)，所以可能每個(gè)月定期清理一次，但可能不會(huì)有人介意因?yàn)?quot;drop/delete/rebuild"失去訪問(wèn)權(quán)限15分鐘，這些操作總是有一些好處的，大多數(shù)的索引在刪除數(shù)據(jù)之后可以更加高效的運(yùn)行。

注意事項(xiàng)

    當(dāng)"大數(shù)據(jù)量刪除"浮現(xiàn)在你的腦海中時(shí)，我希望這兩個(gè)例子可以讓你知道需要考慮些什么？因此，在我們開(kāi)始"怎樣"之前，先讓我們來(lái)對(duì)可能出現(xiàn)的情況和與之相關(guān)的想法進(jìn)行分類。

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

刪除原因非常簡(jiǎn)單：

    1.提升性能。

    2.回收空間 - 希望可能是數(shù)據(jù)庫(kù)或者特定表空間的空間;它最終可能是數(shù)據(jù)庫(kù)之外的磁盤空間。

常見(jiàn)刪除模式有：

    1.根據(jù)時(shí)間來(lái)對(duì)表中的數(shù)據(jù)進(jìn)行刪除。

    2.根據(jù)表中數(shù)據(jù)處理完成時(shí)間來(lái)進(jìn)行刪除。

    3.從表中刪除一類數(shù)據(jù)(這可能意味著我們要?jiǎng)?chuàng)建兩張表，或者分區(qū)表(列表分區(qū))，或許非分區(qū)表)。

    一旦我們找出原因，我們就會(huì)提出一些關(guān)鍵問(wèn)題--如何刪除數(shù)據(jù)才能提高性能？我們?nèi)绾瓮ㄟ^(guò)其他的方式來(lái)提高效率(例如改進(jìn)索引)？通過(guò)刪除數(shù)據(jù)釋放的空間是否可以立即使用，或者還必須做些其他操作？刪除的帶來(lái)的負(fù)面影響是什么？我們可能采取的進(jìn)一步措施帶來(lái)的負(fù)面影響又是什么？我們是否有真實(shí)的平臺(tái)？我們可以對(duì)預(yù)測(cè)的停機(jī)時(shí)間進(jìn)行驗(yàn)證，執(zhí)行相應(yīng)的任務(wù)，測(cè)試不可以預(yù)測(cè)的負(fù)面影響有哪些？

    理解模式非常重要，但在使用數(shù)據(jù)庫(kù)時(shí)卻經(jīng)常被忽略。當(dāng)你刪除數(shù)據(jù)時(shí)，在表塊中和索引塊中釋放出相應(yīng)的空間，當(dāng)新數(shù)據(jù)出現(xiàn)時(shí)可能會(huì)重新使用該空間。但由于這種方式表中釋放的空閑空間意味著新數(shù)據(jù)的物理分布與當(dāng)前其他數(shù)據(jù)所遵循的分布模式不同，這意味著隨著時(shí)間的推移，因?yàn)槟Ｊ降牟煌樵?a)可能變得非常低效，優(yōu)化器(b)可能認(rèn)定某個(gè)索引不在是最好的選擇，因?yàn)閿?shù)據(jù)分布模式的改變導(dǎo)致索引的"clustering_factor"出現(xiàn)了變化。

    我提出的三種主要的刪除模式，是基于他們對(duì)性能的威脅程度。如果假設(shè)你是第一次進(jìn)行大數(shù)據(jù)刪除，那么最容易考慮這些模式。有些時(shí)候，只有你進(jìn)行了幾次刪除周期后威脅才會(huì)出現(xiàn)。如果按照數(shù)據(jù)的原始到達(dá)日期刪除，很可能會(huì)在表段的開(kāi)頭(前幾個(gè)區(qū))留下很多的空閑塊，這就意味著新插入的數(shù)據(jù)可能會(huì)插入到表段開(kāi)頭的一組區(qū)中，而不是表段的末尾。具體來(lái)說(shuō)，假設(shè)有一個(gè)包含100000個(gè)塊的表，你剛剛刪除該表中前5000個(gè)塊中的數(shù)據(jù)，接下來(lái)插入的幾十萬(wàn)行數(shù)據(jù)將插入到1-5000的塊中，而不是100001-105000；盡管表中的絕對(duì)位置已改變，但數(shù)據(jù)的模式不會(huì)改變。

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

    在最后一種模式中 - 刪除整個(gè)數(shù)據(jù)類別，"借貸"可能是很好的一個(gè)例子。出于某些原因我們可能決定為5年貸款創(chuàng)建一張單獨(dú)的表，因?yàn)橘J款已經(jīng)成為業(yè)務(wù)的重要部分 - 所以我們必須從當(dāng)前的貸款表中刪除他們。當(dāng)然，這種就是剛剛刪除表中每個(gè)塊10%-30%數(shù)據(jù)的模式。我們可能發(fā)現(xiàn)這些塊均沒(méi)有出現(xiàn)在空閑空間中，或者我們發(fā)現(xiàn)在接下來(lái)的九個(gè)月里，我們?cè)诒淼拿總€(gè)塊中插入了少數(shù)幾行數(shù)據(jù)，而人們會(huì)抱怨“2016年的性能非常的差”。

索引

    當(dāng)然，我們?cè)谘芯繑?shù)據(jù)模式時(shí)還應(yīng)該考慮索引中的模式(和副作用)。因?yàn)槲覀儚纳贁?shù)相鄰塊中刪除所有行，那即使其中的一個(gè)場(chǎng)景也意味著我們可以高效的從表中刪除數(shù)據(jù)，我們還需要考慮表中每個(gè)索引都會(huì)發(fā)生什么事情。非常緊湊的表刪除可能導(dǎo)致非常分散的索引刪除，因?yàn)殡S機(jī)I/O - 讀(通過(guò)會(huì)話)和寫(xiě)(數(shù)據(jù)庫(kù)寫(xiě)入)，可能需要很長(zhǎng)的時(shí)間，可能不會(huì)給我們?nèi)魏魏罄m(xù)空間和性能好處。

    考慮從"股票價(jià)格"表中刪除2001年4月1日的數(shù)據(jù)：所有的行都將一起到達(dá)，所以我們可以清空表中連續(xù)的幾百個(gè)塊 - 如果我們有一個(gè)索引(報(bào)價(jià)_日期，股票_代碼)，我們將清空索引中的幾百個(gè)連續(xù)的塊，如果這是我們驅(qū)動(dòng)刪除的索引，則不會(huì)產(chǎn)生過(guò)多的I/O；如果我們有一個(gè)索引(股票_代碼，報(bào)價(jià)_日期) - 我們很可能會(huì)不得不訪問(wèn)幾千個(gè)索引葉塊來(lái)刪除每個(gè)索引條目！因?yàn)橐獔?zhí)行大量的隨機(jī)I/O，刪除可能非常緩慢。OTN中關(guān)于插入和刪除最常見(jiàn)的抱怨之一就是"db file sequential read"等待；執(zhí)行計(jì)劃中不會(huì)告訴我們關(guān)于索引維護(hù)的開(kāi)銷，所以很容易忘記一個(gè)大的刪除操作會(huì)導(dǎo)致非常緩慢的隨機(jī)I/O。(有趣的是SQL Server會(huì)告訴你刪除操作會(huì)維護(hù)哪些索引)。

    索引維護(hù)對(duì)于大的刪除操作影響如此之大 - 而且會(huì)產(chǎn)生持久的后果 - 這一點(diǎn)確實(shí)值得我們思考。實(shí)際上，我們可以設(shè)計(jì)一種策略，根據(jù)每個(gè)索引的定義和實(shí)際使用情況，對(duì)單個(gè)表上的索引進(jìn)行不同的處理。對(duì)于給定的表，我們可以刪除(或者標(biāo)記不可以)和重建一些索引，與此同時(shí)保留一部分索引，在刪除后進(jìn)行重建索引或者合并索引。

總結(jié)

    大的刪除操作并不是一個(gè)平常的操作，不應(yīng)該在沒(méi)有經(jīng)過(guò)深思熟慮的情況下進(jìn)行。我們?yōu)槭裁匆獎(jiǎng)h除大量的數(shù)據(jù)？我們是否知道在刪除操作完成后我們又需要如何驗(yàn)證我們是否到達(dá)最終目標(biāo)？我們可以使用哪些方法來(lái)最小化的執(zhí)行刪除所帶來(lái)的影響以及刪除完成之后我們還需要做什么才能達(dá)到最終的目標(biāo)？

    何種效率及工作量將由我們要?jiǎng)h除的數(shù)據(jù)模式?jīng)Q定，首先在表中，然后在索引(可能更重要的是索引)中。

    在下一期中我們將討論"大規(guī)模"刪除的一些技術(shù)問(wèn)題

譯者: 楊禹航
原作者: Jonathan Lewis
原文地址：https://www.red-gate.com/simple-talk/sql/oracle/massive-deletes-part-1/