news 2026/5/24 8:48:57

MySQL JOIN 优化详解

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
MySQL JOIN 优化详解

我刚工作的时候，有次上线了个新功能，结果有个 JOIN 查询慢得要命，用户投诉电话被打爆。DBA 帮我一看执行计划，发现驱动表选错了，扫描了 2000 万行。

从那以后，我每次写 JOIN 查询都会用EXPLAIN看看执行计划，避免线上事故。

今天咱们就来彻底搞懂 MySQL 的 JOIN 优化，看完这篇，你就能写出高性能的 JOIN 查询了。

JOIN 的两种算法

MySQL 的 JOIN 有两种算法：Nested Loop Join（嵌套循环连接）和Hash Join（哈希连接，MySQL 8.0+）。

1. Nested Loop Join（NLJ，默认）

NLJ 的思路很简单：双层循环。

for each row in t1 { -- 外层循环（驱动表） for each row in t2 { -- 内层循环（被驱动表） if (join_condition) { output row } } } ``` **问题**：如果 t1 有 1000 行，t2 有 10000 行，那内层循环要执行 1000 × 10000 = 1000 万次！ **优化**：给被驱动表的关联字段加索引，这样内层循环不需要全表扫描（变成 **Index Nested Loop Join，INLJ**）。 ### 2. Hash Join（HJ，MySQL 8.0+） Hash Join 的思路：先给被驱动表建哈希表，再遍历驱动表去匹配。

– 第 1 步：给 t2 建哈希表（按关联字段）
hash_table = build_hash_table(t2, join_key)

– 第 2 步：遍历 t1，去哈希表匹配
for each row in t1 {
if (hash_table.contains(t1.join_key)) {
output row
}
}
```
优势：不需要索引，适合大表关联。

劣势：只能做等值关联（=），不能做非等值关联（<>、<=、BETWEEN）。

驱动表 vs 被驱动表

JOIN 查询有两个表：驱动表（Driver Table）和被驱动表（Driven Table）。

  • 驱动表：外层循环，全表扫描或者用索引
    • 被驱动表：内层循环，用索引匹配
      关键：驱动表选错了，性能会差 10 倍！

怎么选驱动表？

原则：小表驱动大表（驱动表的数据量少）。

-- 假设 users 表有 1000 行，orders 表有 1000000 行SELECT*FROMusers uJOINorders oONu.id=o.user_id;

优化器会选择：users 作为驱动表（1000 行），orders 作为被驱动表（用user_id索引匹配）。

为什么？因为外层循环的次数决定了内层循环要执行多少次。如果驱动表小，内层循环执行次数就少。

验证一下

EXPLAINSELECT*FROMusers uJOINorders oONu.id=o.user_id;

输出：

+----+-------------+-------+-------+---------------+---------+---------+-----------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-----------------+------+-------+ | 1 | SIMPLE | u | ALL | PRIMARY | NULL | NULL | NULL | 1000 | | | 1 | SIMPLE | o | ref | idx_user_id | idx_user_id | 5 | db.u.id | 10 | | +----+-------------+-------+-------+---------------+---------+---------+-----------------+------+-------+

解读：

  1. 第 1 行：table = u（users 是驱动表），type = ALL（全表扫描，因为要遍历所有行）
    1. 第 2 行：table = o（orders 是被驱动表），type = ref（用索引匹配），ref = db.u.id（关联字段）
      总扫描行数：1000（驱动表）+ 1000 × 10（被驱动表，平均每次匹配 10 行）= 11000 行。

如果驱动表选反了（orders 是驱动表）：

总扫描行数：1000000（驱动表）+ 1000000 × 1（被驱动表，假设 users 每张表只有 1 行匹配）= 2000000 行。

性能差距：2000000 / 11000 ≈ 181 倍！

优化 JOIN 的实战步骤

咱们来个实际案例：假设要查所有用户的订单，SQL 很慢。

第 1 步：看执行计划

EXPLAINSELECT*FROMusers uJOINorders oONu.id=o.user_idWHEREu.age>20;

输出：

+----+-------------+-------+-------+---------------+---------+---------+-----------------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-----------------+----------+-------------+ | 1 | SIMPLE | u | range | idx_age | idx_age | 5 | NULL | 500000 | Using where | | 1 | SIMPLE | o | ref | idx_user_id | idx_user_id | 5 | db.u.id | 10 | | +----+-------------+-------+-------+---------------+---------+---------+-----------------+----------+-------------+

问题：

  1. 驱动表u（users）扫了 500000 行（rows = 500000）
    1. 被驱动表o（orders）平均每次匹配 10 行，总匹配 500000 × 10 = 5000000 行
    1. 总扫描行数：500000 + 5000000 = 5500000 行

第 2 步：优化驱动表的选择

问题：WHERE u.age > 20过滤后还有 500000 行，驱动表太大了。

方案：如果orders表过滤后行数更少，可以换个写法让优化器选择orders作为驱动表。

-- 假设我们要查 "2024 年下的订单" 的用户信息EXPLAINSELECT*FROMusers uJOINorders oONu.id=o.user_idWHEREo.created_at>'2024-01-01';

输出：

+----+-------------+-------+-------+---------------+---------+---------+-----------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-----------------+------+-------+ | 1 | SIMPLE | o | range | idx_created | idx_created | 6 | NULL | 1000 | | | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | db.o.user_id | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-----------------+------+-------+

优化效果：

  1. 驱动表变成了o（orders），只扫 1000 行
    1. 被驱动表u（users）平均每次匹配 1 行，总匹配 1000 × 1 = 1000 行
    1. 总扫描行数：1000 + 1000 = 2000 行（从 5500000 降到 2000，2750 倍提升！）

第 3 步：确保被驱动表的关联字段有索引

如果被驱动表的关联字段没索引，会导致Block Nested Loop Join（BNLJ），性能极差。

-- 假设 orders.user_id 没有索引EXPLAINSELECT*FROMusers uJOINorders oONu.id=o.user_id;

输出：

+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------------+ | 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 1000 | | | 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 1000000 | Using join buffer (Block Nested Loop) | +----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------------+

问题：

  1. Extra = Using join buffer (Block Nested Loop)：用了 BNLJ 算法
    1. BNLJ 会把驱动表的数据放到join buffer，然后全表扫描被驱动表匹配
    1. 如果被驱动表很大，join buffer放不下，会分多次全表扫描（性能炸裂）
      解决方案：给被驱动表的关联字段加索引。
CREATEINDEXidx_user_idONorders(user_id);

常见 JOIN 类型及优化

1. INNER JOIN（内连接）

SELECT*FROMusers uINNERJOINorders oONu.id=o.user_id;

优化：

  • 给被驱动表的关联字段加索引
    • 让小表驱动大表（优化器会自动选，但你可以用STRAIGHT_JOIN强制顺序）
-- 强制驱动表顺序（u 是驱动表，o 是被驱动表）SELECT*FROMusers u STRAIGHT_JOIN orders oONu.id=o.user_id;

2. LEFT JOIN（左连接）

SELECT*FROMusers uLEFTJOINorders oONu.id=o.user_id;

特点：LEFT JOIN 的左表一定是驱动表（因为要保留左表的所有行）。

优化：

  • 给右表（被驱动表）的关联字段加索引
    • 如果右表太大，考虑用子查询过滤后再 JOIN
-- 优化前：右表太大SELECT*FROMusers uLEFTJOINorders oONu.id=o.user_id;-- 优化后：先过滤右表SELECT*FROMusers uLEFTJOIN(SELECT*FROMordersWHEREcreated_at>'2024-01-01')oONu.id=o.user_id;

3. RIGHT JOIN（右连接）

SELECT*FROMusers uRIGHTJOINorders oONu.id=o.user_id;

特点：RIGHT JOIN 的右表一定是驱动表。

建议：尽量用 LEFT JOIN 代替 RIGHT JOIN（更符合阅读习惯）。

4. 多表 JOIN（>2 张表）

SELECT*FROMt1JOINt2ONt1.id=t2.t1_idJOINt3ONt2.id=t3.t2_id;

优化：

  • 给所有被驱动表的关联字段加索引
    • 调整 JOIN 顺序，让小表驱动大表
      如果优化器选错了驱动表，用STRAIGHT_JOIN强制顺序：
SELECT*FROMt1 STRAIGHT_JOIN t2ONt1.id=t2.t1_id STRAIGHT_JOIN t3ONt2.id=t3.t2_id;

实战建议

1. 给被驱动表的关联字段加索引（最重要！）

这是最重要的建议。如果被驱动表的关联字段没索引，会导致 BNLJ，性能极差。

-- 检查关联字段有没有索引EXPLAINSELECT*FROMusers uJOINorders oONu.id=o.user_id;-- 如果被驱动表 type = ALL，说明没走索引

2. 让小表驱动大表

如果优化器选错了驱动表，用STRAIGHT_JOIN强制顺序。

-- 强制驱动表顺序SELECT*FROMsmall_table STRAIGHT_JOIN big_tableON...;

3. 先过滤再 JOIN

如果某张表可以先过滤（WHERE 条件），先过滤再 JOIN，减少驱动表的数据量。

-- 优化前：先 JOIN 再过滤SELECT*FROMusers uJOINorders oONu.id=o.user_idWHEREo.created_at>'2024-01-01';-- 优化后：先过滤再 JOINSELECT*FROMusers uJOIN(SELECT*FROMordersWHEREcreated_at>'2024-01-01')oONu.id=o.user_id;

4. 避免 SELECT *

**SELECT *** 会查所有字段，增加网络传输和内存消耗，而且无法用覆盖索引。

建议：只查需要的字段。

-- 优化前SELECT*FROMusers uJOINorders oONu.id=o.user_id;-- 优化后SELECTu.id,u.name,o.id,o.amountFROMusers uJOINorders oONu.id=o.user_id;

5. 用 EXPLAIN 检查执行计划

每次写完 JOIN 查询，都用EXPLAIN看看执行计划，确保：

  1. 被驱动表的关联字段走了索引（type = ref或eq_ref）
    1. 驱动表的数据量尽量小（rows尽量小）
    1. Extra里没有Using join buffer (Block Nested Loop)

总结

  • MySQL 的 JOIN 有两种算法：Nested Loop Join（默认）和 Hash Join（MySQL 8.0+）
    • JOIN 查询有驱动表（外层循环）和被驱动表（内层循环），要让小表驱动大表
    • 优化 JOIN 的步骤：看执行计划 → 确保被驱动表的关联字段有索引 → 让小表驱动大表 → 先过滤再 JOIN
    • 常见 JOIN 类型：INNER JOIN、LEFT JOIN、RIGHT JOIN、多表 JOIN
    • 实战建议：给被驱动表的关联字段加索引、让小表驱动大表、先过滤再 JOIN、避免 SELECT *、用 EXPLAIN 检查执行计划
      如果你能把 JOIN 的两种算法、驱动表的选择、优化步骤讲清楚，面试官绝对觉得你是高级开发。

实战代码都在我本地跑过，你可以放心复制。如果有问题，欢迎评论区交流！

版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/5/24 8:45:20

C#中BindingList的作用小结

在C#中&#xff0c;BindingList<T> 是一个非常重要的集合类&#xff0c;位于 System.ComponentModel 命名空间&#xff0c;主要用于实现数据绑定&#xff08;Data Binding&#xff09;场景。 1. 核心作用 BindingList<T> 是 List<T> 的增强版&#xff0c;主…

作者头像 李华
网站建设 2026/5/24 8:44:16

Warcraft Helper终极指南：让魔兽争霸3在现代系统焕发新生

Warcraft Helper终极指南&#xff1a;让魔兽争霸3在现代系统焕发新生 【免费下载链接】WarcraftHelper Warcraft III Helper , support 1.20e, 1.24e, 1.26a, 1.27a, 1.27b 项目地址: https://gitcode.com/gh_mirrors/wa/WarcraftHelper 还在为魔兽争霸3在Windows 10/11…

作者头像 李华
网站建设 2026/5/24 8:41:28

XXMI启动器：游戏模组管理的终极智能革命

XXMI启动器&#xff1a;游戏模组管理的终极智能革命 【免费下载链接】XXMI-Launcher Modding platform for GI, HSR, WW and ZZZ 项目地址: https://gitcode.com/gh_mirrors/xx/XXMI-Launcher 还在为游戏模组管理而烦恼吗&#xff1f;每次想给心爱的角色换个新造型&…

作者头像 李华
网站建设 2026/5/24 8:40:39

给 Agent 起名的艺术

《给 Agent 起名的艺术&#xff1a;从技术辨识度到情感共鸣&#xff0c;让你的 AI 伙伴“开口即让人记住”》 副标题&#xff1a;大模型时代的AI身份构建方法论&#xff0c;附百万级曝光命名的公式、避坑指南与多场景命名库 引言 痛点引入 你有没有过这种经历&#xff1f; 熬…

作者头像 李华
网站建设 2026/5/24 8:38:34

JetBrains IDE试用期重置终极指南：三步轻松恢复30天试用

JetBrains IDE试用期重置终极指南&#xff1a;三步轻松恢复30天试用 【免费下载链接】ide-eval-resetter 项目地址: https://gitcode.com/gh_mirrors/id/ide-eval-resetter 你是否曾因JetBrains IDE试用期到期而苦恼&#xff1f;ide-eval-resetter正是解决这一痛点的终…

作者头像 李华