mysql的连表查询的底层算法原理

随风飘的云
87 阅读10分钟

MySQL 连表查询的底层核心是 通过特定算法将多个表的记录组合成符合查询条件的结果集，其性能直接依赖于表的大小、索引设计、连接条件等因素。MySQL 底层主要实现了三种经典的连接算法，分别是 嵌套循环连接（Nested Loop Join） 、哈希连接（Hash Join） 、排序合并连接（Sort-Merge Join） ，且不同版本对算法的支持不同（例如 Hash Join 从 MySQL 8.0.18 开始正式支持）。

核心前提：连接的本质

连表查询（JOIN）的本质是 笛卡尔积过滤：

  1. 首先对参与连接的表计算 “笛卡尔积”（所有表的记录两两组合，结果集大小 = 表 1 行数 × 表 2 行数 × ... × 表 N 行数）；
  2. 再通过 ON 条件（连接条件）、WHERE 条件过滤笛卡尔积中的无效记录，最终得到目标结果。

但直接计算笛卡尔积会产生海量无效数据（例如两个 10 万行的表，笛卡尔积是 1e9 行），因此所有底层连接算法的核心目标都是 避免全量计算笛卡尔积，通过 “有序遍历”“哈希匹配” 等方式快速筛选有效记录。

一、嵌套循环连接（Nested Loop Join，NLJ）

1. 算法原理

最基础、最常用的连接算法，核心是  “外层循环遍历驱动表，内层循环匹配被驱动表” ，类似程序中的嵌套循环（for 循环套 for 循环）。

核心步骤：

  1. 选择驱动表和被驱动表：

    • 驱动表（Outer Table）：外层循环遍历的表，优先选择 数据量小（小表）或 有索引 的表（减少外层循环次数）；
    • 被驱动表（Inner Table）：内层循环遍历的表，需通过连接条件快速匹配驱动表的记录（依赖索引优化）。

  2. 外层循环：遍历驱动表的每一条记录（记为 r）；

  3. 内层循环：对于驱动表的每条记录 r，根据连接条件（如 r.id = s.user_id），在被驱动表中查找匹配的记录（记为 s）；

  4. 结果合并：将匹配成功的 (r, s) 组合加入结果集。

示意图：

for each 记录 r in 驱动表:
    for each 记录 s in 被驱动表 where s.连接列 = r.连接列:
        输出 (r, s)

2. 关键优化：索引嵌套循环（Index Nested Loop，INLJ）

如果 被驱动表的连接列上有索引，内层循环无需全表扫描，而是通过索引快速查找匹配记录（类似 WHERE 条件 + 索引查询），这是 NLJ 的最优形态，也是 MySQL 中最常用的连接方式。

性能分析：

  • 时间复杂度：O(M × log N)（M = 驱动表行数，N = 被驱动表行数，log N 是索引查找的时间）；
  • 核心优势：无需扫描被驱动表全表，仅通过索引匹配，效率极高；
  • 适用场景：中小表连接、被驱动表连接列有索引。

3. 退化情况：朴素嵌套循环（Naive Nested Loop）

如果 被驱动表的连接列无索引，内层循环只能全表扫描，时间复杂度为 O(M × N)（例如 M=1 万、N=1 万时，需执行 1 亿次循环），性能极差，MySQL 会尽量避免这种情况（若无法避免，会触发 “块嵌套循环” 优化）。

4. 优化变种：块嵌套循环（Block Nested Loop，BNL）

为解决 “朴素嵌套循环” 的低效问题，MySQL 引入了 BNL 优化（默认开启，通过 join_buffer_size 控制）：

  • 核心思路：批量读取驱动表的记录到内存缓冲区（join buffer），再批量与被驱动表匹配，减少被驱动表的全表扫描次数。

  • 步骤：

    1. 外层循环读取驱动表的一批记录（大小由 join_buffer_size 决定），存入 join buffer；
    2. 内层循环扫描一次被驱动表，将被驱动表的每条记录与 join buffer 中的所有驱动表记录批量匹配；
    3. 重复上述步骤，直到驱动表所有记录处理完毕。

  • 性能提升：将被驱动表的扫描次数从 M 次（朴素 NLJ）减少到 ceil(M / 缓冲区记录数) 次（例如 M=1 万，缓冲区可存 1000 条，则仅需扫描 10 次被驱动表）。

二、哈希连接（Hash Join，HJ）

1. 算法原理

MySQL 8.0.18 及以上版本支持的连接算法，核心是  “用哈希表快速匹配连接条件” ，适用于 无索引的大表连接（NLJ 此时会退化为全表扫描，效率极低，而 HJ 更高效）。

核心步骤（以两表连接为例）：

  1. 选择构建表（Build Table）和探测表（Probe Table） ：

    • 构建表：优先选择 数据量小 的表（小表），将其所有记录按 “连接列” 构建哈希表（内存中）；

      • 哈希表的 Key = 连接列的值（如 user_id），Value = 构建表的整行记录；
      • 若构建表过大，内存存不下，会拆分到磁盘（分区哈希表），但性能会下降。

  2. 探测匹配：

    • 遍历探测表（大表）的每一条记录，提取其连接列的值，计算哈希值；
    • 用该哈希值在哈希表中快速查找匹配的 Key，找到后将探测表记录与哈希表中的 Value（构建表记录）组合，加入结果集。

示意图：

# 第一步：构建哈希表（小表）
hash_table = {}
for each 记录 r in 构建表:
    key = hash(r.连接列)
    hash_table[key].append(r)

# 第二步：探测匹配（大表）
for each 记录 s in 探测表:
    key = hash(s.连接列)
    if key in hash_table:
        for each 记录 r in hash_table[key]:
            if r.连接列 == s.连接列:  # 哈希碰撞校验
                输出 (r, s)

2. 关键细节

  • 哈希碰撞处理：不同连接列的值可能计算出相同的哈希值（哈希碰撞），因此找到 Key 后需再次校验原始连接列的值是否相等；
  • 内存限制：若构建表过大，MySQL 会使用 “分段哈希连接”（Partitioned Hash Join）：将构建表和探测表按连接列的哈希值拆分成多个分区，逐个分区加载到内存处理，避免内存溢出；
  • 适用场景：大表连接、连接列无索引（NLJ 效率低）、等值连接（= 条件，哈希表仅支持等值匹配）。

3. 性能分析

  • 时间复杂度：O(M + N)（M = 构建表行数，N = 探测表行数），构建哈希表耗时 O(M)，探测匹配耗时 O(N)，效率远高于朴素 NLJ（O(M×N)）；
  • 优势：无需索引，适合无索引大表的等值连接；
  • 劣势：仅支持等值连接（不支持 <、> 等范围连接），依赖内存（大表分区会影响性能）。

三、排序合并连接（Sort-Merge Join，SMJ）

1. 算法原理

适用于 非等值连接（如 <、>、BETWEEN）或 无索引但可排序的场景，核心是  “先排序，后合并” ，避免嵌套循环的全表扫描。

核心步骤：

  1. 排序阶段：

    • 对两个参与连接的表，分别按 “连接列” 进行排序（若表已按连接列排序，可跳过此步骤）；
    • 排序后的表满足 “连接列有序” 的特性。

  2. 合并阶段：

    • 用两个指针（i 指向表 A，j 指向表 B）同时遍历两个排序后的表；

    • 根据连接条件（如 A.id < B.user_id）移动指针，匹配符合条件的记录：

      • 若 A[i].id < B[j].user_id：匹配成功，将 (A[i], B[j]) 加入结果集，移动 i 指针（继续找 A 的下一条记录）；
      • 若 A[i].id >= B[j].user_id：移动 j 指针（找 B 的下一条更大的记录）；

    • 直到其中一个表遍历完毕。

示意图：

# 第一步：排序（假设已排序）
A_sorted = 表A按连接列排序
B_sorted = 表B按连接列排序

# 第二步：合并遍历
i = 0, j = 0
while i < len(A_sorted) and j < len(B_sorted):
    a = A_sorted[i]
    b = B_sorted[j]
    if a.连接列 满足 连接条件 (如 a.id < b.user_id):
        输出 (a, b)
        i += 1
    else:
        j += 1

2. 关键细节

  • 排序开销：排序阶段的时间复杂度是 O(M log M + N log N)（M、N 为两表行数），若表已按连接列排序（如连接列是主键、有排序索引），可省略排序步骤，性能大幅提升；
  • 适用场景：非等值连接（哈希连接不支持）、两表数据量大但可排序、连接列有排序索引；
  • MySQL 支持情况：MySQL 底层支持 SMJ，但实际优化器更倾向于 NLJ（有索引时）或 HJ（无索引等值连接时），仅在非等值连接且无更好选择时才会使用 SMJ。

3. 性能分析

  • 时间复杂度：O(M log M + N log N + M + N)（排序 + 合并）；
  • 优势：支持非等值连接，避免嵌套循环的全表扫描；
  • 劣势：排序开销较大，若表未排序，性能可能不如 HJ（等值连接场景）。

四、MySQL 优化器的算法选择逻辑

MySQL 的查询优化器（Optimizer）会根据 表的大小、索引情况、连接条件类型 自动选择最优的连接算法，核心逻辑如下：

  1. 优先选择 INLJ（索引嵌套循环） ：

    • 条件：被驱动表的连接列有索引，且驱动表是小表；
    • 原因：效率最高（O(M log N)），无需排序或哈希表开销。

  2. 其次选择 HJ（哈希连接） ：

    • 条件：等值连接（=）、被驱动表连接列无索引、两表数据量较大（大表连接）；
    • 原因：避免 NLJ 的全表扫描，效率高于朴素 NLJ 和 SMJ（等值连接场景）。

  3. 最后选择 SMJ 或 BNL：

    • SMJ：非等值连接（<、> 等），无索引或索引无效；
    • BNL：无索引的小表连接（join_buffer_size 足够时），避免朴素 NLJ 的低效。

示例：优化器选择逻辑

sql

-- 场景1：t1是小表（100行），t2的user_id有索引（连接列）
SELECT * FROM t1 JOIN t2 ON t1.id = t2.user_id;
-- 优化器选择：INLJ（驱动表t1，被驱动表t2通过索引匹配）

-- 场景2：t1和t2都是大表（100万行），t2的user_id无索引，等值连接
SELECT * FROM t1 JOIN t2 ON t1.id = t2.user_id;
-- 优化器选择：HJ（t1作为构建表，t2作为探测表，哈希匹配）

-- 场景3：非等值连接，无索引
SELECT * FROM t1 JOIN t2 ON t1.id < t2.user_id;
-- 优化器选择：SMJ（先排序，后合并）

五、底层优化建议（基于算法原理）

  1. 优先给连接列建索引：触发 INLJ（最优算法），避免全表扫描；
  2. 小表作为驱动表：减少外层循环次数（INLJ）或降低哈希表构建开销（HJ）；
  3. 控制 join_buffer_size：BNL 场景下，适当增大缓冲区（默认 256K），减少被驱动表扫描次数；
  4. 等值连接优先：哈希连接（HJ）仅支持等值连接，非等值连接需依赖 SMJ（排序开销大）；
  5. 避免大表无索引连接：无索引的大表连接会触发 BNL 或 HJ（分区哈希），性能较差，尽量给连接列建索引。

总结

MySQL 连表查询的底层算法围绕 “减少无效数据扫描” 展开：

  • NLJ：依赖索引，适合中小表、有索引场景，是最优选择；
  • HJ：依赖哈希表，适合大表、无索引、等值连接场景；
  • SMJ：依赖排序，适合非等值连接场景。

理解底层算法的核心是：索引决定是否能触发高效的 INLJ，表大小决定 HJ/SMJ 的选择，连接条件类型决定是否支持 HJ。优化连表查询的本质，就是让优化器能选择最优的连接算法（通常是 INLJ 或 HJ）

avatar
文章
阅读
粉丝