連接查詢算是日常比較常用的數據庫關聯關鍵字涉及左外連接、右外連接、內連接三種連接方式，本文將從MySQL 8.0的角度針對連接查詢和優化進行深入解析，希望對你有幫助。

一、詳解MySQL left join

1. 關聯查詢案例介紹

我們現在有一個驅動表customer，它存儲客戶id、姓名以及出生日期，默認情況下id是主鍵，沒有任何索引，對此我們給出DDL語句：

CREATE TABLE `customer` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

customer有一張關聯表，c_id記錄著與其關聯數據的id，并用available_balance記錄客戶余額，對應DDL如下，可以看到此時我們沒有添加任何索引：

CREATE TABLE `customer_balances` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `c_id` bigint NOT NULL,
  `available_balance` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1863126107830751234 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

假設此時數據庫大約有2000w的數據，我們希望查出姓名為if2vbdr1kzk47rdmulrxix48tl2r9finmonxpl25cfrqvv7m0t的用戶的出生日期和可用余額，如果沒有記錄余額則設置為null，對應我們給出這樣一條SQL：

SELECT name,birthday from customer c 
left join customer_balances cb on c.id =cb.c_id
 WHERE name='if2vbdr1kzk47rdmulrxix48tl2r9finmonxpl25cfrqvv7m0t';

最終查詢結果如下，耗時大約是1s多一些，對于用戶而言超過200ms的延遲都是有感知的，所以針對這個查詢我們需要進行相應的優化，對此筆者以市面上常見的面經為出發點，逐步拆解并解決這道問題：

name                                              |birthday           |available_balance|
--------------------------------------------------+-------------------+-----------------+
if2vbdr1kzk47rdmulrxix48tl2r9finmonxpl25cfrqvv7m0t|2024-12-01 11:02:35|         25853253|

2. 講講join的原理

join底層關聯本質上都是基于驅動表(上面的c表)的結果到被驅動表(上面的cb表)進行循環掃描定位，這里筆者以MySQL5.7、MySQL 8兩個版本對join連接的幾種類型進行介紹：

(1) Simple Nested-Loop Join：這也就是我們上文中兩張關聯表沒有加索引關聯查詢，得到所有驅動表c的數據后，直接給cb表走全表掃描定位匹配，極端情況下要查詢count(c)*count(cb)次，也就是我們傳說中的時間復雜度為O(n^2):

(2) Index Nested-Loop Join：這就是join左右字段都加索引后的查詢，這意味著驅動表的選擇不在于我們自身，而是由MySQL優化器決定，當驅動表的結果交給被驅動表時，被驅動表直接通過索引定位到關聯數據并阻塞。

(3) Block Nested-Loop Join：沒有索引列的情況都會選擇該算法而不優先考慮Simple Nested-Loop Join，Block Nested-Loop Join相比Simple Nested-Loop Join多了一個中間操作，它會將驅動表查詢結果緩存到join buffer，與被驅動表關聯時會進行批量內存關聯與合并。

(4) HashJoin：這是8.0.18及其之后的版本對于關聯查詢的優化，其原理是針對驅動表join字段進行哈希運算生成結果集存入內存中，然后掃描被驅動表并直接通過哈希運算定位到驅動表是否存在關聯的值已完成結果合并。當然如果驅動表數據量大的話，驅動表部分數據還會利用磁盤進行分片，生成臨時文件，然后被驅動表同樣是通過哈希運算定位到磁盤分片編號進行物理磁盤IO獲取關聯結果。

3. 能不能說說這個LEFT JOIN如何加索引

上文提到查詢耗時為1s多，針對索引添加我們優先使用explain 來分析一下SQL的查詢過程：

explain SELECT c.name,c.birthday,cb.available_balance 
from customer c 
left join customer_balances cb on c.id =cb.c_id 
WHERE name='if2vbdr1kzk47rdmulrxix48tl2r9finmonxpl25cfrqvv7m0t';

以我們的SQL為例該查詢首先查詢驅動表c，它會基于where條件進行全表掃描獲取數據，基于查詢結果緩存到hash join buffer再到關聯表即被驅動表的聚簇索引進行全表掃描匹配結果：

這一點我們也可以從執行計劃看出，c表和cb表都走了全表掃描，且關聯查詢時被驅動表cb用到MySQL 8的hash join關聯，這種關聯方式本質上就說

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows   |filtered|Extra                                     |
--+-----------+-----+----------+----+-------------+---+-------+---+-------+--------+------------------------------------------+
 1|SIMPLE     |c    |          |ALL |             |   |       |   |3079319|    10.0|Using where                               |
 1|SIMPLE     |cb   |          |ALL |             |   |       |   |3447555|   100.0|Using where; Using join buffer (hash join)|

針對該執行計劃，我們進行逐步的調優，針對驅動表c的查詢，因為用到了name字段，所以針對name添加一個索引：

ALTER TABLE db.customer DROP INDEX customer_name_IDX;
CREATE INDEX customer_name_IDX USING BTREE ON db.customer (name);

經過調整之后，查詢耗時提升為0.739s，查看執行計劃，可以看到針對驅動表的慢查詢已經走索引了，現在問題就是出在被驅動表cb還是走全表掃描：

id|select_type|table|partitions|type|possible_keys    |key              |key_len|ref  |rows   |filtered|Extra                                     |
--+-----------+-----+----------+----+-----------------+-----------------+-------+-----+-------+--------+------------------------------------------+
 1|SIMPLE     |c    |          |ref |customer_name_IDX|customer_name_IDX|403    |const|      1|   100.0|                                          |
 1|SIMPLE     |cb   |          |ALL |                 |                 |       |     |4566577|   100.0|Using where; Using join buffer (hash join)|

所以我們針對被驅動表cb的c_id增加一個索引:

CREATE INDEX customer_balances_c_id_IDX USING BTREE ON db.customer_balances (c_id);

最終查詢耗時優化為0.001s，

id|select_type|table|partitions|type|possible_keys             |key                       |key_len|ref    |rows|filtered|Extra|
--+-----------+-----+----------+----+--------------------------+--------------------------+-------+-------+----+--------+-----+
 1|SIMPLE     |c    |          |ref |customer_name_IDX         |customer_name_IDX         |403    |const  |   1|   100.0|     |
 1|SIMPLE     |cb   |          |ref |customer_balances_c_id_IDX|customer_balances_c_id_IDX|8      |db.c.id|   1|   100.0|     |

4. left  join on 左右字段是否都需要加索引？為什么？

回答這個問題，我們首先需要了解左外連接的工作機制，它本質上就是基于驅動表(也就是上文的c表)的id與被驅動表cb進行鏈接，如果cb沒有數據則結果顯示null：

這也就意味著left join左邊的字段是基于where條件的查詢結果篩選出來的數據，然后遍歷并與被驅動表cb進行關聯，所以如果left join左邊（也就是我們驅動表c的id）如果不作為查詢條件的情況下，可以不加索引，當然我們本次關聯的id本身就是主鍵，所以這個問題就沒有討論的必要了。

對于left join的右邊，它是作為被驅動表(也就是我們的cb表)的關聯查詢條件，從執行計劃就可以看出如果沒添加索引，它會基于驅動表c給的關聯條件id進行全表掃描以找到符合條件的數據，所以為了提升被驅動表cb的檢索速度，關聯條件c_id是需要增加索引的。

5. 你覺得針對聯表查詢還有那些優化技巧

除了上述優化技巧，針對關聯查詢我們可以從表結構設計以及SQL查詢層面考慮優化：

• 如果業務上允許的話，可以考慮將關聯的字段冗余一份到驅動表上，直接避免關聯查詢開銷。
• 如果驅動表和被驅動都具備篩選能力(即關聯的表都可以通過where查詢到需要的數據)，可以考慮用數據量小的表作為驅動表，采用小表驅大表的方式完成關聯查詢。
• 非必要不采取left join或者right join，盡可能在關聯條件上加索引，然后通過inner join讓MySQL優化器幫我們選擇驅動表并完成數據檢索。

二、小結

在數據庫操作領域，MySQL 的 LEFT JOIN 無疑是一項極為重要的功能，它為我們提供了從多個表中獲取關聯數據的強大能力。然而，隨著數據量的不斷增長以及業務邏輯的日益復雜，LEFT JOIN 的性能問題逐漸凸顯，成為開發者和數據庫管理員需要重點關注的方面。

本文深入探討了一系列針對 LEFT JOIN 的性能優化策略。

首先，我們詳細分析了合理設計表結構對性能的巨大影響。通過確保表的主鍵、外鍵以及索引的正確設置，可以顯著減少數據庫在執行 LEFT JOIN 操作時的搜索范圍，提高查詢效率。例如，為頻繁用于連接條件的列創建合適的索引，能夠讓數據庫快速定位到相關數據，避免全表掃描帶來的性能損耗。 索引優化方面，我們了解到復合索引的巧妙運用以及避免索引失效的重要性。

復合索引可以在多個列上創建單一索引結構，從而在多條件查詢時發揮重要作用。同時，要注意查詢語句的書寫方式，避免因不當的操作符或函數使用導致索引失效，確保索引能夠在 LEFT JOIN 操作中充分發揮作用。 查詢語句的優化也是關鍵環節。我們學會了通過簡化查詢邏輯、合理利用子查詢以及使用 STRAIGHT_JOIN 等方式來引導數據庫優化器生成更高效的執行計劃。這些優化手段能夠幫助數據庫更好地理解我們的查詢意圖，合理分配資源，從而提升 LEFT JOIN 的執行速度。

此外，數據庫的配置參數對 LEFT JOIN 性能也有著不可忽視的影響。通過調整諸如內存分配、緩存大小等參數，可以為數據庫的運行提供更有利的環境，進一步提升 LEFT JOIN 的執行效率。

在實際應用中，我們應當根據具體的業務場景和數據特點，綜合運用這些優化策略。同時，持續進行性能測試和監控，及時發現并解決性能瓶頸問題。只有這樣，我們才能在充分利用 LEFT JOIN 強大功能的同時，確保數據庫系統的高效穩定運行，為業務的發展提供堅實的數據支持。希望本文所介紹的優化策略能夠幫助讀者在處理 MySQL LEFT JOIN 相關問題時更加得心應手，提升數據庫應用的整體性能和質量。