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

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

程式設計師必備介面測試偵錯工具：

推薦學習：

一、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原理總結之左連線、右連線、內連線與Hash連線的詳細內容，更多請關注TW511.COM其它相關文章！