MySQL JOIN 原理与优化：Nested Loop、BKA、Hash Join 一次讲透

个人主页 for_ever_love__ 欢迎各位大佬莅临其他栏目: 大模型开发从0到1 其他栏目: iOS项目总结大全 其他栏目: 我想学python了 其他栏目: iOS UI 文章目录MySQL JOIN 原理与优化Nested Loop、BKA、Hash Join 一次讲透一、连接语义和连接算法是两回事二、准备可运行的实验表三、驱动表、被驱动表与“嵌套循环”3.1 INNER JOIN 通常可以重排3.2 外连接会限制重排但也可能被简化四、算法一Index Nested-Loop Join4.1 eq_ref、ref、range 说明什么4.2 连接列类型必须兼容五、BKA把大量随机探测批量化六、旧版算法Block Nested-Loop Join6.1 版本差异必须记住七、算法三Hash Join7.1 Hash Join 不是“不需要索引”八、LEFT JOIN 慢的真正原因九、ON 与 WHERE外连接最容易踩的坑十、反连接找“没有订单的客户”十一、一对多放大索引也救不了错误粒度十二、如何读 JOIN 执行计划十三、优化顺序与常见误区十四、小结MySQL JOIN 原理与优化Nested Loop、BKA、Hash Join 一次讲透JOIN慢真正的问题通常不是“连接表太多”而是候选行太多、被驱动表缺少有效访问路径或基数估算错了。MySQL 5.7 与 8.0 的连接算法也有明显变化旧版常见 BNL8.0.18 引入 Hash Join8.0.20 起后者接管了 BNL 的主要场景。本文从驱动表开始拆解索引嵌套循环、BKA/BNL 与 Hash Join并用执行计划说明怎样优化。一、连接语义和连接算法是两回事先区分两个维度。维度常见类型决定什么连接语义INNER/LEFT/RIGHT/CROSS哪些匹配或未匹配行要保留连接算法Nested Loop、BKA、BNL、Hash JoinMySQL 怎样找到并组合这些行LEFT JOIN不是一种算法它只规定左表未匹配的行也要保留右侧列补NULL。同一个LEFT JOIN在不同索引和数据分布下可能采用不同物理算法。二、准备可运行的实验表CREATEDATABASEIFNOTEXISTSjoin_demoDEFAULTCHARACTERSETutf8mb4;USEjoin_demo;CREATETABLEcustomer(idBIGINTUNSIGNEDPRIMARYKEY,tenant_idBIGINTUNSIGNEDNOTNULL,nameVARCHAR(50)NOTNULL,levelTINYINTUNSIGNEDNOTNULL,KEYidx_tenant_level(tenant_id,level,id))ENGINEInnoDB;CREATETABLEorders(idBIGINTUNSIGNEDPRIMARYKEY,tenant_idBIGINTUNSIGNEDNOTNULL,customer_idBIGINTUNSIGNEDNOTNULL,statusTINYINTUNSIGNEDNOTNULL,amountDECIMAL(12,2)NOTNULL,created_atDATETIMENOTNULL,KEYidx_tenant_customer(tenant_id,customer_id),KEYidx_tenant_status_time(tenant_id,status,created_at))ENGINEInnoDB;插入少量演示数据INSERTINTOcustomerVALUES(1,1001,Alice,3),(2,1001,Bob,2),(3,1001,Carol,1),(4,2001,David,3);INSERTINTOordersVALUES(101,1001,1,1,500.00,2026-09-01 10:00:00),(102,1001,1,1,800.00,2026-09-02 10:00:00),(103,1001,2,0,200.00,2026-09-03 10:00:00),(104,2001,4,1,900.00,2026-09-04 10:00:00);典型连接SELECTc.id,c.name,o.idASorder_id,o.amountFROMcustomerAScJOINordersASoONo.tenant_idc.tenant_idANDo.customer_idc.idWHEREc.tenant_id1001ANDc.level2;三、驱动表、被驱动表与“嵌套循环”连接必须先从某个输入取得行再到另一个输入寻找匹配这两个角色常称为驱动表outer input先产生候选行被驱动表inner input针对外层行寻找匹配。最朴素的伪代码是for each row c in filtered_customer: for each row o matching (c.tenant_id, c.id): emit(c, o)对于三表连接前两表的中间结果又成为下一层的外部输入。因此不能只看原表大小真正重要的是经过各自过滤后的候选集大小。表原始行数WHERE 后行数角色判断价值customer100 万20很适合先产生候选行orders10 万8 万即使原表更小也未必适合驱动“小表驱动大表”只是简化记忆更准确的说法是让低成本、低基数的输入尽早参与连接。3.1 INNER JOIN 通常可以重排对内连接优化器一般可根据成本改变表的连接顺序SQL 中谁写在前面并不等于谁先执行。EXPLAINSELECT...FROMcustomerAScJOINordersASoON...WHERE...;传统EXPLAIN中同一查询块通常按输出顺序展示访问次序MySQL 8.0 的树形计划更直观EXPLAINFORMATTREESELECT...;3.2 外连接会限制重排但也可能被简化LEFT JOIN要保留左表未匹配行因此重排空间通常小于内连接。不过如果WHERE对右表提出拒绝 NULL 的条件外连接可能被优化成内连接。SELECTc.id,o.idFROMcustomerAScLEFTJOINordersASoONo.customer_idc.idWHEREo.status1;右表补出的NULL无法通过o.status 1所以未匹配左表行最终不会保留。若业务只需要有已支付订单的客户直接写INNER JOIN更清楚。四、算法一Index Nested-Loop Join当被驱动表连接列有可用索引时外层每产生一行就执行一次索引探测。KEYidx_tenant_customer(tenant_id,customer_id)连接条件恰好提供联合索引的完整等值前缀ONo.tenant_idc.tenant_idANDo.customer_idc.id近似成本可理解为扫描外层候选行 外层行数 × 内层索引探测成本 匹配行回表成本假设外层只有 100 行每次通过 BTree 定位少量订单代价通常可控。如果外层有 100 万行即使每次探测很快累计也可能很慢。4.1 eq_ref、ref、range 说明什么type典型含义连接场景const主键/唯一键等值且可当常量单行表访问eq_ref对每个外层组合最多匹配一行完整非 NULL 唯一键连接ref对每个外层值可能匹配多行普通索引等值连接range扫描一个或多个索引范围范围连接或过滤ALL全表扫描无有效路径或扫描更便宜eq_ref不一定总比ref“优化得更好”它也表达了数据约束连接键在内层唯一。若业务上本应一对一应使用唯一约束而不是只追求执行计划标签。4.2 连接列类型必须兼容-- 推荐两边都是 BIGINT UNSIGNEDorders.customer_idcustomer.id常见问题包括差异风险数字列连接字符串列隐式转换索引利用受限SIGNED对UNSIGNED范围语义不一致utf8mb4对其他字符集字符集转换不同 collation比较规则和转换成本变化字符长度差异很大索引空间与比较成本增加先统一模型再谈 Hint。很多“明明有索引却没用”的连接根因就在字段定义不一致。五、BKA把大量随机探测批量化Batched Key Access 仍属于索引连接优化。它把一批外层连接键放入 Join Buffer再配合 Multi-Range Read 批量访问内层索引尽量把随机回表变得更有顺序。逐行 INLJ外层行 → 索引探测 → 回表 → 下一行 BKA收集一批键 → 批量索引访问 → 按更友好的顺序取数据BKA 更可能在以下场景受益外层候选行不少内层连接列有二级索引查询需要内层非覆盖列存在大量回表访问主键较分散随机 I/O 成本明显。相关开关可在会话中实验SETSESSIONoptimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson;不要看到 BKA 就全局照抄配置。若查询已被覆盖索引满足、数据全在内存批处理的额外管理成本未必划算。六、旧版算法Block Nested-Loop Join当被驱动表没有可用连接索引时最坏的简单嵌套循环会对每个外层行扫描一次内表。外层 M 行 × 内层 N 行 ≈ M × N 次比较BNL 的改进是把一批外层行的必要列放入 Join Buffer只扫描一次内表并与整批数据比较。若 Buffer 放不下全部外层数据则分多个批次内表也要重复扫描。方式内表扫描方式主要问题简单 NLJ、无索引每个外层行扫一次内表重复 I/O 极大BNL每批外层行扫一次内表仍需大量比较可能重复扫描INLJ每个外层行做索引探测依赖内层索引join_buffer_size是每个可用连接缓冲区的大小不是全局共享的一块固定内存。复杂查询和高并发下盲目调大可能造成显著内存压力。6.1 版本差异必须记住MySQL 版本无索引等值连接的典型优化5.7常见 Block Nested-Loop8.0.188.0.19开始支持 Hash Join能力仍在演进8.0.20Hash Join 取代 BNL 的主要使用位置因此在 MySQL 8.0.20 看到Using join buffer时不要条件反射地认定一定是 BNL。应结合EXPLAIN FORMATTREE或EXPLAIN ANALYZE查看具体算子。七、算法三Hash JoinHash Join 通常选择一侧构建哈希表再扫描另一侧用连接键探测哈希桶。build input → 构建 hash(key → rows) probe input → 计算 key → 探测匹配 → 输出结果它适合没有可用连接索引、需要处理较大候选集的等值连接。相比 BNL 的批量两两比较哈希探测平均更接近线性处理。EXPLAINFORMATTREESELECT/* 无连接索引的实验表 */a.id,b.payloadFROMaJOINbONb.join_keya.join_key;计划中可能出现Inner hash join (b.join_key a.join_key)7.1 Hash Join 不是“不需要索引”如果外层过滤后只有几十行内层连接键索引探测通常比构建大型哈希表更便宜。Hash Join 解决的是某类无索引或大批量连接问题不是否定索引设计。场景更可能有利的路径外层很小内层连接键有索引INLJ外层较大内层二级索引需大量回表BKA 可能受益两侧候选集较大且无有效连接索引Hash Join连接后还需极少结果先改善过滤和基数估算内存不足时 Hash Join 可能分区并使用磁盘性能会受可用内存、行宽和数据量影响。八、LEFT JOIN 慢的真正原因“LEFT JOIN 天生比 INNER JOIN 慢”是错误结论。常见根因是左侧保留行太多外层循环规模大右表连接列没有匹配的联合索引连接列类型或 collation 不一致一对多关系导致中间结果爆炸右表过滤条件放错位置语义与预期不同统计信息失真优化器低估或高估基数。一个高价值索引通常与完整连接条件对齐CREATEINDEXidx_orders_tenant_customerONorders(tenant_id,customer_id);只创建customer_id索引在多租户系统中可能既扩大匹配范围也增加跨租户数据风险。连接条件与索引都应包含租户键。九、ON 与 WHERE外连接最容易踩的坑需求列出所有高等级客户并统计其 9 月已支付订单包括 0 单客户。SELECTc.id,COUNT(o.id)ASpaid_countFROMcustomerAScLEFTJOINordersASoONo.tenant_idc.tenant_idANDo.customer_idc.idANDo.status1ANDo.created_at2026-09-01ANDo.created_at2026-10-01WHEREc.tenant_id1001ANDc.level2GROUPBYc.id;右表条件放在ON中表示哪些订单可以匹配左表条件放在WHERE中表示哪些客户进入结果。-- 错误语义没有匹配订单的客户被 WHERE 过滤WHEREo.status1外连接计数还要使用COUNT(o.id)不能使用COUNT(*)否则补 NULL 的那一行也会被计数。十、反连接找“没有订单的客户”两种常见写法都可表达反连接。SELECTc.id,c.nameFROMcustomerAScLEFTJOINordersASoONo.tenant_idc.tenant_idANDo.customer_idc.idWHEREc.tenant_id1001ANDo.idISNULL;SELECTc.id,c.nameFROMcustomerAScWHEREc.tenant_id1001ANDNOTEXISTS(SELECT1FROMordersASoWHEREo.tenant_idc.tenant_idANDo.customer_idc.id);NOT EXISTS通常更直接地表达“无匹配即返回”也避开NOT IN遇到 NULL 后变成 UNKNOWN 的语义陷阱。MySQL 8.0 可能把它优化为 Anti Join但仍需要内层连接键索引和准确统计信息。十一、一对多放大索引也救不了错误粒度SELECTc.id,o.id,p.idFROMcustomerAScJOINordersASoONo.customer_idc.idJOINpayment_recordASpONp.order_ido.id;若一个客户有 100 笔订单每笔订单有 5 条支付尝试一个客户会产生 500 行。如果后续只想统计客户数却用DISTINCT消除爆炸结果通常是在为错误查询形态买单。可先按业务粒度聚合WITHpaid_orderAS(SELECTDISTINCTorder_idFROMpayment_recordWHEREpay_statusSUCCESS)SELECTc.id,COUNT(*)ASpaid_ordersFROMcustomerAScJOINordersASoONo.customer_idc.idJOINpaid_orderASpONp.order_ido.idGROUPBYc.id;MySQL 5.7 没有 CTE可改成派生表。关键不是语法而是尽早把中间结果压缩到正确粒度。十二、如何读 JOIN 执行计划EXPLAINANALYZESELECTc.id,o.idFROMcustomerAScJOINordersASoONo.tenant_idc.tenant_idANDo.customer_idc.idWHEREc.tenant_id1001ANDc.level2;重点观察指标问题访问顺序哪个输入先产生行actual rows每层实际输出多少行loops内层索引探测重复了多少次估算与实际差距统计信息是否误导优化器Index lookup是否按连接键有效探测Hash join是否构建并探测哈希表排序/临时表连接后是否又处理巨大中间集传统EXPLAIN.rows不是查询最终扫描量的精确答案。在嵌套循环中应结合各层rows、filtered和循环次数理解8.0 优先使用真实执行统计。如果估算严重偏离实际可先更新统计信息ANALYZETABLEcustomer,orders;低基数且分布倾斜的非索引列在 MySQL 8.0 还可评估直方图但不要把统计信息当作索引替代品。十三、优化顺序与常见误区推荐按下面顺序处理 JOIN 慢查询确认连接语义和结果粒度正确分别估算每张表过滤后的候选行检查内层连接键是否有匹配索引对齐连接列类型、字符集和 collation检查一对多是否放大中间结果用EXPLAIN ANALYZE找到高rows × loops节点更新统计信息并重新比较计划最后才考虑 Hint、STRAIGHT_JOIN或参数调整。误区正确认识永远小表驱动大表看过滤后的基数和整体成本LEFT JOIN 一定更慢慢在保留规模、索引和基数不在关键字有连接索引就一定快外层过大时百万次探测仍然昂贵join_buffer_size越大越好每连接、每会话可能分配需控制并发内存MySQL 8.0 仍总是 BNL8.0.20 主要由 Hash Join 取代STRAIGHT_JOIN是优化捷径它限制重排数据变化后可能固定坏计划十四、小结INNER/LEFT JOIN定义结果语义INLJ、BKA、BNL、Hash Join 才是物理连接方式。驱动表应理解为先产生候选行的输入判断依据是过滤后基数与总成本不是原表大小。INLJ 依赖被驱动表连接键索引外层行数决定索引探测次数。BKA 通过批量键访问和 MRR 改善大量二级索引回表不是所有查询都受益。MySQL 5.7 常见 BNLMySQL 8.0.20 的同类位置主要使用 Hash Join。Hash Join 适合较大无索引连接但构建哈希表也消耗内存溢写磁盘后会变慢。LEFT JOIN右表条件应根据语义放在ON放进WHERE可能把外连接变成内连接。连接列的类型、符号、字符集与 collation 应保持兼容。一对多导致的中间结果爆炸不能靠多建一个索引从根本上解决。优化 JOIN 要看EXPLAIN ANALYZE的真实行数和循环次数优先修复高rows × loops节点。