当前位置： 首页 > news >正文

MySQL JOIN 优化详解

news 2026/5/24 8:52:35

我刚工作的时候，有次上线了个新功能，结果有个 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 的两种算法、驱动表的选择、优化步骤讲清楚，面试官绝对觉得你是高级开发。

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

http://www.jsqmd.com/news/876236/

相关文章：

  • Frida Hook Java层还原Android客户端签名算法
  • Spectre与Meltdown漏洞：原理、影响与防护措施
  • Mermaid Live Editor：为什么每个开发者都需要这个实时图表编辑神器？
  • 分期乐京东e卡回收安全吗？三分钟了解回收全流程 - 团团收购物卡回收
  • 2026年亲测必备：10个论文降AI工具，免费将AI率降至5%以下（附避坑教程） - 降AI实验室
  • E7Helper第七史诗自动化助手：新手也能轻松上手的终极游戏解放方案
  • MySQL 子查询优化：从慢查询到飞起的实战之路
  • 长沙手表变现不被坑的密码，合扬本地老店实测封神 - 李宏哲1
  • PotPlayer字幕翻译插件：5分钟实现外语影视无障碍观看的终极免费方案
  • 专业级AMD Ryzen调试工具SMUDebugTool：深度解析与实战应用指南
  • 深入解析大模型架构之争：全能通用模型 vs 领域专精模型
  • WechatDecrypt终极指南：3步快速解密你的微信聊天数据库
  • CentOS 7上编译安装glibc 2.28，我踩过的那些坑（附完整排错流程）
  • 基于ASAR文件系统解析的WeMod客户端增强框架技术实现
  • Docker .dockerignore 完全指南
  • 教你在分期乐京东e卡回收平台上快速提现的秘诀 - 团团收购物卡回收
  • 揭秘分期乐京东e卡回收平台：快速变现的最佳选择 - 团团收购物卡回收
  • 安卓逆向实战：用Frida Hook Java层还原API-Sign签名算法
  • RDPWrap配置踩坑实录：更新rdpwrap.ini文件解决Listener state不支持问题
  • 【最新 v 2.7.5】从“手动搬砖“到“AI 代劳“：Windows 一键部署 Open Claw，效率差距就是这么拉开的
  • TeamSpeak 3权限与防火墙配置深度解析
  • 2026南京GEO优化公司实测盘点TOP5 避坑选型指南 - 小艾信息发布
  • 免费开源的AMD Ryzen调试神器：SMUDebugTool完全指南
  • XHS-Downloader：智能高效的小红书内容采集与下载解决方案
  • 终极解决方案：3分钟让浏览器变身微信客户端，告别登录限制
  • NCM转MP3完整指南：3步解锁网易云音乐加密文件
  • Android 17 适配实战指南：新特性解读、隐私变更与迁移全攻略
  • C# OpenCvSharp内存管理陷阱与性能优化指南
  • 5分钟部署企业级PDF处理能力：Poppler Windows预编译包实战指南
  • 双层优化与线性规划：超参数调优的高效混合策略