唯一索引和普通索引应该怎么选？

1、查询对比

假设有一个表T，有索引字段idx，执行下面的语句：

select * from T where idx = 5;

• 对于唯一索引，首先会根据B+Tree根节点进行搜索，定位到idx = 5这一条记录所在页并读入内存，由于字段值唯一，因此不需要对下一条记录进行判断，直接返回即可；
• 对于普通索引，在拿到第一条idx = 5的记录后，还需要对后续记录进行判断，直到idx != 5

对比两者的消耗，虽然普通索引需要对后续的记录进行判断，但由于读入的是一整个数据页，大部分情况下后续需要判断的记录也在这一个数据页中。因此后续的判断操作基本是在内存中进行一次遍历计算而已，影响很小。因此可以得出结论，在查询方面二者的效率基本一致。

但是如果是在更新情况下，二者的效率就大不相同

2、更新对比

（1）是否需要读入？

首先抛出一个问题，对一条记录进行更新，有必要将其读取到内存当中吗？

• 对于唯一索引，为了确保字段的唯一性，肯定是需要先读入到内存当中才能进行判断，避免出错
• 而对于普通索引，则取决于后续是否要查询到这条记录，如果要进行查询，那么自然是需要读入内存的；而如果一直不需要进行查询，那么直接更新即可；

经过分析，显然二者是否需要进行读入有一定的差异，唯一索引是必定要进行读入，而普通索引则有时候可以不读入。读入一条记录需要对磁盘进行随机访问，需要消耗非常多的时间，如果能够减少不必要读入次数，那么对性能的提升是显著的，于是就有了change buffer的出现

（2）change buffer可以减少普通索引的读入

• 【1】change buffer的工作模式
  当需要对一条记录进行更新且该记录不在内存当中时，在不影响一致性的情况下，InnoDB会将该更新操作记录在change buffer当中，不对该记录所在的数据页进行读入，如果后续对该页进行了查询，那么再进行读入并与change buffer的记录进行合并

• 【2】change buffer的作用
  当更新操作不涉及到唯一索引时，change buffer就可以大展拳脚了。由于普通索引不需要进行唯一性判断，因此读入操作并不是必须的，此时就可以使用change buffer进行记录，如果后续的业务不涉及到对脏页的查询，那么就相当于减少了一次对磁盘的随机读入，性能大大增加；而唯一索引由于需要进行唯一判断，因此无法应用change buffer

• 【3】change buffer的局限
  任何一个东西的使用都是有代价的，使用change buffer可以在不进行查询时偷懒。但是当业务要求先写后查时，change buffer的维护工作就反而成为累赘了。

（3）结论

经过上面的分析，我们可以得到以下结论

• 在写多读少的业务中，由于change buffer的存在，可以使得普通索引减少不必要的读操作，大幅度的提高性能，此时建立普通索引优于唯一索引
• 在读多写少或是先写后读的业务场景中，那么change buffer的维护的代价可能大过作用，因此唯一索引可能占优

3、实际应用

普通索引和唯一索引在查询方面的差距几乎一样，但是在更新方面则有显著差异，二者的选择依赖与业务场景，在写多读少的业务里，普通索引的性能是要远远强于唯一索引的。虽然维护change buffer需要耗费一定的资源，但是change buffer是可关闭的。在业务场景允许的情况下，由于IO操作所带来的消耗巨大，那么普通索引可能是一个更好的选择。

需要注意的是，change buffer只是用于内存同步，减少不必要的读操作，而对数据库表的实际修改是由redo log日志进行控制的，因此不必担心因为内存丢失等原因而导致对数据库表的更新没有持久化的问题，且change buffer是可以持久化到硬盘的