RWP談SQL優化

pxbibm發表於2018-01-10

Oracle Real-World Performance團隊是一個人數不多的天才團隊,整個團隊的資料庫效能優化經驗有超過400人年。團隊成員分佈在美國,中國和歐洲,不斷的尋找和創造新的方法分析診斷當今世界業務系統的效能。Oracle Real-World Performance團隊有著很多出類拔萃的戰績,在工作中,多次將客戶系統效能提升幾十上百倍,給客戶系統效能提升1000倍或更多也並不罕見。
  在中國,業界同仁將Oracle Real-World Performance團隊簡稱為RWP團隊。目前RWP團隊在中國共有7名成員。
RWP談SQL優化

    說到SQL優化,做為讀者的您,頭腦中第一反應是什麼?索引?Hint?分割槽?引數?執行計劃?哈哈哈有被言中吧 ;-),今天我們就來談談SQL優化的整體思路,希望能夠對您有所啟發。
1. 設定一個高的目標

 如果您把一個SQL從一個小時優化到了1分鐘,您會停止工作嗎?會不會考慮是否能給它優化到1秒鐘?
   工作中,每個人都有壓力,壓力之下,很容易疏於思考。一個SQL多長時間能跑完,依賴於它跑在什麼樣的硬體和軟體環境上。一個SQL能不能跑的更快,本質上是:它是否能夠更加充分的利用硬體資源和軟體能力。
    做SQL優化,給自己設定一個高的目標非常重要!
2.  去優化那些好的SQL

 

有了高的目標,接下來,還要找到那些好的SQL進行優化。那麼,什麼是好的SQL?
(1)   有效的 SQL

    資料庫是為了執行SQL設計的,不是為了一執行就報錯的無效SQL設計的。

     如果執行一個SQL,報ORA的錯誤,那麼這是一個無效的SQL,它不應該存在於您的系統裡面,當然更不應該成為您優化的物件。

    如果執行一個SQL,報ORA的錯誤,那麼在資料庫裡面會是一個failure parse。如果您系統的AWR報告裡面有failure parse,那麼您要注意了,後果可能很嚴重。

(2)   您知道業務含義的SQL

    有很多時候,一些SQL和PL/SQL儲存過程是根本就不需要被執行的。但是由於種種原因,那些SQL和PL/SQL儲存過程存在在系統中,可能都已存在了很長時間,寫那些SQL和PL/SQL儲存過程的人可能早就跳槽了,為了所謂的“穩定”,沒有人去動那些SQL和PL/SQL儲存過程。去優化這些根本就不需要被執行的SQL和PL/SQL儲存過程當然是沒有任何意義的。


    所以,在優化任何一條SQL之前,應該首先知道那條SQL業務上的含義,確定它確實是需要被執行的,再去優化它。

 

(3)   構造好的SQL

    如果一個SQL語句裡面有IN列表,IN列表裡面有幾百個值,那麼那幾百個值,很有可能是來源於另外一個SQL,而非人工輸入。由於IN列表中值的個數有一個允許的上限,有些SQL甚至會長成下面的樣子:

RWP談SQL優化

 

    幾百幾千幾萬個值在IN列表裡面,那是不是SQL構造的不好,是不是應該先將它改成一個JOIN再去考慮其他?

 

(4)   沒有編寫錯誤的SQL

    N個表做JOIN的話,一般情況應該有N-1個JOIN條件。如果JOIN條件小於N-1個的話,就會有CARTESIAN JOIN出現,結果集裡面會有重複值。在SELECT LIST裡面加上DISTINCT,通常就可以使得SQL得到功能上正確的結果集。這就好比您去銀行取錢,實際只要取1000塊錢,可是您先取了2000塊錢,再把餘下的1000存回去,多此一舉,雖然實際結果是對的,您確實是取了1000塊錢。

    當SQL處理的資料量小的時候,這個多此一舉對於響應時間的影響並不會很大。可是當SQL處理的資料量大的時候,這個影響就會完全凸顯出來。還是那個取錢的例子,如果您實際只要取1000塊錢,可是您先取了10001000塊錢,再把餘下的10000000塊錢存回去。最後您也會得到1000塊錢,可是銀行員工為您取錢的時候數出10001000塊錢的時間,和把錢存回去的時候再數好10000000塊錢的時間,都是您辦業務的時間,您取錢的時間就會變得相當長了。

     SQL語句中WHERE條件裡面的值的資料型別,應該與相應的列的資料型別一致。否則SQL語句雖不會報錯,會隱式的用函式將那個列轉換成與相應的值的資料型別一致,去執行SQL。這種隱式資料型別轉換,可能會導致ORA-01722的錯誤,可能會導致相應的列上的索引不能被使用到,可能會導致明明可以使用分割槽裁剪但卻用不上的情況,響應時間可能差好幾個數量級。


3.  給SQL一個好的執行環境


    SQL需要在好的環境上執行才能夠效能好。那麼什麼是好的執行環境呢?

    正確的給軟體打上補丁,是打造好的執行環境的第一步。明明您都花了錢買軟體,明明人家軟體廠家都出了補丁可以讓軟體跑的更好更快,為什麼不打補丁呢?當然了,打補丁是個技術活,怎麼正確的給軟體打上補丁,肯定是要按照軟體廠家的說明來,或者諮詢軟體廠家啦。

    使用預設的init.ora引數設定,也是打造好的執行環境的重要一環。使用預設的init.ora引數設定,意味著您是按照Oracle內部研發團隊設計軟體的方法去使用它,意味著您使用的是經過Oracle內部測試團隊嚴格測試的軟體。當然了,有一些特定的應用軟體,比如Oracle的EBS,要求修改init.ora引數,這種情況是要修改,因為那些修改是經過應用軟體廠家嚴格測試過的。

    如果是因為遇到bug,需要修改某些引數做為臨時解決方案,那麼當那個bug修復之後,您應該及時將相應的引數改回去,否則後果可能也會很嚴重噢。

    另外,若隨意修改init.ora引數,可能會導致售後的問題。

4.  從資料庫設計的角度優化SQL

 
    現在Oracle資料庫軟體使用的是Cost Based Optimizer(CBO),基於成本的優化器。

    本質上來講,優化器就是一系列的演算法。優化器會接受輸入的資訊來生成SQL的執行計劃。輸入的資訊包括:


(1)   統計資訊

    統計資訊包括兩個方面,系統的統計資訊,和實際使用者資料的統計資訊。

     系統的統計資訊,推薦大家使用預設設定。實際使用者資料的統計資訊,最重要的是要有代表性,要能夠反應資料的特徵。

 

(2)   約束

    NOT NULL, PK, FK, UK等等約束,若實際資料是需要符合約束的,那麼那些約束應該存在於資料庫裡面,應該讓優化器知道這些約束的存在。

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

 RWP談SQL優化


    customer表只出現在了JOIN部分,但是並沒有出現在SELECTlist裡面,也沒有出現在查詢條件裡面,也沒有出現在GROUP BY和ORDER BY的部分裡面。那麼如果lineorder表上的JOIN key(lo_custkey)上存在外來鍵約束的話,優化器就會知道lo_custkey = c_custkey這個JOIN總是能夠JOIN的上,那麼在實際執行的時候就不會去JOIN customer這個表了。執行計劃可以是下面這樣事兒的:

 RWP談SQL優化

    您擦亮雙眼看好了麼,customer表壓根兒就沒有出現在執行計劃裡面!您能做的最快的JOIN就是不JOIN啊哈哈哈。這種情況我們叫做JOIN elimination,發生的前提條件是相關約束的存在。

 

(3)   Schema設計

    Schema的設計,包括資料模型,索引,分割槽,壓縮,clustering(資料根據相應的KEY值物理上存放在一起)等等,對SQL效能都有非常重要的影響。


    有些SQL裡面,一個表和自己JOIN幾十次,就是因為資料模型設計得不好導致的。此時若只是專注於SQL本身,能夠取得的效能提升恐怕就非常有限了。

    Schema設計是門大學問,每一個方面都可以對SQL的效能有幾個數量級的影響。想做好SQL優化的話,您必須要將schema設計重視起來。

 

 

5.  從執行角度優化SQL

 
從執行的角度去優化SQL,主要是要考慮以下方面:


Access method,是通過索引訪問資料,還是全表掃描。

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

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

並行執行時,生產者程式組和消費者程式組之間的資料分發方法,是hash,還是broadcast,還是其他的分發方法。

資料是否有傾斜,是否某些KEY值對應的資料特別多,其他KEY值對應的資料特別少。

 

總結
    以上幾點給您提供了一個SQL優化的整體思路。整體思路總是很重要。

    那麼具體的,如果一個效能差的SQL擺在您的面前,必須去優化它,要從那裡入手呢?SQL Monitor Report將會是您的好朋友,歡迎一見鍾情。後續我們會推出系列文章,舉例說明如何用SQL Monitor Report診斷SQL的效能問題,歡迎您持續關注。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12798004/viewspace-2149991/,如需轉載,請註明出處,否則將追究法律責任。

相關文章