一次夜維SQL的性能優化

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

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

階段一：

順序刪除每張表，例如表A和B，B為A表子表，由于表有主外鍵關系，因此需要先刪B表，再要刪除A，刪除條件是從A表檢索出歷史過期的數據對應的記錄id，用B表p_id和A表id關聯，執行刪除，id字段是A表主鍵，使用序列賦值，p_id、id和c_date均有索引定義，A表總數據量2000萬，A表每日待刪除數據量為200萬，B表總數據量5000萬，B表每日待刪除數據量約為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(只有日期無時間，只保存10天)區分度低，因此子查詢用了全表掃描，刪除B表需要執行200次SQL語句，即200次20000萬A表的全表掃描，業務量初期數據有限，A表數據量處于百萬級，機器配置較高，因此沒有問題，但隨著數據量的增加，執行時間變久，毋庸置疑。

階段二：

由于業務量增加，數據庫積累的數據有一定量，導致夜維執行時間越來越久，需要進行優化。

首先子查詢全表掃描，不可避免，為了提升效率，一種思路就是少做事。200次2000萬A表的全表掃描操作，是否可以避免？

既然每次需要刪除的是，2000萬中的200萬，可以先將這200萬存入中間表，即使全表掃描，只掃描200萬，要比掃描2000萬數據要強些，中間表C只有一個字段，用于存儲待刪除的id標記，

create table C (id number);

 

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

然后用中間表，和B表關聯，

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

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

階段三：

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

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

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

這幾次超長執行，從數據庫層面看，反映的現象就是物理讀超高，例如之前這條SQL物理讀，值是3000，這幾次值就是10000。

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

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

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表的時候，首先程序中循環，以id為條件，一次檢索1萬記錄，例如第一次是"where pkid > 0 and pkid < 10001"，第二次是"where pkid > 10000 and pkid < 20001"，即將B表每次刪除1萬條，批量刪除的邏輯，推至內層循環，

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

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

階段四：

可是這種修改，當晚執行，就出現了問題，夜維日志報錯，ORA-01555，

從alert日志中，確認就是這條SQL，導致了這個ORA-01555錯誤，

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錯誤，快照太舊，是Oracle一個非常經典的錯誤號，簡單一句話介紹，我覺得就是“DML語句需要用UNDO記錄的數據找到前鏡像時，該記錄在UNDO中已經被覆蓋，導致無法利用UNDO中的記錄完成一致性讀”，我曾寫了一篇小文介紹(http://blog.csdn.net/bisal/article/details/18187635)。

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

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

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

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

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

(3) 然后以(1)結果集為驅動表，和(2)結果集進行NESTED LOOPS SEMI連接操作。

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

這比800次掃描200萬的數據，有過之而無不及，不報ORA-01555的錯誤才怪，

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

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

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

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

碰巧測試了下，1910000-1920000區間，這條SQL執行迅速，看其對應的執行計劃，正是我們需要的，

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

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

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

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

有的同事提問：

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

此時看下pkid=1910000-1920000對應的C表id記錄，可以發現，基本都是有序，而且間距較小，例如

1000001

1000003

1000010

1000011

...

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

1000021

1000210

1000020

1001000

1000002

...

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

C表中id列越有序，對應于表B記錄，就越可能位于相同數據塊，消耗更小IO操作，因此此時的焦點，就在于如何讓C表id有序？

之前C表數據用如下語句，

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

由于從表A檢索，未指定任何order by排序，因此默認會按照數據，在數據塊中的排序順序，進行讀取，無法保證有序。此時我們增加order by，讓其按照id順序進行讀取，就可以保證表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;

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

刪除語句不變，

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

但此時任何區間，都可以按照上面，正確的執行計劃，進行刪除操作，

這兩天執行夜維，基本保持2.5小時左右用時，這張總計5000萬數據量的B表，800萬/日刪除用時，45分鐘左右，一下從主要矛盾，變為次要矛盾了。

問題解決過程，屬于團隊的智慧，感謝開發團隊的山山、運維團隊的力偉、運行團隊的健哥、亞偉和albert兄。

總結：

1. 有人曾說，好架構不是設計出來的，而是演進出來的，對于某些數據庫開發來說，同樣適用，不同的方案在不同階段，適用程度不同，例如本文示例。

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

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

一個執行計劃異常變更的案例 - 前傳

一個執行計劃異常變更的案例 - 外傳之綁定變量窺探

一個執行計劃異常變更的案例 - 外傳之查看綁定變量值的幾種方法

rolling invalidation對子游標產生的影響

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

一個執行計劃異常變更的案例 - 外傳之查詢執行計劃的幾種方法

一個執行計劃異常變更的案例 - 外傳之AWR

一個執行計劃異常變更的案例 - 外傳之ASH

一個執行計劃異常變更的案例 - 外傳之SQL AWR

一個執行計劃異常變更的案例 - 外傳之直方圖

一個執行計劃異常變更的案例 - 外傳之SQL Profile(上)

一個執行計劃異常變更的案例 - 外傳之SQL Profile(下)

一個執行計劃異常變更的案例 - 正傳