19.4 优化多表查询 · 波仔的 AI 编程实战课

📄 一句话概括

「小表驱动大表」在这台沙箱上测不出差别——优化器自己会重排连接顺序。 真正决定多表查询快慢的，是「连接条件上的列有没有索引」。 N+1 查询的代价在网络，不在 CPU；本地测不出来，恰恰是它最危险的地方。

📝 本章和前面几章的关系

这一篇讲多表查询。教材 19.4 只有一节，篇幅不长，但它是全书里「反直觉结论」最多的一节。 两个常见的「口诀」在这里都要打折： 「小表驱动大表」在 SQLite 上测不出差别；「JOIN 一定比 N+1 快」在本地也测不出来。 这种「口诀不成立」的场合最值得写下来，因为它教的是「怎么判断」，不是「记住哪句话」。

一、驱动表：这次测不出差别

「小表驱动大表」的意思是：让行数少的那张表去带路，每带一行就去大表里定位一次。 反过来让大表带路，就要定位很多次。 听起来理所当然。但把两种写法各跑一遍，计划是这样的：

写法	执行计划
`FROM 小表 JOIN 大表`	`SCAN` 大表 + 按主键查小表
`FROM 大表 JOIN 小表`	完全一样

两个计划一模一样，耗时也在同一水平——差别落在测量噪声里。 原因是 SQLite 自己会重排连接顺序，根本不看你写的先后。 所以在这台沙箱上，「小表在前」这件事测不出差别。 MySQL 的优化器也会重排，但重排结果受统计信息影响，所以在那边「写法」偶尔还是有用。 这条给了一个更普遍的教训：口诀是「优化器的行为」的总结，不是「语法」的规定。 优化器变聪明了，口诀就过期了。所以「为什么」比「怎么做」重要。

二、被驱动表有没有索引，才是分水岭

真正决定快慢的是另一件事：连接条件上那个列，有没有索引。

`tb_item.cid` 上	执行计划	相对耗时
没有索引	`SCAN` 整张四万行的表	1 倍
有索引	`SEARCH ... USING INDEX i_cid`	约七分之一

没有索引时，它干脆换了个策略：把四万行全扫一遍、每行回主表对一次分类。 有索引时，才变成「按 cid < 3 直接定位」。 这就是嵌套循环的代价：外层行数乘以「内层每一行要花多少」。 内层有索引，这个「多少」是一次定位；没索引，就是一次全扫。 所以多表查询优化的第一句话永远是：连接条件上的列，有没有索引。 顺序、写法、JOIN 还是子查询，都是第二位的问题。 顺便说一个容易被忽略的地方：外键列不会自动建索引。 声明 REFERENCES 只是约束，不是索引。很多人以为「有外键就有索引」，这是错的。

三、N+1：本地测不出来，上线才疼

「先查出二十个分类，再逐个去查这类的商品」——这是应用层最常见的写法，俗称 N+1 查询。 把它和一次 JOIN 放在一起量，这次实测的结果有点意外：

写法	相对耗时
二十次单表查询（N+1）	快 2.4 倍
一次 `JOIN` 加 `GROUP BY`	1 倍

N+1 反而更快。先别急着下结论，看原因： 一是本地文件、没有网络往返； 二是 JOIN 那个 GROUP BY 要在四万行上分组，建哈希表的成本压过了「多查十九次」。 换成真实的客户端服务器架构，每一次查询都要走一次网络往返，二十次就是二十个来回。 那时候结论会反过来——网络往返的固定成本，比一次本地分组贵得多。 所以「N+1 查询」这个问题，在本地测的时候看着不严重，上了线才暴露。 这也是为什么性能问题经常「开发环境好好的」——环境本身把问题掩盖了。 这条经验值得单独立一条规矩：凡「代价在网络」的优化，本地都测不出来。 要验证只能在接近生产的环境里测，或者干脆按「减少往返次数」的原则设计，不测了。

四、动手 Demo：两种连接顺序、有索引和没索引、N+1 对 JOIN

这一段建两张表：二十个分类、四万个商品。先按两种顺序写同一句连接，看计划和耗时；再把 tb_item.cid 上的索引删了，看计划怎么换形状；最后把「二十次单表查」和「一次 JOIN」放一起量。

import sqlite3, time

c = sqlite3.connect(u"join.db", isolation_level=None)
c.executescript(u"""
CREATE TABLE tb_cat (id INTEGER PRIMARY KEY, cname TEXT);
CREATE TABLE tb_item (id INTEGER PRIMARY KEY, cid INTEGER, name TEXT);
""")
c.execute(u"BEGIN")
c.executemany(u"INSERT INTO tb_cat VALUES (?,?)",
              [(i, u"类%d" % i) for i in range(1, 21)])
c.executemany(u"INSERT INTO tb_item VALUES (?,?,?)",
              [(i, i % 20 + 1, u"货%05d" % i) for i in range(1, 40001)])
c.execute(u"COMMIT")

def best(fn, n=5):
    t = None
    for _ in range(n):
        t0 = time.perf_counter(); fn()
        dt = time.perf_counter() - t0
        t = dt if t is None else min(t, dt)
    return t

def plan(sql):
    return u" / ".join(r[-1] for r in c.execute(u"EXPLAIN QUERY PLAN " + sql))

qa = u"SELECT count(*) FROM tb_cat c JOIN tb_item i ON i.cid=c.id WHERE c.id<3"
qb = u"SELECT count(*) FROM tb_item i JOIN tb_cat c ON i.cid=c.id WHERE c.id<3"
print(plan(qa))          # 两种顺序的计划
print(plan(qb))

c.execute(u"CREATE INDEX i_cid ON tb_item(cid)")
print(plan(qa))          # 加了索引之后

# N+1 vs JOIN
ids = [r[0] for r in c.execute(u"SELECT id FROM tb_cat")]
def n_plus_1():
    for cid in ids:
        c.execute(u"SELECT count(*) FROM tb_item WHERE cid=?", (cid,)).fetchone()
def one_join():
    c.execute(u"SELECT c.id, count(i.id) FROM tb_cat c "
              u"LEFT JOIN tb_item i ON i.cid=c.id GROUP BY c.id").fetchall()
print(best(n_plus_1) * 1000, best(one_join) * 1000)

实跑输出：

--------------------------------------------------------------------
优化多表查询：驱动表、索引、少查几次
--------------------------------------------------------------------


  驱动表：这一次两种写法计划完全一样
  --------------------------------------------------------------------
**教材强调「小表驱动大表」，意思是让行数少的那张表去带路。**
**先按两种顺序各写一遍，看计划：**
小表在前 SCAN i / SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
大表在前 SCAN i / SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
小表在前 5.2429 毫秒 / 大表在前 5.0050 毫秒
**两个计划一模一样，耗时也在同一水平——两次测量的差别落在噪声里。**
**SQLite 自己会重排连接顺序，不看你怎么写。**
**所以在这台沙箱上，「小表在前」这件事测不出差别。**
**MySQL 的优化器也会重排，但重排的结果受统计信息影响，所以在那边「写法」偶尔还是有用的。**
**真正决定快慢的不是顺序，是「被驱动表上有没有索引」。**
**把 `i_cid` 删掉再看同一句：**

  被驱动表没索引：计划整个换了形状
  --------------------------------------------------------------------
SCAN i / SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
SEARCH i USING COVERING INDEX i_cid (cid<?) / SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`tb_item.cid` 上  耗时
----------------------------
没有索引          0.005 秒
有索引            0.713 毫秒
**差 7 倍。**
**注意两个计划的形状：没有索引时，它干脆换成「把四万行全扫一遍、每行回主表对一次分类」；**
**有索引时，才变成「按 `cid < 3` 直接定位」。**
**这就是嵌套循环的代价：外层行数乘以「内层每一行要花多少」。**
**内层有索引，这个「多少」是一次定位；没索引，就是一次全扫。**
**所以多表查询优化的第一句话永远是：连接条件上的列，有没有索引。**
**顺序、写法、`JOIN` 还是子查询，都是第二位的问题。**

  少查几次：一次 JOIN 和二十次单表查
  --------------------------------------------------------------------
**「先查出二十个分类，再逐个去查这类的商品」——这是应用层最常见的写法，俗称 N+1 查询。**
**把它和一次 `JOIN` 放在一起量：**
写法                       耗时
-------------------------------------
二十次单表查询（N+1）      3.315 毫秒
一次 `JOIN` 加 `GROUP BY`  7.825 毫秒
**这次实测，反而是 N+1 快 2.4 倍——先别急着下结论，看原因。**
**原因有两个：一是本地文件、没有网络往返；**
**二是 `JOIN` 那个 `GROUP BY` 要在四万行上分组，建哈希表的成本压过了「多查十九次」。**
**换成真实的客户端服务器架构，每一次查询都要走一次网络往返，二十次就是二十个来回。**
**那时候结论会反过来——网络往返的固定成本，比一次本地分组贵得多。**
**所以「N+1 查询」这个问题，在本地测的时候看着不严重，上了线才暴露。**
**这也是为什么性能问题经常「开发环境好好的」——环境本身把问题掩盖了。**

开头那两个 print 打出来的计划一模一样——这就是「顺序不影响」的直接证据。 注意这两个计划都是 SCAN 开头：因为那时候 cid 上还没索引。 第三个 print 是转折点：加了索引之后，计划变成 SEARCH ... USING INDEX i_cid。 一句话、一个索引，形状就变了。 最后一行两个数：本地测出来 N+1 往往更快。 这个结果反直觉，但它是对的——本地没有网络往返，而 GROUP BY 有实打实的计算成本。 不要在本地测出来的数上做架构决策，这一条比任何具体倍数都重要。

30 秒实验

把 WHERE c.id < 3 换成 WHERE c.id < 20，看 N+1 和 JOIN 的差距会不会变——为什么。
删掉 i_cid 之后，把 tb_item 从四万行降到四百行，看「没有索引」的代价还在不在。
把 one_join 里的 count(i.id) 换成 count(*)，看结果对不对、快慢差多少。

常见误区

背「小表驱动大表」当成铁律。优化器会重排，这句话在新版本里可能已经不起作用了。
以为声明了外键就自动有索引。REFERENCES 是约束，不是索引。要加速连接，得单独 CREATE INDEX。
在本地测出「N+1 也不慢」就放心了。代价在网络，本地测不出来。这个结论只能到生产环境验证。

自测三问（点开对答案）

1. 为什么「小表在前」这台沙箱上测不出差别？ —— 因为优化器会自己重排连接顺序，你写的先后不影响执行。这条口诀总结的是「优化器的行为」，不是语法规定，优化器变聪明它就过期了。 2. 连接条件上的列没索引，代价为什么是「乘积」？ —— 因为嵌套循环：外层的每一行都要去内层找一遍。内层没索引就只能全扫，所以总代价约等于「外层行数 × 内层行数」。 3. 为什么 N+1 在本地可能比 JOIN 还快？ —— 因为本地没有网络往返，而 JOIN 加 GROUP BY 有实打实的计算成本。上了客户端服务器架构，每次查询一次往返，结论就会反过来。

多表查询讲完了。下一篇是第 19 章最后一篇：从字段类型开始，把表设计做省。

☐ （本篇完，下一篇 19.5 优化表设计）