最近單位搬家，從國家會議中心，搬往空氣清新的順義后沙峪，搬遷之前的完結(jié)上線中，碰見了一些棘手的問題，有一些值得借鑒的地方。

十多年來，創(chuàng)新互聯(lián)公司不忘初心，以網(wǎng)站建設(shè)互聯(lián)網(wǎng)行業(yè)服務(wù)標(biāo)桿為目標(biāo)，不斷提升技術(shù)設(shè)計(jì)服務(wù)水平，幫助客戶在互聯(lián)網(wǎng)推廣自己的產(chǎn)品、服務(wù)和品牌，為客戶創(chuàng)造價(jià)值從而實(shí)現(xiàn)自身價(jià)值！

這是一個(gè)夜維程序的優(yōu)化。這個(gè)夜維的目的，是每日刪除30+張表歷史數(shù)據(jù)，其中的主要矛盾，是一張5000萬的表，以下僅針對這張表的優(yōu)化，做下介紹，大致經(jīng)歷了幾個(gè)階段，

階段一：

順序刪除每張表，例如表A和B，B為A表子表，由于表有主外鍵關(guān)系，因此需要先刪B表，再要刪除A，刪除條件是從A表檢索出歷史過期的數(shù)據(jù)對應(yīng)的記錄id，用B表p_id和A表id關(guān)聯(lián)，執(zhí)行刪除，id字段是A表主鍵，使用序列賦值，p_id、id和c_date均有索引定義，A表總數(shù)據(jù)量2000萬，A表每日待刪除數(shù)據(jù)量為200萬，B表總數(shù)據(jù)量5000萬，B表每日待刪除數(shù)據(jù)量約為800萬，為了減小UNDO和REDO壓力，需要批量提交，SQL類似如下，

delete from B where B.p_id in (select id from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd')) and rownum < ?;

一次刪除10000條(?值為10000)，由于c_date(只有日期無時(shí)間，只保存10天)區(qū)分度低，因此子查詢用了全表掃描，刪除B表需要執(zhí)行200次SQL語句，即200次20000萬A表的全表掃描，業(yè)務(wù)量初期數(shù)據(jù)有限，A表數(shù)據(jù)量處于百萬級，機(jī)器配置較高，因此沒有問題，但隨著數(shù)據(jù)量的增加，執(zhí)行時(shí)間變久，毋庸置疑。

階段二：

由于業(yè)務(wù)量增加，數(shù)據(jù)庫積累的數(shù)據(jù)有一定量，導(dǎo)致夜維執(zhí)行時(shí)間越來越久，需要進(jìn)行優(yōu)化。

首先子查詢?nèi)頀呙瑁豢杀苊?，為了提升效率，一種思路就是少做事。200次2000萬A表的全表掃描操作，是否可以避免？

既然每次需要刪除的是，2000萬中的200萬，可以先將這200萬存入中間表，即使全表掃描，只掃描200萬，要比掃描2000萬數(shù)據(jù)要強(qiáng)些，中間表C只有一個(gè)字段，用于存儲待刪除的id標(biāo)記，

create table C (id number);

 

insert into C select id from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd');

然后用中間表，和B表關(guān)聯(lián)，

delete from B where B.p_id in (select id from C) and rownum <= ?;

?值為10000，代表每次刪除1萬。同時(shí)從運(yùn)行同事了解，夜維執(zhí)行期間，數(shù)據(jù)庫負(fù)載不高，因此可以充分利用資源，數(shù)據(jù)庫服務(wù)器80C128G，應(yīng)用開啟多線程，除了主子表外，其他表實(shí)現(xiàn)并發(fā)刪除操作。

階段三：

隨著業(yè)務(wù)量逐漸增加，上面的機(jī)制仍不能滿足要求，而且有幾次夜維執(zhí)行時(shí)間，甚至超了20小時(shí)，奇怪的是，夜維某些天正常，可能5、6個(gè)小時(shí)就能完成，某些天就會出現(xiàn)超長，甚至有一次第二天即將執(zhí)行，然而第一天夜維還未完成，為了不影響執(zhí)行，手工kill了舊進(jìn)程。

回來再看這條SQL，其中子查詢返回的記錄，大約200萬左右數(shù)據(jù)，B表和子查詢關(guān)聯(lián)，得到所有符合條件的記錄，大約800萬，即1:4的關(guān)系，1條C表的id值，對應(yīng)B表4條記錄，為了批量提交，每次只刪除這800萬中的1萬，

delete from B where B.p_id in (select id from C) and rownum <= ?;

這幾次超長執(zhí)行，從數(shù)據(jù)庫層面看，反映的現(xiàn)象就是物理讀超高，例如之前這條SQL物理讀，值是3000，這幾次值就是10000。

由于子查詢肯定全表掃描，每次執(zhí)行，都要讀取200萬數(shù)據(jù)，第一次執(zhí)行SQL語句，就需要從磁盤文件讀取，放入buffer cache，此時(shí)消耗物理讀，若這個(gè)時(shí)間段內(nèi)，對于數(shù)據(jù)庫緩存消耗高，例如其他大表的頻繁加載，就會增加buffer cache的age out刷出操作，進(jìn)而可能出現(xiàn)，第二次執(zhí)行這條SQL語句，這200萬數(shù)據(jù)部分、甚至全部，需要從磁盤再次讀取，如果待刪除800萬，一次刪除1萬記錄，就需要執(zhí)行800次，極端情況，就需要重復(fù)加載800次200萬數(shù)據(jù)，平均下來，單次物理讀高，就可想而知了。

為了緩解，打算這么調(diào)整，為C表增加pkid字段，用于存儲rownum，如下示例，id仍是待刪除的條件值，pkid則為A表id對應(yīng)的rownum，其目的就是為了，C表每個(gè)id都對應(yīng)一個(gè)編號，且這個(gè)編號是有序遞增，

create table C (id number, pkid number);

 

create index idx_c_01 on c(pkid);

 

insert into C select id, rownum from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd');

刪除B表的時(shí)候，首先程序中循環(huán)，以id為條件，一次檢索1萬記錄，例如第一次是"where pkid > 0 and pkid < 10001"，第二次是"where pkid > 10000 and pkid < 20001"，即將B表每次刪除1萬條，批量刪除的邏輯，推至內(nèi)層循環(huán)，

delete from B where B.p_id in (select id from C where pkid > ? and pkid < ?);

按照業(yè)務(wù)評估，C表1個(gè)id，對應(yīng)B表4條記錄，因此子查詢1萬，B表刪除4萬，雖然一次批量刪除較之前，有所增加，但看著是可控，而且可以避免，每次讀取C表所有200萬數(shù)據(jù)。

階段四：

可是這種修改，當(dāng)晚執(zhí)行，就出現(xiàn)了問題，夜維日志報(bào)錯(cuò)，ORA-01555，

從alert日志中，確認(rèn)就是這條SQL，導(dǎo)致了這個(gè)ORA-01555錯(cuò)誤，

ORA-01555 caused by SQL statement below (SQL ID: xxxxxxxxxx, Query Duration=11500 sec, SCN: 0x0001.f10b2hk7):

delete from B where B.p_id in (select id from C where pkid > :1 and pkid < :2);

ORA-01555錯(cuò)誤，快照太舊，是Oracle一個(gè)非常經(jīng)典的錯(cuò)誤號，簡單一句話介紹，我覺得就是“DML語句需要用UNDO記錄的數(shù)據(jù)找到前鏡像時(shí)，該記錄在UNDO中已經(jīng)被覆蓋，導(dǎo)致無法利用UNDO中的記錄完成一致性讀”，我曾寫了一篇小文介紹(http://blog.csdn.net/bisal/article/details/18187635)。

再看SQL語句，說明執(zhí)行delete操作，時(shí)間太久，導(dǎo)致期間使用的UNDO前鏡像，已經(jīng)被其他事務(wù)覆蓋了，因此直接報(bào)錯(cuò)ORA-01555。而且懷疑，這條SQL語句，可能沒有一次執(zhí)行成功的，由于使用了綁定變量，緩存未被刷新，檢索出來，報(bào)錯(cuò)SQL使用的綁定變量值，正是第一次執(zhí)行需要的0-10000，

(提取方法可參考《 一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之查看綁定變量值的幾種方法 》)

要了解為什么執(zhí)行慢，就需要看一下，SQL語句的執(zhí)行計(jì)劃，此處屏蔽了表名，解釋一下，

(1) 首先對表B執(zhí)行全表掃描。

(2) 對表A執(zhí)行了索引掃描。

(3) 然后以(1)結(jié)果集為驅(qū)動表，和(2)結(jié)果集進(jìn)行NESTED LOOPS SEMI連接操作。

SQL執(zhí)行慢原因基本清楚了，表B有5000萬的數(shù)據(jù)，表A總計(jì)200萬數(shù)據(jù)，1次檢索1萬數(shù)據(jù)，相當(dāng)于執(zhí)行200次5000萬數(shù)據(jù)的全表掃描再和1萬數(shù)據(jù)進(jìn)行NESTED LOOPS SEMI表連接操作，進(jìn)而刪除B表數(shù)據(jù)。

這比800次掃描200萬的數(shù)據(jù)，有過之而無不及，不報(bào)ORA-01555的錯(cuò)誤才怪，

delete from B where B.p_id in (select id from C) and rownum <= ?;

問題來了，B表的p_id字段有索引，查看統(tǒng)計(jì)信息，無論是表，還是索引，都是每晚22:00，由自動采集任務(wù)更新了，夜維執(zhí)行時(shí)間，每日00:30開始執(zhí)行，可以說每次用的，都是最新的統(tǒng)計(jì)信息，這次調(diào)整，原義是限制內(nèi)層數(shù)據(jù)量，為了減少數(shù)據(jù)，然后利用B表索引，為何沒用上p_id索引？

難道子查詢1次1萬，有些過了？

用二分法嘗試，0-5000、0-2500、...、甚至使用0-10，都比較慢，沒用索引。

碰巧測試了下，1910000-1920000區(qū)間，這條SQL執(zhí)行迅速，看其對應(yīng)的執(zhí)行計(jì)劃，正是我們需要的，

(1) 索引范圍掃描表C。

(2) 索引范圍掃描表B。

(3) (1)和(2)進(jìn)行NESTED LOOPS連接操作。

為何這一區(qū)間，就可以使用B表的索引，0-10000區(qū)間就不能使用？

有的同事提問：

“C表id如果排序，和B表中順序一致的話，會不會有影響？就是在插入C表id前，按照id排下序?！?/p>

此時(shí)看下pkid=1910000-1920000對應(yīng)的C表id記錄，可以發(fā)現(xiàn)，基本都是有序，而且間距較小，例如

1000001

1000003

1000010

1000011

...

再看一下pkid=1-10000區(qū)間，有些是無序的，而且差值較大，例如，

1000021

1000210

1000020

1001000

1000002

...

之前我們說了，C表的id來自于A表的主鍵序列，意味著有序遞增，換句話說，id越近的記錄數(shù)，就越可能位于同一個(gè)數(shù)據(jù)塊，id越遠(yuǎn)的記錄數(shù)，就越可能不在同一個(gè)數(shù)據(jù)塊，區(qū)別就是，例如前者讀取兩個(gè)記錄，可能只需要1次IO，后者可能需要2次IO，這很像索引的聚簇因子，即索引鍵值對應(yīng)的數(shù)據(jù)記錄，在數(shù)據(jù)塊中存儲的越有序，clustering factor的值越低，計(jì)算索引掃描的成本值，就會越低，此時(shí)認(rèn)為索引掃描更高效，

C表中id列越有序，對應(yīng)于表B記錄，就越可能位于相同數(shù)據(jù)塊，消耗更小IO操作，因此此時(shí)的焦點(diǎn)，就在于如何讓C表id有序？

之前C表數(shù)據(jù)用如下語句，

insert into C select id, rownum from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd');

由于從表A檢索，未指定任何order by排序，因此默認(rèn)會按照數(shù)據(jù)，在數(shù)據(jù)塊中的排序順序，進(jìn)行讀取，無法保證有序。此時(shí)我們增加order by，讓其按照id順序進(jìn)行讀取，就可以保證表C中id有序，

insert into C select p.*, rownum from (select id from A where c_date<=to_date('xxxx-xx-xx', 'yyyy-mm-dd') order by id) p;

細(xì)心的朋友可能注意到，order by id是否主鍵，對于rownum取值的順序，可能會有影響。

刪除語句不變，

delete from B where B.p_id in (select id from C where pkid > ? and pkid < ?);

但此時(shí)任何區(qū)間，都可以按照上面，正確的執(zhí)行計(jì)劃，進(jìn)行刪除操作，

這兩天執(zhí)行夜維，基本保持2.5小時(shí)左右用時(shí)，這張總計(jì)5000萬數(shù)據(jù)量的B表，800萬/日刪除用時(shí)，45分鐘左右，一下從主要矛盾，變?yōu)榇我芰恕?/p>

問題解決過程，屬于團(tuán)隊(duì)的智慧，感謝開發(fā)團(tuán)隊(duì)的山山、運(yùn)維團(tuán)隊(duì)的力偉、運(yùn)行團(tuán)隊(duì)的健哥、亞偉和albert兄。

總結(jié)：

1. 有人曾說，好架構(gòu)不是設(shè)計(jì)出來的，而是演進(jìn)出來的，對于某些數(shù)據(jù)庫開發(fā)來說，同樣適用，不同的方案在不同階段，適用程度不同，例如本文示例。

2. 但是從某一方面來講，這種性能隱患，又是可以設(shè)計(jì)，可以避免，比如大表的全表掃描，如果開始不考慮，毋庸置疑，就是會隨著數(shù)據(jù)量的增加，產(chǎn)生影響，可以看出，邏輯設(shè)計(jì)，以及SQL審核，在數(shù)據(jù)庫開發(fā)工作中的重要。

3. 出現(xiàn)SQL性能問題，首先要看的就是執(zhí)行計(jì)劃，當(dāng)然你要知道，如何找出真實(shí)的執(zhí)行計(jì)劃，如何找出綁定變量值，可能還需要看10053的trace文件，這些常用知識點(diǎn)，可能未必記得，但用的時(shí)候知道從何檢索，Oracle官方文檔、Google等等，就可以了，之前曾寫過一些小文，僅供參考，

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 前傳

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之綁定變量窺探

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之查看綁定變量值的幾種方法

rolling invalidation對子游標(biāo)產(chǎn)生的影響

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之聚簇因子(Clustering Factor)

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之查詢執(zhí)行計(jì)劃的幾種方法

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之AWR

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之ASH

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之SQL AWR

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之直方圖

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之SQL Profile(上)

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 外傳之SQL Profile(下)

一個(gè)執(zhí)行計(jì)劃異常變更的案例 - 正傳