大數(shù)據(jù)量刪除的思考（四）

在本系列的前一期文章中，我制作了一些圖，突出顯示了按表掃描執(zhí)行大量刪除操作和按索 引范圍掃描執(zhí)行大量刪除之間的主要區(qū)別。 根據(jù)所涉及的數(shù)據(jù)模式，選擇正確的策略可能對(duì)隨機(jī)I/Os 的數(shù)量、生成的undo的數(shù)量和排序所需的CPU數(shù)量產(chǎn)生顯著影響——所有這些都可能影響執(zhí)行刪除所需的時(shí)間。

然而，這個(gè)簡(jiǎn)單的演示跟生產(chǎn)環(huán)境當(dāng)中相比，生產(chǎn)環(huán)境當(dāng)中這個(gè)情況更為復(fù)雜。所以，如果你面臨著一項(xiàng)艱巨的任務(wù)，你需要仔細(xì)考慮如何對(duì)真正代表你要處理的系統(tǒng)的東西進(jìn)行建模。實(shí)際上有兩種不同的情況，這一點(diǎn)很重要。

* 當(dāng)你在處理一個(gè)非常大的一次性任務(wù)時(shí)，你需要在第一時(shí)間就把它做好，一些關(guān)鍵性的特殊情況不要發(fā)現(xiàn)的太遲——尤其是如果你不允許把生產(chǎn)系統(tǒng)離線來(lái)完成這個(gè)任務(wù)任務(wù)，而且你的工作期限很緊的話。

* 當(dāng)你有一份常規(guī)的、但不經(jīng)常發(fā)生的、非常大的工作時(shí)，有必要了解一下哪些看起來(lái)不相關(guān)的小操作可能對(duì)運(yùn)行時(shí)產(chǎn)生很大影響;而且，了解下一次升級(jí)可能會(huì)出現(xiàn)什么問(wèn)題是值得的，這樣您就可以預(yù)先解決任何問(wèn)題。

當(dāng)然，后者的一個(gè)簡(jiǎn)單例子是我對(duì)12c的簡(jiǎn)短評(píng)論，以及它通過(guò)索引快速全掃描來(lái)驅(qū)動(dòng)刪除的能力-這一功能在早期版本的Oracle中無(wú)法運(yùn)行。在我的小示例中，一個(gè)測(cè)試將其執(zhí)行計(jì)劃從11g的索引全掃描更改為12c的索引快速全掃描，完成所需的時(shí)間是原來(lái)的兩倍。

繼續(xù)想一想——當(dāng)你試圖通過(guò)索引范圍掃描來(lái)刪除Oracle中的表或者索引時(shí)，您能想到多少事情，這可能會(huì)產(chǎn)生怎樣的影響？

對(duì)于一個(gè)繁忙的系統(tǒng)，這個(gè)建議聽起來(lái)不錯(cuò)。有時(shí)候，你會(huì)發(fā)現(xiàn)一個(gè)長(zhǎng)時(shí)間運(yùn)行的DML語(yǔ)句在運(yùn)行時(shí)速度非常慢，因?yàn)槭聦?shí)上它涉及到數(shù)據(jù)中最近的部分，因此會(huì)受到當(dāng)前變化的影響;從這一點(diǎn)來(lái)看，Oracle發(fā)現(xiàn)它必須讀取undo段來(lái)獲取undo數(shù)據(jù)，這使得創(chuàng)建與讀取一致的數(shù)據(jù)塊版本成為可能-它需要這樣做，以便它可以檢查當(dāng)前和讀取一致的版本的塊同意哪些行應(yīng)該刪除。

我做的一個(gè)例子是通過(guò)“date_open”索引刪除數(shù)據(jù)-因此，如何強(qiáng)制索引進(jìn)行降序范圍掃描，以便首先檢查最新的數(shù)據(jù)在它有很多(或任何)時(shí)間遭受其他DML的附帶損害之前?

有一個(gè)非?？旖莸姆椒梢詸z驗(yàn)這個(gè)想法的有效性。所以我們要做的就是檢查排序的行數(shù)和刪除的行數(shù)我們就能知道優(yōu)化是否發(fā)生了。

我的測(cè)試數(shù)據(jù)集有1000000行和4個(gè)索引(主鍵client_ref、date_open和date_closed索引)，所以在最好的情況下，我應(yīng)該看到:“sort (rows)”= 4 *行被刪除。下面是我做的一個(gè)測(cè)試的總結(jié)，我想知道會(huì)發(fā)生什么：

delete /*+ index_desc(t1 t1_pk) */ from t1 where id <= 5e6
5000000 rows deleted.
Name                                 Value                       
----                                 -----
sorts (rows)                            29

我們刪除了500萬(wàn)行并(有效地)沒(méi)有排序。當(dāng)我們按降序遍歷索引時(shí)，優(yōu)化根本不適用—我確實(shí)檢查了執(zhí)行計(jì)劃是否顯示了我所指定的“索引范圍遞減掃描”。

create index t1_dt_open on t1(date_open desc) nologging tablespace test_8k_assm_2;
delete /*+ index(t1 t1_dt_open) */ from t1 where date_open <= add_months(sysdate, -60);
4999999 rows deleted.
Name                                 Value
----                                 -----
sorts (rows)                    20,003,449

在副作用很小的范圍內(nèi)，“sort (rows)”= 4 *已刪除的行:所以可以使用降序索引先嘗試刪除較新的數(shù)據(jù)——這很好，作為一個(gè)通用特性來(lái)記住可能很有用。

讓我們想象一下其他可能出錯(cuò)的情況。

*我在這個(gè)表上定義了一個(gè)主鍵——但是你可以使約束延遲，或者您可以簡(jiǎn)單地創(chuàng)建一個(gè)非惟一索引來(lái)保護(hù)惟一(或主鍵)約束。如果我們?cè)噲D通過(guò)主鍵索引刪除，會(huì)產(chǎn)生什么影響?

*如果我們要考慮約束，我們可能要考慮外鍵約束的影響——我們有一個(gè)client_ref列，在生產(chǎn)系統(tǒng)中，它可能是對(duì)clients表的外鍵引用。讓我們創(chuàng)建這個(gè)表并添加外鍵約束。

*當(dāng)我們使事情變得更困難時(shí)——有一個(gè)眾所周知的特性將數(shù)組處理轉(zhuǎn)換為“逐行”處理——觸發(fā)器。如果我們向表中添加行級(jí)觸發(fā)器，會(huì)產(chǎn)生什么效果?什么類型的觸發(fā)器(在之前/之后、插入/更新/刪除)有什么區(qū)別嗎?

以下是一些結(jié)果-首先，主鍵約束的非唯一索引：

alter table t1 drop primary key;
alter table t1 add constraint t1_pk primary key(id)
deferrable initially immediate
using index nologging tablespace test_8k_assm_2
;
delete /*+ index(t1 t1_pk) */ from t1 where id <= 5e6;
5000000 rows deleted.
Name                                 Value
----                                 -----
sorts (rows)                     15,000,004

在這個(gè)例子中，Oracle將我的主鍵索引設(shè)置為非惟一，作為約束可延遲的副作用，但是即使約束不可延遲，并且您只是將索引創(chuàng)建為非惟一，其效果也是一樣的。統(tǒng)計(jì)數(shù)據(jù)告訴我們，我們已經(jīng)將優(yōu)化應(yīng)用于四個(gè)索引中的三個(gè)——快速檢查一下v$segment_statistics，就會(huì)發(fā)現(xiàn)它是主鍵索引，沒(méi)有進(jìn)行特殊處理，它受到了超過(guò)500萬(wàn)個(gè)“db塊更改”的影響。在這一點(diǎn)上，有必要快速檢查一下，看看通過(guò)其他索引驅(qū)動(dòng)是否會(huì)改變這種情況——但是不會(huì)，這是惟一約束與非惟一索引結(jié)合的副作用。

其次，當(dāng)大表是“子表”時(shí)，引用完整性的影響：

create table t2 (
        client_id,
        client_name
) as
select
        distinct
                client_ref,
                rpad('x',100,'x')
from
        t1
;
alter table t2 add constraint t2_pk primary key(client_id);
alter table t1 modify client_ref not null;
alter table t1 add constraint t1_fk_t2 foreign key (client_ref) references t2(client_id);
5000000 rows deleted.
Name                                 Value
----                                 -----
sorts (rows)                    15,002,849

我們已經(jīng)排序了大約1500萬(wàn)行，而通常我們需要排序2000萬(wàn)行.同樣，我們可以檢查v$segment_statistics來(lái)找出哪個(gè)索引遭受了500萬(wàn)的損失“db block changes”你可能不會(huì)對(duì)“外鍵”索引被逐行維護(hù)而感到驚訝-我們可能會(huì)猜測(cè)，這是某種先發(fā)制人的代碼使得Oracle必須處理“外鍵鎖定”威脅。 我們通過(guò)主鍵刪除這個(gè)特定測(cè)試的后續(xù)操作是，考慮如果我們通過(guò)外鍵索引本身刪除，或者甚至將約束修改為“on delete cascade”并刪除一些父行，將會(huì)發(fā)生什么。通過(guò)client_ref在t1上驅(qū)動(dòng)delete仍然優(yōu)化了其他三個(gè)索引，但是當(dāng)您試圖利用“on delete cascade”機(jī)制時(shí)，這個(gè)技巧根本沒(méi)有機(jī)會(huì)產(chǎn)生大規(guī)模的效果。在幕后你會(huì)發(fā)現(xiàn)這樣的事情：

delete from "TEST_USER"."T1"
where
 "CLIENT_REF" = :1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute   3000      5.23      15.37      69349       9238     428052       32510
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3003      5.23      15.37      69349       9238     428052       32510
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     0          0          0  DELETE  T1 (cr=3 pr=22 pw=0 time=9672 us)
     7          8         11   INDEX RANGE SCAN T1_CLIENT (cr=3 pr=0 pw=0 time=125 us cost=3 size=594 card=22)(object id 150589)
This output the consequence of a bulk delete of 3,000 rows from t2 – because of the “on delete cascade”, the delete operated row by row on t2 and for each row Oracle executed a delete statement against t1.

這個(gè)輸出是t2批量刪除3000行的結(jié)果——由于“ on delete cascade ”，delete在t2上逐行操作，對(duì)于每一行Oracle都對(duì)t1執(zhí)行一條delete語(yǔ)句。

從技術(shù)上講，基于數(shù)組的優(yōu)化是有效的，由于索引范圍掃描，它給我們帶來(lái)了一點(diǎn)好處，但是數(shù)據(jù)的分散性是如此之大，以至于每次調(diào)用幾乎沒(méi)有給我們帶來(lái)任何好處。在某個(gè)階段，我們將不得不進(jìn)一步探索這種父/子的關(guān)系。

最后是觸發(fā)器。眾所周知，行級(jí)觸發(fā)器可以將數(shù)組處理轉(zhuǎn)換為單行處理——Oracle的索引維護(hù)優(yōu)化也會(huì)發(fā)生同樣的事情嗎？

create or replace trigger t1_brd
before delete on t1
for each row
begin
    null;
end;
/
delete /*+ index(t1 t1_pk) */ from t1 where id <= 5e6;
5000000 rows deleted.
Name                                 Value
----                                 -----
sorts (rows)                         2,639

優(yōu)化完全消失了。同樣的事情也會(huì)發(fā)生在“為每一行刪除后”觸發(fā)器上，但是如果觸發(fā)器是insert或update(行級(jí))觸發(fā)器，則不會(huì)發(fā)生這種情況。值得一提的是，索引優(yōu)化也發(fā)生在索引列的值發(fā)生變化的更新上(請(qǐng)參閱本文)，因此留給感興趣的讀者一個(gè)練習(xí)，看看哪些(如果有的話)觸發(fā)器類型允許優(yōu)化在數(shù)組更新后繼續(xù)存在。

| 譯者簡(jiǎn)介

湯健·沃趣科技數(shù)據(jù)庫(kù)技術(shù)專家

沃趣科技數(shù)據(jù)庫(kù)工程師，多年Oracle數(shù)據(jù)庫(kù)從業(yè)經(jīng)驗(yàn)，深入理解Oracle數(shù)據(jù)庫(kù)結(jié)構(gòu)體系，現(xiàn)主要參與公司一體機(jī)產(chǎn)品安裝、測(cè)試、優(yōu)化，并負(fù)責(zé)電信行業(yè)數(shù)據(jù)庫(kù)以及系統(tǒng)運(yùn)維。