★ FEATURED ARTICLE 上个月我在排查一个报表查询的性能问题EXPLAIN 的结果里出现了一行让我停住视线的提示Using join buffer (Batched Key Access)。当时我对 Batched Key Access 的印象还停留在“MySQL 5.6 加进来的一个优化开关”没想到它在一次真实的 JOIN 优化里帮了大忙。如果你也经常和慢查询打交道或者看到过 BKA 这个词但一直没搞懂它到底干了什么这篇文章就是为你准备的。我会从一次实际排查讲起拆解 Batched Key Access 的原理、开启方式、适用边界以及它和 Block Nested-Loop、HASH JOIN 之间的取舍关系。1. 一次真实慢查询EXPLAIN 里冒出来的陌生提示1.1 症状SQL 看着简单就是跑不快先看当时那条 SQL逻辑上非常简单查某段时间内的订单并且要带出每个订单的明细商品名、数量和单价。SELECT o.order_no, oi.product_name, oi.quantity, oi.price FROM orders o INNER JOIN order_items oi ON o.id oi.order_id WHERE o.created_at BETWEEN 2025-01-01 AND 2025-01-31;表结构也不复杂。订单主表orders大概 8 万行订单明细表order_items有 3400 万行order_items.order_id上建了一个普通二级索引。按常规思路这应该走 Index Nested-Loop Join拿orders的每一行去order_items的索引里查找匹配的order_id然后回表取product_name、quantity、price。问题就出在“回表”上。平均每个订单关联几百条明细8 万订单算下来要回表几千万次。每次回表都是一次随机 IO数据量一大buffer pool 根本覆盖不过来磁盘读的指针到处跳查询自然就卡住了。EXPLAIN 里order_items那一行 Extra 列显示的是Using join buffer (Block Nested Loop)说明优化器也没打算走普通索引连接。1.2 传统 JOIN 策略回顾NLJ、BNL 和它们的问题要理解 BKA 到底改进了什么得先把 MySQL 处理 JOIN 的几种传统方式捋清楚。Index Nested-Loop JoinNLJ是最理想的情况。驱动表每匹配一行就去被驱动表的索引里做一次点查时间复杂度接近 O(N)但是对索引要求很高而且每次查找后如果还需要回表IO 就变成随机访问了。像上面这个场景order_items.order_id有索引MySQL 却没有选 NLJ说明优化器估算的回表成本已经大到超过全表扫描。Block Nested-Loop JoinBNL是被驱动表没有可用索引时的兜底方案。它不再一行一行去扫被驱动表而是先把驱动表的数据按块读入join_buffer_size指定的内存区域然后用整个缓冲区的数据去和被驱动表的每一行做比较。这样确实减少了被驱动表的扫描次数但问题是如果驱动表总大小远超 join_buffer就要分很多批每一批都会把被驱动表完整扫一遍。我用一个简单估算说明当时的困境。8 万行orders如果每行数据大约 200 字节总共 16MB而默认join_buffer_size只有 256KB那么需要分 64 批。每批都要扫一遍 3400 万的order_items即使扫描是顺序读累计扫描行数也到了 21 亿级别慢是必然的。BNL 虽然比 Simple Nested-Loop 好很多但本质上还是“大表多次全扫”的思路对 IO 的消耗非常可观。2. BKA 到底改了什么批量键 MRR 的组合拳2.1 核心思路把“逐行查”变成“成批查”Batched Key Access 的定位很明确它是 Block Nested-Loop 的升级版针对的是“被驱动表有索引但索引访问和回表成本很高”的场景。普通 NLJ 的做法是驱动表每取一行就用这一行的连接列值去被驱动表索引里找一次。BKA 不这么干它把驱动表的一个数据块先读进 join_buffer然后从这一整块数据里把连接列的值全部提取出来聚合成一批“key”再一次性交给被驱动表的索引去做批量查找。想想快递分拣的场景就很好懂了。普通方式相当于每个快递员拿一个包裹单独跑一趟对应的小区BKA 则是在分拣中心先把所有去同一个方向的包裹合成一车快递员拉着一车包裹出发到小区门口再挨个派送。跑腿次数少了单次运输效率也高了。这个“合成一车”的动作对应的就是 BKA 从 join_buffer 里批量提取连接列并生成 key 集合的过程。它把返回结果集需要的多行匹配从“多次独立查找”合并成了“一次批量查找”。2.2 MRR 如何把随机回表变成顺序读BKA 能成立很大程度上依赖 Multi-Range ReadMRR。MRR 原本是 MySQL 5.6 为优化二级索引范围扫描引入的BKA 找到这些 key 后会把这些批量 key 交给 MRR 接口去访问索引。MRR 做了一件非常关键的事它不会按照 key 的输入顺序去逐个访问二级索引而是先把这些 key 对应的二级索引记录读出来再把得到的主键放入read_rnd_buffer_size指定的内存里进行排序。排序完成后回表读取数据行就变成了大致按主键顺序的顺序 IO。这里藏着整个优化里最重要的原理随机 IO 变顺序 IO。在机械硬盘时代顺序读和随机读的性能差可能达到几十倍即便在 SSD 时代顺序读对 IO 队列的占用和读放大也远小于随机读。对于需要回表几千万次的大 JOIN把回表顺序理顺收益立竿见影。还有一个隐蔽的好处MRR 会对来自不同 key 的重叠索引区间做合并避免同一个索引页被重复读取。相当于多个订单查找过程中如果它们命中的明细数据落在同一个索引页里这个页只需要被加载一次整体 IO 压力进一步下降。2.3 BKA 的一次完整执行链路把整个过程串起来看一次 BKA 的执行大致分成六步优化器选择驱动表按块读取数据并放入join_buffer_size内存区域。从 join_buffer 中的批量行里提取连接列值生成一组索引查找 key。将这组 key 交给 MRR 接口MRR 在被驱动表的二级索引上做批量查找。MRR 将查到的所有主键按物理存储顺序排序。按照排好序的主键分批回表读取被驱动表的完整数据行。将回表结果与 join_buffer 中的驱动表行做连接匹配返回结果集。之后重复上述过程直到驱动表全部分批处理完毕。注意到没有BNL 是被动地把整个驱动表缓冲区和被驱动表所有行去比对BKA 则是主动地利用驱动表的连接列值去索引里“定向捞数据”。前者像拿着名单去满城挨家挨户敲门后者是先按名单把每户地址排序再一条街一条街按顺序送信效率自然不同。BKA 要求被驱动表必须有一个至少能用于定位行的索引否则批量 key 没有地方可查MRR 也无从谈起。这个前提在后面排查时非常重要。3. 开启并验证 BKA参数、EXPLAIN 与实测对比3.1 两个开关要同时打开mrr 与 batched_key_accessMySQL 默认情况下 BKA 是关闭的必须手动打开。打开方式是通过optimizer_switch控制而且不是只开batched_key_access一个开关必须连带 MRR 一起。SET optimizer_switch mrron,mrr_cost_basedoff,batched_key_accesson;这条命令里三个部分都要注意mrron开启 Multi-Range ReadBKA 依赖它做索引访问和回表排序。mrr_cost_basedoff让优化器不要基于成本去判断是否使用 MRR而是强制使用。默认情况下mrr_cost_basedon优化器可能认为 MRR 不划算BKA 就不会生效。batched_key_accesson真正打开 BKA 算法。建议先用SET SESSION做会话级开启方便验证。确认效果好再考虑是否改到全局配置。直接改全局有风险后面我会详细讲。3.2 从 EXPLAIN 里确认 BKA 真的生效了开启 BKA 后重新执行 EXPLAIN观察被驱动表那一行。如果 BKA 正常生效Extra 列会出现明确提示Using join buffer (Batched Key Access)我当时的执行计划大致长这样EXPLAIN SELECT o.order_no, oi.product_name, oi.quantity, oi.price FROM orders o INNER JOIN order_items oi ON o.id oi.order_id WHERE o.created_at BETWEEN 2025-01-01 AND 2025-01-31;idselect_typetabletypekeyrowsExtra1SIMPLEoALLNULL80000NULL1SIMPLEoirefidx_order_id34000000Using join buffer (Batched Key Access)这里typeref说明它确实是用order_items.order_id索引去定位的而不是全表扫。Extra 里的Using join buffer (Batched Key Access)就是算法生效的标志。如果你看到 Extra 里写的是Using join buffer (Block Nested Loop)那就要回头检查是不是mrr_cost_based还开着或者被驱动表上没有可用索引。MySQL 8.0 还支持EXPLAIN FORMATTREE树形输出里会被标记为Batched key access排查起来更直观。3.3 同一份 SQL 的三组运行数据为了确认这个优化不是玄学我在同样的数据量下跑了几组对比。为了避免干扰每次执行前都清空相关缓存并且只统计 SQL 层面耗时。第一组是原生状态也就是默认优化器的选择。order_items.order_id索引存在但优化器判断回表成本太高最终走 Block Nested-Loop耗时约 2.4 秒。第二组开启 BKA 并关闭mrr_cost_based耗时降到 0.8 秒左右。第三组我在order_items上加了一个覆盖索引(order_id, product_name, quantity, price)让查询完全不用回表耗时进一步降到 0.15 秒。这个结果说明了两个问题。首先BKA 对“有索引但回表成本高”的场景确实非常有效2.4 秒到 0.8 秒的提升足够让一个报表任务从超时边缘回到可接受范围。其次BKA并不是最优解覆盖索引才是更彻底的方案因为它直接消灭了回表操作。所以我的建议是BKA 适合作为“无法使用覆盖索引”时的备选方案而不应该成为第一优先级的追求。4. 哪些场景该用哪些场景别碰BKA 的边界与坑4.1 适合 BKA 的场景特征经过几次实战我总结出 BKA 真正发挥价值的场景一般有几个特征驱动表相对较小能被分批完整读入 join_buffer。被驱动表非常大连接列有索引但查询需要回表取很多列。数据量明显超出 buffer pool 的覆盖范围随机回表 IO 代价很高。属于批处理、报表、离线计算这类低并发查询。前两个特征决定 BKA 有施展空间后两个决定 IO 优化的收益能真正兑现。如果数据全部在内存里随机回表已经很快BKA 里的排序反而成了纯开销。4.2 常见坑参数没配对、索引不存在、把小查询拖慢坑一只开了batched_key_accesson但mrr_cost_based还是默认的on。这种情况下优化器可能因为成本原因放弃 MRR最终 Extra 里看不到 BKA你以为是 MySQL 不生效其实是开关没配对。坑二被驱动表根本没有可用于定位的索引。BKA 和 MRR 本质上都是在索引访问的基础上做优化没有索引批量 key 无处投放优化器只能退回 Block Nested-Loop甚至 HASH JOIN 的老路。很多人期望 BKA 能解决“完全没有索引”的大表连接这是误解。坑三把 BKA 用在 OLTP 的高频短查询上。BKA 需要在 join_buffer 里批量收集驱动表数据再对一批 key 做排序这个过程本身有 CPU 和内存开销。对于每次只查几条数据的在线查询这些开销完全是浪费反而比简单的 NLJ 更慢。生产环境的 OLTP 库我不会全局开启 BKA。坑四没有控制 join_buffer_size 就贸然开启。批量 batch 的大小直接受join_buffer_size约束。默认 256KB 太小驱动表一个大块根本装不下BKA 退化成几乎每几行就一次索引访问效果大打折扣。我当时把它调到了 4MB 才看到明显变化。4.3 join_buffer_size 与 read_rnd_buffer_size 的调法调参这件事要分两层看。join_buffer_size决定驱动表批处理的行数也直接决定 BKA 一次能攒多少 key。我的经验是对于分析型查询可以尝试从 2MB 起步逐步调到 8MB 或 16MB观察耗时变化曲线找到拐点即可。比如测试结果join_buffer_size耗时说明256KB默认2.1 秒批量太小效果不明显2MB1.2 秒明显改善8MB0.8 秒基本接近峰值32MB0.75 秒再往上收益甚微再往上调通常只会增加内存压力没必要。read_rnd_buffer_size控制的是 MRR 排序主键时用多少内存这个参数可以沿用默认值因为它只影响单次排序的大小MRR 会自动分批处理。除非你的主键特别长或者单次批回表数量极大否则不需要特别动它。最稳妥的做法是先在会话级别开着 BKA配合EXPLAIN观察执行计划再按上面方式小步调整 join_buffer确认收益后再决定是否写入配置文件。千万不要为了一个慢查询去改全局参数因为你不知道其他查询会因此付出什么代价。5. 和 BNL、HASH JOIN 放一起看选型与版本思路5.1 四种策略的核心差异对照搞懂了 BKA很容易就会问它和 BNL、HASH JOIN、NLJ 之间的关系到底怎么理清我用一张表把它们的核心差异汇总起来方便以后选型时对照。策略被驱动表索引要求主要内存开销被驱动表访问方式适用场景NLJ需要高效索引很小逐行索引查找 回表驱动表小、回表率低、索引选择性好BNL不需要索引join_buffer_size多块扫描 内存比较非等值连接、小数据量笛卡尔结合BKA需要索引join_buffer read_rnd_buffer批量 key 索引查找 MRR 排序回表大表等值连接、回表成本高HASH JOIN不需要索引内存或磁盘临时文件构建哈希表 探测8.0.18 大表等值连接NLJ 的优势是内存占用低执行逻辑最直接。BNL 的优势是不挑索引但依赖大量内存缓冲。BKA 的优势是把随机回表变成顺序回表前提是被驱动表有索引。HASH JOIN 则是把比较过程转换成了哈希探测理论上对被驱动表的索引没有要求。5.2 MySQL 8.0 的 HASH JOIN 对 BKA 意味着什么MySQL 8.0.18 引入 HASH JOIN 后等值连接且被驱动表没有高效索引的场景优化器会优先考虑 HASH JOIN。它的思路是把驱动表构建成一张哈希表然后用被驱动表的每一行去探测时间复杂度接近线性而且充分利用了内存。在很多情况下HASH JOIN 的表现会比 BNL 好也比 BKA 更省事因为它连索引都不需要。那是不是意味着 BKA 就过时了没有。HASH JOIN 只在等值连接下有效而 BKA 在使用索引的场景仍然有发挥空间尤其是你已经有一个二级索引只是想优化回表顺序的时候。另外在 MySQL 5.7 及更早版本里没有 HASH JOIN面对大表等值连接但索引不佳的情况BKA 就是最好的优化手段之一。所以我的判断是如果线上是 MySQL 8.0 且遇到无索引的等值连接大表直接让优化器走 HASH JOIN 更合理如果是有索引但回表成本高BKA 和覆盖索引才是对症的方案。5.3 我现在的实际选型顺序经过这个案例之后我自己形成了一套相对固定的排查顺序写出来供你参考。第一步看被驱动表的连接列是否存在可用索引。没有索引MySQL 8.0 优先确认 HASH JOIN 是否生效5.7 及以下就考虑 BNL 以及是否有加索引的空间。第二步有索引但仍然慢用EXPLAIN确认是不是回表导致的随机 IO。如果查询需要返回的列很多、覆盖索引维护成本过高我会在会话级开启 BKA配合mrr_cost_basedoff做一次对比验证。第三步跑数据对比。同一份 SQL 分别在原状态、BKA 开启、覆盖索引三种情况下执行用耗时和 IO 指标说话而不是只看理论。第四步把收益和代价都记录清楚。如果 BKA 收益显著且只在低并发批处理场景使用我会把它固化到对应的会话初始化脚本里而不是改全局配置。这样既不影响其他查询又能在需要时自动生效。这套流程用了几次之后我发现很多慢 JOIN 的问题并不在算法选型上而是表结构设计时忽略了访问路径。算法优化能给查询续命但表结构合理才是根治之道。最后分享一个我踩过几次之后养成的习惯每次改完 JOIN 优化参数我都会用SHOW STATUS LIKE Select_full_join以及EXPLAIN FORMATTREE做前后对比确认执行计划真的走到了预期路径。BKA 是一个好工具但它和所有数据库优化手段一样讲究对症下药。理解了它解决什么问题、解决到什么程度你才能在下次遇到类似慢查询时心里真正有底。
阅读完成 · 觉得有帮助？
