《數據分布決定SQL寫法》要點：
本文介紹了數據分布決定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.snjht.com/jiaocheng/4075.html