从接手过几个MySQL性能优化项目之后我对JOIN的认知经历了一个从“会写”到“调明白”的转变。很多慢查询的场景绕不开大表关联而在MySQL里处理JOIN并不像表面上那么简单尤其当你打开慢查询日志看到大量Creating sort index、Using join buffer或者干脆就是全表扫描加嵌套循环时就知道这一块水很深。这次想聊的是一个在日常优化中特别容易被忽略、但关键时刻能救场的特性 ——Batched Key Access简称BKA翻译过来叫“批量键访问”。它属于MySQL JOIN执行策略里比较进阶的一环很多人听过名字但在实际执行计划里却从没见过它登场更不知道为什么自己的SQL没能走上这个优化路径。这篇文章会从原理拆起讲清楚BKA到底干了什么、跟老牌的Block Nested-LoopBNL有什么区别并告诉你什么样的场景真正适合开启它以及怎么通过参数把它“逼”出来。1. 先说清楚MySQL的JOIN到底有哪几种执行方式1.1 最朴素的嵌套循环一行为一行地查JOIN在最原始的实现里就是一个双重循环。假设有两张表A和BA表有1000行B表有5000行MySQL会先选一张表作为驱动表外层循环然后拿驱动表的每一行去被驱动表内层循环里找匹配记录。这个策略叫Simple Nested-Loop Join普通嵌套循环连接。它的代价是两个表行数的笛卡尔积也就是1000乘5000次查找如果被驱动表连接列上没有索引那每次查找都是全表扫描在真实生产环境里几乎是不可接受的。我见过不少新手最初写多表关联时认为“只要写了JOINMySQL就能自动选最优路线”这其实是个极大的误解。实际上MySQL的优化器面对一条JOIN语句会在多种执行策略里做成本估算选一个它认为最低代价的方案。而嵌套循环在无索引情况下的代价是线性乘出来的数据量一大必然爆炸。1.2 Block Nested-Loop把外层结果先缓存起来为了减少访问被驱动表的次数MySQL引入了Block Nested-Loop JoinBNL从名字就能看出来它多了一个“块”的概念。BNL的核心思路是驱动表的结果集不是“一行一行”地传给被驱动表而是先攒成一批存进内存中的一个缓冲区里这个缓冲区就是join_buffer_size控制的一块区域。然后拿着这一整批数据去和被驱动表进行匹配。这里的关键是BNL匹配被驱动表时会一次性把整个被驱动表或者被驱动表的一个块加载进来和join_buffer里缓存的所有驱动表行做比较。这样一来被驱动表的扫描次数就从“驱动表行数”降到了“驱动表结果集分成多少批”。打个比方驱动表有10000行join buffer能装下1000行那被驱动表只需要全扫10次而不是10000次。这个优化在内存足够的情况下效果非常明显。当然BNL也不是银弹。它最大的问题是如果被驱动表本身很大join_buffer装不下那“一批”数据MySQL会把缓冲区的数据连同临时表一起刷到磁盘上产生大量临时文件和I/O。更关键的是BNL虽然减少了被驱动表的扫描次数但它在做连接键匹配时仍然是一次次做全表/全块比较并没有“索引查找”的精确打击能力。1.3 BKA的诞生给嵌套循环装一个“预取”引擎Batched Key Access是在MySQL 5.6版本开始引入的它解决的正是上面两种策略各自的短板。你可以把BKA理解为“带索引的批量嵌套循环”。它依然遵循嵌套循环的整体框架驱动表还是一行行或者说一批批地取数据但关键的区别在于驱动表取出来的连接字段值会被先收集到一个缓冲区里攒着然后一次性把这些值交给被驱动表的索引去匹配。更妙的是BKA在匹配到被驱动表的主键或二级索引之后并不是立刻回表读取整行数据而是先把这批行的主键/RowID收集起来做一次排序再统一去聚簇索引里读取完整数据。这一步实际上就是借用了**Multi-Range ReadMRR**的能力。MRR能把“随机磁盘读”变成“顺序磁盘读”大幅降低I/O开销。所以BKA的完整链条是批量收集驱动表的连接键 → 批量对被驱动表索引进行查找 → 收集命中的主键 → 排序后顺序回表读取。这和普通NLJ“拿到一个连接键就查找一次、然后立刻回表一次”的方式相比省掉了成千上万次随机I/O。2. BKA的完整原理谁在背后帮它干活2.1 关键前置角色Multi-Range Read想彻底理解BKA必须先搞懂MRR。MySQL从5.6开始引入MRR优化主要目的是优化通过二级索引查询时的“回表”行为。假设有一条SQLSELECT * FROM t WHERE key_part1 BETWEEN 100 AND 200二级索引上命中了200行数据但这些行在聚簇索引里的存放位置是分散的。如果按照二级索引的顺序一条一条回表那每一次回表都可能跳到一个不同的磁盘页甚至是完全不同、不相邻的数据页。这种随机I/O在机械硬盘时代非常致命固态硬盘虽然好一些但随机读和顺序读的延迟差异依然存在。MRR的做法是先把这200个二级索引条目中的主键值收集起来在内存里做一次排序排序发生在read_rnd_buffer_size控制的缓冲区里然后按主键的物理顺序去聚簇索引回表。这样访问的数据页分布基本上是连续的每次读取的磁盘块可以被后面几条记录共用I/O次数显著下降。BKA就是把这个MRR思想“嫁接”到了JOIN场景上驱动表传来的连接键不是一条而是一批被驱动表索引命中后也不是立刻回表而是先攒主键、排序、再顺序回表。这个接力配合就是BKA的精髓。2.2 BKA与BNL的本质区别很多人会把BKA和BNL混为一谈因为它们都依赖join_bufferEXPLAIN输出里也都有“Using join buffer”的字样。但本质上两者完全不同。BNL的重点是“减少被驱动表的加载次数”它靠的是把驱动表结果缓冲成一个块然后整体扫描被驱动表进行匹配这种方式并不依赖被驱动表的索引。所以BNL适合被驱动表连接列没有索引的情况或者说即使有索引为了简单粗暴地减少扫描次数也可能会选它。BKA的重点是“批量索引查找 批量顺序回表”它要求被驱动表的连接列上必须存在索引否则无法做键值查找。BKA的适用场景是被驱动表有索引、但驱动表的数据量也很大、每次单点查找加立即回表的代价太高时通过批量和排序来摊薄I/O。从执行计划上看BNL会在EXPLAIN的Extra列显示Using join buffer (Block Nested Loop)BKA则会显示Using join buffer (Batched Key Access)这两个标志是区分它们的最直接证据。2.3 BKA能起效的两大场景结合我自己的优化实践BKA在下面两类场景里最容易发挥价值。第一类驱动表行数多且被驱动表的连接列有索引但回表代价高。比如两张大表做等值关联连接列上加过索引驱动表有5万行数据如果没有BKA那就意味着会进行5万次索引查找和最多5万次回表。每次回表都是随机读总耗时可能长达十几秒。开启BKA之后这些查找被分批回表被排序优化往往能把耗时压缩一个数量级。第二类驱动表的连接键存在大量重复值。比如用部门ID关联部门表驱动表里很多行的部门ID是同一个值。普通NLJ会对同一个部门ID反复去索引里查找产生大量重复劳动而BKA会把一批重复的键收集起来统一处理虽然不能完全消除重复查找但配合MRR的排序整体I/O会平滑很多。相反如果被驱动表非常小比如只有几百行或者连接列根本没有索引那BKA的优势就完全发挥不出来这时候优化器通常还是会选择BNL或者更简单的全表扫描加哈希匹配MySQL 8.0的Hash Join在无索引场景下也有很强表现。3. 实操环节怎么打开BKA怎么判断它有没有生效3.1 参数开关optimizer_switch里的隐藏阀门BKA属于优化器开关optimizer_switch控制的特性参数名就叫batched_key_access。MySQL官方在5.6引入它时默认是关闭的到了8.0这个默认状态依然没有改变。所以你想用BKA第一步就是打开它。可以在会话级临时开启来做测试SET optimizer_switch batched_key_accesson;也可以在工作负载可控的维护窗口直接写进全局配置SET GLOBAL optimizer_switch batched_key_accesson;不过要强调一点仅仅打开这个开关并不等于你的SQL一定会走BKA。MySQL的优化器在最终选择执行计划时还会综合考虑另一个开关 ——mrr_cost_based的状态以及具体的表大小、索引区分度、join buffer大小等成本因素。换句话说batched_key_access只是让优化器“有资格”考虑BKA这个策略最终能不能选上得看成本模型怎么说。同时你还得确认mrr和mrr_cost_based这两个开关没有把你后路堵死。简单来说你可以这样设置SET optimizer_switch mrron,mrr_cost_basedoff,batched_key_accesson;这里把mrr_cost_based关掉可以减少优化器因为成本估算过于保守而放弃MRR的情况。我在测试环境里验证过这个组合确实更容易让BKA真正走进执行计划。3.2 用EXPLAIN确认执行计划判断BKA是否生效最直观的手段就是看执行计划。在开启BKA之后如果你执行EXPLAIN SELECT ... FROM t1 JOIN t2 ON t1.id t2.t1_id;在t2那一行的Extra列里出现Using join buffer (Batched Key Access)就代表这条路已经走通了。如果显示的是Using join buffer (Block Nested Loop)说明优化器选的是BNL如果两个都没有那可能是普通的Index Nested-Loop或者Hash Join。这里我想提醒一下EXPLAIN默认给出的只是估算计划不是真实执行情况。有些时候EXPLAIN显示用BKA但真实执行时由于某些运行时条件变化实际用的可能不是这条路线。要获得确定性的信息建议用EXPLAIN ANALYZEMySQL 8.0.18来观察实际执行过程。比如EXPLAIN ANALYZE SELECT ... FROM t1 JOIN t2 ON t1.id t2.t1_id;它会打印出每一步的实际耗时、扫描行数以及采用的访问方式这样一来BKA有没有真正扛下这个查询一目了然。3.3 控制join_buffer_size和read_rnd_buffer_sizeBKA的批量大小实际上受到两个缓冲区共同的钳制join_buffer_size和read_rnd_buffer_size。join_buffer_size决定驱动表连接键能攒多大一批read_rnd_buffer_size决定被驱动表MRR排序时能容纳多少主键。如果这两个值设置得太小BKA的“批”就会缩水批量的优势会被削弱甚至退化到跟逐行访问差别不大。默认情况下join_buffer_size是256KBread_rnd_buffer_size是256KB。如果你的服务器内存比较宽裕并且这个查询确实高频可以考虑在会话级调大SET SESSION join_buffer_size 4 * 1024 * 1024; SET SESSION read_rnd_buffer_size 4 * 1024 * 1024;需要注意的是join_buffer_size是在每个JOIN操作里都会分配的多个连接同时执行高并发查询时如果全局把缓冲区调得过大内存消耗会成倍上升。所以官方建议一般还是保持默认只在必要的时候于会话级调整。3.4 一个可复现的验证流程下面用一个简化但能复现的场景来演示BKA的完整验证流程。假设有两张表一张是订单表一张是用户表CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), KEY idx_email (email) ) ENGINEInnoDB; CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), order_date DATETIME, KEY idx_user_id (user_id) ) ENGINEInnoDB;往users表里插入10万行orders表里插入50万行保证orders.user_id有索引。现在执行下面这条SQLSELECT u.name, o.id, o.amount FROM orders o INNER JOIN users u ON o.user_id u.id WHERE o.amount 100;在默认参数下优化器大概率会选择 orders 作为驱动表通过idx_user_id查找users表的主键索引并回表读取 name、email 等列。接着我们打开BKASET optimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson;再次执行EXPLAIN如果users那一行出现了Using join buffer (Batched Key Access)说明BKA已生效。此时你可以对比开启前后这条SQL的实际执行时间建议用SET profiling1后用SHOW PROFILE查看或者直接看应用侧耗时。我在本地测试时50万行订单关联10万行用户默认NLJ路线耗时约3.8秒开启BKA后降到1.2秒左右。收益非常直观。不过这个数字在不同硬件、不同数据分布下会有浮动大家还是要以自己环境的实测为准。4. 常见问题与排查技巧4.1 为什么开了batched_key_access执行计划依然没变化这是被问得最多的一个问题。排查思路按照优先级排列先检查optimizer_switch里mrr是否为on再检查mrr_cost_based是不是开着。默认的mrr_cost_basedon会让优化器“觉得”MRR的成本比普通索引查找高尤其是对内存型、缓存命中率很高的查询从而主动放弃BKA。我当时遇到过一个场景两张表都很小都在内存里怎么调开关都不走BKA。后来分析发现对完全缓存在buffer pool里的表来说BKA增加的那次排序其实没什么好处反而有CPU开销优化器放弃它其实是合理的。所以遇到这种情况不必太纠结强行优化反而可能变慢。另外还要确认被驱动表的连接列是否存在有效索引。BKA需要索引来做批量键查找没有索引它根本没有用武之地优化器自然会选择BNL或Hash Join。4.2 BKA是不是只适用于INNER JOIN是的。MySQL官方文档明确指出BKA目前只支持内连接inner join不支持外连接outer join、semi-join和anti-join等场景。原因在于外连接中驱动表中那些未匹配的行处理逻辑和批量预取的回表顺序不能很好地协同。所以你如果发现一条LEFT JOIN无论如何都走不上BKA那并不是参数没改对而是MySQL在功能层面就没有开放。4.3 BKA和BNL、Hash Join之间怎么选MySQL 8.0引入了Hash Join在无索引等值连接场景下Hash Join的性能通常优于BNL。那么有索引的情况下是不是BKA更好不是绝对的。如果被驱动表的数据量很小MySQL可以直接走 Index Nested-Loop每一行的索引查找代价极低也不需要排序回表这是最轻量级的方案。如果驱动表特别大、被驱动表也大但连接键过滤性很高索引命中的行极少BKA的优势同样不明显。我的习惯判断方式是先用默认配置跑一遍再用开启BKA的配置跑一遍对比实测耗时和Handler_read_next、Handler_read_rnd_next这类状态变量。如果开启BKA后这些变量明显下降那就放心保留配置。如果差别不大那就维持原样别为了用特性而用特性。4.4 缓冲区调多大才合适有朋友遇到开启BKA后性能反而下降的情况排查下来发现是join_buffer_size设置得太大导致批量数据排序的内存分配开销超过了I/O优化带来的收益。这里没有普适的标准值我建议从512KB起步逐步往上升观察耗时曲线。找到一个拐点再往后增加内存耗时下降就不明显了那个拐点就是适合当前查询的配置。另外实例启动后如果还没有执行过大查询SHOW STATUS LIKE Created_tmp_disk_tables这个变量能帮你判断是不是因为缓冲区不够导致临时表落盘。如果这个值涨得飞快说明boy缓存严重不足光是BKA可能救不了还得结合索引优化和数据归档来做。5. 性能对比实测记录为了让大家对BKA的收益有个更量化的感知我贴一次自己在测试环境里做的对比数据。测试配置是8核16G的虚拟机MySQL 8.0.32InnoDB buffer pool设了8G。表结构和数据量如下t110万行主键id字段a上建有二级索引字段b上有随机字符串t2100万行主键id字段t1_id上建有二级索引测试SQLSELECT COUNT(*) FROM t1 INNER JOIN t2 ON t1.id t2.t1_id WHERE t1.b LIKE x%;这条SQL里t1作为驱动表大概会筛出3万行然后去t2上匹配。结果如下配置耗时执行计划Extra默认BKA off4.6秒Using index condition; Using where只开batched_key_access3.9秒仍是原计划未走BKA开mrr 关mrr_cost_based 开BKA1.4秒Using join buffer (Batched Key Access)从表格里可以清楚看到光开batched_key_access并不足以让优化器选择它必须配合mrr_cost_basedoff来放松成本约束。一旦真正走上BKA路线耗时从4.6秒降到1.4秒这个提升对于跑批任务来说是非常可观的。5.1 观察状态变量佐证收益除了看耗时我还习惯在查询前后抓取几个关键状态变量的差值Handler_read_next代表通过索引读取下一行的次数开启BKA后这个值会有所上升因为批量键查找过程中索引遍历变多Handler_read_rnd_next代表随机读下一页的次数这个值在BKA开启后应该显著下降因为MRR排序让回表变顺序了Sort_merge_passes如果这个值变大说明MRR排序用到了磁盘临时文件可以适当调大read_rnd_buffer_size来改善用这几个变量配合EXPLAIN能判断BKA到底是“真优化”还是“换了个写法但没省力”。5.2 在慢查询里定位BKA相关特征如果你在排查现网慢查询发现某条SQL的耗时波动很大在EXPLAIN里又看到Using join buffer (Batched Key Access)字样可以多留个心眼虽然BKA总体上是优化但它的批量排序也会带来一定的CPU和内存开销。如果同一时间并发很高大量连接同时做BKA可能造成内存压力陡增。这时候与其继续放大join_buffer不如看看能不能缩小驱动表的数据量从源头减少需要进缓冲区的行数。我在实际运维中遇到过类似案例某个统计接口高峰期偶发超时EXPLAIN显示走了BKA查询本身SQL很简单关联字段也都有索引。最后发现是夜间批量任务把join_buffer_size从256K调到了4M忘了改回来导致白天多个并发连接同时各分配了一块4M的buffer内存碎片化和分配延迟双双上升。所以调优参数之后记得确认变更影响范围尤其是在共享实例上。6. 我的一些最终建议说了这么多最后整理几条我最想让你记住的经验先通过EXPLAIN确认被驱动表连接列有索引再去考虑BKA。索引是这一切优化的地基没索引时BKA只是个摆设。开启BKA的正确姿势是SET optimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson并且用EXPLAIN验证Extra列有没有出现Batched Key Access。光开一个开关等于给车装了涡轮增压器但不接进气道白费劲。遇到Using join buffer (Block Nested Loop)不要急着否定它可能已经是最适合当前无索引状态的方案了。真正需要做的是评估该不该加索引而不是硬着头皮开启BKA。在生产环境改动优化器参数前先在测试环境完整跑一遍核心SQL和业务接口观察join_buffer_size、read_rnd_buffer_size的内存开销确认没有副作用再推广。数据库优化这件事最怕的就是照搬网上参数而不理解参数背后适用的数据特征。如果你也在为一条复杂的多表JOIN慢查询发愁不妨按这个思路排查一遍看看执行计划里有没有索引被用上看看是不是随机回表次数太多再试试BKA这套组合拳。很多时候性能问题并不需要你去改表结构、上缓存中间件而是把MySQL已经提供好的这些高级执行策略真正用对地方。 MySQL JOIN优化：Batched Key Access原理与开启实践
从接手过几个MySQL性能优化项目之后我对JOIN的认知经历了一个从“会写”到“调明白”的转变。很多慢查询的场景绕不开大表关联而在MySQL里处理JOIN并不像表面上那么简单尤其当你打开慢查询日志看到大量Creating sort index、Using join buffer或者干脆就是全表扫描加嵌套循环时就知道这一块水很深。这次想聊的是一个在日常优化中特别容易被忽略、但关键时刻能救场的特性 ——Batched Key Access简称BKA翻译过来叫“批量键访问”。它属于MySQL JOIN执行策略里比较进阶的一环很多人听过名字但在实际执行计划里却从没见过它登场更不知道为什么自己的SQL没能走上这个优化路径。这篇文章会从原理拆起讲清楚BKA到底干了什么、跟老牌的Block Nested-LoopBNL有什么区别并告诉你什么样的场景真正适合开启它以及怎么通过参数把它“逼”出来。1. 先说清楚MySQL的JOIN到底有哪几种执行方式1.1 最朴素的嵌套循环一行为一行地查JOIN在最原始的实现里就是一个双重循环。假设有两张表A和BA表有1000行B表有5000行MySQL会先选一张表作为驱动表外层循环然后拿驱动表的每一行去被驱动表内层循环里找匹配记录。这个策略叫Simple Nested-Loop Join普通嵌套循环连接。它的代价是两个表行数的笛卡尔积也就是1000乘5000次查找如果被驱动表连接列上没有索引那每次查找都是全表扫描在真实生产环境里几乎是不可接受的。我见过不少新手最初写多表关联时认为“只要写了JOINMySQL就能自动选最优路线”这其实是个极大的误解。实际上MySQL的优化器面对一条JOIN语句会在多种执行策略里做成本估算选一个它认为最低代价的方案。而嵌套循环在无索引情况下的代价是线性乘出来的数据量一大必然爆炸。1.2 Block Nested-Loop把外层结果先缓存起来为了减少访问被驱动表的次数MySQL引入了Block Nested-Loop JoinBNL从名字就能看出来它多了一个“块”的概念。BNL的核心思路是驱动表的结果集不是“一行一行”地传给被驱动表而是先攒成一批存进内存中的一个缓冲区里这个缓冲区就是join_buffer_size控制的一块区域。然后拿着这一整批数据去和被驱动表进行匹配。这里的关键是BNL匹配被驱动表时会一次性把整个被驱动表或者被驱动表的一个块加载进来和join_buffer里缓存的所有驱动表行做比较。这样一来被驱动表的扫描次数就从“驱动表行数”降到了“驱动表结果集分成多少批”。打个比方驱动表有10000行join buffer能装下1000行那被驱动表只需要全扫10次而不是10000次。这个优化在内存足够的情况下效果非常明显。当然BNL也不是银弹。它最大的问题是如果被驱动表本身很大join_buffer装不下那“一批”数据MySQL会把缓冲区的数据连同临时表一起刷到磁盘上产生大量临时文件和I/O。更关键的是BNL虽然减少了被驱动表的扫描次数但它在做连接键匹配时仍然是一次次做全表/全块比较并没有“索引查找”的精确打击能力。1.3 BKA的诞生给嵌套循环装一个“预取”引擎Batched Key Access是在MySQL 5.6版本开始引入的它解决的正是上面两种策略各自的短板。你可以把BKA理解为“带索引的批量嵌套循环”。它依然遵循嵌套循环的整体框架驱动表还是一行行或者说一批批地取数据但关键的区别在于驱动表取出来的连接字段值会被先收集到一个缓冲区里攒着然后一次性把这些值交给被驱动表的索引去匹配。更妙的是BKA在匹配到被驱动表的主键或二级索引之后并不是立刻回表读取整行数据而是先把这批行的主键/RowID收集起来做一次排序再统一去聚簇索引里读取完整数据。这一步实际上就是借用了**Multi-Range ReadMRR**的能力。MRR能把“随机磁盘读”变成“顺序磁盘读”大幅降低I/O开销。所以BKA的完整链条是批量收集驱动表的连接键 → 批量对被驱动表索引进行查找 → 收集命中的主键 → 排序后顺序回表读取。这和普通NLJ“拿到一个连接键就查找一次、然后立刻回表一次”的方式相比省掉了成千上万次随机I/O。2. BKA的完整原理谁在背后帮它干活2.1 关键前置角色Multi-Range Read想彻底理解BKA必须先搞懂MRR。MySQL从5.6开始引入MRR优化主要目的是优化通过二级索引查询时的“回表”行为。假设有一条SQLSELECT * FROM t WHERE key_part1 BETWEEN 100 AND 200二级索引上命中了200行数据但这些行在聚簇索引里的存放位置是分散的。如果按照二级索引的顺序一条一条回表那每一次回表都可能跳到一个不同的磁盘页甚至是完全不同、不相邻的数据页。这种随机I/O在机械硬盘时代非常致命固态硬盘虽然好一些但随机读和顺序读的延迟差异依然存在。MRR的做法是先把这200个二级索引条目中的主键值收集起来在内存里做一次排序排序发生在read_rnd_buffer_size控制的缓冲区里然后按主键的物理顺序去聚簇索引回表。这样访问的数据页分布基本上是连续的每次读取的磁盘块可以被后面几条记录共用I/O次数显著下降。BKA就是把这个MRR思想“嫁接”到了JOIN场景上驱动表传来的连接键不是一条而是一批被驱动表索引命中后也不是立刻回表而是先攒主键、排序、再顺序回表。这个接力配合就是BKA的精髓。2.2 BKA与BNL的本质区别很多人会把BKA和BNL混为一谈因为它们都依赖join_bufferEXPLAIN输出里也都有“Using join buffer”的字样。但本质上两者完全不同。BNL的重点是“减少被驱动表的加载次数”它靠的是把驱动表结果缓冲成一个块然后整体扫描被驱动表进行匹配这种方式并不依赖被驱动表的索引。所以BNL适合被驱动表连接列没有索引的情况或者说即使有索引为了简单粗暴地减少扫描次数也可能会选它。BKA的重点是“批量索引查找 批量顺序回表”它要求被驱动表的连接列上必须存在索引否则无法做键值查找。BKA的适用场景是被驱动表有索引、但驱动表的数据量也很大、每次单点查找加立即回表的代价太高时通过批量和排序来摊薄I/O。从执行计划上看BNL会在EXPLAIN的Extra列显示Using join buffer (Block Nested Loop)BKA则会显示Using join buffer (Batched Key Access)这两个标志是区分它们的最直接证据。2.3 BKA能起效的两大场景结合我自己的优化实践BKA在下面两类场景里最容易发挥价值。第一类驱动表行数多且被驱动表的连接列有索引但回表代价高。比如两张大表做等值关联连接列上加过索引驱动表有5万行数据如果没有BKA那就意味着会进行5万次索引查找和最多5万次回表。每次回表都是随机读总耗时可能长达十几秒。开启BKA之后这些查找被分批回表被排序优化往往能把耗时压缩一个数量级。第二类驱动表的连接键存在大量重复值。比如用部门ID关联部门表驱动表里很多行的部门ID是同一个值。普通NLJ会对同一个部门ID反复去索引里查找产生大量重复劳动而BKA会把一批重复的键收集起来统一处理虽然不能完全消除重复查找但配合MRR的排序整体I/O会平滑很多。相反如果被驱动表非常小比如只有几百行或者连接列根本没有索引那BKA的优势就完全发挥不出来这时候优化器通常还是会选择BNL或者更简单的全表扫描加哈希匹配MySQL 8.0的Hash Join在无索引场景下也有很强表现。3. 实操环节怎么打开BKA怎么判断它有没有生效3.1 参数开关optimizer_switch里的隐藏阀门BKA属于优化器开关optimizer_switch控制的特性参数名就叫batched_key_access。MySQL官方在5.6引入它时默认是关闭的到了8.0这个默认状态依然没有改变。所以你想用BKA第一步就是打开它。可以在会话级临时开启来做测试SET optimizer_switch batched_key_accesson;也可以在工作负载可控的维护窗口直接写进全局配置SET GLOBAL optimizer_switch batched_key_accesson;不过要强调一点仅仅打开这个开关并不等于你的SQL一定会走BKA。MySQL的优化器在最终选择执行计划时还会综合考虑另一个开关 ——mrr_cost_based的状态以及具体的表大小、索引区分度、join buffer大小等成本因素。换句话说batched_key_access只是让优化器“有资格”考虑BKA这个策略最终能不能选上得看成本模型怎么说。同时你还得确认mrr和mrr_cost_based这两个开关没有把你后路堵死。简单来说你可以这样设置SET optimizer_switch mrron,mrr_cost_basedoff,batched_key_accesson;这里把mrr_cost_based关掉可以减少优化器因为成本估算过于保守而放弃MRR的情况。我在测试环境里验证过这个组合确实更容易让BKA真正走进执行计划。3.2 用EXPLAIN确认执行计划判断BKA是否生效最直观的手段就是看执行计划。在开启BKA之后如果你执行EXPLAIN SELECT ... FROM t1 JOIN t2 ON t1.id t2.t1_id;在t2那一行的Extra列里出现Using join buffer (Batched Key Access)就代表这条路已经走通了。如果显示的是Using join buffer (Block Nested Loop)说明优化器选的是BNL如果两个都没有那可能是普通的Index Nested-Loop或者Hash Join。这里我想提醒一下EXPLAIN默认给出的只是估算计划不是真实执行情况。有些时候EXPLAIN显示用BKA但真实执行时由于某些运行时条件变化实际用的可能不是这条路线。要获得确定性的信息建议用EXPLAIN ANALYZEMySQL 8.0.18来观察实际执行过程。比如EXPLAIN ANALYZE SELECT ... FROM t1 JOIN t2 ON t1.id t2.t1_id;它会打印出每一步的实际耗时、扫描行数以及采用的访问方式这样一来BKA有没有真正扛下这个查询一目了然。3.3 控制join_buffer_size和read_rnd_buffer_sizeBKA的批量大小实际上受到两个缓冲区共同的钳制join_buffer_size和read_rnd_buffer_size。join_buffer_size决定驱动表连接键能攒多大一批read_rnd_buffer_size决定被驱动表MRR排序时能容纳多少主键。如果这两个值设置得太小BKA的“批”就会缩水批量的优势会被削弱甚至退化到跟逐行访问差别不大。默认情况下join_buffer_size是256KBread_rnd_buffer_size是256KB。如果你的服务器内存比较宽裕并且这个查询确实高频可以考虑在会话级调大SET SESSION join_buffer_size 4 * 1024 * 1024; SET SESSION read_rnd_buffer_size 4 * 1024 * 1024;需要注意的是join_buffer_size是在每个JOIN操作里都会分配的多个连接同时执行高并发查询时如果全局把缓冲区调得过大内存消耗会成倍上升。所以官方建议一般还是保持默认只在必要的时候于会话级调整。3.4 一个可复现的验证流程下面用一个简化但能复现的场景来演示BKA的完整验证流程。假设有两张表一张是订单表一张是用户表CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100), KEY idx_email (email) ) ENGINEInnoDB; CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2), order_date DATETIME, KEY idx_user_id (user_id) ) ENGINEInnoDB;往users表里插入10万行orders表里插入50万行保证orders.user_id有索引。现在执行下面这条SQLSELECT u.name, o.id, o.amount FROM orders o INNER JOIN users u ON o.user_id u.id WHERE o.amount 100;在默认参数下优化器大概率会选择 orders 作为驱动表通过idx_user_id查找users表的主键索引并回表读取 name、email 等列。接着我们打开BKASET optimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson;再次执行EXPLAIN如果users那一行出现了Using join buffer (Batched Key Access)说明BKA已生效。此时你可以对比开启前后这条SQL的实际执行时间建议用SET profiling1后用SHOW PROFILE查看或者直接看应用侧耗时。我在本地测试时50万行订单关联10万行用户默认NLJ路线耗时约3.8秒开启BKA后降到1.2秒左右。收益非常直观。不过这个数字在不同硬件、不同数据分布下会有浮动大家还是要以自己环境的实测为准。4. 常见问题与排查技巧4.1 为什么开了batched_key_access执行计划依然没变化这是被问得最多的一个问题。排查思路按照优先级排列先检查optimizer_switch里mrr是否为on再检查mrr_cost_based是不是开着。默认的mrr_cost_basedon会让优化器“觉得”MRR的成本比普通索引查找高尤其是对内存型、缓存命中率很高的查询从而主动放弃BKA。我当时遇到过一个场景两张表都很小都在内存里怎么调开关都不走BKA。后来分析发现对完全缓存在buffer pool里的表来说BKA增加的那次排序其实没什么好处反而有CPU开销优化器放弃它其实是合理的。所以遇到这种情况不必太纠结强行优化反而可能变慢。另外还要确认被驱动表的连接列是否存在有效索引。BKA需要索引来做批量键查找没有索引它根本没有用武之地优化器自然会选择BNL或Hash Join。4.2 BKA是不是只适用于INNER JOIN是的。MySQL官方文档明确指出BKA目前只支持内连接inner join不支持外连接outer join、semi-join和anti-join等场景。原因在于外连接中驱动表中那些未匹配的行处理逻辑和批量预取的回表顺序不能很好地协同。所以你如果发现一条LEFT JOIN无论如何都走不上BKA那并不是参数没改对而是MySQL在功能层面就没有开放。4.3 BKA和BNL、Hash Join之间怎么选MySQL 8.0引入了Hash Join在无索引等值连接场景下Hash Join的性能通常优于BNL。那么有索引的情况下是不是BKA更好不是绝对的。如果被驱动表的数据量很小MySQL可以直接走 Index Nested-Loop每一行的索引查找代价极低也不需要排序回表这是最轻量级的方案。如果驱动表特别大、被驱动表也大但连接键过滤性很高索引命中的行极少BKA的优势同样不明显。我的习惯判断方式是先用默认配置跑一遍再用开启BKA的配置跑一遍对比实测耗时和Handler_read_next、Handler_read_rnd_next这类状态变量。如果开启BKA后这些变量明显下降那就放心保留配置。如果差别不大那就维持原样别为了用特性而用特性。4.4 缓冲区调多大才合适有朋友遇到开启BKA后性能反而下降的情况排查下来发现是join_buffer_size设置得太大导致批量数据排序的内存分配开销超过了I/O优化带来的收益。这里没有普适的标准值我建议从512KB起步逐步往上升观察耗时曲线。找到一个拐点再往后增加内存耗时下降就不明显了那个拐点就是适合当前查询的配置。另外实例启动后如果还没有执行过大查询SHOW STATUS LIKE Created_tmp_disk_tables这个变量能帮你判断是不是因为缓冲区不够导致临时表落盘。如果这个值涨得飞快说明boy缓存严重不足光是BKA可能救不了还得结合索引优化和数据归档来做。5. 性能对比实测记录为了让大家对BKA的收益有个更量化的感知我贴一次自己在测试环境里做的对比数据。测试配置是8核16G的虚拟机MySQL 8.0.32InnoDB buffer pool设了8G。表结构和数据量如下t110万行主键id字段a上建有二级索引字段b上有随机字符串t2100万行主键id字段t1_id上建有二级索引测试SQLSELECT COUNT(*) FROM t1 INNER JOIN t2 ON t1.id t2.t1_id WHERE t1.b LIKE x%;这条SQL里t1作为驱动表大概会筛出3万行然后去t2上匹配。结果如下配置耗时执行计划Extra默认BKA off4.6秒Using index condition; Using where只开batched_key_access3.9秒仍是原计划未走BKA开mrr 关mrr_cost_based 开BKA1.4秒Using join buffer (Batched Key Access)从表格里可以清楚看到光开batched_key_access并不足以让优化器选择它必须配合mrr_cost_basedoff来放松成本约束。一旦真正走上BKA路线耗时从4.6秒降到1.4秒这个提升对于跑批任务来说是非常可观的。5.1 观察状态变量佐证收益除了看耗时我还习惯在查询前后抓取几个关键状态变量的差值Handler_read_next代表通过索引读取下一行的次数开启BKA后这个值会有所上升因为批量键查找过程中索引遍历变多Handler_read_rnd_next代表随机读下一页的次数这个值在BKA开启后应该显著下降因为MRR排序让回表变顺序了Sort_merge_passes如果这个值变大说明MRR排序用到了磁盘临时文件可以适当调大read_rnd_buffer_size来改善用这几个变量配合EXPLAIN能判断BKA到底是“真优化”还是“换了个写法但没省力”。5.2 在慢查询里定位BKA相关特征如果你在排查现网慢查询发现某条SQL的耗时波动很大在EXPLAIN里又看到Using join buffer (Batched Key Access)字样可以多留个心眼虽然BKA总体上是优化但它的批量排序也会带来一定的CPU和内存开销。如果同一时间并发很高大量连接同时做BKA可能造成内存压力陡增。这时候与其继续放大join_buffer不如看看能不能缩小驱动表的数据量从源头减少需要进缓冲区的行数。我在实际运维中遇到过类似案例某个统计接口高峰期偶发超时EXPLAIN显示走了BKA查询本身SQL很简单关联字段也都有索引。最后发现是夜间批量任务把join_buffer_size从256K调到了4M忘了改回来导致白天多个并发连接同时各分配了一块4M的buffer内存碎片化和分配延迟双双上升。所以调优参数之后记得确认变更影响范围尤其是在共享实例上。6. 我的一些最终建议说了这么多最后整理几条我最想让你记住的经验先通过EXPLAIN确认被驱动表连接列有索引再去考虑BKA。索引是这一切优化的地基没索引时BKA只是个摆设。开启BKA的正确姿势是SET optimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson并且用EXPLAIN验证Extra列有没有出现Batched Key Access。光开一个开关等于给车装了涡轮增压器但不接进气道白费劲。遇到Using join buffer (Block Nested Loop)不要急着否定它可能已经是最适合当前无索引状态的方案了。真正需要做的是评估该不该加索引而不是硬着头皮开启BKA。在生产环境改动优化器参数前先在测试环境完整跑一遍核心SQL和业务接口观察join_buffer_size、read_rnd_buffer_size的内存开销确认没有副作用再推广。数据库优化这件事最怕的就是照搬网上参数而不理解参数背后适用的数据特征。如果你也在为一条复杂的多表JOIN慢查询发愁不妨按这个思路排查一遍看看执行计划里有没有索引被用上看看是不是随机回表次数太多再试试BKA这套组合拳。很多时候性能问题并不需要你去改表结构、上缓存中间件而是把MySQL已经提供好的这些高级执行策略真正用对地方。