一、主键用整型还是字符串

两万行、同样的内容，只有主键不同：一个自增整数，一个 19 字符的字符串。

第二行多了将近四成——而且这还只是 19 个字符的键。 换成 UUID（36 个字符）会差得更多，因为它不但更宽，还是随机的。 随机这件事比宽度更要命：整数自增的键总是插在 B 树最右边， 而随机字符串的键会插到树的各个位置，导致页分裂、缓存命中率下降。 所以「主键用自增整数、业务编号另开一列」这条建议，理由在物理层，不在风格层。 MySQL 的 InnoDB 还多一层：主键是聚簇索引的键，所有二级索引里都存着它——主键越宽， 每个二级索引都跟着变宽。 换句话说，在 InnoDB 里「主键宽度」这件事会被放大好几倍。 这也是为什么「用 UUID 做主键」在很多 MySQL 项目里会被明确禁掉。

二、NOT NULL：这一次实测没有差别

两行一模一样——这次实测里，NOT NULL 没有省下任何空间。 原因是 SQLite 的存储格式里，「是不是 NULL」不占额外的位； 它记的是「这个字段多长」，NULL 就是长度 0。 MySQL 那边不一样：每行有一个 NULL 位图，可空列会占位，而且允许 NULL 的列没法被索引得很紧凑。 所以那条建议在 MySQL 上成立，在 SQLite 上不成立。 这正是「照抄优化建议」最容易出错的地方——建议本身没错，只是绑定了引擎。 不过 NOT NULL 还有另一个价值，和存储无关：它是约束。 允许 NULL 的列，count(列) 会漏掉空值、= 比较永远匹配不到 NULL、聚合函数的行为也不一样。 这些坑比省几个字节重要得多——所以这条建议该守，但理由不是「省空间」。 「守一条建议」和「知道为什么守它」，在遇到例外时的表现完全不同。

三、字段宽度：够用就行，别留余量

教材举的例子是「邮编用 CHAR(6) 而不是 CHAR(255)」。 这个道理在 SQLite 上表现得不明显，因为它是变长存储——写多长就占多长。 但在 MySQL 上很直接：CHAR(255) 是定长的，哪怕只存 6 个字符，也占 255 个字符的位置。 所以「按实际需要定宽度」这条，是「定长类型」才有的问题。 变长类型（VARCHAR、TEXT）不存在这个问题，但有另一个。 超长的 VARCHAR 在某些引擎里会溢出到行外存储，读的时候多一次跳转。 所以「够用就行」这句话的准确版本是：按「这一类数据最长能有多长」来定， 而不是按「万一以后要存更长的呢」来定。 留余量的代价不是空间，是「每次读都要多跳一次」。

四、本章小结与实践与练习

第 19 章五篇，真正要带走的是五条： 一、优化先量化：慢日志找目标，profile 分段，EXPLAIN 看计划。三样缺一不可。 二、SCAN 和 SEARCH 是分水岭；看到 SCAN 就问「能不能定位」。 三、三种写法会让索引白建：函数包裹列、LIKE 前缀（有条件）、复合索引不满足最左前缀。 四、结构层的四件事：拆表省扫描、中间表省计算、批量插入省落盘、体检三条保平安。 五、所有优化建议都绑着引擎。照抄之前先量一次——量一次比记十条有用。 这一章最该记住的一句是：优化的对象不是「SQL」，是「扫多少行」。 语法漂亮但全表扫，一样慢；写得丑但走索引，一样快。 所以每次动手之前，先看计划里那两个词——SCAN 还是 SEARCH。 19.7 的练习可以挑这三道： 造一张十万行的表，把「函数包裹列」改写成范围比较，量改前改后的耗时； 给一张表加三个不同的复合索引，分别用「满足最左前缀」和「不满足」的条件各查一次，列成表； 写一个脚本，自动跑一批查询、按耗时排序、把 SCAN 的那几条标出来。 三道都动手做过，这一章就算真读完了。

五、动手 Demo：主键类型、NOT NULL、字段宽度，三件事各量一次

这一段三件事：同样两万行，主键一个用整数一个用 19 字符字符串，量体积； 再量 NOT NULL 和可空列的差别；最后讲字段宽度为什么在 SQLite 上量不出来、在 MySQL 上却很直接。

import sqlite3

c = sqlite3.connect(u"design.db", isolation_level=None)
c.executescript(u"""
CREATE TABLE t_int (id INTEGER PRIMARY KEY, v TEXT);
CREATE TABLE t_txt (id TEXT PRIMARY KEY, v TEXT);
""")
c.execute(u"BEGIN")
c.executemany(u"INSERT INTO t_int VALUES (?,?)",
              [(i, u"x" * 40) for i in range(1, 20001)])
c.executemany(u"INSERT INTO t_txt VALUES (?,?)",
              [(u"k%018d" % i, u"x" * 40) for i in range(1, 20001)])
c.execute(u"COMMIT")
c.execute(u"VACUUM")

for nm in (u"t_int", u"t_txt"):
    sz = c.execute(u"SELECT SUM(pgsize) FROM dbstat WHERE name=?",
                    (nm,)).fetchone()[0]
    print(nm, sz // 1024, u"KB")

# NOT NULL vs 可空
c.executescript(u"""
CREATE TABLE t_nn (a INTEGER NOT NULL, b TEXT NOT NULL);
CREATE TABLE t_nu (a INTEGER, b TEXT);
""")
c.execute(u"BEGIN")
c.executemany(u"INSERT INTO t_nn VALUES (?,?)", [(i, u"v") for i in range(20000)])
c.executemany(u"INSERT INTO t_nu VALUES (?,?)", [(i, u"v") for i in range(20000)])
c.execute(u"COMMIT")
c.execute(u"VACUUM")
for nm in (u"t_nn", u"t_nu"):
    sz = c.execute(u"SELECT SUM(pgsize) FROM dbstat WHERE name=?",
                    (nm,)).fetchone()[0]
    print(nm, sz // 1024, u"KB")

实跑输出：

--------------------------------------------------------------------
优化表设计：字段类型不是小事
--------------------------------------------------------------------


  主键用整型还是字符串：体积差多少
  --------------------------------------------------------------------
主键类型               两万行占多少
-----------------------------------
`INTEGER`（自增整数）  952 KB
`TEXT`（19 字符）      1320 KB
**第二行多了 39%——而且这还只是 19 个字符的键。**
**换成 UUID（36 个字符）会差得更多，因为它不但更宽，还是随机的。**
**随机这件事比宽度更要命：整数自增的键总是插在 B 树最右边，**
**而随机字符串的键会插到树的各个位置，导致页分裂、缓存命中率下降。**
**所以「主键用自增整数、业务编号另开一列」这条建议，理由在物理层，不在风格层。**
**MySQL 的 InnoDB 还多一层：主键是聚簇索引的键，所有二级索引里都存着它——主键越宽，**
**每个二级索引都跟着变宽。**

  NOT NULL：这一次实测没有差别
  --------------------------------------------------------------------
约束               两万行占多少
-------------------------------
两列都 `NOT NULL`  224 KB
两列都允许 `NULL`  224 KB
**两行一模一样——这次实测里，`NOT NULL` 没有省下任何空间。**
**原因是 SQLite 的存储格式里，「是不是 NULL」不占额外的位；**
**它记的是「这个字段多长」，`NULL` 就是长度 0。**
**MySQL 那边不一样：每行有一个 NULL 位图，可空列会占位，**
**而且允许 NULL 的列没法被索引得很紧凑。所以那条建议在 MySQL 上成立，在 SQLite 上不成立。**
**这正是「照抄优化建议」最容易出错的地方——建议本身没错，只是绑定了引擎。**
**不过 `NOT NULL` 还有另一个价值，和存储无关：它是约束。**
**允许 NULL 的列，`count(列)` 会漏掉空值、`=` 比较永远匹配不到 NULL、**
**聚合函数的行为也不一样。**
**这些坑比省几个字节重要得多——所以这条建议该守，但理由不是「省空间」。**

  字段宽度：够用就行，别留余量
  --------------------------------------------------------------------
**教材举的例子是「邮编用 `CHAR(6)` 而不是 `CHAR(255)`」。**
**这个道理在 SQLite 上表现得不明显，因为它是变长存储——写多长就占多长。**
**但在 MySQL 上很直接：`CHAR(255)` 是定长的，哪怕只存 6 个字符，也占 255 个字符的位置。**
**所以「按实际需要定宽度」这条，是「定长类型」才有的问题。**
**变长类型（`VARCHAR`、`TEXT`）不存在这个问题，但有另一个：**
**超长的 `VARCHAR` 在某些引擎里会溢出到行外存储，读的时候多一次跳转。**
**所以「够用就行」这句话的准确版本是：按「这一类数据最长能有多长」来定，**
**而不是按「万一以后要存更长的呢」来定。**

  表设计的三条硬规矩
  --------------------------------------------------------------------
规矩                    为什么
----------------------------------------------------------
主键用自增整数          窄、有序、页分裂少
能 `NOT NULL` 就别留空  约束比空间重要，空值语义坑多
宽度按实际需要定        定长类型浪费空间，变长类型浪费跳转
**三条里第一条收益最大、最没有争议。**
**第二、三条要结合引擎看：SQLite 上第二条省不了空间，**
**MySQL 上第一条的收益还要翻倍（因为主键会被复制进每个二级索引）。**
**所以看优化建议的时候，先问一句「这条是针对哪个引擎说的」。**
**问不出来，就先在自己的库上量一次——量一次比记十条建议有用。**

第一组两行差四成左右，这是「宽度」的直接后果。 但真正贵的不是这四成，是「随机插入导致页分裂」——那部分在体积上看不出来，体现在写入慢、 缓存命中率低。 第二组两行完全一样。 这个「一样」比「差多少」更有价值：它说明这条建议在 SQLite 上不成立。 如果你只记住「NOT NULL 省空间」，第一次在 SQLite 上量的时候就会怀疑自己量错了。 所以这一段的用法是：拿它当「基准」，以后看到任何优化建议，先跑一遍这个骨架，再决定信不信。

30 秒实验

1. 把 t_txt 的主键改成 36 字符的 UUID 样式（用 uuid 模块生成），量体积，看差多少。
2. 把 t_nn／t_nu 的行数从两万加到十万，看两行还是不是一样。
3. 给 t_int 加一个 CREATE INDEX i_v ON t_int(v)，再给 t_txt 加同样的索引， 比较两个索引各占多少——这能看出「主键宽度会被复制进索引」这件事。

常见误区

1. 以为「NOT NULL 省空间」在哪个引擎上都成立。SQLite 上实测一样大； MySQL 上才有 NULL 位图的差别。
2. 以为字符串主键只是「宽一点」。随机字符串还会导致页分裂和缓存命中率下降， 那部分代价比宽度大。
3. 以为字段宽度「留点余量没关系」。定长类型上它是实打实的浪费，变长类型上可能导致行外存储、 多一次跳转。

第 19 章到这里结束。下一篇进入第 20 章：权限管理及安全控制。

• ☐ （本篇完，下一篇 20.1 安全保护策略概述）

相关笔记

• 优化查询
• 优化数据库结构
• 优化多表查询
• 🛢️ MySQL从入门到精通 · 索引