MySQL 普通索引和唯一索引，到底怎么选？
本文整理自《MySQL 实战 45 讲》第 09 讲，梳理"普通索引 vs 唯一索引"的选型逻辑，并补充第 10 讲对思考题的最新解答。
一句话结论
查询几乎无差别，关键看更新——核心机制是 change buffer。redo log 省随机写 IO（转顺序写），change buffer 省随机读 IO。业务能保证唯一性时，优先普通索引。
一、问题背景
假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的 SQL 语句：
select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz';
所以，你一定会考虑在 id_card 字段上建索引。
⚠️ 由于身份证号字段比较大，不建议把身份证号当做主键——主键是聚簇索引，长主键会让所有二级索引膨胀、每页 key 数减少、插入易页分裂。优先用短整型自增主键，身份证号作二级索引。
那么现在有两个选择：要么给 id_card 字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，这两个选择逻辑上都是正确的。
从性能的角度考虑，你选哪个？依据是什么？
下面从查询和更新两个过程来分析。
二、查询过程：性能几乎无差别
以 select id from T where k=5 为例。B+ 树从树根按层搜索到叶子节点（数据页），数据页内部通过二分法定位记录。
| 索引类型 | 命中后行为 |
|---|---|
| 普通索引 | 查到第一个满足条件的记录 (5,500) 后，还要找下一条记录，直到碰到第一个不满足 k=5 的记录 |
| 唯一索引 | 索引定义了唯一性，查到第一个满足条件的记录后立即停止 |
这个不同带来的性能差距有多少？答案是，微乎其微。
为什么"多查一次"几乎免费？
关键在于 InnoDB 按数据页（默认 16KB）为单位读写：
- 找到
k=5记录时，它所在的整个数据页已在内存中； - 普通索引多做的那次"查找并判断下一条记录" = 一次指针寻找 + 一次计算，纯内存操作；
- 对现代 CPU 代价可忽略。
唯一略贵的情况（概率极低）
仅当 k=5 记录刚好是数据页的最后一条，取下一条需读下一个数据页（可能触发磁盘 IO）。但整型字段一个 16KB 页可放近千个 key，记录恰好是页末的概率极低，平均性能差异仍可忽略。
→ 查询性能不是选型依据。
三、更新过程：change buffer 是关键
1. change buffer 是什么？
当需要更新一个数据页时：
- 数据页在内存 → 直接更新；
- 数据页不在内存 → 在不影响数据一致性的前提下，InnoDB 会把这些更新操作缓存在 change buffer 中，不必立即从磁盘读入该页；
- 下次查询访问该页时，把页读入内存，再执行 change buffer 中与该页有关的操作，保证数据逻辑正确。
关键性质：
- 名字叫 buffer，但可持久化——内存有拷贝，也写磁盘（系统表空间 ibdata1）；
- 内存来自 buffer pool，大小由
innodb_change_buffer_max_size动态设置（如 50 = 最多占 buffer pool 的 50%）。
2. merge：应用变更
把 change buffer 中的操作应用到原数据页、得到最新结果的过程称为 merge。触发时机：
- 访问该数据页时；
- 后台线程定期 merge；
- 数据库正常关闭（shutdown）时。
merge 前累积的变更多 → 收益越大（一次读盘应用多次变更）。
3. 谁能用 change buffer？
| 索引类型 | 能否用 change buffer | 原因 |
|---|---|---|
| 普通索引 | ✅ 能 | 无需判唯一性，不必读页 |
| 唯一索引 | ❌ 不能 | 更新必须先读页判断是否违反唯一性约束；既然已读入内存，直接更新更快，change buffer 无意义 |
这是普通索引 vs 唯一索引更新性能差距的根因。
4. 插入 (4,400) 的两种情况
情况一：目标页在内存中
- 唯一索引：找到 3 和 5 之间的位置，判断没有冲突，插入，结束；
- 普通索引：找到 3 和 5 之间的位置，插入，结束。
- 差别只是一个判断，微小 CPU 时间——不是重点。
情况二：目标页不在内存中
- 唯一索引：需要将数据页读入内存，判断没有冲突，插入，结束；
- 普通索引：将更新记录在 change buffer，语句就结束了。
- 将数据从磁盘读入内存涉及随机 IO，是数据库里成本最高的操作之一——这才是重点，差距明显。
真实案例：某 DBA 把业务库的一个普通索引改成唯一索引后，内存命中率从 99% 暴跌到 75%，整个系统阻塞、更新语句全部堵住。原因：大量插入操作下，唯一索引被迫频繁读页判重，用不上 change buffer。
四、应用场景（重点）
change buffer 只对普通索引生效，且并非所有普通索引场景都收益。判断标准：merge 之前累积的变更多，收益才大。
✅ 场景一：写多读少（收益最大）
页面写完后马上被访问的概率小，change buffer 能长时间累积变更，merge 时一次读盘应用多次变更。
- 典型业务：账单类、日志类系统；
- 应对：用普通索引 + 开大 change buffer。
❌ 场景二：写后立即读（反而副作用）
写入之后马上查询该数据页 → 立即触发 merge，随机 IO 次数没减少，反而增加了 change buffer 的维护代价。
- 应对：这种业务模式应关闭 change buffer。
✅ 场景三：机械硬盘 + 历史数据归档库（收益显著）
机械硬盘随机 IO 代价远高于 SSD，change buffer 省随机读的收益被放大。
- 典型业务：出于成本用机械硬盘的"历史数据"归档库；
- 应对：表里尽量用普通索引，把 change buffer 尽量开大，确保写入速度；
- 归档库的数据已确保无唯一键冲突，甚至可把唯一索引改成普通索引提速。
📌 选型决策表
| 业务情况 | 选型 |
|---|---|
| 业务能保证唯一性 + 写多读少 | 普通索引（借 change buffer 加速更新） |
| 业务能保证唯一性 + 写后立即读 | 普通索引 + 关闭 change buffer |
| 业务不能保证 / 要 DB 约束唯一 | 唯一索引（正确性优先，没得选） |
| 机械硬盘归档库 | 普通索引 + 开大 change buffer |
核心原则：业务正确性优先。本文讨论性能的前提是"业务代码已保证不写入重复数据"。若业务不能保证，必须唯一索引；这时本文的意义在于——遇到大量插入慢、内存命中率低时，多一个排查思路。
五、change buffer vs redo log（易混淆重点）
两者都为减少随机 IO，但机制不同：
| 机制 | 节省的 IO | 本质 |
|---|---|---|
| redo log | 随机写 IO（转顺序写） | WAL：先写日志再改数据 |
| change buffer | 随机读 IO | 缓存对不在内存页的更新，推迟读页 |
更新流程（Page1 在内存、Page2 不在内存）
执行 insert into t(id,k) values(id1,k1),(id2,k2);，涉及四部分：内存、redo log（ib_logfileX）、数据表空间（t.ibd）、系统表空间（ibdata1）。
- Page 1 在内存中，直接更新内存；
- Page 2 不在内存，在 change buffer 区域记录"往 Page 2 插入一行"；
- 上述两个动作都记入 redo log。
→ 事务完成。成本很低：写两处内存 + 一次顺序写盘。图中两个虚线箭头是后台操作，不影响响应时间。
读流程
- 读 Page 1：直接从内存返回（WAL 后不必读盘、不必读 redo log，内存数据已是正确的）；
- 读 Page 2：把 Page 2 从磁盘读入内存，应用 change buffer 里的操作日志，生成正确版本返回。
→ 直到需要读 Page 2 时，该数据页才被读入内存。
六、思考题解答
题目：change buffer 一开始是写内存的，如果这个时候机器掉电重启，会不会导致 change buffer 丢失？change buffer 丢失的话，再从磁盘读入数据就没有 merge 过程，等于数据丢失了，会出现这种情况吗？
答案：不会丢失
以下解答来自第 10 讲「上期问题时间」的最新讲解。
虽然是只更新内存，但在事务提交的时候，change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。
补充：merge 的执行流程（第 10 讲展开）
- 从磁盘读入数据页到内存；
- 从 change buffer 里找出这个数据页的 change buffer 记录（可能有多个），依次应用，得到新版数据页；
- 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
到这里 merge 过程结束。此时数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，是另外一个过程。
关键点
- change buffer 的变更会进 redo log → 崩溃恢复时能找回；
- 唯一索引的更新既然用不上 change buffer，也就不涉及这个问题；
- 这也解释了为什么 change buffer 必须写入系统表空间（持久化）——保证数据一致性，崩溃后可恢复。
七、总结
- 查询：普通索引和唯一索引性能几乎无差别（按 16KB 数据页读写，多查一次是内存内指针+计算）；
- 更新：关键在 change buffer——唯一索引用不上（必须读页判唯一性），普通索引能用；
- 场景：写多读少收益最大，写后立即读反而副作用，机械硬盘归档库收益显著；
- 选型：业务能保证唯一性 → 优先普通索引；不能保证 → 必须唯一索引（正确性优先）；
- redo log vs change buffer：前者省随机写，后者省随机读，互补；
- 掉电不丢：change buffer 操作记入 redo log，崩溃恢复可找回。