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

我会带着你远行 2023-10-07 15:51 19阅读 0赞

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

*  [优美的音乐节奏带你浏览这个效果的编码过程][Link 1]
 *  [坚持每一天，是每个有理想青年的追求][Link 2]
 *  [追寻年轻人的脚步，也许你的答案就在这里][Link 3]
 *  [如果你迷茫 不妨来瞅瞅这里][Link 4]

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

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

select username from tb_user where id_card=''

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

#### 1 从查询角度来分析 ####

如执行查询语句如下：

select username from tb_user where id_card='740'

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

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

--------------------

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

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

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

--------------------

#### 2 从修改数据角度来分析 ####

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

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

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 3]

##### 2.1 唯一索引的更新 #####

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

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

##### 2.2 插入新值时 #####

如插入id\_card 为 740的用户数据，需要插入的目标页在内存中，

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 4]

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

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

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

#### 3 索引选择 ####

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

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

--------------------

完毕

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

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

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

![在这里插入图片描述][20201031094959816.gif_pic_center]

[Link 1]: https://www.bilibili.com/video/BV1QX4y1G7Sh/
[Link 2]: https://www.zhihu.com/zvideo/1348960604003180544
[Link 3]: https://www.ixigua.com/6933784858019758595
[Link 4]: https://biglead.blog.csdn.net/article/details/93532582
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20210310183016575.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20210310183231196.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/2021031018555167.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20210310185721518.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/2021031018583146.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70#pic_center
[Link 5]: https://www.toutiao.com/c/user/token/MS4wLjABAAAAYMrKikomuQJ4d-cPaeBqtAK2cQY697Pv9xIyyDhtwIM/
[20201031094959816.gif_pic_center]: https://img-blog.csdnimg.cn/20201031094959816.gif#pic_center