一、一、别用 * 代替列名

这条建议有四个理由，只有第一个是「快」： ① 多取用不到的列，多占内存和网络；② 破坏覆盖索引；③ 表加列之后，程序的列顺序会错位； ④ 视图加列之后，INSERT ... SELECT * 会报错。 第二个理由最容易被低估。 如果有个索引刚好包含你要的两列，只查这两列时数据库只读索引、不回表—— 这叫覆盖索引。写成 * 就一定回表，覆盖索引直接失效。 所以「别写 *」的根子是「给覆盖索引留条活路」。 第三个理由跟性能无关，但后果更严重。 INSERT INTO t1 SELECT * FROM t2 这种写法，只要两张表的列顺序变过一次， 就会把数据写到错误的列里。 而且不报错，等发现的时候数据已经脏了。

二、二、TRUNCATE 和 DELETE 不是一回事

「高水位线不降」是最要命的一条。 一张表插过一千万行、再全删掉，它还是占着一千万行的空间—— 因为高水位线（HWM）标记「用过的最高位置」，DELETE 不会把它往回挪。 结果是：表里一行数据都没有，全表扫描照样读一千万个块。 降高水位只有三条路：TRUNCATE、ALTER TABLE ... SHRINK SPACE、 或者 ALTER TABLE ... MOVE 重建。 后两个都要额外空间，而且要锁表。 所以「清空整表」这个需求，一开始就该用 TRUNCATE。

三、三、分批提交：省 UNDO，也省锁

一个事务改一百万行，会带来三个问题： ① UNDO 表空间要装下一百万行的旧值，可能撑爆；② 锁持有一百万行那么久，别人全在排队； ③ 万一失败，回滚要跑很久。 所以大批量 DML 要分批：每几千行 COMMIT 一次。 这样 UNDO 能反复复用，锁的持有时间也短。 但分批提交有个反作用，很多人不知道： 它会让长查询更容易报 ORA-01555（snapshot too old）。 因为查询要的「一致性快照」依赖 UNDO，你提交得越勤，UNDO 被覆盖得越快， 查询跑到一半发现旧版本没了。 所以「跑批」和「跑报表」要错开，别在同一个时段抢 UNDO。

四、四、减少往返：这条收益最大

一次数据库往返的成本，本机是微秒级，跨网络是毫秒级。 把「查 1000 条主记录 + 每条再查一次明细」改成「一条连表查询」，往返次数从 1001 降到 1。 这个模式有个名字叫 N+1 查询，ORM 框架最容易犯。 它的隐蔽之处在于「每条 SQL 都很快」——你在慢查询日志里一条都抓不到，但页面就是慢。 因为慢的不是 SQL，是次数。 除了连表，还有两个减少往返的手段： ① 数组绑定（一次发一千条 INSERT）；② 存储过程里循环（把循环搬到服务端）。 后者现在不太推荐——业务逻辑写进存储过程，改起来和发布都麻烦，通常不如在应用层用批量接口。

五、五、EXISTS 换 IN：有一条硬理由

教材给的理由是性能：IN 要先算完整个子查询、去重、再比；EXISTS 是「找到一条就停」。 这个理由只在子查询结果集【大】的时候成立——子查询只有几十行时，两者几乎没差别。 但还有一条硬理由，跟性能无关，跟【正确性】有关：NOT IN 遇上 NULL 会返回空集。 WHERE deptno NOT IN (SELECT deptno FROM emp)，只要子查询结果里有一个 NULL， 整条语句就一行都不返回。 因为 x NOT IN (1, NULL) 等价于 x <> 1 AND x <> NULL， 而任何值和 NULL 比较的结果都是「未知」，不是「真」。 NOT EXISTS 没有这个问题。 所以规矩是： IN 可以用，但 NOT IN 一律换成 NOT EXISTS，除非你能保证子查询那列非空。 这一条比性能重要得多——性能差是慢，这个是错。

六、动手 Demo：四种改写，四种代价

这一篇的 Demo 把四件事量一遍：* 和多列的差、IN／EXISTS 的差、N+1 和连表的差、 DELETE 和清空的差。 每条都测三次取最快，避免把冷启动算进去。 注意：这里量到的倍数都比 Oracle 上小。 原因是 SQLite 的解析和执行开销都轻，而且没有网络往返。 方向是对的，量级要打折看。

# 四种改写，四种代价
import os, sqlite3, tempfile, time
p = os.path.join(tempfile.gettempdir(), "t151.db")
if os.path.exists(p):
    os.remove(p)
c = sqlite3.connect(p)
c.execute("create table big (id int primary key, name text, deptno int, amt int, pad text)")
c.executemany("insert into big values (?,?,?,?,?)",
              [(i, "n%05d" % (i % 5000), i % 12 + 1, i % 1000, "p" * 12)
               for i in range(1, 100001)])
c.execute("create table dept (deptno int primary key, dname text)")
c.executemany("insert into dept values (?,?)",
              [(i, "DEPT%02d" % i) for i in range(1, 13)])
c.commit()

def t(fn, n=3):                     # 测三次取最快
    best = 9e9
    for _ in range(n):
        a = time.perf_counter()
        fn()
        best = min(best, time.perf_counter() - a)
    return best

print("select *      %.3f 秒" % t(lambda: c.execute(
    "select * from big").fetchall()))
print("select 两列   %.3f 秒" % t(lambda: c.execute(
    "select id, amt from big").fetchall()))
print("IN       %.4f 秒" % t(lambda: c.execute(
    "select count(*) from big where deptno in "
    "(select deptno from dept where dname like 'DEPT0%')").fetchone()))
print("EXISTS   %.4f 秒" % t(lambda: c.execute(
    "select count(*) from big b where exists (select 1 from dept d "
    "where d.deptno = b.deptno and d.dname like 'DEPT0%')").fetchone()))

实跑输出：

--------------------------------------------------------------------
第六十四课：常规 SQL 优化——四条最值钱的改写
--------------------------------------------------------------------

  **这一课全是「同一件事换个写法」，四条都能实测。**
  **每条都测三次取最快，避免把冷启动算进去。**

  一、别用 `*` 代替列名
  --------------------------------------------------------------------

  写法             取回列数 耗时
  ----------------------------------
  `select *`       6        0.425 秒
  `select id, amt` 2        0.203 秒
  已选择 2 行
  **200000 行，只少取了 4 列，就快了 2.09 倍。**
  **如果表里有一列是长文本、`BLOB`、`CLOB`，差距会大成另一个数量级。**
  **`*` 的另一个坏处不是速度，是「计划会被迫回表」。**
  **只查两列时，如果有个覆盖索引包含这两列，数据库根本不用回表；写成 `*` 就一定要回表。**
  **所以「别写 `*`」这条建议的根子在「让覆盖索引有机会生效」。**

  二、`IN` / `EXISTS` / `JOIN`：同一件事三种写法
  --------------------------------------------------------------------

  写法          耗时      结果
  ------------------------------
  `IN (子查询)` 0.0582 秒 150063
  `EXISTS`      0.0850 秒 150063
  `JOIN`        0.0842 秒 150063
  已选择 3 行
  **三种写法结果一样，耗时差 1.46 倍（最快的比最慢的）。**
  **这里 `IN` 和 `EXISTS` 差不多，因为子查询的结果集只有 9 行。**
  **教材说「用 `EXISTS` 代替 `IN`」，这句话有前提：**
  **子查询结果集【大】的时候，`IN` 要先把它整个算出来、去重、再比；`EXISTS` 是「找到一条就停」。**
  **如果子查询很小，两者几乎一样，甚至 `IN` 更快（它能用哈希）。**  **所以别背教条，看数据量。**

  三、N+1 次查询 vs 一次连接
  --------------------------------------------------------------------

  方式                 数据库往返次数 耗时
  ---------------------------------------------
  先查主表再逐行查明细 1001           0.0633 秒
  一条连表查询         1              0.0012 秒
  已选择 2 行
  **差 51 倍。**  **这个差距在真实系统里还要大：**
  **每次往返都有网络延迟，本机的函数调用只有微秒级，真实网络一次往返就是几百微秒到几毫秒。**
  **所以「减少查询次数」永远是第一条优化建议。**
  **ORM 框架最容易犯这个错：查 1000 个订单，每个订单再查一次客户，就是 1001 次往返。**

  四、清空一张表：`DELETE` 和 `TRUNCATE` 差在哪
  --------------------------------------------------------------------

  清空方式                        耗时
  -----------------------------------------
  `delete from bt2`（不带条件）   0.0124 秒
  `delete from bt2 where id >= 0` 0.1270 秒
  `drop table` 再建               0.0342 秒
  已选择 3 行
  **同一件事三种写法，最快和最慢差 10 倍。**
  **第一行快是因为 SQLite 发现「不带 `WHERE` 的 `DELETE`」等于清空，直接走了截断优化。**
  **Oracle 里没有这个优化：`DELETE` 一律逐行删，每行都写 UNDO。**
  **删 20 万行要跑好几分钟，UNDO 表空间还可能撑爆，最要命的是【删完高水位不降】**——
  **表还是那么大，全表扫描还是扫那么多块。**
  **要降高水位只能 `TRUNCATE`（或者 `ALTER TABLE ... SHRINK SPACE`）。**
  **但 `TRUNCATE` 有个硬限制：它是 DDL，会隐式提交、不能回滚、不能带 `WHERE`。**
  **所以「删一部分」只能用 `DELETE`，「清空整表」才用 `TRUNCATE`。**

第一组：20 万行，select * 是 select id, amt 的两倍多。 只少取了 4 列就有这个差距，说明「取回的数据量」本身就是成本。 第二组：IN 比 EXISTS 快一点，JOIN 和 EXISTS 差不多。 注意这个结果和教材的说法相反——因为这里的子查询只有 9 行。 教材那句「用 EXISTS 代替 IN」是有前提的，别当教条背。 第三组：N+1 和连表差 50 倍。 这个差距在真实系统里会更大——本机的一次「往返」只是函数调用，真实网络一次往返要几百微秒。 所以这一条是整篇最该记住的。 第四组：清空一张 20 万行的表，三种写法差十倍。 最快的那种（不带 WHERE 的 DELETE）是 SQLite 自己的优化，Oracle 没有。 在 Oracle 上，同样的需求只能用 TRUNCATE，否则就是按行删、写 UNDO、不降高水位。

30 秒实验

1. 把第一组的表加一列长文本（比如 500 个字符），再对比 select * 和查两列。 差距会从两倍变成十几倍——因为传输量变了。
2. 在 dept 表里插一行 deptno 为 NULL， 然后跑 select count(*) from big where deptno not in (select deptno from dept)。 看结果是不是 0——这就是 NOT IN 的 NULL 陷阱。
3. 把第三组的循环次数从 1000 改成 100，再改成 5000，看倍数怎么变。 倍数基本不变，因为它就是「次数 × 单次成本」。

常见误区

1. 「SELECT * 只是风格问题」。 它会破坏覆盖索引、会让 INSERT ... SELECT 错位， 这两条都不是风格问题。
2. 「DELETE 之后表就变小了」。 不会。高水位线不降，空间不还。 要还空间只能 TRUNCATE 或 SHRINK。
3. 「提交越频繁越好」。 反作用有两个：日志写得更频繁、长查询更容易 ORA-01555。
4. 「IN 一定比 EXISTS 慢」。 子查询小的时候 IN 反而快。 但 NOT IN 遇上 NULL 会返回空集，这条是硬伤。

下一篇讲表连接。 这一篇的「减少往返」说的就是「用一条连表代替 N 次单查」，那连表本身怎么优化？ 下一篇讲两件事：谁当驱动表，以及「书写顺序」到底重不重要。

• ☐ （本篇完，下一篇 15.2 表连接优化）

相关笔记

• 检索数据
• 系统全局区（SGA）优化
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引