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

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

• 唯一索引：唯一索引和主键索引一样不能重复。唯一索引可作为数据的一个合法检验手段。
• 普通索引：在创建普通索引时，没有任何的限制条件，比如非空或者唯一，可以在任意字段上建立普通索引。

假如现在在维护一个大学的学生管理系统，每个人都有一个唯一的学号，在业务上代码已经保证不会写入两个重复的学号，如果要通过学号查找电话号，执行的SQL语句是：

select phone from student where s_id = 19********;

应该在学号上面建立索引来提高查找速度，理所应当的应该是用学号作为主键，建立主键索引。

但是由于学号字段太长了，会额外占用很多的数据页的空间，会导致同等数据下数据页变多了。所以不建议用学号作为主键。所以考虑要么给学号建立唯一索引，要么就是建立普通索引。如果业务上已经保证了不会有两个重复的学号的，那么这两个选哪一个都行。

问题如果要从性能上分析，如何选择？

查找过程：

执行的查询语句是select id from T where k=6。这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

• 对于普通索引来说，查找到(6,600)这条数据之后，在去查找下一条记录，知道遇到第一个不相等的结束。
• 对于唯一索引来说，因为是唯一的，所以查找到(6,600)这条数据之后，直接结束了。

这个不同会给性能带来什么影响呢？答案是微乎其微。

InnoDB的数据是以数据页在磁盘中存放的，读取一条记录的时候，是以页来从磁盘上面整体读取到内存中，并不是把所有的都读出来。每页的数据大小默认是16KB。

所以当找到k=6的时候，这个数据页已经在内存中了。所以对于普通索引来说，只是比唯一索引多了一次寻址和计算的操作，

还有一个特殊的情况：找到k=6的这个记录之后，其位置正好处于数据页的最后一行，那么他就要在读进来一页然后寻址在判断，直到找到第一个k!=6的为止。这个操作就复杂了。

一个数据页可能有上千个key，所以这种情况的概率很低。在计算平均性能的时候，这个操作成本对于现在的CPU来说仍可认为是忽略不计的。

这个是查找的过程。

更新过程：

一个新的概念：change buffer

当需要更新一个数据页的时候，如果数据页还在内存中就直接更新，如果数据页在磁盘中就先将更新的内容缓存在change buffer中，这样就节省了将数据页从磁盘读取到内存这个操作。下次需要访问这个数据页的时候，将数据页读取到内存中，然后执行change buffer中与这个数据页相关的更新操作，可以保证这个数据页的正确性。

change buffer它是可以进行持久化的，它也会被写入到磁盘上。（ change buffer可以看成也是一个数据页，需要被持久化到 系统表空间（ibdata1），以及把这个change buffer页的改动记录在redo log里，事后刷进系统表空间（ibdata1）。 ）

上述提到的将change buffer中记录的操作更新到数据页，这个操作叫做merge。触发merge有三种方式：

• 除了访问这个数据页会触发merge之外
• 系统后台的线程会定期merge
• 在数据库正常的关闭的时候，也会merge操作。

change buffer 的适用场景：

先看唯一索引，唯一索引每次更新数据的时候，要将数据页读到内存中，然后找到相关的位置判断要更新的数据存在否，所以唯一索引在更新数据的时候总是要把数据页读到内存中，change buffer对它起不到任何作用。也可以说是起到负面的作用了，因为写change buffer也需要时间啊。

实际上用到它的就只有普通索引了。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

所以刚才的问题，普通索引的性能更好一些。

问题普通索引的所有场景，change buffer都适用吗？

merge的时候才是数据页更新的时间，所以change buffer积累的越多，merge收益也就越大，

有这么一种情况，当用户更新数据之后立刻想要看更新的数据（数据页不在内存中），然后思考：

它立刻拿数据，change buffer 刚写完就要将数据页读进内存，还没来的及merge呢，上边说了，数据从磁盘到内存涉及随机IO的访问，所以相当于上边说的唯一索引的场景，change buffer 起不到任何的作用，反而增加了这个change buffer维护的代价。

所以如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

因此，对于写多读少的场景非常适用于change buffer，比如账单类，日志系统。

特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。（ 因为机械硬盘盘头寻址慢，随机读成本相对于ssd更高，而change buffer可以有效减少随机读 ）

因为redolog 和 change buffer 比较像，简单的说一下两者之间的区别：

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。