MySQL中唯一索引和普通索引在查询和更新时的性能差异是什么？

知识点图片

在 MySQL（特别是最常用的 InnoDB 存储引擎）中，唯一索引（Unique Index）和普通索引（Normal Index）在数据结构上都是 B+ 树。

它们在查询时的性能差异微乎其微，但在更新（插入、修改、删除）时的性能差异可能非常大。这背后的核心机制在于 InnoDB 的 Change Buffer（写缓冲）。

以下是详细的深度对比分析：

假设我们要执行查询：SELECT * FROM table WHERE k = 5;

对于唯一索引：
- B+ 树从根节点层层向下搜索，找到 k=5 的记录后，由于索引具有唯一性，引擎知道不可能有下一条相同的记录，因此直接停止检索。
对于普通索引：
- 查找到 k=5 的第一条记录后，因为普通索引允许重复，引擎需要继续向后查找下一条记录，直到遇到第一个不是 k=5 的记录为止。

性能对比结论：微乎其微。

原因： InnoDB 的数据是按“数据页”（默认 16KB）为单位读写的。当查找到 k=5 的记录时，这条记录所在的数据页大概率已经被加载到了内存（Buffer Pool）中。
对于普通索引来说，多做一次“查找下一条记录”的操作，仅仅是在内存中进行一次指针移动和简单的 CPU 计算。除非极其罕见的情况（刚好这条记录是该数据页的最后一条，且下一条记录在磁盘的另一个数据页上），否则根本不会产生额外的磁盘 I/O。
数据库性能的瓶颈主要在磁盘 I/O，因此这多出来的一次内存/CPU 操作耗时可以忽略不计。

假设我们要执行更新（比如插入一条 k=5 的新记录）。此时需要分两种情况讨论：目标数据页在内存中 和 目标数据页不在内存中。

这是两者性能拉开差距的关键！

普通索引：
- 既然数据页不在内存里，InnoDB 不会立刻去磁盘读数据页。
- 它会将这个更新操作记录在内存的 Change Buffer 中，然后立刻返回“更新成功”。
- 之后，如果有查询操作刚好要访问这个数据页，引擎才会把数据页从磁盘读入内存，并将 Change Buffer 中的操作合并（Merge）到这个数据页上。后台线程也会定期执行 Merge 操作。
- 优势：减少了一次随机磁盘读取（Random I/O），大幅提升了更新性能。
唯一索引：
- 由于是唯一索引，数据库必须保证 k=5 这个值在整个表中是唯一的。
- 如果数据页不在内存中，引擎无法判断是否违背了唯一性原则。
- 因此，InnoDB 必须将数据页从磁盘读入内存（发生一次随机磁盘 I/O），在内存中确认没有冲突后，才能执行插入。
- 劣势：无法使用 Change Buffer，必须进行昂贵的磁盘随机读，导致更新速度大幅下降。

Change Buffer 的作用是将原本需要立刻写入磁盘的更新操作缓存下来，将多次对同一个数据页的更新合并为一次，从而将随机写转化为顺序写（写 Redo Log），并减少随机读。

适用场景：

写多读少的业务（如账单日志、行为埋点）： 普通索引配合 Change Buffer 可以极大提升写入吞吐量。
写完后立刻被查询的场景： 如果业务是“插入一条数据后立刻就去查它”，那么 Change Buffer 就不起作用了。因为刚存入 Change Buffer，立刻就被查询触发了 Merge，导致依然要读磁盘。此时 Change Buffer 反而增加了额外的维护代价。

比较维度	唯一索引 (Unique Index)	普通索引 (Normal Index)
查询性能	极快（找到即停）	极快（多查一次内存，差距可忽略）
更新性能	较慢（无法使用 Change Buffer，如果数据页不在内存，必须读磁盘校验唯一性）	较快（可使用 Change Buffer 延迟更新，避免随机磁盘 I/O）
核心约束	保证数据绝对唯一	仅用于加速查询

业务正确性优先于性能： 如果业务上要求某个字段必须唯一（例如：用户手机号、身份证号），请毫不犹豫地使用唯一索引。不要指望在应用层（Java/Go 代码）去用 SELECT + INSERT 来保证唯一性，高并发下一定会产生脏数据（除非用分布式锁，但代价更大）。
如果没有唯一性要求： 只是为了加快查询速度，请坚决使用普通索引。
极端情况（既要唯一又要极高写入性能）： 如果业务要求唯一，但数据量极大、写入 QPS 极高，唯一索引导致数据库 I/O 扛不住。此时才考虑将数据库改为普通索引（利用 Change Buffer 提升写入），然后在应用层引入 Redis 校验、分布式锁或消息队列串行化等极其复杂的架构来保证唯一性。（一般不推荐，除非到了性能生死存亡的时刻）。

播面