MySql 应该选择普通索引 还是唯一 索引???

在码农的世界里,优美的应用体验,来源于程序员对细节的处理以及自我要求的境界,年轻人也是忙忙碌碌的码农中一员,每天、每周,都会留下一些脚印,就是这些创作的内容,有一种执着,就是不知为什么,如果你迷茫,不妨来瞅瞅码农的轨迹。

如在我们的用户表中,每个用户都有一个身份证号,用户在注册或者是身份信息认证时,业务代码已校验了这个身份证号的唯一性。

当经常会有根据用户身份证号来查用户信息时,一般会在 身份证号(id_card)上建立 索引。

select username from tb_user where id_card=''

给id_card 字段创建索引可以考虑唯一索引或者是普通索引。

1 从查询角度来分析

如执行查询语句如下:

select username from tb_user where id_card='740'

如果是普通索引,查询过程如下所示

在这里插入图片描述
从 id_card 索引树上查询到 id_card为740这一行然后回表获取这一行数据,然后再向下查询一次,不满足条件,终结查询。


如果是唯一索引,查询过程如下图所示

在这里插入图片描述
对于唯一索引来讲,索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。

在这个查询过程中,这两种选择的区别只是“查找和判断下一条记录”的操作,只是一次指针寻找和一次计算,性能消耗差异对于现在的 CPU 来说可以忽略不计。
在这里插入图片描述


2 从修改数据角度来分析

InnoDB引擎是按数据页为单位来读写数据的,InnoDB引擎是以页为单位,从磁盘读出来,然后以页为单位,将其整体读入内存,在 InnoDB 中,每个数据页的大小默认是 16KB。

所以在更新数据时,如果数据页在内存中就直接更新,否则会将这些更新操作缓存在 change buffer 中,先不作从磁盘中读入数据页,减少读磁盘,语句的执行速度会得到 明显的提升。

然后在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。

将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge,同时系统有后台线程会定期 merge,当然在数据库正常关闭 (shutdown)的过程中,也会执行 merge 操作。

在这里插入图片描述

2.1 唯一索引的更新

唯一索引的更新就不能使用 change buffer。

因为 唯一索引来,所有的更新操作都要先判断这个操作是否违反唯一性约束,如要插入 id_card 为 740的用户数据,而数据库中已有740这个唯一身份证,就不能进行操作,而这个过程必须要将数据页读入内存才能判断。

2.2 插入新值时

如插入id_card 为 740的用户数据,需要插入的目标页在内存中,

  • 对于唯一索引来说,找到 739 和 741之间的位置,判断到没有冲突,插入这个值,语句执行
    结束;
  • 对于普通索引来说,找到 739 和 741之间的位置,插入这个值,语句执行结束。

两者的差别就是一次的冲突判断,性能方面可以忽略。

在这里插入图片描述

当需要插入的目标页不在内存中

  • 对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结
    束;
  • 对于普通索引来说,则是将更新记录在 change buffer,语句执行就结束了。

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

3 索引选择

普通索引和唯一索引应该怎么选择。其实,这两类索引在查询能力上是没差别的,主要考虑的是对更新性能的影响。

在业务开发中,如果所有的更新后面,都马上伴随着对这个记录的查询,那么应该关闭 change buffer,反之则应该开启。


完毕

不局限于思维,不局限语言限制,才是编程的最高境界。

以小编的性格,肯定是要录制一套视频的,随后会上传

有兴趣 你可以关注一下 西瓜视频 — 早起的年轻人

在这里插入图片描述

早起的年轻人 CSDN认证博客专家 移动开发 项目管理 Java
只要用心去做,每一件事情还是有可能成功的,当然成功是没有界限的,只不过是达到自己心里的那个目标,公众号:我的大前端生涯,一个爱喝茶的程序员,通常会搞搞SpringBoot 、Herbinate、Mybatiys、Android、iOS、Flutter、Vue、小程序等.
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页