选择唯一索引还是普通索引

要点

唯一索引和普通索引的区别是:唯一索引是只有唯一的值,普通的索引则是可能有重复的值。

InnoDB的存储原理

存储过程

在 InnoDB 中,数据是以页的形式存储的,也就说,在查找到对应的数据的时候,直接是以这个数据所在的页加载在内存中,然后返回,在 InnoDB 中,每个数据页的大小是 16K.

更新过程

更新的过程是,如果这个数据页在内存中的话,那么就是直接更新了,如果不是的话,那么就是先把更新缓存在 change buffer 中,这样就避免了从整个磁盘来读取数据,再写入。在下次需要访问到这个数据页的时候,将数据写入在这个内存中,然后再执行 change buffer 相关的操作,这种方式是一种,可以完成在逻辑的一致性相关的情况下,又能够实现性能方面的提升的手段。

在 change buffer 中的操作到原始的页里面,过程叫做 merge , 除了访问这个数据的时候会进行 merge ,在后台的也会有定时的线程来进行 merge,在数据库的正常关闭的过程中也很会有 merge 的过程。

数据读取内存是需要占用 buffer pool 的,所以这个操作能够避免占用内存。

对唯一性索引来说,每次的更新过程都会判断是否违反了唯一键约束,在判断的过程则是会进行把数据加载到内存中来更新,所以这个时候就不会用 change buffer 了,而是直接更新了。

在 change buffer 的过程中,使用的是 buffer pool 的资源,所以,可以通过设置 innodb_change_buffer_max_size 这个参数来动态设置。

所以普通索引和唯一索引的插入数据的过程:

  • 这个数据在内存中:

那么就是在插入数据的时候,两者都是直接插入,唯一索引比普通的索引增加一次的判断,这个都是在内存中的操作,所以,性能微乎其微。

  • 这个数据不在内存中:

唯一索引:先把要插入的那个数据的所在页加载到内存中,然后判断是否会违反唯一性约束,如果没有,就插入。

普通索引:直接就是加入到 change buffer 中,结束。

change buffer 的使用场景

面对着写多读少的场景,这个时候使用 change buffer 的 时候,收益是最大的,例如账单系统、日志系统。
面对着读多写少的场景,这个时候使用 change buffer 的时候,反而起到反作用,因为这个除了要维护change buffer以外,还会导致在更新的过程中,消耗到CPU和资源。

关于索引的选择

尽量选择的是普通索引,如果一个业务中,涉及到的是一条数据写完后,马上查询的话,那么这个时候需要把 change buffer 给关掉。如果面对的是机械硬盘的时候,这个时候可以配合普通索引和 change buffer 来一起使用。

数据的更新过程中,redo log 和change buffer的过程
1
2
//插入两条数据
mysql> insert into t(id,k) values(id1,k1),(id2,k2);

image

图中显示,K1的值在内存中,K2的值不在内存中。
操作的过程如下:

  1. K1 在内存中,然后直接更新 K1.
  2. K2 不在内存中,先是把 K2 从数据表空间添加到 change buffer 中,然后再添加到 system table space (系统表)中.
  3. 在 redo log 中,先加入 K1 的更新操作过程,然后再添加 K2 在 change buffer 中的操作过程。

带有 change buffer 的读操作

image

过程:
K1 是在内存中的,所以直接就返回了。
K2 是在磁盘中,先是去把 K2 所在的数据页进行加载到内存中,然后进行 change buffer 的操作,把 K2 的值添加进去,这个时候再返回。

参考文章:

《MySQL45讲》专栏中的【普通索引和唯一索引,应该怎么选择?】

spacedong wechat
愿意交个朋友吗~
觉得有收获么