紅聯Linux門戶
Linux幫助

改寫函數提高SQL效率的研究

發布時間:2017-11-17 09:51:29來源:紅聯作者:Ronny
這是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.linuxprobe.com/datadistribution-decide-sqlwriting.html
免費提供最新Linux技術教程書籍,為開源技術愛好者努力做得更多更好,開源站點:http://www.linuxprobe.com/
文章評論

共有 1 條評論

  1. Ronny 于 2017-11-17 09:51:34發表:

    改寫函數提高SQL效率的研究

贵州体彩11选5查询号码 15选5专家预测 深圳股市行情 安徽快三最新开奖结果 青海快三专家推荐的好 电脑炒股软件哪个最好 青海11选5查询 秒速时时彩计划 河北11选5中奖规则表 今日太平洋股票行情 快3大小单双稳赚技巧