企业官网建设流程全解析

上个月我在排查一个报表查询的性能问题，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 Join（NLJ）是最理想的情况。驱动表每匹配一行，就去被驱动表的索引里做一次点查，时间复杂度接近 O(N)，但是对索引要求很高，而且每次查找后如果还需要回表，IO 就变成随机访问了。像上面这个场景，order_items.order_id有索引，MySQL 却没有选 NLJ，说明优化器估算的回表成本已经大到超过全表扫描。

Block Nested-Loop Join（BNL）是被驱动表没有可用索引时的兜底方案。它不再一行一行去扫被驱动表，而是先把驱动表的数据按块读入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 Read（MRR）。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 的执行大致分成六步：

1. 优化器选择驱动表，按块读取数据并放入join_buffer_size内存区域。
2. 从 join_buffer 中的批量行里提取连接列值，生成一组索引查找 key。
3. 将这组 key 交给 MRR 接口，MRR 在被驱动表的二级索引上做批量查找。
4. MRR 将查到的所有主键按物理存储顺序排序。
5. 按照排好序的主键分批回表，读取被驱动表的完整数据行。
6. 将回表结果与 join_buffer 中的驱动表行做连接匹配，返回结果集。

之后重复上述过程，直到驱动表全部分批处理完毕。注意到没有，BNL 是被动地把整个驱动表缓冲区和被驱动表所有行去比对；BKA 则是主动地利用驱动表的连接列值去索引里“定向捞数据”。前者像拿着名单去满城挨家挨户敲门，后者是先按名单把每户地址排序，再一条街一条街按顺序送信，效率自然不同。

BKA 要求被驱动表必须有一个至少能用于定位行的索引，否则批量 key 没有地方可查，MRR 也无从谈起。这个前提在后面排查时非常重要。

3. 开启并验证 BKA：参数、EXPLAIN 与实测对比

3.1 两个开关要同时打开：mrr 与 batched_key_access

MySQL 默认情况下 BKA 是关闭的，必须手动打开。打开方式是通过optimizer_switch控制，而且不是只开batched_key_access一个开关，必须连带 MRR 一起。

SET optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';

这条命令里三个部分都要注意：

• mrr=on：开启 Multi-Range Read，BKA 依赖它做索引访问和回表排序。
• mrr_cost_based=off：让优化器不要基于成本去判断是否使用 MRR，而是强制使用。默认情况下mrr_cost_based=on，优化器可能认为 MRR 不划算，BKA 就不会生效。
• batched_key_access=on：真正打开 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';

这里type=ref说明它确实是用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 FORMAT=TREE，树形输出里会被标记为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_access=on，但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，观察耗时变化曲线，找到拐点即可。比如测试结果：

再往上调通常只会增加内存压力，没必要。read_rnd_buffer_size控制的是 MRR 排序主键时用多少内存，这个参数可以沿用默认值，因为它只影响单次排序的大小，MRR 会自动分批处理。除非你的主键特别长或者单次批回表数量极大，否则不需要特别动它。

最稳妥的做法是：先在会话级别开着 BKA，配合EXPLAIN观察执行计划，再按上面方式小步调整 join_buffer，确认收益后再决定是否写入配置文件。千万不要为了一个慢查询去改全局参数，因为你不知道其他查询会因此付出什么代价。

5. 和 BNL、HASH JOIN 放一起看：选型与版本思路

5.1 四种策略的核心差异对照

搞懂了 BKA，很容易就会问：它和 BNL、HASH JOIN、NLJ 之间的关系到底怎么理清？我用一张表把它们的核心差异汇总起来，方便以后选型时对照。

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_based=off做一次对比验证。

第三步，跑数据对比。同一份 SQL 分别在原状态、BKA 开启、覆盖索引三种情况下执行，用耗时和 IO 指标说话，而不是只看理论。

第四步，把收益和代价都记录清楚。如果 BKA 收益显著且只在低并发批处理场景使用，我会把它固化到对应的会话初始化脚本里，而不是改全局配置。这样既不影响其他查询，又能在需要时自动生效。

这套流程用了几次之后，我发现很多慢 JOIN 的问题并不在算法选型上，而是表结构设计时忽略了访问路径。算法优化能给查询续命，但表结构合理才是根治之道。

最后分享一个我踩过几次之后养成的习惯：每次改完 JOIN 优化参数，我都会用SHOW STATUS LIKE 'Select_full_join'以及EXPLAIN FORMAT=TREE做前后对比，确认执行计划真的走到了预期路径。BKA 是一个好工具，但它和所有数据库优化手段一样，讲究对症下药。理解了它解决什么问题、解决到什么程度，你才能在下次遇到类似慢查询时，心里真正有底。