唯一索引和普通索引应该如何选择
- 唯一索引:唯一索引和主键索引一样不能重复。唯一索引可作为数据的一个合法检验手段。
- 普通索引:在创建普通索引时,没有任何的限制条件,比如非空或者唯一,可以在任意字段上建立普通索引。
假如现在在维护一个大学的学生管理系统,每个人都有一个唯一的学号,在业务上代码已经保证不会写入两个重复的学号,如果要通过学号查找电话号,执行的SQL语句是:
select phone from student where s_id = 19********;
应该在学号上面建立索引来提高查找速度,理所应当的应该是用学号作为主键,建立主键索引。
但是由于学号字段太长了,会额外占用很多的数据页的空间,会导致同等数据下数据页变多了。所以不建议用学号作为主键。所以考虑要么给学号建立唯一索引,要么就是建立普通索引。如果业务上已经保证了不会有两个重复的学号的,那么这两个选哪一个都行。
问题
如果要从性能上分析,如何选择?
![](https://i-blog.csdnimg.cn/blog_migrate/c52114ff5de8c022fa59ed010cde299b.png)
查找过程:
执行的查询语句是select id from T where k=6。这个查询语句在索引树上查找的过程,先是通过B+树从树根开始,按层搜索到叶子节点,也就是图中右下角的这个数据页,然后可以认为数据页内部通过二分法来定位记录。
- 对于普通索引来说,查找到(6,600)这条数据之后,在去查找下一条记录,知道遇到第一个不相等的结束。
- 对于唯一索引来说,因为是唯一的,所以查找到(6,600)这条数据之后,直接结束了。
这个不同会给性能带来什么影响呢?答案是微乎其微。
InnoDB的数据是以数据页在磁盘中存放的,读取一条记录的时候,是以页来从磁盘上面整体读取到内存中,并不是把所有的都读出来。每页的数据大小默认是16KB。
所以当找到k=6的时候,这个数据页已经在内存中了。所以对于普通索引来说,只是比唯一索引多了一次寻址和计算的操作,
还有一个特殊的情况:找到k=6的这个记录之后,其位置正好处于数据页的最后一行,那么他就要在读进来一页然后寻址在判断,直到找到第一个k!=6的为止。这个操作就复杂了。
一个数据页可能有上千个key,所以这种情况的概率很低。在计算平均性能的时候,这个操作成本对于现在的CPU来说仍可认为是忽略不计的。
这个是查找的过程。
更新过程:
一个新的概念:change buffer
当需要更新一个数据页的时候,如果数据页还在内存中就直接更新,如果数据页在磁盘中就先将更新的内容缓存在change buffer中,这样就节省了将数据页从磁盘读取到内存这个操作。下次需要访问这个数据页的时候,将数据页读取到内存中,然后执行change buffer中与这个数据页相关的更新操作,可以保证这个数据页的正确性。
change buffer它是可以进行持久化的,它也会被写入到磁盘上。( change buffer可以看成也是一个数据页,需要被持久化到 系统表空间(ibdata1),以及把这个change buffer页的改动记录在redo log里,事后刷进系统表空间(ibdata1)。 )
上述提到的将change buffer中记录的操作更新到数据页,这个操作叫做merge。触发merge有三种方式:
- 除了访问这个数据页会触发merge之外
- 系统后台的线程会定期merge
- 在数据库正常的关闭的时候,也会merge操作。
change buffer 的适用场景:
先看唯一索引,唯一索引每次更新数据的时候,要将数据页读到内存中,然后找到相关的位置判断要更新的数据存在否,所以唯一索引在更新数据的时候总是要把数据页读到内存中,change buffer对它起不到任何作用。也可以说是起到负面的作用了,因为写change buffer也需要时间啊。
实际上用到它的就只有普通索引了。
change buffer 用的是 buffer pool 里的内存,因此不能无限增大。change buffer 的大小,可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候,表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
将数据从磁盘读入内存涉及随机 IO 的访问,是数据库里面成本最高的操作之一。
change buffer 因为减少了随机磁盘访问,所以对更新性能的提升是会很明显的。
所以刚才的问题,普通索引的性能更好一些。
问题
普通索引的所有场景,change buffer都适用吗?
merge的时候才是数据页更新的时间,所以change buffer积累的越多,merge收益也就越大,
有这么一种情况,当用户更新数据之后立刻想要看更新的数据(数据页不在内存中),然后思考:
它立刻拿数据,change buffer 刚写完就要将数据页读进内存,还没来的及merge呢,上边说了,数据从磁盘到内存涉及随机IO的访问,所以相当于上边说的唯一索引的场景,change buffer 起不到任何的作用,反而增加了这个change buffer维护的代价。
所以如果所有的更新后面,都马上伴随着对这个记录的查询,那么应该关闭 change buffer。而在其他情况下,change buffer 都能提升更新性能。
因此,对于写多读少的场景非常适用于change buffer,比如账单类,日志系统。
特别地,在使用机械硬盘时,change buffer 这个机制的收效是非常显著的。所以,当你有一个类似“历史数据”的库,并且出于成本考虑用的是机械硬盘时,那你应该特别关注这些表里的索引,尽量使用普通索引,然后把 change buffer 尽量开大,以确保这个“历史数据”表的数据写入速度。( 因为机械硬盘盘头寻址慢,随机读成本相对于ssd更高,而change buffer可以有效减少随机读 )
因为redolog 和 change buffer 比较像,简单的说一下两者之间的区别:
redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。