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

qing_yun發表於2023-02-01

最近很多朋友都在搞信創資料庫遷移,從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;

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

來自 “ 白鱔的洞穴 ”, 原文作者:白鱔;原文連結:https://mp.weixin.qq.com/s/34DlP6J_BSFTkm5JMjf9UA,如有侵權,請聯絡管理員刪除。

相關文章