PG 數據庫中的表連接方式及應用場景

關係型數據庫的表連接最常用的有三種方式，NESTED LOOP，HASH JOIN 和 MERGE JOIN(SORT MERGE JOIN,SMJ)。在 Oracle 數據庫中，DBA 也經常被這三種 JOIN 給折騰的夠嗆，一旦優化器因爲統計信息的不準確出現了 JOIN 類型選擇錯誤，那麼對系統性能來說，就沒法看了。前陣子有朋友問我這三種 JOIN 在什麼場合使用。實際上 PG 數據庫對這三種 JOIN 的支持都還不錯，今天早上事情比較多，我就寫個簡單點的文章，利用一個 INNER JOIN 的 SQL 來給大家解釋一下這三種 JOIN 模式，並且大致分析一下其應用場景吧。

DROP TABLE JOIN1;

DROP TABLE JOIN2;

create table join1 (id integer,name varchar(300));

create table join2 (id integer,name varchar(300),score integer);

insert into join1 values

(generate_series(1,10000),

'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA');

insert into join1 values

(generate_series(50001,51000),

'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAASSSSSAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA');

insert into join2 values

(generate_series(1,10000),'

aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',1);

insert into join2 values

(generate_series(1,10000),

'aaaaaaaaaaaaaaaaAAAAAAABBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',2);

insert into join2 values

(generate_series(20001,22000),

'aaaaaaaaaaaaaaaaAACCCCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',3);

VACUUM ANALYZE JOIN1;

VACUUM ANALYZE JOIN2;

先準備兩張測試表 JOIN1/JOIN2。先看看如果沒有索引情況下是使用什麼樣的執行計劃的。

可以看出，這時候使用了 HASH JOIN，而且選擇了行數較大的表 JOIN2 來做驅動表。我們可以繼續驗證一下是不是總是選用數據量較大的表。

可以很明顯的看到，HASH JOIN 總是選擇較小的數據集來構建 HASH 表，然後用較大的數據集去做探測。這個也很容易理解，這是 HASH JOIN 的算法決定的，這樣的開銷最小。

我們在 JOIN2 上創建了一個 ID 字段的索引，並且在 JOIN1 上設置了一個篩選條件，只選取 10 條記錄，可以看到，執行計劃不再使用 HASH JOIN 而選擇 NESTED LOOP 了。因爲較少的循環，並且通過索引可以很快速的完成 JOIN。

我們再來看一個 MERGE JOIN 的問題，什麼時候會走 MERGE JOIN 呢？我們修改一下對 JOIN1 和 JOIN2 的數據篩選條件，讓二者的數據集規模相對接近，這時候我們看到使用了 MERGE JOIN。因爲 MERGE JOIN 是把兩個行源的數據分別先排序，然後將兩個排過序的行源做 JOIN，因此當二者體量比較接近的時候，使用 MERGE JOIN 是比較合適的。

上面這個例子可以看到，當二行源的數據量差異較大時，又開始使用 HASH JOIN，而不使用 MERGE JOIN 了。

另外，PG 數據庫的 MERGE JOIN 是有條件的，不支持 <> 等操作。在這方面大家也要注意。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/Q_EwUgzZgav5fLLx8lHnXw