自增主键与空间回收
两个看起来很小、但追问能挖很深的话题。
自增值存在哪
| 引擎 | 存储位置 | 重启后 |
|---|---|---|
| MyISAM | 数据文件里 | 保持 |
| InnoDB 8.0 之前 | 只在内存里 | 重启后用 max(id) + 1 重新计算 |
| InnoDB 8.0 起 | 变更写入 redo log | 从 redo log 恢复，保持 |
8.0 之前的 bug：
-- 表里有 id = 1, 2, 3
DELETE FROM t WHERE id = 3; -- 自增值仍是 4（不回退）
-- 重启 MySQL
INSERT INTO t ... -- 得到 id = 3，不是 4！
重启后自增值被重算成 max(id)+1 = 3。如果 id=3 这行曾被外部系统引用过，就会出现ID 复用导致的数据错乱。8.0 把自增值变更记入 redo log 后彻底修好。
🔥 自增值为什么会不连续
三个原因，面试常问「自增 ID 一定连续吗」：
1. 唯一键冲突
-- 当前自增值 = 3
INSERT INTO t VALUES (null, 1, 1); -- 假设 c 上有唯一索引且 c=1 已存在
-- 报 Duplicate entry 错误，但自增值已经变成 4
INSERT INTO t VALUES (null, 2, 2); -- 得到 id = 4，3 被跳过
执行流程是：先申请自增值 → 再去判断唯一键冲突。申请完就把自增值改了，后面失败也不回退。
2. 事务回滚
BEGIN;
INSERT INTO t VALUES (null, 5, 5); -- 申请到 id=3，自增值变 4
ROLLBACK;
INSERT INTO t VALUES (null, 5, 5); -- 得到 id=4
为什么不设计成「失败就回退」
因为并发。假设允许回退：
事务 A 申请到 id=3
事务 B 申请到 id=4
事务 A 回滚，把自增值退回 3
事务 C 申请，拿到 3 → 和事务 B 的 4 没冲突，但如果 B 也回滚、D 再申请...
要保证回退后不出现重复，就必须在申请自增值时加锁判断「这个值有没有被别人用过」，意味着自增值的申请从无锁变成串行。MySQL 选择了「允许不连续」来换取并发性能 —— 这个取舍的解释是本题的得分点。
3. 批量插入的双倍申请策略
INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA 这类语句事先不知道要插入多少行，一行一行申请自增值太慢。InnoDB 的策略是每次申请的数量翻倍：
第 1 次申请 1 个
第 2 次申请 2 个
第 3 次申请 4 个
第 4 次申请 8 个 ...
所以插入 4 行时，第三次申请了 4 个但只用了 1 个，剩下 3 个被浪费，下一条语句从更大的值开始。
innodb_autoinc_lock_mode
控制自增锁的释放时机，三个值：
| 值 | 行为 | 并发 | binlog 安全性 |
|---|---|---|---|
| 0 | 语句执行结束才释放锁（表级锁） | 最差 | statement 格式安全 |
| 1（5.x 默认） | 普通 INSERT 申请完立即释放；INSERT...SELECT 等批量语句仍等到语句结束 | 中 | statement 格式安全 |
| 2（8.0 默认） | 一律申请完立即释放 | 最好 | statement 格式不安全 |
为什么 mode=2 + statement binlog 会主从不一致？
两个并发的 INSERT ... SELECT 交叉申请自增值，主库上得到的 id 是交错的（如 A 拿到 1,3,5，B 拿到 2,4,6）。但 binlog 以 statement 格式记录的是两条原始 SQL 语句，备库重放时串行执行，A 会连续拿到 1,2,3，B 拿到 4,5,6 —— 主备数据不一致。
所以 MySQL 8.0 同时把两个默认值都改了：innodb_autoinc_lock_mode=2 + binlog_format=ROW。两者必须配套，这个关联是高频追问点。
🔥 自增 ID 用完了会怎样
分两种情况，差别巨大：
显式定义了自增主键
int unsigned 上限是 4294967295（约 42.9 亿），int signed 是约 21.4 亿。达到上限后，自增值保持在上限不再增长，下一次插入会报：
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
报错了，所以能发现。应对：一开始就用 bigint unsigned（上限 1844 亿亿，按每秒插入 100 万行算要 58 万年）。
没有定义主键（依赖隐藏的 row_id）
InnoDB 会生成一个 6 字节的隐藏 row_id，但只用低 48 位。它是整个实例共享的全局计数器。
达到 2^48 - 1 之后，从 0 重新开始 —— 新插入的行会覆盖掉 row_id 相同的老数据，而且：
:::danger 不报错，静默覆盖 没有任何错误、没有任何告警，数据就那么被覆盖了。这是「必须显式定义主键」最硬的理由 —— 不是为了性能或规范，是为了防止静默数据丢失。
虽然 2^48 很大（281 万亿），但这是整个实例所有无主键表共用的计数器。
:::
其他 ID 的上限（面试偶尔深问）：
| ID | 位数 | 用完后 |
|---|---|---|
row_id | 48 | 归 0，覆盖数据 |
Xid（binlog 事务 ID） | 64 | 理论上重复，但需要 MySQL 重启 2^64 次 |
trx_id | 48 | 归 0，可能导致 MVCC 可见性判断错误（实际不可达） |
thread_id | 32 | 归 0，复用已释放的 ID（无害） |
只读事务不分配真实 trx_id —— 它用一个很大的临时值（2^48 + thread_id）充数，避免消耗全局 trx_id。所以在 innodb_trx 里看到特别大的事务 ID，那是只读事务。
为什么 DELETE 不释放磁盘空间
DELETE FROM t; -- 删掉所有数据
-- 但 t.ibd 文件大小不变！
原因：DELETE 只是把记录标记为「可复用」，这些位置会留给后续插入使用，但不会返还给操作系统。这些可复用但未使用的空间就是空洞（碎片）。
空洞的三个来源：
- 删除 —— 记录或整页被标记为可复用
- 插入导致的页分裂 —— 往已满的页中间插入时，页一分为二，两个页都只有一半满
- 更新 —— InnoDB 的 update 对变长字段实际是「标记删除 + 插入新记录」，同样产生空洞
随机插入的危害在这里体现得最清楚：主键是随机值（如 UUID）时，插入位置随机，页分裂频繁，表空间里到处是半满的页，实际占用可能是数据量的 2～3 倍。
重建表与 Online DDL
回收空间的办法是重建表：
ALTER TABLE t ENGINE = InnoDB; -- 等价于 ALTER TABLE t ENGINE=InnoDB, ALGORITHM=INPLACE
OPTIMIZE TABLE t; -- = recreate + analyze
重建的原理是建一个新表，按主键顺序把数据一行行插入，这样页是紧凑的，最后用新表替换旧表。
5.6 之前：阻塞 DML
老版本的流程是：建临时表 tmp_table（在 server 层）→ 逐行拷贝 → 替换。拷贝期间不能有写入，否则数据会丢。大表上这就是一次停机。
5.6+：Online DDL
关键改进是引入 row log：
1. 拿 MDL 写锁
2. 降级为 MDL 读锁 ← 从这里开始允许 DML
3. 建临时文件（在 InnoDB 内部），扫描原表主键树，逐页写入临时文件
期间所有对原表的写操作都记入 row log
4. 临时文件生成后，把 row log 的内容应用上去
5. 升级回 MDL 写锁，用临时文件替换原表数据文件
6. 释放 MDL 锁
MDL 写锁只在开头和结尾短暂持有，中间的长时间拷贝过程持有的是读锁，所以不阻塞读写。
:::warning Online 和 inplace 是两个不同的概念 两个词经常被混用，但含义不同：
- inplace —— 操作在 InnoDB 内部完成，不经过 server 层的临时表。相对的是
COPY算法（经 server 层拷贝） - online —— 不阻塞 DML
两者不等价的例子：
| 操作 | inplace | online |
|---|---|---|
重建表 ALTER TABLE t ENGINE=InnoDB | ✅ | ✅ |
| 加全文索引 / 空间索引 | ✅ | ❌ 会阻塞 DML |
改列类型 ALTER ... MODIFY COLUMN | ❌ 走 COPY | ❌ |
「加全文索引是 inplace 但不是 online」是这个区分的标准例子。
ALGORITHM=INPLACE, LOCK=NONE 可以显式声明要求，不满足时直接报错而不是悄悄降级为阻塞操作 —— 生产改表应该总是加上这个声明。
:::
重建表的两个代价
- 需要额外的磁盘空间 —— 重建期间新旧两份数据同时存在，磁盘至少要有表大小的 1 倍余量（加上 row log）
- 重建后表可能更大 —— 因为 InnoDB 重建时故意给每页留 1/16 的空闲空间（为了后续更新不立刻分裂）。所以对一张刚插入完、页本来就很满的表执行重建，结果反而变大
MySQL 8.0 的 Instant DDL
8.0.12 引入 ALGORITHM=INSTANT，在表末尾加列只改元数据，秒级完成，完全不重建表、不拷贝数据。
限制：
- 只能加在最后一列（8.0.29 起放宽，支持任意位置）
- 不能和其他 ALTER 操作一起写
- 不能用于加索引、改类型
:::tip 生产改表的决策顺序
- 加列且 MySQL ≥ 8.0.12 →
ALGORITHM=INSTANT，秒级完成 - 其他 online DDL 支持的操作 →
ALGORITHM=INPLACE, LOCK=NONE，但要先确认无长事务（否则 MDL 排队，见锁机制的 MDL 事故模式） - 不支持 online 的操作（改列类型、改字符集）或超大表 → gh-ost / pt-online-schema-change
- 任何情况下都在低峰期执行，并先在和生产等量级的测试库上计时 :::
ANALYZE 和 OPTIMIZE 的区别
| 命令 | 做什么 |
|---|---|
ALTER TABLE t ENGINE=InnoDB | 重建表（recreate），回收空间 |
ANALYZE TABLE t | 不动数据，只重新采样索引统计信息 |
OPTIMIZE TABLE t | recreate + analyze |
索引统计信息不准会导致优化器选错索引，此时 ANALYZE TABLE 是最轻量的修复手段（不动数据、很快）。详见生产排查。