MySQL中NestedLoopJoin如何确定驱动表，优化器选择逻辑是怎样的？

本文共计933个文字，预计阅读时间需要4分钟。

很多人以为 `FROM t1 JOIN t2` 就意味着 `t1` 是驱动表。这是错误的。MySQL 优化器会基于统计信息（如行数、索引选择性、WHERE 条件过滤率）来估算各表的访问成本，并选择扫描行数更少、可用高效索引的表作为驱动表。因此，直接写出 `t1 JOIN t2`，若 `t2` 有强大的 WHERE 条件并覆盖索引，优化器很可能将其作为驱动表。

验证方法：执行 EXPLAIN FORMAT=TREE，看输出中哪个表出现在最外层嵌套的 Nested loop join 下方；或用 EXPLAIN FORMAT=JSON 查 "join_order" 字段。

为什么 STRAIGHT_JOIN 能强制指定驱动表

STRAIGHT_JOIN 是唯一能绕过优化器、人工锁定驱动表顺序的手段。它要求优化器严格按 SQL 中表出现的顺序执行联接，且只对 JOIN 左右两侧生效（LEFT JOIN 的左表仍必须驱动）。

• 适用场景：优化器误判（比如统计信息过期、小表没走索引但大表走了）、需要稳定执行计划用于压测或监控
• 风险点：STRAIGHT_JOIN 不校验索引可用性——如果被指定为驱动表的表在关联字段上无索引，就会退化成全表扫描 × 驱动表行数，性能雪崩
• 注意：STRAIGHT_JOIN 是语句级 hint，不能写在视图或存储过程中隐式生效

Nested Loop Join 在 MySQL 中实际是 Block Nested-Loop（BNL）或 Index Nested-Loop（INLJ）

MySQL 没有传统意义上的“简单嵌套循环”，而是根据是否有可用索引自动切换策略：

• 当内表关联字段有索引 → 用 Index Nested-Loop Join：驱动表每行去查一次内表索引，IO 少、延迟低
• 当内表无关联索引 → 默认启用 Block Nested-Loop Join：把驱动表一批行（受 join_buffer_size 限制）缓存进内存，再批量匹配内表，减少内表扫描次数
• join_buffer_size 太小会导致 BNL 分多轮加载，反而增加内表扫描次数；太大可能挤占其他连接内存，需结合 SHOW STATUS LIKE 'Select_full_join' 观察未使用索引的联接次数

常见误判驱动表导致的慢查询特征

典型现象不是“慢”，而是“慢得不合理”：驱动表行数不多，但执行时间随内表数据量陡增，EXPLAIN 显示 type=ALL 且 rows 极高。

• 错误写法：SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.state = 'active' —— 若 users 表小且 state 有索引，却因 orders 在前被当作驱动表，导致遍历全部订单去查用户
• 修复思路：换写法（SELECT * FROM users u STRAIGHT_JOIN orders o ON ... WHERE u.state = 'active'），或给 orders.user_id 加索引触发 INLJ
• 隐藏陷阱：分区表或 ICP（Index Condition Pushdown）开启时，rows 显示值可能严重低估真实扫描量，务必结合 Handler_read_* 状态变量确认物理读行为

驱动表选择本质是成本估算博弈，而估算依赖准确的统计信息。定期执行 ANALYZE TABLE，比强行调换表序或加 hint 更治本。