MySQL原理總結之左連接、右連接、內連接與Hash連接

這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了左連接、右連接、內連接與hash連接的工作原理，並分析子查詢與join的不同，根據所學提供一些工作中需要掌握的實戰技巧，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql影片教學

#一、MySQL資料庫JOIN連線

在多個表上建立索，並且多個表JOIN和子查詢語句相對比較困難。許多開發人員不自覺地認為JOIN 會降低 SQL 的效能效率，因此他們將多表 SQL 拆分為單表查詢，認為這會影響SQL執行的效率，原因是開發人員不了解JOIN實作過程。

聯接之間的表關聯使用索引進行匹配，假設表R和表S是連接的。

表R被稱為驅動表，表R中透過WHERE條件過濾的資料將在表S對應的索引上逐一查詢。如果驅動表R的資料量不大，則上述演算法非常有效。

以下三種JOIN 類型，驅動表各是哪張表：

SELECT * FROM R LEFT JOIN S ON R.x = S.x WEHRE ...
SELECT * FROM R RIGHT JOIN S ON R.x = S.x WEHRE ...
SELECT * FROM R INNER JOIN S ON R.x = S.x WEHRE ...

#1、inner join

##對於INNER JOIN，驅動表可能是表R，也可能是表S。顯示左邊右邊共有的資料。

在這種場景下，誰需要查詢的資料量越少，誰就是驅動表。我們來看下面的範例

SELECT * FROM R INNER JOIN S  ON R.x = S.x  WHERE R.y = ? AND S.z = ?

2、LEFT JOIN

#上述Left Join 來說，驅動表就是左表R；Right Join中，驅動表就是右表S。這是 JOIN 類型決定左表或右表的資料一定要查詢。

傳回包含左表中的所有記錄和右表中聯結欄位相等的記錄。即使右表中沒有匹配，也從左表返回所有的行。

SELECT * FROM R LEFT JOIN S  ON R.x = S.x  WHERE R.y = ? AND S.z = ?

3、RIGHT JOIN

#上述Left Join 來說，驅動表就是左表R；Right Join中，驅動表就是右表S。這是 JOIN 類型決定左表或右表的資料一定要查詢。

傳回包含右表中的所有記錄和左表中聯結欄位相等的記錄。即使左表中沒有匹配，也從右表返回所有的行。

SELECT * FROM R RIGHT JOIN S  ON R.x = S.x  WHERE R.y = ? AND S.z = ?

二、MySQL資料庫Hash Join

1、什麼是 Hash Join

MySQL中的第二個JOIN 是Hash JOIN，當兩個表之間的連接條件沒有索引時使用該演算法。

如果沒有連接，那麼建立索引可以嗎？

如果某些列是選擇性低的索引，則在建立索引以匯入資料時必須對資料進行排序，這會影響匯入效能；輔助索引將出現返回表的問題。如果過濾的資料量很大，直接全表掃描會更快。

對於OLAP業務查詢（OLAP 是

線上分析處理，用於資料分析，它使我們能夠同時分析來自多個資料庫系統的資訊），哈希連接是必不可少的功能。 MySQL 8.0開始支援 Hash Join演算法，加強了對 OLAP 業務的支援。

因此，如果您的查詢資料量不太大，且查詢的回應時間要求在分鐘級別，則可以使用單一實例MySQL 8.0來完成大資料的查詢。

2、Hash JOIN 工作原理

#Hash JOIN出現在MySQL 8.0的執行計畫中，Hash JOIN 掃描關聯的兩個表：首先，在掃描磁碟機表的過程中建立一個雜湊表；當掃描第二個表時，將在雜湊表中搜尋每個關聯記錄。如果找到，將傳回記錄。

雜湊連接選擇驅動程式表和巢狀循環連接演算法，兩者基本上相同。兩個較小的表都用作驅動程式表。如果驅動器表很大，並且創建的哈希表超過了內存大小，MySQL將自動將結果轉儲到磁碟。

三、子查詢

我發現相當多的開發同學，包括我自己更喜歡寫子查詢，而不是傳統的JOIN語句。

子查詢的邏輯非常清晰。雖然 JOIN 也可以滿足需求，但這並不容易理解，因為LEFT JOIN是一種代數關係，子查詢更傾向於從人類思維的角度來理解。

然而，在MySQL 8.0中，優化器會自動將 in 子查詢最佳化為 JOIN 執行計劃，這將顯著提高效能。

我們只需要專注於SQL執行計劃，如果兩個執行計劃相同，則效能沒有差異。

在MySQL 8.0之前，MySQL沒有完全最佳化子查詢。因此，您將在子查詢的執行計劃中看到DEPENDENT SUBQUERY的提示，這表示它是一個從屬子查詢，子查詢需要依賴外部表的關聯。 DEPENDENT SUBQUERY的執行速度可能非常慢，而且大多數時候您需要手動將其轉換為兩個表之間的連接。

所以這裡部落客提示大家，如果你的目前的MySQL 8.0版本可以寫子查詢，因為子查詢的最佳化相當完整;

對於MySQL 8.0 之前版本的MySQL，需要查看所有子查詢的SQL 執行計劃。對於 DEPENDENT SUBQUERY 的提示，要進行最佳化，否則將對業務產生重大效能影響；DEPENDENT SUBQUERY的最佳化通常會重寫為用於表格連接的衍生表。

推薦學習：mysql影片教學

#

以上是MySQL原理總結之左連接、右連接、內連接與Hash連接的詳細內容。更多資訊請關注PHP中文網其他相關文章！