一、三种连接方式，各管一段

嵌套循环的代价是「驱动表行数 × 内层每次的成本」。 所以它怕的是「驱动表大」和「内层没索引」——十万行驱动、内层每次全表扫，那就是十万次全表扫。 哈希连接的代价是「两张表各扫一遍 + 建哈希表的内存」。 它怕的是内存不够—— 哈希表放不下就会落临时表空间，那就变成「哈希连接带落盘」，比嵌套循环还慢。 排序合并现在很少见了。 它只在「两边都已经排好序」时才有优势，比如两个索引扫描的结果直接对撞。

二、驱动表怎么选

规矩：选「过滤之后行数最少」的那张当驱动表。 注意是「过滤之后」，不是「原表最小」。 一张一亿行的表，加了条件只返回 10 行，它就该当驱动表。 这条规矩的由来是嵌套循环的代价公式：总成本 ≈ 驱动表行数 × 内层单次成本。 驱动表行数在乘号的左边，所以它越小越好。 但如果连接方式变成哈希，这条规矩就反过来了—— 哈希连接要把「小表」建哈希表放进内存，所以这时候是「原表小」占便宜。 所以「谁当驱动表」这件事，取决于「用什么方式连」，两个问题绑在一起。 想让优化器按你的想法来，可以用提示：/*+ LEADING(a b) */ 指定先访问 a， /*+ USE_NL(b) */ 指定对 b 用嵌套循环。 但提示是最后手段，先怀疑统计信息。

三、WHERE 的书写顺序：一条过时的经验

老资料里常说「把过滤性强的条件写在 WHERE 的最后面」。 这在 RBO（基于规则的优化器）时代是对的—— RBO 按「你写的顺序」从后往前套，所以顺序确实影响执行。 CBO 完全不是这么干的。 它先把 WHERE 解析成一堆独立的「谓词」，每个谓词单独估选择性，再枚举连接顺序、算总成本、 挑最便宜的。 你写的顺序在解析完就丢了。 怎么验证？把两个条件换个位置，比两份执行计划。 计划一模一样，就说明书写顺序没影响。 下一篇的 Demo 里就有这个对照。 RBO 在 10g 之后就被废掉了（OPTIMIZER_MODE=RULE 在 10g 还能用，11g 起彻底没了）。 所以现在还在传「条件顺序」的，多半是二十年前的经验。

四、连接列上的索引：最值钱的那一个

嵌套循环能不能跑起来，全看被驱动表的连接列有没有索引。 没索引的话，外层每来一行内层就全表扫一遍。 这也解释了「为什么小表连大表不一定慢，大表连小表反而可能很慢」： 如果大表当驱动表、小表没索引，那就是「大表行数十万次全表扫小表」。 虽然小表只有几百行，但乘上十万次就是几千万次读。 所以「连接列建索引」这条建议，收益往往比「WHERE 列建索引」还大。 而且它有个额外好处：外键列建了索引，还能避免父表操作时的锁放大（13.3 讲过）。

五、读执行计划的三个要点

要点一：看层级，不看行号。 缩进一层的是「内层」，它对外层每一行都要执行一次。 Oracle 的计划是从里往外读，最里面那步最先执行。 要点二：看「访问方式」这四个字。 TABLE ACCESS FULL 是全表扫，INDEX RANGE SCAN 是索引范围扫， INDEX UNIQUE SCAN 是唯一索引直接定位。 这四种（还有 INDEX SKIP SCAN、INDEX FAST FULL SCAN）看懂了，计划就看得懂一半。 要点三：看成本（Cost）和基数（Rows）。 Rows 是优化器【估计】这一步返回多少行，Cost 是它估的代价。 如果 Rows 估得离谱（比如实际 10 行它估 10 万行），那问题不在 SQL，在统计信息。 这一条是「读计划」和「读对计划」的分界线。

六、动手 Demo：读计划、建索引、验证书写顺序

这一篇的 Demo 用一个 20 万行的表连一个 12 行的部门表，把三件事跑出来： ① 没有索引时计划长什么样；② 建了索引之后变成什么样；③ 换个 WHERE 书写顺序，计划会不会变。 读法上要习惯一件事：这里用的是 SQLite 的 EXPLAIN QUERY PLAN，输出比 Oracle 简略， 但「谁在外层、走不走索引」这两件事是一样的。

# 读执行计划，验证「书写顺序不影响执行顺序」
import os, sqlite3, tempfile
p = os.path.join(tempfile.gettempdir(), "t151.db")   # 复用上一篇的库
c = sqlite3.connect(p)

def plan(sql):
    rows = c.execute("explain query plan " + sql).fetchall()
    return ["%s%s" % ("  " * (0 if i == 0 else 1), r[3])
            for i, r in enumerate(rows)]

q = ("select count(*) from big b join dept d on d.deptno = b.deptno "
     "where d.dname = 'DEPT03'")
print("\n".join(plan(q)))
# SCAN d
# SEARCH b USING ... (deptno=?)

c.execute("create index i_deptno on big(deptno)")
c.execute("analyze")
print("建索引之后:")
print("\n".join(plan(q)))

# 把两个 WHERE 条件换个位置，计划应完全一样
a = plan("select count(*) from big b join dept d on d.deptno = b.deptno "
         "where b.amt > 500 and d.dname = 'DEPT03'")
b = plan("select count(*) from big b join dept d on d.deptno = b.deptno "
         "where d.dname = 'DEPT03' and b.amt > 500")
print("两种写法计划一样吗:", a == b)

实跑输出：

--------------------------------------------------------------------
第六十五课：表连接——谁当驱动表，顺序重不重要
--------------------------------------------------------------------

  **连接优化的全部问题就两个：谁当驱动表（谁在外面），以及用什么方式连（嵌套循环还是哈希）。**
  **这一课用执行计划当证据，把「书写顺序」这件事一次说清。**

  一、执行计划怎么读
  --------------------------------------------------------------------
  **`EXPLAIN QUERY PLAN` 的输出，从上往下读：**
    SCAN d
    SCAN b
  **缩进在里面的是外层（先做的），缩进一层的是内层（每次外层取一行，内层跑一次）。**
  **Oracle 的执行计划是从里往外读、按缩进看层级，道理一样。**
  **上面这个计划是「先按名字找到 1 个部门，再拿这个部门号去 `big` 里找行」。**
  **`big` 用到了 `deptno` 上的索引吗？没有索引，所以是 `SCAN`。**

  二、连接列没索引：慢在哪一步
  --------------------------------------------------------------------
  **先看没有索引时的计划：**
    SCAN d
    SCAN b
  **`big` 那一步是 `SCAN`，也就是每来一个部门就扫一遍 20 万行。**
  **部门表才 12 行，所以外层扫 12 次，内层扫 12 遍大表。**
  **正解是永久建一个：`CREATE INDEX i_big_deptno ON big(deptno);`**
  **建完之后再看计划：**
    SCAN d
    SEARCH b USING COVERING INDEX i_big_deptno (deptno=?)

                      耗时
  -----------------------------
  `big.deptno` 没索引 0.0282 秒
  `big.deptno` 有索引 0.0012 秒
  已选择 2 行
  **从 `SCAN` 变成 `SEARCH`，耗时从 0.0282 秒降到 0.0012 秒，快了 23 倍。**
  **顺带说一句「自动索引」：有些库会在查询里临时建索引，用完就丢，计划里写 `AUTOMATIC`。**
  **这次没出现，是因为优化器估出来不划算。**  **它不是「每次都会帮你兜底」。**

  三、书写顺序和执行顺序：不是一回事
  --------------------------------------------------------------------
  **同一句查询，把两个 `WHERE` 条件换个位置：**
    写法一：SCAN d | SEARCH b USING INDEX i_big_deptno (deptno=?)
    写法二：SCAN d | SEARCH b USING INDEX i_big_deptno (deptno=?)
  **两份计划完全一样。**
  **因为优化器先把 `WHERE` 解析成一堆「谓词」，再统一估算成本选顺序，跟你敲字的先后没关系。**
  **所以「把过滤性强的条件写在前面」这条老经验，在 CBO 下是无效的。**
  **它只对「按规则执行」的老优化器（RBO）有意义。**

  四、想控制驱动表：写了也未必管用
  --------------------------------------------------------------------

  写法         耗时
  ----------------------
  小结果集在左 0.0012 秒
  小结果集在右 0.0012 秒
  已选择 2 行
  **两者几乎一样。**  **因为优化器会把子查询展开、自己重排，你写在哪边它不管。**
  **这就是 CBO（基于成本的优化器）和「按你写的顺序执行」的根本区别。**
  **想真的控制顺序，得用 `/*+ ORDERED */` 这类提示，或者用 `LEADING` 提示指定。**
  **但提示是最后手段：先让优化器自己选，选错了再看统计信息是不是过时了。**

第一段：SCAN d 在外层，SCAN b 在内层。 意思是「先扫 12 行的部门表，每取一行就把 20 万行的大表扫一遍」。 这个计划比「一次扫完大表再连」慢得多——因为大表被扫了 12 遍。 第二段：建了 big(deptno) 的索引之后，内层从 SCAN 变成 SEARCH。 耗时从百分之几秒降到千分之几秒，快了二十倍左右。 这就是「连接列必须有索引」的实际效果。 第三段：把两个 WHERE 条件换个位置，两份计划一模一样。 脚本里直接比了两份计划是否相等，输出 True。 所以「条件写在前面」这条经验，在这个优化器上完全没有作用。 一个数字要说明：这里的差距是二十倍，Oracle 上往往是几百倍。 因为 Oracle 的嵌套循环每次回表是一次「逻辑读」，代价比 SQLite 的函数调用高。

30 秒实验

1. 把 dept 表复制成 5000 行的版本，再跑同一个查询。 看优化器会不会换驱动表——小表变大之后，选择可能就变了。
2. 把 big(deptno) 的索引删掉，只留主键，再跑一次。 确认计划退回 SCAN，并且耗时回到原来的量级。
3. 在 dept 上再加一个 dname 的索引，看查询计划里外层会不会也变成 SEARCH。 12 行的表全扫比走索引还快，所以通常不会变——这也说明「小表不需要索引」。

常见误区

1. 「小表一定当驱动表」。 不对。是「过滤后行数少的」当驱动表。 大表加个条件只返回 10 行，它就该驱动。
2. 「WHERE 条件按过滤性排序能提高性能」。 CBO 不看书写顺序。 这条经验是 RBO 时代的，早就失效了。
3. 「哈希连接总比嵌套循环快」。 不一定。内存不够要落盘，那就更慢。 而且小结果集用哈希连接是浪费——建哈希表本身有成本。
4. 「计划里出现全表扫就是错的」。 大表取 99% 的行，全表扫才是对的。 全表扫的问题不是「扫」，是「扫了不该扫的」。

下一篇讲索引。这一篇说「连接列要有索引」，那什么样的列该有索引、什么样的列建了也白建？ 下一篇用三种「白建」的情况把这件事说清。

• ☐ （本篇完，下一篇 15.3 合理使用索引）

相关笔记

• 常规 SQL 语句优化
• 索引对象
• 死锁
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引