聊聊國產資料庫遷移中的表連線效能問題

最近很多朋友都在搞信創資料庫遷移，從Oracle遷移到國產或者開源資料庫後，最大的抱怨就是效能下降太厲害，這好像和一些廠商宣稱的效能不太符合。不過有時候問題也不像一些抱怨那麼嚴重。大多數遇到效能下降的問題都是執行計劃的問題，都是可以解決的，不過也有一些在Oracle上執行計劃很好的SQL，遷移到了國產資料庫或者開源資料庫上後，怎麼調整都調不好。在開始搞信創資料庫後，SQL最佳化將成為一件大事情。

實際上SQL最容易出問題的地方還是表連線，單表的訪問，哪怕走錯了索引，SQL跑的慢點了，影響還沒那麼嚴重。表連線的執行計劃出問題了，可能在Oracle上秒出的SQL，幾個小時都跑不出來。這兩天寫了一些雲山霧罩的文章，今天就寫點輕鬆的，和大家聊聊表連線。表連線有很多種，內連線、左外連線、右外連線、全外連線、半連線(SEMI JOIN)、ANTI JOIN等。把這些連線搞清楚，把這些連線採用的連線模式搞準確，那麼SQL跑起來也不至於那麼讓人沮喪了。

為了更好的解釋今天的內容，我首先建立了兩張測試表。我們今天的測試是在一個PG 12.3核心的資料庫上進行。首先我們建立兩張測試表，然後我們再來看看各種表連線的情況。

內連線是最常用的一種，是取兩表的交集資料。典型的語法是：SELECT count(*) FROM join1 j1 INNER JOIN join2 j2 ON j1.id = j2.id;。

如果對兩張表不加篩選條件，對上萬條資料的JOIN，最佳的執行方式是HASH JION。從執行計劃上也可以看出這一點。Inner join的等價SQL是：SELECT count(*) FROM join1 j1,join2 j2 where j1.id = j2.id;

如果我們把這條語句稍微改一下，改成SELECT count(*) FROM join1 j1,join2 j2 where j1.id <> j2.id;我們會發現這條語句的執行效率下降的很厲害，從8.3毫秒變為將近40秒了。從執行計劃上看，沒有使用HASH JOIN，而是使用了效能較差的NESTED LOOP。如果在某個國產資料庫中因為這種情況存在無法使用HASH JOIN引發應用效能問題，在語義等價的情況下，看看是否能夠使用NOT IN或者NOT EXISTS來改寫，參考下面的例子。

左外連線返回的結果是整個左行源加上二者交集的部分。典型的SQL是SELECT count(*) FROM join1 j1 LEFT JOIN join2 j2 ON j1.id = j2.id;

不過我們從執行計劃上看，這條SQL並沒有選擇left join的執行計劃，而是選擇了以JION2表為驅動表的右外連線。最佳化器認為這個執行計劃效率更高。

右外連線和左外連線類似：SELECT count(*) FROM join2 j1 RIGHT JOIN join2 j2 ON j1.id = j2.id;

和上面的例子類似，最佳化器認為改為左外連線效果更好。

半連線SEMI JOIN和LEFT JOIN是SQL語句中兩種不同的連線型別，SEMI JOIN，該語句只返回左表中包含右表中的行，並且僅返回一次。而LEFT JOIN返回左表的所有行，如果右表中沒有匹配的行，則返回NULL值。其典型的SQL是：SELECT count(*) FROM join1 j1 where exists (SELECT id FROM join2 j2 WHERE j1.id = j2.id);

ANTI JOIN是排除右表的資料。典型SQL是：

SELECT count(*) FROM join1 j1 where not exists (SELECT id FROM join2 j2 WHERE j1.id = j2.id);

還有一些連線模式，比如全外連線等，我們今天就不做討論了。最後再給大家介紹一個從Oracle資料庫遷移到國產開源資料庫上最容易出效能問題的SQL：select j1.* from join1 j1,join2 j2 where j1.id=j2.id or j1.id=100;。

這條SQL的特點是在條件裡出現了Or條件，在Oracle中這條SQL的執行效率是沒問題的，不過在很多國產、開源資料庫中效能就出問題了。遇到這種情況，改寫SQL是最終解決方法。如果在等價的情況下，我們可以用union來改寫這條SQL。

select j1.* from join1 j1,join2 j2 where j1.id=j2.id

union

select j1.* from join1 j1 where j1.id=100;

今天早上寫了一半有客戶過來，下午又要開始出差了，今天就先寫到這裡吧。有些問題只是提出來了，並沒有做深入的分析，只是給大家提了一個思考的方向，如果有興趣的朋友可以再去深入分析一下。解決了這些問題，國產資料庫替代的工作就會順利很多。