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

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

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

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

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

原理

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

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

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

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

原理

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

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

實(shí)戰(zhàn)案例

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

-- 創(chuàng)建表t1（1萬(wàn)行）和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萬(wàn)行數(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)查詢并分析計(jì)劃

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

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

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

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

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

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

原理

當(dāng)被驅(qū)動(dòng)表無(wú)索引且MySQL版本≤8.0.19時(shí)，采用BNL算法，核心是“批量匹配減少I(mǎi)O”：

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

實(shí)戰(zhàn)案例

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

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

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

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

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

2.4 無(wú)索引方案2：Hash Join（MySQL 8.0.20+）

原理

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

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

實(shí)戰(zhàn)對(duì)比

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

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

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

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

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

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

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

原理

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

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

如何開(kāi)啟BKA

BKA需手動(dòng)開(kāi)啟MRR相關(guān)參數(shù)：

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

執(zhí)行計(jì)劃關(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個(gè)核心策略

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

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

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

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

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

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

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

對(duì)于百萬(wàn)級(jí)以上數(shù)據(jù)的關(guān)聯(lián)查詢，開(kāi)啟BKA可大幅減少I(mǎi)O次數(shù)，尤其適合“驅(qū)動(dòng)表大、被驅(qū)動(dòng)表有索引”的場(chǎng)景。

4. 升級(jí)MySQL至8.0.20+

用Hash Join替代BNL，無(wú)索引場(chǎng)景下性能提升30%+，同時(shí)減少資源占用。

四、總結(jié)

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

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

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

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

最新評(píng)論