《數(shù)據(jù)分布決定SQL寫法》要點(diǎn):
本文介紹了數(shù)據(jù)分布決定SQL寫法,希望對您有用。如果有疑問,可以聯(lián)系我們。
這是2016年8月份上海MOORACLE大會上陳宏義老師(老K)分享的一個案例,將一個merge SQL,通過改寫成plsql的方式,大大提高了執(zhí)行效率. 老虎劉在看到這個案例的時候,開始沒有注意到執(zhí)行計(jì)劃里面顯示的各表實(shí)際記錄數(shù),不認(rèn)為plsql的改寫方式比分析函數(shù)的寫法更高效,還與陳老師有過幾次郵件討論,直到后來仔細(xì)查看了執(zhí)行計(jì)劃.
原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是將用戶交易明細(xì)表(t_trade )的最近的一筆消費(fèi)額,更新到用戶信息表(t_customer)的消費(fèi)額字段,使用的是merge操作.
執(zhí)行計(jì)劃:
老虎劉注:
在沒有掌握分析函數(shù)的寫法前,SQL的紅色部分是group by后取其他字段信息的一個較為常見的寫法,也是這個SQL執(zhí)行效率差的根本原因.
原SQL還有一個隱患,就是如果t_trade的某個cstno對應(yīng)的最大trade_date有重復(fù),那么這個SQL會報ORA-30926 錯誤無法執(zhí)行.
如果不仔細(xì)看執(zhí)行計(jì)劃(兩表的真實(shí)數(shù)據(jù)量信息),這種SQL的慣用優(yōu)化方法是使用分析函數(shù)改寫:
改寫方法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對應(yīng)的max?trade_date 重復(fù)時報錯的問題.
但是陳老師沒有使用分析函數(shù)的改寫方法,而是根據(jù)兩表數(shù)據(jù)量相差較大的特點(diǎn),將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;
/
根據(jù)原SQL的執(zhí)行計(jì)劃我們知道,t_customer表的記錄數(shù)比較少,只有1000多條,而t_trade表有1000萬條,比例為1:10000(不知道這是真實(shí)數(shù)據(jù)還是測試數(shù)據(jù),只有1000多個用戶,而且一個用戶平均1萬個消費(fèi)明細(xì),看起來不像真實(shí)數(shù)據(jù)).
在這樣一個兩表數(shù)據(jù)相差較大的特殊情況下,plsql寫法確實(shí)是比分析函數(shù)的寫法要高效.這個改寫非常巧妙.
我們再來分析一下這兩種改寫的優(yōu)缺點(diǎn):
1、plsql的改寫方式,適合在t_customer表比較小,而且t_customer 和 t_trade 兩表的記錄數(shù)比例比較大的情況下,執(zhí)行效率才會比分析函數(shù)的改寫高一些.在本例中,如果t_customer表的記錄數(shù)是10萬,那么分析函數(shù)的寫法反而要比plsql的寫法快上幾十到上百倍.
3、plsql這種改寫的前提是必須存在t_trade表cstno + trade_date 兩字段的聯(lián)合索引.而分析函數(shù)的改寫就不需要任何索引的支持.
4、對于t_trade這種千萬記錄級別的表,使用分析函數(shù)的寫法可以通過開啟并行來提速;plsql的改寫,如果要提高效率,需要先將t_customer表按cstno分組,用多個session并發(fā)執(zhí)行.
我們再來看看,陳老師的這段plsql,是不是可以用單個sql來實(shí)現(xiàn),我做了一個嘗試,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;
執(zhí)行計(jì)劃大致如下:
這種寫法也是需要t_trade表存在cstno+trade_date 聯(lián)合索引(IDX_T_TRADE),而且T_customer 表的數(shù)據(jù)量遠(yuǎn)低于T_trade.
根據(jù)執(zhí)行計(jì)劃,這個sql的執(zhí)行效率應(yīng)該比plsql寫法的效率不相上下.
總結(jié):
SQL優(yōu)化,除了要避免低效的SQL寫法,主要還是要看表的數(shù)據(jù)量與數(shù)據(jù)分布情況,plsql的改寫方法,在少數(shù)比較特殊的情況下會體現(xiàn)出較高的效率,在某些數(shù)據(jù)分布的情況下,效率可能還不如原SQL.但是,優(yōu)化思路非常值得借鑒.
而分析函數(shù)的改寫方式,則不論數(shù)據(jù)如何分布,都會比原SQL要高效,通用性更強(qiáng).
對于本例改寫前的SQL,應(yīng)該還有很多開發(fā)人員和DBA在使用,在了解了分析函數(shù)的使用方法后,原SQL的低效寫法就應(yīng)該被徹底拋棄了.
最后的plsql改寫成單SQL,邏輯看起來比較復(fù)雜難懂,一般不會用到這樣的改寫,大家了解一下就好了.
還是那句話,優(yōu)化無定式,優(yōu)化器是死的,人腦是活的,只有掌握了原理,才能讓SQL執(zhí)行效率越來越高.
轉(zhuǎn)載請注明本頁網(wǎng)址:
http://www.fzlkiss.com/jiaocheng/4075.html