作为数据库性能调优里绕不开的一个硬骨头JOIN 慢、JOIN 卡、JOIN 把 CPU 打满几乎每个用 MySQL 的后端和 DBA 都遇到过。很多人一上来就甩一句“加索引”可有时候加了索引还是慢有时候优化器压根不用你建的索引这时候就得回到最底层去看——MySQL 到底用哪种 JOIN 算法在跑你的语句执行计划里又藏着哪些线索。这篇笔记从 JOIN 算法本身讲起结合执行计划的读法把排查和优化的思路串起来。适合被慢查询折磨过的业务开发也适合刚接触性能调优、想系统理解连接原理的 DBA。先说明一下这里讨论的 JOIN 都是基于 InnoDB 存储引擎版本以 MySQL 8.0 为主部分对比会提到 5.7。不同版本在优化器行为上有差异但理解了核心机制换版本你也能自己推断。1. JOIN 算法的演进与核心原理1.1 从嵌套循环说起最基础也最好理解的 JOIN 算法是 Nested Loop Join也就是嵌套循环连接。它的思路特别直白从驱动表也就是执行计划里排在前面的那张表取一行然后去被驱动表里找匹配行找到就返回接着取驱动表下一行再找一轮。整个过程就是两层循环外层遍历驱动表内层扫描被驱动表。如果你写过两层 for 循环一定能想象到这种方式的代价。假设驱动表有 M 行被驱动表有 N 行没有索引的情况下内层每次都要全表扫描那比较次数就是 M × N。一旦 M 和 N 都上了十万比较量就是十的十次方级别这个量级在 CPU 上跑起来就是灾难。所以真实场景里MySQL 不会傻乎乎地用纯全表扫描去做嵌套循环。只要被驱动表的连接列上有索引内层循环就可以通过索引快速定位到匹配行而不是扫全表。这种带索引的嵌套循环MySQL 叫 Index Nested-Loop JoinINLJ也是我们平时最希望走到的算法。它的成本大约等于外层扫描 M 行加上内层基于主键或二级索引的 M 次点查时间复杂度接近 O(M) 到 O(M log N)比笛卡尔式的全表扫描好太多。实操中你会发现想让优化器走 INLJ最重要的就是给被驱动表的连接列加上索引。这里有个容易踩的坑连接条件两侧的字段字符集不一致比如一张表 utf8mb4另一张表 latin1MySQL 可能无法直接使用索引做字符串比较导致虽然列上有索引执行计划却显示全表扫描。这个坑我后面会专门讲。1.2 块嵌套循环与缓存的意义纯嵌套循环是按“行”为单位去被驱动表找数据的每找一次都要发起一次存储引擎层的读取。如果被驱动表非常大且没有可用索引这种逐行读取的开销根本无法接受。于是 MySQL 引入了 Block Nested-Loop JoinBNL中文叫块嵌套循环连接。BNL 的核心思路是“批量缓存”。它不是从驱动表取一行就去被驱动表找一次而是先把驱动表的一批行放进 join buffer然后一次性取被驱动表的一个数据块在这个块里和 join buffer 中的所有行做匹配。这样一来被驱动表的扫描次数大幅减少从原来的 M 次降低到“被驱动表总块数 / join buffer 能装下的驱动表行数”这么多次。你可以把 join buffer 想象成一个大托盘服务员MySQL不再为每一道菜单独跑一趟厨房而是攒够一托盘再去端菜效率自然上去。BNL 的适用场景是没有索引的等值连接、部分非等值连接以及被驱动表特别大的情况。在 MySQL 5.7 里你会在执行计划的 Extra 列看到Using join buffer (Block Nested Loop)这样的提示。到了 MySQL 8.0优化器对 BNL 做了改进并且引入了 hash join很多原本走 BNL 的场景会自动转为 hash join。所以如果你还在 5.7 上看到 BNL建议认真检查一下被驱动表的连接列是不是缺索引因为 BNL 本质上是拿内存换时间当 join buffer 不够大时性能依旧不理想。有一点必须强调join buffer 是有大小限制的由参数join_buffer_size控制默认只有 256KB。如果你的驱动表一次装不满MySQL 就会分多次处理每次处理都重新扫描被驱动表。如果发现执行计划是 BNL 且被驱动表扫描次数很高适度调大这个参数可能有帮助但要注意它是会话级别的且是每线程独立分配的QPS 高的时候调太大会撑爆内存。1.3 哈希连接的引入MySQL 8.0.18 开始正式支持了 Hash Join。这个算法的思路是先把驱动表的连接列算成哈希值放到内存里的哈希表中然后扫描被驱动表对每一行计算连接列的哈希值到哈希表里探测是否匹配。由于哈希碰撞概率低每个探测基本是 O(1) 的复杂度所以总成本大约是被驱动表扫一遍的代价。Hash Join 特别适合两张表都没有合适索引但连接列是等值关系的场景。比如你在两张大表上做WHERE a.id b.user_id两边都没有索引嵌套循环会慢到怀疑人生hash join 却能利用内存高速完成匹配。之所以以前 MySQL 一直不引入 hash join除了历史原因还因为它在 OLTP 场景下不是主流需求加上内存开销和不确定的哈希冲突团队一直很谨慎。现在引入后很多没有索引的等值连接查询直接在优化器阶段就选用了 hash join相比 BNL 性能提升非常明显。不过 hash join 也不是万能的。它要求连接操作是等值连接非等值连接比如a.id b.id没法用。另外它需要把驱动表build成哈希表整个操作是内存密集型的如果驱动表太大导致哈希表溢出到磁盘性能就会急剧下降。所以在设计表结构时不要想着“反正有 hash join我可以不建索引了”这完全是把优化器的兜底方案当成了常规手段风险很大。2. 执行计划中的 JOIN 线索2.1 如何读懂 EXPLAIN 中的 type 与 Extra谈到 JOIN 性能分析EXPLAIN 是第一手资料。很多初学者只看 rows 列的估算值实际上 type 列和 Extra 列的信息量更大。type 列描述了访问类型从好到差大致是system const eq_ref ref range index ALL。在 JOIN 场景里如果被驱动表的访问类型是eq_ref或ref说明连接条件用到了主键或唯一索引这是最优状态如果是range说明用到了索引但做了范围扫描一般也能接受如果出现ALL那就是全表扫描大概率是连接列没索引或者优化器认为扫全表比走索引更快通常是表太小。Extra 列更值得玩味。看到Using index condition表示用到了索引下推ICP存储引擎层在索引层面就过滤掉不满足条件的行减少了回表看到Using where说明存储引擎返回后 Server 层还得再次过滤这时候通常要关注是不是有查询条件没被索引覆盖看到Using temporary说明查询用到了临时表常见于 GROUP BY、ORDER BY 和某些 JOIN 场景看到Using filesort说明需要额外排序这可能和 JOIN 的关联顺序有关。还有一个常被忽略的线索是Using join buffer (Block Nested Loop)在 5.7 里如果看到它基本可以断定被驱动表没有可用索引或者连接条件不是索引可直接定位的形式。在 8.0 中则可能显示Using join buffer (hash join)或Backward index scan等。多花点时间把这些标志记熟你就能在优化器做出选择的第一时间反应过来它为什么慢。注意EXPLAIN 给出的 rows 是估算值通常基于采样统计不一定准确但 type、Extra 是真实计划的反映。要拿更精确的耗时得用 EXPLAIN ANALYZE8.0.18。2.2 驱动表选择与执行顺序JOIN 的执行顺序不是简单的“SQL 里谁写在前面谁就是驱动表”。优化器会基于表大小、索引情况、过滤条件等多维度信息用成本模型算出不同的连接顺序选总成本最低的那一个。MySQL 默认使用贪婪搜索来减少候选计划的枚举次数但结果并不总是完美所以偶尔会出现“明明小表驱动大表更好优化器却选了大表驱动小表”的情况。理解这一点很重要因为驱动表的选择直接决定了外层扫描的行数。原则上驱动表应当是过滤后行数比较少的表被驱动表则应当让连接列走索引。如果你发现 EXPLAIN 中第一行的表不是预期中的小表可以通过 STRAIGHT_JOIN 强制指定连接顺序但不建议在业务代码里轻易使用因为它会干扰优化器的后续调整。更好的做法是更新统计信息ANALYZE TABLE、调整查询条件写法和索引设计让优化器自己做出正确选择。另外EXPLAIN中的 id 列可以帮你识别“子查询”和“派生表”的连接顺序。id 相同表示这两张表在同一个 JOIN 层级从上到下就是实际执行顺序id 不同且 id 值越大越先执行也就是子查询先被物化。如果发现子查询被物化成临时表再参与 JOIN临时表又没有索引那性能往往不好。这时候可以尝试用窗口函数或直接改写 JOIN避免物化带来的额外开销。2.3 用 EXPLAIN ANALYZE 看真实耗时MySQL 8.0.18 起EXPLAIN ANALYZE 可以实际执行语句并输出每一步的耗时、行数和循环次数。它的调用方式很简单EXPLAIN ANALYZE SELECT ...。和普通 EXPLAIN 不同它能告诉你“这步实际消耗了多少毫秒”、“实际处理了多少行”而不是估值。这是优化 JOIN 时我非常依赖的工具。举个例子如果你怀疑 JOIN 慢在被驱动表的反复扫描EXPLAIN ANALYZE 会打印类似Nested loop inner join (cost...) (actual time... rows... loops...)的信息。loops代表这一步被执行了多少次。如果loops很大而内层的rows很小说明每次只拿到几行但循环了很多次可能是指数选择有问题如果内层actual time很高那就说明单次访问索引的代价大需要检查索引结构或统计信息。EXPLAIN ANALYZE 会真实运行 SQL所以对线上的大查询要谨慎最好在预发布环境或只读副本上执行或者加上LIMIT来观察前几步的行为。很多时候一条慢 JOIN 的真实瓶颈并不在 JOIN 本身而在于驱动表上的 WHERE 过滤没有走索引导致驱动表扫了太多行。EXPLAIN ANALYZE 能一眼把这个幻觉打破。3. JOIN 性能优化的实操三板斧3.1 索引设计让连接条件走索引索引设计是 JOIN 优化里最关键、也最立竿见影的一步。对于INNER JOIN你应该在两张表的连接列上都建立索引这样无论优化器选择哪张表作为驱动表被驱动表都能快速匹配。对于LEFT JOIN左表的驱动地位通常不变所以右表的连接列必须有索引如果右表行特别多没有索引的 LEFT JOIN 会触发 BNL扫表扫到怀疑人生。这里有一个常见误区以为只要连接列类型相同就行忽略了排序规则和字符集。比如一张表的连接列是utf8mb4_unicode_ci另一张是utf8mb4_general_ci虽然都是 utf8mb4但字符集排序规则不同MySQL 就无法直接使用索引进行字符串比较需要在内存中做转换执行计划里连接列的 type 会变成ALL或ref但带Using where。最简单的解决办法是统一字符集和排序规则或者在 SQL 里显式加COLLATE但后者会阻止索引使用所以更推荐前者。另外连接列如果是复合索引中的第二列单独用它做 JOIN 不一定能走索引。比如表上有(a, b)复合索引而查询用b做连接索引就无法直接用于等值匹配。这时就要考虑增加一个以 b 开头的索引或者调整查询结构。别盲目建索引先看执行计划再对照索引结构才能精准命中问题。3.2 驱动表与查询条件裁剪在实际工作中我发现很多 JOIN 慢的源头不是 JOIN 本身而是驱动表太大。比如一条查询写的是SELECT * FROM orders o JOIN users u ON o.user_id u.id WHERE o.created_at 2024-01-01如果 orders 上有年份分区那么驱动表 orders 会被裁剪到很小。但如果没分区、没索引MySQL 只能全表扫 orders然后再去 users 表匹配。所以驱动表的 WHERE 条件有没有走索引直接决定了外层扫描量。在这个地方最实用的一招是“先过滤后 JOIN”。用子查询或者派生表把两张表需要的数据提前缩小再连接。比如SELECT o.id, u.name FROM (SELECT id, user_id, amount FROM orders WHERE created_at 2024-01-01) o JOIN (SELECT id, name FROM users WHERE status 1) u ON o.user_id u.id;不过要注意MySQL 8.0 的优化器会自动对派生表做合并或物化有时你的子查询会被合并回原表执行计划未必按你写的来。用EXPLAIN FORMATTREE或 EXPLAIN ANALYZE 观察实际效果如果计划不理想可以尝试用/* derived_condition_pushdown() */等优化器提示来控制。总体思路是让驱动表尽可能小被驱动表尽可能走索引。3.3 改写 SQL 与临时表策略有些 JOIN 问题可以通过改写 SQL 绕过去。比如多表 JOIN 的 ON 条件里带 OR这会大大限制索引使用。ON a.id b.id OR a.id c.id这种写法基本不可能走索引只能扫描后计算。遇到这种需求通常可以拆成两个 JOIN用 UNION 合并结果或者用 IN 子查询替代部分逻辑。再有就是 GROUP BY 与 JOIN 的组合。很多慢查询是先把两张表 JOIN 得到宽表再分组统计导致临时表巨大。更聪明的做法是先在各自表里做聚合再把聚合结果 JOIN。例如SELECT u.id, COALESCE(SUM(o.amount), 0) FROM users u LEFT JOIN orders o ON o.user_id u.id GROUP BY u.id;如果 orders 表巨大这个 LEFT JOIN 会把所有订单行都牵进来再分组非常浪费。你可以改成SELECT u.id, COALESCE(t.total, 0) FROM users u LEFT JOIN ( SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ) t ON t.user_id u.id;这样 orders 只扫一遍做聚合再和 users JOIN行数大大减少性能提升通常非常明显。这种“能先聚合就先聚合”的思路在 JOIN 优化中百试不爽。4. 常见 JOIN 性能问题排查实录4.1 经典案例跨库 JOIN 引起的慢查询“跨库 join”这个词在热词里出现了也确实是我被问到最多的问题之一。很多业务早期把不同模块的数据拆到不同库甚至不同实例然后用代码做二次查询后来为了省事改成在 MySQL 里直接跨库 JOIN。跨库 JOIN 本身只是多了一层库名限制只要在同一实例且账号有权限SQL 是可以执行的但它往往比同库 JOIN 更慢原因有两个一是不同库的表可能存储在不同的物理文件或磁盘上扫描时的IO路径更长二是跨库 JOIN 很难做分区裁剪和统计信息共享优化器可能拿到的是不精确的统计。我处理过一个案例业务库 A 的订单表 join 库 B 的用户表订单表每天千万级用户表百万级。初看是有索引的但执行计划显示用户表走了ALL。排查发现用户表的连接列是VARCHAR(50)而订单表的连接列是BIGINT因此 MySQL 先将用户表的连接列隐式转换为数字导致索引失效。这就是典型的“连接列类型不一致”造成的隐式转换。跨库只是表象真正的病根在字段定义。解决办法是把用户表的 id 列改成 BIGINT并迁移数据如果暂时不能改表可以在 SQL 里显式CAST或者CONVERT但这样会阻止索引使用属于临时方案。从这里也能看出遇到跨库 JOIN 慢先别急着怪“跨库”按普通 JOIN 的排查思路来十有八九能找到更具体的原因。4.2 优化器选错索引的应对优化器不是神仙它也会选错索引。最常见的情况是一张表上有联合索引(user_id, status)你在 JOIN 条件里用user_id但 WHERE 里只有status。优化器认为(status)的区分度更高就选了status上的索引。结果 join user 表时扫描行数反而增加。这种现象在统计信息不准、或者数据分布极度不均的时候特别常见。我的建议是先别急着用 FORCE INDEX。第一步重新分析表ANALYZE TABLE 表名;让统计信息更新。第二步看执行计划中估算 rows 和实际行数是否偏差很大如果偏差大就是用错了索引。第三步在 SQL 里加优化器提示/* INDEX(表名 索引名) */来指导优化器选择。用提示比 FORCE INDEX 更温和因为 FORCE INDEX 是硬性指定会使优化器完全丧失重新选择的能力。还有一个小技巧如果 WHERE 条件里的字段区分度本来就不好比如枚举类型只有三五个值即使它有索引优化器也很可能选择全表扫描因为回表成本太高。这种情况下应该考虑把 WHERE 条件和连接列组合成一个复合索引让它覆盖更多条件而不是把希望寄托在单个列索引上。4.3 JOIN 与排序的碰撞JOIN 后面跟 ORDER BY 是另一个高频坑位。MySQL 处理JOIN ... ORDER BY 被驱动表字段时如果 ORDER BY 的字段不在驱动表上也不在连接条件涉及的索引里就必然会用到 filesort。filesort 并不是“用文件排序”它可能发生在内存里但当结果集超过sort_buffer_size时就会产生磁盘临时文件性能断崖式下跌。优化思路有两种。第一种是让 ORDER BY 字段成为连接条件或驱动表的一部分使得排序可以直接利用索引顺序。比如驱动表是主表ORDER BY 主表的主键如果 JOIN 走的是 INLJ结果按主键输出就可能免去 filesort。第二种是减少参与排序的字段宽度只 SELECT 必要的列不要SELECT *因为排序的字段越多、行越宽sort buffer 能容纳的行就越少越容易落盘。如果 JOIN 的最终目的是“取每个分组最新的 N 条”这种需求不建议直接 JOIN而是先用窗口函数ROW_NUMBER() PARTITION BY在子查询里过滤再 JOIN 其他表。窗口函数在 MySQL 8.0 里性能稳定并且能显著减少 JOIN 后的排序和临时表压力。5. 一点扩展从单机 JOIN 到分布式思维5.1 数据分片对 JOIN 的影响当单表数据量达到几千万甚至上亿即便走了索引JOIN 的性能也可能无法满足要求。这时候很多人会想分库分表。但分库分表之后一个致命问题出现了——原来在同一库里的两张表现在可能分布在不同的数据节点上单条 SQL 里的 JOIN 将无法直接执行只能靠应用层或中间件做多路查询再合并。分布式 JOIN 的设计核心是“提前把连接键路由到同一节点”。比如订单表和用户表都按 user_id 分片那么同一个用户的订单和基本信息一定在同一个分片里JOIN 就可以在每个分片内并行执行再把结果汇总。这种思路叫“分片键对齐”。如果分片键不一致就要考虑在写入时冗余字段比如把用户姓名冗余进订单表这样查询时根本不需要 JOIN。这些都是架构层面的取舍。和单机优化相比分布式场景更强调“数据建模先行”写代码之前就得想清楚哪些字段要冗余、哪些表要一起分发。如果你的系统还在单机阶段别急着引入分库分表先把 JOIN 优化到极致才是性价比最高的方案。5.2 什么时候不该用 JOIN最后说说 JOIN 的边界。说实话JOIN 不是银弹有些场景在业务层面就不该用。比如两张超大规模的事实表做任意条件下的匹配即便 hash join 能跑资源消耗也非常大。更合理的方式是把数据导入到专门的分析引擎或者利用 ETL 预先加工成汇总表。又比如一对多关联后还需要统计汇总也尽量先缩聚合再 JOIN而不是等 JOIN 完再聚合。还有那些需要跨多个微服务查询数据的场景如果为了一个接口硬把多个库的数据 JOIN 到一起其实是把数据库当成了服务编排器这种做法会让数据库成为性能瓶颈也让服务难以水平扩展。我在实际研发中养成一个习惯每写一条有 JOIN 的查询先问自己三个问题——能否通过冗余字段避免 JOIN能否通过预先聚合缩小 JOIN 双方的数据量能否接受在应用层并多次简单查询后做内存匹配如果有一个答案是肯定的我会优先考虑改写。这不是说 JOIN 不好而是说作为开发者要在合适的地方用合适的工具。MySQL 的 JOIN 非常强大但我们的目标是让整体架构更清晰、性能更稳定而不是把压力都放在一条复杂 SQL 上。根据我个人的经验真正把 JOIN 优化做到位不是靠某一个技巧而是形成一个习惯每写一条 SQL都习惯性地去 EXPLAIN 一下看清 type 和 Extra再想一想到底是优化器的问题还是索引的问题还是 SQL 写法的问题。长期下来你对 MySQL 行为的直觉就会越来越准写出来的查询从一开始就是高效的。
MySQL JOIN算法与性能优化：从执行计划到实战排查
作为数据库性能调优里绕不开的一个硬骨头JOIN 慢、JOIN 卡、JOIN 把 CPU 打满几乎每个用 MySQL 的后端和 DBA 都遇到过。很多人一上来就甩一句“加索引”可有时候加了索引还是慢有时候优化器压根不用你建的索引这时候就得回到最底层去看——MySQL 到底用哪种 JOIN 算法在跑你的语句执行计划里又藏着哪些线索。这篇笔记从 JOIN 算法本身讲起结合执行计划的读法把排查和优化的思路串起来。适合被慢查询折磨过的业务开发也适合刚接触性能调优、想系统理解连接原理的 DBA。先说明一下这里讨论的 JOIN 都是基于 InnoDB 存储引擎版本以 MySQL 8.0 为主部分对比会提到 5.7。不同版本在优化器行为上有差异但理解了核心机制换版本你也能自己推断。1. JOIN 算法的演进与核心原理1.1 从嵌套循环说起最基础也最好理解的 JOIN 算法是 Nested Loop Join也就是嵌套循环连接。它的思路特别直白从驱动表也就是执行计划里排在前面的那张表取一行然后去被驱动表里找匹配行找到就返回接着取驱动表下一行再找一轮。整个过程就是两层循环外层遍历驱动表内层扫描被驱动表。如果你写过两层 for 循环一定能想象到这种方式的代价。假设驱动表有 M 行被驱动表有 N 行没有索引的情况下内层每次都要全表扫描那比较次数就是 M × N。一旦 M 和 N 都上了十万比较量就是十的十次方级别这个量级在 CPU 上跑起来就是灾难。所以真实场景里MySQL 不会傻乎乎地用纯全表扫描去做嵌套循环。只要被驱动表的连接列上有索引内层循环就可以通过索引快速定位到匹配行而不是扫全表。这种带索引的嵌套循环MySQL 叫 Index Nested-Loop JoinINLJ也是我们平时最希望走到的算法。它的成本大约等于外层扫描 M 行加上内层基于主键或二级索引的 M 次点查时间复杂度接近 O(M) 到 O(M log N)比笛卡尔式的全表扫描好太多。实操中你会发现想让优化器走 INLJ最重要的就是给被驱动表的连接列加上索引。这里有个容易踩的坑连接条件两侧的字段字符集不一致比如一张表 utf8mb4另一张表 latin1MySQL 可能无法直接使用索引做字符串比较导致虽然列上有索引执行计划却显示全表扫描。这个坑我后面会专门讲。1.2 块嵌套循环与缓存的意义纯嵌套循环是按“行”为单位去被驱动表找数据的每找一次都要发起一次存储引擎层的读取。如果被驱动表非常大且没有可用索引这种逐行读取的开销根本无法接受。于是 MySQL 引入了 Block Nested-Loop JoinBNL中文叫块嵌套循环连接。BNL 的核心思路是“批量缓存”。它不是从驱动表取一行就去被驱动表找一次而是先把驱动表的一批行放进 join buffer然后一次性取被驱动表的一个数据块在这个块里和 join buffer 中的所有行做匹配。这样一来被驱动表的扫描次数大幅减少从原来的 M 次降低到“被驱动表总块数 / join buffer 能装下的驱动表行数”这么多次。你可以把 join buffer 想象成一个大托盘服务员MySQL不再为每一道菜单独跑一趟厨房而是攒够一托盘再去端菜效率自然上去。BNL 的适用场景是没有索引的等值连接、部分非等值连接以及被驱动表特别大的情况。在 MySQL 5.7 里你会在执行计划的 Extra 列看到Using join buffer (Block Nested Loop)这样的提示。到了 MySQL 8.0优化器对 BNL 做了改进并且引入了 hash join很多原本走 BNL 的场景会自动转为 hash join。所以如果你还在 5.7 上看到 BNL建议认真检查一下被驱动表的连接列是不是缺索引因为 BNL 本质上是拿内存换时间当 join buffer 不够大时性能依旧不理想。有一点必须强调join buffer 是有大小限制的由参数join_buffer_size控制默认只有 256KB。如果你的驱动表一次装不满MySQL 就会分多次处理每次处理都重新扫描被驱动表。如果发现执行计划是 BNL 且被驱动表扫描次数很高适度调大这个参数可能有帮助但要注意它是会话级别的且是每线程独立分配的QPS 高的时候调太大会撑爆内存。1.3 哈希连接的引入MySQL 8.0.18 开始正式支持了 Hash Join。这个算法的思路是先把驱动表的连接列算成哈希值放到内存里的哈希表中然后扫描被驱动表对每一行计算连接列的哈希值到哈希表里探测是否匹配。由于哈希碰撞概率低每个探测基本是 O(1) 的复杂度所以总成本大约是被驱动表扫一遍的代价。Hash Join 特别适合两张表都没有合适索引但连接列是等值关系的场景。比如你在两张大表上做WHERE a.id b.user_id两边都没有索引嵌套循环会慢到怀疑人生hash join 却能利用内存高速完成匹配。之所以以前 MySQL 一直不引入 hash join除了历史原因还因为它在 OLTP 场景下不是主流需求加上内存开销和不确定的哈希冲突团队一直很谨慎。现在引入后很多没有索引的等值连接查询直接在优化器阶段就选用了 hash join相比 BNL 性能提升非常明显。不过 hash join 也不是万能的。它要求连接操作是等值连接非等值连接比如a.id b.id没法用。另外它需要把驱动表build成哈希表整个操作是内存密集型的如果驱动表太大导致哈希表溢出到磁盘性能就会急剧下降。所以在设计表结构时不要想着“反正有 hash join我可以不建索引了”这完全是把优化器的兜底方案当成了常规手段风险很大。2. 执行计划中的 JOIN 线索2.1 如何读懂 EXPLAIN 中的 type 与 Extra谈到 JOIN 性能分析EXPLAIN 是第一手资料。很多初学者只看 rows 列的估算值实际上 type 列和 Extra 列的信息量更大。type 列描述了访问类型从好到差大致是system const eq_ref ref range index ALL。在 JOIN 场景里如果被驱动表的访问类型是eq_ref或ref说明连接条件用到了主键或唯一索引这是最优状态如果是range说明用到了索引但做了范围扫描一般也能接受如果出现ALL那就是全表扫描大概率是连接列没索引或者优化器认为扫全表比走索引更快通常是表太小。Extra 列更值得玩味。看到Using index condition表示用到了索引下推ICP存储引擎层在索引层面就过滤掉不满足条件的行减少了回表看到Using where说明存储引擎返回后 Server 层还得再次过滤这时候通常要关注是不是有查询条件没被索引覆盖看到Using temporary说明查询用到了临时表常见于 GROUP BY、ORDER BY 和某些 JOIN 场景看到Using filesort说明需要额外排序这可能和 JOIN 的关联顺序有关。还有一个常被忽略的线索是Using join buffer (Block Nested Loop)在 5.7 里如果看到它基本可以断定被驱动表没有可用索引或者连接条件不是索引可直接定位的形式。在 8.0 中则可能显示Using join buffer (hash join)或Backward index scan等。多花点时间把这些标志记熟你就能在优化器做出选择的第一时间反应过来它为什么慢。注意EXPLAIN 给出的 rows 是估算值通常基于采样统计不一定准确但 type、Extra 是真实计划的反映。要拿更精确的耗时得用 EXPLAIN ANALYZE8.0.18。2.2 驱动表选择与执行顺序JOIN 的执行顺序不是简单的“SQL 里谁写在前面谁就是驱动表”。优化器会基于表大小、索引情况、过滤条件等多维度信息用成本模型算出不同的连接顺序选总成本最低的那一个。MySQL 默认使用贪婪搜索来减少候选计划的枚举次数但结果并不总是完美所以偶尔会出现“明明小表驱动大表更好优化器却选了大表驱动小表”的情况。理解这一点很重要因为驱动表的选择直接决定了外层扫描的行数。原则上驱动表应当是过滤后行数比较少的表被驱动表则应当让连接列走索引。如果你发现 EXPLAIN 中第一行的表不是预期中的小表可以通过 STRAIGHT_JOIN 强制指定连接顺序但不建议在业务代码里轻易使用因为它会干扰优化器的后续调整。更好的做法是更新统计信息ANALYZE TABLE、调整查询条件写法和索引设计让优化器自己做出正确选择。另外EXPLAIN中的 id 列可以帮你识别“子查询”和“派生表”的连接顺序。id 相同表示这两张表在同一个 JOIN 层级从上到下就是实际执行顺序id 不同且 id 值越大越先执行也就是子查询先被物化。如果发现子查询被物化成临时表再参与 JOIN临时表又没有索引那性能往往不好。这时候可以尝试用窗口函数或直接改写 JOIN避免物化带来的额外开销。2.3 用 EXPLAIN ANALYZE 看真实耗时MySQL 8.0.18 起EXPLAIN ANALYZE 可以实际执行语句并输出每一步的耗时、行数和循环次数。它的调用方式很简单EXPLAIN ANALYZE SELECT ...。和普通 EXPLAIN 不同它能告诉你“这步实际消耗了多少毫秒”、“实际处理了多少行”而不是估值。这是优化 JOIN 时我非常依赖的工具。举个例子如果你怀疑 JOIN 慢在被驱动表的反复扫描EXPLAIN ANALYZE 会打印类似Nested loop inner join (cost...) (actual time... rows... loops...)的信息。loops代表这一步被执行了多少次。如果loops很大而内层的rows很小说明每次只拿到几行但循环了很多次可能是指数选择有问题如果内层actual time很高那就说明单次访问索引的代价大需要检查索引结构或统计信息。EXPLAIN ANALYZE 会真实运行 SQL所以对线上的大查询要谨慎最好在预发布环境或只读副本上执行或者加上LIMIT来观察前几步的行为。很多时候一条慢 JOIN 的真实瓶颈并不在 JOIN 本身而在于驱动表上的 WHERE 过滤没有走索引导致驱动表扫了太多行。EXPLAIN ANALYZE 能一眼把这个幻觉打破。3. JOIN 性能优化的实操三板斧3.1 索引设计让连接条件走索引索引设计是 JOIN 优化里最关键、也最立竿见影的一步。对于INNER JOIN你应该在两张表的连接列上都建立索引这样无论优化器选择哪张表作为驱动表被驱动表都能快速匹配。对于LEFT JOIN左表的驱动地位通常不变所以右表的连接列必须有索引如果右表行特别多没有索引的 LEFT JOIN 会触发 BNL扫表扫到怀疑人生。这里有一个常见误区以为只要连接列类型相同就行忽略了排序规则和字符集。比如一张表的连接列是utf8mb4_unicode_ci另一张是utf8mb4_general_ci虽然都是 utf8mb4但字符集排序规则不同MySQL 就无法直接使用索引进行字符串比较需要在内存中做转换执行计划里连接列的 type 会变成ALL或ref但带Using where。最简单的解决办法是统一字符集和排序规则或者在 SQL 里显式加COLLATE但后者会阻止索引使用所以更推荐前者。另外连接列如果是复合索引中的第二列单独用它做 JOIN 不一定能走索引。比如表上有(a, b)复合索引而查询用b做连接索引就无法直接用于等值匹配。这时就要考虑增加一个以 b 开头的索引或者调整查询结构。别盲目建索引先看执行计划再对照索引结构才能精准命中问题。3.2 驱动表与查询条件裁剪在实际工作中我发现很多 JOIN 慢的源头不是 JOIN 本身而是驱动表太大。比如一条查询写的是SELECT * FROM orders o JOIN users u ON o.user_id u.id WHERE o.created_at 2024-01-01如果 orders 上有年份分区那么驱动表 orders 会被裁剪到很小。但如果没分区、没索引MySQL 只能全表扫 orders然后再去 users 表匹配。所以驱动表的 WHERE 条件有没有走索引直接决定了外层扫描量。在这个地方最实用的一招是“先过滤后 JOIN”。用子查询或者派生表把两张表需要的数据提前缩小再连接。比如SELECT o.id, u.name FROM (SELECT id, user_id, amount FROM orders WHERE created_at 2024-01-01) o JOIN (SELECT id, name FROM users WHERE status 1) u ON o.user_id u.id;不过要注意MySQL 8.0 的优化器会自动对派生表做合并或物化有时你的子查询会被合并回原表执行计划未必按你写的来。用EXPLAIN FORMATTREE或 EXPLAIN ANALYZE 观察实际效果如果计划不理想可以尝试用/* derived_condition_pushdown() */等优化器提示来控制。总体思路是让驱动表尽可能小被驱动表尽可能走索引。3.3 改写 SQL 与临时表策略有些 JOIN 问题可以通过改写 SQL 绕过去。比如多表 JOIN 的 ON 条件里带 OR这会大大限制索引使用。ON a.id b.id OR a.id c.id这种写法基本不可能走索引只能扫描后计算。遇到这种需求通常可以拆成两个 JOIN用 UNION 合并结果或者用 IN 子查询替代部分逻辑。再有就是 GROUP BY 与 JOIN 的组合。很多慢查询是先把两张表 JOIN 得到宽表再分组统计导致临时表巨大。更聪明的做法是先在各自表里做聚合再把聚合结果 JOIN。例如SELECT u.id, COALESCE(SUM(o.amount), 0) FROM users u LEFT JOIN orders o ON o.user_id u.id GROUP BY u.id;如果 orders 表巨大这个 LEFT JOIN 会把所有订单行都牵进来再分组非常浪费。你可以改成SELECT u.id, COALESCE(t.total, 0) FROM users u LEFT JOIN ( SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id ) t ON t.user_id u.id;这样 orders 只扫一遍做聚合再和 users JOIN行数大大减少性能提升通常非常明显。这种“能先聚合就先聚合”的思路在 JOIN 优化中百试不爽。4. 常见 JOIN 性能问题排查实录4.1 经典案例跨库 JOIN 引起的慢查询“跨库 join”这个词在热词里出现了也确实是我被问到最多的问题之一。很多业务早期把不同模块的数据拆到不同库甚至不同实例然后用代码做二次查询后来为了省事改成在 MySQL 里直接跨库 JOIN。跨库 JOIN 本身只是多了一层库名限制只要在同一实例且账号有权限SQL 是可以执行的但它往往比同库 JOIN 更慢原因有两个一是不同库的表可能存储在不同的物理文件或磁盘上扫描时的IO路径更长二是跨库 JOIN 很难做分区裁剪和统计信息共享优化器可能拿到的是不精确的统计。我处理过一个案例业务库 A 的订单表 join 库 B 的用户表订单表每天千万级用户表百万级。初看是有索引的但执行计划显示用户表走了ALL。排查发现用户表的连接列是VARCHAR(50)而订单表的连接列是BIGINT因此 MySQL 先将用户表的连接列隐式转换为数字导致索引失效。这就是典型的“连接列类型不一致”造成的隐式转换。跨库只是表象真正的病根在字段定义。解决办法是把用户表的 id 列改成 BIGINT并迁移数据如果暂时不能改表可以在 SQL 里显式CAST或者CONVERT但这样会阻止索引使用属于临时方案。从这里也能看出遇到跨库 JOIN 慢先别急着怪“跨库”按普通 JOIN 的排查思路来十有八九能找到更具体的原因。4.2 优化器选错索引的应对优化器不是神仙它也会选错索引。最常见的情况是一张表上有联合索引(user_id, status)你在 JOIN 条件里用user_id但 WHERE 里只有status。优化器认为(status)的区分度更高就选了status上的索引。结果 join user 表时扫描行数反而增加。这种现象在统计信息不准、或者数据分布极度不均的时候特别常见。我的建议是先别急着用 FORCE INDEX。第一步重新分析表ANALYZE TABLE 表名;让统计信息更新。第二步看执行计划中估算 rows 和实际行数是否偏差很大如果偏差大就是用错了索引。第三步在 SQL 里加优化器提示/* INDEX(表名 索引名) */来指导优化器选择。用提示比 FORCE INDEX 更温和因为 FORCE INDEX 是硬性指定会使优化器完全丧失重新选择的能力。还有一个小技巧如果 WHERE 条件里的字段区分度本来就不好比如枚举类型只有三五个值即使它有索引优化器也很可能选择全表扫描因为回表成本太高。这种情况下应该考虑把 WHERE 条件和连接列组合成一个复合索引让它覆盖更多条件而不是把希望寄托在单个列索引上。4.3 JOIN 与排序的碰撞JOIN 后面跟 ORDER BY 是另一个高频坑位。MySQL 处理JOIN ... ORDER BY 被驱动表字段时如果 ORDER BY 的字段不在驱动表上也不在连接条件涉及的索引里就必然会用到 filesort。filesort 并不是“用文件排序”它可能发生在内存里但当结果集超过sort_buffer_size时就会产生磁盘临时文件性能断崖式下跌。优化思路有两种。第一种是让 ORDER BY 字段成为连接条件或驱动表的一部分使得排序可以直接利用索引顺序。比如驱动表是主表ORDER BY 主表的主键如果 JOIN 走的是 INLJ结果按主键输出就可能免去 filesort。第二种是减少参与排序的字段宽度只 SELECT 必要的列不要SELECT *因为排序的字段越多、行越宽sort buffer 能容纳的行就越少越容易落盘。如果 JOIN 的最终目的是“取每个分组最新的 N 条”这种需求不建议直接 JOIN而是先用窗口函数ROW_NUMBER() PARTITION BY在子查询里过滤再 JOIN 其他表。窗口函数在 MySQL 8.0 里性能稳定并且能显著减少 JOIN 后的排序和临时表压力。5. 一点扩展从单机 JOIN 到分布式思维5.1 数据分片对 JOIN 的影响当单表数据量达到几千万甚至上亿即便走了索引JOIN 的性能也可能无法满足要求。这时候很多人会想分库分表。但分库分表之后一个致命问题出现了——原来在同一库里的两张表现在可能分布在不同的数据节点上单条 SQL 里的 JOIN 将无法直接执行只能靠应用层或中间件做多路查询再合并。分布式 JOIN 的设计核心是“提前把连接键路由到同一节点”。比如订单表和用户表都按 user_id 分片那么同一个用户的订单和基本信息一定在同一个分片里JOIN 就可以在每个分片内并行执行再把结果汇总。这种思路叫“分片键对齐”。如果分片键不一致就要考虑在写入时冗余字段比如把用户姓名冗余进订单表这样查询时根本不需要 JOIN。这些都是架构层面的取舍。和单机优化相比分布式场景更强调“数据建模先行”写代码之前就得想清楚哪些字段要冗余、哪些表要一起分发。如果你的系统还在单机阶段别急着引入分库分表先把 JOIN 优化到极致才是性价比最高的方案。5.2 什么时候不该用 JOIN最后说说 JOIN 的边界。说实话JOIN 不是银弹有些场景在业务层面就不该用。比如两张超大规模的事实表做任意条件下的匹配即便 hash join 能跑资源消耗也非常大。更合理的方式是把数据导入到专门的分析引擎或者利用 ETL 预先加工成汇总表。又比如一对多关联后还需要统计汇总也尽量先缩聚合再 JOIN而不是等 JOIN 完再聚合。还有那些需要跨多个微服务查询数据的场景如果为了一个接口硬把多个库的数据 JOIN 到一起其实是把数据库当成了服务编排器这种做法会让数据库成为性能瓶颈也让服务难以水平扩展。我在实际研发中养成一个习惯每写一条有 JOIN 的查询先问自己三个问题——能否通过冗余字段避免 JOIN能否通过预先聚合缩小 JOIN 双方的数据量能否接受在应用层并多次简单查询后做内存匹配如果有一个答案是肯定的我会优先考虑改写。这不是说 JOIN 不好而是说作为开发者要在合适的地方用合适的工具。MySQL 的 JOIN 非常强大但我们的目标是让整体架构更清晰、性能更稳定而不是把压力都放在一条复杂 SQL 上。根据我个人的经验真正把 JOIN 优化做到位不是靠某一个技巧而是形成一个习惯每写一条 SQL都习惯性地去 EXPLAIN 一下看清 type 和 Extra再想一想到底是优化器的问题还是索引的问题还是 SQL 写法的问题。长期下来你对 MySQL 行为的直觉就会越来越准写出来的查询从一开始就是高效的。