RWP談SQL優(yōu)化

Oracle Real-World Performance團(tuán)隊(duì)是一個人數(shù)不多的天才團(tuán)隊(duì)，整個團(tuán)隊(duì)的數(shù)據(jù)庫性能優(yōu)化經(jīng)驗(yàn)有超過400人年。團(tuán)隊(duì)成員分布在美國，中國和歐洲，不斷的尋找和創(chuàng)造新的方法分析診斷當(dāng)今世界業(yè)務(wù)系統(tǒng)的性能。Oracle Real-World Performance團(tuán)隊(duì)有著很多出類拔萃的戰(zhàn)績，在工作中，多次將客戶系統(tǒng)性能提升幾十上百倍，給客戶系統(tǒng)性能提升1000倍或更多也并不罕見。
  在中國，業(yè)界同仁將Oracle Real-World Performance團(tuán)隊(duì)簡稱為RWP團(tuán)隊(duì)。目前RWP團(tuán)隊(duì)在中國共有7名成員。

    說到SQL優(yōu)化，做為讀者的您，頭腦中第一反應(yīng)是什么？索引？Hint？分區(qū)？參數(shù)？執(zhí)行計(jì)劃？哈哈哈有被言中吧 ;-)，今天我們就來談?wù)凷QL優(yōu)化的整體思路，希望能夠?qū)δ兴鶈l(fā)。
1. 設(shè)定一個高的目標(biāo)

 如果您把一個SQL從一個小時(shí)優(yōu)化到了1分鐘，您會停止工作嗎？會不會考慮是否能給它優(yōu)化到1秒鐘？
   工作中，每個人都有壓力，壓力之下，很容易疏于思考。一個SQL多長時(shí)間能跑完，依賴于它跑在什么樣的硬件和軟件環(huán)境上。一個SQL能不能跑的更快，本質(zhì)上是：它是否能夠更加充分的利用硬件資源和軟件能力。
    做SQL優(yōu)化，給自己設(shè)定一個高的目標(biāo)非常重要！
2.  去優(yōu)化那些好的SQL

有了高的目標(biāo)，接下來，還要找到那些好的SQL進(jìn)行優(yōu)化。那么，什么是好的SQL？
（1）   有效的 SQL

    數(shù)據(jù)庫是為了執(zhí)行SQL設(shè)計(jì)的，不是為了一執(zhí)行就報(bào)錯的無效SQL設(shè)計(jì)的。

     如果執(zhí)行一個SQL，報(bào)ORA的錯誤，那么這是一個無效的SQL，它不應(yīng)該存在于您的系統(tǒng)里面，當(dāng)然更不應(yīng)該成為您優(yōu)化的對象。

    如果執(zhí)行一個SQL，報(bào)ORA的錯誤，那么在數(shù)據(jù)庫里面會是一個failure parse。如果您系統(tǒng)的AWR報(bào)告里面有failure parse，那么您要注意了，后果可能很嚴(yán)重。

（2）   您知道業(yè)務(wù)含義的SQL

    有很多時(shí)候，一些SQL和PL/SQL存儲過程是根本就不需要被執(zhí)行的。但是由于種種原因，那些SQL和PL/SQL存儲過程存在在系統(tǒng)中，可能都已存在了很長時(shí)間，寫那些SQL和PL/SQL存儲過程的人可能早就跳槽了，為了所謂的“穩(wěn)定”，沒有人去動那些SQL和PL/SQL存儲過程。去優(yōu)化這些根本就不需要被執(zhí)行的SQL和PL/SQL存儲過程當(dāng)然是沒有任何意義的。

    所以，在優(yōu)化任何一條SQL之前，應(yīng)該首先知道那條SQL業(yè)務(wù)上的含義，確定它確實(shí)是需要被執(zhí)行的，再去優(yōu)化它。

（3）   構(gòu)造好的SQL

    如果一個SQL語句里面有IN列表，IN列表里面有幾百個值，那么那幾百個值，很有可能是來源于另外一個SQL，而非人工輸入。由于IN列表中值的個數(shù)有一個允許的上限，有些SQL甚至?xí)L成下面的樣子：

    幾百幾千幾萬個值在IN列表里面，那是不是SQL構(gòu)造的不好，是不是應(yīng)該先將它改成一個JOIN再去考慮其他？

（4）   沒有編寫錯誤的SQL

    N個表做JOIN的話，一般情況應(yīng)該有N-1個JOIN條件。如果JOIN條件小于N-1個的話，就會有CARTESIAN JOIN出現(xiàn)，結(jié)果集里面會有重復(fù)值。在SELECT LIST里面加上DISTINCT，通常就可以使得SQL得到功能上正確的結(jié)果集。這就好比您去銀行取錢，實(shí)際只要取1000塊錢，可是您先取了2000塊錢，再把余下的1000存回去，多此一舉，雖然實(shí)際結(jié)果是對的，您確實(shí)是取了1000塊錢。

    當(dāng)SQL處理的數(shù)據(jù)量小的時(shí)候，這個多此一舉對于響應(yīng)時(shí)間的影響并不會很大?？墒钱?dāng)SQL處理的數(shù)據(jù)量大的時(shí)候，這個影響就會完全凸顯出來。還是那個取錢的例子，如果您實(shí)際只要取1000塊錢，可是您先取了10001000塊錢，再把余下的10000000塊錢存回去。最后您也會得到1000塊錢，可是銀行員工為您取錢的時(shí)候數(shù)出10001000塊錢的時(shí)間，和把錢存回去的時(shí)候再數(shù)好10000000塊錢的時(shí)間，都是您辦業(yè)務(wù)的時(shí)間，您取錢的時(shí)間就會變得相當(dāng)長了。

     SQL語句中WHERE條件里面的值的數(shù)據(jù)類型，應(yīng)該與相應(yīng)的列的數(shù)據(jù)類型一致。否則SQL語句雖不會報(bào)錯，會隱式的用函數(shù)將那個列轉(zhuǎn)換成與相應(yīng)的值的數(shù)據(jù)類型一致，去執(zhí)行SQL。這種隱式數(shù)據(jù)類型轉(zhuǎn)換，可能會導(dǎo)致ORA-01722的錯誤，可能會導(dǎo)致相應(yīng)的列上的索引不能被使用到，可能會導(dǎo)致明明可以使用分區(qū)裁剪但卻用不上的情況，響應(yīng)時(shí)間可能差好幾個數(shù)量級。

3.  給SQL一個好的執(zhí)行環(huán)境

    SQL需要在好的環(huán)境上執(zhí)行才能夠性能好。那么什么是好的執(zhí)行環(huán)境呢？

    正確的給軟件打上補(bǔ)丁，是打造好的執(zhí)行環(huán)境的第一步。明明您都花了錢買軟件，明明人家軟件廠家都出了補(bǔ)丁可以讓軟件跑的更好更快，為什么不打補(bǔ)丁呢？當(dāng)然了，打補(bǔ)丁是個技術(shù)活，怎么正確的給軟件打上補(bǔ)丁，肯定是要按照軟件廠家的說明來，或者咨詢軟件廠家啦。

    使用默認(rèn)的init.ora參數(shù)設(shè)置，也是打造好的執(zhí)行環(huán)境的重要一環(huán)。使用默認(rèn)的init.ora參數(shù)設(shè)置，意味著您是按照Oracle內(nèi)部研發(fā)團(tuán)隊(duì)設(shè)計(jì)軟件的方法去使用它，意味著您使用的是經(jīng)過Oracle內(nèi)部測試團(tuán)隊(duì)嚴(yán)格測試的軟件。當(dāng)然了，有一些特定的應(yīng)用軟件，比如Oracle的EBS，要求修改init.ora參數(shù)，這種情況是要修改，因?yàn)槟切┬薷氖墙?jīng)過應(yīng)用軟件廠家嚴(yán)格測試過的。

    如果是因?yàn)橛龅絙ug，需要修改某些參數(shù)做為臨時(shí)解決方案，那么當(dāng)那個bug修復(fù)之后，您應(yīng)該及時(shí)將相應(yīng)的參數(shù)改回去，否則后果可能也會很嚴(yán)重噢。

    另外，若隨意修改init.ora參數(shù)，可能會導(dǎo)致售后的問題。

4.  從數(shù)據(jù)庫設(shè)計(jì)的角度優(yōu)化SQL

 
    現(xiàn)在Oracle數(shù)據(jù)庫軟件使用的是Cost Based Optimizer（CBO），基于成本的優(yōu)化器。

    本質(zhì)上來講，優(yōu)化器就是一系列的算法。優(yōu)化器會接受輸入的信息來生成SQL的執(zhí)行計(jì)劃。輸入的信息包括：

（1）   統(tǒng)計(jì)信息

    統(tǒng)計(jì)信息包括兩個方面，系統(tǒng)的統(tǒng)計(jì)信息，和實(shí)際用戶數(shù)據(jù)的統(tǒng)計(jì)信息。

     系統(tǒng)的統(tǒng)計(jì)信息，推薦大家使用默認(rèn)設(shè)置。實(shí)際用戶數(shù)據(jù)的統(tǒng)計(jì)信息，最重要的是要有代表性，要能夠反應(yīng)數(shù)據(jù)的特征。

（2）   約束

    NOT NULL, PK, FK, UK等等約束，若實(shí)際數(shù)據(jù)是需要符合約束的，那么那些約束應(yīng)該存在于數(shù)據(jù)庫里面，應(yīng)該讓優(yōu)化器知道這些約束的存在。

    舉個例子。多個表做JOIN，如果某張表只是被JOIN了，比如下面這樣事兒的

    customer表只出現(xiàn)在了JOIN部分，但是并沒有出現(xiàn)在SELECTlist里面，也沒有出現(xiàn)在查詢條件里面，也沒有出現(xiàn)在GROUP BY和ORDER BY的部分里面。那么如果lineorder表上的JOIN key（lo_custkey）上存在外鍵約束的話，優(yōu)化器就會知道lo_custkey = c_custkey這個JOIN總是能夠JOIN的上，那么在實(shí)際執(zhí)行的時(shí)候就不會去JOIN customer這個表了。執(zhí)行計(jì)劃可以是下面這樣事兒的：

    您擦亮雙眼看好了么，customer表壓根兒就沒有出現(xiàn)在執(zhí)行計(jì)劃里面！您能做的最快的JOIN就是不JOIN啊哈哈哈。這種情況我們叫做JOIN elimination，發(fā)生的前提條件是相關(guān)約束的存在。

（3）   Schema設(shè)計(jì)

    Schema的設(shè)計(jì)，包括數(shù)據(jù)模型，索引，分區(qū)，壓縮，clustering（數(shù)據(jù)根據(jù)相應(yīng)的KEY值物理上存放在一起）等等，對SQL性能都有非常重要的影響。

    有些SQL里面，一個表和自己JOIN幾十次，就是因?yàn)閿?shù)據(jù)模型設(shè)計(jì)得不好導(dǎo)致的。此時(shí)若只是專注于SQL本身，能夠取得的性能提升恐怕就非常有限了。

    Schema設(shè)計(jì)是門大學(xué)問，每一個方面都可以對SQL的性能有幾個數(shù)量級的影響。想做好SQL優(yōu)化的話，您必須要將schema設(shè)計(jì)重視起來。

5.  從執(zhí)行角度優(yōu)化SQL

 
從執(zhí)行的角度去優(yōu)化SQL，主要是要考慮以下方面：

Access method，是通過索引訪問數(shù)據(jù)，還是全表掃描。

Join方法，是Nested Loop Join，Hash Join，還是Merge Join。

Join順序，是表A Join表B，再Join表C，還是反之。

并行執(zhí)行時(shí)，生產(chǎn)者進(jìn)程組和消費(fèi)者進(jìn)程組之間的數(shù)據(jù)分發(fā)方法，是hash，還是broadcast，還是其他的分發(fā)方法。

數(shù)據(jù)是否有傾斜，是否某些KEY值對應(yīng)的數(shù)據(jù)特別多，其他KEY值對應(yīng)的數(shù)據(jù)特別少。

總結(jié)
    以上幾點(diǎn)給您提供了一個SQL優(yōu)化的整體思路。整體思路總是很重要。

    那么具體的，如果一個性能差的SQL擺在您的面前，必須去優(yōu)化它，要從那里入手呢？SQL Monitor Report將會是您的好朋友，歡迎一見鐘情。后續(xù)我們會推出系列文章，舉例說明如何用SQL Monitor Report診斷SQL的性能問題，歡迎您持續(xù)關(guān)注。