在MySQL日常開發(fā)中，JOIN關(guān)聯(lián)查詢是高頻操作，但相同的業(yè)務(wù)需求，不同的關(guān)聯(lián)方式可能導(dǎo)致數(shù)倍的性能差異。其核心癥結(jié)在于Join算法的選擇與執(zhí)行計劃的優(yōu)化。本文將系統(tǒng)拆解MySQL中5種核心關(guān)聯(lián)查詢算法，結(jié)合實戰(zhàn)案例分析適用場景，并總結(jié)可落地的優(yōu)化策略。

一、關(guān)聯(lián)查詢的核心算法總覽

MySQL的關(guān)聯(lián)查詢本質(zhì)是“驅(qū)動表”與“被驅(qū)動表”的匹配過程，不同算法的差異體現(xiàn)在“如何高效匹配兩表數(shù)據(jù)”。先通過一張表快速掌握各算法的核心邏輯：

二、逐個拆解：5種Join算法的原理與實戰(zhàn)

2.1 被淘汰的“基礎(chǔ)款”：Simple Nested-Loop Join

原理

最樸素的關(guān)聯(lián)邏輯：遍歷驅(qū)動表（數(shù)據(jù)量m）的每一行，都去被驅(qū)動表（數(shù)據(jù)量n）做全表掃描，滿足條件則返回結(jié)果。
掃描總行數(shù) = m * n，若兩表均為1萬行，需掃描1億次，性能極差。

關(guān)鍵結(jié)論

MySQL未實際采用該算法——即使被驅(qū)動表無索引，也會用Block Nested-Loop Join或Hash Join優(yōu)化，此算法僅作為理解其他算法的基礎(chǔ)。

2.2 索引依賴型：Index Nested-Loop Join（NLJ）

原理

當(dāng)被驅(qū)動表的關(guān)聯(lián)字段有索引時，MySQL優(yōu)先選擇NLJ，流程如下：

1. 選擇“小表”作為驅(qū)動表（減少外層循環(huán)次數(shù)）；
2. 遍歷驅(qū)動表每行，提取關(guān)聯(lián)字段值；
3. 通過關(guān)聯(lián)字段的索引，快速定位被驅(qū)動表的匹配行；
4. 合并兩表結(jié)果返回。

實戰(zhàn)案例

1. 準(zhǔn)備測試數(shù)據(jù)

-- 創(chuàng)建表t1（1萬行）和t2（100行，小表）
use martin; 
drop table if exists t1; 
CREATE TABLE `t1` (
  `id` int NOT NULL auto_increment,
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`) -- 關(guān)聯(lián)字段a建索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入1萬行數(shù)據(jù)
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int; set i=1;
while(i<=10000)do
insert into t1(a,b) values(i, i); set i=i+1; 
end while;
end;;
delimiter ; 
call insert_t1();
-- 復(fù)制t1為t2，僅保留100行（小表）
drop table if exists t2; 
create table t2 like t1; 
insert into t2 select * from t1 limit 100;

2. 執(zhí)行關(guān)聯(lián)查詢并分析計劃

explain select * from t1 inner join t2 on t1.a = t2.a;

執(zhí)行計劃關(guān)鍵信息：

• 驅(qū)動表是t2（小表，explain第一行），被驅(qū)動表是t1；
• Extra字段無“Using join buffer”，說明使用NLJ算法；
• 被驅(qū)動表通過idx_a索引匹配，掃描行數(shù)極少。

關(guān)鍵結(jié)論

• NLJ的效率核心依賴被驅(qū)動表的索引，無索引則無法使用；
• 驅(qū)動表選擇“小表”可減少外層循環(huán)次數(shù)，優(yōu)化器默認(rèn)會自動選擇小表作為驅(qū)動表（可通過straight_join強(qiáng)制指定）。

2.3 無索引方案1：Block Nested-Loop Join（BNL）

原理

當(dāng)被驅(qū)動表無索引且MySQL版本≤8.0.19時，采用BNL算法，核心是“批量匹配減少IO”：

1. 將驅(qū)動表數(shù)據(jù)批量寫入join_buffer（默認(rèn)大小256KB，可通過join_buffer_size調(diào)整）；
2. 遍歷被驅(qū)動表每行，與join_buffer中所有驅(qū)動表數(shù)據(jù)對比；
3. 滿足條件則返回結(jié)果。

實戰(zhàn)案例

-- 關(guān)聯(lián)字段b無索引（t1、t2的b字段均未建索引）
explain select * from t1 inner join t2 on t1.b = t2.b;

MySQL 5.7執(zhí)行計劃關(guān)鍵信息：

• Extra字段顯示“Using join buffer (Block Nested Loop)”，確認(rèn)使用BNL；
• 掃描行數(shù) = 驅(qū)動表行數(shù) + 被驅(qū)動表行數(shù)（批量匹配減少了全表掃描次數(shù)）。

關(guān)鍵結(jié)論

• BNL比Simple Nested-Loop Join效率高，但仍需掃描被驅(qū)動表全表；
• join_buffer_size過小時，驅(qū)動表會分批次寫入緩沖區(qū)，導(dǎo)致被驅(qū)動表多次全表掃描，需合理調(diào)整。

2.4 無索引方案2：Hash Join（MySQL 8.0.20+）

原理

MySQL 8.0.20起，用Hash Join替代BNL，核心是“哈希表快速匹配”：

1. 將驅(qū)動表數(shù)據(jù)加載到內(nèi)存，構(gòu)建“關(guān)聯(lián)字段→行數(shù)據(jù)”的哈希表；
2. 逐行讀取被驅(qū)動表，通過哈希函數(shù)計算關(guān)聯(lián)字段的哈希值；
3. 查找哈希表中匹配的哈希值，對比原始數(shù)據(jù)后返回結(jié)果。

實戰(zhàn)對比

同上述BNL案例，在MySQL 8.0.25中執(zhí)行：

explain select * from t1 inner join t2 on t1.b = t2.b;

執(zhí)行計劃關(guān)鍵信息：

• Extra字段顯示“Using join buffer (hash join)”，確認(rèn)使用Hash Join；
• 無需將被驅(qū)動表數(shù)據(jù)寫入磁盤/內(nèi)存，IO次數(shù)比BNL更少，性能提升30%+。

關(guān)鍵結(jié)論

• Hash Join是無索引場景下的最優(yōu)選擇，建議將MySQL升級至8.0.20+；
• 若驅(qū)動表過大，哈希表會溢出到磁盤，需通過join_buffer_size確保哈希表在內(nèi)存中。

2.5 性能天花板：Batched Key Access（BKA）

原理

BKA是NLJ的優(yōu)化版，結(jié)合“批量處理”與“順序IO”，需滿足被驅(qū)動表有索引，流程如下：

1. 驅(qū)動表數(shù)據(jù)批量寫入join_buffer；
2. 批量將關(guān)聯(lián)字段值發(fā)送到MRR（Multi-Range Read）接口；
3. MRR按主鍵排序關(guān)聯(lián)字段對應(yīng)的主鍵ID，減少隨機(jī)IO；
4. 按排序后的主鍵批量讀取被驅(qū)動表數(shù)據(jù)，匹配后返回。

如何開啟BKA

BKA需手動開啟MRR相關(guān)參數(shù)：

-- 開啟MRR和BKA
set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
-- 驗證BKA是否生效
explain select * from t1 inner join t2 on t1.a = t2.a;

執(zhí)行計劃關(guān)鍵信息：

• Extra字段顯示“Using join buffer (Batched Key Access)”，確認(rèn)BKA生效；
• 批量處理減少索引查詢次數(shù)，MRR排序減少隨機(jī)IO，大數(shù)據(jù)量下比NLJ快2-5倍。

三、關(guān)聯(lián)查詢優(yōu)化：4個核心策略

1. 關(guān)聯(lián)字段必須加索引

這是最核心的優(yōu)化！將“無索引場景”（BNL/Hash Join）轉(zhuǎn)化為“有索引場景”（NLJ/BKA），性能提升可達(dá)10倍以上。
案例對比：

• 無索引（BNL）：select * from t1 join t2 on t1.b=t2.b，耗時0.08秒；
• 有索引（NLJ）：select * from t1 join t2 on t1.a=t2.a，耗時0.01秒。

2. 強(qiáng)制選擇小表作為驅(qū)動表

當(dāng)優(yōu)化器選擇錯誤時（如統(tǒng)計信息過時），用straight_join強(qiáng)制指定小表為驅(qū)動表：

-- 強(qiáng)制t2（小表）為驅(qū)動表
select * from t2 straight_join t1 on t2.a = t1.a;

3. 大數(shù)據(jù)量用BKA優(yōu)化

對于百萬級以上數(shù)據(jù)的關(guān)聯(lián)查詢，開啟BKA可大幅減少IO次數(shù)，尤其適合“驅(qū)動表大、被驅(qū)動表有索引”的場景。

4. 升級MySQL至8.0.20+

用Hash Join替代BNL，無索引場景下性能提升30%+，同時減少資源占用。

四、總結(jié)

MySQL關(guān)聯(lián)查詢的效率，本質(zhì)是“算法選擇”與“資源利用”的平衡：

• 有索引優(yōu)先用BKA/NLJ，核心是“索引+小表驅(qū)動”；
• 無索引優(yōu)先用Hash Join（8.0.20+），避免BNL的高IO；
• 大數(shù)據(jù)量必開BKA，通過批量處理和MRR優(yōu)化IO。

掌握這些算法原理與優(yōu)化策略，可輕松應(yīng)對90%以上的MySQL關(guān)聯(lián)查詢性能問題。

到此這篇關(guān)于MySQL Join關(guān)聯(lián)查詢的幾種實現(xiàn)方式優(yōu)化小結(jié)的文章就介紹到這了,更多相關(guān)MySQL Join關(guān)聯(lián)查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論