優秀的優化器是在實踐中磨練出來的
?在和一些國產數據庫廠商的朋友交流的的時候，總能聽到他們說自己的優化器是高手設計出來的，充分利用了現代軟硬件技術，因此與Oracle相比只強不弱。我不太贊成這樣的說法，因為一個優秀的優化器設計能夠做到的只是在大的框架上比較不錯，針對一些常規的SQL語句比較有效，而SQL語句的復雜性往往遠遠超出數據庫設計人員的想象，我們的數據庫廠商也往往低估了開發人員寫SQL的能力。那些天馬行空的神來之筆，會讓再優秀的優化器都感到力不從心。
前些年一個朋友在做一個數據庫遷移的時候遇到一條SQL的性能問題，這條SQL在Oracle上執行的效率很不錯，但是到了一個基于PG的國產數據庫上，就慢得讓人受不了了。我們通過一個簡單的測試案例來復現這個問題。
用戶現場是一個內網系統，因此我們只能采用模仿的方式來給大家復一復盤。我們用dba_objects和dba_tables兩個系統視圖來創建兩張物理表。然后執行這條語句：
Oracle DBA看到這條SQL會覺得十分不解，為啥能寫出這樣的SQL語句來呢？程序員的大腦DBA是很難理解的。就是不知道我們的數據庫廠商懂不懂了。不過實際應用場景中我們確實經常遇到這樣的奇葩SQL。
我們在PG數據庫上做一個類似的測試用例，我們使用PG_TABLES、PG_INDEXES這兩個視圖來創建t1/t2表。
保險起見，建完表后我們做一次vacuum analyze。然后看看這條SQL的執行計劃如何：
這條SQL貌似執行速度還行，不過實際上真實環境的數據是不同的。我們從執行計劃上來看看會有些什么問題。首先在T2表上是做了一個根據掃描，查到一個數組，這個被定義為SubPlan1，然后對T1表做掃描，通過SubPlan1的結果做過濾，獲得最終的數據。這個執行計劃的問題實際上是十分明顯的，當T1/T2表很大的時候，這個查詢會變得很慢。比如我們增加T2的大小到幾萬條記錄。
可以看到，PG的執行計劃變成了在T2表上通過索引掃描，這是優化器做了有效的優化。我們用同樣的方法擴大T2表，到幾十萬條記錄，看看會有什么情況。
執行計劃還是如此，而執行時間已經加大到400多毫秒了。如果數據庫的IO性能有點問題，并且t1表十分巨大，那么這個執行計劃肯定就會有問題了。實際生產環境中就是因為數據量較大，才出現了性能問題。
我們再來看看Oracle的執行計劃，可以看出這兩個執行計劃之間的差異是很大的。
Oracle的執行計劃采用了一個Hash 半連接，通過兩次索引掃描獲得半連接的兩個半區數據，然后用HASH UNIQUE探測內表數據。做一個10053 trace我們可以看到，Oracle在編譯這條SQL的時候，做了多種FPD和轉換的分析，最終才找到了這個最優解。如果對這個分析過程感興趣的朋友可以自己做個10053看看，這里篇幅有限我就不做詳細的介紹了，整個trace文件接近6000行。
這個執行計劃可以說是沒有太大毛病的，通過兩個索引避免了兩張大表的全表掃描，通過Hash半連接確保了整個JOIN的總體規?？煽?。
一個優秀的數據庫產品，其優化器一定會隨著應用規模的擴大，遇到的奇葩SQL越多而變得越來越強大的。如果我們總是告訴用戶，你不應該這么寫SQL，而不從優化器的角度去解決這些奇葩SQL的性能問題，那么我們的進步就會變得太慢，我們與Oracle的技術差距就會越來越大。
對于這個案例，前陣子我正好和一家國產數據庫廠商做過一些交流。他們的老版本中的執行計劃也不是很好。
當時我和廠商的朋友分析他們的執行計劃的時候，我認為雖然在T2的TABLE SCAN上做了LIMIT(1)的過濾，但是如果符合條件的記錄位于一張大表的最后幾行，那么這個掃描的成本會很高。并且最致命的是Nested loop Join Cartestan這個算子，如果T1符合條件的數據比較多，那么這條SQL的執行效率將會特別低，甚至幾個小時執行不出來。
最近我測試了他們的最新版本的產品，讓我感到了新版本在優化器方面的能力提升還是比較大的。
當表的數據量不大的時候，執行計劃通過對兩個索引的掃描，然后做MERGE半連接。
數據量較大的時候，執行計劃改走了Hash 右半連接，與Oracle的執行計劃不同的是，對較小的表T1采用了全表掃描的模式。
雖然在這個執行計劃上還有一些可以商榷的地方，不過不同的數據庫因為對全表掃描的成本的計算不同，因此可能會有不同的選擇。從兩個版本的執行計劃的優化效果上，我們也看到了國產數據庫在核心能力方面的進步。這種進步恐怕只能在不斷的實踐中才能磨練出來。因此我們也有理由相信，隨著數據庫信創工作的不斷深入，我們的數據庫產品也會越來越好的。?
