【演講實錄】RWP團隊談SQL最佳化，實錄rwp團隊sql

【演講實錄】RWP團隊談SQL最佳化，實錄rwp團隊sql

說到SQL最佳化，做為讀者的您，頭腦中第一反應是什嗎？索引？Hint？分區？參數？執行計畫？哈哈哈有被言中吧 ，今天我們就來分享一下，在第七屆資料技術嘉年華上，來自Oracle的曲卓分享的有關SQL最佳化的整體思路，希望能夠對大家有所啟發。

作者簡介：

曲卓（Christine），中國Oracle RWP部門經理，Oracle資料庫效能最佳化專家，對於Oracle資料庫效能最佳化有著非常深刻的理解，為眾多國內外客戶解決過棘手的效能問題。

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之前，應該首Crowdsourced Security Testing道那條SQL業務上的含義，確定它確實是需要被執行的，再去最佳化它。

 

（3）構造好的SQL

如果一個SQL語句裡面有IN列表，IN列表裡面有幾百個值，那麼那幾百個值，很有可能是來源於另外一個SQL，而非人工輸入。由於IN列表中值的個數有一個允許的上限，有些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了，比如下面這樣事兒的

 

customer表只出現在了JOIN部分，但是並沒有出現在SELECTlist裡面，也沒有出現在查詢條件裡面，也沒有出現在GROUP BY和ORDER BY的部分裡面。那麼如果lineorder表上的JOIN key（lo_custkey）上存在外鍵約束的話，最佳化器就會知道lo_custkey = c_custkey這個JOIN總是能夠JOIN的上，那麼在實際執行的時候就不會去JOIN customer這個表了。

執行計畫可以是下面這樣事兒的：

您擦亮雙眼看好了麼，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值對應的資料特別少。

隨著技術的發展，資料在企業中的價值日益凸顯，由ACOUG和雲和恩墨主辦的資料技術嘉年華，圍繞資料及資料庫領域的核心技術，分享前沿資訊、乾貨技術，企業變革之路與戰略方向，邀你一起探索資料價值，共創未來！ 第八屆資料技術嘉年華將於2018年11月16日盛大開幕，精彩等你來！

相關閱讀：

資料為橋邁向智能，渤海財險資料架構智能化演化

金融行業的智能化雲化探索與最佳實務

中國電信的Oracle Sharding架構應用案例分析

從AT&T到青海移動的多租戶資料整合實踐

美團點評資料庫高可用架構的演化與設想

資源下載

關注公眾號：資料和雲（OraNews）回複關鍵字擷取

‘2017DTC’，2017DTC大會PPT

‘DBALIFE’，“DBA的一天”海報

‘DBA04’，DBA手記4經典篇章電子書

‘INTERNALS’，Oracle RAC PPT

‘122ARCH’，Oracle 12.2體繫結構圖

‘2017OOW’，Oracle OpenWorld資料

‘PRELECTION’，大講堂講師課程資料