查询与 Join

Join 是把多张表的数据按关系组合起来。零基础最容易犯的错是：只会写出结果，不知道数据库为了得到结果扫描了多少数据。

Join 是什么

假设订单表只保存 user_id，用户名称在用户表里：

sql

select o.order_no, o.amount_cent, u.username
from orders o
join users u on o.user_id = u.id
where o.user_id = 1001;

这条 SQL 的意思是：

从订单表找到用户 1001 的订单。
用订单里的 user_id 去用户表找用户。
把两边字段拼成结果。

Join 类型

类型	结果
`inner join`	两边都匹配才返回
`left join`	左表一定返回，右表没有则为 `null`
`right join`	右表一定返回，左表没有则为 `null`

商业项目里最常用的是 inner join 和 left join。

Demo 表

sql

create table users (
  id bigint primary key auto_increment,
  username varchar(50) not null
) engine = InnoDB default charset = utf8mb4;

create table orders (
  id bigint primary key auto_increment,
  order_no varchar(64) not null,
  user_id bigint not null,
  amount_cent bigint not null,
  created_at datetime not null,
  unique key uk_order_no (order_no),
  key idx_user_created (user_id, created_at)
) engine = InnoDB default charset = utf8mb4;

Join 执行原理

MySQL 常见 Join 可以理解为：先从驱动表取一批行，再去被驱动表匹配。

mermaid

flowchart TD
    A["确定驱动表"] --> B["从驱动表取行"]
    B --> C["拿连接字段"]
    C --> D["到被驱动表查匹配行"]
    D --> E["拼接结果"]
    E --> F{"还有驱动表行吗"}
    F -- "有" --> B
    F -- "没有" --> G["返回结果"]

如果被驱动表连接字段没有索引，就可能出现驱动表每一行都去被驱动表全表扫描，代价非常高。

驱动表和被驱动表是什么

很多人学 Join 会卡在“左表、右表、驱动表、被驱动表”这几个词上。

概念	含义	关键点
左表	SQL 里 `join` 左边写的表	语法位置
右表	SQL 里 `join` 右边写的表	语法位置
驱动表	执行时先读取的表	优化器可能调整
被驱动表	拿驱动表的行去匹配的表	Join 字段最好有索引

对于 inner join，优化器通常可以调整 Join 顺序，不一定按 SQL 书写顺序执行。对于 left join，为了保留左表语义，优化器调整空间会更小。

为什么要选小结果集做驱动表？因为驱动表每输出一行，后面都可能要去被驱动表匹配一次。驱动表结果越小，后续匹配次数越少。

mermaid

flowchart TD
    A["过滤驱动表"] --> B{"驱动表结果多不多"}
    B -- "少" --> C["匹配次数少"]
    B -- "多" --> D["匹配次数多"]
    C --> E["被驱动表索引查找压力小"]
    D --> F["被驱动表重复查找压力大"]

Nested Loop Join 全过程

Nested Loop Join 可以理解为双层循环。外层循环驱动表，内层到被驱动表找匹配行。

伪代码：

java

for (Order order : filteredOrders) {
    User user = findUserById(order.getUserId());
    if (user != null) {
        result.add(join(order, user));
    }
}

如果 users.id 是主键索引，每次 findUserById 都是一次高效索引查找：

sql

select o.order_no, u.username
from orders o
join users u on o.user_id = u.id
where o.created_at >= '2026-01-01'
  and o.created_at < '2026-02-01';

推荐索引：

sql

create index idx_orders_created_user on orders(created_at, user_id);
-- users.id 是主键索引

执行过程：

mermaid

flowchart TD
    A["orders 按时间范围扫描"] --> B["得到一条订单"]
    B --> C["取 user_id"]
    C --> D["用 users 主键索引查用户"]
    D --> E["拼接订单和用户"]
    E --> F{"orders 还有下一条吗"}
    F -- "有" --> B
    F -- "没有" --> G["返回结果"]

这个流程能快起来，有两个前提：

驱动表过滤后行数不能太大。
被驱动表的 Join 字段要能走索引。

如果 orders 时间范围覆盖半年、命中 500 万行，即使 users.id 有索引，也会做 500 万次匹配。索引查找单次快，不代表重复几百万次仍然快。

被驱动表没有索引会怎样

假设被驱动表没有 users.id 索引，执行过程会变成：

mermaid

flowchart TD
    A["驱动表输出 1 行"] --> B["到被驱动表全表扫描"]
    B --> C["找到匹配行"]
    C --> D{"驱动表还有下一行"}
    D -- "有" --> A
    D -- "没有" --> E["返回结果"]

如果驱动表 1 万行，被驱动表 100 万行，最坏可能接近 100 亿次比较。真实优化器会做一些缓冲和算法优化，但根因仍然是：被驱动表不能通过索引快速定位。

所以商业项目里有一个硬规则：

Join 条件中的被驱动表字段，通常必须有索引。否则数据量一上来，Join 会从“关联查询”退化成“大量重复扫描”。

Block Nested Loop 和 Join Buffer

当被驱动表没有合适索引时，MySQL 可能使用 Block Nested Loop 思路，利用 Join Buffer 把一批驱动表行缓存起来，再扫描被驱动表进行匹配。

它的目标是减少被驱动表被扫描的次数，但不能从根上消除全表扫描。

mermaid

flowchart TD
    A["读取一批驱动表行"] --> B["放入 Join Buffer"]
    B --> C["扫描被驱动表"]
    C --> D["与 Buffer 中多行比较"]
    D --> E["输出匹配结果"]
    E --> F{"驱动表还有下一批吗"}
    F -- "有" --> A
    F -- "没有" --> G["结束"]

为什么 Join Buffer 不能当成优化手段依赖？

问题	影响
仍然要扫被驱动表	数据量越大越慢
占用内存	并发高时可能带来内存压力
无法替代索引定位	只是批量比较，不是直接找到目标行
计划不稳定	数据量和统计信息变化后可能切换计划

面试里可以这样说：Join Buffer 是 MySQL 在缺少高效索引路径时的一种缓冲优化，不是让慢 Join 变成好 Join 的根本方案。根本优化还是让过滤条件和 Join 条件命中合适索引。

BKA 和 MRR 是什么

BKA 是 Batched Key Access，MRR 是 Multi-Range Read。零基础可以先这样理解：

名称	解决什么问题	思路
BKA	Nested Loop 一行一行查被驱动表，随机访问多	先收集一批 Join key，再批量访问
MRR	按二级索引找到主键后回表随机 IO 多	把主键或页访问尽量排序后批量读

例如订单表查到一批 user_id 后，不是每条订单立刻去用户表查一次，而是收集一批 key，再批量访问用户表索引。这样可以减少随机访问和函数调用开销。

mermaid

flowchart TD
    A["驱动表扫描一批行"] --> B["收集一批 Join key"]
    B --> C["按 key 批量访问被驱动表索引"]
    C --> D["取回匹配行"]
    D --> E["和驱动表行组装结果"]

这类优化说明一个事实：MySQL 执行器会尽量把重复小访问变成批量访问。但如果 SQL 本身驱动表结果巨大、被驱动表索引缺失、返回列过多，算法优化也救不了设计问题。

Hash Join 要不要了解

MySQL 8.0 引入过 Hash Join 能力，适合某些等值连接场景。Hash Join 的基本思路是：把较小输入构造成 Hash 表，再扫描另一边做匹配。

mermaid

flowchart TD
    A["较小结果集"] --> B["按 Join key 构建 Hash 表"]
    C["另一张表结果集"] --> D["逐行计算 Join key"]
    D --> E["到 Hash 表查匹配"]
    E --> F["输出 Join 结果"]

它和 Nested Loop 的差异：

对比	Nested Loop	Hash Join
核心动作	外层每行查内层	小表建 Hash，大表探测
更依赖	被驱动表索引	内存和 Hash 构建成本
适合	索引可高效定位	等值连接、大量扫描型连接
风险	重复索引查找多	Hash 表内存、溢写和版本差异

学习 Hash Join 不是为了所有 Join 都手动改成 Hash，而是为了明白：数据库优化器会根据统计信息、索引、数据量和成本模型选择不同算法。生产排查仍然要回到执行计划和真实耗时。

Join 顺序为什么会变

对于 inner join，下面两条 SQL 语义接近：

sql

select *
from orders o
join users u on o.user_id = u.id
where u.status = 1;

sql

select *
from users u
join orders o on o.user_id = u.id
where u.status = 1;

优化器可能会选择先从 users 过滤出 status = 1 的用户，再到 orders 查订单；也可能先从 orders 出发。它会估算哪个路径成本更低。

影响 Join 顺序的因素：

因素	为什么影响
过滤条件选择性	先过滤掉大部分数据更划算
索引是否可用	能不能快速定位被驱动表
表行数统计	优化器需要估算扫描规模
Join 类型	`left join` 受保留左表语义限制
返回字段	是否需要回表、排序、临时表

如果统计信息不准，优化器可能把大结果集当成小结果集，选错驱动表。此时可以先用 analyze table 更新统计信息，再看 EXPLAIN 是否变化。

sql

analyze table orders;
analyze table users;

Join 和事务锁有什么关系

普通一致性读的 select join 通常不会像 update 那样加排他锁，但下面几类 SQL 会让 Join 和锁问题纠缠在一起：

sql

select o.*
from orders o
join order_item i on o.id = i.order_id
where o.status = 0
for update;

或者：

sql

update orders o
join payment p on o.order_no = p.order_no
set o.status = 1
where p.pay_status = 1;

这时要注意：

加锁范围和实际访问路径有关，不只是 where 条件文字。
如果 Join 条件没索引，可能扫描和锁住更多记录。
多表更新要统一表访问顺序，否则容易死锁。
大事务 Join 更新会产生大量 undo、redo，并扩大阻塞时间。

mermaid

flowchart TD
    A["Join 更新"] --> B["优化器选择访问路径"]
    B --> C["按路径扫描记录"]
    C --> D["对需要修改的记录加锁"]
    D --> E{"扫描范围是否过大"}
    E -- "是" --> F["锁等待、死锁、undo/redo 压力上升"]
    E -- "否" --> G["较小范围内完成更新"]

商业项目里，多表更新要特别谨慎。能拆成“先查出主键列表，再按主键分批更新”的场景，通常更容易控制锁范围和事务时间。

Join 索引怎么建

示例：

sql

select o.order_no, o.amount_cent, u.username
from orders o
join users u on o.user_id = u.id
where o.created_at >= '2026-01-01'
  and o.created_at < '2026-02-01';

索引建议：

sql

create index idx_created_user on orders(created_at, user_id);
-- users.id 是主键，天然有索引

原则：

过滤条件所在表要有合适索引。
被连接字段要有索引。
尽量让小结果集作为驱动表。
不要先 Join 出巨大中间结果再过滤。

Left Join 的坑

sql

select u.id, u.username, o.order_no
from users u
left join orders o on u.id = o.user_id
where o.status = 1;

这条 SQL 表面是 left join，但 where o.status = 1 会过滤掉右表为空的行，效果接近 inner join。

如果要保留没有订单的用户，应写成：

sql

select u.id, u.username, o.order_no
from users u
left join orders o
  on u.id = o.user_id
 and o.status = 1;

区别在于：右表条件放在 on 中，表示“匹配右表时过滤”；放在 where 中，表示“Join 完之后过滤最终结果”。

子查询和 Join

子查询：

sql

select *
from orders
where user_id in (
  select id
  from users
  where status = 1
);

Join：

sql

select o.*
from orders o
join users u on o.user_id = u.id
where u.status = 1;

哪个更好不能死背，要看优化器改写和执行计划。商业项目中重点是：

用 EXPLAIN 看是否扫描过多。
确认 Join 字段和过滤字段有索引。
确认返回行数是否可控。

N+1 查询问题

错误写法：

text

先查 20 个订单。
再循环 20 次，每次查一次用户。

流程：

mermaid

flowchart TD
    A["查订单列表 1 次"] --> B["循环订单"]
    B --> C["每个订单查用户 1 次"]
    C --> D["总共 1 + N 次 SQL"]

更推荐：

sql

select o.order_no, o.amount_cent, u.username
from orders o
join users u on o.user_id = u.id
where o.user_id in (1001, 1002, 1003);

或应用层批量查：

sql

select *
from users
where id in (1001, 1002, 1003);

商业场景：订单列表聚合用户信息

后台订单列表经常要展示订单、用户、支付状态。不要一次 Join 十几张表，容易变成慢 SQL。

更稳的方案：

主列表先查订单主表，确保分页稳定。
根据订单里的 user_id、pay_no 批量查用户和支付信息。
应用层组装结果。
对特别高频字段做冗余，比如订单表保存 user_name。

这样做不是因为 Join 不好，而是为了控制主查询的扫描范围和分页稳定性。

面试标准回答

多个一对多同时关联、预聚合、半连接/反连接、时态关联、区间重叠、自关联、全外连接模拟和父子分页的完整案例，继续阅读复杂关联查询实战。

text

Join 的本质是根据关联字段把多张表的行组合起来。MySQL 执行 Join 时可以理解为先确定驱动表，再拿驱动表的行去被驱动表匹配，所以 Join 字段和过滤字段是否有索引非常关键。inner join 只返回两边都匹配的行，left join 会保留左表行。left join 的右表条件如果写在 where 中，可能把右表为空的行过滤掉，导致效果接近 inner join。Join 优化不能只靠语法，要结合 EXPLAIN 看驱动表、扫描行数、索引和 Extra。商业项目中还要避免 N+1 查询和一次 Join 过多大表。

关联学习：EXPLAIN、索引、大表覆盖索引仍然慢。

JDK 8/11/17/21 LTS演进

线程池

查询与 Join ​

Join 是什么 ​

Join 类型 ​

Demo 表 ​

Join 执行原理 ​

驱动表和被驱动表是什么 ​

Nested Loop Join 全过程 ​

被驱动表没有索引会怎样 ​

Block Nested Loop 和 Join Buffer ​

BKA 和 MRR 是什么 ​

Hash Join 要不要了解 ​

Join 顺序为什么会变 ​

Join 和事务锁有什么关系 ​

Join 索引怎么建 ​

Left Join 的坑 ​

子查询和 Join ​

N+1 查询问题 ​

商业场景：订单列表聚合用户信息 ​

面试标准回答 ​

查询与 Join

Join 是什么

Join 类型

Demo 表

Join 执行原理

驱动表和被驱动表是什么

Nested Loop Join 全过程

被驱动表没有索引会怎样

Block Nested Loop 和 Join Buffer

BKA 和 MRR 是什么

Hash Join 要不要了解

Join 顺序为什么会变

Join 和事务锁有什么关系

Join 索引怎么建

Left Join 的坑

子查询和 Join

N+1 查询问题

商业场景：订单列表聚合用户信息

面试标准回答