MFIKC
0%

MySQL 多表连接查询核心逻辑笔记

发表于

MySQL 多表连接查询核心逻辑笔记

一、背景表结构与数据

1. 表结构创建

1
2
3
4
5
6
7
8
-- 双字段都有索引
create table a(a1 int primary key, a2 int ,index(a2));  
-- 三字段，双字段有索引
create table c(c1 int primary key, c2 int ,index(c2), c3 int);  
-- 有主键索引
create table b(b1 int primary key, b2 int);  
-- 无索引
create table d(d1 int, d2 int);

2. 插入数据

1
2
3
4
insert into a values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
insert into b values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);
insert into c values(1,1,1),(2,4,4),(3,6,6),(4,5,5),(5,3,3),(6,3,3),(7,2,2),(8,8,8),(9,5,5),(10,3,3);
insert into d values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10);

二、驱动表的选择

1. 驱动表概念

多表关联查询时，第一个被处理的表，决定连接顺序和查询性能。

2. 选择原则

  • 核心：在不影响最终结果集的前提下，优先选择结果集最小的表作为驱动表。
  • 限制：外连接（如 left join/right join）中，驱动表选择可能影响结果，需谨慎。

3. 结果集计算方式

1
预估结果集 = 每行查询字节数 × 预估的行数
  • 每行查询字节数：select 字段的字节大小总和。
  • 预估行数规则：
    • 无 where 筛选条件：默认全表。
    • 有 where 条件但无法用索引：默认全表。
    • 有 where 条件且可用索引：根据索引预估行数。

4. 案例分析

案例 1

1
select a.*,c.c2 from a join c on a.a2=c.c2 where a.a1>5 and c.c1>5;
  • explain 显示驱动表为 c（根据结果集大小选择）。

案例 2

1
select a.*,c.* from a join c on a.a2=c.c2 where a.a1>5 and c.c1>5;
  • 驱动表仍为 c：虽 c.* 数据量更大，但避免了 a 作为驱动表时的回表操作，性能更优。
  • 结论：结果集是主要因素，但非唯一（需结合回表等操作综合判断）。

三、两表关联的内在逻辑

MySQL 使用Nested-Loop join（嵌套循环连接），根据是否使用索引分为不同算法。

1. 有索引关联（被驱动表可用索引）

（1）Index Nested-Loop join

  • 流程：驱动表逐条取记录，通过索引关联被驱动表，需回表时用主键查询完整数据，结果放入 net buffer（由net_buffer_length控制，默认 1M）。
  • 特点：无需 join buffer，逐条处理，可能有多次随机回表查询。

（2）Batched Key Access (BKA) join

  • 优化点：将随机回表转为顺序查询（依赖 MRR）。

  • 流程：

    1. 驱动表符合条件的记录批量放入 join buffer。
    2. 关联被驱动表索引，主键存入 read_rnd_buffer。
    3. 按主键排序 read_rnd_buffer，顺序回表查询。

  • 启用方式：

    1
    set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

2. 无索引关联（被驱动表无可用索引）

（1）Simple Nested-Loop join

  • 流程：驱动表逐条取记录，全表扫描被驱动表匹配，性能差（驱动表每行对应一次全表扫描）。

（2）Block Nested-Loop (BNL) join

  • 优化点：批量处理驱动表数据，减少被驱动表扫描次数。
  • 流程：
    1. 驱动表数据批量放入 join buffer（上限为 buffer 大小）。
    2. 全表扫描被驱动表，与 buffer 中所有行匹配，匹配成功则放入结果集。
  • 特点：无 read_rnd_buffer，依赖 join buffer 减少扫描次数。

四、多表连接的执行流程

1. 核心逻辑

采用嵌套循环而非 “先两表连接再关联第三表”，形式类似：

1
2
3
4
5
6
7
for row1 in table1 filtered by where{
  for row2 in table2 associated by table1.index1 filtered by where{
    for row3 in table3 associated by table2.index2 filtered by where{
      put into net-buffer then send to client;
    }
  }
}

2. 不同算法的处理方式

  • Index Nested-Loop：逐条处理驱动表记录，依次关联后续表，需回表时单独查询。
  • BKA/BNL：批量处理驱动表数据（利用 join buffer），每个 join 关键字对应一个 buffer，分批次关联后续表。

总结

  1. 驱动表选择：优先结果集小的表，结合回表等操作综合判断。
  2. 两表关联：有索引用 Index Nested-Loop 或 BKA（优化回表），无索引用 BNL（减少扫描次数）。
  3. 多表连接：嵌套循环执行，按连接顺序依次关联，批量算法利用 buffer 提升效率。