MySQL 普通索引和唯一索引，应该如何选择？

MySQL原理剖析 0 11

引言

在前面的文章中，我们已经详细了解了关于索引的概念了，而且我们也已经介绍过普通索引和唯一索引了，也看过了一些案例。所以我相信你对索引也已经有比较深刻的理解了。

我们先来看一个小故事。

你正在开发一个在线电商平台。用户在平台上创建订单后，后台会保存这些订单数据，每一条记录包含订单号、用户 ID （user_id）等信息。随着订单量的不断增加，这张表的数据量已经突破了百万级别。

一天，产品经理跑过来告诉你：“最近有用户反映订单详情页打开速度变慢了。能不能优化一下，提升查询速度？”

你看了一下代码，发现订单详情页需要通过用户 ID 查找该用户的最新订单记录。你很快想到，给 user_id 列创建一个索引应该能解决问题。于是，你快速地执行了以下语句：

CREATE INDEX idx_user_id ON orders(user_id);

上线后，查询速度确实有了明显提升。你暗自庆幸，问题看起来已经解决了。

但好景不长，过了几天，运维团队发来了警报：订单查询的性能再次出现了问题。一些用户抱怨，他们的订单详情页加载速度依旧很慢。经过进一步排查，你发现一个很有意思的现象：这些用户都有一个共同点——他们的用户ID 在订单表中对应了大量的订单记录。

比如用户 A 在平台上下了 1000 多笔订单，当通过索引查询到所有符合条件的记录后，数据库需要一次次回表来获取完整的订单数据，而这一过程耗费了大量的磁盘 I/O。

所以你开始反思：如果把普通索引改为唯一索引，会不会更高效？

但转念一想，唯一索引要求 user_id 是唯一的，但是同一个用户可能有多笔订单，显然 user_id 并不符合唯一性要求。

于是你陷入了两难的境地，不知道该咋办了……

所以这篇文章，我们就基于上面的场景以及之前讲过的知识，再来深入讨论一个问题：在不同的业务场景下，普通索引和唯一索引，应该怎么进行选择。

查询过程

我们还是从一个具体的例子来入手。

有一张表，表结构是这样的：

CREATE TABLE T (
  id INT PRIMARY KEY,  -- 主键索引（聚簇索引）
  k INT NOT NULL,      -- 辅助索引的列
  name VARCHAR(16),
  INDEX (k)            -- 普通索引或唯一索引
) ENGINE=InnoDB;

表中现在有一些数据：

id	k	name
100	1	…
200	2	…
300	3	…
500	5	…
600	6	…

比如我现在要执行这个语句：

select id from T where k=5

之前我们也已经提到过索引树了，所以执行这个查询语句的过程，也就是在索引树上搜索的过程。

这里我们按照两种情况来进行分开讨论：

对于普通索引来说，当查找到记录（500，5）之后，这条记录显然是满足条件的。但是当 k 是普通索引的时候，k 的值不是唯一的，所以可能有多条记录符合查询条件。所以当搜索到第一条满足查询条件的记录以后，还会继续进行查询，直到查询到的记录不满足条件了。
对于唯一索引来说，索引列中的值都是唯一的，所以查找到第一个满足条件的记录之后，就会停止检索了。

通过我们的分析，可以看到的是，当 k 是普通索引的时候，可能会多搜索几次。所以也就导致了性能上的差距，但是，值得一提的是，这种差距是非常小的，小到可以忽略不计。

下面我们来分析一下原因。

InnoDB 存储引擎在读取数据的时候，是按照数据页为单位来进行读取的。比如，当查询到 k=5 这条记录的时候，这条记录所在的一整个数据页就已经在内存中了。如果此时 k 是普通索引，恰好有多个记录满足查询条件，多做的操作也只是多几次指针寻找和查询。

但是，这里会有一个特殊情况，那就是如果查询到的第一条满足查询条件的记录恰好是这个数据页的最后一条数据，该怎么办呢？因为它是数据页的最后一条数据，如果再去读取下一条数据，就需要额外再读取一个数据页了。

值得高兴的是，在InnoDB 中，数据页的大小默认是 16 KB。如果存储整型字段，可以存储几千条。所以上述这种情况基本不会发生。

所以，综上所述，在查询的时候，普通索引和唯一索引的平均性能差距几乎一样。

更新过程

在上一个章节，我们已经具体分析了在普通索引和唯一索引两种情况下的查询过程。接下来呢，我们再来看一下更新过程具体是怎样的。

但是，在了解更新过程之前，我们需要先来了解一个概念，这个概念就是 change buffer，它能帮助我们更好的理解更新过程。

引入 Change Buffer

Change buffer 也叫写缓冲，它主要是用于在数据库中缓存对普通索引页的更改操作。

比如说，当你对数据库中的表进行插入、更新或者删除操作时，如果涉及到普通索引页的更改，这些更改不会立即被写入磁盘上的索引页，而是先被记录到 change buffer 中。这样做的好处是可以减少磁盘 I/O 操作。

举个例子吧，在一个电商数据库中，有一个商品表，包含商品的名称、价格、库存等信息。同时还有一个按照商品名称建立的普通索引。当你更新某个商品的价格时，这个更改操作会先被记录到 change buffer 中。当数据库系统在合适的时候（比如数据库空闲时或者进行索引页读取操作时），会把 change buffer 中的更改合并到磁盘上的索引页中。

将 change buffer 中的更改操作合并到磁盘上的索引页中的过程，就叫 merge 。

比如说，数据库在一段时间内积累了很多对普通索引页的更改操作在 change buffer 中。当数据库系统认为时机合适时，比如数据库空闲了或者有查询操作需要读取相关的索引页时，就会触发 merge 操作。它会把 change buffer 中的这些更改操作按照一定的顺序应用到磁盘上的索引页中，使得索引页中的数据与数据库中的实际数据保持一致。

那么，什么条件下可以使用 change buffer 呢？如果你足够细心的话，你就能留意到上文中的 普通索引页 了，也就是说，change buffer 一般都用在普通索引上。

这时你可能会问：唯一索引可以用吗？答案是，不能。至于为什么，你且听我仔细分析。

对于唯一索引，因为索引列中的值都是唯一的，所以在更新数据的时候，都需要先进行判断操作。那么在进行判断的时候，就需要读取这条被判断的记录，所以这条记录所在的数据页也就被读入内存中了。那既然数据页已经在内存中了，也就不需要 change buffer 了，对吧。

所以呢，唯一索引的更新就没法使用 change buffer 了。实际上，也只有普通索引能用 change buffer 。

至此，我们已经了解了 change buffer 的概念以及运行机制。有了这些基础，我们就可以放心的来看下面的更新流程了。

更新流程

假如我现在要往数据表中插入这条记录：（400，4），会发生什么？

我们可以按照两种情况来讨论。

第一种情况，就是目标页在内存中：

如果目标页在内存中，无论是普通索引还是唯一索引，更新操作相对会比较快。

对于普通索引，数据库可以直接在内存中的索引页进行更新操作，将新的索引值和对应的记录标识添加到索引中。
对于唯一索引，首先会检查内存中的唯一索引页是否存在相同的值。如果不存在，就将新记录插入到表中，并更新唯一索引页。

第二种情况，就是目标页不在内存中，情况就会稍微复杂一些：

对于普通索引，数据库会先将更改操作记录到 change buffer 中。因为如果此时从磁盘读取目标页到内存再进行更新，会比较耗时，而将更改操作记录到 change buffer 可以在后续合适的时候再进行合并操作，从而减少磁盘 I/O 次数，提高性能。
对于唯一索引，数据库必须先从磁盘将目标页读入内存，然后检查唯一索引页是否存在相同的值。如果不存在，就将新记录插入到表中，并更新唯一索引页。

我们都知道，将数据从磁盘读取到内存，这种操作的成本是非常高的。change buffer 的存在可以减少访问磁盘的次数，所以对更新数据的性能提升是非常明显的。

Change Buffer 的使用场景

在上面两个小节中，我们已经了解了change buffer 的运行机制，也知道了其在更新过程中的优点。

现在，我们再来思考一个问题：在所有使用普通索引的场景中，change buffer 都能起到加速作用吗？

显然，在一些写多读少的情况下，change buffer 确实可以加速普通索引的更新操作。比如，当数据库有大量的插入、更新操作，并且更新完数据之后不需要马上去读取，这时就可以将更改操作记录到 change buffer 来避免频繁的磁盘 I/O，等合适的时候再进行合并操作，从而提高性能。这种常见的业务模型一般是账单类系统或者日志类系统。

但是，我们需要考虑这样一种情况，如果在一个业务场景中写入了很多数据，但是又需要马上读取这些数据。在这种情况下，change buffer 就会马上触发 merge 操作，这样的话，磁盘 I/O 的次数并没有减少，反而增加了维护 change buffer 的成本，这样就得不偿失了。

综上所述，change buffer 适用于写多读少的场景，而对于那些更新完数据需要马上查询的业务场景，change buffer 就有反作用了。

索引选择和实践

好，讲到这里，我们回过头来看一下文章刚开头提出的问题。

想必你心中也应该有一个答案了，我就来说说我的想法。

对于读操作，我们在第二个章节已经提到过，那就是普通索引和唯一索引的性能差异不大。因为 SELECT 不涉及索引的变更操作，因此 Change Buffer 在这个场景下并不生效。所以性能表现上两者差异较小。

对于写操作，普通索引和唯一索引的区别就会显现，尤其是当订单表的写入量很高时。

对于普通索引来说，InnoDB 会先将新增记录对应的索引变更写入到 Change Buffer，而不是立即加载索引页到内存中进行更新。后续在空闲时或查询触发时，后台线程会将 Change Buffer 中的内容合并到磁盘上的索引页中。
大量的写操作会被缓存在 Change Buffer 中，减少磁盘 I/O，从而显著提高写入性能。

而对于唯一索引来说，它不支持 Change Buffer，每次更新都需要检查更新的数据是否唯一，因此必须立即将索引页从磁盘加载到内存中，进行更新后再写回磁盘。每次插入都会触发磁盘 I/O，性能就比较差。

所以我会选择建立一个普通索引，不知道你的选择是否和我一样。

总结

这篇文章中，我们通过一个具体的业务场景抛出了一个问题：普通索引和唯一索引应该如何选择。基于这个问题，我们分析了查询过程和更新过程，并且在更新过程的章节中我们引入了change buffer 的概念，还讲了它的使用场景。最后我们得出了一个结论：因为唯一索引无法使用 change buffer ，所以在适合业务的基础上，我们还是优先考虑普通索引。

引言