企业官网建设流程全解析

前阵子接到一个线上慢查询，三张表JOIN，接口一到晚高峰就超时，DBA把EXPLAIN丢给我，我看到一整排的ALL和Extra列里的“Using join buffer”，就知道这条SQL又没吃到JOIN算法的红利，纯靠嵌套循环硬扛。后来花了大半个小时改完，执行时间从2秒多直接掉到30毫秒以内。今天就把这块彻底拆开聊：MySQL的JOIN到底有哪几种算法、执行计划上哪些字段在暴露问题、以及真正遇到慢JOIN时该按什么顺序一步步排查和优化。如果你是能跑EXPLAIN但没深究过JOIN底层逻辑的后端开发，这篇值得收藏；要是已经被生产环境的慢JOIN折磨过，那里面有几个坑相信你会有共鸣。

1. 拿到执行计划以后，先看什么才不算白看

1.1 EXPLAIN输出：JOIN场景下重点盯这三个字段

先给一条典型的JOIN查询和执行计划：

EXPLAIN SELECT o.id, u.name FROM orders o JOIN user u ON o.user_id = u.id WHERE o.amount > 100;

输出大概是这个形态：

+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+ | 1 | SIMPLE | o | ALL | idx_amount | NULL | NULL | NULL | 120000 | Using where | | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | test.o.user_id | 1 | NULL | +----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+

同一个id下面有多行，就是一张多表JOIN的执行计划，从上到下就是优化器排出来的连接顺序，第一行是驱动表，后面是被驱动表。

对JOIN查询来说，执行计划最关键的是看三个东西：

• type：当前这张表是怎么被读取的，取值从好到差大概是system > const > eq_ref > ref > range > index > ALL。对JOIN来说，被驱动表如果出现eq_ref或ref，说明索引吃到了；如果出现ALL，说明每次连接都在做全表扫描，这是最典型的性能红灯。
• rows：优化器估算的扫描行数。注意是估算值，不是精确值，它依赖统计信息，统计信息一旦过期就会严重失真。
• Extra：里面携带“Using join buffer”“Using temporary”“Using filesort”等等，这些附加操作直接决定了算法形态和是否触发了额外的排序、临时表开销。

1.2 type访问类型：eq_ref 是JOIN里最理想的信号，ALL是最响的警报

一个容易忽略的点是：同样的ALL，出现在驱动表位置和被驱动表位置，严重程度完全不同。

驱动表出现ALL，意味着第一张表要扫整张表，这只是一个全表扫描的成本。被驱动表出现ALL，意味着驱动表每返回一行，被驱动表就要全表扫一次。如果驱动表有10万行、被驱动表有100万行，那最坏情况就是10万次 × 100万行的匹配操作，这个量级已经不是“慢查询”，是“要打爆CPU和IO”的级别。

各type在JOIN里的含义：

• eq_ref：对于驱动表的每一行，被驱动表通过主键或唯一索引最多只匹配一行。这是连接列走主键时的理想状态，成本极低。
• ref：被驱动表通过普通二级索引匹配，可能匹配多行。可以接受，但要关注索引键的选择性，选择性差会导致回表量大。
• range：索引范围扫描，一般见于WHERE里有范围条件的列，比ref差一些，但依然是索引访问。
• index：扫整棵索引树，本质上接近全表扫，只是扫的是索引文件，一般出现在覆盖索引不满足但与索引列相关的情况下。
• ALL：全表扫描，经常和Extra里的Using where一起出现。如果出现在被驱动表上，多半是连接列没索引。

1.3 Extra列：Using join buffer / Using temporary / Using filesort暴露了什么

Extra里出现这些关键词，别扫一眼就略过去，它们直接告诉你底层执行策略：

• Using join buffer (Block Nested Loop)：被驱动表连接列没有可用索引，优化器拉起join buffer，走块嵌套循环。5.7及以前的无索引等值JOIN基本都是这个。
• Using join buffer (hash join)：8.0.18到8.0.19时代显式Hash Join的标识，8.0.20之后无索引的内连接等值JOIN默认走Hash Join。
• Using join buffer (Batched Key Access)：使用了BKA优化，通常是为了解决被驱动表有索引但回表随机IO严重的问题。
• Using temporary：说明执行过程创建了临时表，常和GROUP BY、DISTINCT、ORDER BY混在一起。临时表一旦落盘，性能是断崖式下跌。
• Using filesort：排序走不了索引，需要额外排序操作。它叫filesort但不一定是真文件排序，内存装不下才落盘。但只要出现，就有优化空间。

我个人的排查习惯是：先看type，再看Extra。type判断连接本身有没有吃索引，Extra判断排序聚合这些附加操作有没有拖后腿。很多人只盯着JOIN算法，忽略了JOIN之后接了个GROUP BY带来的临时表开销，结果索引加得挺欢，执行时间纹丝不动。

2. MySQL JOIN 算法本身：嵌套循环家族、块循环与哈希连接

2.1 Simple Nested-Loop Join 和 Index Nested-Loop Join：无索引与有索引的天壤之别

最朴素的JOIN实现是Simple Nested-Loop Join，思路就是双重循环：驱动表拿一行，去被驱动表里全表扫描找匹配。扫描量约等于驱动表行数 × 被驱动表行数，也就是笛卡尔积。两张表各10万行，理论匹配次数就是100亿级，这种写法在真实生产里就是灾难。

但真实场景下，优化器没那么傻，如果被驱动表连接列上有索引，它会自动改用Index Nested-Loop Join。内层的“查匹配”变成B+树索引查找，每来一行驱动表记录，就去索引里精确查找一次。复杂度从“乘积”降为“驱动表行数 × 索引查找成本”。

这里有个非常多见的误区：被驱动表的连接列必须要有索引，驱动表连接列有没有索引根本不重要。为什么？因为嵌套循环的匹配动作发生在被驱动表上，驱动表是逐行取数据，不会在被驱动表的索引上做查找。很多开发一遇到JOIN慢，先把两个表的连接列都加上索引，浪费空间倒还在其次，关键是没理解哪边才是关键。加索引优先照顾被驱动表。

2.2 Block Nested-Loop Join：join buffer 是怎么把全表扫描次数降下来的

被驱动表连接列没有索引时，如果还走Simple NLJ，被驱动表会被扫“驱动表行数”次。BNL的优化思路是：把驱动表的一批记录先放进join_buffer，然后拿着这一整批数据去和被驱动表匹配。这样被驱动表只需要被扫描“批次数”次。

算一笔账就明白差距：驱动表10万行，join_buffer一次能放5000行，被驱动表只需要被扫 10万 ÷ 5000 = 20次。如果是Simple NLJ，就是10万次全表扫描，差了5000倍。

BNL在5.6引入，MySQL在join_buffer内部还维护了哈希索引来加速匹配，所以它并不是简单的“块内双重循环”，性能远远好于Simple NLJ。但BNL不是没有代价：join_buffer里存放的是驱动表的行记录，如果驱动表查询了太多无用宽列，比如SELECT了一堆TEXT字段，buffer会很快被填满，批次数就变多。所以有一个非常实用的小技巧：如果发现SQL走上BNL，先看驱动表是不是select了过多无用的宽列，把查询列尽量压缩，join_buffer容纳的行数就更多，被驱动表的扫描次数就更少。这个优化手段比调大join_buffer_size更治本，因为参数调大是全局每个连接都占内存，而压缩查询列是零成本。

2.3 Hash Join：8.0时代无索引等值连接的新答案

从MySQL 8.0.18开始，等值连接且无索引的场景不再硬扛BNL，而是优先走Hash Join。核心逻辑是：优化器选一张较小的表（通常也是驱动表）建立哈希表，然后扫描另一张表，每一行用连接列的哈希值去探测哈希表，命中就输出。

Hash Join对等值连接非常友好，因为哈希探测是O(1)级别；但它对非等值连接，比如>、<、LIKE这类条件无能为力，这些场景依然得走嵌套循环。在8.0.20之后，内连接的等值JOIN场景里，BNL基本被Hash Join顶替，BNL沦落为非等值连接和外连接中没法用Hash Join时的备选。

用EXPLAIN FORMAT=TREE看会更直观，8.0.20以上会出现类似(inner hash join)的节点，语义非常清楚。这也引出我的一个经验：8.0以上如果EXPLAIN显示走Hash Join，先别急着否定，除非扫描行数实在太大，否则不要硬加索引去改变它。某些场景下Hash Join比加了索引后的NLJ更划算，尤其被驱动表行数非常大、连接列索引选择性又很差的时候。

2.4 BKA + MRR：被驱动表有索引但回表乱序时的组合优化

还有一个容易被忽略的优化组合：Batched Key Access。当被驱动表有二级索引，但驱动表返回的匹配主键在物理存储上特别离散，回表会产生大量随机IO。BKA的思路是：先把驱动表的一批连接列值收集起来，通过MRR接口把主键排序，再统一去被驱动表批量读取，把随机IO变成顺序IO。

BKA默认是关闭的，需要用：

SET optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';

注意mrr_cost_based要关掉，否则优化器可能因为cost模型判断不划算而放弃MRR。这个优化在机械硬盘时代收益明显，SSD上随机IO没那么昂贵，收益会缩小。生产环境要不要开，我的建议是拿真实SQL压测对比，不要无脑开启。

3. 执行计划里的危险信号：三条典型JOIN性能问题的排查链路

3.1 被驱动表ALL + Using join buffer：先查连接列的索引、类型和字符集

排查链路按顺序走：

1. 执行计划里第二行（被驱动表）type=ALL，Extra有Using join buffer。
2. 这说明连接列上没有可用索引，或者有索引但因为隐式类型转换、函数包裹、字符集不一致导致索引失效。
3. 先查两表连接列的字段定义，类型是否完全一致。一个非常经典的坑是user.id是BIGINT，orders.user_id是INT，这种情况下MySQL需要做整数提升，被驱动表连接列上的索引可能用不上。
4. 更常见的坑是字符型与数值型比较：user.mobile是VARCHAR存手机号，orders.phone是BIGINT，ON o.phone = u.mobile会让MySQL把字符串转成数值比较，u.mobile上的索引直接失效。
5. 再看字符集和排序规则是否一致。utf8mb4_0900_ai_ci和utf8mb4_general_ci之间的隐式转换同样会坑掉索引。
6. 确认索引存在：SHOW INDEX FROM 表名;。
7. 索引存在但执行计划不用，多半是统计信息过期，跑一下ANALYZE TABLE 表名;再重看EXPLAIN。

排查完之后，被驱动表的type应该从ALL变成eq_ref或ref，Extra里的Using join buffer消失，性能问题基本解决一半。

3.2 驱动表选错：rows 和 filtered 怎么帮你判断该不该 STRAIGHT_JOIN

优化器选择驱动表时，并不是简单按照“小表驱动大表”这一条经验，它内部是按代价模型估算的，扫描行数、索引查找成本、结果集大小都会参与计算。其中rows × filtered这个乘积代表“满足WHERE条件后真正进入JOIN阶段的行数”，这个数才是衡量哪边更适合当驱动表的关键。

举例：一张1000万行的大表，WHERE条件过滤后只剩1万行；另一张10万行的小表，过滤后剩8万行。这种情况下，用大表做驱动表反而可能更优，因为它真正参与JOIN的行数更少。

如果观察执行计划第一张表的rows远大于第二张表，且二者过滤条件都差不多，那多半是优化器选错了。INNER JOIN可以用STRAIGHT_JOIN强制表顺序：

SELECT STRAIGHT_JOIN u.name, COUNT(o.id) FROM user u JOIN orders o ON o.user_id = u.id WHERE u.level = 'VIP' GROUP BY u.id;

STRAIGHT_JOIN强制优化器按FROM子句里的表顺序执行，第一个表就是驱动表。改了之后对比一下执行时间和扫描行数，如果总额明显下降，基本就是优化器选型失误。选型的根因通常是统计信息不准确或直方图缺失。MySQL 8.0可以给过滤性强的列建直方图：

ANALYZE TABLE user UPDATE HISTOGRAM ON level;

这样优化器对level='VIP'的选择性估算会更准。

3.3 JOIN 之后接 GROUP BY / ORDER BY：Using temporary 和 Using filesort 的优化顺序

JOIN本身没问题，但后面接了GROUP BY或ORDER BY，经常能看到Extra里同时出现Using temporary和Using filesort。这种问题的优化顺序很讲究，别一上来就调临时表参数。

先看一个典型：

SELECT g.name, COUNT(*) FROM orders o JOIN goods g ON o.goods_id = g.id GROUP BY g.name ORDER BY COUNT(*) DESC;

执行计划里两张表连接没问题，但GROUP BY的列是g.name，不是主键，排序和分组都得靠临时表。优化手段是：

1. 把GROUP BY的列换成主键：GROUP BY g.id, g.name。MySQL 8.0.13+支持功能依赖检测，只要g.id是主键，g.name功能依赖于它，这个写法合法且省掉大量排序成本。
2. 或者先聚合再JOIN：先对orders表按goods_id聚合，得到小结果集再JOIN goods表。
3. 如果必须按非索引列排序，再考虑tmp_table_size和max_heap_table_size，增大内存临时表上限，避免落盘。但这两个参数是治标不治本的，核心还是要缩小参与分组排序的数据量。

3.4 5.7 与 8.0 的算法差异：哪些优化在升级后自动生效

5.7时代，无索引的等值JOIN只能走BNL，join_buffer_size调大一点会有帮助，但本质上还是块循环扫描。8.0.18开始，无索引等值JOIN优先走Hash Join，性能比BNL有量级提升。8.0.20之后，内连接场景里BNL直接被Hash Join顶替，BNL沦为非等值连接和部分外连接场景的备选。

所以一个经验是：从5.7升到8.0后，原来压测里依赖BNL的SQL往往会有明显提速，但这不代表SQL本身没问题。Hash Join只是把全表扫描时的匹配成本压低了，被驱动表全扫依然是全扫，表特别大的话磁盘IO和内存压力一样存在。它可能让一条SQL从“慢到超时”变成“慢到几秒”，但依然不是最优解。最优解还是让被驱动表通过索引连接，只有那种“怎么加索引都意义不大”的分析型查询，才适合真正交给Hash Join。

4. JOIN 优化的实操路径：先索引后改写，最后才动参数

4.1 连接列索引的设计：类型一致、字符集一致、覆盖索引

给JOIN建索引，我的习惯顺序是：

1. 先保证被驱动表连接列有索引，这是底线。
2. 检查连接列类型和字符集，避免隐式转换，这一步不花钱，但经常能救回一个走丢的索引。
3. 考虑覆盖索引。如果查询里只需要被驱动表的某几个字段，可以在连接列上建立覆盖索引，把经常要取的列加进去，让Extra出现Using index，连回表都省了。
4. 复合索引的字段顺序：一般把等值连接列放前面，范围过滤列放后面。但索引设计是权衡，最好拿真实SQL压测，不要套公式。

另外特别提醒一个反直觉场景：如果被驱动表很大，连接列加了索引之后NLJ性能反而可能不如Hash Join。因为索引NLJ意味着驱动表每行都做一次随机索引探测和回表，驱动表行数一大，随机IO次数就很恐怖。这种情况下先把驱动表过滤到很小，再走索引NLJ才是正解。

4.2 改写三条路：先过滤再JOIN、先聚合再JOIN、能拆就拆

SQL改写是JOIN优化里上限最高、也最容易被忽略的一步。

**第一条路：先过滤再JOIN。**把WHERE条件里能提前缩窄数据集的过滤放在子查询或派生表里，让参与JOIN的数据量从一开始就尽量小。前提是子查询不能被优化器拉平（flattening），否则改了个寂寞，所以要对比执行计划。

**第二条路：先聚合再JOIN。**典型的例子是“统计每个用户的订单数再关联用户表”，先对订单表GROUP BY user_id得到一个小结果，再JOIN用户表。这比先JOIN再聚合的扫描量和临时表大小都小得多。

**第三条路：能拆就拆。**多条大表JOIN可以拆成多个查询，在应用层拼数据。特别注意跨库跨服务的JOIN，网络开销会放大很多倍。有时候一条SQL拆成三条，总耗时反而更短，因为每一条都能吃上精确的索引，而且优化器不会被复杂的连接顺序绕晕。

这里提个反常识：并不是所有子查询改写成JOIN都会更快。MySQL的优化器会把某些子查询自动扁平化成JOIN，也会把某些JOIN物化成派生表。所以我每次改写都用EXPLAIN确认执行计划，绝不凭感觉判断哪种写法更快。

4.3 join_buffer_size、tmp_table_size 与 optimizer_switch 参数调优

参数调优放最后，因为它是放大器，不是发动机。索引和改写做不好，参数调多大都只是延缓爆炸。

join_buffer_size：默认256KB，对BNL而言增大它可以减少被驱动表扫描轮次。但这个参数要慎重，它是per-session分配的，每个连接都会占一份内存。生产环境建议按照并发数和业务量压测，一般256MB以内是比较稳妥的范围。而且它治标不治本：如果驱动表有10万行、每行2KB，想把这一批全放进buffer需要200MB，这是不现实的，不如压缩查询列或者加索引。

tmp_table_size / max_heap_table_size：内存临时表大小上限，实际取两者较小值。JOIN+GROUP BY频繁的场景可以适当调大，但同样有内存压力。临时表落盘其实可以通过改写SQL避免，比如先聚合再JOIN，临时表就小了。

optimizer_switch：可以开关block_nested_loop、hash_join、batched_key_access、mrr等优化。一般不建议关掉hash_join，除非有特殊兼容需求。BKA和MRR则要根据存储介质来定，SSD上收益有限。

innodb_buffer_pool_size：被驱动表全扫频繁时，增大buffer pool可以提升缓存命中率。另外如果一个JOIN慢了，不妨检查一下是否在大事务里，长事务会让MVCC版本链和undo膨胀，间接拖慢全表扫描速度，这个问题经常被人忽略。

4.4 一个反直觉提醒：出现 hash join 时别急着加索引

看到type=ALL + Using join buffer (hash join)，第一反应不该是加索引，而是先算一笔账：Hash Join的构建表成本 + 全表扫描被驱动表成本，和索引NLJ的随机回表成本相比，哪个更低？如果被驱动表极大，驱动表过滤后行数也很大，索引NLJ的随机IO可能比一次性Hash探测还慢。所以出现Hash Join时，我的做法是先用EXPLAIN FORMAT=TREE看执行策略，再压测对比加索引前后的差异。优化目标是让总代价最低，不是让EXPLAIN看起来“漂亮”。

5. 一个线上慢查询的完整复盘：从 EXPLAIN 到改写

5.1 原始SQL和第一版执行计划：问题出在哪一层

线上有个运营后台的报表接口，统计月度VIP用户订单金额排名：

SELECT u.id, u.name, SUM(o.amount) AS total_amount FROM user u JOIN orders o ON o.user_id = u.id WHERE u.level = 'VIP' AND o.created_at BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY u.id, u.name ORDER BY total_amount DESC LIMIT 50;

第一版EXPLAIN大概是这样的：

+----+-------------+-------+--------+---------------+----------------+---------+-----------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+----------------+---------+-----------------+--------+----------------------------------------------+ | 1 | SIMPLE | o | ref | idx_created | idx_created | 5 | const,const | 800000 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | test.o.user_id | 1 | NULL | +----+-------------+-------+--------+---------------+----------------+---------+-----------------+--------+----------------------------------------------+

orders表通过created_at范围索引扫出80万行，user表走主键eq_ref，单看JOIN没问题。真正的瓶颈在Extra：Using temporary和Using filesort，因为它要对80万行做GROUP BY和ORDER BY，临时表巨大，最后再排序取50条。

5.2 先通过索引缩小驱动表扫描范围

第一步是把订单表的扫描范围再缩小。created_at BETWEEN本身已经走索引，但select的amount和user_id还需要回表。我直接在orders表上加了一个覆盖索引：

ALTER TABLE orders ADD INDEX idx_user_created_amount (user_id, created_at, amount);

这个索引看起来是给JOIN连接列user_id和聚合字段amount服务的，但对于当前这条SQL，更关键的是它能覆盖o.user_id、o.created_at、o.amount三个字段，让整个扫描阶段都走索引，不回表。执行计划里orders表的Extra从Using where变成Using index，IO明显下降。

5.3 用派生表先聚合，再JOIN用户表

第二步是改写SQL，把聚合下推到订单表内部：

SELECT u.id, u.name, t.total_amount FROM ( SELECT o.user_id, SUM(o.amount) AS total_amount FROM orders o WHERE o.created_at BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY o.user_id ) t JOIN user u ON u.id = t.user_id WHERE u.level = 'VIP' ORDER BY t.total_amount DESC LIMIT 50;

改写后的逻辑是：先对订单表按user_id聚合，假设活跃VIP用户10万，派生表t最多10万行；再和user表做JOIN，最后过滤VIP并排序。参与临时表排序的数据量从80万降到了一个数量级以下。虽然u.level='VIP'的过滤被挪到了JOIN之后，但派生表已经很小，这个代价完全可接受。

5.4 对比验证与最终效果

改写后的EXPLAIN显示：

+----+-------------+------------+--------+---------------+---------+---------+-----------------+--------+-------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+-----------------+--------+-------------------------------------------+ | 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 100000 | Using temporary; Using filesort | | 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | t.user_id | 1 | NULL | | 2 | DERIVED | o | index | NULL | idx_user_created_amount | 12 | NULL | 800000 | Using where; Using index | +----+-------------+------------+--------+---------------+---------+---------+-----------------+--------+-------------------------------------------+

临时表还是要建，但数据量从80万行缩到10万行以内，排序开销下降非常明显。接口耗时从2.3秒降到40毫秒左右，晚高峰也不再触发超时告警。

这个案例里，索引和改写各贡献了一半效果。索引解决的是扫描和回表成本，改写解决的是排序聚合的数据体量。两者缺一不可。

最后再分享一个排查心得：我现在看JOIN慢查询，永远是EXPLAIN先看type和Extra，然后用EXPLAIN FORMAT=TREE再确认一遍join策略。前者看大概，后者看细节，比如到底是inner hash join还是nested loop、临时表是在内存还是落盘。先把被驱动表的索引问题处理干净，再考虑改写和参数，这几年踩过的坑，一多半都是因为一开始没看懂执行计划就急着改SQL，结果越改越偏。希望这篇能帮大家少走弯路。