資料分佈決定SQL寫法

這是2016年8月份上海MOORACLE大會上陳巨集義老師(老K)分享的一個案例，將一個merge SQL，通過改寫成plsql的方式，大大提高了執行效率。 老虎劉在看到這個案例的時候，開始沒有注意到執行計劃裡面顯示的各表實際記錄數，不認為plsql的改寫方式比分析函式的寫法更高效，還與陳老師有過幾次郵件討論，直到後來仔細檢視了執行計劃。

原SQL如下：

merge into t_customer c using

(

select a.cstno, a.amount from t_trade a,

(select cstno,max(trade_date) trade_date from t_trade

group by cstno) b

where a.cstno = b.cstno and a.trade_date=b.trade_date

) m

on(c.cstno = m.cstno)

when matched then

update set c.amount = m.amount;

這個SQL是將使用者交易明細表（t_trade ）的最近的一筆消費額，更新到使用者資訊表（t_customer）的消費額欄位，使用的是merge操作。

執行計劃：

老虎劉注：

在沒有掌握分析函式的寫法前，SQL的紅色部分是group by後取其他欄位資訊的一個較為常見的寫法，也是這個SQL執行效率差的根本原因。

原SQL還有一個隱患，就是如果t_trade的某個cstno對應的最大trade_date有重複，那麼這個SQL會報ORA-30926 錯誤無法執行。

如果不仔細看執行計劃（兩表的真實資料量資訊），這種SQL的慣用優化方法是使用分析函式改寫：

改寫方法1：

merge into t_customer c using

(

select a.cstno,a.amount from

(select trade_date,cstno,amount,

row_number()over(partition by cstno order by trade_date desc) RNO from t_trade)a

where RNO=1

)  m

on(c.cstno = m.cstno)

when matched then

update set c.amount = m.amount;

這種改寫方法會比原SQL效率提高很多，而且不存在某個cstno對應的max trade_date 重複時報錯的問題。

但是陳老師沒有使用分析函式的改寫方法，而是根據兩表資料量相差較大的特點，將SQL改寫成一段更為高效的plsql：

改寫方法2：

declare

vamount number;

begin

for v in (select * from t_customer )

loop

select amount into vamount from

(select amount from t_trade where cstno=v.cstno order by trade_date desc)

where rownum<2;

update t_customer set amount = vamount where cstno=v.cstno;

end loop

commit;

end;

根據原SQL的執行計劃我們知道，t_customer表的記錄數比較少，只有1000多條，而t_trade表有1000萬條，比例為1:10000（不知道這是真實資料還是測試資料，只有1000多個使用者，而且一個使用者平均1萬個消費明細，看起來不像真實資料）。

在這樣一個兩表資料相差較大的特殊情況下，plsql寫法確實是比分析函式的寫法要高效。這個改寫非常巧妙。

我們再來分析一下這兩種改寫的優缺點：

1、plsql的改寫方式，適合在t_customer表比較小，而且t_customer 和 t_trade 兩表的記錄數比例比較大的情況下，執行效率才會比分析函式的改寫高一些。在本例中，如果t_customer表的記錄數是10萬，那麼分析函式的寫法反而要比plsql的寫法快上幾十到上百倍。

3、plsql這種改寫的前提是必須存在t_trade表cstno + trade_date 兩欄位的聯合索引。而分析函式的改寫就不需要任何索引的支援。

4、對於t_trade這種千萬記錄級別的表，使用分析函式的寫法可以通過開啟並行來提速；plsql的改寫，如果要提高效率，需要先將t_customer表按cstno分組，用多個session併發執行。

我們再來看看，陳老師的這段plsql，是不是可以用單個sql來實現，我做了一個嘗試，SQL程式碼如下：

merge into t_customer c using

(

select tc.cstno,

(select amount

from t_trade td1

where td1.cstno=tc.cstno and td1.trade_date = (select max(trade_date) from t_trade td2 where tc.cstno = td2.cstno) and rownum=1 ) as amount

from t_customer tc

)  m

on(c.cstno = m.cstno)

when matched then

update set c.amount = m.amount;

執行計劃大致如下：

這種寫法也是需要t_trade表存在cstno+trade_date 聯合索引（IDX_T_TRADE），而且T_customer 表的資料量遠低於T_trade。

根據執行計劃，這個sql的執行效率應該比plsql寫法的效率不相上下。

總結：

SQL優化，除了要避免低效的SQL寫法，主要還是要看錶的資料量與資料分佈情況，plsql的改寫方法，在少數比較特殊的情況下會體現出較高的效率，在某些資料分佈的情況下，效率可能還不如原SQL。但是，優化思路非常值得借鑑。

而分析函式的改寫方式，則不論資料如何分佈，都會比原SQL要高效，通用性更強。

對於本例改寫前的SQL，應該還有很多開發人員和DBA在使用，在瞭解了分析函式的使用方法後，原SQL的低效寫法就應該被徹底拋棄了。

最後的plsql改寫成單SQL，邏輯看起來比較複雜難懂，一般不會用到這樣的改寫，大家瞭解一下就好了。

還是那句話，優化無定式，優化器是死的，人腦是活的，只有掌握了原理，才能讓SQL執行效率越來越高。

轉載自：

http://www.yunweipai.com/archives/18609.html