MySQL JOIN底层详解：NLJ嵌套循环、BNL块循环、Hash Join哈希连接彻底吃透

MySQL 所有多表关联底层只有三种JOIN算法NLJ嵌套循环连接、BNL块嵌套循环、Hash Join哈希连接。一、核心前置概念必懂1.1 驱动表 被驱动表驱动表外层表外层循环、数据量更小的表优先遍历被驱动表内层表内层匹配、数据量更大的表负责精准匹配数据优化铁律永远用小表驱动大表最大限度减少内层循环匹配次数。1.2 算法版本边界MySQL 5.7及更早仅支持 NLJ BNL 两种算法MySQL 8.0.18正式支持 Hash Join彻底替代低效BNL算法二、NLJ 嵌套循环连接Index Nested-Loop JoinNLJ是MySQL默认最优JOIN算法也是生产中小表JOIN的首选核心依赖索引提速。2.1 核心原理两层循环 索引快速匹配。遍历驱动表每一行拿着关联字段去被驱动表索引树上精准查找匹配数据。2.2 完整执行流程选取小表作为驱动表读取驱动表所有数据行遍历驱动表每一行取出JOIN关联字段值通过关联字段走被驱动表索引精准定位匹配行匹配成功拼接两表数据返回结果集2.3 复杂度与性能时间复杂度O(n * log m)n驱动表行数小表数值小log m索引树查找开销极快2.4 适用场景被驱动表JOIN字段有索引主键、唯一索引、普通索引均可。2.5 优缺点总结✅ 优点性能极高、CPU开销小、IO极低、稳定高效❌ 缺点强依赖索引无索引直接失效降级为BNL算法,驱动表数据量大的时候,被驱动表频繁回表查询,增加消耗三、BNL 块嵌套循环连接Block Nested-Loop Join当JOIN字段没有索引时MySQL无法使用NLJ会降级为BNL块嵌套循环是5.7版本无索引JOIN的唯一方案。3.1 核心原理利用join_buffer_size 连接缓冲区批量缓存驱动表数据减少被驱动表全表扫描次数。避免「一行驱动、一次全表扫描」的极致低效问题。3.2 执行流程读取驱动表数据批量放入Join Buffer内存缓冲区全表扫描被驱动表逐行与缓冲区中所有数据比对匹配成功则拼接返回一轮缓冲区匹配完成后继续加载下一批驱动表数据3.3 核心参数join_buffer_size默认256KB控制JOIN缓冲区大小3.4 致命缺点生产大坑无索引加持必须全表扫描被驱动表内存批量比对CPU疯狂飙升大表JOIN场景下性能极差极易拖垮数据库驱动表的数据大于JOIN缓冲区大小的时候,会多次扫描被驱动表,增加IO压力3.5 适用场景仅适用于小表无索引临时关联生产严禁大表使用。四、Hash Join 哈希连接MySQL8.0神器MySQL8.0.18 重磅推出彻底替代BNL解决无索引大表JOIN性能灾难是大数据量等值JOIN的最优解。4.1 核心原理小表建哈希表大表逐行哈希匹配。摒弃循环比对用哈希表O(1)查询特性把多层循环降级为近似线性遍历。4.2 完整执行流程两步核心第一步Build 构建阶段读取较小的驱动表构建表以JOIN关联字段为key在内存中构建哈希表存储字段值与完整行数据。第二步Probe 探测阶段逐行扫描较大的被驱动表探测表计算当前行关联字段哈希值直接在内存哈希表中匹配命中则拼接返回结果。4.3 性能优势时间复杂度O(nm)近乎线性遍历无需索引、无需多次全表扫描彻底解决BNL高CPU、高IO问题4.4 严格使用限制面试必考Hash Join 仅支持等值连接不支持、、like、范围JOIN、非等值关联4.5 适用场景大表JOIN大表、无索引等值关联批量数据关联、统计分析、报表查询替代低效BNL算法大幅提升无索引JOIN性能五、三种JOIN算法终极对比生产面试算法版本支持依赖条件性能适用场景NLJ 索引嵌套循环5.7/8.0全支持被驱动表JOIN字段有索引极高日常OLTP小表关联、等值/范围JOINBNL 块嵌套循环5.7/8.0全支持无索引、缓冲区缓存数据极差废弃、仅小表临时关联Hash Join 哈希连接8.0.18等值JOIN、无索引极高大表无索引等值关联、报表统计六、MySQL JOIN算法选型规则优先NLJ只要JOIN字段有索引MySQL优先选择NLJ性能最优无索引看版本8.0自动走Hash Join5.7强制走BNL必慢非等值JOIN无论版本只能走NLJ/BNL无法使用Hash Join大表关联优先升级8.0彻底解决无索引JOIN性能瓶颈七、线上JOIN慢查询根治方案优先加索引给JOIN关联字段建立索引强制走NLJ算法最优解升级MySQL8.0大表无索引关联启用Hash Join替代BNL严格小表驱大表减少外层循环次数从算法层面降开销禁止大表非等值JOIN极易触发全表扫描、CPU打满控制join_buffer_size避免缓冲区过大占用过多内存资源八、面试背诵版150字MySQL JOIN底层包含三种算法。NLJ索引嵌套循环是默认最优算法依赖被驱动表索引小表驱动大表通过索引精准匹配性能高效适用于日常等值与范围JOIN。无索引场景下5.7版本降级为BNL块嵌套循环通过连接缓冲区批量比对CPU与IO开销极大、性能极差。MySQL8.0.18推出Hash Join仅支持等值连接通过小表构建哈希表、大表哈希探测匹配大幅优化大表无索引JOIN性能彻底替代BNL算法。九、全文终极口诀有索引走NLJ小表驱动效率佳无索引57崩BNL循环比对CPU炸八零新增哈希JOIN等值匹配秒应答大表关联升版本索引优先永不差。