join_buffer_size仅在bnl join且被驱动表无法走索引时生效，需explain确认出现“using join buffer (block nested loop)”才有效；建议设2m–8m，超32m易oom，优先优化索引而非调大该值。
join_buffer_size 什么时候真正起作用？
它只在 Block Nested-Loop Join (BNL) 场景下生效，且仅对**被驱动表**的全表扫描（type: ALL）或全索引扫描（type: index）起作用。如果你的 EXPLAIN 输出里没出现 Using join buffer (Block Nested Loop)，调这个值完全无效。
常见误判场景：
-
ON条件用了函数，比如ON UPPER(a.name) = UPPER(b.name)，导致索引失效，被迫走 BNL —— 这本质是索引设计问题，不是缓冲区不够 - 被驱动表有复合索引
(status, category_id)，但ON只写了b.category_id = a.id，最左前缀没覆盖，索引用不上 - 统计信息过期，优化器误判，执行
ANALYZE TABLE后 BNL 消失，join_buffer_size自然闲置
设多大才合理？别盲目加到 64M
join_buffer_size 是线程级变量，每个连接独占一份内存。设得过高，在高并发下会快速耗尽物理内存，触发 OOM 或 swap，反而拖垮整体性能。
实操建议：
- 默认 256KB 太小，线上建议设为
2M–8M（如SET SESSION join_buffer_size = 4194304） - 超过
32M极少带来收益，且容易因内存分配失败导致查询直接报错ERROR 5 (HY000): Out of memory - 不要在全局配置（
my.cnf）里设高值，尤其不能设成64M或更高 —— 100 个并发连接就吃掉 6.4GB - 配合监控：观察
Handler_read_next是否显著下降，以及慢查日志中“全表扫描驱动表后反复回查被驱动表”的模式是否减少
为什么加了索引，join_buffer_size 还是没被用上？
因为 MySQL 优先走 Index Nested-Loop Join (NLJ)，只有当被驱动表无法用索引定位行时，才会退化到 BNL。换句话说：join_buffer_size 是退化路径的补救措施，不是加速主路径的开关。
典型信号：
-
EXPLAIN显示被驱动表type: ALL或type: index，且Extra含Using join buffer (Block Nested Loop) - 驱动表很小（比如几百行），但被驱动表扫描次数暴增（
Handler_read_next达数十万次） - 加了索引后
EXPLAIN突然没了Using join buffer—— 说明索引生效，BNL 被绕过了
这时候调 join_buffer_size 不仅无用，还会掩盖本该解决的索引缺失或写法问题。
它和 sort_buffer_size、tmp_table_size 冲突吗？
不冲突，但会抢内存。MySQL 单条查询可能同时用到：
-
join_buffer_size：仅用于 BNL 场景下的被驱动表扫描缓存 -
sort_buffer_size：用于ORDER BY或GROUP BY的内存排序 -
tmp_table_size：用于内部临时表（如 GROUP BY 中间结果）
三者互不感知，各自按需分配。如果一条 SQL 同时触发 BNL + 排序 + 临时表，总内存消耗 = 三者之和。所以在线上压测时，要一起看 sort_merge_passes、Created_tmp_disk_tables 和 Handler_read_next，避免只盯一个参数调出“假优化”。
最常被忽略的一点：BNL 本身是低效算法，join_buffer_size 只是让它“不那么慢”，而不是“快起来”。真正该花时间的，永远是让优化器选 NLJ，而不是给 BNL 换更大的篮子。
