MySQL：为什么说应该优先选择普通索引，尽量避免使用唯一索引

MySQL：为什么说应该优先选择普通索引，尽量避免使用唯一索引

前言

在使用MySQL的过程中，随着表数据的逐渐增多，为了更快的查询我们需要的数据，我们会在表中建立不同类型的索引。

今天我们来聊一聊，普通索引和唯一索引的使用场景，以及为什么说推荐大家优先使用普通索引，尽量避免使用唯一索引。

对于一个普通的二级索引，目的就是为了加速查询，所以我们可能会为表中的某个字段或者某些字段，建立一个普通的二级索引。

而对于唯一索引来说，由于其唯一键约束的特性，有时我们会更多的赋予其业务含义。比如有一张存储身份证号的表，为了保证身份证号的唯一性，我们会在身份证号字段上建立唯一索引。

那为什么说，不推荐大家使用唯一索引呢？

接下来，我们从查询和更新两方面分析一下唯一索引和普通索引的性能差距。

查询性能

我们知道每个索引其实都是一棵二叉树，所以我简单画了一个索引图，不太好看，大家多多担待。

给大家稍微解释一下这张图，不同颜色代表不同的数据页，这里假设一个数据页里面存放两条数据。

我们知道MySQL磁盘与内存交互是通过一个叫做数据页的单位，每个数据页默认的大小是16K。

在一棵树上，只有叶子节点才会真正的存放数据，非叶子节点存放的是每个下级数据页中最小的索引字段以及指向下级数据页的指针。

对于主键索引，叶子节点存放的是一行真正的数据，而对于二级索引来说，在叶子节点存储的是索引字段以及对应的主键id。

好了，下面我们分析一下，普通二级索引和唯一索引是如何查数据的？

以一个简单的查询sql为例：select id from t where m=103;

1，MySQL从根节点出发，通过二分法判断m=103大于100小于104，所以会找到根节点中100对应的数据页100-102；

2，在100-102的数据页上，由于103大于102，所以会找到102对应的102-103的数据页；

3，在这个数据页上，找到了m=103的记录，并获取到了要查询的id字段。

对于普通的二级索引来说，找到第一条m=103的记录之后，会继续向后查找，在104-105这个数据页中判断是否还有符合m=103条件的记录，如果没有则结束查询。

而对于唯一索引来说，由于其唯一性约束，所以在查找到第一条记录之后，就结束了查找。

可以看到，二者的差别就在于是否继续查到下一条。

那这两者有多大的性能差距呢？答案是几乎没有。

我们知道，MySQL的数据是以页为单位存放的，以一个int类型的二级索引为例，一个int占4个字节，加上MySQL的头信息6个字节，相当于10个字节。

那么一个16k的页上能存放多少记录呢？

16*1024/10 = 1638。也就是说，一个数据页就可能放下1600多条记录。那么我们在查询数据时，会把整个数据页都加载进内存，此时对于普通二级索引判断下一个记录的操作所需的消耗是非常非常小的。

可以说，从查询方面来看，普通二级索引和唯一索引的性能基本是相当的。

更新性能

唯一索引和普通二级索引的性能差距主要体现在更新操作上。

对于MySQL来说，更新一条语句的逻辑是首先读到要更新的记录，如果这个记录没有在内存里，就先加载到内存。然后执行更新的语句，之后再把变更的数据刷新到磁盘中。

但是，对于MySQL来说，把数据从磁盘读到内存涉及到随机IO，是成本非常高的一种操作。

如果每次更新数据都要这么来一次的话，高性能这个指标恐怕很难保证。

所以，设计MySQL的大神们引入了一个叫做change buffer的东西。

change buffer是一种可以持久化的缓存数据，当我们要更新数据时，如果要更新的数据不存在于内存，此时并不需要把数据从磁盘加载到内存，而是将更新操作记录在change buffer中，更新操作就算完成了。

当下次要读取这些数据时，会把读到的数据和change buffer进行合并，或者叫merge。

通过change buffer，更新操作就不需要去读磁盘了，全程都是内存操作，性能自然可以得到极大的提升。

但是！但是问题又来了！

change buffer只对普通二级索引有效，对于唯一索引是没有效果的。

为什么呢？

因为在更新一条记录时，我们需要检查索引的唯一性约束。

如何检查呢？自然首先要把数据从磁盘加载到内存里面才能进行判断。

可是如果都已经把数据加载到内存里，再去使用change buffer不就显得多此一举了。

所以，唯一索引不能，也没必要去使用change buffer来提升性能了。

由于对唯一索引的更新涉及到读磁盘这个随机IO操作，性能自然也是比不上普通二级索引了，这就是推荐大家优先使用普通二级索引的原因了。

经过对比，大家也可以看到，这两种索引在查询上性能基本是一致的，其性能差距主要体现在更新操作上。

其实即便是大家有一些特殊的业务需要，比如存放唯一的身份证号等，还是建议大家通过业务层去约束。

总的来说，普通的二级索引比唯一索引带来的收益要更大。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。