MySQL和PostgreSQL在多表連線演算法上的差異

以下文章來源於資料庫架構之美   ，作者資料庫架構之美

我們知道mysql 沒有 hash join ，也沒有 merge join ，所以在連線的時候只有一種演算法 nest loop join ， nl join 使用驅動表的結果集作為外表到內表中查詢每一條記錄，如果有索引，就會走索引掃描，沒有索引就會全表掃。

nl join 並不能適用所有場景，例如兩個表都是很大的表的等值連線，這種場景是 hash join 所擅長的，而且是生產環境中最常見的場景。 mysql 在這個時候就顯得力不從心，所以在使用 mysql 時我們可能會制定如下規範：禁止使用大表連線。這也是 mysql 永遠的痛。不過據說 8.0 版本已經將 hash join 作為一個需求納入了，我們拭目以待吧。

 

相比起來，postgresql 的最佳化器十分的強勁。支援了 hash join 、 nest loop 、 sort merge join ，掃描演算法支援 seq scan 、 index scan 、 index only scan ，同時還支援堆內元組技術（ HOT ）。在 postgresql11 版本中還加入了並行掃描，親測在兩張大表（一張 1.6 億一張 256 萬資料，均無索引）做 join 結果集 300 多萬， pg 開啟並行大概 20s 以內就跑出結果，強於其他資料庫。

 

上面討論了兩表join 的演算法，下面看看多表 join 時 mysql 和 pg 是如何處理的。多表 join 其實涉及到一個問題：如何找到代價最小的最優路徑。為什麼會有這個問題呢？因為在多表連線時，每兩個表之間連線具有一個代價值，最佳化器會根據代價估算調整不同表 join 的順序，最後算出一個最優或者近似最優代價，使用這個代價生成執行計劃，這樣就涉及到圖論中的最短路徑問題，不同的連線順序組合代表了圖的遍歷，最優代價其實就是求無源圖的最短路徑問題。我們知道兩種主流的最短路徑演算法是迪傑斯特拉（Dijkstra ）演算法和弗洛伊德（floyd ）演算法，這兩種演算法也是動態規劃中的經典演算法。

 

在mysql 中計算最優代價使用貪心演算法，而 pg 使用的是動態規劃。

 

Mysql ：

Mysql 連線使用貪心演算法，下面這個圖表明瞭貪心演算法的過程：

 

貪心演算法的前提是確定源點，演算法思想也和名字很像，只找當前步驟的最優解，是一種深度優先的解法，演算法複雜度是O （ n ²）找到後繼續深入下一層，直至達到終點。比如上圖從 A 到 G ，使用貪心演算法的路徑是 A->B->D->G 演算法，代價是 1+2+6=9 ，很明顯這並不是最優解，最優解我們肉眼可以看出來是 A->C->F->G ，代價是 2+3+1=6 。所以我們看貪心演算法並不是全域性最優的，但是優點是演算法複雜度低， mysql 可能也是基於這種考慮而使用貪心演算法，不想將時間都浪費在計算代價上了，因為如果關聯的表特別多，那麼代價的計算是指數級增長，所以貪心演算法雖然不是最優解，但是在連線表的數量很大的情況下具有一定優勢。

 

Postgresql ：

再來看看pg 使用的動態規劃，動態規劃解決的是無源最短路徑問題，我們想象一下其實多表連線本身就是一個無源最短路徑問題，只是 mysql 在進行連線的時候隨機選了一個作為起點而已。

 

動態規劃的思想是將問題分解為子問題，將問題遞推為子問題進行解決。以floyd 演算法為例。演算法使用鄰接矩陣來表示每個點之間的距離，如果沒有連線，則代表無窮大。比如下面這個圖：

 

弗洛伊德演算法使用矩陣記錄節點直接距離，它的強大之處在於它經過若干次計算後得到任意兩個節點直接的最短距離，是真正意義上的無源最短路徑演算法，但是它的演算法複雜度也比較高，是O （ n ³）。下面介紹一下該演算法，演算法的核心思想是如果 a[ij]>a[ik]+a[kj] ，那麼 a[ij]=a[ik]+a[kj] ，對於每兩個節點 ab 之間的距離，如果存在第三個中間節點 c 使得 acb 的距離更短，那麼 ab 的距離使用 acb 代替，並更新到矩陣。這樣的遍歷過程我們大致就理解了需要三層迴圈，裡面的兩層迴圈是對於 ab 、 ac 、 ad...de 總共（ n-1 ） * （ n-1 ）種選擇（自己對自己的距離不用計算）計算每個中間節點（最外層迴圈）的距離是否更小。矩陣計算過程如下：

 

對於第一行，依次計算 ab ， ac ， ad ， ae 的距離是否有第三個節點進行替換，對於 ab 計算發現， ab<ac+cb&&ab<ad+db&&ab<ae+eb ，所以 ab 不用更新，同理 ac 也不用更新，對於 ad ，計算得到 ab+bd=6 ， ac+cd= ∞， ae+ed= ∞，於是更新 ad=6 ，同理計算更新 ae=8 ；然後依次計算下面幾行。全部遍歷完，經歷了三層迴圈，演算法複雜度是 O （ n ³）。 pg 使用該演算法能夠得到最優執行計劃，但是在表的個數很多時計算代價所付出的代價也很大。

 

綜上，mysql 使用貪心演算法只能得到區域性最優執行計劃，但是計算最優解所消耗的代價較小，而 pg 使用動態規劃能夠得到最優執行計劃，但是計算最優解演算法複雜度較高，代價較大。但是總體上 mysql 的最佳化器相比 pg 還是有很大差距， pg 的最佳化器甚至引入了基因演算法，有很多比較學術的考量，當得起學術派資料庫的稱號，也希望 mysql 能夠越來越好吧。

 

活動預告

---

2019 資料技術嘉年華來啦！現場大咖雲集，與你共暢資料的魅力。現在加入，盡享早鳥票價優惠：