nested loop join没索引时爆炸式慢，是因为驱动表每行都要被驱动表全表扫描匹配，10万×100万=100亿次比对，cpu与io均爆满；explain中type为all、extra含using join buffer (block nested loop)即为典型征兆。
为什么Nested Loop Join在没索引时会爆炸式慢
因为MySQL默认的Nested-Loop Join（NLJ）本质是两层循环：驱动表每取一行，就被驱动表就要全表扫描一次。如果驱动表过滤后剩10万行，被驱动表100万行且没索引，那就是10万 × 100万 = 100亿次逐行比对——CPU跑满、IO打爆、查询卡死都是常态。
常见错误现象包括：EXPLAIN中type列为ALL或index、rows值远超预期、Extra列出现Using where; Using join buffer (Block Nested Loop)（说明已退化为BNL但依然吃力）。
- 索引建错位置：只给被驱动表建索引，但驱动表关联字段没索引 → 优化器可能放弃INLJ，回退到BNL甚至Simple NLJ
- 字段类型不一致：比如
orders.user_id是BIGINT，users.id是INT，隐式转换让两边索引都失效 - 字符集/排序规则不同：
utf8mb4_general_ci关联合utf8mb4_unicode_ci字段，同样触发全表扫
Index Nested-Loop Join（INLJ）才是高效关键
INLJ不是新算法，而是NLJ的“有索引版本”：它要求被驱动表的JOIN字段必须有索引，这样内层循环就不用全表扫，而是走B+树快速定位，时间复杂度从O(M×N)降到O(M×log N)。
但注意：INLJ生效的前提是驱动表本身也得能高效定位行——所以WHERE条件字段、ORDER BY字段最好也有索引，否则驱动表自己就得先扫一遍。
-
EXPLAIN中type显示ref、eq_ref或range，且key列非NULL，基本可确认走INLJ - 别信“我建过索引”，一定要看
key和rows是否真实生效；rows值要是表总行数的80%，那索引大概率没用上 - 复合索引要注意最左前缀：如果ON条件是
t1.a = t2.a AND t1.b = t2.b，t2上建(a,b)比单列a索引更稳
join_buffer_size调大真能救急吗
不能。绝大多数情况下，调大join_buffer_size对NLJ性能提升微乎其微，甚至有害。
因为只有当驱动表**没有可用索引**且连接是等值（=）时，MySQL才启用Block Nested-Loop（BNL），这时join_buffer才起作用。而一旦驱动表或被驱动表任一端有有效索引，优化器就会优先选INLJ——此时join_buffer_size完全不参与计算。
- 查执行计划：若
Extra里没出现Using join buffer，调这个参数就是白忙 - 高并发下盲目调大
join_buffer_size，容易引发内存争抢甚至OOM，尤其在连接数多的实例上 - 真正该调的是
innodb_buffer_pool_size（缓存热数据）和tmp_table_size（避免磁盘临时表）
LEFT JOIN的驱动表根本没法换
这是最容易被忽略的硬约束：LEFT JOIN的左表强制为驱动表，优化器无权重排顺序。如果你写SELECT * FROM big_orders o LEFT JOIN users u ON o.user_id = u.id，哪怕users只有100行、big_orders有500万行，MySQL也必须拿big_orders做外层循环。
所以LEFT JOIN的优化核心只有一条：让左表在进入JOIN前就尽可能小。
- 务必把过滤条件写在LEFT JOIN的左表上，比如
WHERE o.status = 'shipped' AND o.create_time > '2026-05-01'，而不是等JOIN完再筛 - 避免在LEFT JOIN左表用
OR、函数、IS NULL等让索引失效的操作 - 如果业务允许，考虑用
INNER JOIN替代——优化器就能自由选驱动表了
真正卡住性能的，往往不是算法本身，而是索引有没有建对、建在哪、以及LEFT JOIN那条不可动摇的左表规则。很多慢查询调优到最后，发现只是少建了一个user_id索引，或者把WHERE条件错放到了JOIN之后。
