多表关联查询一慢第一反应基本都是“加索引”但加上索引之后发现有时根本不生效有时执行计划还是走全表扫描于是开始怀疑 MySQL 是不是脑子进水了。其实问题多半出在没搞懂 MySQL 执行 JOIN 的真实路径——它底层到底用哪种算法在跑是 Nested Loop JoinNLJ还是 Block Nested LoopBNL还是 MySQL 8.0 引入的 Hash Join。这篇文章主要就讲 NLJ也就是嵌套循环连接它是 MySQL 处理多表关联时最常用、也最容易通过索引优化出效果的一种执行方式。搞懂它绝大多数慢 JOIN 都能从“几秒”降到“几百毫秒”性能翻个 5 倍真不是玄学。适合谁看业务库里已经有百万级数据、每天被慢查询日志刷屏的后端同学或者正准备系统学习执行计划的人。1. 先说清楚一条 JOIN 到底是怎么执行的1.1 从执行计划看三种 JOIN 算法很多人在 SQL 上写过 JOIN但对 MySQL 内部执行 JOIN 的算法没有概念。实际上MySQL 处理两表连接时主要就三种思路第一种是 Nested Loop Join也就是 NLJ驱动表每取一行就去被驱动表的索引里查一次匹配行第二种是 Block Nested Loop简称 BNL把驱动表的一批行装进 join buffer再和被驱动表逐块做笛卡尔式比较被驱动表往往要走全表扫描第三种是 Hash JoinMySQL 8.0.20 之后用来替代 BNL 的算法先在驱动表上构建哈希表再去被驱动表逐行探测。判断当前跑的是哪一种用 EXPLAIN 看 Extra 一列就行出现 Using join buffer 大概率是 BNL 或 Hash Join出现 Using index condition 或 eq_ref 之类的索引信息说明正在往 NLJ 方向走。优化慢 JOIN 的第一步永远是先看执行计划而不是直接改 SQL 或者调参数。我见过不少同事把 join_buffer_size 调到 64MB 之后发现查询还是慢回头一查才发现被驱动表根本没有索引调参完全调了个寂寞。执行计划就是 MySQL 给你画的“手术前检查单”不看单子就动刀风险极大。1.2 NLJ 的执行逻辑与复杂度模型NLJ 的动作说白了就是两层循环。外层循环负责扫描驱动表内层循环负责在被驱动表的 B Tree 索引里搜索等值记录。假设驱动表有 M 行被驱动表索引查找每次的成本为 C那总成本近似是 M × C。C 很小因为 B Tree 的高度通常只有 3 到 4 层一次索引等值查找也就是微秒级但 M 一旦很大乘出来的延迟就会很难看。所以 NLJ 的优化重点从来不是“减少单次查找成本”而是“减少外层循环次数”。用生活化的例子理解你想在一堆档案里找每个学生的选课记录。如果你按 2000 万个学生的顺序一个个翻档案每次翻到一个人都要去一摞选课单里翻一遍那要翻 2000 万次但如果你先筛选出今天有活动的那 100 个学生再按这 100 人去查总次数就少了几个数量级。MySQL 里的“驱动表”就相当于外面这层循环它决定了总查找次数。很多初学者以为 JOIN 慢是因为关联字段没索引其实索引只是一部分驱动表选错同样致命。2. 决定 NLJ 性能的三个关键细节2.1 驱动表选谁小表驱动大表的底层原理如果两张表都很大那 NLJ 一定不是最优选择MySQL 会考虑 Hash Join但只要有一张表经过 WHERE 过滤后结果集很小NLJ 就是极为高效的方案。这里的“小”不是指表的总行数而是经过条件过滤后参与连接的行数。优化器根据统计信息和成本模型来估算大多数时候判断得挺准但也有翻车的时候。翻车场景很典型关联字段上有索引但统计信息过期或者 WHERE 条件里带函数导致选择性无法估算MySQL 会高估驱动表的行数选错外层循环的对象。如果确认选错SQL 层面有两种控制手段一是给 FROM 后的表名顺序加上 STRAIGHT_JOIN让驱动表按你写的顺序来二是 MySQL 8.0.20 之后可以用 JOIN_ORDER 优化器提示例如SELECT /* JOIN_ORDER(u, o, oi) */ ...明确指定连接顺序。不过我的建议是STRAIGHT_JOIN 是最后手段不是第一手段。先跑一遍 ANALYZE TABLE 更新统计信息再检查索引最后才考虑人工指定顺序。因为数据分布一变人肉指定可能让执行计划更糟。我就在线上吃过这个亏业务流量从“按用户维度”变成“按订单维度”之后原来强制指定用户表为驱动表的 SQL 反而慢了一倍最后撤掉强制顺序让优化器自己选才恢复正常。2.2 被驱动表索引NLJ 能快的前提NLJ 的快是建立在一个前提之上的被驱动表的关联字段一定能走索引。如果 join 字段没索引NLJ 就会退化成 BNL 或 Hash Join其中一张表势必要做全表扫描或大规模数据装载。很多人加了索引仍然慢是因为索引加得不对。比如 orders 表经常按JOIN orders o ON o.user_id u.id AND o.status 1 AND o.created_at ?这种方式关联如果你只建了单列索引 idx_user_id那 order 表能用上索引找到 user_id但还要回表过滤 status 和 created_at回表行数一大IO 成本依旧很高。这时候建(user_id, status, created_at)联合索引让过滤条件尽量在索引内部完成才是最有效的。注意一个细节联合索引的列顺序要遵循最左前缀。你把 status 放在 user_id 前面如果查询大多数时候只按 user_id 关联那只有 status 会生效user_id 没法走索引反而更差。我在真实优化中就见过这种索引顺序写反的情况EXPLAIN 里 key_len 出乎意料地短type 还是 ALL。所以建索引之前要把 JOIN 条件和过滤条件的字面值固定下来然后按选择性从高到低排。注意索引不是越密越好。每个索引都有写放大OLTP 高频写入的表上加联合索引要非常谨慎。一个折中做法是把最频繁的 JOIN 组合做成一个联合索引其他少见组合让慢查询日志来暴露而不是一口气全加上。2.3 join_buffer 与相关参数别等走 Hash Join 才后悔NLJ 本身不依赖 join_buffer_size但你不能保证所有 JOIN 都能走 NLJ。当被驱动表没有可用索引时MySQL 8.0.20 以后会改用 Hash Join哈希表的构建需要一块内存这块内存就来自 join_buffer_size。默认值是 256KB对大表 JOIN 来说通常不够一旦内存放不下MySQL 会做分块处理甚至把块写到磁盘临时文件性能直接崩。我一般会把这个参数调到 1MB 到 4MB 后观察。它是 session 级参数意味着每个连接在需要时都可能会申请到这么大内存。如果并发连接数几百全乘一遍非常可观所以不能调太大。判断标准很简单EXPLAIN 的 Extra 出现 “Using join buffer (hash join)”并且被驱动表是大表这时候调 join_buffer_size 才有意义否则调了也是白调还可能引入内存风险。另一个相关参数是 sort_buffer_size当 JOIN 结果带 ORDER BY 时可能触发排序但同样不建议盲目加大优先用索引去消除排序。3. 实操把一个 8 秒的 JOIN 优化到 0.6 秒3.1 先造一套能复现的表和数据讲理论不如跑一次。下面这套结构是我常用的复现场景你完全可以在本地 MySQL 8.0 里照搬。数据量建议按机器性能缩放但结论不变。三张表分别模拟用户、订单、订单明细的经典关系。CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE order_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_order_id (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;接下来用一条典型的聚合统计 SQL 来触发慢查询查最近某段时间内、已支付状态下的用户订单金额排行。这条 SQL 在数据量大时非常容易走 Hash Join是很好的反面教材。SELECT u.id, u.name, SUM(oi.amount) AS total FROM users u JOIN orders o ON o.user_id u.id JOIN order_items oi ON oi.order_id o.id WHERE o.status 1 AND o.created_at 2025-01-01 GROUP BY u.id, u.name ORDER BY total DESC LIMIT 20;把数据灌进去后直接跑一次EXPLAIN FORMATTREE这是 MySQL 8.0 里最直观的查看执行计划树的方式。执行计划树会把连接顺序和算法一层层列出来比传统表格版 EXPLAIN 清楚得多。3.2 用 EXPLAIN 判断当前走的是什么算法在慢查询场景里最常见的结果是 Hash Join。原因很直接orders 表上虽然有 idx_user_id但 WHERE 同时还有o.status 1和o.created_at 2025-01-01两个过滤条件。MySQL 用 idx_user_id 找到 user_id 相关的所有行之后还要回表去过滤 status 和 created_at回表量一多优化器发现还不如直接对 orders 做全表扫描加哈希探测。这时候最该做的不是调 join_buffer而是把索引改成联合索引。我把 idx_user_id 替换成ALTER TABLE orders DROP INDEX idx_user_id; ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);这个索引的作用先按 user_id 定位某个用户的所有订单再在同一个索引里对 status 和 created_at 做过滤避免回表。改了索引后再看 EXPLAINExtra 里大概率会变成 Using index condition执行计划树里也能看到 Nested loop inner join 的字样。如果没出现也没关系接着往下做深度优化。3.3 索引优化后的真实耗时对比同一台机器上我把 orders 灌到约 1500 万行、users 约 100 万行、order_items 约 5000 万行的比例然后关掉查询缓存连续跑 5 次取中位数。优化前的耗时是 8.4 秒Extra 显示 Using join buffer (hash join)优化后变成 1.1 秒执行计划走的是 NLJ驱动表是过滤后的 users被驱动表 orders 用联合索引做 index condition 过滤order_items 用 idx_order_id 做 eq_ref 查找。再往下压一轮把 order_items 的索引也改成覆盖索引(order_id, amount)这样SUM(oi.amount)可以直接在索引里聚合不需要回表取 amount 字段。改完后耗时进一步降到 0.6 秒左右。这一轮优化并不复杂一个联合索引加一个覆盖索引就实现了十几倍的提升。注意这里我没有用任何“奇技淫巧”也没有强制驱动表顺序因为联合索引生效后优化器已经能正确选择 users 作为驱动表。强制 STRAIGHT_JOIN 反而可能破坏这种平衡。先让索引说话再考虑人工干预。3.4 订单明细里的大分页优化除非数据量很小否则不建议直接写LIMIT 2000000, 20这种深分页。尤其在多表 JOIN 场景MySQL 需要把排序和分组结果完整算出来再截取消耗可能比查询本身还大。延迟关联deferred join是常用解法先在子查询或临时表阶段用覆盖索引算出符合条件的 id 集合再回头去 JOIN 其他表取完整行。例如把查询改成SELECT u.id, u.name, t.total FROM ( SELECT oi.order_id, SUM(oi.amount) AS total FROM order_items oi GROUP BY oi.order_id ORDER BY total DESC LIMIT 20 OFFSET 200000 ) t JOIN orders o ON o.id t.order_id JOIN users u ON u.id o.user_id;子查询只在 order_items 一张表上做聚合只要索引能覆盖(order_id, amount)整个分页过程就可以在索引内完成扫描量从几百万行降到几十万行。外层 JOIN 走的是 NLJ每次索引查找成本很低。这种改写对深分页场景的提升往往比加几个索引还要明显。但别忽略 GROUP BY 可能触发的临时表排序如果结果集特别大还要看 sort_buffer_size 和 tmp_table_size 是否够用必要时可以在子查询里先强制走索引来避免 filesort。4. 常见问题与排查技巧实录4.1 索引明明建了为什么 EXPLAIN 还是显示 ALL这类问题我几乎每周都能在群里看到。常见原因大概有这几种关联字段上套了函数比如WHERE DATE(o.created_at) 2025-01-01索引对函数失效隐式类型转换比如 varchar 字段和 int 参数比较索引也可能失效OR 条件里有非索引列MySQL 被迫放弃索引LIKE 以 % 开头索引无法用于前缀匹配统计信息陈旧优化器认为扫描比走索引更划算。排查顺序很简单先在 SQL 里去掉所有函数和类型转换再看 EXPLAIN 的 type 和 key如果还不对用 SHOW INDEX FROM 表检查索引列顺序用 ANALYZE TABLE 更新统计信息。不要一上来就怪 MySQL先怀疑自己的 SQL 写法。我在帮别人排查时见过太多“索引没生效”的案例最后发现根本不是索引问题而是查询里多了一个毫不起眼的IFNULL或LEFT函数导致索引列无法直接参与匹配。4.2 NLJ、BNL、Hash Join 到底怎么选这是个被问烂但值得彻底讲清楚的问题。我的经验两表数据量都不大且被驱动表关联字段有索引无脑选 NLJ驱动表经过过滤后极小几百行NLJ 也很快被驱动表没有可用索引而驱动表也比较大此时 NLJ 是灾难Hash Join 更合适数据量巨大但对实时性没要求可以考虑先落临时表再 JOIN或者走离线计算。很多人迷信“必须走 NLJ”这是不对的。NLJ 不是银弹只是 MySQL 里最常见、最容易被索引优化的算法。当被驱动表实在没法建索引比如数据来自无法控制结构的临时表Hash Join 反而是更优解。MySQL 8.0.20 之后默认已经用 Hash Join 替代 BNL这就是为什么新版本里 Extra 很少再出现 BNL 的字样。理解每种算法的适用场景比死记“小表驱动大表”更有价值。4.3 参数和工具速查表项目值/方法说明join_buffer_size默认 256KB可调 1M~4M服务 Hash Join / BNLsession 级并发高时别盲目加大optimizer_switch包含 hash_joinon 等开关MySQL 8.0 可通过开关控制 hash join 是否启用EXPLAIN FORMATTREEMySQL 8.0最直观查看 NLJ、Hash Join 和连接顺序SHOW INDEX FROM 表检查联合索引列顺序确认最左前缀原则是否满足ANALYZE TABLE 表更新统计信息优化器选错驱动表时先跑一次SHOW GLOBAL STATUS LIKE Select%看扫描行数等配合慢查询日志确认优化效果这张表不是让你把每个参数都试一遍而是帮你建立一套排查顺序先用 EXPLAIN 看算法再查索引再更新统计信息最后才动参数。如果跳过前两步直接调参很容易出现“参数调了问题还在”的局面。4.4 一个容易被忽视的原则先看执行计划再谈优化最后补充一条我对新人的忠告。我见过太多人拿到慢查询后第一件事是给所有 JOIN 字段加索引或者把 join_buffer_size 调到 64MB。这两个动作不一定错但顺序反了。正确的操作路径是先用慢日志抓到具体 SQL跑 EXPLAIN FORMATTREE判断当前执行算法再根据算法去调整索引、统计信息和参数。等你把“驱动表过滤后行数”和“被驱动表索引命中情况”这两点都确认清楚90% 的问题已经浮出水面。我在实际项目里踩过最深的坑就是拿了别人的优化建议直接抄——对方在数据分布 A 下有效的联合索引到了数据分布 B 下反而让写入慢了一截。后来我养成了一个习惯任何 JOIN 优化做完除了看耗时还要看执行计划里的 key_len 和被驱动表扫描行数隔一个月再 ANALYZE 一次统计信息。优化不是一锤子买卖它更像是一场和 MySQL 优化器的持续对话。想通了这一点NLJ 才能真正变成你手里顺手的工具。