一、大字段拆出去：一张窄表加一张附表

一张宽表里有几个「很少查、但很占地方」的列（备注、规格、长文本）。 把它们挪到附表里，主表就变窄了。

窄表加附表，加起来比原来还大一点——因为多存了一份 id。 所以「拆了更省空间」这句话是错的。拆表的收益不在空间，在「扫描时少读」： 只查商品名和价格时，扫窄表要读的页数比扫宽表少得多，因为宽表一页放不下几行。 这就是拆表的真正理由：让「常用的那几列」待在一张紧凑的表里。 代价也明确：查一次完整记录要 JOIN 回去，多一次读。 所以拆不拆，取决于「常用查询要不要那些大字段」——要，就别拆。 这个判断得看真实的查询清单，不能凭感觉。

二、中间表：把算一次的结果存下来

「按分类统计商品数和金额」这种查询，每次都要在五万行上分组。 把结果存进一张中间表，之后直接读：

差两个数量级。这就是中间表存在的全部理由。 但它带来的问题也很明确：数据可能过期，而且过期了没人提醒。 所以中间表必须配一套更新机制——定时任务、触发器、或者写的时候同步更新。 不配就是埋雷：它会变成一个「看起来是实时数据、其实是上周的」的陷阱。 第 15 章讲触发器维护冗余数据时踩过这个坑：基线不对齐，差值恒定存在。 那一节的结论在这里完全适用——中间表就是「冗余数据」，本质一样。 所以三板斧也一样：先对齐基线、之后所有写都走同一条路、最后配一个对账。

三、批量插入：先插数据，再建索引

五万行，两种顺序各跑一次：

快一成半。倍数不算大，但方向很稳定： 先建索引，每插一行都要维护一次 B 树；后建索引，是「整批排一次序」。 批量导入的常规顺序就是这个：建表（不带索引）→ 灌数据 → 建索引 → 分析表。 四步里第二步必须包在一个事务里，那条比索引顺序影响大得多。 第 18 章量过：包事务和不包事务差两个数量级，而索引顺序只差一成半。 所以优化要有优先级——先做影响大的那件事。 这个优先级判断在别处也用得上：先看「是不是每行一次落盘」，再看「索引什么时候建」， 最后才看「SQL 写得漂不漂亮」。

四、分析表、检查表、优化表

教材讲的三条语句，在 SQLite 里各有对应：

ANALYZE 的作用是让优化器「知道数据的分布」，好选对索引。 这次实测里，删掉大批数据之后做 ANALYZE，执行计划没变—— 因为「cat = ? 走索引」这个选择太明显了，统计信息怎么变都一样。 但这不代表 ANALYZE 没用。表大、条件模糊、多个索引可选的时候，它就决定选哪个。 什么时候该跑？大批量增删改之后跑一次，这是最省事的规矩。 quick_check 只查结构，快；integrity_check 连索引和表对不对得上都查，慢。 日常巡检用前者，怀疑坏了再用后者。 VACUUM 是「整库重写」，第 18 章量过：删掉九成数据之后做一次，文件缩到十分之一。 这三条都属于「平时不用管、出事时想起它」的东西。 所以更实际的做法是把它们写进运维手册的固定位置：每周 quick_check、大批量变更后 ANALYZE、 季度 VACUUM。 靠人记的事情迟早会漏，写进流程才会真的发生。

五、动手 Demo：拆表量体积、中间表量耗时、批量插入两种顺序、三张体检单

这一段四件事：把一张宽表拆成窄表加附表，量三张表各占多少页； 把「每次现算分组」和「读中间表」放一起比；比较「先建索引再插」和「先插再建索引」； 最后跑一遍 ANALYZE／quick_check／integrity_check／optimize。

import os, sqlite3, time

# ① 拆表：宽表 vs 窄表加附表
c = sqlite3.connect(u"split.db", isolation_level=None)
c.executescript(u"""
CREATE TABLE tb_wide (id INTEGER PRIMARY KEY, name TEXT, cat INTEGER,
                       price REAL, stock INTEGER, memo TEXT, spec TEXT);
CREATE TABLE tb_base (id INTEGER PRIMARY KEY, name TEXT, cat INTEGER,
                       price REAL, stock INTEGER);
CREATE TABLE tb_memo (id INTEGER PRIMARY KEY, memo TEXT, spec TEXT);
""")
c.execute(u"BEGIN")
c.executemany(u"INSERT INTO tb_wide VALUES (?,?,?,?,?,?,?)",
              [(i, u"商品%05d" % i, i % 100, 1.0 * i, i % 50,
                u"备注" * 40, u"规格" * 40) for i in range(1, 20001)])
c.execute(u"COMMIT")
c.execute(u"VACUUM")
for nm in (u"tb_wide", u"tb_base", u"tb_memo"):
    sz = c.execute(u"SELECT SUM(pgsize) FROM dbstat WHERE name=?",
                    (nm,)).fetchone()[0]
    print(nm, sz // 1024, u"KB")

# ② 中间表：现算 vs 读缓存
q = u"SELECT cat, count(*), sum(price) FROM tb_g GROUP BY cat"
t1 = best(lambda: c.execute(q).fetchall())
c.execute(u"CREATE TABLE tb_stat (cat INTEGER PRIMARY KEY, cnt INTEGER, amt REAL)")
c.execute(u"INSERT INTO tb_stat SELECT cat, count(*), sum(price) "
          u"FROM tb_g GROUP BY cat")
t2 = best(lambda: c.execute(u"SELECT cat, cnt, amt FROM tb_stat").fetchall())
print(u"现算 %.1f ms / 中间表 %.3f ms" % (t1 * 1000, t2 * 1000))

# ③ 体检三条
print(c.execute(u"PRAGMA quick_check").fetchone())
print(c.execute(u"PRAGMA integrity_check").fetchone())
print(len(c.execute(u"PRAGMA optimize").fetchall()))

实跑输出：

--------------------------------------------------------------------
优化数据库结构：拆、加、批、检
--------------------------------------------------------------------


  大字段拆出去：一张窄表加一张宽表
  --------------------------------------------------------------------
表       占用
-----------------
tb_wide  10288 KB
tb_base  520 KB
tb_memo  10028 KB
**一次拆表，宽表变成窄表加附表，两部分加起来比原来大一点（多了一份 id）。**
**「拆了更省空间」是错的——拆表的收益不在空间，在「扫描时少读」：**
**只查商品名和价格时，扫窄表的页数比扫宽表少得多，因为宽表每页放不下几行。**
**这就是拆表的真正理由：让「常用的那几列」待在一张紧凑的表里。**
**代价也明确：查一次完整记录要 `JOIN` 回去，多一次读。**
**所以拆不拆，取决于「常用查询要不要那些大字段」——要，就别拆。**

  中间表：把算一次的结果存下来
  --------------------------------------------------------------------
做法                 耗时
-------------------------------
每次现算 `GROUP BY`  0.028 秒
读中间表             0.158 毫秒
**差 177 倍。这就是中间表存在的全部理由：**
**把「算一次要几十毫秒」变成「读一次要零点几毫秒」。**
**但代价是「数据可能过期」，而且过期了没人提醒。**
**所以中间表必须配一套更新机制（定时任务、触发器、或者写的时候同步更新），**
**不然它会变成一个「看起来是实时数据、其实是上周的」的陷阱。**
**第 15 章讲触发器维护冗余数据时踩过这个坑：基线不对齐，差值恒定存在。**

  批量插入：先插数据，再建索引
  --------------------------------------------------------------------
做法                  耗时
------------------------------
先建索引，再插五万行  0.786 秒
先插五万行，再建索引  0.589 秒
**快 1.33 倍。倍数不算大，但方向很稳定：**
**先建索引，每插一行都要维护一次 B 树；后建索引，是「整批排一次序」。**
**批量导入的常规做法就是这个顺序：建表（不带索引）→ 灌数据 → 建索引 → 分析表。**
**四步里第二步必须包在一个事务里，那条比索引顺序影响大得多（第 18 章量过，差两个数量级）。**

  分析表、检查表、优化表
  --------------------------------------------------------------------
**删掉大批数据之后，统计信息就旧了。先看分析前后的计划：**
分析前 SEARCH tb_g USING INDEX i_cat (cat=?)
分析后 SEARCH tb_g USING INDEX i_cat (cat=?)
索引   统计值
-------------
i_cat  500 5
**这次计划没变——因为「`cat = ?` 走索引」这个选择太明显了，统计信息怎么变都一样。**
**但这不代表 `ANALYZE` 没用：表大、条件模糊、多个索引可选的时候，它就决定选哪个。**
**MySQL 那边是 `ANALYZE TABLE`，对应 SQLite 的 `ANALYZE`；**
**`CHECK TABLE` 对应 `PRAGMA quick_check`；`OPTIMIZE TABLE` 对应 `VACUUM`。**
语句                      返回
------------------------------
`PRAGMA optimize`         0 行
`PRAGMA quick_check`      ok
`PRAGMA integrity_check`  ok
**`quick_check` 只查结构，快；`integrity_check` 连索引和表对不对得上都查，慢。**
**日常巡检用 `quick_check`，怀疑坏了再用 `integrity_check`。**
**`optimize` 是「按需分析」：它自己判断要不要更新统计信息，返回空表示「不用」。**

第①步三行数字要一起看：宽表约 10 MB，窄表约 0.5 MB，附表约 10 MB。 窄表加附表加起来比宽表大一点点——这就是「多存一份 id」的代价。 但窄表只有 0.5 MB 这件事很关键：常用查询只碰它，读的页数少了二十倍。 第②步那个比值是这一节的核心，但别忘了它的前提：中间表的数据是「上一次算的」。 如果主表刚变过而中间表没跟着更新，越快反而越危险。 第③步三条输出：两个 ok 加一个 0（optimize 认为不用做事）。 0 这个返回容易被误读成「失败」，其实是「没必要」——它自己判断过了。

30 秒实验

1. 把 memo 的宽度从 40 个「备注」加到 120 个，重跑第①步，看宽表和附表的体积比例怎么变。
2. 在第②步之后，往主表插一千行，再读一次中间表，看它有没有变（应该没有）。
3. 把第③步的 quick_check 换成 integrity_check，各跑五次取最快，比较两者耗时。

常见误区

1. 以为拆表能省空间。拆完通常更大（多一份 id）。它省的是「常用查询的扫描量」。
2. 以为中间表建好就完事。没有更新机制，它就是一份「看起来是实时数据」的旧快照。
3. 把 PRAGMA optimize 返回空当成失败。那是「当前不需要更新统计信息」，是正常结果。

结构优化讲完了。下一篇回到查询，但这次是多表：连接怎么连才快。

• ☐ （本篇完，下一篇 19.4 优化多表查询）

相关笔记

• 优化查询
• 使用触发器
• 🛢️ MySQL从入门到精通 · 索引