這是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 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執行效率越來越高。
以上是優化SQL效率的研究的詳細內容。更多資訊請關注PHP中文網其他相關文章！
