MySQL普通索引和唯一索引有哪些区别？

数据库是一种以某种方式存储在一起的数据集合，可以与多个用户共享，具有最小的可能冗余，并且独立于应用程序。可以将其视为电子文件柜-存储电子文件的地方，用户可以在文件中添加，查询，更新和删除数据。数据库的种类有很多，其中MySQL数据库中有普通索引和唯一索引两种，很多人都不知道二者之间的区别。

　　MySQL普通索引和唯一索引有哪些区别？

　　一、查询和更新上的区别

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。建议尽量选择普通索引。

　　1.1MySQL的查询操作

▶普通索引

查找到第一个满足条件的记录后，继续向后遍历，直到第一个不满足条件的记录。

▶唯一索引

由于索引定义了唯一性，查找到第一个满足条件的记录后，直接停止继续检索。

普通索引会多检索一次，几乎没有影响。因为InnoDB的数据是按照数据页为单位进行读写的，需要读取数据时，并不是直接从磁盘读取记录，而是先把数据页读到内存，再去数据页中检索。

一个数据页默认16KB，对于整型字段，一个数据页可以放近千个key，除非要读取的数据在数据页的最后一条记录，就需要再读一个数据页，这种情况很少，对CPU的消耗基本可以忽略了。

因此说，在查询数据方面，普通索引和唯一索引没差别。

　　1.2MySQL的更新操作

更新操作并不是直接对磁盘中的数据进行更新，是先把数据页从磁盘读入内存，再更新数据页。

▶普通索引

将数据页从磁盘读入内存，更新数据页。

▶唯一索引

将数据页从磁盘读入内存，判断是否唯一，再更新数据页。

由于MySQL中有个changebuffer的机制，会导致普通索引和唯一索引在更新上有一定的区别。

changebuffer的作用是为了降低IO操作，避免系统负载过高。changebuffer将数据写入数据页的过程，叫做merge。

如果需要更新的数据页在内存中时，会直接更新数据页;如果数据不在内存中，会先将更新操作记入changebuffer，当下一次读取数据页时，顺带merge到数据页中，changebuffer也有定期merge策略。数据库正常关闭的过程中，也会触发merge。

对于唯一索引，更新前需要判断数据是否唯一（不能和表中数据重复），如果数据页在内存中，就可以直接判断并且更新，如果不在内存中，就需要去磁盘中读出来，判断一下是否唯一，是的话就更新。changebuffer是用不到的。即使数据页不在内存中，还是要读出来。

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

结论：唯一索引用不了changebuffer，只有普通索引可以用。

　　二、changebuffer和redolog的区别

2.1changebuffer的适用场景

changebuffer的作用是降低更新操作的频率，缓存更新操作。这样会有一个缺点，就是更新不及时，对于读操作比较频繁的表，不建议使用changebuffer。

因为更新操作刚记录进changebuffer中，就读取了该表，数据页被读到了内存中，数据马上就merge到数据页中了。这样不仅不会降低性能消耗，反而会增加维护changebuffer的成本。

2.2changebuffer和redolog区别

我们举一个例子用来理解redolog和changebuffer。我们执行以下SQL语句：

mysql>insertintot(id,k)values(id1,k1),(id2,k2);

假设，(id1,k1)在数据页Page1中，(id2,k2)在数据页Page2中。并且Page1在内存中，Page2不在内存中。

执行过程如下：

▶直接向Page1中写入(id1,k1)；

▶在changebuffer中记下"向Page2中写入(id2,k2)"这条信息；

▶将以上两个动作记入redolog。

做完上面这些，事务就可以完成了。执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

这条更新语句，涉及了四个部分：内存、redolog(ib_log_fileX)、数据表空间(t.ibd)、系统表空间(ibdata1)。

如果要读数据的话，过程是怎样的？

mysql>select*fromtwherekin(k1,k2)；

假设读操作在更新后不久，此时内存中还有Page1，没有Page2，那么读操作就和redolog以及ibdata1无关了。

▶从内存中获取到Page1上的最新数据(id1,k1)；

▶将数据页Page2读入内存，执行merge操作，此时内存中的Page2也有最新数据(id2,k2)；

　　需要注意的是：

▶redolog中的数据，可能还没有flush到磁盘，磁盘中的Page1和Page2中并没有最新数据，但我们依然可以拿到最新数据（内存中的Page1就是最新的，Page2虽然不是最新的，但是从磁盘读到内存中后，执行了merge操作，内存中的Page2就是最新的了。）

▶如果此时MySQL异常宕机了，比如服务器异常掉电，changebuffer中的数据会不会丢？

changebuffer中的数据分为两部分，一部分是已经merge到ibdata1中的数据，这部分数据已经持久化，不会丢失。另一部分数据，还在changebuffer中，没有merge到ibdata1，分3种情况：

▶changebuffer写入数据到内存，redolog也已经写入(ib-log-filex)，但是未commit，binlog中也没有fsync到磁盘，这部分数据会丢失；

▶changebuffer写入数据到内存，redolog也已经写入(ib-log-filex)，但是未commit，binlog已写入到磁盘，这部分不会多丢失，异常重启后会先从binlog恢复redolog，再从redolog恢复changebuffer；

▶changebuffer写入数据到内存，redolog和binlog都已经fsync，直接从redolog恢复，不会丢失。

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

以上就是关于MySQL普通索引和唯一索引有哪些区别的全部内容介绍，想了解更多关于数据库的信息，请继续关注中培教育。