周末的相亲大会现场，红队来了 500 位嘉宾，蓝队来了 800 位。红娘的任务只有一个：把两队的嘉宾按条件配成一对。你可以想象三种撮合方式。第一种，红娘拿着红队名单，从第 1 位开始，挨个问遍蓝队每个人——这是最笨但也最可靠的 Nested Loop。第二种，红娘在门口摆一张登记桌，每位蓝队嘉宾把“择偶关键词”登记进档案袋再进场，之后红队嘉宾来一个，翻一下档案袋就能找到所有匹配——这是 Hash Join。第三种，两个队先各自按关键词排好队，然后同时往前走，关键词相同的人自然碰头——这是 Merge Join。数据库处理 SQL JOIN，本质上就是红娘相亲：从两张表出发，按连接条件把行配对。而“执行计划”是红娘手里的战术板，“索引”则是那本通讯录。读完这篇文章，你就能看懂战术板、选对撮合术，让多表查询真正飞起来。

岗位关联

学会用执行计划诊断 JOIN 慢查询、在三种连接算法与索引策略之间做对选择，是后端开发与 DBA 在大数据量场景下的核心竞争力。

目录
  1. 可视化演示
  2. 认识JOIN：两个队伍的配对游戏
  3. 看懂执行计划：红娘手里的战术板
  4. Nested Loop：挨个问的笨功夫
  5. Hash Join：先建档案再速配
  6. Merge Join：两队排好队一起走
  7. 索引优化：给配对写一本通讯录
  8. 驱动表选择：谁先上场有讲究
  9. 大表JOIN优化：人太多了怎么办
  10. AI 应用场景
  11. Python 演示
  12. 常见误区
  13. 面试题

可视化演示

对比展示Nested Loop Join（逐行嵌套）、Hash Join（构建+探测）、Merge Join（排序合并）三种JOIN执行计划的原理与复杂度差异。

本节配有交互动效（进入视口后自动加载，约 3KB）。

'+captions.nl+'

认识JOIN：两个队伍的配对游戏

相亲大会开场，红队和蓝队各就各位。红娘心里清楚，自己的工作不是让“所有人认识所有人”，而是按照一定规则撮合。可是如果她偷懒，不设任何规则就让大家自由社交，那红队每人要见蓝队所有人——500 人见 800 人，就是 40 万次见面。这种“全体互相配对”在 SQL 里有个学名，叫笛卡尔积，也是 JOIN 最需要警惕的隐形炸弹。

在 SQL 的世界里，JOIN 就是把两张表中的行按“连接条件”配对：比如订单表的 user_id 等于用户表的 id，就像红娘要求“双方都养猫”才能配对。常见的连接类型有 INNER JOIN（只保留配对成功的）、LEFT JOIN（红队全员保留，蓝队能配上才出现）、RIGHT JOIN（反过来）和 CROSS JOIN（不做任何筛选的笛卡尔积）。连接条件写得越具体，被筛掉的无意义配对就越多，查询也就越快。

写 JOIN 之前，先问自己三个问题：要配谁（哪两张表）、按什么条件配（连接条件）、条件能筛掉多少对。条件筛得越狠，配对成本越低。如果一个 JOIN 忘了写 ON 条件，那基本就是在制造笛卡尔积事故——这就像红娘忘了核对关键词，让全场嘉宾陷入无差别社交，场面瞬间失控。

∣A×B∣=m×n\displaystyle |A \times B| = m \times n
这张公式说的是：不带连接条件的 JOIN（笛卡尔积），结果行数等于表 A 行数 m 乘以表 B 行数 n。10 万行 × 10 万行就是 100 亿行，任何数据库都扛不住。
  1. 符号 |A|表示表 A 的行数，就像红队一共有 500 位嘉宾，这个 m 就是行数。
    ∣A∣=m\displaystyle |A| = m
  2. A × B 笛卡尔积把表 A 的每一行都和表 B 的每一行做一次组合，形成“全员见面”的配对结果。
    A×B\displaystyle A \times B
  3. 结果行数相乘总配对数是 m × n，而不是 m + n，因为每一行都要和对方全部行相遇。
    ∣A×B∣=m×n\displaystyle |A \times B| = m \times n

看懂执行计划：红娘手里的战术板

经验丰富的红娘在开干之前，会先在战术板上画出撮合方案：是挨个问，还是摆登记桌，还是让两队排好队同步走。数据库也有一位“红娘”——优化器，它会在真正执行 SQL 之前，生成一份详细的“执行计划”，把“打算怎么配对”全部写在上面。看懂这份战术板，你就知道数据库准备用什么算法、预计扫多少行。

查看战术板的命令是 EXPLAIN SELECT ...。执行计划里有几个关键指标：type 表示扫描方式，从好到差大致是 const → ref → range → index → ALL，其中 ALL 是灾难性的全表扫描；rows 是优化器估算的扫描行数；Extra 里出现 Using temporary 或 Using filesort 则是危险信号，说明要建临时表或额外排序。慢查询优化 80% 的工作，就是先用 EXPLAIN 找到 type=ALL 的那张表。

看战术板要像红娘一样冷静：先看 type 是 ALL 还是 ref，再看 rows 的量级，最后瞄一眼 Extra 有没有隐藏开销。很多 DBA 接到慢查询告警后第一件事不是改 SQL，而是先跑一遍 EXPLAIN——战术板永远比猜更可靠。

C=CIO+CCPU+CMEM\displaystyle C = C_{\text{IO}} + C_{\text{CPU}} + C_{\text{MEM}}
优化器眼里，执行代价 = 磁盘读取成本 + CPU 计算成本 + 内存占用成本。它永远在候选计划里挑总代价最低的，而我们优化的目标就是帮它把某项成本降下来。
  1. 总代价 C一条查询的整体耗时，是优化器选择执行计划的最终依据。
    C\displaystyle C
  2. 磁盘读取 C_IO全表扫描要读多少页数据，是慢查询的大头成本，对应红娘挨个跑腿的时间。
    CIO\displaystyle C_{\text{IO}}
  3. CPU 计算 C_CPU在内存中比较连接条件的计算量，行数越多，计算越贵。
    CCPU\displaystyle C_{\text{CPU}}
  4. 内存成本 C_MEM排序、建临时表、哈希表占用的内存，内存不够就会落盘，雪上加霜。
    CMEM\displaystyle C_{\text{MEM}}

Nested Loop：挨个问的笨功夫

最朴素的撮合方式：红娘拿着红队名单，从第 1 位开始，挨个去问蓝队的每一个人“你认识他吗”。蓝队问完了，再轮到红队第 2 位，再次把蓝队所有人问一遍。这种“一人问全队”的做法，在数据库里就是 Nested Loop Join（嵌套循环连接）。外层的表叫驱动表，内层的表叫被驱动表，两层循环嵌套在一起。

Nested Loop 的致命弱点在于行数相乘：红队 500 人、蓝队 800 人，就要问 40 万次。如果蓝队人手一本“嘉宾通讯录”（索引），红娘就不用挨个见面，而是直接翻通讯录定位——这就像给被驱动表的连接列加上了索引，每次内层查找从“全表扫”变成“按索引找”，把内层的线性扫描变成了对数级定位。

挨个问不是不行，关键看场面大小。人少的时候，挨个问最灵活、实现最简单；一旦双方都是大队伍，40 万次、400 亿次的问法就会瞬间击垮全场。所以 Nested Loop 的适用场景很明确：驱动表足够小，或者被驱动表连接列有索引。这也是“CBO 优化器”选择它时的主要理由。

Tbefore=m×n,Tafter≈m×log⁡2n\displaystyle T_{\text{before}} = m \times n, \qquad T_{\text{after}} \approx m \times \log_2 n
加索引前后对比：没索引时是 m×n 次完整比较；给被驱动表连接列加索引后，外层每行只需做一次约 log₂n 的索引查找，总次数接近 m×log₂n。
  1. m 是外层驱动表行数驱动表决定外层循环次数，行数越少越好，对应红队名单的长度。
    m=∣驱动表∣\displaystyle m = |\text{驱动表}|
  2. n 是内层被驱动表行数没索引时内层每次都要扫全部 n 行，这是比较次数爆炸的根源。
    n=∣被驱动表∣\displaystyle n = |\text{被驱动表}|
  3. 无索引 T_before外层 m 行每行扫描内层 n 行，总共 m×n 次比较。
    Tbefore=m×n\displaystyle T_{\text{before}} = m \times n
  4. 有索引 T_after内层借助索引做二分式查找，每次成本降到 log₂n，总量约等于 m×log₂n。
    Tafter≈m×log⁡2n\displaystyle T_{\text{after}} \approx m \times \log_2 n

Hash Join：先建档案再速配

大型相亲会场，红娘学聪明了：在门口摆一张登记桌，每位蓝队嘉宾进场前，按“择偶关键词”算出一个编号，把资料放进对应编号的档案袋。等红队嘉宾进场时，红娘只做一件事——算他的关键词编号，直接走到对应档案袋前翻出所有匹配。这就是 Hash Join（哈希连接）的思路：一次构建，多次速配。

Hash Join 分为两个阶段。构建阶段：优选出较小的表（或优化器指定的表），对连接列逐行计算哈希值，放进内存中的哈希表（档案袋阵列）。探测阶段：拿另一张大表的每一行，同样计算哈希值，直接到对应桶里找匹配行。整个过程不需要索引，但有一个硬性前提：连接条件必须是等值连接（=）。MySQL 8.0.18 之后，大表无索引等值 JOIN 会自动启用 Hash Join。

登记桌之所以快，是因为把“到处问”变成了“按编号直接翻档案”。但它的代价是：档案袋要占地方——哈希表需要内存。如果小表大到内存装不下，桶就会被写到磁盘上，性能立刻崩盘。所以 Hash Join 最适合“大表对等连接且无索引”的场景，这也是数仓和大数据引擎最常用的 JOIN 方式。

THash=O(m+n)\displaystyle T_{\text{Hash}} = O(m + n)
Hash Join 的复杂度是线性的：构建阶段处理小表 m 行，探测阶段处理大表 n 行，总共 O(m+n)，和 Nested Loop 的 O(m×n) 形成鲜明对比。这就是大表等值连接的首选方案。
  1. m 行构建哈希表把小表逐行算哈希放进桶里，每行只处理一次，就像每位蓝队嘉宾登记一次档案。
    构建: m 次哈希\displaystyle \text{构建: } m \text{ 次哈希}
  2. n 行探测匹配大表逐行算哈希去对应桶里捞匹配，每行也是 O(1) 命中。
    探测: n 次哈希\displaystyle \text{探测: } n \text{ 次哈希}
  3. 总复杂度 O(m+n)两项相加就是线性增长，行数翻倍耗时最多翻倍，而不是翻平方。
    THash=O(m+n)\displaystyle T_{\text{Hash}} = O(m + n)

Merge Join：两队排好队一起走

红娘又想到一个新办法：让红队和蓝队先分别按“择偶关键词”排好队，然后两队同时从队首出发。排在最前面的两个人关键词相同，直接配对成功，两人一起出列；如果不相同，关键词较小的那一队往前走一步，再比较。就像两条流水线同步推进，双方指针各走各的，碰上了就配对。这就是 Merge Join（归并连接）。

Merge Join 的高效前提是：两个连接列都已经有序。如果本来就有序（比如走索引返回），代价极低；如果无序，就得先各自排序，排序本身是 O(m log m) 和 O(n log n)。相比 Hash Join 只能做等值连接，Merge Join 对非等值条件（>、<、>=）也能优雅处理，因为有序数据天然支持范围匹配。

排队的智慧在于：把“反复询问”的平方级成本，转化成了“一次排序 + 线性合并”的近似线性成本。但要注意，排序本身不便宜——如果两张表都是无序大表，Merge Join 光排序就要吃掉大量时间。所以它最舒服的场景是：连接列恰好有索引（数据已经有序），或者我们需要非等值连接，再或者结果集本身要求有序输出。

TMerge=O(mlog⁡m+nlog⁡n+m+n)\displaystyle T_{\text{Merge}} = O(m\log m + n\log n + m + n)
Merge Join 的总代价 = 两表各自排序的开销 + 双指针合并的开销。如果省掉排序，合并阶段本身只有 O(m+n)。
  1. 排序阶段 O(m log m)表 A 若无序要先排序，队形整齐是要花时间的。
    mlog⁡m\displaystyle m\log m
  2. 排序阶段 O(n log n)表 B 同样排序，两队都要先排队。
    nlog⁡n\displaystyle n\log n
  3. 合并阶段 O(m+n)双指针各走一遍，每个元素最多被比较一次，像两队同时前进。
    m+n\displaystyle m + n
  4. 总复杂度排序成本加合并成本，整体取大 O。
    O(mlog⁡m+nlog⁡n+m+n)\displaystyle O(m\log m + n\log n + m + n)

索引优化：给配对写一本通讯录

红娘手里最好用的工具，是一本按姓氏拼音排好的通讯录。没有它，找人要挨家挨户敲门；有了它，翻几页就能定位到人。数据库里的索引就是这样的通讯录，底层通常是一棵 B+ 树，把连接列的值有序组织起来，查找复杂度只有对数级。

给 JOIN 加索引，最核心的一条是：索引要加在被驱动表（内层表）的连接列上。Nested Loop 中，外层驱动表本来就要被全部读一遍，加不加索引没区别；真正需要“快速找人”的是内层被驱动表。另外还有两个进阶技巧：一是覆盖索引，让索引包含查询需要的全部列，这样连回表都省了；二是警惕索引失效——对索引列套函数、隐式类型转换、LIKE 前缀通配符，都会让优化器放弃索引，通讯录瞬间变成废纸。

索引不是越多越好——每本通讯录都要有人去更新维护。表上每多一个索引，插入、更新、删除时就要多维护一棵树。正确的姿势是：先用 EXPLAIN 看清哪一步是全表扫描，再针对被驱动表的连接列精准建索引，而不是盲目地“雨露均沾”。索引优化是 JOIN 提速性价比最高的一步，但也是最容易用错的一步。

Tlookup=O(log⁡dN)\displaystyle T_{\text{lookup}} = O(\log_d N)
B+ 树索引的查找复杂度：N 是表的总行数，d 是每个节点的分支数（通常上百），log_d N 意味着千万行数据也只需 4 层左右就能定位到目标。
  1. N 行数据索引要组织的总行数，通讯录总共要收录多少人。
    N\displaystyle N
  2. d 叉树B+ 树每个节点有 d 个子节点，d 通常很大，所以树很矮。
    d\displaystyle d
  3. 查找层数 log_d N从根到叶需要下钻的层数，d 越大层数越少，查找越快。
    log⁡dN\displaystyle \log_d N

驱动表选择：谁先上场有讲究

相亲大会的时间有限，红娘必须决定：先让哪一队的人去询问对方？如果红队只有 50 人、蓝队有 5000 人，聪明的红娘一定让红队先上场——外层循环少跑几轮，全场节奏就快得多。在 Nested Loop 里，这个“先上场的表”叫驱动表（外层表），另一张叫被驱动表（内层表）。

驱动表选择的本质是控制外层循环次数。Nested Loop 的总代价约等于驱动表行数 × 内层单次查找代价，所以“小表驱动大表”成为黄金法则。需要注意的是，MySQL 的优化器是基于统计信息（行数、基数、选择性）来自动决策的；如果统计信息过期，它可能选错驱动表。此时可以人为干预：在 MySQL 里用 STRAIGHT_JOIN 强制指定表连接顺序，或者在 SQL 中调整表的书写顺序。

小表驱动大表，本质是减少“询问次数”。但这条法则只对 Nested Loop 有意义：Hash Join 中优化器会自动把较小的表放到构建侧，Merge Join 则根本不关心谁驱动谁。所以别把金句当教条——先看执行计划里优化器选了什么算法，再判断该不该手动干预顺序。

CNL=m+m×clookup\displaystyle C_{\text{NL}} = m + m \times c_{\text{lookup}}
Nested Loop 的代价模型：外层扫描 m 行（驱动表），每行触发一次内层查找，内层查找成本为 c_lookup。驱动表 m 越小，总代价越低——这就是“小表驱动大表”的数学依据。
  1. 外层扫描 m驱动表 m 行必须全部读一遍，这部分成本固定。
    m\displaystyle m
  2. 内层查找 c_lookup驱动表每行都要去被驱动表查一次，单次成本取决于有没有索引。
    clookup\displaystyle c_{\text{lookup}}
  3. 总代价 m × c_lookup外层行数乘以内层单次成本，m 越小、c_lookup 越小，整体越快。
    CNL=m+m×clookup\displaystyle C_{\text{NL}} = m + m \times c_{\text{lookup}}

大表JOIN优化：人太多了怎么办

当两方都来了几万人，红娘的登记桌、排队法全都扛不住了——一个会场根本装不下。这时红娘必须改变策略：把大会拆成几十场小会，每场只请一批人进来撮合，结束后把配对结果记到台账上，最后把所有台账汇总。这就是大表 JOIN 的“分批处理”和“物化”思路。

分批处理：按主键范围或时间字段把大表切成若干小片，每片单独 JOIN，结果写入临时表，全部批次完成后合并。物化技巧：把需要频繁 JOIN 的小表或中间结果先物化成一张带索引的临时表，后续 JOIN 直接复用，避免重复计算。还有一个反直觉的优化是“冗余字段”：把维度表的几个常用属性直接冗余到事实表里，用空间换时间，让查询干脆不用 JOIN。

一场撮合变成多场撮合，场场小而精，反而比一场巨型大会更高效。分批处理的精髓是“控制单次规模”，让 Hash Join 的内存桶装得下、让 Nested Loop 的内层索引撑得住；物化与冗余则是“减少重复劳动”。这两种思路在离线数仓和 AI 特征工程里极其常见，属于大表 JOIN 的终极武器。

K=⌈NB⌉\displaystyle K = \left\lceil \frac{N}{B} \right\rceil
分批次数公式：N 是总行数，B 是每批处理的行数，K 是需要的批次数。比如 2 亿行每批 500 万行，就需要 40 批。
  1. 总行数 N大表的总数据量，比如 2 亿行订单。
    N\displaystyle N
  2. 每批行数 B单批能高效处理的行数，取决于内存和单次 JOIN 耗时。
    B\displaystyle B
  3. 批次数 K总行数除以每批行数向上取整，得到需要跑多少批。
    K=⌈NB⌉\displaystyle K = \left\lceil \frac{N}{B} \right\rceil

AI 应用场景

电商订单中心慢查询排查

阶段：数据分析与性能诊断

解决的问题：传统方式是等监控告警后人工翻慢日志，定位问题全凭经验和运气。AI 时代借助执行计划分析工具，可以自动识别“全表扫描 + Nested Loop 爆炸”型的 JOIN 语句，直接推荐索引创建位置，把诊断从小时级压缩到分钟级。

具体案例：某电商大促期间，订单表 JOIN 用户表的接口 P99 超时达到 5 秒。DBA 执行 EXPLAIN 后发现被驱动表 type=ALL，rows 估算 800 万行，属于典型的内层全表扫描。在 user_id 上建立索引后，type 从 ALL 变为 ref，P99 降到 50ms。

离线特征工程大表拼接

阶段：特征工程

解决的问题：推荐系统离线任务需要把亿级用户行为表和用户画像表做 JOIN，直接一次性 JOIN 会导致内存溢出或哈希表落盘，任务跑不完。采用按用户 ID 范围分批 JOIN 并物化中间表的策略后，每批规模可控，整体耗时可降低 60% 以上。

具体案例：某推荐团队每天凌晨跑特征任务，行为表 2 亿行、画像表 5000 万行。按用户 ID 每 500 万行切一批，分批 JOIN 到临时表物化后再合并，任务完成时间从凌晨 4 点提前到 0 点 40 分。

数仓宽表建模减少 JOIN

阶段：数据建模

解决的问题：构建宽表时事实表需要 JOIN 多张维度表，JOIN 次数越多查询越慢。把高频维度字段（用户等级、商品类目、门店城市）直接冗余进事实表，把“多次 JOIN”变成“单表查询”，是典型的物化与反范式设计。

具体案例：某公司订单明细宽表原先要 JOIN 用户、商品、门店三张维度表，报表查询耗时 30 秒。将三个高频字段冗余进事实表后，查询降到 1 秒以内，存储成本仅增加约 8%。

Python 演示

python52 行
"""SQL JOIN 优化演示: Nested Loop vs Hash Join 对比模拟两张表做等值连接, 统计比较次数观察复杂度差异"""  def nested_loop_join(table_a, table_b, key_a, key_b):    """嵌套循环连接: 外层每行都遍历内层全部行"""    result = []    comparisons = 0    for row_a in table_a:        for row_b in table_b:            comparisons += 1            if row_a[key_a] == row_b[key_b]:                merged = dict(row_a)                merged.update(row_b)                result.append(merged)    return result, comparisons  def hash_join(table_a, table_b, key_a, key_b):    """哈希连接: 先构建哈希桶, 再逐行探测"""    result = []    comparisons = 0    bucket = {}    for row_b in table_b:        comparisons += 1        bucket.setdefault(row_b[key_b], []).append(row_b)    for row_a in table_a:        comparisons += 1        for row_b in bucket.get(row_a[key_a], []):            merged = dict(row_a)            merged.update(row_b)            result.append(merged)    return result, comparisons  def main():    # 表A 2000 行, 表B 3000 行, 通过 fan_id 等值连接    table_a = [{"id": i, "fan_id": i % 500, "name": "user_" + str(i)} for i in range(2000)]    table_b = [{"id": i, "fan_id": i % 800, "city": "city_" + str(i % 10)} for i in range(3000)]     nl_rows, nl_cost = nested_loop_join(table_a, table_b, "fan_id", "fan_id")    hj_rows, hj_cost = hash_join(table_a, table_b, "fan_id", "fan_id")     print("Nested Loop 比较次数: " + str(nl_cost) + ", 结果行数: " + str(len(nl_rows)))    print("Hash Join   比较次数: " + str(hj_cost) + ", 结果行数: " + str(len(hj_rows)))    print("提速倍数: " + str(round(nl_cost / hj_cost, 1)) + "x")  if __name__ == "__main__":    main()
目的：用 Python 模拟数据库的 Nested Loop 与 Hash Join 两种连接算法，通过统计“比较次数”直观感受 O(m×n) 与 O(m+n) 的差距。
  1. for row_a in table_a:外层循环遍历驱动表，每行都要去内层查找一次。
  2. for row_b in table_b:内层循环全表扫描，这是 Nested Loop 比较次数爆炸的根源。
  3. if row_a[key_a] == row_b[key_b]:等值连接条件，Hash Join 只支持这种 = 匹配。
  4. bucket.setdefault(row_b[key_b], []).append(row_b)构建哈希桶：把被驱动表按连接键归档，对应相亲登记桌。
  5. for row_b in bucket.get(row_a[key_a], []):探测阶段：直接按哈希键取桶，O(1) 命中匹配行。
  6. nl_rows, nl_cost = nested_loop_join(table_a, table_b, "fan_id", "fan_id")跑 Nested Loop，返回结果和比较次数。
  7. print("提速倍数: " + str(round(nl_cost / hj_cost, 1)) + "x")输出两种算法的耗时倍差，验证线性优势。

常见误区

误区网上都讲 JOIN 必须小表驱动大表，所以我写 SQL 时把所有 JOIN 都改成小表在前，可有些查询反而变慢了。

正解小表驱动只对 Nested Loop 有意义，它减少的是外层循环次数。Hash Join 中优化器会自动把更小的表放到构建侧，人工调顺序没意义；Merge Join 更关心两侧是否有序。真正的“一招鲜”是先用 EXPLAIN 看清优化器选了哪种算法，再对症下药，而不是盲目套口诀。

为何常见：教程大多拿 Nested Loop 举例，容易让人把局部结论当成全局真理，忽略了优化器会动态选择算法这一事实。

误区我为了让 JOIN 变快，把两表的连接列都建了索引，结果查询没快多少，写入反而变慢了。

正解Nested Loop 只需要被驱动表（内层表）的连接列有索引，驱动表本来就要被全表扫一遍，索引帮不上忙。就像相亲只需要给“被反复询问的那一队”发通讯录，给发问方也发一份纯属浪费。两边都建索引既占空间又拖慢插入更新。

为何常见：直觉上觉得“两边都加速=更快”，但没理解 Nested Loop 是单向查找：只有内层表需要按连接值快速定位。

误区EXPLAIN 里明明显示了 key 和 index，是不是代表查询已经很快了？为什么线上还是很慢？

正解key 只表示“用了哪个索引”，但 type=index 可能意味着“扫了整棵索引树”，比全表扫描好不了多少。要重点看 type 的等级：const/ref 是快速定位，range 是范围扫描，index 是全索引扫描，ALL 才是真正的全表扫描。还要结合 rows 估算和 Extra 里是否出现 Using filesort。

为何常见：新手只盯着有没有 key，不知道 type 有严格的等级排序，也没把 rows 和 Extra 纳入判断体系。

误区我只要把服务器内存加够大，所有大表 JOIN 都交给 Hash Join，是不是就一定能快了？

正解Hash Join 只支持等值连接，且哈希表大到内存装不下时会落盘，性能急剧下降。对有序数据，Merge Join 甚至更快；对小表加内层索引，Nested Loop 反而是最优。优化不是“把某个算法用到极致”，而是根据数据分布、连接类型选择匹配的算法，执行计划才是最终裁判。

为何常见：把算法的“适用场景”混淆成“绝对优劣”，忽略了优化器是在多个候选计划里挑代价最小的。

误区我把可能出现的连接列组合都建了联合索引，是不是 JOIN 就一定能走索引、一定快？

正解索引有维护成本，每次插入更新都要同步维护 B+ 树，索引越多写入越慢。而且函数包裹、隐式类型转换、LIKE 前缀通配都会让索引失效，建了也可能白建。正确做法是：先 EXPLAIN 看实际扫描方式，再针对最热的慢查询精确建索引，而非盲目堆砌。

为何常见：只看到索引“加速查询”的收益，忽略了写入成本与失效条件，把建索引当成了越多越保险。

面试题

Q1. 两张表不带任何连接条件做 JOIN，结果集行数为？

单选题 · easy

  • A.两表行数相加之和
  • B.两表行数相乘之积← 正确答案
  • C.两表行数相减之差
  • D.两表行数相除之商

答案：B。不带连接条件的 JOIN 就是笛卡尔积，每行都和对方所有行配对，所以结果是 m×n 相乘。相加是并集的思路，相减相除更是无关操作。

Q2. Nested Loop JOIN 最适合下列哪种场景？

单选题 · easy

  • A.两张大表做等值连接
  • B.驱动表小且内层有索引← 正确答案
  • C.两表都已排好顺序
  • D.连接条件是非等值连接

答案：B。Nested Loop 的性能取决于外层行数与内层查找代价。小表驱动加内层连接列有索引，比较次数可大幅下降。两张大表更适合 Hash Join，已排好序可考虑 Merge Join。

Q3. Hash Join 只适用于哪种连接条件？

单选题 · easy

  • A.等值连接条件← 正确答案
  • B.范围连接条件
  • C.模糊匹配条件
  • D.任意连接条件

答案：A。哈希表只能做“键完全相等”的精确命中，范围、模糊匹配无法直接定位到桶。就像登记桌只能按完整关键词归档，Hash Join 仅用于等值连接。

Q4. Merge Join 能高效工作的前提条件是？

单选题 · mid

  • A.两表行数必须相等
  • B.连接列均已排好序← 正确答案
  • C.驱动表必须足够小
  • D.连接列必须设主键

答案：B。Merge Join 靠双指针同步前进，只有两边都按连接列有序才能“碰头即配对”。行数相等、驱动表小、主键约束都不是必要条件。

Q5. 使用 Nested Loop 时，驱动表应该优先选择？

单选题 · mid

  • A.行数较大的表
  • B.行数较小的表← 正确答案
  • C.随机选择一张表
  • D.任意有索引的表

答案：B。驱动表是外层循环，外层每行都要去内层查找。外层行数越少，总比较次数越少，所以选小表驱动；索引应加在内层被驱动表上才能发挥作用。

Q6. EXPLAIN 结果中 type 为 ALL 表示？

单选题 · mid

  • A.使用了主键查找
  • B.发生了全表扫描← 正确答案
  • C.使用了唯一索引
  • D.使用了覆盖索引

答案：B。ALL 是全表扫描，最慢，需要逐行读完整张表。const/ref 走索引快速定位，range 走范围扫描，都比 ALL 快。看到 ALL 就说明连接列上没走索引。

Q7. 下列哪些做法能优化大表 JOIN 性能？

多选题 · hard

  • A.给连接列建立索引← 正确答案
  • B.把大表切分多批次← 正确答案
  • C.物化中间结果表← 正确答案
  • D.直接做笛卡尔积

答案：ABC。连接列加索引降低内层查找代价；分批降低单次内存压力；物化中间表让重复 JOIN 复用结果。笛卡尔积会让结果行数爆炸式放大，必须避免。

Q8. 下列哪些情况会导致索引失效？

多选题 · mid

  • A.索引列套用函数处理← 正确答案
  • B.隐式类型转换发生← 正确答案
  • C.前缀通配符模糊查询← 正确答案
  • D.唯一索引等值查找

答案：ABC。对索引列做函数运算、隐式类型转换、LIKE 前缀通配符都会让优化器放弃索引。等值查找恰恰是索引最喜欢的用法，不会失效。

Q9. 两张各 10 万行的表做等值 JOIN，且连接列都无索引，哪种算法综合最快？

单选题 · hard

  • A.Nested Loop 双重循环
  • B.Hash Join 哈希连接← 正确答案
  • C.Merge Join 归并连接
  • D.Cross Join 笛卡尔积

答案：B。无索引时 Nested Loop 要比较 100 亿次，基本不可行；Merge Join 需要先全表排序，额外开销大；Hash Join 构建加探测接近 O(m+n)，是大表等值连接的首选。

Q10. 关于驱动表选择与干预，下列说法正确的有？

多选题 · hard

  • A.NL 中宜选小表做驱动← 正确答案
  • B.Hash 用小表构建哈希← 正确答案
  • C.驱动表选择无需索引
  • D.统计信息影响优化器← 正确答案

答案：ABD。Nested Loop 中小表驱动能显著减少外层循环次数；Hash Join 优化器自动把较小表放构建侧；优化器依赖统计信息决策，统计过期会选错驱动表。驱动表选择当然要考虑索引分布，C 说法错误。