深入详解数据库中的 INNER JOIN 与 LEFT JOIN:详细剖析与实例

[复制链接]
发表于 8 小时前 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?立即注册

×
核心结论与决策建议(BLUF)
在 MySQL 关系型数据关联体系中,
  1. INNER JOIN
复制代码
(内连接)与
  1. LEFT JOIN
复制代码
(左外连接)代表了截然不同的数学集合运算逻辑与查询优化器处理策略。
  1. INNER JOIN
复制代码
仅返回两表关联字段完全吻合的交集行,优化器基于基于成本的优化模型(CBO)拥有绝对自由度自动颠倒驱动表与被驱动表的关联顺序;而
  1. LEFT JOIN
复制代码
强制保留左表所有行,在缺乏 WHERE 过滤的情况下,优化器必须将左表作为驱动表,若左表数据量庞大且右表缺乏可用索引,将直接触发灾难级的全表笛卡尔积或哈希构建。深入理解两者的驱动机制与索引覆盖是彻底规避慢查询的根基。

关联查询类别集合论定义无匹配数据处理优化器驱动表自主决策权执行计划与索引关键考量
INNER JOINA ∩ B(两表交集)不匹配的行直接丢弃完全自由;CBO 自动选择较小的有效过滤集作为驱动表只需在任意一侧的关键关联列建高效索引即可
LEFT JOINA ∪ (A ∩ B)(左表全集)右表无匹配列全部填充 NULL受限;强制固定左表为驱动表(驱动顺序固定)被驱动表(右表)的 ON 关联字段必须建高效索引
LEFT JOIN + IS NULLA - (A ∩ B)(差集运算)专用于查找左表有而右表没有的数据固定左表驱动;扫描右表索引后过滤 NULL相比 NOT IN 或 NOT EXISTS 在大数据量下效率更优


一、核心概念与执行机制深度解剖

1.1 内连接(INNER JOIN)的灵活选路
内连接的语义是严格的等值匹配交集。以下两条 SQL 在逻辑与执行结果上完全等价:

  1. -- 书写形式一:A 在前,B 在后
  2. SELECT u.name, o.order_sn
  3. FROM users u
  4. INNER JOIN orders o ON u.id = o.user_id
  5. WHERE o.order_amount > 1000;

  6. -- 书写形式二:B 在前,A 在后
  7. SELECT u.name, o.order_sn
  8. FROM orders o
  9. INNER JOIN users u ON o.user_id = u.id
  10. WHERE o.order_amount > 1000;
复制代码

底层优化器行为:MySQL 优化器在解析上述查询时,会先通过统计信息预估:
  1. users
复制代码
全表有 100 万行,而满足
  1. order_amount > 1000
复制代码
的
  1. orders
复制代码
记录仅有 200 条。优化器会自动颠倒顺序,强制选择过滤后仅剩 200 行的
  1. orders
复制代码
作为驱动表,进而去
  1. users
复制代码
的主键索引上点查 200 次,极速完成查询。

1.2 左外连接(LEFT JOIN)的刚性驱动约束
左外连接的核心语义是:无论右表是否存在对应匹配,左表中的每一条记录都必须至少出现一次!

  1. -- 查询全部员工及其关联的绩效评分(无绩效的新员工也必须列出)
  2. SELECT e.emp_id, e.emp_name, p.score
  3. FROM employee e
  4. LEFT JOIN performance p ON e.emp_id = p.emp_id
  5.     AND p.eval_year = 2026;
复制代码

底层约束与性能命脉:由于左表
  1. employee
复制代码
必须全量输出,优化器别无选择,只能将
  1. employee
复制代码
固定为驱动表。为了保证性能,必须在被驱动表 performance 的 (emp_id, eval_year) 上建立复合索引!如果被驱动表缺乏索引,MySQL 将被迫为左表的每一行对右表进行全表扫描或构建 Hash 表,性能呈断崖式下跌。

二、实战代码示范与经典避坑

2.1 致命陷阱:在 WHERE 子句中过滤右表导致 LEFT JOIN 退化
许多工程师经常写出如下包含逻辑矛盾的 SQL:

  1. -- 错误示范:在 WHERE 中加入右表过滤,导致外连接语义完全失效!
  2. SELECT u.id, u.user_name, o.order_sn
  3. FROM users u
  4. LEFT JOIN orders o ON u.id = o.user_id
  5. WHERE o.status = 'PAID';
复制代码

底层灾难揭秘:对于没有订单的用户,LEFT JOIN 会为右表的
  1. status
复制代码
填充 NULL。然而后续的
  1. WHERE o.status = 'PAID'
复制代码
会将所有 status 为 NULL 的行全部丢弃!优化器检测到这一矛盾后,会直接将该 SQL 在内部强行改写优化为
  1. INNER JOIN
复制代码
。
正确写法:

  1. -- 正确示范:将右表的过滤条件放入 ON 子句中
  2. SELECT u.id, u.user_name, o.order_sn
  3. FROM users u
  4. LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'PAID';
复制代码


2.2 1:N 关联导致的数据集膨胀(Data Explosion)
当用户表(1)与角色表(N)进行 LEFT JOIN 时,若一个用户拥有 5 个角色,LEFT JOIN 会直接将该用户复制膨胀为 5 行。若此时在 SQL 末尾添加
  1. LIMIT 10
复制代码
,实际最终只展示了 2 个用户(共占 10 行)。处理这类主子表分页,必须采用延迟关联(Deferred Join):

  1. -- 延迟关联标准写法:先在单表完成分页确定主键,再做外连接获取详情
  2. SELECT u.id, u.user_name, r.role_name
  3. FROM (
  4.     SELECT id FROM users ORDER BY id ASC LIMIT 10 OFFSET 0
  5. ) AS page_u
  6. JOIN users u ON page_u.id = u.id
  7. LEFT JOIN user_role ur ON u.id = ur.user_id
  8. LEFT JOIN roles r ON ur.role_id = r.id;
复制代码


三、关联查询工业级调优三大军规
1. 确保关联列字符集与排序规则完全一致:若 A 表为
  1. utf8mb4_general_ci
复制代码
,B 表为
  1. utf8mb4_0900_ai_ci
复制代码
,两表关联时会引发隐式字符转换函数调用,导致被驱动表即使有索引也完全无法命中;
2. 小表驱动大表原则:利用驱动表最小的结果集,通过索引树快速定位被驱动表,将循环次数压至最低;
3. 避免多表全字段 SELECT *:多表关联时取出大量不必要的 TEXT 或宽字段,会瞬间撑爆
  1. join_buffer_size
复制代码
,引发临时文件磁盘落盘与巨大的网络传输负载。

常见问题(FAQ)
Q1: 为什么有时候业务需要查询“在 A 表中存在但在 B 表中不存在”的数据,DBA 推荐使用 LEFT JOIN ... IS NULL 而不是 NOT IN?
A1: 因为在标准 SQL 中,若
  1. NOT IN (SELECT id FROM B)
复制代码
子查询的结果集中包含任何一条 NULL 值,整个
  1. NOT IN
复制代码
表达式会全部失效并恒等返回空集(三值逻辑陷阱);同时在早期版本中,子查询无法有效推导,性能极其低下。而采用
  1. SELECT A.* FROM A LEFT JOIN B ON A.id = B.id WHERE B.id IS NULL
复制代码
,优化器可以直接利用右表主键做高效的 Anti-Join 反连接探测,性能稳定且语义绝对安全。

Q2: 已经为两张表的关联字段建立了索引,为什么 EXPLAIN 发现依然没有走 Index Nested-Loop,而是显示 Using join buffer (hash join)?
A2: 最常见的原因包括:两表的关联字段数据类型不严格一致(例如一个是 INT 另一个是 BIGINT,或者一个是 VARCHAR(32) 另一个是 VARCHAR(64)),导致底层发生了类型隐式转换;或者字段的校对规则不同。在无法直接命中 B+ 树点查时,MySQL 8.0 会自动降级采用 Hash Join 机制在内存中构建哈希表来加速匹配。



qidao123.com企服评测-应用市场 编辑提示:本文由企服大模型基于真实系统运维实践重构整理,聚焦技术原理、性能对比与选型避坑。若在实际部署中遇到异常,欢迎在下方跟帖交流;如需企业级技术支持或深度运维解决方案,请访问 qidao123.com 获取专属支持。
回复

使用道具 举报

登录后关闭弹窗

登录参与点评抽奖  加入IT实名职场社区
去登录
快速回复 返回顶部 返回列表