MySQL唯一索引和普通索引选哪个?

想象这样一个场景,在设计一张用户表时,每人的身份证号是唯一的,需要搜索。但由于身份证号字段较大,不好将其作为主键。在业务代码已经保证插入身份证唯一的情况下,可以选择建立唯一索引和普通索引,这时该如何选择呢?接下来,将从查询和更新的执行过程进行分析。

查询过程

假设 k 是表 t 上的索引,在搜索 select id from t where k=5 时,会先从 k 这棵 B+ 的树根开始,按层搜索叶子节点,找到 k=5 的数据页,然后在数据页内容进行二分法定位。

对于普通索引,找到 k=5 的记录后,会继续向下查找一个,直到碰到第一个不是 5 的记录结束。

对于唯一索引,由于取值唯一,找到后直接停止。

由于 InnoDB 是按照数据页为单位(数据页默认 16 KB)进行读写的,在读取一条数据时,会将整个数据页整体读到内存。 在读入内存的数据页中,如果包含 k=5 的记录,在查询的情况下,唯一索引比普通索引多了一次查找和判断的过程,可以忽略。

如果 k=5 是当前数据页的最后一条,就需要在读取下一个数据页。但这发生的概率较低,也可以忽略。

所以总得来说,普通索引和唯一索引在查询的过程中差异不大。

change buffer

在分析唯一索引和普通索引的影响前,先来认识一下 change buffer 这个结构。

什么是 change buffer ?

在执行更新操作时,如果要更新的数据页在内存中就直接更新,否则的话,在不影响数据一致性的前提下,InnoDB 会将更新操作缓存在 change buffer 中,从而省去了从磁盘读取数据页的过程。在下次查询操作读取到恰好需要更新的数据页时,会将 change buffer 的更新语句执行,写入数据页。将操作应用到硬盘的过程叫 merge. 后台线程会定期 merge 或 数据库正常关闭时,也会进行 merge 操作。

merge 的执行流程:

  1. 从磁盘读入老版本数据页。
  2. 从 change buffer中找出和该数据页关联的记录,依次应用,得到新版数据页。
  3. 写 redo log,记录数据的变更和 change buffer 的变更。

change buffer 实际上是可以持久化到硬盘中的数据,也就是说在内存和硬盘上都 change buffer 的存在。change buffer 之前叫 insert buffer,开始只对 insert buffer 有优化,后来加上了对 delete 和 update 的支持,进而改名叫 change buffer。

可以看到,先将更新操作记录在 change buffer,减少了将磁盘数据页读取到内存的过程,语句的执行速度会有很明显的提升。同时,将数据读入内存,会占用 buffer pool 内存,所以减少读操作,还提高了内存使用率。

Buffer Pool 是内存中的一个区域,InnoDB 在访问表和索引数据时会在其中进行缓存。允许在内存中直接更新经常使用的数据,来加快处理速度。在一些专用的服务器上,会将 80% 的物理内存分为 buffer pool.

可以通过 innodb_change_buffer_max_size 来设置 change buffer 占用 buffer pool 的大小。

change buffer 应用场景?

如上面提到,change buffer 预先保存了更新记录,减少了读取数据页的过程,从而提高性能。也就是说如果 change buffer 中针对不同的数据页如果包含的更新记录越多,其实收益也就越大。

因此对于写多读少的业务(更新完立即查询)change buffer 发挥的作用也就越大。如常见的账单类,日志类等系统。

如果业务是更新完立即查询,虽然可以将更新记录放在 change buffer 中,但由于之后要马上查询数据页,所以会立即触发 merge 过程。这样随机访问 IO 次数并不会减少,反而增加了 change buffer 的维护代价,起到反效果。

更新过程

对于唯一索引来说,所有的更新操作都需要判断是否违反唯一性约束。所以必须把所需要的数据页读入内存,然后直接更新就可以,不需要使用 change buffer. 所以 change buffer 只对普通索引有用。

具体分析下,对于一张表插入一个新记录:

如果新记录要更新的数据页在内存中:

对于唯一索引,找到合适的位置,判断有没有冲突,插入值,语句结束。

对于普通索引:找到位置,插入值,语句结束。

所以数据页在内存时,唯一和普通索引就差一个判断的过程。可以忽略。

MySQL唯一索引和普通索引选哪个?

扫一扫手机访问