普通索引和唯一索引

  • 主键索引:特殊的唯一索引,不允许有空值
  • 唯一索引:与 "普通索引"类似,但是索引列的值必须唯一,但是允许为空

查找过程的区别

普通索引和唯一索引在查找时有区别:

  • 普通索引在找到匹配的值之后,还会继续往下寻找
  • 唯一索引在找到匹配的之后,就不会再往下寻找了

但是这带来的区别通常来说特别小,因为InnoDB的数据都是按照页为单位读取的,当需要一条记录时,会把这个记录所在的页全部读入内存(每个数据页默认大小16KB)。除非这个数据在页尾,那么才可能继续往下读入下一页。

更新过程的区别

当需要更新一个数据时,如果数据所在的数据也在内存中,那么就会直接更新,而如果数据也不在内存中,在不影响数据一致性的前提下,InnoDB会将这些操作缓存在change buffer中,这样的话就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据也的时候,数据页被读入内存,然后在执行change buffer中与这个页相关的操作。

change buffer是可以持久化的数据。

change buffer中的操作应用到原数据页,得到新数据页的过程称为merge。除了访问数据也会触发merge外,后台线程也会定期的merge,数据库正常关闭时也会执行merge操作。

对于唯一索引来说,每次更新时都需要对唯一性约束进行校验,比如插入时,需要判断值是否已经存在,而这个判断的过程则需要将数据读入内存,如果已经读入则更新会比较快,否则还需要将数据页读入内存。所以此过程用不到change buffer,而普通索引则可以使用到

change buffer使用的时buffer pool里面的内存,因此不能无限增大。可以通过innodb_chagne_buffer_max_size来动态设置。如果设置为50,则change buffer最多只能占用buffer pool的50%。

对于写多读少的业务来说,数据在写完之后马上被访问到的概率比较小,此时change buffer效果最好。如账单,日志类系统。

而如果写了后马上又要查询,那么即使记录到了change buffer中,马上又出发merge过程。这样的话IO没有减少,白白增加了change buffer的维护代价。

索引选择

这两类索引在查询时能力没太多差别,而在更新时则有change buffer的区别。所以尽量使用普通索引。

而如果更新之后马上又要查询记录,那么就应该关闭change buffer,将参数调为0.

Last modification:January 26, 2022
If you think my article is useful to you, please feel free to appreciate