营销型网站建设价格,免费获客软件,wordpress虚拟主机安装,苏州哪家网站建设前言
在使用MySQL的过程中#xff0c;随着表数据的逐渐增多#xff0c;为了更快的查询我们需要的数据#xff0c;我们会在表中建立不同类型的索引。
今天我们来聊一聊#xff0c;普通索引和唯一索引的使用场景#xff0c; 以及为什么说推荐大家优先使用普通索引#xf…前言
在使用MySQL的过程中随着表数据的逐渐增多为了更快的查询我们需要的数据我们会在表中建立不同类型的索引。
今天我们来聊一聊普通索引和唯一索引的使用场景 以及为什么说推荐大家优先使用普通索引尽量避免使用唯一索引。
对于一个普通的二级索引目的就是为了加速查询 所以我们可能会为表中的某个字段或者某些字段建立一个普通的二级索引。
而对于唯一索引来说由于其唯一键约束的特性有时我们会更多的赋予其业务含义。 比如有一张存储身份证号的表为了保证身份证号的唯一性我们会在身份证号字段上建立唯一索引。
那为什么说不推荐大家使用唯一索引呢
接下来我们从查询和更新两方面分析一下唯一索引和普通索引的性能差距。
查询性能
我们知道每个索引其实都是一棵二叉树所以我简单画了一个索引图不太好看大家多多担待。 给大家稍微解释一下这张图不同颜色代表不同的数据页这里假设一个数据页里面存放两条数据。
我们知道MySQL磁盘与内存交互是通过一个叫做数据页的单位每个数据页默认的大小是16K。
在一棵树上只有叶子节点才会真正的存放数据非叶子节点存放的是每个下级数据页中最小的索引字段以及指向下级数据页的指针。
对于主键索引叶子节点存放的是一行真正的数据而对于二级索引来说在叶子节点存储的是索引字段以及对应的主键id。
好了下面我们分析一下普通二级索引和唯一索引是如何查数据的
以一个简单的查询sql为例select id from t where m103;
1MySQL从根节点出发通过二分法判断m103大于100小于104所以会找到根节点中100对应的数据页100-102
2在100-102的数据页上由于103大于102所以会找到102对应的102-103的数据页
3在这个数据页上找到了m103的记录并获取到了要查询的id字段。
对于普通的二级索引来说找到第一条m103的记录之后会继续向后查找在104-105这个数据页中判断是否还有符合m103条件的记录如果没有则结束查询。
而对于唯一索引来说由于其唯一性约束所以在查找到第一条记录之后就结束了查找。
可以看到二者的差别就在于是否继续查到下一条。
那这两者有多大的性能差距呢答案是几乎没有。
我们知道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操作性能自然也是比不上普通二级索引了这就是推荐大家优先使用普通二级索引的原因了。
经过对比大家也可以看到这两种索引在查询上性能基本是一致的其性能差距主要体现在更新操作上。
其实即便是大家有一些特殊的业务需要比如存放唯一的身份证号等还是建议大家通过业务层去约束。
总的来说普通的二级索引比唯一索引带来的收益要更大。
